Bug #17655 Insert into <table> select ... with auto increment field not work properly
Submitted: 22 Feb 2006 20:41 Modified: 23 Feb 2006 14:40
Reporter: Denis Lavoie Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:5.0.15-nt OS:Windows (Windows 2003)
Assigned to: CPU Architecture:Any

[22 Feb 2006 20:41] Denis Lavoie
Description:
The 'Insert into <tablename> select ...' statement does not work properly on table with an auto increment field.  The auto increment field is not incremented properly.  

How to repeat:
Here is the script to reproduce the problem:

/*
SQLyog Enterprise - MySQL GUI v5.02
Host - 5.0.15-nt : Database - test
*********************************************************************
Server version : 5.0.15-nt
*/

/*Table structure for table `tab1` */

CREATE TABLE `tab1` (
  `descr` varchar(50) default NULL,
  `id` int(10) unsigned NOT NULL auto_increment,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Table structure for table `tab2` */

CREATE TABLE `tab2` (
  `descr` varchar(50) default NULL,
  `id` int(10) unsigned NOT NULL auto_increment,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/* Procedure structure for procedure `test_insert_into_select` */

drop procedure if exists `test_insert_into_select`;

DELIMITER $$;

CREATE PROCEDURE `test_insert_into_select`()
BEGIN
delete from tab1;
delete from tab2;
#
ALTER TABLE tab1 DROP COLUMN id;
ALTER TABLE tab2 DROP COLUMN id;
#
ALTER TABLE tab1 ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  ADD PRIMARY KEY (id);
ALTER TABLE tab2 ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  ADD PRIMARY KEY (id);
#
insert into tab1 (descr) select 'test1';
commit;
insert into tab1 (descr) select 'test2';
commit;
insert into tab1 (descr) select 'test3';
commit;
insert into tab1 (descr) select 'test4';
commit;
insert into tab1 (descr) select 'test5';
commit;
insert into tab2 (descr) select 'test1';
commit;
insert into tab2 (descr) select 'test2';
commit;
insert into tab2 (descr) select 'test3';
commit;
insert into tab2 (descr) select 'test4';
commit;
insert into tab2 (descr) select 'test5';
commit;
END$$

DELIMITER ;$$

/* Procedure structure for procedure `test_insert_into_value` */

drop procedure if exists `test_insert_into_value`;

DELIMITER $$;

CREATE PROCEDURE `test_insert_into_value`()
BEGIN
delete from tab1;
delete from tab2;
#
ALTER TABLE tab1 DROP COLUMN id;
ALTER TABLE tab2 DROP COLUMN id;
#
ALTER TABLE tab1 ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  ADD PRIMARY KEY (id);
ALTER TABLE tab2 ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  ADD PRIMARY KEY (id);
#
insert into tab1 (descr) value ('test1');
commit;
insert into tab1 (descr) value ('test2');
commit;
insert into tab1 (descr) value ('test3');
commit;
insert into tab1 (descr) value ('test4');
commit;
insert into tab1 (descr) value ('test5');
commit;
insert into tab2 (descr) value ('test1');
commit;
insert into tab2 (descr) value ('test2');
commit;
insert into tab2 (descr) value ('test3');
commit;
insert into tab2 (descr) value ('test4');
commit;
insert into tab2 (descr) value ('test5');
commit;
END$$

DELIMITER ;$$

Suggested fix:
Execute the two differents stores procedure 'test_insert_into_value' and 'test_insert_into_select' and look at the result in tab1 and tab2.

The 'test_insert_into_value'  stored procedure is working properly where the id's of tab1 and tab2 start from 1 to 5.

The 'test_insert_into_select'  stored procedure is not working properly where the id's of tab1 start from 1 to 5 and tab2 start from 6 to 10!  

This cause many problems with more complex queries, especially when we do data importation scripts.  There is a workaround where we can create a cursor and do an insert into values for each rows but it is really too slow for importation purposes.
[23 Feb 2006 10:26] Valeriy Kravchuk
Thank you for a problem report. Sorry, but I was not able to repeat the behaviour you described with 5.0.19-BK on Linux:

mysql> CREATE TABLE `tab1` (
    ->   `descr` varchar(50) default NULL,
    ->   `id` int(10) unsigned NOT NULL auto_increment,
    ->   PRIMARY KEY  (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.21 sec)

mysql> CREATE TABLE `tab2` (
    ->   `descr` varchar(50) default NULL,
    ->   `id` int(10) unsigned NOT NULL auto_increment,
    ->   PRIMARY KEY  (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE PROCEDURE `test_insert_into_select`()
    -> BEGIN
    -> delete from tab1;
    -> delete from tab2;
    -> #
    -> ALTER TABLE tab1 DROP COLUMN id;
    -> ALTER TABLE tab2 DROP COLUMN id;
    -> #
    -> ALTER TABLE tab1 ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    ->   ADD PRIMARY KEY (id);
    -> ALTER TABLE tab2 ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    ->   ADD PRIMARY KEY (id);
    -> #
    -> insert into tab1 (descr) select 'test1';
    -> commit;
    -> insert into tab1 (descr) select 'test2';
    -> commit;
    -> insert into tab1 (descr) select 'test3';
    -> commit;
    -> insert into tab1 (descr) select 'test4';
    -> commit;
    -> insert into tab1 (descr) select 'test5';
    -> commit;
    -> insert into tab2 (descr) select 'test1';
    -> commit;
    -> insert into tab2 (descr) select 'test2';
    -> commit;
    -> insert into tab2 (descr) select 'test3';
    -> commit;
    -> insert into tab2 (descr) select 'test4';
    -> commit;
    -> insert into tab2 (descr) select 'test5';
    -> commit;
    -> END$$;
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE PROCEDURE `test_insert_into_value`()
    -> BEGIN
    -> delete from tab1;
    -> delete from tab2;
    -> #
    -> ALTER TABLE tab1 DROP COLUMN id;
    -> ALTER TABLE tab2 DROP COLUMN id;
    -> #
    -> ALTER TABLE tab1 ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    ->   ADD PRIMARY KEY (id);
    -> ALTER TABLE tab2 ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    ->   ADD PRIMARY KEY (id);
    -> #
    -> insert into tab1 (descr) value ('test1');
    -> commit;
    -> insert into tab1 (descr) value ('test2');
    -> commit;
    -> insert into tab1 (descr) value ('test3');
    -> commit;
    -> insert into tab1 (descr) value ('test4');
    -> commit;
    -> insert into tab1 (descr) value ('test5');
    -> commit;
    -> insert into tab2 (descr) value ('test1');
    -> commit;
    -> insert into tab2 (descr) value ('test2');
    -> commit;
    -> insert into tab2 (descr) value ('test3');
    -> commit;
    -> insert into tab2 (descr) value ('test4');
    -> commit;
    -> insert into tab2 (descr) value ('test5');
    -> commit;
    -> END$$;
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> call test_insert_into_value();
Query OK, 0 rows affected (0.11 sec)

mysql> select * from tab1;
+-------+----+
| descr | id |
+-------+----+
| test1 |  1 |
| test2 |  2 |
| test3 |  3 |
| test4 |  4 |
| test5 |  5 |
+-------+----+
5 rows in set (0.01 sec)

mysql> select * from tab2;
+-------+----+
| descr | id |
+-------+----+
| test1 |  1 |
| test2 |  2 |
| test3 |  3 |
| test4 |  4 |
| test5 |  5 |
+-------+----+
5 rows in set (0.01 sec)

mysql> call test_insert_into_select();
Query OK, 0 rows affected (0.08 sec)

mysql> select * from tab1;
+-------+----+
| descr | id |
+-------+----+
| test1 |  1 |
| test2 |  2 |
| test3 |  3 |
| test4 |  4 |
| test5 |  5 |
+-------+----+
5 rows in set (0.00 sec)

mysql> select * from tab2;
+-------+----+
| descr | id |
+-------+----+
| test1 |  1 |
| test2 |  2 |
| test3 |  3 |
| test4 |  4 |
| test5 |  5 |
+-------+----+
5 rows in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.19    |
+-----------+
1 row in set (0.00 sec)

I see no difference. So, please, try to use a newer version, 5.0.18.
[23 Feb 2006 10:27] Valeriy Kravchuk
Maybe, you have to provide some other code for one of your procedures?
[23 Feb 2006 13:21] Denis Lavoie
Many thanks for your quick answer!  I downloaded and installed MySQL Server version 5.0.18-nt on a Windows 2003 and it works great now.  It seems thats it was a bug on version 5.0.15-nt only.

We can close this issue.

Thanks, 

Denis Lavoie
[23 Feb 2006 14:40] Denis Lavoie
I tried the new version 5.0.18-nt on Windows XP and it was working fine, not on Windows 2003 as I said on the previous comment.  

My problem now is that I'm unabled to install this new version on my Windows Server 2003 machine.  See 'Bugs #17672'..

Thanks for your support!

Denis Lavoie