Cannot access MySQL database after moving files in data directory

I am using Xampp locally and I recently did a computer upgrade where I went from XP to Windows 7 and also got a new drive to install Windows on.

I have all my old Xampp files in: G:\xampp
I installed the new Xampp in: C:\xampp (which is the directory location they were previously in)

I then copied over everything in G:\xampp\mysql\data to C:\xampp\mysql\data.

When I view the specific database in SQLYog I can see everything fine apart from one table which says:


Table 'qotm.wp_36rwq4kcgt_options' doesn't exist

...but when I try and connect to the database via a script I get:


InnoDB: Cannot open table qotm/wp_36rwq4kcgt_options from the internal data dictionary of InnoDB though the .frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem.

I visited that link and the closest thing I could find relevant was the part under Problem Opening Table; however I do not have any #sql-.ibd* files in that directory, nor any .ibd files whatsoever in there.

I then found this post but it only mentions .ibd files which I do not have any of.

Ok, the problem was because I didn't copy the ibdata* file. You may expect this file to be located within the respective database folders just like the other related files, but you would be wrong.

My ibdata file was located at mysql/data/ibdata1; as soon as I copied that to the new location everything was good again.

Note however I have read of this file being located in different locations, so your mileage may vary.

StackExchange Database Administrators Q#124589, answer score: 5