mysqldump: Got error: 1017: Can't find file: 'drupal_install_test' (errno: 2) when using LOCK TABLES

Question

I'm trying to backup a drupal site database but I'm having some issues. When I ran the following command:

mysqldump -uroot -p drupaSite > drupaSite.sql

I get the following error:

mysqldump: Got error: 1017: Can't find file: 'drupal_install_test' (errno: 2) when using LOCK TABLES

if I tried to query the table I get the same error:

mysql> select * from drupal_install_test;  
ERROR 1017 (HY000): Can't find file: 'drupal_install_test' (errno: 2)

I check the status of the table in the database:

show table status from drupaSite;

I get the following output:

| drupal_install_test     | NULL   |    NULL | NULL       |   NULL |           NULL |        NULL |               NULL |         NULL |      NULL |           NULL | NULL                | NULL                | NULL                | NULL            |     NULL | NULL           | Can't find file: 'drupal_install_test' (errno: 2) | 

I ran the following query:

SELECT * FROM information_schema.tables WHERE table_name='drupal_install_test'\G

I get the following output:

*************************** 1. row ***************************
  TABLE_CATALOG: NULL
   TABLE_SCHEMA: drupaSite
     TABLE_NAME: drupal_install_test
     TABLE_TYPE: BASE TABLE
         ENGINE: NULL
        VERSION: NULL
     ROW_FORMAT: NULL
     TABLE_ROWS: NULL
 AVG_ROW_LENGTH: NULL
    DATA_LENGTH: NULL
MAX_DATA_LENGTH: NULL
   INDEX_LENGTH: NULL
      DATA_FREE: NULL
 AUTO_INCREMENT: NULL
    CREATE_TIME: NULL
    UPDATE_TIME: NULL
     CHECK_TIME: NULL
TABLE_COLLATION: NULL
       CHECKSUM: NULL
 CREATE_OPTIONS: NULL
  TABLE_COMMENT: Can't find file: 'drupal_install_test' (errno: 2)

I ran the following query:

CHECKSUM TABLE drupal_install_test;

I got the following output:

+-------------------------------+----------+
| Table                         | Checksum |
+-------------------------------+----------+
| drupaSite.drupal_install_test |     NULL | 
+-------------------------------+----------+
1 row in set, 1 warning (4.34 sec)

I ran the following query:

CHECK TABLE drupal_install_test;

and I get the following output:

   +-------------------------------+-------+----------+---------------------------------------------------+
    | Table                         | Op    | Msg_type | Msg_text                                          |
    +-------------------------------+-------+----------+---------------------------------------------------+
    | drupaSite.drupal_install_test | check | Error    | Can't find file: 'drupal_install_test' (errno: 2) | 
    | drupaSite.drupal_install_test | check | error    | Corrupt                                           | 
    +-------------------------------+-------+----------+---------------------------------------------------+

2 rows in set (0.02 sec)

My question for you guys is how can I fix this in a way I can backup the database and restore it in another server. The site is working just fine I need to migrate the server. I would really appreciate your help guys.

Answer 1

You apparently have a missing drupal_install_test in your database schema. As the name says, it's a test table that is created at installation time to test the performance of the database and of the database user you provided for your site. At the end of the installation process, it gets dropped from the database.

The problem here is that the table has been dropped but mysql still thinks it exists. The reason why that table has remained in information_schema.tables should definitely be investigated and may reflect some issues in the database or in your server's configuration.

That said, if drupal_install_test is the only corrupted table in your database, you can try and drop it and proceed with your backup. I recommend that you make a full backup of the mysql data directory (in a typical linux installation /var/lib/mysql) and issue a DROP TABLE drupal_install_test command. Mysql should be able to restore the integrity of the schema tables and you should be able to proceed with your backup.