Is the table constantly collapsing?

Question

MySQL

Hello.
Faced a recurring and very annoying problem.
There is a table. Data is being inserted into the table. Before "a week ago" everything worked for a year without failures.
Since "a week ago" I get the following error: " Unable to save result set " on SELECT query and broken table:

TABLE IS marked AS crashed;
2 clients are USING OR haven't closed the table properly;
Wrong record length 1024 of 1027 at 8509040
Corrupt

What is the cause of such trouble? It worked like clockwork.

Answer 1

If it used to work, but now it breaks all the time, I would look first of all at the disk: it either fell down, or (for example) the space in the partition ran out. Look at dmesg, the disk subsystem tells a lot of interesting things there.

Answer 2

There are many options here, at least describe how you backup your database. (if a simple copy / paste then problems may arise)
It would also not be bad to describe the database server (what software is the usual mysql or percona?) It would also be very good to know how the load on it has increased.

Answer 3

What can I say, I had a similar situation, the reason was mysql assembly. It got better after the update.
But in this case, there may still be a problem with the disk system. Better check it for integrity.
Also, in principle, if you completely clean the table with the “DELETE FROM table_name” command, then the more optimal way would be to use “TRUNCATE TABLE table_name”.

Answer 4

As for REPAIR, I don’t seem to know what MyISAM or InnoDB table you have ... It’s just that REPAIR won’t help you with InnoDB (If InnoDB is better, read the documentation.
Also, if TRIGGERS are attached to the table, then most likely the problem maybe not with the table on which errors fall, but with the table in which this trigger makes changes.