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

[gnif]

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

[madaboutlinux]

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

[gnif]

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

[venom1st]

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

I would be VERY happy!

[akrzystek]

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

Try:

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).

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.

[sehh]

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

[apogee]

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

[sehh]

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.

[gnif]

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.

[cPanelDon]

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.