The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

server-wide integrity test for all mysql dbs?

Discussion in 'General Discussion' started by spaceman, Jun 22, 2006.

  1. spaceman

    spaceman Well-Known Member

    Joined:
    Mar 25, 2002
    Messages:
    481
    Likes Received:
    0
    Trophy Points:
    16
    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.
     
    #1 spaceman, Jun 22, 2006
    Last edited: Jun 22, 2006
  2. spaceman

    spaceman Well-Known Member

    Joined:
    Mar 25, 2002
    Messages:
    481
    Likes Received:
    0
    Trophy Points:
    16
    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/MyISAM_versus_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?
     
  3. spaceman

    spaceman Well-Known Member

    Joined:
    Mar 25, 2002
    Messages:
    481
    Likes Received:
    0
    Trophy Points:
    16
    /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.

    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!
     
Loading...

Share This Page