1

Sometimes tables in the mySQL database crash after a server restart or after a lost connection to the database. This have happen several times for the last 3-4 weeks. This is how the error message looks like:

145 - Table './xxx/sessions' is marked as crashed and should be repaired select value from sessions where sesskey = '60fa1fab3a3d285cfb7bc1c93bb85f64' and expiry > '1395226673' [TEP STOP]

So far it’s have been the tables "sessions" and "whos_online" that have crashed. If I repair the tables in phpmyadmin it will work fine again

After the last crash I changed "sessions" from MyISAM to InnoDB. The table "whos_online" still use MyISAM.

I use osCommerce 2.2 rc2a and I'm looking for any thoughts and suggestions in this matter.

One solution might be to change both these tables to InnoDB, since it supposed to be self-healing. Is that a good or bad idea?

Another one would be to have them in MyISAM and do something like this with the php-file that echo the error message:

if $error contain "is marked as crashed and should be repaired"
run a table repair script

Would that be a good or bad idea?

Here’s my server specs Database: MySQL 5.5.36-cll PHP Version: 5.3.15

Fredrik Johansson
  • 85
  • 1
  • 11
  • Do you know the difference between MyISAM and InnoDB? That will probably answer why you should or should not change the table engine. – Namphibian Mar 20 '14 at 04:40
  • I have done some reading about it theese last days, but I'm still not sure how a change will affect in the end. I saw that osCommerce v3.0 only use Innodb and that Magento (in not all, atleaste some magento-shops) use InnoDB. So a straight answer would be very much appreciated. – Fredrik Johansson Mar 20 '14 at 12:10

1 Answers1

0

At first you should address the issue that tables become corrupted in the first place. InnoDB tries to repair broken files, but it cannot do magic. Repeated unsafe shutdowns or other accidents may seriously corrupt your database!

If you only have a small website, all variants are good. MyISAM is a little faster (sometimes), while InnoDB provides some extended features. If the server is strong enough, you will likely encounter no issues. I would stick with InnoDB in most cases as it accounts for consistent data and throws errors if files are broken, unlike MYISAM tables which are used and then sometimes throw errors in production...

But if something severely breaks, it requires more effort to get MySQL with InnoDB back up running if it does not automatically. There exist different InnoDB recovery modes which are only available from the shell of your server and require modifying the config file, starting the server by hand, reading its output and maybe doing some other actions. If you do not have any clue of these issues, you might want to stick with MyISAM instead. The server always starts, and if the table is utterly broken, you might need to import a backup. But this is more easily than editing config files and reading database outputs etc.

Ulrich Thomas Gabor
  • 6,584
  • 4
  • 27
  • 41