Return to Question

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

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.

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

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: