Fixing corrupt InnoDB WooCommerce sessions table

November 27, 2018bywebsevendev

I somehow managed to corrput the WooCommerce sessions database table, possibly by manipulating the cart before wp_loaded hook, or perhaps by rebooting the (test) server during a crucial operation.

Regardless, the service mysql start no longer starter the mysql server.
systemctl status mysql.service giving an exit code Failed to start MySQL Community Server..

Looking further into the log files (cat /var/log/mysql/error.log) I could see messages such as:

<ERROR> InnoDB: Index `session_key` of table `dbname`.`wp_27_woocommerce_sessions` is corrupted
<ERROR> InnoDB: InnoDB is trying to free page <page id: space=1114, page number=92> though it is already marked as free in the tablespace! The tablespace free space info is corrupt. You may need to dump your tables and recreate the whole database!
<ERROR> <FATAL> InnoDB: Please refer to http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html for information about forcing recovery.

Edited the mysql configuration file:

nano /etc/mysql/my.cnf

Adding this line below the [mysqld] bracket:

innodb_force_recovery=3

Now after saving the file, I was able to start the mysql server in recovery mode (read only) using service mysql start.

Then I ran a database check using the command:

mysqlcheck --all-databases -u myusername -p

Resulting in a list of tables, some of which have errors:

dbname.wp_27_term_taxonomy                           OK
dbname.wp_27_termmeta                                OK
dbname.wp_27_terms                                   OK
dbname.wp_27_wc_download_log                         OK
dbname.wp_27_wc_webhooks                             OK
dbname.wp_27_woocommerce_api_keys                    OK
dbname.wp_27_woocommerce_attribute_taxonomies        OK
dbname.wp_27_woocommerce_downloadable_product_permissions OK
dbname.wp_27_woocommerce_log                         OK
dbname.wp_27_woocommerce_order_itemmeta              OK
dbname.wp_27_woocommerce_order_items                 OK
dbname.wp_27_woocommerce_payment_tokenmeta           OK
dbname.wp_27_woocommerce_payment_tokens              OK
(((chap-mark-start)))dbname.wp_27_woocommerce_sessions(((chap-mark-end)))
Warning  : InnoDB: The B-tree of index PRIMARY is corrupted.
Warning  : InnoDB: Index session_key is marked as corrupted
error    : Corrupt
dbname.wp_27_woocommerce_shipping_zone_locations     OK
dbname.wp_27_woocommerce_shipping_zone_methods       OK
dbname.wp_27_woocommerce_shipping_zones              OK
dbname.wp_27_woocommerce_tax_rate_locations          OK
dbname.wp_27_woocommerce_tax_rates                   OK

Made note of all the corrupt tables.

Then I backed up the databases with this command:

mysqldump --all-databases -u myusername -p {gt} corrupt.sql

And deleted the corrupt table(s):

mysql -u myusername -p

drop table dbname.wp_27_woocommerce_sessions;

exit

Then I checked again to see that everything was good:

mysqlcheck --all-databases -u myusername -p

Stopped the mysql service:

service mysql stop

Removed the innodb_force_recovery=3 line from the configuration file:

nano /etc/mysql/my.cnf

And started the service up again:

service mysql start

Luckily for me the WooCommerce session data was not something that needed restoring.

Fixing corrupt InnoDB WooCommerce sessions table

Demos

Documentation

Social media

Newsletter