MySQL Bugs: #20226: Documentation enhancement request in 5.10.4.3 (How to Repair Tables)

Bug #20226	Documentation enhancement request in 5.10.4.3 (How to Repair Tables)
Submitted:	2 Jun 2006 14:46	Modified:	18 Mar 2008 10:22
Reporter:	[ name withheld ]	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	all	OS:	Any (RedHat 9)
Assigned to:	Jon Stephens	CPU Architecture:	Any

Description:
In section 5.10.4.3 of the documentation:

http://dev.mysql.com/doc/refman/5.0/en/repair.html

Stage 3 outlines a process for a complex repair when serious corruption happens involving TRUNCATE. Please *please* note in that section (and anywhere else that is appropriate) that if the affected table in question is replicated that you must make a copy of the table on each server in the replication chain and repeat the process there or the TRUNCATE will break your table in all of the slaves. Or words to that effect.

Stage 3: Difficult repair

You should reach this stage only if the first 16KB block in the index file is destroyed or contains incorrect information, or if the index file is missing. In this case, it is necessary to create a new index file. Do so as follows:

1. Move the data file to a safe place.
2. Use the table description file to create new (empty) data and index files:

shell> mysql db_name
mysql> SET AUTOCOMMIT=1;
mysql> TRUNCATE TABLE tbl_name;
mysql> quit

3. Copy the old data file back onto the newly created data file. (Do not just move the old file back onto the new file. You want to retain a copy in case something goes wrong.)

Go back to Stage 2. myisamchk -r -q should work. (This should not be an endless loop.)

You can also use the REPAIR TABLE tbl_name USE_FRM SQL statement, which performs the whole procedure automatically. There is also no possibility of unwanted interaction between a utility and the server, because the server does all the work when you use REPAIR TABLE. See Section 13.5.2.6, “REPAIR TABLE Syntax”.

How to repeat:
Follow the steps in Stage 3 of 5.10.4.3 on a table that is replicated to another server.

Suggested fix:
See description.

This *might* be a documentation request, but not yet. A developer
should verify that the behavior for replication is as described and
determine whether we should document that, or whether it is a
bug to be fixed.

Surely is should be docuemtned. Just execute truncate and repair on any table. You'll get:

# at 187
#060627 16:35:39 server id 1  end_log_pos 268   Query   thread_id=3     exec_tim
e=0     error_code=0
SET TIMESTAMP=1151415339;
truncate table tst;
# at 268
#060627 16:35:50 server id 1  end_log_pos 355   Query   thread_id=3     exec_tim
e=0     error_code=0
SET TIMESTAMP=1151415350;
repair table tst use_frm;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

in the binary log, and both these statements will be replicated. While that step:

"Copy the old data file back onto the newly created data file." will not be replicated other than manually (and this is what exactly suggested).

Although, repair table tst use_frm; should be just a nice way to hande this, as slave side withh do everything automatically.

So, there should be notre that TRUNCATE is not safe with replication, and proposed workaround should be described, while REPAIR is more safe.

You're going to need to re-seed the slaves anyway in this situation, so it would probably be prudent to add documentation to that effect. Replication should be stopped manually in this situation, if it is not already broken because of corruption errors. After the master has an acceptable & functional table, that table should be manually propagated to the slaves and replication can be resumed. Care must be taken to apply queued statements directed towards *other* tables but not towards the newly repaired table.

I think TRUNCATE is not safe for replication, when executing TRUNCATE command on the master, an warning should be issued, but the statement will still be written into binlog, the warning message should tell the user that TRUNCATE command is not safe for replication and the slave will stop when executing it, the user has to do backup and execute it manually on the slave. 

When the slave's SQL thread encounters this statement, it will issue an error and stop, and tell the user to do backup and run this command manually before continue replication.

If the user really want to replicate TRUNCATE command, they should use alternatives such as DELETE or DROP & CREATE.

REFINED PROBLEM DESCRIPTION
---------------------------
The repair precedure contains steps that are not logged.  This makes
the log incorrect to use in replication or point-in-time recovery.

ALTERNATIVE SOLUTIONS
---------------------
1. Add checks for TRUNCATE on slave and warnings on master.
2. Carefully document in the repair procedure that one needs to turn
   logging off during repair, since there is a non-logged file
   restoration as part of the procedure.

DECISION
--------
Any user can issue a TRUNCATE statement.  No *user* should be able to
cause replication to stop for other users (the DBA "owns" control over
logging and replication, not the users).

Therefore, lets go with solution 2 and simply document that the DBA
needs to turn logging off while doing repair.

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.