Bug #25647 improve docs: warn that repair, optimize, analyze can corrupt merge base tables
Submitted: 16 Jan 2007 11:54 Modified: 8 May 2007 16:50
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Closed Impact on me:
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:* OS:Any (*)
Assigned to: Paul DuBois CPU Architecture:Any
Tags: analyze, bfsm_2007_02_01, corruption, merge, mysqlcheck, Optimize, Repair

[16 Jan 2007 11:54] Shane Bester
The mysqlcheck, optimize table, repair table, and analyze table documentation should warn about running those operations for tables when they are base tables of a MERGE table.

I think the --myisam_recovery option might also be dangerous to use if the table getting repaired is really belonging to a MERGE collection.

How to repeat:
Have alot of insert/select/update/delete/replace activity on merge + base tables.
then run "mysqlcheck -o test" and watch the corruptions happen.

Suggested fix:
Warn about merge tables in: 


Some warnings already exist in: http://dev.mysql.com/doc/refman/5.0/en/merge-table-problems.html
[19 Jan 2007 8:37] MySQL Verification Team
one warning says: "If you do so, the MERGE table may still refer to the original table, which yields unexpected results. "

in reality, it mostly yields table corruption. perhaps be more explicit there.
[23 Jan 2007 19:01] Paul DuBois
Shane, is there something the user can to do to minimize
or eliminate the possibility of table damage? For example,
does FLUSH TABLES before the table maintenance operation
help if you can guarantee that other statements don't
also access the table in the meantime?
[24 Jan 2007 13:07] MySQL Verification Team
After doing some tests, even this isn't enough to prevent corruptions of the base table t1 when using optimize table:

lock table t1 write;
flush table t1;
optimize table t1;
unlock tables;

when in other threads you have inserts happening into the merge table m1.
I hope all these problems are addressed in bug #25700
[2 Feb 2007 10:23] Sergei Golubchik
Please note that there's NO table corruption here, table corruption is reported, but the table is fine.
[22 Mar 2007 9:02] Jon Stephens
If that's the case, then this is a server bug.
[22 Mar 2007 13:21] MySQL Verification Team
Jon, this is 100% a documentation request.  the real server bugs are all handled in other reports mostly by dean and myself (i forget exact #'s). 

I just thought a small note should be made about the consequence
of running those DDL statements in a merge table situation would
help prevent users from doing so.. 

Maybe it's not needed, but that's up to you to decide ultimately.
[8 May 2007 16:50] Paul DuBois
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

Per discussion with Shane and Serg, the corruption reported for MERGE tables is indeed a spurious message. A FLUSH TABLES should be performed after modifying the underlying MyISAM tables. I will extend the section in the manual on MERGE tables to point this out.