Menu
I get gaps in my auto-incremented keys, even with
innodb_autoinc_lock_mode = 0
I isolated the problem to a single INSERT ... SELECT statement. Basically, every INSERT ... SELECT statement increments the auto_increment of the table by one even when no insert is actually performed (duplicate key). In my case, I use INSERT IGNORE, but I tested without and auto_increment is still wrongly incremented.
I worry about this because this INSERT ... SELECT statement runs with somewhat high-frequency so keys are getting large quickly.
I'll live with it if there's no way around, but is there any way to avoid this behavior?
832
AUTO_INCREMENT columns start from 1 by default. The automatically generated value can never be lower than 0. Each table can have only one AUTO_INCREMENT column. It must defined as a key (not necessarily the PRIMARY KEY or UNIQUE key).
To obtain the value immediately after an INSERT , use a SELECT query with the LAST_INSERT_ID() function. For example, using Connector/ODBC you would execute two separate statements, the INSERT statement and the SELECT query to obtain the auto-increment value.
It could have multiple causes: Check if the auto_increment value on the table itself, has the next highest value. Mind that if you have transactions where you INSERT a row and rollback the transaction, that auto_increment value will be gone/skipped.
Syntax for MySQLMySQL uses the AUTO_INCREMENT keyword to perform an auto-increment feature. By default, the starting value for AUTO_INCREMENT is 1, and it will increment by 1 for each new record. VALUES ('Lars','Monsen'); The SQL statement above would insert a new record into the "Persons" table.
It is a bug. See this: http://bugs.mysql.com/bug.php?id=61058
...You can avoid it using:
ALTER TABLE `test` AUTO_INCREMENT = 1;
after every insert...select
However it is not that good idea because alter table creates temporary table, copies data and so on..
76
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
