Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

1 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.

share|improve this answer
    
+1 Aaron Brown. Thank you. –  Gopinath Jan 23 '12 at 5:26

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.