Auto Increment skipping numbers?
The default auto_increment behavior in MySQL 5.1 and later will "lose" auto-increment values if the INSERT fails. That is, it increments by 1 each time, but doesn't undo an increment if the INSERT fails. It's uncommon to lose ~750 values but not impossible (I consulted for a site that was skipping 1500 for every INSERT that succeeded).
You can change innodb_autoinc_lock_mode=0
to use MySQL 5.0 behavior and avoid losing values in some cases. See http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html for more details.
Another thing to check is the value of the auto_increment_increment
config variable. It's 1 by default, but you may have changed this. Again, very uncommon to set it to something higher than 1 or 2, but possible.
I agree with other commenters, autoinc columns are intended to be unique, but not necessarily consecutive. You probably shouldn't worry about it so much unless you're advancing the autoinc value so rapidly that you could run out of the range of an INT (this has happened to me).
How exactly did you fix it skipping 1500 for ever insert?
The cause of the INSERT failing was that there was another column with a UNIQUE constraint on it, and the INSERT was trying to insert duplicate values in that column. Read the manual page I linked to for details on why this matters.
The fix was to do a SELECT first to check for existence of the value before attempting to INSERT it. This goes against common wisdom, which is to just try the INSERT and handle any duplicate key exception. But in this case, the side-effect of the failed INSERT caused an auto-inc value to be lost. Doing a SELECT first eliminated almost all such exceptions.
But you also have to handle a possible exception, even if you SELECT first. You still have a race condition.
You're right! innodb_autoinc_lock_mode=0 worked like a charm.
In your case, I would want to know why so many inserts are failing. I suspect that like many SQL developers, you aren't checking for success status after you do your INSERTs in your AJAX handler, so you never know that so many of them are failing.
They're probably still failing, you just aren't losing auto-inc id's as a side effect. You should really diagnose why so many fails occur. You could be either generating incomplete data, or running many more transactions than necessary.
Sequential increment skipping numbers
serial
columns, or IDENTITY
in Postgres 10 or later, draw numbers from a SEQUENCE
and gaps are to be expected. Their job is to make concurrent write access possible with unique numbers - not necessarily gap-less numbers.
If you don't actually have concurrent write access, there are simple ways to achieve (mostly) gap-less numbers. Like:
INSERT INTO tbl (info)
SELECT 'xxx'
WHERE NOT EXISTS (SELECT FROM tbl WHERE info = 'xxx');
That doesn't burn a serial ID from the SEQUENCE
because a duplicate insert is skipped. (The INSERT
might still fail for any other reason - and burn a serial number. You could reset the SEQUENCE
in such a case:
- How to reset postgres' primary key sequence when it falls out of sync?
While inserting multiple rows in a single statement, you also have to rule out duplicates within the inserted set. Example code:
- Return data from subselect used in INSERT in a Common Table Expression
But if you do have concurrent writes, none of the above works reliably, on principle. You better learn to accept gaps in the IDs. You can always have a query with row_number() OVER (ORDER BY id)
to generate gap-less numbers after the fact. However, the numbers are still arbitrary to a degree. Smaller numbers were not necessarily committed earlier. There are exceptions under concurrent write load. Related:
- Primary Key Value Not Incrementing Correctly
- Serial numbers per group of rows for compound key
- Auto increment table column
Or consider a UUID instead (dat type uuid
) and avoid the inherent problem of duplicates with random values in a huge key space. Not at all serial, though:
- Generating a UUID in Postgres for Insert statement?
Why does my auto-incremented Id skip numbers in SQL Server?
What is causing this?
A couple of potential causes come to mind:
- Rows were deleted?
- The results you're looking at aren't sorted by
id
? - Identifiers were allocated in a transaction which wasn't committed?
- The database engine allocated potential identifiers as an internal performance tuning and that allocation was lost (unexpected server re-start, for example)?
There could be more potential causes I'm not thinking of.
How can I prevent this in the future?
Depends on the cause. But it's kind of a moot point, really. Why would you need to "prevent this"? What exactly is wrong with this situation? Identifiers don't need to be consecutive, they just need to be unique. (And preferably sequential for an index, otherwise the database will have to re-build the index.)
How can I stop my current table from continuing on this dark road?
The dark road of... generating unique identifiers? Well, I guess you could manually supply unique identifiers. GUIDs are good for that sort of thing. There are pros and cons, though. (I don't know if recent implementations have improved this, but historically GUIDs don't make for a good clustered index.)
MySQL: Why does my INSERT statement skip 56 numbers when auto-incrementing the id?
This behavior has something to do with "bulk inserts" and the innodb_autoinc_lock_mode
setting.
As far as I understand it (the documentation isn't quite clear about this), when you use a INSERT INTO ... SELECT
statement, MySQL cannot know how many rows are actually being inserted before running the query, but the IDs for the new AUTO_INCREMENT values have to be reserved when using innodb_autoinc_lock_mode=1
(consecutive) or 2
(interleaved). From my observation it reserves a set of AUTO_INCREMENT numbers where the count is a power of 2 (cannot confirm this, only a guess). See the following example:
CREATE TABLE sourceTable(
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(20)
);
CREATE TABLE targetTable(
id INT AUTO_INCREMENT PRIMARY KEY,
original VARCHAR(30)
);
INSERT INTO sourceTable(name) VALUES ('one');
INSERT INTO sourceTable(name) VALUES ('two');
INSERT INTO sourceTable(name) VALUES ('three');
INSERT INTO sourceTable(name) VALUES ('four');
INSERT INTO sourceTable(name) VALUES ('five');
INSERT INTO targetTable(original) SELECT name FROM sourceTable;
INSERT INTO targetTable(original) VALUES ('manual');
SELECT * FROM targetTable;
This will generate the following output:
+----+----------+
| id | original |
+----+----------+
| 1 | one |
| 2 | two |
| 3 | three |
| 4 | four |
| 5 | five |
| 8 | manual |
+----+----------+
When inserting the 5 rows from the source table, it reserves the next 8 possible AUTO_INCREMENT values because that is the closest power of 2 number greater than 5. However, it will use only 5 of them since you insert only 5 rows.
In your case, you are inserting 200 rows, so the closest power of 2 number greater than 200 would be 256. So you have a "gap" of 56 missing AUTO_INCREMENT values and the next entry gets the ID 256.
MySQL skipping auto_increment values
This is a documented behavior:
“Lost” auto-increment values and sequence gaps
In all lock modes (0, 1, and 2), if a transaction that generated auto-increment values rolls back, those auto-increment values are “lost”. Once a value is generated for an auto-increment column, it cannot be rolled back, whether or not the “INSERT-like” statement is completed, and whether or not the containing transaction is rolled back. Such lost values are not reused. Thus, there may be gaps in the values stored in an AUTO_INCREMENT column of a table.
In your use case, the auto_increment is assigned a new value before the unique constraint on username
is checked. Then the constraint check fails and the transaction is rolled back, leaving a gap in the sequence.
There are various other cases than can lead to gaps in the auto_increment sequence (eg when using the INSERT ... IGNORE
or INSERT ... ON DUPLICATE KEY
syntax), that are in essence related to an implicit or explicit rollback of the transaction.
Bottom line: do not assume that aut_increment keys are sequential. What is guaranteed is uniqueness, and, to some extent, increasing numbers.
Related Topics
How to Do a Curl Request to the Same Server
How to Convert Array of Bytes to a String in PHP
Using Utf8Mb4 with PHP and MySQL
Facebook Sdk V4 for PHP Minimal Example
How to Create a Database-Driven Multi-Level Navigation Menu Using Laravel
Create Programmatically a Product Using Crud Methods in Woocommerce 3
Get the Index of a Certain Value in an Array in PHP
Reverse Order of Foreach List Items
Should I Be Using Assert in My PHP Code
Definitive Way to Get User Ip Address PHP
Php: How to Get Associative Array Key from Numeric Index
How to Convert MySQL Time to Unix Timestamp Using PHP
Creating a Folder When I Run File_Put_Contents()
PHP Float with 2 Decimal Places: .00
Simpler Way to Check If Variable Is Not Equal to Multiple String Values