Auto Increment skipping numbers?

by

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.

More Related Contents:

  1. Warning: mysqli_connect(): (HY000/1045): Access denied for user ‘username’@’localhost’ (using password: YES)
  2. phpmyadmin automatic logout time
  3. Using PHP data in MySQL [closed]
  4. which is better every user have a table or just a row [closed]
  5. PHP – Change value y,n in database [closed]
  6. The requested URL was not found on this server.
  7. PDOException SQLSTATE[HY000] [2002] No such file or directory
  8. Many-to-many relationships examples
  9. strange character encoding of stored data , old script is showing them fine new one doesn’t
  10. Warning: mysql_connect(): [2002] No such file or directory (trying to connect via unix:///tmp/mysql.sock) in
  11. How to upload images into MySQL database using PHP code
  12. How to loop through a mysql result set
  13. MySQL check if a table exists without throwing an exception
  14. Maximum execution time in phpMyadmin
  15. How to write a stored procedure using phpmyadmin and how to use it through php?
  16. Mysql No connection could be made because the target machine actively refused it
  17. Mysql event error using php
  18. Replacing mysql_* functions with PDO and prepared statements
  19. Access denied for user ‘root’@’localhost’ with PHPMyAdmin
  20. Is there any way to check the performance of mysql Indexing
  21. #1193 – Unknown system variable ‘lc_messages’ when trying to login to phpmyadmin
  22. MySQL skipping first row
  23. Fatal error: Call to undefined function mysqli_result()
  24. Laravel nested relationships
  25. How to SELECT DEFAULT value of a field
  26. Selecting all fields except only one field in mysql [duplicate]
  27. MySQL ORDER BY rand(), name ASC
  28. How to get last inserted id from table MySQL [duplicate]
  29. Installing PDO driver on MySQL Linux server
  30. phpmysql error – #1273 – #1273 – Unknown collation: ‘utf8mb4_general_ci’

Leave a Comment