Bug #13405 | MERGE fails with 'not identical' when CREATE SELECT used | ||
---|---|---|---|
Submitted: | 22 Sep 2005 14:49 | Modified: | 2 Jul 2006 12:21 |
Reporter: | Dave Pullin (Basic Quality Contributor) | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: Merge storage engine | Severity: | S1 (Critical) |
Version: | 5.0.12-beta-nt-log/BK source | OS: | Windows (Windows 2000/Linux) |
Assigned to: | Antony Curtis | CPU Architecture: | Any |
[22 Sep 2005 14:49]
Dave Pullin
[22 Sep 2005 14:56]
Dave Pullin
There's an obvious workaround that works so this bug isn't 'serious' for me any more. Workaround: Separate the CREATE .. SELECT into CREATE; and INSERT INTO; .... drop table if exists temp_merge,temp_tbl; CREATE TABLE temp_tbl (a int, b int, c int, d int ) ; insert into temp_tbl select * from temp_source ; CREATE TABLE temp_merge like temp_tbl; alter table temp_merge engine=merge union=(temp_tbl);
[22 Sep 2005 15:18]
MySQL Verification Team
Thank you for the bug report. I was able for to repeat on Windows but on Linux I got a crash!. Changing priority and severity. c:\mysql\bin>mysql -uroot test Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 5.0.14-rc-nt Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> CREATE TABLE temp_source (a int, b int, c int, d int) ; Query OK, 0 rows affected (0.11 sec) mysql> CREATE TABLE temp_tbl (a int, b int, c int, d int ) -> select * from temp_source ; Query OK, 0 rows affected (0.16 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> CREATE TABLE temp_merge like temp_tbl; Query OK, 0 rows affected (0.02 sec) mysql> alter table temp_merge engine=merge union=(temp_tbl); ERROR 1168 (HY000): All tables in the MERGE table are not identically defined mysql> drop table if exists temp_merge,temp_tbl; Query OK, 0 rows affected (0.00 sec) mysql> CREATE TABLE temp_tbl (a int, b int, c int, d int ) ; Query OK, 0 rows affected (0.05 sec) mysql> CREATE TABLE temp_merge like temp_tbl; Query OK, 0 rows affected (0.02 sec) mysql> alter table temp_merge engine=merge union=(temp_tbl); Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> miguel@hegel:~/dbs/5.0> bin/mysqladmin -uroot create dbq miguel@hegel:~/dbs/5.0> bin/mysql -uroot dbq Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 to server version: 5.0.14-rc-debug Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> CREATE TABLE temp_source (a int, b int, c int, d int) ; Query OK, 0 rows affected (0.05 sec) mysql> CREATE TABLE temp_tbl (a int, b int, c int, d int ) -> select * from temp_source ; ERROR 2013 (HY000): Lost connection to MySQL server during query mysql> CREATE TABLE temp_merge like temp_tbl; ERROR 2006 (HY000): MySQL server has gone away
[1 Jul 2006 23:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
[2 Jul 2006 6:15]
Sergei Golubchik
As a result of fixing BUG#10952, you cannot ALTER TABLE to MERGE anymore.
[2 Jul 2006 12:21]
Dave Pullin
This bug was automatically closed as 'needing feedback' - but I see not request for anything. Is there something you wanted from me?
[2 Jul 2006 18:58]
Sergei Golubchik
"No Feedback" was a mistake. The bug is closed because ALTER TABLE from your original "how to repeat" is no longer possible.
[30 Jun 2009 0:38]
Roel Van de Paar
Actually, though the ALTER TABLE change of BUG#10952 *was reversed*, it looks like this issue no longer appears in 5.1.35: ------------ mysql> drop table if exists temp_merge,temp_tbl,temp_source; CREATE TABLE temp_source (a int, b int, c int, d int) ; CREATE TABLE temp_tbl (a int, b int, c int, d int ) select * from temp_source ; CREATE TABLE temp_merge like temp_tbl; alter table temp_merge engine=merge union=(temp_tbl); /* repeat without the SELECT */ drop table if exists temp_merge,temp_tbl; CREATE TABLE temp_tbl (a int, b int, c int, d int ) ; CREATE TABLE temp_merge like temp_tbl; alter table temp_merge engine=merge union=(temp_tbl); Query OK, 0 rows affected, 3 warnings (0.00 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0 Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> ------------ The second testcase above (resulting in 1168, 2013 and 2006 errors) also works without any problems in 5.1.35.