1

Can anyone suggest me the workaround for the following bug in mysql.

  http://bugs.mysql.com/bug.php?id=61058

This bug was reported long ago (2007) with mysql 5.0, but i am still facing same issue with mysql 5.6. I have followed this post which first gave some insight for this problem.

Mysql auto increment jumps when insert-select

Followed the post as some one suggested preferably setting 

ALTER TABLE `test` AUTO_INCREMENT = 1;

which will not work in my case where i have a stored procedure which will run in background as an event for a specified time.

Can anyone please suggest me for a workaround....??

Thanks in advance

Community
  • 1
  • 1
Amit
  • 465
  • 2
  • 6
  • 20

2 Answers2

1

First, you should make sure that mysql is up to date and second try to reset auto increment like below:

ALTER TABLE tablename AUTO_INCREMENT = 1
mgokhanbakal
  • 1,679
  • 1
  • 20
  • 26
  • ,as i said above same thing i tried in my stored procedure where i'll call the above code before doing Insert...select, but still the auto_increment column skips values.. – Amit Nov 20 '13 at 05:05
  • You can contact with MySql support about this bug if you have already applied possible patchs or updates related to this issue. – mgokhanbakal Nov 20 '13 at 05:07
1

You can change innodb_autoinc_lock_mode=0 (“traditional” lock mode) from mysql.ini to avoid skipping values in primary key in some cases. See the manual innodb auto increment handling for more details.

As per manual 'The traditional lock mode option is provided for backward compatibility, performance testing, and working around issues with “mixed-mode inserts”, due to possible differences in semantics'.

'In this lock mode, all “INSERT-like” statements obtain a special table-level AUTO-INC lock for inserts into tables with AUTO_INCREMENT columns. This lock is normally held to the end of the statement (not to the end of the transaction) to ensure that auto-increment values are assigned in a predictable and repeatable order for a given sequence of INSERT statements, and to ensure that auto-increment values assigned by any given statement are consecutive'.

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. it is very uncommon to set it to something higher than 1 or 2, but possible.

Haritsinh Gohil
  • 5,818
  • 48
  • 50