120927 16:57:04 [ERROR] Cannot find or open table reportingdb/v3_zone_date_cpm7k#P#pcurrent_2012926 from the internal 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'm restoring from the backup, what is the right procedure to get rid of this problem?

What I've tried (on the another server):

1. DROP TABLE --> still get the "doen't exist"
2. Stop MySQL
Move all the table's files to another location
Copy the backup files to corresponding database
Start MySQL:

120927 19:12:07 InnoDB: Error: table 'reportingdb/v3_zone_date_cpm7k#P#MERGER_2012828'InnoDB: in InnoDB data dictionary has tablespace id 741528,InnoDB: but tablespace with that id or name does not exist. HaveInnoDB: you deleted or moved .ibd files?InnoDB: This may also be a table created with CREATE TEMPORARY TABLEInnoDB: whose .ibd and .frm files MySQL automatically removed, but theInnoDB: table still exists in the InnoDB internal data dictionary.InnoDB: Please refer toInnoDB: http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting-datadict.htmlInnoDB: for how to resolve the issue.InnoDB: We removed now the InnoDB internal data dictionary entryInnoDB: of table `reportingdb`.`v3_zone_date_cpm7k` /* Partition `MERGER_2012828` */.120927 19:12:07 InnoDB: error: space object of table 'reportingdb/v3_zone_date_cpm7k#P#MERGER_2012926',InnoDB: space id 921829 did not exist in memory. Retrying an open.120927 19:12:07 InnoDB: Error: table `reportingdb`.`v3_zone_date_cpm7k` /* Partition `pcurrent_2012926` */ does not exist in the InnoDB internalInnoDB: data dictionary though MySQL is trying to drop it.InnoDB: Have you copied the .frm file of the table to theInnoDB: MySQL database directory from another database?InnoDB: You can look for further help fromInnoDB: http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting.html

I am willing to bet the record for that table missing from the information_schema database.
SELECT * FROM information_schema.tables WHERE TABLE_SCHEMA = 'reportingdb' AND table_name = 'v3_zone_date_cpm7k';

I had this happen to me once, when I copied database files from one machine to another, I restored from a dump file instead and all was good.

Comment

I am willing to bet the record for that table missing from the information_schema database.
SELECT * FROM information_schema.tables WHERE TABLE_SCHEMA = 'reportingdb' AND table_name = 'v3_zone_date_cpm7k';

Obviously:

Empty set (1.01 sec).

ggreen wrote on Thu, 27 September 2012 08:59

I had this happen to me once, when I copied database files from one machine to another, I restored from a dump file instead and all was good.

How did you restore if tablespace id from the backup is diffrent from the current tablespace id?