Killing INSERT + SELECT Can Corrupt MySQL Table on MyISAM
The MySQL INSERT . SELECT command allows you to insert several rows into a table from various information. But at times, when you kill the command on MyISAM, you observe that the table gets corrupt. Such type of errors usually occur with large data tv sets. When you run CHECK TABLE command to determine the table for consistency, it reports how the table is unusable as it is corrupt. The table thus corrupted always be restored from backup or you can alternatively use MySQL Repair tools which have competent products enable safe repair of database and its objects.
To illustrate the same, consider which you 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 Measurements 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
The error indicates how the table has got corrupt. A great deal more kill the INSERT . SELECT statement, which is being applied on the table with indexes and contains large regarding data, when you are get corrupt. This occurs when we disable the indexes for fast insert. After the bulk_insert operation is completed, indexes are tried for you to become re-enabled. While MySQL is re-enabling the indexes, killing the query results into aborting the repair table operation so therefore the table gets dodgy.
Use a few of these methods to renovate the corrupted MyISAM table:
You make use of the following command to repair:
REPAIR TABLE table_name
Where, table_name is the url of corrupted table
Use MySQL Recovery tools for achieving best and safe repair results. MySQL Repair tools are fully-competent database recovery utilities offering powerful scanning technology and easy to use interface.
Stellar Phoenix Database Recovery For MySQL is the best and advanced MySQL Recovery utility. It supports both MySQL database storage engines - MyISAM and InnoDB and is competent absolutely repair and restore MySQL objects regarding tables, primary key(s) and relations. Individuals compatible with Windows Vista, XP, 2003 and 2003. It supports MySQL 5.x. It will also support Windows based recovery from MySQL database installed on Linux network.