MySQL Bugs: #71114: I got issue for multiple insert in mysql for AutoIncrement number for table.

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

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

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 ...

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

@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)

@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)