0

So I have to maintain a legacy system that has millions of rows in a MySQL myisam table.

Once in a blue moon, the UPS or the power supply craps out, and crashes the server and hence the tables will be autoatically repaired at next use. This can take an hour or more.

Is there a way to transparently move historical data from the table to another maybe read-only table which will not be marked as crashed when things go wrong? If the REPAIR would only have to deal with e.g. last year's data, it would be way quicker.

My problem i that the SELECTs would stil have to be able to take into account all data from all times (like transparent a UNION or "view").

Improve this question
3
  • 1
    What you're describing is called "table partitioning", so it might help to look it up. Since you're willing to modify the database schema, have you considered converting the table to the InnoDB engine to minimize the effect of crashes?
    – mustaccio
    Oct 6, 2021 at 20:47
  • Would table partitioning only "crash" the most recent partition? I've read somewhere else that MySQL detects crashes by simply counting and subtrating opened and closed handles. If there's a mismatch, it wasn't closed properly and hence need to be checked, which sound rather simplistic.
    – Moritz von Schweinitz
    Oct 7, 2021 at 3:20
  • @MoritzvonSchweinitz - I think MyISAM does a simple-minded thing -- mark the index file (the .MYI) as crash as soon as you open the table. If MySQL terminates gracefully, it flushes the index stuff to the .MYI and turns of the "crashed" flag.
    – Rick James
    Oct 7, 2021 at 3:54

1 Answer 1

Reset to default
2

Simple:

 ALTER TABLE t ENGINE=InnoDB;

That will make it so it does not need to be "Repaired" after crashes.

Caveat: The disk space might be 3 times as large.

Meanwhile, is the "crashed and needs repair" stopping you from doing anything? The need for repair in MyISAM usually implies that the INDEX is corrupted. But if you are never modifying the table, the index(es) will never actually be corrupt. That is, it may be a "false alarm".

However, that would require you to get rid of the "automatic repair", which is probably established in my.cnf. And then manually do REPAIR TABLE on any MyISAM tables that are not read only.

(I see not advantage for PARTITION in this situation; I think the REPAIR will go through all the partitions.)

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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