Results 1 to 10 of 10

Thread: HELP - Failed upgrade of mysql 4.1 -> 5.1, corrupt tables

  1. #1
    Registered Member
    Join Date
    Mar 2007
    Posts
    8

    Default HELP - Failed upgrade of mysql 4.1 -> 5.1, corrupt tables [SOLVED]

    I just performed an upgrade from 4.1 of mysql to 5.1 which went without errors, but now there are over 2800 tables that are failing with the error "Incorrect key file for table 'XXXX'; try to repair it"

    I have tried myisam check, repair table, etc... with no success. Please help, this is affecting a production server with over 200 databases on it.

    Solved, see below
    Last edited by gnif; 01-17-2010 at 06:40 PM. Reason: cPanel sucks

  2. #2
    Registered Member
    Join Date
    Jan 2005
    Location
    Earth
    Posts
    1,052

    Default

    What steps you followed while upgrading Mysql? You need to upgrade using

    /scripts/mysqlup --force
    and then have to recompile it with apache/php using

    /scripts/easyapache
    You can also try to repair the tables using the following command

    cd /var/lib/mysql/db_name/
    myisamchk -r *.MYI

  3. #3
    Registered Member
    Join Date
    Mar 2007
    Posts
    8

    Default

    Thanks for trying, but if you read my original post, I tried all of this.

    Right now I am very very cranky with cPanel, why the hell was this issue not clearly documented? Moving from mysql 4 to 5 in general works fine, but if you have joomla websites, your screwed, mysql for some reason failes to upgrade the jos_sessions, jos_components and jos_acl_aro tables. This occured across every database with a joomla install on it.

    mysql_upgrade on the command line reported these failures to upgrade, yet cPanel kept silent about it, HOW DUMB!

    I did rectify the problem after 14 hours of messing about and the help of two other DBAs. No form of check or upgrade would solve this problem.

    In the end, I had to write a script to grab the entries from a .sql backup of the tables across all the databases, drop the tables, and re-create them, then re-insert the data into them. Thank goodness the tables that were affected are not changed frequently.

    cPanel could avoid this issue by just doing a mysqldump of every database, and after installing mysql 5, restoring every database from the mysql 4 backup.

    This brought me to my 2nd problem, cPanel DOES NOT tell you that you will need to rebuild Apache/PHP after the MySQL upgrade, nor does it check if easyapache is up to date first! It also does not tell you that PHP 5.3 has a new mysql driver in it that may cause problems, which broke every wordpress site on our server.

    If I had of known that apache/php would need re-building, I would have done it FIRST to ensure compatibility with our websites, instead of having a minefield of updates to search for problems in, is it the database again, or apache, or php...

    In short, if you plan to do this...

    * Upgrade server software
    * Upgrade apache, php first!
    * Test for website compatibility
    * Backup your databases using mysqldump
    * Upgrade MySQL 5
    * Restore your databases
    * Re-build apache and PHP again

  4. #4
    Registered User
    Join Date
    Feb 2010
    Posts
    1

    Default

    I would really like to see the script/solution then! I'm having exactly the same problems....

    I would be VERY happy!

  5. #5
    Registered Member
    Join Date
    Jul 2005
    Posts
    15

    Default

    Quote Originally Posted by venom1st View Post
    I would really like to see the script/solution then! I'm having exactly the same problems....
    Try:

    Code:
    mysql_upgrade -u root -pPASSWORD
    If this doesn't help, you probably still have ISAM tables in some of your databases, have a look at the output of mysql_upgrade and/or search for ISM and ISD files within your database dir (usually /var/lib/mysql).

    Code:
    find /var/lib/mysql -name *.ISM
    If there are some of these, bad news, ISAM is no longer supported by MySQL (>=5.x). You need a 4.X MySQL server to convert ISAM tables into the MYISAM format (ALTER TABLE isamtablename ENGINE=MYISAM). Or, probably easier, drop the databases which still have ISAM tables and load your last, flat sql dump of these databases.

  6. #6
    Registered Member sehh's Avatar
    Join Date
    Feb 2006
    Location
    Europe
    Posts
    515

    Default

    Same here, the upgrade to 5.1 messed up all Joomla installations.
    CODE IS POETRY

  7. #7
    Registered Member
    Join Date
    Nov 2004
    Location
    Europe
    Posts
    107
    cPanel/WHM Access Level

    DataCenter Provider

    Default

    same here, all joomlas are defekt and the backups not usable! thanks cpanel!!! the upgrade assistent should really do db dups, I've no idea what to tell the clients

  8. #8
    Registered Member sehh's Avatar
    Join Date
    Feb 2006
    Location
    Europe
    Posts
    515

    Default

    Yes, cPanel developers made a mistake here and messed up their upgrade method. They know that their method corrupts all Joomla installations and still they don't care to fix it.

    It is also your mistake that you haven't kept proper backups. I kept a backup (two in fact, one is the while /var/lib/mysql directory and another is a whole mysqldump --opt of all databases) and I also came here in the forum and read a bit about the process.

    If you had read the forum you'd know that Joomla will be corrupted, thus you would have taken steps to make avoid that (backups).

    Here is what I did:

    1) zip the contents of /var/lib/mysql
    2) run "mysqldump --opt --all-databases > mysql.sql"
    3) update to latest mysql 5.1
    4) find all joomla installations with "ls -la /home/*/public_html/COPYRIGHT.php"
    5) restore from backup only those joomla databases with: "mysql --one-database [MYDB] < mysql.sql"

    thats it, easy and fast.
    CODE IS POETRY

  9. #9
    Registered Member
    Join Date
    Mar 2007
    Posts
    8

    Default

    I had backups, that was not the issue, its that cPanel does not tell you in any way that this will corrupt databases.

    It would be a very simple matter for them to just add a notice about it, or even a solution to it that is very simple...

    mysqldump --opt --all-databases > backup.sql
    update mysql
    mysql -u root < backup.sql

    Woah! thats a hard fix!, I mean those two lines of code would be so hard to add! thats like seconds of work.... R&D time would be enormous! Get your act together cPanel and fix this obvious flaw

    Also, your backup of the /var/lib/mysql folder would have been useless as they were in mysql 4.x format and would not upgrade. Experience has taught me that a mysqldump is the only safe way to backup a database for future import.

  10. #10
    cPanel Quality Assurance Analyst cPanelDon's Avatar
    Join Date
    Nov 2008
    Location
    Houston, Texas, U.S.A.
    Posts
    2,554
    cPanel/WHM Access Level

    DataCenter Provider

    Default

    If there is any uncertainty regarding the MySQL upgrade facility provided in WHM I recommend that you carefully read and review our official documentation that verbosely details the process, including describing when the upgrade process prompts if you would like to automatically run EasyApache. The full WHM menu path and applicable documentation resources are as follows: WHM: Main >> Software >> MySQL Upgrade

    It would help very much if anyone experiencing the described difficulty could provide their active ticket ID number where the issue was escalated as at the present time I am not aware of any existing report (i.e., ticket submission) that details the specific circumstances involved.

    If you have a reproducible situation with clear steps known to duplicate an issue it would be greatly appreciated to escalate the information via a formal bug report or via a support request so that proper investigation may take place. Depending on your situation and available information, either click here to submit a formal bug report, or click here to submit a general support request. Upon completion of the ticket submission, please click here to send me a private message with your new ticket ID number so that I may follow-up internally.

Similar Threads

  1. Failed MySQL Upgrade From 5.0 to 5.1
    By traxweb in forum Database Discussions
    Replies: 4
    Last Post: 02-18-2014, 12:31 PM
  2. Replies: 3
    Last Post: 09-25-2013, 02:55 AM
  3. Corrupt tables
    By mojamoi in forum Database Discussions
    Replies: 5
    Last Post: 02-03-2012, 07:43 AM
  4. innoDB tables corrupt, mySQL failing to restart
    By TheBiggestGnome in forum cPanel & WHM Discussions
    Replies: 1
    Last Post: 06-13-2011, 01:49 AM
  5. HELP - Failed upgrade of mysql 4.1 -> 5.1, corrupt tables
    By gnif in forum cPanel & WHM Discussions
    Replies: 9
    Last Post: 04-29-2010, 11:18 PM
bargain