Home / server / Questions / 422061
Accepted
Oneiroi
Asked: 2012-08-29 07:42:13 +0800 CST

mysql - can't drop table does not exist, can't create table exists

  • 772

After a server crash we are having some very strange issues with one particular table reference.

Opting for a restoration from backup the database was dropped and a backup SQL dump loaded, only this fails on the create table for cache_content with the error "table already exists"

mysql> create table cache_content( id int NOT NULL DEFAULT 0 ) ENGINE=InnoDB DEFAULT CHARSET=utf8; ERROR 1005 (HY000): Can't create table '****.cache_content' (errno: -1) mysql> drop table cache_content; ERROR 1051 (42S02): Unknown table 'cache_content'

Oddly the drop table, removed the .frm but not the .ibd file (if it exists), the create table will create the .ibd file but not the .frm file.

I've tried numerous methods for restoration, including importing the dump into a new database (completed without issue), shuttind down mysql and copying the relevant .frm and .ibd files, then using idbconnect to attempt to attach this "known good" version:

... Space id: 1952673645 (0x74636F6D) Next record at offset: 74 TABLE_ID of `****/`.`cache_content` can not be 0 ...

Checking the related tables in information_schema, I can see this is the case and the TABLESPACE has been assigned to 0

mysql> select * from INNODB_SYS_TABLES where `SCHEMA`="*****";
+----------+--------+--------------------------+------+--------+-------+
| TABLE_ID | SCHEMA | NAME                     | FLAG | N_COLS | SPACE |
+----------+--------+--------------------------+------+--------+-------+
...

|    19791 | *****  | cache_content            |    1 |      9 |     0 |
+----------+--------+--------------------------+------+--------+-------+
N rows in set (0.01 sec)

mysql> select * FROM INNODB_SYS_INDEXES where TABLE_ID=19791;
+----------+--------+----------+------+----------+---------+-------+
| INDEX_ID | NAME   | TABLE_ID | TYPE | N_FIELDS | PAGE_NO | SPACE |
+----------+--------+----------+------+----------+---------+-------+
|     7919 | expire |    19791 |    0 |        1 |  311158 |     0 |
+----------+--------+----------+------+----------+---------+-------+
1 row in set (0.00 sec)

Server Version: Percona-Server-server-55-5.5.27-rel28.0.291.rhel6.x86_64

Now I'm fairly sure from the reading I've done into that removing ibdata1 ib_logfile* may be the only way of cleaning up this "ghost" reference.

My Question: Is there any way to clean up these ghost references to allow the table to be restored from backup?

mysql

1 Answers

  1. Best Answer

    In the end I opted to restore all databases from backups,

    1. mysqldump --all-databases --triggers > /path/to/dumpfile.sql
    2. service mysql shutdown
    3. rm -rf /path/to/datadir && mkdir /path/to/datadir && chown mysql.mysql /path/to/datadir
    4. iptables -I INPUT -p tcp --dport 3306 -j REJECT && service mysql start
    5. mysql_install_db --datadir=/path/to/datadir
    6. mysql < /path/to/dumpfile.sql
    7. Drop and restore any databases that were not intact during 1. from individual backups
    8. service mysql restart && service iptables restart

    I'm leaving this answer here and the question unaccepted for the next week; I am hoping someone can provide a solution where the entire ibdata does not need rebuilding.

    • 3