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.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.040 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.