Thursday, February 23, 2012

Missing MySQL Files

I'm back with yet another MySQL mystery. Seriously, I think MySQL has it out for me. I turned replication on today and did a refresh of 'SHOW SLAVE STATUS\G;' eveyr few seconds. Replication stopped twice. Once it was because I'd neglected to chown my entire /var/lib/mysql directory to the mysql user and group. The second time was because it couldn't find a table that a query was attempting to modify. I went into the directory and sure enough the .frm file was there (which defined the table; metadata) but the MYD (data) and MYI (index) files weren't there. I worked with one of our developers to try and figure out what had happened and where it'd gone. I restored backups from a variety of days to see when that files had gone missing. I wanted to see if it had been related to any other server work I'd done, such as migrating MySQL over to a different machine. At no point were the files there.

I ran across a post that reminded me that MyISAM and InnoDB engines store their files differently. We use MyISAM as the default so in theory no table should have been using a different engine, but I was pulling up blanks so I gave it a look.


mysql> show create table mynewtable\G;

*************************** 1. row ***************************

       Table: worktypematerials

Create Table: CREATE TABLE `mynewtable` (

  `Marker` int(10) unsigned NOT NULL AUTO_INCREMENT,

  `ParentMarker` varchar(50) DEFAULT NULL,

  `Material` varchar(20) DEFAULT NULL,

  `Description` text,

  `Quantity` double DEFAULT NULL,

  `SortOrder` int(11) DEFAULT NULL,

  PRIMARY KEY (`Marker`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

1 row in set (0.00 sec)

The table is using InnoDB. That's why the replication couldn't find the files the statement seemed to be looking for. How is it that this lone database has tables using a different storage engine than the others? Unclear. One speculation is that because we originally migrated these database files from their internal server, they may have been using InnoDB in their environment.

The solution seemed to be to simply convert the engine from InnoDB to MyISAM with the ALTER TABLE statement. Unfortunately the slave server didn't think there was a table so trying that operation yielded an error that it did not exist. What was really odd was that the setup on the master server was the same. The same ib_logfile files were in place, the .frm file was there on both machines, but for some reason the master could read the table just fine but the slave could not. Pretty frustrating. The log files showed:

120222 15:21:14 [ERROR] Cannot find or open table database01/blahblahtable

It pointed me to http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting.html which said the issue is an orphaned .frm file for which there is no corresponding table inside InnoDB, with the solution being "simply delete the .frm file". But, ummm...I actually want that table. And in fact I can see it in SHOW TABLES. One poster had done the exact same steps as I (stopped mysql, zipped the data directory up, moved everything over) but had deleted the InnoDB files, which I had actually not done.This meant that although all of the required files were there, there was still some inconsistency between InnoDB's internal storage and the frm file.

I started to post a question to Stack Exchange to get some help on how to fix this mess. Sometimes the act of trying to explain a problem in detail (and a little time away from it) helps you to see more options. As I was explaining my situation I realized that since I had the .frm file, maybe I could recreate the tables from it. I found this article from Percona, but it pretty much said you could use it to be able to describe the table but nothing else (i.e. insert data into it) which I didn't really see the point of. I found another option that looked like it could work (and the very first paragraph explained my situation perfectly, though it made me feel like a dolt), although it seemed a little more handsy than I'd hoped and introduces a margin for error (what if I missed an .frm file?).

I came up with the idea to let our in-house application recreate the tables for me. We have a database check sequence that can be run from within the application to update/create tables as necessary, so I figured I could point my installation of the app to the slave server, and run the database tools. This could in theory work except replication has been off for a bit now and quite frankly I still want to convert the tables to MyISAM for consistency (and so that this doesn't happen again since the source of the problem is that I didn't know we had InnnoDB tables at all).

One of the developers came up with another solution: dump the database to my local machine, which uses MyISAM as the default, and restore it locally. Since the InnoDB Engine isn't available on my Windows machine (due to the way I installed MySQL), it will change the InnoDB databases to MyISAM. Then dump the newly converted database back up to the database server and restore it there. Take another dump of the data directory and copy it over to the slave server and start the replication process over again.

Sounds like a plan. I'll let you know how it goes. 

No comments:

Post a Comment