Bug #18692 myisampack cannot be executed on a live server
Submitted: 31 Mar 2006 15:50 Modified: 24 Feb 2007 19:37
Reporter: Ajit Deshpande Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: MyISAM storage engine Severity:S4 (Feature request)
Version:5.0.19, 5.0.74, 5.1.30 OS:Any (Linux (Centos 4.2))
Assigned to: CPU Architecture:Any

[31 Mar 2006 15:50] Ajit Deshpande
Description:
I ran myisampack on big table while the MySQL server was still running.

Following this, I tried to do a selet from the table and it complained that the table was marked as "corrupted".

I then tried the same on another table that I had packed and it gave the same problem.

I then tried "repair table" on the first table and that completed trashed the table and dropped all the rows in the table.

I then ran "show table status" and that was reporting the table as "Dynamic" instead of "Compressed".

So, I think what happened was that mysql did not realize that myisampack has been run from outside the server and that the contents have been corrupted.

Now, even though mysql thought that the second table was corrupted, on a hunch, I bounce mysqld. And then the 2nd table showed up correctly as "Compressed" after the mysql reboot and I was able to select from the table.

Ajit

How to repeat:
start mysqld

create an myisam table

insert some rows

run myisampack on the myisam table

select from the packed table

-- mysql will say that the table is corrupted

repair table packed_table

-- all rows will be deleted

Suggested fix:
So, I think we should do one of the following:

(1) Change myisampack so that it notifies mysqld that the table format has changed (update information_schema maybe?) 

OR

(2) Make a big WARNING in the myisampack documentation that mysqld should be bounced after myisampack. Or at the very least do not run "repair table" on a packed table that the mysqld cannot interpret.
[31 Mar 2006 16:02] MySQL Verification Team
Thank you for the bug report. Did you applied the below note
from the Manual:

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

 Please note the following:

    *

      If the mysqld server was invoked with external locking disabled, it is not a good idea to invoke myisampack if the table might be updated by the server during the packing process. It is safest to compress tables with the server stopped. 

Thanks in advance.
[19 Sep 2006 21:51] J Jorgenson
Please re-open this as a 'Feature' request:
Feature Request:
   When a table is explicitly Locked/Unlocked to check if externally modified?

It seems unreasonable to require 'external-locking' for high-availablility databases that need periodic myisamchk/myisampack operations.

My Testings (MyISAM tables):

Table corruption ALWAYS occurs when external-locking is disabled, AND the following sequences are used:
  FLUSH TABLE x;
  LOCK TABLE x READ (or WRITE, doesn't matter);
  myisamchk x;
  myisampack x;
  myisamchk -qra x;
  UNLOCK TABLES;
-- Table is corrupt, swapping sequence of lock/Flush doesn't resolve issue.

However, using the following technique the table IS NOT corrupt:
  ALTER TABLE x RENAME x_comp;
  FLUSH TABLE x_comp;
  myisamchk;
  myisampack;
  myisamchk;
  FLUSH TABLE x_comp;
  ALTER TABLE x_comp RENAME x;

The only different is Locking the table before compressing it.
[24 Feb 2007 19:37] MySQL Verification Team
Thank you for the bug report feature request.
[23 Dec 2008 8:42] MySQL Verification Team
it would be nice to to implement the entire myisampack procedure with some variation of ALTER TABLE, and make it safe to run on a live server.
[19 Jul 2011 10:54] MySQL Verification Team
see bug #41711