MySQL Bugs: #6853: MERGE Table Issue

Bug #6853	MERGE Table Issue
Submitted:	26 Nov 2004 20:02	Modified:	8 Oct 2008 9:46
Reporter:	George Mrvos	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Merge storage engine	Severity:	S2 (Serious)
Version:	5.0.0	OS:	Linux (SLES9)
Assigned to:	Leandro Morgado	CPU Architecture:	Any

Description:
When a table that is part of a merge group has its indexes disabled, data truncated, then has the data repopulated and indexes re-enabled the merge table reports error...
ERROR 1030 (HY000): Got error 124 from storage engine...

How to repeat:
Create several identical tables (table1, table2,etc) including indexes and populate with data. Create the merge table(table0). Then on table1 do alter table table1 disable keys; Then...truncate table table1. Then repopulate with data and enable keys. When yo u query table0 you get the error  ERROR 1030 (HY000): Got error 124 from storage engine.

Suggested fix:
You should be able to disable, change data, then enable keys on an underlying table that is part of a merge group without having to re-specify the merge table definition.

Unfortunately, it cannot be fixed withot big code changes (which are "work in progress", by the way).

It is documented in the manual, "MERGE Table Problems":

   * You can't use `DROP TABLE', `ALTER TABLE', `DELETE FROM' without
     a `WHERE' clause, `REPAIR TABLE', `TRUNCATE TABLE', `OPTIMIZE
     TABLE', or `ANALYZE TABLE' on any of the tables that are mapped
     into a `MERGE' table that is "open."  If you do this, the `MERGE'
     table may still refer to the original table and you will get
     unexpected results. The easiest way to work around this deficiency
     is to issue a `FLUSH TABLES' statement to ensure that no `MERGE'
     tables remain "open."

I'm using mysql-5.0.3-beta-win32 and when execute this :

SELECT * FROM file_x WHERE col_y IN
(SELECT col_y from file_x WHERE col_z IN ('text_1','text_2'));

I get:
ERROR 1030 (HY000): Got error 124 from storage engine

This is not the case with mysql-4.1.10a-win32.

Sorry, but part of the comment is missing in my previous message.

How is this 'MERGE Table Issue' related to Sub-selects?
*/*

There have been quite a few fixes for merge tables in 5.1 and 6.0, the bug needs to be re-verified.

SQL commands to create/populate test

Attachment: dump.sql (application/octet-stream, text), 1.76 KiB.

output of tests on 5.0.0

Attachment: test-mysql5.0.0 (application/octet-stream, text), 2.84 KiB.

output of tests on 5.0.67

Attachment: test-mysql5.0.67 (application/octet-stream, text), 2.79 KiB.

I was unable to reproduce this behaviour on reported 5.0.0 version or on latest stable 5.0.67GA version. See the attached files for results.

Can you provide the necessary steps to replicate the error?