ERROR Cannot find or open table?

Question

• 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 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

This is the table DDL from a month old backup (so the partitions have changed), but for reference:

CREATE TABLE `v3_zone_date_cpm7k` ( 
 `campaignid` mediumint(9) NOT NULL DEFAULT '0' COMMENT 'sub_campaignid', 
 `zoneid` smallint(6) NOT NULL DEFAULT '0', 
 `bannerid` mediumint(9) NOT NULL DEFAULT '0', 
 `totalclick` mediumint(9) unsigned NOT NULL DEFAULT '0', 
 `realclick` mediumint(9) unsigned NOT NULL DEFAULT '0', 
 `clickcharge` mediumint(9) NOT NULL DEFAULT '0', 
 `totalview` mediumint(9) unsigned NOT NULL DEFAULT '0', 
 `viewcharge` mediumint(9) unsigned NOT NULL DEFAULT '0', 
 `dt` date NOT NULL DEFAULT '0000-00-00', 
 `partnerid` smallint(6) unsigned NOT NULL DEFAULT '0', 
 KEY `ix_zoneid` (`zoneid`,`dt`), 
 KEY `ix_dt` (`dt`), 
 KEY `ix_campaignid` (`bannerid`) 
) ENGINE=InnoDB DEFAULT CHARSET=latin1 
/*!50100 PARTITION BY RANGE (TO_DAYS(dt)) 
(PARTITION p00 VALUES LESS THAN (0) ENGINE = InnoDB, 
PARTITION p04 VALUES LESS THAN (734965) ENGINE = InnoDB, 
PARTITION p05 VALUES LESS THAN (735025) ENGINE = InnoDB, 
PARTITION MERGER_2012822 VALUES LESS THAN (735102) ENGINE = InnoDB, 
PARTITION pcurrent_2012822 VALUES LESS THAN (735103) ENGINE = InnoDB, 
PARTITION pcurrent_2012823 VALUES LESS THAN (735104) ENGINE = InnoDB)*/

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

http://bugs.mysql.com/bug.php?id=52422

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. Have
    InnoDB: you deleted or moved .ibd files?
    InnoDB: This may also be a table created with CREATE TEMPORARY TABLE
    InnoDB: whose .ibd and .frm files MySQL automatically removed, but the
    InnoDB: table still exists in the InnoDB internal data dictionary.
    InnoDB: Please refer to
    InnoDB: http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting-datadict.html
    InnoDB: for how to resolve the issue.
    InnoDB: We removed now the InnoDB internal data dictionary entry
    InnoDB: 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 internal
    InnoDB: data dictionary though MySQL is trying to drop it.
    InnoDB: Have you copied the .frm file of the table to the
    InnoDB: MySQL database directory from another database?
    InnoDB: You can look for further help from
    InnoDB: http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting.html

---

UPDATE Fri Sep 28 08:21:49 ICT 2012

Follow your suggestion, I have:

• stopped the MySQL
• moved v3_zone_date_cpm7k* to another location
• started the MySQL

• imported the .sql file and got the error:

  ERROR 1050 (42S01) at line 25: Table 'reportingdb.v3_zone_date_cpm7k /* Partition p00 */' already exists

the error log shows:

120928  8:26:42  InnoDB: Warning: trying to init to the tablespace memory cache
InnoDB: a tablespace 932889 of name './reportingdb/v3_zone_date_cpm7k#P#p00.ibd',
InnoDB: but a tablespace 932783 of the same name
InnoDB: already exists in the tablespace memory cache!
InnoDB: We assume that InnoDB did a crash recovery, and you had
InnoDB: an .ibd file for which the table did not exist in the
InnoDB: InnoDB internal data dictionary in the ibdata files.
InnoDB: We assume that you later removed the .ibd and .frm files,
InnoDB: and are now trying to recreate the table. We now remove the
InnoDB: conflicting tablespace object from the memory cache and try
InnoDB: the init again.

and the .idb file is created automatically after restarting:

# ls -l /var/lib/mysql/reportingdb/v3_zone_date_cpm7k#P#p00.ibd 
-rw-rw---- 1 mysql mysql 65536 Sep 28 08:35 /var/lib/mysql/reportingdb/v3_zone_date_cpm7k#P#p00.ibd

Answer 1

I've spent a bit of time trying to reproduce the error on the partition scheme, but cannot get the exact error with the orphaned table

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.

By moving the .ibd file for the partition out of the data directory (which it seems somehow has happened), I get an expected error:

[ERROR] MySQL is trying to open a table handle but the .ibd file for table foo/v3_zone_date_cpm7k#P#pcurrent_2012822 does not exist.

From a chat discussion I know you have an outdated backup file. Barring actually being able to force drop the partition 'pcurrent_2012926' (some data loss), the steps to restore this backup are as follows (a month worth of data loss unfortunately):

• Take a backup of your main server (just in case!)
• Restore the backup on a different server
• Take a mysqldump of the table: mysqldump -uuser -p reportingdb v3_zone_date_cpm7k > v3_zone_date_cpm7k.sql
• copy v3_zone_date_cpm7k.sql to the main server
• On the main server, attempt to do this: DROP TABLE reportingdb.v3_zone_date_cpm7k
• If that works, import your dumpfile: mysql -uuser -p reportingdb < v3_zone_date_cpm7k.sql which should restore that table (with a month-old table)
• If the DROP TABLE does not work, try moving the v3_zone_date_cpm7k.frm and other files to a different location and restarting the server. Then import the dump file

The last step is in regards to the error message telling you you have an orphaned table:

This means that there is an orphaned .frm file without a corresponding table inside InnoDB. You can drop the orphaned .frm file by deleting it manually. [src]

