MySQL, OS X, and a corrupted table.

September 10, 2005 5:35 PM

WordPress, MySQL and OS X Server. Our tables keep getting corrupted. Help!

We're using WordPress blogs to teach journalism students about online media. We've got a lot of seperate WordPress installs each with their own database.

WordPress 1.5.2. MySQL 4.1.12-standard. OS X Server 10.3.9. PHP 4.3.11.

Every couple of weeks this semester, one of the wp_options tables gets corrupted (in a different database/WP install each time, so far). The first two times, it was just one row that seemed to be messed up -- an error would show up on the blog saying it couldn't get a specific value from the database. The third time, it couldn't even open the file the table was stored in. All three times, I was able to repair the table in phpMyAdmin (although the first two times, the messed up row got dropped from the table).

It seems to happen randomly. It's never happened to any of the tables for our wiki or our phpBB database, and it's only happened in the last month (and we don't know what we could have done to cause this to start).

I can fix it when it happens, but I'm worried that we might lose valuable data if this happens again.

So what are the main causes of table breakage? What can I do to narrow down the cause? What can I do to make it stop?

posted by katieinshoes to computers & internet (5 comments total)

The biggest cause of corruption is killing the server process while the table is open for writing. You don't ever do this, right?
posted by cillit bang at 5:45 PM on September 10, 2005

Are you shutting down MySQL correctly when you reboot? ("mysqladmin -u root -ppassword shutdown", or you can probably do it from phpMyAdmin somehow, but I'm not familiar with that package). You should then make sure that the mysqld process ends before you reboot, but it probably will fairly quickly if you have a normal-ish setup. If the machine crashes for any reason while mysqld is running, there is also the potential for table corruption.

Basically, any time mysqld ends abnormally, you need to check/repair all your tables. If you don't, they're very likely to start acting weird over time.

Almost always, repairing tables is safe for data, but you REALLY need backups to have all your bases covered. (Shut down MySQL, copy the table data files somewhere safe.) If you have a backup and all the binary log files since that backup, you'll be able to rebuild your database even if the data files themselves go poof.

MyISAM or InnoDB?
posted by trevyn at 5:52 PM on September 10, 2005

(...skipping the rant about the journalism school's refusal to hire someone who actually knows this stuff to take care of our precious servers...)

So if we restart the computer through the regular Apple Menu -> Restart... dialog, would it not shut down MySQL correctly as part of its shutdown procedure?

(We do have backups, so I guess I wouldn't be completely screwed if disaster struck.)
posted by katieinshoes at 6:38 PM on September 10, 2005

If it's specific to wp_options, I'd suspect a MySQL bug; every time we encountered repeating unexplained corruption of a single table it's been some bug with indexing or similar. We also had regular table corruption problems (again with a specific table) with .12, so I'd strongly recommend upgrading to .14.

Any properly scheduled reboot should terminate MySQL correctly; it'll broadcast SIGTERM, wait for everything to shut down, then SIGKILL anything that seems to have hung after 30s or a minute, at least that's what all the *ixy systems I've used do.
posted by Freaky at 7:12 PM on September 10, 2005

So if we restart the computer through the regular Apple Menu -> Restart... dialog, would it not shut down MySQL correctly as part of its shutdown procedure?

50/50 chance, probably. The shutdown process will try to start shutting it down cleanly, wait some period of time, and then kill it off uncleanly. If the MySQL process is busy, it will respond to the first shutdown request with "ok, will do, soon as I have a free moment" which could be seconds or could be minutes. If it's minutes, then eventually the shutdown process will send it increasingly-hard-to-ignore signals to exit. Some of those may leave it in a bad state, and since you're seeing it in a bad state I'd look into that possibility first.
posted by mendel at 8:11 PM on September 10, 2005