Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question
2  
This stackoverflow.com/questions/3218872/… may help –  Iain Aug 28 '12 at 15:58
    
@Iain thanks; no affect unfortunately. –  Oneiroi Aug 28 '12 at 16:04
add comment

1 Answer

up vote 2 down vote accepted

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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