Stuck with the Plesk Error: innodb-page-size mismatch in tablespace? We can help you.

We come across this error when one or more MySQL databases is corrupted.

As part of our Server Management Services, we assist our customers with several Plesk queries.

Today, let us see how we can fix this error.

Plesk Error: innodb-page-size mismatch in tablespace

Few common symptoms of the error are as follows:

(a) MySQL service fails to start with the following errors:

1. In Plesk for Linux, in MySQL log file /var/log/mysql/error.log, /var/log/mysqld.log, or /var/log/mariadb/mariadb.log:

   [ERROR] InnoDB: innodb-page-size mismatch in tablespace ./example_db/table_name.ibd (table example_db/table_name)
   …
   InnoDB: Error: could not open single-table tablespace file ./example_db/table_name.ibd

   journalctl shows:

   InnoDB: space header page consists of zero bytes in tablespace ./example_db/table_name.ibd (table example_db/table_name)

2. In Plesk for Windows, in MySQL log file %plesk_dir%Databases\MySQL\Data\server_hostname.err:

   [ERROR] InnoDB: checksum mismatch in tablespace .\mysql\slave_relay_log_info.ibd (table mysql/slave_relay_log_info)

(b) Unable to access a MySQL database via phpMyAdmin in Domains > example.com > Databases:

#2002 - The server is not responding (or the local server's socket is not correctly configured).

In addition, one of the following errors is found in the MySQL log file:

[ERROR] Table table_name contains fewer indexes inside InnoDB than are defined in the MySQL .frm file. Have you mixed up .frm files from different installations? See http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting.html

Or

InnoDB: MySQL and InnoDB data dictionaries are out of sync.

(c) When we attempt to open database overview in Domains > example.com > Databases, we face:

Internal error ;-P
{"status":"error","statusMessages":[{"status":"error","class":"","content":"SQLSTATE[HY000] [2002] No such file or directory<br><a href='http://kb.plesk.com/plesk-error/search?metaId=0faa7e4d9d6e1d1c8182d642d419756f&messageId=2eb8d2699e722503bf79ae1c7819dc4e&file=Abstract.php&line=144&type=Zend_Db_Adapter_Exception&version=17.8.11&message=SQLSTATE%5BHY%5D+%5B%5D+No+such+file+or+directory' target='_blank'>Search for related Knowledge Base articles</a>","source":null,"title":"Error"}]}

(d) Plesk backup fails with the error:

Database example_db is skipped from backup due to error: Unable to connect to database: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) at /usr/local/psa/PMM/agents/shared/Db/MysqlDbiBackend.pm line 62.

(e) Repairing the database using mysqlcheck fails:

# MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysqlcheck -u admin example_db
status : Operation failed
table_name
Error : Table 'table_name' doesn't exist

OR

# MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysqlcheck -u admin --all-databases
mysqlcheck: Got error: 2013: Lost connection to MySQL server during query when executing 'CHECK TABLE ... '
example_db.mytable
warning : Table is marked as crashed
error : Can't read key from filepos: 8936448
Error : Incorrect key file for table './example_db/table_name.MYI'; try to repair it
error : Corrupt

In addition, dumping database may also fail with one of the above errors.

How to fix it?

1. Initially, we log in to Plesk.
2. Then we go to Tools & Settings > Backup Manager (or Domains > example.com > Backup Manager for a subscription backup).
3. We click on a backup that contains the database to restore.
4. Select Type of object to restore as Database > select a subscription > select the database to restore.
5. Finally, we click Restore to begin the restoration.

Restore a database from Plesk backup via command-line

1. To do so, we connect to a Plesk server via SSH.
2. Find the current backup location:

   # grep “DUMP_D” /etc/psa/psa.conf
   DUMP_D /var/lib/psa/dumps

3. Then we find the database to restore with the path from step 2 and a database name using the command below:

   # find /var/lib/psa/dumps/ | grep example_db

   # find /var/lib/psa/dumps/ | grep example_db
   /var/lib/psa/dumps/clients/john_doe/domains/example.com/databases/example_db
   /var/lib/psa/dumps/clients/john_doe/domains/example.com/databases/example_db/backup_xxxxxxxxx.tgz

