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:
None 
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
Description:
Get
 ERROR 1168 (HY000): All tables in the MERGE table are not identically defined
even though I am only merging a single table.
See console log below:
note that the table 'temp_tbl' is being created with
 CREATE .... SELECT ...   (although the select yields 0 rows)

If the 'SELECT ....' is removed the problem does not occur.

Note also (not shown) that if you remove any one of the columns (A, B,C,D), the problem does not occur.

This problem also occurs on 4.1.10-nt except that the error msg is
Can't open file: '#sql-128_1546.MRG' (errno: 143)

(THANK YOU for making better error messages for MERGE problems!!)
(This is the simplest form of the problem that is occuring is a real life application).

Console Log
mysql> drop table if exists temp_merge,temp_tbl,temp_source;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE temp_source (a int, b int, c int, d int) ;
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE temp_tbl (a int, b int, c int, d int )
    ->   select * from temp_source ;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE temp_merge like temp_tbl;
Query OK, 0 rows affected (0.00 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> /* repeat without the SELECT */
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.01 sec)

mysql> CREATE TABLE temp_merge like temp_tbl;
Query OK, 0 rows affected (0.00 sec)

mysql> alter table temp_merge engine=merge union=(temp_tbl);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

How to repeat:
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);
[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.