Mysql: Innodb error when migrating mysql from linux to windows

Question

I have migrated my data directory for an InnoDB mysql server from a linux machine to a windows machine. I started the server up, selected the database, and tried to query a table. Got the error "Table does not exist", however it is shown in the "Show tables;" command.

I checked my server error log and found:

"[ERROR] Cannot find or open table [dbname/tablename] from the internal data dictionary of InnoDB though the .frm file for the table exists. data dictionary of InnoDB though the .frm file for the table exists. Maybe you have deleted and recreated InnoDB data files but have forgotten to delete the corresponding .frm files of InnoDB tables, or you have moved .frm files to another database? or, the table contains indexes that this version of the engine doesn't support. See http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting.html how you can resolve the problem."

I believe it may have something to do with the case sensitive? I've tried the lower_case_table_names setting on 0, 1 and 2 with no luck.

Anyone have any ideas? Much obliged!

Answer 1

You need to do a mysqldump of everything !!!

With regard to the error message, you have what I call a pidgeon hole. It is essentially a table's metadata that got corrupted in ibdata1. There is no way to erase it. You cannot drop the table the metadata is looking for because the corresponding data outside ibdata1 cannot be referenced via its inode. Sometimes, even mysqldumps won't work when it hits the table entry via the .frm.

From another perspective, the metadata contained in ibdata1 is Lunix-ish and inode centric, which are concepts foreign to FAT-based Windows. I would not trust InnoDB metadata built this way. Doing a mysqldump gives you logcial data representation via SQL that is both OS and hardware agnostic.

If the datadump is too big, you need to do parallel dumps of the databases or tables and load those mysqldumps into MySQL Windows.

If you are unsure or wary of scripting this, get MAATKIT and use mk-parallel-dump (deprecated tool but good for adhoc dumps) to spit out the data as CSV files. Then, use 'mysqldump --no-data --routines --triggers' and generate table structures file. Run the table structures file in MySQL Windows. Finally, load the CSV into MySQL Windows using LOAD DATA INFILE.