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.

We take db backup by stopping the database and then issuing a tar -zcf on the mysql data directory. When we restore the data directory on another machine and try to start the slave, the mysql error log reports that a number of tables have crashed with messages similar to the following:

140221 19:09:49 [ERROR] /usr/sbin/mysqld: Table './adplatform/aggregate_app' is marked as crashed and should be repaired
140221 19:09:49 [Warning] Checking table:   './adplatform/aggregate_app'
140221 19:09:49 [ERROR] 1 client is using or hasn't closed the table properly

Trying to recover the table using myisamchk results in the following for a particular table:

$ myisamchk -o -f -r -v --key_buffer_size=4G --myisam_sort_buffer_size=4G --read_buffer_size=2G --write_buffer_size=2G aggregate_app
Warning: option 'read_buffer_size': unsigned value 2147483648 adjusted to 2147483647
Warning: option 'write_buffer_size': unsigned value 2147483648 adjusted to 2147483647
- recovering (with sort) MyISAM-table 'aggregate_app'
Data records: 337215562
- Fixing index 1
  - Searching for keys, allocating buffer for 49367435 keys
  - Last merge and dumping keys
- Fixing index 2
  - Searching for keys, allocating buffer for 252645129 keys
  - Last merge and dumping keys
myisamchk: Unexpected eof found when reading file '/tmp/ST5BRnZ9' (Errcode: 175)
myisamchk: error: 175 when fixing table
MyISAM-table 'aggregate_app' is not fixed because of errors
Try fixing it by using the --safe-recover (-o), the --force (-f) option or by not using the --quick (-q) flag

As you can see, I'm using the -o and the -f options and not using the -q option. Is there any way to repair this table?

share|improve this question
    
what happens when you select from the table? –  akuzminsky Feb 21 at 20:37
    
Is it possible you ran out of space on your /tmp partition while this was running? MySQL error code 175: File to short; Expected more data in file –  Michael - sqlbot Feb 22 at 0:23
    
@Michael-sqlbot - My /tmp lies on a partition with 750G of free space. –  Chandranshu Feb 22 at 4:36
    
Right, but I wanted to be certain you've monitored the free space as the process runs, such that you have confirmed it's not a case of... open temp file, unlink temp file, fill all available temp space stealthily by writing to open-but-unlinked file, crash, space allocated to open file is immediately freed by the system because the process holding it open has crashed...? –  Michael - sqlbot Feb 22 at 10:38
    
Incidentally, you say you take backups by "stopping the database" ... define "stopping the database" please... that should not result in these errors for any reason I can think of. –  Michael - sqlbot Feb 22 at 10:41
show 1 more comment

Your Answer

 
discard

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

Browse other questions tagged or ask your own question.