Bug #32079 MERGE doen't work with tables with different row_format

• Submitted: 3 Nov 2007 20:07
• Modified: 13 Nov 2007 21:54
• Reporter: Grigory Rubtsov
• Status: Not a Bug
• Category: MySQL Server: Merge storage engine
• Severity: S3 (Non-critical)
• OS: Any
• CPU Architecture: Any

[3 Nov 2007 20:07] Grigory Rubtsov

Description:
If row_format is different for underlying tables, merge doesn't work.

How to repeat:
mysql> \s
--------------
mysql  Ver 14.14 Distrib 5.1.22-rc, for redhat-linux-gnu (x86_64) using readline 5.0

Connection id:          8
Current database:       test
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.1.22-rc-community-log MySQL Community Edition (GPL)
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    cp1251
Db     characterset:    cp1251
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:            /var/run/mysqld/mysql1.sock
Uptime:                 1 day 9 hours 7 min 9 sec

Threads: 1  Questions: 74  Slow queries: 0  Opens: 43  Flush tables: 4  Open tables: 2  Queries per second avg: 0.0
--------------

mysql> create table m (a int, f varchar(10)) ENGINE=MyISAM;
Query OK, 0 rows affected (0.00 sec)

mysql> create table m2 (a int, f varchar(10)) ROW_FORMAT=fixed ENGINE=MyISAM;
Query OK, 0 rows affected (0.00 sec)

mysql> create table mm (a int, f varchar(10)) engine=MERGE UNION=(m,m2) INSERT_METHOD=LAST;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from mm;
ERROR 1168 (HY000): Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist

[13 Nov 2007 21:54] Sveta Smirnova

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

According to http://dev.mysql.com/doc/refman/5.0/en/merge-storage-engine.html:

"The MERGE storage engine, also known as the MRG_MyISAM engine, is a collection of identical MyISAM tables that can be used as one. “Identical” means that all tables have identical column and index information."