I really hope this is not necessary and you can restore the partition by another means. This is a last resort method.

---

I finally reproduced your initial error. Though it will do little to restore the partition, it might be helpful to understand to keep this from happening in the future (potential issue in how backup/restore process is handled, or how the partitions are created):

• I copied the v3_zone_date_cpm7k to a different location (as a backup).
• issue a DROP TABLE v3_zone_date_cpm7k

• copy the v3_zone_date_cpm7k files BACK to the datadir

  desc v3_zone_date_cpm7k;

  ERROR 1146 (42S02): Table 'v3_zone_date_cpm7k' doesn't exist

[ERROR] Cannot find or open table v3_zone_date_cpm7k#P#p00 from the internal data dictionary of InnoDB though the .frm file for the table exists.

Answer 2

There is a special number registered with each InnoDB table. It's called the tablespace id. It is a number that is assigned upon table creation and registered in the data dictionary inside ibdata1.

There is a way to make this problem go away, but you must jump through many hoops to achieve this. Keep in mind that if you copy an InnoDB table to another disk, perform any kind of DDL, and try to copy the table data back, there is a small possibility of causing the tablespace ids to become mismatched.

The only person I have ever read about in terms of this recovery is Chris Calender, which is the guide you made reference to. I have written about this before.

• Mar 25, 2012 : Why does InnoDB store all databases in one file?
• Apr 23, 2012 : restore table from .frm and .ibd file?
• Sep 28, 2011 : How to Recover an InnoDB table whose files were moved around

I think your problem may have to do with the tablespace id itself. In the Sep 28 post I mentioned, I assisted a DB Hosting Client who found this same reference. His problem was this:

The tablespace_id of this table was 912. He tried to restore an old copy of the .ibd file. It had a tablespace_id far less than 912. Here is what I helped him do:

• Created separate MySQL instance
• Wrote the stored procedure to create and drop the InnoDB table 911 times.
• Created InnoDB table (Now tablespace_id was 912)
• Discarded tablespace
• Switched in the backup .ibd file
• Imported tablespace
• MySQLDumped the table
• Imported the mysqldump into the original server

Client had 30 tables he had to do. I only wrote the stored procedure for him. I didn't stick around for the gory details. Yet, the client got all the InnoDB tables back, matching all tablespace_ids correctly.

In your case, I honestly don't know if Chris Calender's method would work properly on a partitioned table. I guess the idea would be to get all .ibd files in the partition to have identical tablespace_ids.

Answer 3

Problem solved by running the following command from the mysql> prompt:

CREATE TABLE `v3_zone_date_cpm7k` (
  `campaignid` mediumint(9) NOT NULL DEFAULT '0' COMMENT 'sub_campaignid',
  `zoneid` smallint(6) NOT NULL DEFAULT '0',
  `bannerid` mediumint(9) NOT NULL DEFAULT '0',
  `totalclick` mediumint(9) unsigned NOT NULL DEFAULT '0',
  `realclick` mediumint(9) unsigned NOT NULL DEFAULT '0',
  `clickcharge` mediumint(9) NOT NULL DEFAULT '0',
  `totalview` mediumint(9) unsigned NOT NULL DEFAULT '0',
  `viewcharge` mediumint(9) unsigned NOT NULL DEFAULT '0',
  `dt` date NOT NULL DEFAULT '0000-00-00',
  `partnerid` smallint(6) unsigned NOT NULL DEFAULT '0',
  KEY `ix_zoneid` (`zoneid`,`dt`),
  KEY `ix_dt` (`dt`),
  KEY `ix_campaignid` (`bannerid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

then comment out it in the .sql file:

-- DROP TABLE IF EXISTS `v3_zone_date_cpm7k`;
-- /*!40101 SET @saved_cs_client     = @@character_set_client */;
-- /*!40101 SET character_set_client = utf8 */;
-- CREATE TABLE `v3_zone_date_cpm7k` (
--   `campaignid` mediumint(9) NOT NULL DEFAULT '0' COMMENT 'sub_campaignid',
--   `zoneid` smallint(6) NOT NULL DEFAULT '0',
--   `bannerid` mediumint(9) NOT NULL DEFAULT '0',
--   `totalclick` mediumint(9) unsigned NOT NULL DEFAULT '0',
--   `realclick` mediumint(9) unsigned NOT NULL DEFAULT '0',
--   `clickcharge` mediumint(9) NOT NULL DEFAULT '0',
--   `totalview` mediumint(9) unsigned NOT NULL DEFAULT '0',
--   `viewcharge` mediumint(9) unsigned NOT NULL DEFAULT '0',
--   `dt` date NOT NULL DEFAULT '0000-00-00',
--   `partnerid` smallint(6) unsigned NOT NULL DEFAULT '0',
--   KEY `ix_zoneid` (`zoneid`,`dt`),
--   KEY `ix_dt` (`dt`),
--   KEY `ix_campaignid` (`bannerid`)
-- ) ENGINE=InnoDB DEFAULT CHARSET=latin1
-- /*!50100 PARTITION BY RANGE (TO_DAYS(dt))
-- (PARTITION p00 VALUES LESS THAN (0) ENGINE = InnoDB,
--  PARTITION p04 VALUES LESS THAN (734965) ENGINE = InnoDB,
--  PARTITION p05 VALUES LESS THAN (735025) ENGINE = InnoDB,
--  PARTITION MERGER_2012822 VALUES LESS THAN (735102) ENGINE = InnoDB,
--  PARTITION pcurrent_2012822 VALUES LESS THAN (735103) ENGINE = InnoDB,
--  PARTITION pcurrent_2012823 VALUES LESS THAN (735104) ENGINE = InnoDB) */;
-- /*!40101 SET character_set_client = @saved_cs_client */;

and import normally.