MySQL reports following error: InnoDB: Table `mysql`.`innodb_table_stats` not found

Products

• STARTER PACK-7
• CA Rapid App Security
• CA API Gateway

Issue/Introduction

After upgrade to API Gateway 9.3 / 9.4 the following errors are found in the mysql logs :

2019-04-24T07:15:10.082735Z 0 [ERROR] InnoDB: Table `mysql`.`innodb_table_stats` not found.
2019-04-24T07:15:10.082791Z 0 [Warning] InnoDB: Recalculation of persistent statistics requested for table `ssg`.`service_usage` but the required persistent statistics storage is not present or is corrupted. Using transient stats instead.
 

Environment

Release: L7SGA299000-9.3-API Gateway SOA Gateway-HARDWARE APPLIANCE DUAL CPU
Component:

Resolution

1) check if partition has free space 
df -h 

If /var/lib/mysql partition is used >90% try to open up some space by deleting ssgbin-log.* and ssgrelay-bin.* files (Keep the 2 most current ones if possible) 

2) in mysql run 
use mysql; 
mysql drop table innodb_index_stats; 
mysql drop table innodb_table_stats; 
mysql drop table slave_master_info; 
mysql drop table slave_relay_log_info; 
mysql drop table slave_worker_info; 

There might be an error coming back, that the tables cannot be found. This can be ignored. 

3) remove the corresponding files for the tables from filesystem: 
cd /var/lib/mysql/mysql 
rm -f innodb_index_stats.frm 
rm -f innodb_index_stats.ibd 
rm -f innodb_table_stats.frm 
rm -f innodb_table_stats.ibd 
rm -f slave_master_info.frm 
rm -f slave_master_info.ibd 
rm -f slave_relay_log_info.frm 
rm -f slave_relay_log_info.ibd 
rm -f slave_worker_info.frm 
rm -f slave_worker_info.ibd 

4) create the tables again using the queries in the attached file

5) restart mysql server 

6) check on mysql log, if error 
[Warning] InnoDB: Table mysql/innodb_index_stats has length mismatch in the column name table_name. Please run mysql_upgrade 

Run 
mysql_upgrade --force 
and restart mysql server again. Any error displayed during the run of mysql_upgrade can be ignored, if last message is "Upgrade process completed successfully"

7) Rebuild replication as outlined in KB article https://comm.support.ca.com/kb/reinitialize-replication-in-a-multinode-cluster/kb000044402