Plesk Error: innodb-page-size mismatch in tablespace








Sometimes, we see this error when one or more MySQL databases is corrupted.

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

Here at Ibmi Media, as part of our Server Management Services, we regularly help our Customers to fix related Plesk errors.


Nature of 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:

i. 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)


ii. 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 Plesk Error: innodb-page-size mismatch in tablespace ?

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


How to 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


How to restore a database In Linux ?

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

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

# service mysql stop

3. 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/

4. 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

5. Eventually, we start the MySQL service.

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

7. The value can be from 1 to 6.

8. 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.

9. 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

10. However, if the dump fails with the error:

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

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

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

12. Then, 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:

i. 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”`

ii. We remove the innodb_force_recovery option from the MySQL configuration file.

iii. Then we restart the MySQL service:

# service mysqld restart

iv. We check the MySQL log file for any errors.

v. 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


How to restore a database 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 to fix database Mysql issues? We'd be happy to assist. ]



Conclusion

This article covers methods to resolve Plesk Error: innodb-page-size mismatch in tablespace.


For Linux Tutorials

We create Linux HowTos and Tutorials for Sys Admins. Visit us on LinuxAPT.com

Also for Tech related tips, Visit forum.outsourcepath.com or General Technical tips on www.outsourcepath.com