MySQL Bugs: #8053: MERGE tables on compressed tables report ERROR 1030

Bug #8053	MERGE tables on compressed tables report ERROR 1030
Submitted:	20 Jan 2005 17:10	Modified:	8 Feb 2005 12:13
Reporter:	Lorenzo Luconi Trombacchi	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:
Version:	4.1.9	OS:	Linux (Linux Fedora Core 3 x86_64)
Assigned to:	Ingo Strüwing	CPU Architecture:	Any

Description:
Hi,

I found a strange error using merge tables with compressed tables.

I have cutted your merge example from paragraph 14.2 of your on-line documentation and pasted it to my mysql server:

> CREATE TABLE t1 ( a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, message CHAR(20)); 
> CREATE TABLE t2 ( a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, message CHAR(20));
> INSERT INTO t1 (message) VALUES ('Testing'),('table'),('t1');
> INSERT INTO t2 (message) VALUES ('Testing'),('table'),('t2');
> CREATE TABLE total ( a INT NOT NULL AUTO_INCREMENT,  message CHAR(20), INDEX(a)) TYPE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST;

After this I run this query without problems:
> SELECT * FROM total WHERE a = 1;

But I tried to pack table t1 with myisampack command and the same query report this error:
ERROR 1030 (HY000): Got error 124 from storage engine

Other query like these work as usual:
> SELECT * FROM total;
> SELECT COUNT(*) FROM total;

I tried to recreate the merge table, to pack the table t2 too (so all the tables are packed), but I always got the same error.

Only if I remove all the index definition from the merge table, the query works:
> CREATE TABLE total ( a INT NOT NULL,  message CHAR(20)) TYPE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST;

... but this is not a good solution for my real tables with about 3.000.000 records.

These are the information about my system:

- Dual AMD Opteron
- Linux Fedora Core 3 64 bit version (x86_64)
- Kernel 2.6.10-1.741_FC3smp
- MySQL 4.1.9 x86_64 ( I downloaded the rpm version from mysql mirror)
- 4 Gbyte Ram, HDD in RAID SCSI, LVM2 partition with ext3

Thank you in advance.

Ciao,
Lorenzo Luconi Trombacchi

How to repeat:
All the information about how to repeat the problem are explained in description section

Hi,

Thank you for the report, but I wasn't able to repeat it.
Is this exactly set of actions that cause such error?

This info was sent to dev-bugs instead of using web-interface.

This is the summary of the action:

- Create table t1
- Create table t2
- Insert some record in t1 and t2 table
- create the merge table named total
- select * from total where a = 1  - (it works)
- stop mysql server
- execute command: myisampack -f t1.MYI (-f is needed because t1 is too 
small)
- restart mysql server
- select * from total where a = 1 - I got the error
- drop table total and i recreate it again
- same query same error

I tried again all the actions in the same order listed in my email in another server and I got the same error.
The server is different from the first one:

- AMD Athlon 64 (single cpu)
- Fedora Core 3 x86_64
- Kernel 2.6.10-1.741_FC3 x86_64
- MySQL 4.1.8 x86_64 in rpm downloaded from mysql mirror
- 512 Mbyte Ram, HDD SATA RAID software, LVM2 partition with ext3

This is the summary of the action:

- Create table t1
- Create table t2
- Insert some record in t1 and t2 table
- create the merge table named total
- select * from total where a = 1  - (it works)
- stop mysql server
- execute command: myisampack -f t1.MYI (-f is needed because t1 is too small)
- restart mysql server
- select * from total where a = 1 - I got the error
- drop table total and i recreate it again
- same query same error

I made again the same test to another server (the third) with a completely differnt version and architecture with same error....

- AMD Athlon 2800+ (single 32 bit cpu)
- RedHat 9
- Kernel 2.4.20-37.9.legacy
- MySQL 4.0.21 in rpm downloaded from mysql mirror
- 512 Mbyte RAM, HDD SATA RAID Soft, LVM partition with ext3

Probably I make some mistakes somewhere....
I'm using merge tables with compressed and no compressed tables without any problem on MySQL 3 and RedHat 7.3.

Thank you.

Lorenzo

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

Additional info:

I could reproduce the behaviour. Error number 124 means HA_ERR_WRONG_INDEX.
The index on the compressed table was disabled, but the optimizer tried to use it anyway.
But:
The manual says in 8.2. myisampack, the MySQL Compressed Read-only Table Generator:
" After you run myisampack, you must run myisamchk to re-create any indexes."
myisampack says:
"Remember to run myisamchk -rq on compressed tables".
I did so and found everything working well.