On Mon, 10 Dec 2001, Michael Widenius wrote:

I meant that it wasn't crashed because it got compressed while it was in the cache of mysql. I ran CHECK TABLE on all tables before starting the script to dump all tables. The table was compressed and usable.

mysql> check table blast_self_data; +------------------------------+-------+----------+-----------------------------------------------------------+ | Table | Op | Msg_type | Msg_text +------------------------------+-------+----------+-----------------------------------------------------------+ | Rsphaeroides.blast_self_data | check | warning | Table is marked as crashed
and last repair failed | | Rsphaeroides.blast_self_data | check | error | Size of datafile is:
17198194 Should be: 29024668 | | Rsphaeroides.blast_self_data | check | error | Corrupt | +------------------------------+-------+----------+-----------------------------------------------------------+ 3 rows in set (13.56 sec)

mysql> flush tables; Query OK, 0 rows affected (1.35 sec)

mysql> quit

mysql@kulan$ cp /home/mysql/Rsphaeroides/blast_self_data.MYD-
/home/mysql/Rsphaeroides/blast_self_data.MYD

kulan$ mysql Drosophila_melanogaster Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1415 to server version: 3.23.44-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> flush tables; Query OK, 0 rows affected (0.00 sec)

mysql> use Rsphaeroides; Database changed mysql> repair table blast_self_data; [...] | Rsphaeroides.blast_self_data | repair | info | Found block that points
outside data file at 17196572 | | Rsphaeroides.blast_self_data | repair | info | Found block that points
outside data file at 17196676 | | Rsphaeroides.blast_self_data | repair | info | Found block that points
outside data file at 17196828 | | Rsphaeroides.blast_self_data | repair | info | Found block that points
outside data file at 17197400 | | Rsphaeroides.blast_self_data | repair | info | Found block that points
outside data file at 17197544 | | Rsphaeroides.blast_self_data | repair | info | Found block with too small
length at 17197588; Skipped | | Rsphaeroides.blast_self_data | repair | status | OK
| +------------------------------+--------+----------+--------------------------------------------------------------------------------+ 28262 rows in set (11 min 37.29 sec)

mysql> check table blast_self_data; +------------------------------+-------+----------+----------+ | Table | Op | Msg_type | Msg_text | +------------------------------+-------+----------+----------+ | Rsphaeroides.blast_self_data | check | status | OK | +------------------------------+-------+----------+----------+ 1 row in set (0.04 sec)

mysql> select * from blast_self_data limit 1; Empty set (0.00 sec)

mysql> select * from blast_self_data limit 10; Empty set (0.00 sec)

mysql> quit Bye kulan$

Hmm, so I have back empty rows. Try yourself, you have Rsphaeroides.blast_self_data-compressed.tgz already.

I got already back the data I've exported last week. The table was OK:

Our cuurent status - the .MYD cannot be repaired via truncate/cp strategy. The repair gives 28262 empty rows.

mysql@kulan$ ls -la /home/mysql/Rsphaeroides/blast_self_data.* -rw------- 1 mysql mysql 17198194 Dec 10 16:42
/home/mysql/Rsphaeroides/blast_self_data.MYD -rw------- 1 mysql mysql 1024 Dec 10 18:19
/home/mysql/Rsphaeroides/blast_self_data.MYI -rw------- 1 mysql mysql 8744 Dec 10 18:06
/home/mysql/Rsphaeroides/blast_self_data.frm mysql@kulan$

Note that the table was NOT crashed when was backupped via mysqldump. So, the
size of .MYD might be different. I'd get tommorow the exact copy of this table from our tapes.
However, here's the restored table from mysqldump files at least.

mysql@kulan$ ls -la /home/mysql/Rsphaeroides_restored/blast_self_data.* -rw-rw---- 1 mysql mysql 29024592 Dec 10 18:38
/home/mysql/Rsphaeroides_restored/blast_self_data.MYD -rw-rw---- 1 mysql mysql 33792 Dec 10 18:38
/home/mysql/Rsphaeroides_restored/blast_self_data.MYI -rw-rw---- 1 mysql mysql 8744 Dec 10 18:38
/home/mysql/Rsphaeroides_restored/blast_self_data.frm mysql@kulan$ kulan$ mysql Rsphaeroides_restored Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1627 to server version: 3.23.44-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select count(*) from blast_self_data; +----------+ | count(*) | +----------+ | 594 | +----------+ 1 row in set (0.03 sec)

mysql> quit Bye kulan$