Weird mySQL v4-v5 upgrade bug

jez9999

Well-Known Member
Jun 10, 2005
87
0
156
I recently switched from using mySQL 4.1.21-standard on my system to using mySQL 5.0.27-standard; selected version 5 in the 'tweak settings' dialog in WHM, ran 'Software/Update Server Software' in WHM *and* ran /scripts/mysqlup. Also ran easyapache and rebuilt Apache. Rebooted the system.

I am now getting a very weird bug. I can run mysql's client when the system first boots up, and my old databases and tables all seem to be there. However, apart from the tables in the 'mysql' DB, all the others seem to be 'corrupted'. When I try to select them, i get one of three errors:
- ERROR 1017 (HY000): Can't find file: '(table name)' (errno: 2)
- ERROR 1146 (42S02): Table '(table name)' doesn't exist
- ERROR 1033 (HY000): Incorrect information in file: ./(whatever).frm

So, catastrophic database corruption, I thought. I was getting ready to jettison the whole lot and restore from backup when I restarted the mySQL daemon via WHM, and suddenly everything worked. I've repeated this process a few times (reboot, access, restart daemon, access) and it keeps happening. The errors occur until I restart the mySQL daemon and then it works fine. These errors only seem to apply to tables that were created before I switched to mySQL 5 - tables that I create now are accessible the first time the daemon loads up and don't cause errors.

Does anyone have any idea what might be causing this? It sure has baffled me. The data from the 'old' tables is clearly there, and working fine, but I'd rather not have to restart the mySQL daemon every time after I reboot for it to work!!
 
Last edited:

jez9999

Well-Known Member
Jun 10, 2005
87
0
156
Thanks, everyone who helped.

Had to track the problem down myself with a lot of blood sweat and tears.

In the mysqld logfile, InnoDB's engine was complaining along these lines:
Code:
usr/sbin/mysqld: Can't create/write to file '/tmp/ibAsiEMe' (Errcode: 13)
061219 17:49:22  InnoDB: Error: unable to create temporary file; errno: 13
I eventually tracked down this cPanel page:
http://faq.cpanel.net/show.cgi?qa=116340173808618

Fixed the 'real' /tmp dir permissions, and it now seems to work correctly on boot and InnoDB is no longer complaining.

I have no idea why upgrading from mySQL 4 to 5 caused the /tmp dir's permissions to get screwed up, but I do, of course know this (as any cPanel/WHM user should know): always trust cPanel to cause random system failures and screwups that will p*ss you off, at every turn.