MySQL Bugs: #25647: improve docs: warn that repair, optimize, analyze can corrupt merge base tables

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:	None
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

Description:
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: 

http://dev.mysql.com/doc/refman/5.0/en/mysqlcheck.html
http://dev.mysql.com/doc/refman/5.0/en/repair-table.html
http://dev.mysql.com/doc/refman/5.0/en/analyze-table.html
http://dev.mysql.com/doc/refman/5.0/en/optimize-table.html

Some warnings already exist in: http://dev.mysql.com/doc/refman/5.0/en/merge-table-problems.html

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.

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?

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

Please note that there's NO table corruption here, table corruption is reported, but the table is fine.

If that's the case, then this is a server bug.

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.

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.