- 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
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
What can I do now?
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):
DROP TABLE --> still get the "doen't exist"
Move all the table's files to another location
Copy the backup files to corresponding database
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 '
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.
.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