server-wide integrity test for all mysql dbs?

[spaceman]

Hi All,

Today, on two different websites on two servers in remote locations (Aus and US), we've had mysql table corruptions. In both case, 3 tables were corrupted. When I ran a WHM 'repair database' script on each of the dbs I saw errors like these:

actorsno_primary.admin OK
actorsno_primary.age OK
actorsno_primary.animal
error : Can't find file: 'animal.MYI' (errno: 2)
actorsno_primary.animal_type
error : Can't find file: 'animal_type.MYI' (errno: 2)
actorsno_primary.casting OK
actorsno_primary.class OK
actorsno_primary.family
error : Can't find file: 'family.MYI' (errno: 2)
actorsno_primary.gender OK
actorsno_primary.news OK
actorsno_primary.person OK
actorsno_primary.person_talent OK
actorsno_primary.talent OK
actorsno_primary.teacher OK

I believe the time (today) and the number (3) to be largely coincidental.

But what I'd like to do now is to run an integrity test on all mysql dbs on all servers we administer (5).

Best way to do this? I only know the 'one domain at a time' WHM > repair database option.

Thanks in advance.

[spaceman]

Hey, I just found out the smoking gun: all the tables that got corrupted were type InnoDB, whereas all the ones that are fine were type MyISAM.

from http://wiki.dreamhost.com/index.php/..._InnoDB_tables ...

"A general guideline could be as follows: if you require multi-statement transactions, advanced isolation levels and row-level locking, foreign key constraints, or otherwise have a requirement for ACID features, go for InnoDB. Otherwise, simply use MyISAM, the default."

Seeing as we don't require all the above, I'm now researching how to convert from InnoDB to MyISAM. Any clues anyone?

I'm assuming it's a bad thing to mix MyISAM tables with InnoDB on the same database? Or shouldn't it matter?

[spaceman]

/etc/my.cnf

Somehow this line had come to be included (or uncommented) in /etc/my.cnf on our two servers:

skip-innodb

So in actual fact, the tables that we thought were corrupted, weren't. They had merely been disabled by the unexpected introduction of this option in my.cnf.

Chapter: 7.5 (http://www.mysql.com/doc/en/InnoDB_start.html)
"If you don't want to use InnoDB tables, you can add the skip-innodb option to your MySQL option file. "

Chapter 4.1.1 mysqld Command-line Options
(http://www.mysql.com/doc/en/Command-line_options.html)
"--skip-innodb
Disable usage of Innodb tables. This will save memory and disk space and speed up some things. "

So we now know what caused the problem, but we don't know what caused skip-innodb to appear in my.cnf when it wasn't in their before!