4. We then extract the database to the /root directory with a full path to the database from step 3:

   # tar -xvf /var/lib/psa/dumps/clients/john_doe/domains/example.com/databases/example_db/backup_xxxxxxxxx.tgz -C /root/

5. Finally, to restore a database from this dump, we run:

   # MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysql -uadmin db_example < /path/to/database_dump

If there is no backup of the corrupted database, recover it using the InnoDB force recovery scenario

• In Linux

First and foremost, we connect to the server via SSH.

Then we force InnoDB Recovery to stop the affected MySQL service:

# service mysql stop

We back up all the MySQL data storage files. Its default location will be /var/lib/mysql/

For example,

# mkdir /root/mysql_backup
# cp -a /var/lib/mysql/* /root/mysql_backup/

Under the [mysqld] section in the MySQL configuration file, we can set the innodb_force_recovery value. This will allow us to start MySQL service and create all database dump.

For example,

# vi /etc/my.cnf
[mysqld]
innodb_force_recovery = 2

Eventually, we start the MySQL service.

However, the service may fail to start. Then we set the parameter innodb_force_recovery to a greater value and try again.

The value can be from 1 to 6.

Suppose the service fails to start with an error:

InnoDB: Waiting for the background threads to start

In such a case, we add directive innodb_purge_threads.

Then we try to dump all databases:

# MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysql -Ns -uadmin psa -Ne”show databases”|grep -v information_schema | grep -v performance_schema > /root/db_list.txt
# mkdir /root/db_backup/
# cat /root/db_list.txt | while read i; do MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysqldump -uadmin “$i” –routines –databases > /root/db_backup/”$i”.sql; echo $i; sleep 5; done

However, if the dump fails with the error:

Incorrect information in file: ‘./psa/APSApplicationItems.frm’ when using LOCK TABLES”`

Then we increase the innodb_force_recovery value, restart the MySQL service, and try again.

Our Support Techs recommend to dump databases one by one. Therefore, we don’t need to go through restore of all databases if it fails.

Moving ahead, if we are unable to dump the databases, then we try to Copy table content or Restore from the backup.

Let us see how we can do that.

Initially, we 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”`

We remove the innodb_force_recovery option from the MySQL configuration file.

Then we restart the MySQL service:

# service mysqld restart

We check the MySQL log file for any errors.

Then we proceed to restore databases from the dumps.

For example,

# for db in `cat /root/db_list.txt`; do echo -e “Importing $db…”; MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysql -uadmin < /root/db_backup/$db.sql; done

• In Windows

1. Initially, we connect to the server via RDP.
2. We create a backup of all current Plesk-related MySQL data and databases at %plesk_dir%MySQL\Data\.
3. Then we open the %plesk_dir%MySQL\my.ini file.
4. In the [PleskSQLServer] section, we add:

   innodb_force_recovery = 1
   skip-grant-tables

5. Then we attempt to start PleskSQLserver.
6. If it fails, we set innodb_force_recovery to a greater value and try again.
7. Once it starts in force recovery mode, we create dumps of all databases via the PowerShell using:

   PS cd $env:plesk_dir\mysql\bin\
   mkdir .\data_restore
   .\mysql.exe -uadmin -P8306 -sNe “SHOW DATABASES” | findstr /V performance_schema | findstr /V information_schema > c:\db_list.txt
   foreach ($var in get-content c:\db_list.txt) {.\mysqldump.exe -uadmin -P8306 $var > .\data_restore\$var.sql}

8. Then we stop the PleskSQLserver service.
9. We remove all MySQL data except the MySQL folder and error logs from %plesk_dir%MySQL\Data\ directory.
10. In addition, we remove the line innodb_force_recovery from the my.ini file
11. Later, we start PleskSQLserver.
12. Restore the databases from the dumps made:

    PS foreach ($var in get-content c:\db_list.txt) {.\mysql.exe -uadmin -P8306 -e”create database $var”}
    foreach ($var in get-content c:\db_list.txt) {get-content .\data_restore\$var.sql | .\mysql.exe -uadmin -P8306 $var}

13. Finally, we remove the skip-grant-tables line from the my.ini file and restart PleskSQLserver.

[Find it hard with the procedures? We’d be happy to assist]

Conclusion

In short, we saw how our Support Techs fix the Plesk Error.