The context: a ScalarX client carried out an update from an old version of MySQL to a slightly less old version of MariaDB (10.5) on a server (in practice a CentOS virtual machine on Proxmox VE).
This update was carried out for reasons of application compatibility: with a little more than 350 hosted applications, MariaDB 10.5 offered an ideal compromise by being compatible with the oldest as well as the most recent versions of its clients' applications and sites.
The client does application monitoring of each website / application it hosts and everything was working well. Today, 25 December 2024, ScalarX monitoring reports excessive use of the system partition, so we step in to investigate and fix the problem.
The alert comes from a MariaDB error log that gradually filled up until it reached the alert threshold. It contained errors such as:
2024-12-25 17:39:20 46728158 [ERROR] InnoDB: Column last_update in table `mysql`.`innodb_table_stats` is BINARY(4) NOT NULL but should be INT UNSIGNED NOT NULL (flags mismatch).
2024-12-25 17:39:20 46728158 [ERROR] InnoDB: Fetch of persistent statistics requested for table `xxxxxxx`.`LUKwsQnl_actionscheduler_actions` but the required system tables mysql.innodb_table_stats and mysql.innodb_index_stats are not present or have unexpected structure. Using transient stats instead.
This type of incident sometimes happens when mysql_upgrade did not run correctly during the update. So we first try to force the upgrade, but all the tables are OK, no corrections are made.
After taking two snapshots of the VM, we then proceed to the deletion of the problematic tables:
DROP TABLE IF EXISTS mysql.innodb_table_stats;
DROP TABLE IF EXISTS mysql.innodb_index_stats;
This operation is followed by a restart of the MariaDB server and finally by running mysql_upgrade –force again in order to make sure the tables are recreated cleanly.
Once these operations were done, no further errors were reported.
While we run checks on the MariaDB / MySQL server side, the client uses their application monitoring to check that the sites and applications are working properly:
SHOW TABLES LIKE 'innodb_%';
DESCRIBE innodb_table_stats;
DESCRIBE innodb_index_stats;
SELECT COUNT(*) FROM innodb_table_stats;
SELECT COUNT(*) FROM innodb_index_stats;
Everything is OK on this Christmas Day, just another day ;). Remember to back up *before* attempting this type of operation.
—
Christophe Casalegno
You can follow me on: Telegram | Facebook | LinkedIn | X | YouTube | Twitch
1 comment