How to Fix Missing MySQL Tables in Zimbra (gtid_slave_pos, innodb_index_stats)

After performing emergency maintenance, my Zimbra server experienced issues after migrated to a new host.

The issue was related to the MySQL service, and I used this wiki as a reference for recovery: https://wiki.zimbra.com/wiki/Mysql_Crash_Recovery

After completing the recovery steps from the wiki, I ran into problems with gtid_slave_pos, innodb_index_stats, and innodb_table_stats As follows

To resolve the issue, I took the following recovery actions:

# Stop Zimbra Service

su - zimbra
zmcontrol stop

# Backup DB before recovery

cp -r /opt/zimbra/db /opt/zimbra/db-backup

# Remove innodb and gtid table

cd /opt/zimbra/db/data/mysql/
rm -f innodb_* gtid_slave_pos.*

# Start Zimbra Mysql service

mysql.server start

# Recreate innodb and gtid table

There are 3 tables that need to be created.

mysql mysql -e "CREATE TABLE innodb_index_stats (
         database_name                           VARCHAR(64) NOT NULL,
         table_name                                      VARCHAR(64) NOT NULL,
         index_name                                      VARCHAR(64) NOT NULL,
         last_update                                     TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
         /* there are at least:
         stat_name='size'
         stat_name='n_leaf_pages'
         stat_name='n_diff_pfx%' */
         stat_name                                       VARCHAR(64) NOT NULL,
         stat_value                                      BIGINT UNSIGNED NOT NULL,
         sample_size                                     BIGINT UNSIGNED,
         stat_description                        VARCHAR(1024) NOT NULL,
         PRIMARY KEY (database_name, table_name, index_name, stat_name)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0"

mysql mysql -e "CREATE TABLE IF NOT EXISTS innodb_table_stats (
         database_name                           VARCHAR(64) NOT NULL,
         table_name                                      VARCHAR(64) NOT NULL,
         last_update                                     TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
         n_rows                                          BIGINT UNSIGNED NOT NULL,
         clustered_index_size            BIGINT UNSIGNED NOT NULL,
         sum_of_other_index_sizes        BIGINT UNSIGNED NOT NULL,
         PRIMARY KEY (database_name, table_name)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0"

mysql mysql -e "CREATE TABLE IF NOT EXISTS gtid_slave_pos (
domain_id INT UNSIGNED NOT NULL,
sub_id BIGINT UNSIGNED NOT NULL,
server_id INT UNSIGNED NOT NULL,
seq_no BIGINT UNSIGNED NOT NULL,
PRIMARY KEY (domain_id, sub_id))
COMMENT='Replication slave GTID position'"

The system table is taken from mysql_system_tables.sql. You can find it using the following command:

find /opt/zimbra/ -name mysql_system_tables.sql

# Stop Zimbra mysql service

mysql.server stop

# Start Zimbra Service

zmcontrol start

# Check via zmdbintegrityreport

/opt/zimbra/libexec/zmdbintegrityreport -v

Good Luck 🙂

Source: https://heholdsthekeys.net/doku.php?id=tech_documents:zimbra:fix_database_issues