Bug #71114 I got issue for multiple insert in mysql for AutoIncrement number for table.
Submitted: 9 Dec 2013 6:45 Modified: 9 Dec 2013 10:16
Reporter: Nishit Modi Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.6.15, 5.7.3-m13 OS:Windows
Assigned to: CPU Architecture:Any
Tags: AutoIncrement Sequence Issue

[9 Dec 2013 6:45] Nishit Modi
Description:
I got issue for multiple insert in mysql for AutoIncrement number for table.

How to repeat:
Dear sir,

         I got issue for multiple insert in mysql for AutoIncrement number for table.

e.g.
Please follow below step.
1)
I have Two table table1 and table2 
CREATE TABLE `table1` (
  `intAutoNumber` int(9) unsigned zerofill NOT NULL AUTO_INCREMENT,
  `chrvalue` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`intAutoNumber`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE `table2` (
  `intAutoNumber` int(9) unsigned zerofill NOT NULL AUTO_INCREMENT,
  `chrvalue` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`intAutoNumber`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

2) Single row Insert in table1

INSERT INTO table1 (chrvalue) VALUES ('aa');
INSERT INTO table1 (chrvalue) VALUES ('ba');
INSERT INTO table1 (chrvalue) VALUES ('ca');
INSERT INTO table1 (chrvalue) VALUES ('da');
INSERT INTO table1 (chrvalue) VALUES ('ea');
INSERT INTO table1 (chrvalue) VALUES ('fa');
INSERT INTO table1 (chrvalue) VALUES ('ga');

My table1 data will be as below

intAutoNumber	chrvalue
000000001	  aa
000000002	  ba
000000003	  ca
000000004	  da
000000005	  ea
000000006	  fa
000000007	  ga

3) Not i want copy top 5 row from table1 to table2 so my query as below

INSERT INTO table2 (chrvalue)(SELECT chrvalue FROM table1 LIMIT 0,5);

My table2 data will be as below

intAutoNumber	chrvalue
000000001	  aa
000000002	  ba
000000003	  ca
000000004	  da
000000005	  ea

4) Now i follow step 3 one more time

INSERT INTO table2 (chrvalue)(SELECT chrvalue FROM table1 LIMIT 0,5);

My table2 data will be as below

intAutoNumber	chrvalue
000000001	  aa
000000002	  ba
000000003	  ca
000000004	  da
000000005	  ea
000000008	  aa
000000009	  ba
000000010	  ca
000000011	  da
000000012	  ea

so above table we can see intAutoNumber "000000008" in place of "000000006".

How it will be possible????

Thanks
[9 Dec 2013 9:57] Hartmut Holzgraefe
Looks as if auto_increment IDs were prefetched for all SELECT results without taking LIMIT into account ...

Not a bug as there is no guarantee on auto_increment being without gaps, just that the ID sequence will be monotonous, so "only" a feature request IMHO to handle INSERT INTO...SELECT with LIMIT in a more clever, less surprising way ...
[9 Dec 2013 10:13] MySQL Verification Team
Hello Nishit,

Thank you for the bug report, duplicate of http://bugs.mysql.com/bug.php?id=61058
See, http://dev.mysql.com/doc/refman/5.7/en/innodb-auto-increment-handling.html

Thanks,
Umesh
[9 Dec 2013 10:19] Peter Laursen
@Nishit ..

You are reproting this as an issue with "SQLYog Ultimate - Mysql GUI v9.50".  SQLyog is not a program from Oracle (it is a 3rd party client distributed by Webyog).

However sinde this is reproducible in *pure SQL* it is not licent specific.  Version should be changed to the MySQL server version in case - or "any".

Client-specific issues with SQLyog )what this reprot is not) are irrelevant for this bugs systems.  You should report those to http://forums.webyog.com or send a mail to support@webyog.com.

Peter
(not a MySQL/Oracle person - but working for Webyog)
[9 Dec 2013 10:24] Peter Laursen
@Nishit .. one more point.

What is your setting of 'innodb_autoinc_lock_mode' variable?  If it is "1" you will most likely achieve the behavior you expect by changing this server variable to "0" (and as far as I can understand it does not really matter as long as you don't use replication)