MySQL errors, any ideas?

[jasggomes]

Hi to all,

Doing a check on an old 8.7.11 latest patch server I come across this on mysql_error.log::

Code: Select all

2019-07-02 12:22:49 7f92de67cb00 InnoDB: Error: Column last_update in table "mysql"."innodb_table_stats" is INT UNSIGNED NOT NULL but should be BINARY(4) NOT NULL (type mismatch).
2019-07-02 12:22:49 7f92de67cb00 InnoDB: Error: Fetch of persistent statistics requested for table "mboxgroup98"."tombstone" but the required system tables mysql.innodb_table_stats and mysql.innodb_index_stats are not present or have unexpec
ted structure. Using transient stats instead.

Any ideas?

Thanks
JG

[JDunphy]

It been a long long time but I checked my notes and I had something similar when I did my upgrade to from 8.7.1. Here are my notes:

Code: Select all

-----------------------------------------
from: https://forums.zimbra.org/viewtopic.php?t=60288

/opt/zimbra/log/mysql_error.log

nnoDB: Error: Fetch of persistent statistics requested for table "mboxgroup62"."mail_item" 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 behavior is caused by bug in Zimbra installation script which doesn't perform mysql_upgrade during Zimbra upgrade process.

Resolution:
1. Obtain mysql root password:
$ zmlocalconfig -s | grep mysql | grep password

2. Create missing directory and symbolic link:
$ mkdir /opt/zimbra/data/tmp/mysqldata
$ ln -s /opt/zimbra/data/tmp/mysql/mysql.sock /opt/zimbra/data/tmp/mysqldata/mysql.sock

3. Perform mysql_upgrade
$ /opt/zimbra/common/bin/mysql_upgrade -u root -p

[samantaa]

It seems the problem in there is the timestamps fields caused by a bug in the database this happened usually after the update as the database is updating all the fields. You can try this fixed:
1. Re-creating the database by, exporting and importing then restart your mysql.
2. Repair your database (make sure you stop all the services that use mysql such as apache):

Code: Select all

mysqldump="mysqldump --events --quick --single-transaction"
exclude="information_schema|performance_schema"

cd /tmp

# Backup and re-import each database on the system
for db in $(mysql -e "show databases;" -s --skip-column-names | grep -vE "($exclude)")
do
        echo "Repairing database $db"
        $mysqldump $db > $db.sql
        mysql $db < $db.sql
done

Before doing this make sure you have a whole back of your database. Hope this helps.