InnoDB recovery fails: space id 1111 did not exist in memory

Symptoms

1. MySQL fails to start and it is required to fix InnoDB corruption. However, article How to fix InnoDB corruption cases for the MySQL database does not help even with innodb_force_recovery = 6 .

2. The following errors can be found in /var/log/mysql/error.log :

   InnoDB: space id 111 did not exist in memory. Retrying an open.
   /usr/sbin/mysqld(_Z24do_handle_one_connectionP3THD+0x176)[0x7fa6a54ac146]
   InnoDB: error: space object of table 'example_table/usr/sbin/mysqld(handle_one_connection+0x41)[0x7fa6a54ac1a1]
   t/lib/x86_64-linux-gnu/libpthread.so.0(+0x8184)[0x7fa6a4282184]
   _db/c/lib/x86_64-linux-gnu/libc.so.6(clone+0x6d)[0x7fa6a39a5bed]
   a
   Trying to get some variables.
   Some pointers may be invalid and cause the dump to abort.
   tQuery (7fa644004b70): alog_pris an invalid pointer
   oConnection ID (thread ID): 4
   dStatus: NOT_KILLED
   

   AND

   InnoDB: error: space object of table 'example_db/17:24:20 UTC - mysqld got signal 11 ;
   

Cause

Crash of the server.

Resolution

1. Remove all the MySQL data storage files except the mysql folder. For example:

   # rm -rf `ls -d /var/lib/mysql/* | grep -v "/var/lib/mysql/mysql"`
   

2. Restore Plesk-related databases from the latest daily dump:

   # cp /var/lib/psa/dumps/mysql.daily.dump.0.gz /root/
   
   # gunzip /root/mysql.daily.dump.0.gz
   
   # MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysql -uadmin < /root/mysql.daily.dump.0
   

3. Restore users' databases from the backup (if any):

   • Find websites and related databases executing the following MySQL query:

     # MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysql -uadmin psa -e"select d.name, db.name from domains d , data_bases db where db.dom_id=d.id"
     
     +---------------+--------------+
     
     | name          | name         |
     
     +---------------+--------------+
     
     | example.com   | example_db   |
     
     +---------------+--------------+
     

   • Recreate the database itself:

     # MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysql -uadmin psa -e"create database example_db"
     

   • Go to: Domains > example.com > Backup Manager .

   • Choose the latest valid backup.

   • Set Type of object to restore > Database .

   • Move the database to selected.

   • Click Restore .