The MySQL INSERT ... SELECT command allows you
by:Modern Century
2021-03-17
To illustrate the same, consider that you use a table with name 'a' on MyISAM storage engine'. You execute INSERT ... SELECT command and kill it while it is running. After this, you use CHECK TABLE command to perform the sanity check of the table and come across errros similar to:
Table Op Msg_type Msg_text
test.a check warning Table is marked as crashed and last repair failed
test.a check warning Size of indexfile is: 1337344 Should be: 1024
test.a check error Record-count is not ok; is 92522 Should be: 0
test.a check warning Found 92522 key parts. Should be: 0
test.a check error Partition pMin returned error
test.a check error Corrupt
Cause
The error indicates that the table has got corrupt. When you kill the INSERT ... SELECT statement, which is being applied on the table with indexes and contains large amount of data, it might get corrupt. This occurs when we disable the indexes for the purpose of fast insert. After the bulk_insert operation is completed, indexes are tried to be re-enabled. While MySQL is re-enabling the indexes, killing the query results into aborting the repair table operation and hence the table gets corrupt.
Solution
Use any of these methods to repair the corrupted MyISAM table:
You can use the following command to repair:
REPAIR TABLE table_name
Where, table_name is the name of corrupted table
Use MySQL Recovery tools for achieving best and safe repair results. MySQL Repair tools are fully-competent database recovery utilities built with powerful scanning technology and easy to use interface.
Stellar Phoenix Database Recovery For MySQL is the most powerful and advanced MySQL Recovery utility. It supports both MySQL database storage engines - MyISAM and InnoDB and is competent to completely repair and restore MySQL objects such as tables, primary key(s) and relations. It is compatible with Windows Vista, XP, 2003 and 2000. It supports MySQL 5.x. It also supports Windows based recovery from MySQL database installed on Linux platform.
Custom message