Mysql – MyISAM : Should I be concerned by these warnings

backupcorruptionmyisamMySQL

I restored some tables from recent Databases backups. Because Im supporting a legacy system that only uses MyISAM tables in their DBs, for backing up I used a little script that pretty much copies all the database fils (the folders with the *.frm *.MYD and *.MYI).

At first everything seemed ok, I could see and query every table without problem, but just to be sure I did a little check in MySQL Administrator and got this:

Status checked for table fotdb.tabfotlog.
1 client is using or hasn't closed the table properly
------------------------------------------------------------
Status checked for table fotdb.tabfotlog.
Size of indexfile is: 11223040        Should be: 11224064
------------------------------------------------------------
Status checked for table fotdb.tabfotlog.
Size of datafile is: 28854024         Should be: 28854668
------------------------------------------------------------
Status checked for table fotdb.tabfotlog.
Corrupt

After that the table was labeled as corrupt and I couldn't access to it, so I did a little repair and everything went back to normal.

My guts tell me it has to do with the fact that the tables were used and written in while copying (because availability needs I cannot lock the tables while I copy them, and since every table by design is completely independent one from another, it is acceptable to have different point of time for each table).

My question is, should I be particularly worried about this? If it was this easy to fix I think I just can get by with this, but there may be bigger concerns Im not aware of and that's what I want to know. Specially since MyISAM documentation is particularly difficult to find.

Best Answer

Yes, you should be worried, but you can improve your backup process with a few changes.

ANALYSIS OF MESSAGES

Status checked for table fotdb.tabfotlog.
1 client is using or hasn't closed the table properly

MyISAM has a header that contains the number of open file handles on the table. If you make a copy of the table with a nonzero value for the number of open file handles, copying it to another MySQL instance or restoring it to your current MySQL instance will result in the table being marked crashed.

Status checked for table fotdb.tabfotlog.
Size of indexfile is: 11223040        Should be: 11224064
------------------------------------------------------------
Status checked for table fotdb.tabfotlog.
Size of datafile is: 28854024         Should be: 28854668

Running REPAIR TABLE fotdb.tabfotlog; will fix it all of these problems after restoring it, but you may probably lose a few rows in the process.

What accounts the discrepancy in the size of .MYD and .MYI ?

The MyISAM Storage Engine relies solely on the OS for disk changes. That means the OS is responsible for flushing disk changes to the .MYD and .MYI files. This mechanism becomes much worse if you are talking about using MyISAM in a Windows environment.

SUGGESTION

There was a post made back on March 24, 2014 entitled Does a MyISAM table locks if I copy it while MySQL is running? (READ DISCLAIMER)

I have a post (Shell Scripting included) and so does @BillKarwin (proposes XtraBackup for MyISAM) that you may find helpful on scripting a FLUSH TABLES WITH READ LOCK plus other nuances of MyISAM you need to consider.

EPILOGUE

MyISAM Documentation is actually very easy to find. I keep some links in the myisam tag info. The first link goes to the latest MyISAM Documentation in MySQL 5.6.

UPDATE 2014-05-21 19:07 EDT

It just dawned on me. The posts from @BillKarwin and I were answering you from Mar 24th.

Related Question