Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a weird issue with an InnoDB table restore. Can you please help me to recover the InnoDB database? In MyISAM it works.

  1. we have the database(/mnt/OLD/var/lib/mysql/ims) with 300 Innodb tables(.frm). The server crashed due to hardware## Heading ## problem and we could able to recover the /var/lib/mysql folder, then we reinstall the new server with same Linux version (6 Update 4)and the same MySQL version 5.5.34 and copied the ims database folder and their 300 innodb tables as well as ibdata1, ib_logfile0, ib_logfile1 to /var/lib/mysql. When we try the ‘select’ query, it failed with ‘ERROR 1146 (42S02): Table 'ims.users' doesn't exist’.

  2. We have a 6months old backup and restored it using mysqldump. Then tried the following to correct internal metadata id, but not works.

When I tried to mysql> ALTER TABLE users DISCARD TABLE,

The mysqld.log shows

131119 15:42:06 InnoDB: Error: table 'ims'.'users' InnoDB: is in the system tablespace 0 which cannot be discarded

Tried the following:

[root@localhost ims]# /etc/init.d/mysqld restart

Stopping mysqld:                                           [  OK  ]

Starting mysqld:                                           [  OK  ]

[root@localhost ims]# mysql -u root -p
Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.34 MySQL Community Server (GPL) by Remi

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| backupdb           |

| ims                |

| mysql              |

| performance_schema |

| temp_ims           |

| test               |

+--------------------+

7 rows in set (0.00 sec)

mysql> use ims;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> select count(*) from users;

+----------+

| count(*) |

+----------+

|       70 |

+----------+

1 row in set (0.00 sec)


mysql>
mysql> create table newusers like users;

Query OK, 0 rows affected (0.07 sec)


mysql>

mysql> alter table users rename oldusers;

Query OK, 0 rows affected (0.03 sec)


mysql> alter table newusers rename users;

Query OK, 0 rows affected (0.02 sec)


mysql> drop table oldusers;

Query OK, 0 rows affected (0.02 sec)


mysql> alter table users DISCARD TABLESPACE;

ERROR 1030 (HY000): Got error -1 from storage engine

mysql> use ims;

Database changed

mysql> alter table ims.users DISCARD TABLESPACE;

ERROR 1030 (HY000): Got error -1 from storage engine

mysql> alter table ims.users import TABLESPACE;

ERROR 1030 (HY000): Got error -1 from storage engine

mysql> select count(*) from users;

+----------+

| count(*) |

+----------+

|        0 |

+----------+

1 row in set (0.00 sec)


mysql> Bye


[root@localhost ims]# cp /mnt/OLD/var/lib/mysql/ims/users.frm /var/lib/mysql/ims/

[root@localhost ims]# /etc/init.d/mysqld restart


Stopping mysqld:                                           [  OK  ]

Starting mysqld:                                           [  OK  ]

[root@localhost ims]#

[root@localhost ims]# mysql -u root -p

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 2

Server version: 5.5.34 MySQL Community Server (GPL) by Remi


Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.


Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.


Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


mysql> use ims;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed


mysql> select count(*) from users;

ERROR 1146 (42S02): Table 'ims.users' doesn't exist

mysql> Bye

[root@localhost lib]#

[root@localhost lib]# /etc/init.d/mysqld stop

Stopping mysqld:                                           [  OK  ]

[root@localhost lib]#

[root@localhost lib]# rm -f /var/lib/mysql/ib*

[root@localhost lib]# cp /mnt/OLD/var/lib/mysql/ib* /var/lib/mysql/

[root@localhost lib]# /etc/init.d/mysqld start

Starting mysqld:                                           [  OK  ]

[root@localhost lib]# mysql -u root -p

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 2

Server version: 5.5.34 MySQL Community Server (GPL) by Remi


Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.


Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.


Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


mysql> use ims;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> select count(*) from users;

ERROR 1146 (42S02): Table 'ims.users' doesn't exist

mysql>
share|improve this question

Your Answer

 
discard

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

Browse other questions tagged or ask your own question.