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 tables. 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 sets. When you run CHECK TABLE command to check the table for consistency, it reports that the table is unusable as it is corrupt. The table thus corrupted should be restored from backup or you can alternatively use MySQL Repair tools that are competent products to allow safe repair of database and its objects.
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 Repair 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.
Article Source :- http://www.goarticles.com/cgi-bin/showa.cgi?C=1909241
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 Repair 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.
Article Source :- http://www.goarticles.com/cgi-bin/showa.cgi?C=1909241
Comments
Post a Comment