# ERROR Cannot find or open table? **URL:** \ **Category:** Other MySQL® Questions\ **Created:** [September 27, 2012, 4:51am UTC](https://forums.percona.com/t/error-cannot-find-or-open-table/1902 "2012-09-27T04:51:05Z")\ **Posts on this page:** 3\ **Page:** 1
**Author:** ![quanta](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/quanta/32/892_2.png) [@quanta](https://forums.percona.com/u/quanta)\ **Post date:** [September 27, 2012, 4:51am UTC](https://forums.percona.com/t/error-cannot-find-or-open-table/1902/1 "2012-09-27T04:51:05Z")
- 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](http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting.html) 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](http://bugs.mysql.com/bug.php?id=52422)”][MySQL Bugs: #52422: Cannot do ALTER TABLE DISCARD TABLESPACE if table has partitions](http://bugs.mysql.com/bug.php?id=52422%5B/URL%5D) 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:](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](http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting.html) ---
**Author:** ![ggreen](https://avatars.discourse-cdn.com/v4/letter/g/fbc32d/32.png) [@ggreen](https://forums.percona.com/u/ggreen)\ **Post date:** [September 27, 2012, 10:29am UTC](https://forums.percona.com/t/error-cannot-find-or-open-table/1902/2 "2012-09-27T10:29:46Z")
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. ---
**Author:** ![quanta](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/quanta/32/892_2.png) [@quanta](https://forums.percona.com/u/quanta)\ **Post date:** [September 27, 2012, 11:01am UTC](https://forums.percona.com/t/error-cannot-find-or-open-table/1902/3 "2012-09-27T11:01:18Z")
ggreen wrote on Thu, 27 September 2012 08:59 > [@](#): > > 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?