Bug #32364 MERGE doen't work with compressed and uncompressed tables
Submitted: 14 Nov 2007 9:56 Modified: 14 Nov 2007 11:29
Reporter: Grigory Rubtsov Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Merge storage engine Severity:S2 (Serious)
Version:5.1.22-rc, 5.1 BK, 5.0 BK, 4.1 BK OS:Any
Assigned to: CPU Architecture:Any

[14 Nov 2007 9:56] Grigory Rubtsov
Description:
The bug is "A bug" complement to "Not a Bug" http://bugs.mysql.com/bug.php?id=32079

Please refer to documentation: http://dev.mysql.com/doc/refman/5.1/en/merge-storage-engine.html
"However, any or all of the MyISAM tables can be compressed with myisampack."

Actually in 5.0 MERGE works with ROW_FORMAT=DYNAMIC and ROW_FORMAT=FIXED tables freely. Documentation says only about columns definiton and nothing about ROW_FORMAT.

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

mysql> insert into m VALUES (1,'a'), (2,'b'), (3,'c'), (4,'d');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> insert into m SELECT m.* from m m, m mx;
Query OK, 16 rows affected (0.00 sec)
Records: 16  Duplicates: 0  Warnings: 0

mysql> insert into m SELECT m.* from m m, m mx;
Query OK, 400 rows affected (0.00 sec)
Records: 400  Duplicates: 0  Warnings: 0

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

[root@amec0 test]# myisampack m.MYI
Compressing m.MYD: (420 records)
- Calculating statistics
- Compressing file
89.27%     
[root@amec0 test]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.1.22-rc-community-log MySQL Community Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

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

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

Then without ROW_FORMAT=FIXED

mysql> drop table mm;
Query OK, 0 rows affected (0.00 sec)

mysql> drop table m2;
Query OK, 0 rows affected (0.00 sec)

mysql> create table m2 (a int, f varchar(10)) 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 limit 5;
ERROR 1194 (HY000): Table 'mm' is marked as crashed and should be repaired
[14 Nov 2007 11:28] Sveta Smirnova
test case

Attachment: bug32364.test (application/octet-stream, text), 459 bytes.

[14 Nov 2007 11:29] Sveta Smirnova
Thank you for the report.

Verified as described using attached test case.
[9 Jan 2008 17:27] Timothy Smith
Workaround is to define underlying tables the same way.