ERROR Cannot find or open table?

MySQL & MariaDB Other MySQL® Questions
1
  • MySQL version: 5.5.24

Due to the following problem:

mysql> desc reportingdb.v3_zone_date_cpm7k; ERROR 1146 (42S02): Table ‘reportingdb.v3_zone_date_cpm7k’ doesn’t exist

/var/log/mysqld.log

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 MySQL :: MySQL 8.0 Reference Manual :: 15.21 InnoDB Troubleshooting how you can resolve the problem.

(I haven’t find out the reason yet)

The table’s files are still exist in the datadir:

-rw-rw---- 1 mysql mysql 8932 Sep 26 16:50 /var/lib/mysql/reportingdb/v3_zone_date_cpm7k.frm -rw-rw---- 1 mysql mysql 84 Sep 26 16:50 /var/lib/mysql/reportingdb/v3_zone_date_cpm7k.par -rw-rw---- 1 mysql mysql 9437184 Sep 13 17:56 /var/lib/mysql/reportingdb/v3_zone_date_cpm7k#P#MERGER_2012828.ibd -rw-rw---- 1 mysql mysql 1048576 Sep 27 15:42 /var/lib/mysql/reportingdb/v3_zone_date_cpm7k#P#MERGER_2012926.ibd

I’m going to recover this table follow this guide. But at 2c. step, I get the below errors:

mysql> alter table v3_zone_date_cpm7k_restore discard tablespace; ERROR 1031 (HY000): Table storage engine for ‘v3_zone_date_cpm7k_restore’ doesn’t have this option

[URL=“http://MySQL Bugs: #52422: Cannot do ALTER TABLE DISCARD TABLESPACE if table has partitions”]MySQL Bugs: #52422: Cannot do ALTER TABLE DISCARD TABLESPACE if table has partitions

What can I do now?

UPDATE

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: MySQL :: MySQL 8.0 Reference Manual :: 15.21 InnoDB Troubleshooting

2

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.

3

ggreen wrote on Thu, 27 September 2012 08:59

Obviously:

Empty set (1.01 sec).

ggreen wrote on Thu, 27 September 2012 08:59

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