Repairing Myisam Table when there was no additional Disk space & Table corrupted

Question

I had a table which MyIsam as storage Engine contains million rows. There is a purging task and need to remove 35 days old data. i measured up the rows in following manner :

mysql> select count(*) from table_date;

+----------+
| count(*) |
+----------+
| 53217368 |
+----------+

2. Here are the min and max value for the  table1:

mysql> select min(table_date),max(table_date) from table1;
+---------------------+---------------------+
| min(table_date)     | max(table_date)     |
+---------------------+---------------------+
| 2011-08-09 04:05:01 | 2012-01-13 04:04:16 |
+---------------------+---------------------+
row in set (0.01 sec)

3. Date and time 35 days ago from the current date & time will be:

mysql> select now(),now() - interval 35 day;
+---------------------+-------------------------+
| now()               | now() - interval 35 day |
+---------------------+-------------------------+
| 2012-01-13 21:41:36 | 2011-12-09 21:41:36     |
+---------------------+-------------------------+

4. Number of rows which contains dataentry_date less than the above date is:

mysql> select count(*) from table1 where table_date < '2011-12-09 21:41:36';
+----------+
| count(*) |
+----------+
| 30729315 |
+----------+
1 row in set (3 min 3.34 sec)

Now purged the 3 million rows using :

mysql> delete from table1 where table_date < (now() - interval 35 day);

After running  the  delete query has been terminated with the below error message
and mysql has been restarted.

Tried to Repair using :

mysql> check table table_date;
+----------------------------+-------+----------+-----------------------------------------------------------------------------------+
| Table                      | Op    | Msg_type | Msg_text                                                                          |
+----------------------------+-------+----------+-----------------------------------------------------------------------------------+
| tabe1.table_date           | check | warning  | 7 clients are using or haven't closed the table properly                          |
| tabe1.table_date           | check | error    | Can't read key from filepos: 2048                                                 |
| tabe1.table_date           | check | Error    | Incorrect key file for table './table1/table_date.MYI'; try to repair it |
| tabe1.table_date           | check | error    | Corrupt                                                                           |
+----------------------------+-------+----------+-----------------------------------------------------------------------------------+
4 rows in set (0.01 sec)

No luck & then tried with repair table :

mysql> repair table table_date;
+----------------------------+--------+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table                      | Op     | Msg_type | Msg_text                                                                                                                                                                                 |
+----------------------------+--------+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tabe1.table_date           | repair | error    | 28 when writing to datafile                                                                                                                                                              |
| tabe1.table_date           | repair | Error    | Disk is full writing './table/tabl1.TMD' (Errcode: 28). Waiting for someone to free space... (Expect up to 60 secs delay for server to continue after freeing disk space)                |
| tabe1.table_date           | repair | Error    | Retry in 60 secs. Message reprinted in 600 secs                                                                                                                                          |
| tabe1.table_date           | repair | Error    | Retry in 60 secs. Message reprinted in 600 secs                                                                                                                                          |
| tabe1.table_date           | repair | Error    | Error writing file './table/table_data.TMD' (Errcode: 28)                                                                                                                                |
| tabe1.table_date           | repair | status   | Operation failed                                                                                                                                                                         |
+----------------------------+--------+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
6 rows in set (43 min 5.53 sec)

No luck again.

Error Log :
--------------------
Version: '5.1.45-community' socket: '/var/lib/mysql/mysql.sock' port: 3306
MySQL Community Server (GPL)
120120 4:38:05 [ERROR] /usr/sbin/mysqld: Incorrect key file for
table './tabl1/table_data.MYI'; try to repair it
120120 4:39:14 [ERROR] /usr/sbin/mysqld: Incorrect key file for
table './table1/table_date.MYI'; try to repair it
120120 5:05:13 [ERROR] /usr/sbin/mysqld: Disk is full
writing './table1/table_date.TMD' (Errcode: 28). Waiting for someone to free
space... (Expect up to 60 secs delay for server to continue after freeing disk
space)
120120 5:05:13 [ERROR] /usr/sbin/mysqld: Retry in 60 secs. Message reprinted
in 600 secs
120120 5:15:13 [ERROR] /usr/sbin/mysqld: Retry in 60 secs. Messagemysql>

I tried with Myisamchk -r & -o  also. But no luck.

Here is my present table status :

mysql> show table status like 'table_date'\G
        *************************** 1. row ***************************
                   Name: table_date
                 Engine: NULL
                Version: NULL
             Row_format: NULL
                   Rows: NULL
         Avg_row_length: NULL
            Data_length: NULL
        Max_data_length: NULL
           Index_length: NULL
              Data_free: NULL
         Auto_increment: NULL
            Create_time: NULL
            Update_time: NULL
             Check_time: NULL
              Collation: NULL
               Checksum: NULL
         Create_options: NULL
                Comment: Table './table1/table_date'
                         is marked as crashed and last (automatic?)
      1 row in set (0.01 sec)

Q1. When i used myisamchk .TMD got created.It is a intermediate data file i could understand.if i can delete this will it work out. ?

Q2. The table occupies 17GB space. And got crashed after excuting the delete. When trying to repair disk space issue. Any tricky solution to get back this table to working state. Solution will appreciated.

Answer 1

If you cannot free up or add any disk space, you could copy the table_data.* files (MYD,MYI,frm,etc) to another machine with plenty of free disk, run the repair there with myisamchk, then copy the files back to the original machine. If the server needs to stay running, do a FLUSH TABLES WITH READ LOCK table_data before copying the data to the other server and UNLOCK TABLES after you copy the data back. You will need to keep the FLUSH TABLES ... session running for this duration.

So, it would look like this

server1:

# keep this session running for the duration of the repair!
mysql> FLUSH TABLES WITH READ LOCK table_data;
/var/lib/mysql# rsync -aP table_date.* server2:/somedir/

server2:

# make a backup
/somedir# tar -czvf table_data_backup.tgz table_date.*
# run the repair
/somedir# myisamchk -r table_data
# copy the files back
/somedir# rsync -aP table_date.* server1:/var/lib/mysql/

server1:

# make sure the permissions are correct on /var/lib/mysql/table_date.*
mysql> UNLOCK TABLES;

Remember, do not close the FLUSH TABLES WITH READ LOCK session.

You may want to give myisamchk additional memory for the following parameters to speed it up: --key_buffer_size --sort_buffer_size --read_buffer_size --write_buffer_size

Next, convert your table to InnoDB. There's almost no good reason to use MyISAM these days.

Answer 2

I have experimented such Free Space trouble myself. I am on Windows, i simply created the table.TMD file (an empty text file) on another harddrive. Then i created a Symbolic link to it, into the database directory.

mklink E:\table.TMD C:\sql\data\table.TMD

First path being the empty txt file, and second one being the place where myisamcheck is gonna create the .TMD. It will trick it to finaly write into the External harddrive.

I dont know whats the equivalent in Linux or else tho. But it must exists.