Mariadb/MyISAM: Incompatible key or row definition between the MariaDB .frm file and the information in the storage engine. You have to dump an

Question

We have a db with around 150 tables in, of which about 30 or so are partitioned as such :

ALTER TABLE `myTable`
  REORGANIZE PARTITION PFUTURE INTO (PARTITION P201910 VALUES LESS THAN ('2019-11-01'),
             PARTITION PFUTURE VALUES LESS THAN MAXVALUE);

We've been automatically partitioning these tables on a monthly basis for around 3 years now without any issue. Recently on 1 of the tables, after a partitioning exercise (with no errors/warnings reported) we see this :

Got error 190 "Incompatible key or row definition between the MariaDB .frm file and the information in the storage engine. You have to dump an

from storage engine MyISAM" whilst running a show table status. I have restored from a backup (after stopping/starting mariadb) and re-issued the command, and the same error is reported.

Repair table fails with the same message.

Is there a process I can follow to un-corrupt the table and make the partitioning process complete?

We're using MariaDB 10.4.8. We are not in a position to use any other table format, MyISAM is what we have and what we have to stick with for now.

What I should mention is that we recently upgraded to MariaDB 10.4.8, and although I'd instantly jump on that as being the cause, the other 29 tables partitioned perfectly.

Answer 1

Try the following:

First check for all running queries and possible Connections your table might use. You can do this by

SHOW PROCESSLIST;

which gives you all running queries/Connections. You can terminate a query/Connection by

KILL <id>;

After that repair your table

REPAIR TABLE myTable;

Try to run this more than once until your Status Returns as ok. After that

OPTIMIZE TABLE myTable:

Then you can use the table again.