Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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!

share|improve this question
1  
Did you do a mysqldump migration or are you trying to copy over the data directory (i.e. /var/lib/mysql)? The former is the usual method. –  gravyface Apr 22 '11 at 16:02
    
I moved the data directory. The database is over 2TB in size, a datadump would take forever. –  coderego Apr 22 '11 at 16:05
    
Yikes. I'm sure copying it to Windows wasn't exactly quick. –  gravyface Apr 22 '11 at 16:06
    
Indeed it was not quick. I needed a faster environment to run queries, was told I couldn't have a linux box so....yeah....Any ideas on how I can get this running? –  coderego Apr 22 '11 at 16:10
    
The only experience I've had with moving innodb data was to another device on another partition, not to another server (and platform). I would personally suck it up and perform a mysqldump locally and then copy it to the Windows Server via an smbmount and rsync. –  gravyface Apr 22 '11 at 16:17

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

share|improve this answer
    
Thanks for the response. Unfortunately that is not an option. I must get the data up and running with what I have. I cannot now go back and setup a parallel dump. This data directory that I have transferred is a historic version that needs to be analyzed. I have it up and running in linux but do not have the query speed needed. I have to report on this later today so, I need to get it up on the windows server. Is there absolutly no way to get the copied data files to work? –  coderego Apr 22 '11 at 17:14
    
I am curious. Why is the query speed not there ??? Does it need optimization ??? Do you have enough innodb_buffer_pool ??? How much RAM does the Linux box have ??? –  RolandoMySQLDBA Apr 22 '11 at 18:11
    
The linux box its running on is a laptop. 4 gb of ram –  coderego Apr 25 '11 at 15:19

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.