4

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?

3

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.

  • 1
    adding --routines to mysqldump would be useful too. – endo64 Apr 16 '17 at 19:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.