0

I have a large MyISAM table which has crashed. Repairing the table will take some time. The table only INSERTed to and SELECTed from, never updated. To allow the application to continue working, albeit with reduced capability, I thought of

  • renaming the crashed table
  • creating a new table with the original name
  • switching processing back on
  • repairing the backup table
  • switching off processing
  • merging the repaired and new data
  • switching on processing

The other steps in this process do not pose any risk due to the nature of the application. Is it safe to rename a crashed MyISAM table? How?

I believe that I can't simply do ALTER TABLE...RENAME.... as this always does a row-by-row copy into a new table.

Apparently Peter Zaitsev uses a "tiny script which moves out all MyISAM tables out of MySQL database directory" but doesn't seem to give details of what this script does (presumably stops database first?).

  • 1
    Very carefully, with the server daemon stopped, renaming the table1.frm, table1.MYD, and table1.MYI files in the database directory to table2.frm, table2.MYD, and table2.MYI should accomplish what you want, for MyISAM only. Be sure the new files have precisely the same upper/lower-case. MyISAM has no dictionary, so it just discovers the table names by examining the filenames. If the table has triggers, there will be .TRN files but I'm not at all sure whether those are safe to rename in the same way. – Michael - sqlbot Sep 8 '16 at 12:29
  • Let this be a warning that you should move from MyISAM to InnoDB. The latter repairs its own tables. – Rick James Sep 9 '16 at 22:56
  • I would have a great deal of problems all the time if this application were running on innodb. Innodb may be more robust but has a big performance cost; if money and my time were not limited I would migrate to tokudb on sssds, but thats not going to happen any time soon. – symcbean Sep 11 '16 at 13:44
  • Hmmm... There are performance differences, but usually they can be overcome by changing indexes, tuning, etc. – Rick James Sep 11 '16 at 14:22
0

This might work; do it with caution:

CREATE TABLE new LIKE real;   -- copy schema
RENAME TABLE real TO bad, new TO real;  -- replace broken table with empty
REPAIR TABLE bad;     -- slowly repair
RENAME TABLE real TO old, bad TO real;  -- replace empty table with fixed table
DROP TABLE old;   -- clean up

I don't know if the CREATE will work with a broken real.

The RENAMEs are 'instantaneous'.

Then schedule moving to InnoDB.

  • Unfortunately it does not work. I did have the create table statement from a backup but as per my post above, the rename relies on carrying out a row by row copy of the original table data which would have failed. Time pressures meant I decided to repair the table in place rather than risk further problems. – symcbean Sep 11 '16 at 13:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.