Mysql – get table does not exist in thesql after adding new hard disk

MySQL

I have added a new hard disk to my VM where my mysql server were located on. Suddenly, mysql returns table does not exist for all my databases; even though I can see all tables with show tables command. 

mysql> use mydb;
mysql> show tables;
+-----------------------+
| Tables_in_mydb        |
+-----------------------+
| mytable               |
+-----------------------+
1 row in set (0.00 sec)

mysql> select * from mytable;
ERROR 1146 (42S02): Table 'mydb.mytable' doesn't exist

mysql> select * from mydb.mytable;
ERROR 1146 (42S02): Table 'mydb.mytable' doesn't exist

I have tried to restore tables with solutions provided here:

Using mysqlcheck --repair:

root@mylab:/var/lib/mysql# mysqlcheck -umyuser -pmypass --repair -- 
databases mydb
mydb.mytable
Error    : Table 'mydb.mytable' doesn't exist
status   : Operation failed

Using REPAIR TABLE:

mysql> use mydb;
mysql> REPAIR TABLE mytable;
+---------------------------+--------+----------+-------------------------------------------------+
| Table                     | Op     | Msg_type | Msg_text                                        |
+---------------------------+--------+----------+-------------------------------------------------+
| mydb.mytable              | repair | Error    | Table 'mydb.mytable' doesn't exist              |
| mydb.mytable              | repair | status   | Operation failed                                |
+---------------------------+--------+----------+-------------------------------------------------+
2 rows in set (0.00 sec)

Using ALTER TABLE t1 ENGINE = InnoDB;:

mysql> use mydb;
mysql> ALTER TABLE mytable ENGINE = InnoDB;
ERROR 1146 (42S02): Table 'mydb.mytable' doesn't exist

Using mysqldump:

root@mylab:/var/lib/mysql# mysqldump -umyuser -pmypass mydb > /root/mydb.sql
mysqldump: Got error: 1146: Table 'mydb.mytable' doesn't exist when using LOCK TABLES

I have tried repairing with these solutions. Since mysqlcheck had failed before, I tried myisamchk for all files starting with mytable in /var/lib/mysql/mydb/; the command output was is not a MyISAM-table for all of them.

EDIT 1:

I checked database management files in /var/lib/mysql/ which is set as datadir in /etc/mysql/my.cnf; all of them were listed there:

root@lab-188:/var/lib/mysql# ls -lat | grep ib
-rw-rw----  1 mysql mysql  5242880 May 24 12:22 ib_logfile0
-rw-rw----  1 mysql mysql 18874368 May 24 12:22 ibdata1
-rw-rw----  1 mysql mysql  5242880 May 24 11:08 ib_logfile1

EDIT 2:

I also tried following solutions:

Restart mysql service:

Still get the same error.

Fix mysql directory's permissions:

chmod -R 755 /var/lib/mysql/
chown -R mysql:mysql /var/lib/mysql/

Nothing changed.

Try loading database files in another server:

I copied the whole directory of database, /var/lib/mysql/mydb/, to another server which has mysql too, restarted mysql there and logged in to it.

The database was shown there, but selecting content of its tables returned same error as previous server:

mysql> select * from mytable;
ERROR 1146 (42S02): Table 'mydb.mytable' doesn't exist

EDIT 3:

Another solution which I found here, was to disble/enable FOREIGN_KEY_CHECKS which didn't help in my case:

SET FOREIGN_KEY_CHECKS=0;
SET FOREIGN_KEY_CHECKS=1;

Best Answer

You can't move InnoDB tables around by moving files. There is critical info in ibdata1 that you lose by simply moving /var/lib/mysql/mydb/.

Nor does it work for a database, such as /var/lib/mysql/mydb/.

Related Question