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:
Category:MySQL Server: Merge storage engine Severity:S2 (Serious)
Version:5.0.0 OS:Linux (SLES9)
Assigned to: Leandro Morgado CPU Architecture:Any

[26 Nov 2004 20:02] George Mrvos
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.
[27 Nov 2004 10:26] Sergei Golubchik
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
     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."
[7 Apr 2005 15:34] edo h
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.
[7 Apr 2005 16:02] edo h
Sorry, but part of the comment is missing in my previous message.

How is this 'MERGE Table Issue' related to Sub-selects?
[29 Sep 2008 20:27] Konstantin Osipov
There have been quite a few fixes for merge tables in 5.1 and 6.0, the bug needs to be re-verified.
[7 Oct 2008 18:04] Leandro Morgado
SQL commands to create/populate test

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

[7 Oct 2008 18:05] Leandro Morgado
output of tests on 5.0.0

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

[7 Oct 2008 18:05] Leandro Morgado
output of tests on 5.0.67

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

[7 Oct 2008 18:12] Leandro Morgado
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?