MySQL errors, any ideas?

Discuss your pilot or production implementation with other Zimbra admins or our engineers.
User avatar
jasggomes
Advanced member
Advanced member
Posts: 76
Joined: Sat Sep 13, 2014 12:59 am
Location: Lisbon, PT
ZCS/ZD Version: Release 8.7.11.GA.1854.UBUNTU14.64
Contact:

MySQL errors, any ideas?

Postby jasggomes » Tue Jul 02, 2019 11:19 am

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


User avatar
JDunphy
Outstanding Member
Outstanding Member
Posts: 459
Joined: Fri Sep 12, 2014 11:18 pm
Location: Victoria, BC
ZCS/ZD Version: 8.7.11_P12 RHEL6 Network Edition
Contact:

Re: MySQL errors, any ideas?

Postby JDunphy » Tue Jul 02, 2019 2:36 pm

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
Posts: 3
Joined: Tue Jun 25, 2019 7:31 pm

Re: MySQL errors, any ideas?

Postby samantaa » Tue Jul 02, 2019 2:51 pm

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.

Return to “Administrators”

Who is online

Users browsing this forum: Google [Bot] and 24 guests