Repair Table =============== Explore corruption on belle7. .. contents:: :local: References ----------- * :google:`mysql repair corruption` * http://dev.mysql.com/doc/refman/5.0/en/repair-table.html * http://dev.mysql.com/doc/refman/5.1/en/repair-table.html MySQL Versions and `USE_FRM` option -------------------------------------- :: dybdb1.ihep.ac.cn 5.0.45-community-log MySQL Community Edition (GPL) belle7.nuu.edu.tw 5.0.77-log Source distribution cms01.phys.ntu.edu.tw 4.1.22-log From http://dev.mysql.com/doc/refman/5.0/en/repair-table.html As of MySQL 5.0.62, if you use `USE_FRM` for a table that was created by a different version of the MySQL server than the one you are currently running, REPAIR TABLE will not attempt to repair the table. In this case, the result set returned by REPAIR TABLE contains a line with a `Msg_type` value of `error` and a `Msg_text` value of `Failed repairing incompatible .FRM file`. * so on belle7 I cannot use `USE_FRM` to repair the table from dybdb1 Prior to MySQL 5.0.62, do not use `USE_FRM` if your table was created by a different version of the MySQL server. Doing so risks the loss of all rows in the table. It is particularly dangerous to use `USE_FRM` after the server returns this message: * the tables appear have been created `2013-02-04` so seems no issue with version differences for dybdb1 repair using `USE_FRM` :: mysql> select table_name, table_type, engine, version, table_rows, data_length, max_data_length, index_length, data_free, create_time, update_time, check_time from information_schema.tables where table_schema = 'tmp_ligs_offline_db' ; +-----------------------+------------+-----------+---------+------------+-------------+-------------------+--------------+-----------+---------------------+---------------------+---------------------+ | table_name | table_type | engine | version | table_rows | data_length | max_data_length | index_length | data_free | create_time | update_time | check_time | +-----------------------+------------+-----------+---------+------------+-------------+-------------------+--------------+-----------+---------------------+---------------------+---------------------+ | ChannelQuality | BASE TABLE | MyISAM | 10 | 1745856 | 24441984 | 3940649673949183 | 25170944 | 0 | 2013-04-22 12:50:10 | 2013-04-22 23:32:27 | NULL | | ChannelQualityVld | BASE TABLE | MyISAM | 10 | 9093 | 463743 | 14355223812243455 | 96256 | 0 | 2013-04-22 12:50:10 | 2013-04-22 23:32:27 | NULL | | DaqRawDataFileInfo | BASE TABLE | FEDERATED | 10 | 310821 | 70867188 | 0 | 0 | 0 | NULL | 1970-01-01 08:33:33 | NULL | | DaqRawDataFileInfoVld | BASE TABLE | FEDERATED | 10 | 310821 | 13986945 | 0 | 0 | 0 | NULL | 1970-01-01 08:33:33 | NULL | | DqChannel | BASE TABLE | MyISAM | 10 | 65489088 | 2750541696 | 11821949021847551 | 1015181312 | 0 | 2013-02-04 16:07:51 | 2013-05-20 06:26:54 | NULL | | DqChannelStatus | BASE TABLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | | DqChannelStatusVld | BASE TABLE | MyISAM | 10 | 341125 | 17397375 | 14355223812243455 | 3826688 | 0 | 2013-02-04 16:07:56 | 2013-05-20 06:26:55 | 2013-05-13 13:16:02 | | DqChannelVld | BASE TABLE | MyISAM | 10 | 341089 | 17395539 | 14355223812243455 | 3606528 | 0 | 2013-02-04 16:07:51 | 2013-05-20 06:26:54 | NULL | | LOCALSEQNO | BASE TABLE | MyISAM | 10 | 4 | 276 | 19421773393035263 | 2048 | 0 | 2013-02-04 16:09:33 | 2013-05-20 06:26:54 | NULL | +-----------------------+------------+-----------+---------+------------+-------------+-------------------+--------------+-----------+---------------------+---------------------+---------------------+ 9 rows in set (0.09 sec) Repairs and replication ------------------------ From http://dev.mysql.com/doc/refman/5.0/en/repair-table.html By default, the server writes `REPAIR TABLE` statements to the binary log so that they replicate to replication slaves. To suppress logging, specify the optional `NO_WRITE_TO_BINLOG` keyword or its alias `LOCAL`. * this DB is skipped from replication, so presumably no problem BUT should perhaps use `REPAIR LOCAL TABLE DqChannelStatus` MyISAM repairs ------------------ From http://dev.mysql.com/doc/refman/5.0/en/myisam-repair.html :: [root@belle7 tmp_offline_db_ext]# man myisamchk [root@belle7 tmp_offline_db_ext]# myisamchk -vvv *.MYI Warning: option 'key_buffer_size': unsigned value 18446744073709551615 adjusted to 4294963200 Warning: option 'read_buffer_size': unsigned value 18446744073709551615 adjusted to 4294967295 Warning: option 'write_buffer_size': unsigned value 18446744073709551615 adjusted to 4294967295 Warning: option 'sort_buffer_size': unsigned value 18446744073709551615 adjusted to 4294967295 Checking MyISAM file: DqChannelPacked.MYI Data records: 323000 Deleted blocks: 0 - check file-size - check record delete-chain No recordlinks - check key delete-chain block_size 1024: - check index reference - check data record references index: 1 --------- Checking MyISAM file: DqChannelPackedVld.MYI Data records: 323000 Deleted blocks: 0 - check file-size - check record delete-chain No recordlinks - check key delete-chain block_size 1024: - check index reference - check data record references index: 1 [root@belle7 tmp_offline_db_ext]# Following sections `Stage 3: Difficult repair` and `Stage 2: Easy safe repair` -------------------------------------------------------------------------------- * http://dev.mysql.com/doc/refman/5.0/en/myisam-repair.html move MYI and MYD into keep ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Simulate a missing MYI:: [root@belle7 tmp_offline_db_ext]# mkdir ../tmp_offline_db_ext_keep [root@belle7 tmp_offline_db_ext]# mv DqChannelPacked.MYI DqChannelPacked.MYD ../tmp_offline_db_ext_keep [root@belle7 tmp_offline_db_ext]# ll ../tmp_offline_db_ext_keep total 19072 -rw-rw---- 1 mysql mysql 14858000 May 21 13:43 DqChannelPacked.MYD -rw-rw---- 1 mysql mysql 4621312 May 21 13:46 DqChannelPacked.MYI drwxr-xr-x 41 mysql mysql 4096 May 22 18:56 .. drwxr-xr-x 2 root root 4096 May 22 18:57 . truncate the moved table, recreating a 1024 byte MYI and empty MYD ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ :: mysql> show tables ; +------------------------------+ | Tables_in_tmp_offline_db_ext | +------------------------------+ | DqChannelPacked | | DqChannelPackedVld | +------------------------------+ 2 rows in set (0.00 sec) mysql> SET autocommit=1; Query OK, 0 rows affected (0.00 sec) mysql> truncate table DqChannelPacked ; Query OK, 0 rows affected (0.02 sec) mysql> quit Bye :: [root@belle7 tmp_offline_db_ext]# ll total 19392 -rw-rw---- 1 mysql mysql 8908 May 10 18:18 DqChannelPackedVld.frm -rw-rw---- 1 mysql mysql 8896 May 10 18:18 DqChannelPacked.frm -rw-rw---- 1 mysql mysql 16473000 May 11 20:18 DqChannelPackedVld.MYD -rw-rw---- 1 mysql mysql 3314688 May 14 15:04 DqChannelPackedVld.MYI drwxr-xr-x 41 mysql mysql 4096 May 22 18:56 .. -rw-rw---- 1 mysql mysql 1024 May 22 18:59 DqChannelPacked.MYI -rw-rw---- 1 mysql mysql 0 May 22 18:59 DqChannelPacked.MYD drwxr-x--- 2 mysql mysql 4096 May 22 18:59 . Copy the MYD back from keep ontop of the empty MYD ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Copy the old data file back onto the newly created data file. (Do not just move the old file back onto the new file. You want to retain a copy in case something goes wrong.) :: [root@belle7 tmp_offline_db_ext]# cp ../tmp_offline_db_ext_keep/DqChannelPacked.MYD . cp: overwrite `./DqChannelPacked.MYD'? y [root@belle7 tmp_offline_db_ext]# ll total 33924 -rw-rw---- 1 mysql mysql 8908 May 10 18:18 DqChannelPackedVld.frm -rw-rw---- 1 mysql mysql 8896 May 10 18:18 DqChannelPacked.frm -rw-rw---- 1 mysql mysql 16473000 May 11 20:18 DqChannelPackedVld.MYD -rw-rw---- 1 mysql mysql 3314688 May 14 15:04 DqChannelPackedVld.MYI drwxr-xr-x 41 mysql mysql 4096 May 22 18:56 .. -rw-rw---- 1 mysql mysql 1024 May 22 18:59 DqChannelPacked.MYI drwxr-x--- 2 mysql mysql 4096 May 22 18:59 . -rw-rw---- 1 mysql mysql 14858000 May 22 19:06 DqChannelPacked.MYD The result so far is a drastically shrunk MYI. Repopulate the index with `myisamchk -r -q` ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ From `Stage 2: Easy safe repair` of http://dev.mysql.com/doc/refman/5.0/en/myisam-repair.html This attempts to repair the index file without touching the data file. If the data file contains everything that it should and the delete links point at the correct locations within the data file, this should work, and the table is fixed. :: [root@belle7 tmp_offline_db_ext]# myisamchk -r -q DqChannelPacked Warning: option 'key_buffer_size': unsigned value 18446744073709551615 adjusted to 4294963200 Warning: option 'read_buffer_size': unsigned value 18446744073709551615 adjusted to 4294967295 Warning: option 'write_buffer_size': unsigned value 18446744073709551615 adjusted to 4294967295 Warning: option 'sort_buffer_size': unsigned value 18446744073709551615 adjusted to 4294967295 - check record delete-chain - recovering (with sort) MyISAM-table 'DqChannelPacked' Data records: 0 - Fixing index 1 Data records: 323000 [root@belle7 tmp_offline_db_ext]# Those warnings are a know bug http://bugs.mysql.com/bug.php?id=33785 reported for 5.0.54 fixed in 5.0 series at 5.0.87 The values adjusted to are 4G:: In [86]: ( 1 << 32 ) - 1 Out[86]: 4294967295L In [21]: (4294967295+1)/1024/1024/1024 Out[21]: 4L From `Stage 2: Easy safe repair` If you want a repair operation to go much faster, you should set the values of the `sort_buffer_size` and `key_buffer_size` variables each to about 25% of your available memory when running myisamchk. Unfortunately I do not have 16G of memory, so potentially a repair will run out of memory with these settings. After that succeed to create MYI of precisely the prior size, but different content:: [root@belle7 tmp_offline_db_ext]# ll DqChannelPacked.MYI ../tmp_offline_db_ext_keep/DqChannelPacked.MYI -rw-rw---- 1 mysql mysql 4621312 May 21 13:46 ../tmp_offline_db_ext_keep/DqChannelPacked.MYI -rw-rw---- 1 mysql mysql 4621312 May 22 19:08 DqChannelPacked.MYI [root@belle7 tmp_offline_db_ext]# [root@belle7 tmp_offline_db_ext]# diff -b DqChannelPacked.MYI ../tmp_offline_db_ext_keep/DqChannelPacked.MYI Binary files DqChannelPacked.MYI and ../tmp_offline_db_ext_keep/DqChannelPacked.MYI differ [root@belle7 tmp_offline_db_ext]# Hexdump comparison:: [root@belle7 tmp_offline_db_ext]# xxd -c 64 DqChannelPacked.MYI > /tmp/s/DqChannelPacked_MYI_recreated.xxd [root@belle7 tmp_offline_db_ext]# xxd -c 64 ../tmp_offline_db_ext_keep/DqChannelPacked.MYI > /tmp/s/DqChannelPacked_MYI_original.xxd Shows differences only within first 4 lines of the dump (256 bytes of the MYI). Just header differences perhaps:: [root@belle7 tmp_offline_db_ext]# diff /tmp/s/DqChannelPacked_MYI_original.xxd /tmp/s/DqChannelPacked_MYI_recreated.xxd 1,4c1,4 < 0000000: fefe 0701 0000 01b0 00b0 0064 00c8 0002 0000 0100 0801 0000 0000 20ff 0000 0000 0004 edb8 0000 0000 0000 0000 0000 0000 0004 edb8 ffff ffff ffff ffff 0000 0000 ...........d.............. ..................................... < 0000040: 0046 8400 0000 0000 00e2 b710 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 ef79 cdce 0000 615b 0000 0011 0000 0000 0000 0001 0000 0000 .F.......................................y....a[................ < 0000080: 0046 8000 ffff ffff ffff ffff 0000 0000 0000 0000 519b 0976 0000 0000 0000 0001 0000 0000 519b 0975 0000 0000 0000 0000 0000 0000 519b 0a20 0000 0000 0004 edb8 .F..................Q..v............Q..u............Q.. ........ < 00000c0: 0000 0001 0000 0001 0000 0000 0000 0400 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 002e 0000 002e 0000 002e ................................................................ --- > 0000000: fefe 0701 0000 01b0 00b0 0064 00c8 0002 0000 0100 0801 0000 0000 28ff 0000 0000 0004 edb8 0000 0000 0000 0000 0000 0000 0004 edb8 ffff ffff ffff ffff 0000 0000 ...........d..............(..................................... > 0000040: 0046 8400 0000 0000 00e2 b710 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 1a2b 0000 0004 0000 0000 0000 0001 0000 0000 .F.............................................+................ > 0000080: 0046 8000 ffff ffff ffff ffff 0000 0000 0000 0000 519c a52f 0000 0000 0000 0001 0000 0000 519c a52f 0000 0000 0000 0000 0000 0000 519c a74b 0000 0000 0004 edb8 .F..................Q../............Q../............Q..K........ > 00000c0: 0000 0000 0000 0000 0000 0000 0000 0400 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 0000 002e 0000 002e 0000 002e ................................................................ Check the table survived this trauma ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Seems so:: mysql> show tables ; +------------------------------+ | Tables_in_tmp_offline_db_ext | +------------------------------+ | DqChannelPacked | | DqChannelPackedVld | +------------------------------+ 2 rows in set (0.00 sec) mysql> select count(*) from DqChannelPacked ; +----------+ | count(*) | +----------+ | 323000 | +----------+ 1 row in set (0.00 sec) mysql> select * from DqChannelPacked where SEQNO=101010 ; +--------+-------------+-------+--------+------------+------------+------------+------------+------------+------------+-------+ | SEQNO | ROW_COUNTER | RUNNO | FILENO | MASK0 | MASK1 | MASK2 | MASK3 | MASK4 | MASK5 | MASK6 | +--------+-------------+-------+--------+------------+------------+------------+------------+------------+------------+-------+ | 101010 | 1 | 21520 | 245 | 2147483647 | 2147483647 | 2147483647 | 2147483647 | 2147483647 | 2147483647 | 63 | +--------+-------------+-------+--------+------------+------------+------------+------------+------------+------------+-------+ 1 row in set (0.00 sec) myisamcheck memory ------------------- * http://dev.mysql.com/doc/refman/5.0/en/myisamchk-memory.html On N have 4G of memory, so need to restrict to 1G:: [blyth@belle7 ~]$ free -m total used free shared buffers cached Mem: 4052 1639 2412 0 576 684 -/+ buffers/cache: 378 3673 Swap: 1983 0 1983 :: myisamchk --sort_buffer_size=256M --key_buffer_size=512M --read_buffer_size=64M --write_buffer_size=64M # suggestion for 512MB available myisamchk --sort_buffer_size=512M --key_buffer_size=1024M --read_buffer_size=128M --write_buffer_size=128M # suggestion for 512MB available doubled No speed difference, but maybe as nothing to fix:: [root@belle7 tmp_offline_db_ext]# time myisamchk --sort_buffer_size=512M --key_buffer_size=1024M --read_buffer_size=128M --write_buffer_size=128M -r -q DqChannelPacked Warning: option 'key_buffer_size': unsigned value 18446744073709551615 adjusted to 4294963200 Warning: option 'read_buffer_size': unsigned value 18446744073709551615 adjusted to 4294967295 Warning: option 'write_buffer_size': unsigned value 18446744073709551615 adjusted to 4294967295 Warning: option 'sort_buffer_size': unsigned value 18446744073709551615 adjusted to 4294967295 - check record delete-chain - recovering (with sort) MyISAM-table 'DqChannelPacked' Data records: 323000 - Fixing index 1 real 0m0.291s user 0m0.235s sys 0m0.050s [root@belle7 tmp_offline_db_ext]# time myisamchk -r -q DqChannelPacked Warning: option 'key_buffer_size': unsigned value 18446744073709551615 adjusted to 4294963200 Warning: option 'read_buffer_size': unsigned value 18446744073709551615 adjusted to 4294967295 Warning: option 'write_buffer_size': unsigned value 18446744073709551615 adjusted to 4294967295 Warning: option 'sort_buffer_size': unsigned value 18446744073709551615 adjusted to 4294967295 - check record delete-chain - recovering (with sort) MyISAM-table 'DqChannelPacked' Data records: 323000 - Fixing index 1 real 0m0.290s user 0m0.242s sys 0m0.048s Help variable dumping suggests it gets the message despite the warnings:: [root@belle7 tmp_offline_db_ext]# myisamchk --help | grep size Warning: option 'key_buffer_size': unsigned value 18446744073709551615 adjusted to 4294963200 Warning: option 'read_buffer_size': unsigned value 18446744073709551615 adjusted to 4294967295 Warning: option 'write_buffer_size': unsigned value 18446744073709551615 adjusted to 4294967295 Warning: option 'sort_buffer_size': unsigned value 18446744073709551615 adjusted to 4294967295 key_buffer_size 520192 key_cache_block_size 1024 myisam_block_size 1024 read_buffer_size 262136 write_buffer_size 262136 sort_buffer_size 2097144 [root@belle7 tmp_offline_db_ext]# [root@belle7 tmp_offline_db_ext]# [root@belle7 tmp_offline_db_ext]# myisamchk --sort_buffer_size=512M --key_buffer_size=1024M --read_buffer_size=128M --write_buffer_size=128M | grep size Warning: option 'key_buffer_size': unsigned value 18446744073709551615 adjusted to 4294963200 Warning: option 'read_buffer_size': unsigned value 18446744073709551615 adjusted to 4294967295 Warning: option 'write_buffer_size': unsigned value 18446744073709551615 adjusted to 4294967295 Warning: option 'sort_buffer_size': unsigned value 18446744073709551615 adjusted to 4294967295 key_buffer_size 1073741824 key_cache_block_size 1024 myisam_block_size 1024 read_buffer_size 134217728 write_buffer_size 134217728 sort_buffer_size 536870912 [root@belle7 tmp_offline_db_ext]# Create a throwaway DB ----------------------- :: mysqlhotcopy.py --regex "^DqChannelPacked" -l debug --ALLOWEXTRACT --flattop -C --rename tmp_offline_db_ext tmp_offline_db coldcopy archive examine extract Verify accessible before being detructive ------------------------------------------ :: mysql> use tmp_offline_db_ext Database changed mysql> show tables ; +------------------------------+ | Tables_in_tmp_offline_db_ext | +------------------------------+ | DqChannelPacked | | DqChannelPackedVld | +------------------------------+ 2 rows in set (0.00 sec) mysql> select count(*) from DqChannelPacked ; +----------+ | count(*) | +----------+ | 323000 | +----------+ 1 row in set (0.00 sec) mysql> select count(*) from DqChannelPackedVld ; +----------+ | count(*) | +----------+ | 323000 | +----------+ 1 row in set (0.00 sec) mysql> select * from DqChannelPackedVld order by SEQNO desc limit 5 ; +--------+---------------------+---------------------+----------+---------+---------+------+-------------+---------------------+---------------------+ | SEQNO | TIMESTART | TIMEEND | SITEMASK | SIMMASK | SUBSITE | TASK | AGGREGATENO | VERSIONDATE | INSERTDATE | +--------+---------------------+---------------------+----------+---------+---------+------+-------------+---------------------+---------------------+ | 323000 | 2013-04-27 23:07:43 | 2013-04-27 23:29:31 | 4 | 1 | 2 | 0 | -1 | 2013-04-27 23:07:43 | 2013-05-11 12:18:46 | | 322999 | 2013-04-27 23:07:43 | 2013-04-27 23:29:31 | 4 | 1 | 4 | 0 | -1 | 2013-04-27 23:07:43 | 2013-05-11 12:18:45 | | 322998 | 2013-04-27 23:44:38 | 2013-04-27 23:54:30 | 1 | 1 | 1 | 0 | -1 | 2013-04-27 23:44:38 | 2013-05-11 12:18:45 | | 322997 | 2013-04-27 23:44:38 | 2013-04-27 23:54:30 | 1 | 1 | 2 | 0 | -1 | 2013-04-27 23:44:38 | 2013-05-11 12:18:44 | | 322996 | 2013-04-28 00:10:09 | 2013-04-28 00:22:35 | 2 | 1 | 1 | 0 | -1 | 2013-04-28 00:10:09 | 2013-05-11 12:18:44 | +--------+---------------------+---------------------+----------+---------+---------+------+-------------+---------------------+---------------------+ 5 rows in set (0.00 sec) mysql> select * from DqChannelPacked order by SEQNO desc limit 5 ; +--------+-------------+-------+--------+------------+------------+------------+------------+------------+------------+-------+ | SEQNO | ROW_COUNTER | RUNNO | FILENO | MASK0 | MASK1 | MASK2 | MASK3 | MASK4 | MASK5 | MASK6 | +--------+-------------+-------+--------+------------+------------+------------+------------+------------+------------+-------+ | 323000 | 1 | 38878 | 115 | 2147483647 | 2147483647 | 2147483647 | 2147483647 | 2147483647 | 2147483647 | 63 | | 322999 | 1 | 38878 | 115 | 2147483647 | 2147483647 | 2139095039 | 2147483647 | 2147483647 | 2147483647 | 63 | | 322998 | 1 | 38886 | 229 | 2147483647 | 2147483647 | 2147483647 | 2147483647 | 2147483647 | 2147483647 | 63 | | 322997 | 1 | 38886 | 229 | 2147483647 | 2147483647 | 2147483647 | 2147483647 | 2147483647 | 2147483647 | 63 | | 322996 | 1 | 38860 | 198 | 2147483647 | 2147483647 | 2147483647 | 2147483647 | 2147483647 | 2147483647 | 63 | +--------+-------------+-------+--------+------------+------------+------------+------------+------------+------------+-------+ 5 rows in set (0.00 sec) Be destructive, delete the MYI index file for a table ------------------------------------------------------ :: [root@belle7 tmp_offline_db_ext]# pwd /var/lib/mysql/tmp_offline_db_ext [root@belle7 tmp_offline_db_ext]# ll total 38484 -rw-rw---- 1 mysql mysql 8908 May 10 18:18 DqChannelPackedVld.frm -rw-rw---- 1 mysql mysql 8896 May 10 18:18 DqChannelPacked.frm -rw-rw---- 1 mysql mysql 16473000 May 11 20:18 DqChannelPackedVld.MYD -rw-rw---- 1 mysql mysql 14858000 May 11 20:18 DqChannelPacked.MYD -rw-rw---- 1 mysql mysql 4658176 May 13 13:08 DqChannelPacked.MYI -rw-rw---- 1 mysql mysql 3314688 May 14 15:04 DqChannelPackedVld.MYI drwxr-x--- 2 mysql mysql 4096 May 16 17:11 . drwxr-xr-x 40 mysql mysql 4096 May 20 19:54 .. [root@belle7 tmp_offline_db_ext]# rm DqChannelPacked.MYI rm: remove regular file `DqChannelPacked.MYI'? y [root@belle7 tmp_offline_db_ext]# Repairing the damage --------------------- * :google:`repair mysql corruption` * http://www.databasejournal.com/features/mysql/article.php/10897_3300511_2/Repairing-Database-Corruption-in-MySQL.htm Appears to work OK for a while (memory cache ?) then after flushing:: mysql> flush tables ; Query OK, 0 rows affected (0.02 sec) mysql> select count(*) from DqChannelPacked ; ERROR 1017 (HY000): Can't find file: 'DqChannelPacked' (errno: 2) Check table repeats that error and repair table fails to clear it:: mysql> check table DqChannelPacked ; +------------------------------------+-------+----------+-----------------------------------------------+ | Table | Op | Msg_type | Msg_text | +------------------------------------+-------+----------+-----------------------------------------------+ | tmp_offline_db_ext.DqChannelPacked | check | Error | Can't find file: 'DqChannelPacked' (errno: 2) | | tmp_offline_db_ext.DqChannelPacked | check | error | Corrupt | +------------------------------------+-------+----------+-----------------------------------------------+ 2 rows in set (0.00 sec) mysql> REPAIR TABLE DqChannelPacked ; +------------------------------------+--------+----------+-----------------------------------------------+ | Table | Op | Msg_type | Msg_text | +------------------------------------+--------+----------+-----------------------------------------------+ | tmp_offline_db_ext.DqChannelPacked | repair | Error | Can't find file: 'DqChannelPacked' (errno: 2) | | tmp_offline_db_ext.DqChannelPacked | repair | error | Corrupt | +------------------------------------+--------+----------+-----------------------------------------------+ 2 rows in set (0.00 sec) mysql> mysql> check table DqChannelPacked ; +------------------------------------+-------+----------+-----------------------------------------------+ | Table | Op | Msg_type | Msg_text | +------------------------------------+-------+----------+-----------------------------------------------+ | tmp_offline_db_ext.DqChannelPacked | check | Error | Can't find file: 'DqChannelPacked' (errno: 2) | | tmp_offline_db_ext.DqChannelPacked | check | error | Corrupt | +------------------------------------+-------+----------+-----------------------------------------------+ 2 rows in set (0.00 sec) With the `USE_FRM` succeed to repair the table, which recreated the MYI index that I deleted. Ordinarily `USE_FRM` is not advised unless the other repair techniques fail, see http://dev.mysql.com/doc/refman/5.0/en/repair-table.html :: mysql> REPAIR TABLE DqChannelPacked USE_FRM ; +------------------------------------+--------+----------+-----------------------------------------+ | Table | Op | Msg_type | Msg_text | +------------------------------------+--------+----------+-----------------------------------------+ | tmp_offline_db_ext.DqChannelPacked | repair | warning | Number of rows changed from 0 to 323000 | | tmp_offline_db_ext.DqChannelPacked | repair | status | OK | +------------------------------------+--------+----------+-----------------------------------------+ 2 rows in set (0.42 sec) mysql> check table DqChannelPacked ; +------------------------------------+-------+----------+----------+ | Table | Op | Msg_type | Msg_text | +------------------------------------+-------+----------+----------+ | tmp_offline_db_ext.DqChannelPacked | check | status | OK | +------------------------------------+-------+----------+----------+ 1 row in set (0.14 sec)