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: | |
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
[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