0

Well, this problem occured to me first time. Yesterday, one of our mysql system server crashed and when it restarted last few inserts were completely rollbacked.

I am sure that they were proper inserts as we fetch results after inserts to print a PDF and PDF was generated properly. and system was crashed after 2-3 minutes of that INSERT operation.

System is PHP - MySQL based and we are using MyISAM as storage engine for our tables with autocommit ON

This is unique as earlier, after a system crash we normally had crashed tables but nothing like rollback operation. all the table are intact and operational after system crash.

Can anyone explain to me how is that possible if autocommit is ON. Thanks in advance

1

MyISAM does not support transactions, commits, or rollbacks. You have to switch to the InnoDB.

InnoDB Architecture

InnoDB Architecture

InnoDB has mechanisms, log files, and associative memory structures for rollback, commit, transaction isolation, and MVCC.

MyISAM has no infrastructure like this. Each MyISAM table is its own entity.

Here is a great reason to switch to InnoDB: MyISAM does not cache data. It lets the OS do that. That's a scary thought for MySQL/Linux, and horrific nightmare for MySQL/Windows. I wrote about this difference in the past : What are the main differences between InnoDB and MyISAM?

Thus, in the event of a crash, not only do you lose any data the OS did not flush to disk, but you also may have to run CHECK TABLE/REPAIR TABLE to fix open file counts in the headers of the MyISAM tables affected.

In your case, you are playing Russian Roulette relying on data from a MyISAM tables after a server crash or a mysqld crash.

Please see my past posts on using and tuning InnoDB

Give it a Try !!!

UPDATE 2014-01-03 10:29 EST

In your comment, you asked

is it possible that data that was inserted 2-3 minutes before crash also lost in MyISAM?

Absolutely !!! I actually saw this happen three ago. I create a MyISAM table in /dev/shm (Shared Memory). I loaded it with a 3GB .MYD and 2GB .MYI. While loading it, I ran ls -l on the table and it kept saying 0 bytes for .MYD and 1024 bytes for .MYI. After 5 min, I killed the mass INSERT. Suddenly, data and index pages started to pour into the table files. Had mysqld actually crashed, those data and index pages would never have made it to the table. In your case, this would especially be true for all open MyISAM tables in a write-heavy database environment.

  • is it possible that data that was inserted 2-3 minutes before crash also lost in MyISAM? – simply-put Jan 3 '14 at 5:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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