How to resolve MySQL database errors on system tables after removing ibdata files in MYSQL 5.7 and above !
search cancel

How to resolve MySQL database errors on system tables after removing ibdata files in MYSQL 5.7 and above !

book

Article ID: 137801

calendar_today

Updated On:

Products

CA API Gateway Precision API Monitoring Module for API Gateway (Layer 7) CA API Gateway Enterprise Service Manager (Layer 7) CA Microgateway CA API Gateway

Issue/Introduction

Starting with MySQL 5.7 some internal system tables are using innodb storage which is writing data to the ibdata files in the /var/lib/mysql/ directory .

MySQL 5.7 is set to use innodb per table  for new created tables by default , existing tables however wil continue to use the ibdata file in the MySQL database directory .

 

When you you delete the ibdata files to shrink the file to initial size these system tables get corrupted prevent MySQL from starting or  this  will cause errors like :

 

[ERROR] InnoDB: Table `mysql`.`innodb_table_stats` not found. 

[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.

 

running mysql_upgrade --force will also throw errors for these tables .

Environment

Release : 9.3 and above 

Component : API GATEWAY

Cause

This problem most times occurs after upgrading from mysql 5.5 to mysql 5.7 during the upgrade to Gateway 9.3 or above and trying to move all tables to use innodb per table according document 

https://comm.support.ca.com/kb/how-to-move-gateway-database-tables-from-the-system-tablespace-to-its-own-tablespace-after-upgrade-from-9-2-to-9-3/KB000074485

or when the ibdata files are deleted on mysql 5.6 and above to reduce the size of these files .

Resolution

 

!! ! Don;t delete the ibdata files on mysql 5.7 anymore !!! 

 

If you did not delete the ibdata file yet and you need to reduce the ibdata file size and move to innodb per table after upgrading to  MySQL 5.7 

Verify if there are no problems with system tables in MySQL by running :

              mysql_upgrade --force 

No error should be reported , if you get any errors see procedure below  'If the current MySQL database is already corrupted

 

Initialize MySQL data directory and restore databases 

 

Use the following steps to move the existing database tables to use there own ibd data file .

and init the current ibdata file to reduce to initial size.

 

If possible create snapshot before proceeding when running on VMware. 

 

To reduce space and time clear the audit.tables before running the procedure :

https://comm.support.ca.com/kb/how-to-purge-audit-data-in-gateway-9-3-and-newer/kb000074486

 

Stop the gateway service 

     service ssg stop

 

Stop the replication on both nodes if this is a cluster 

     mysqladmin stop-slave 

 

Backup all databases 

     mysqldump --all-databases > /root/all-db-backup.sql

 

No error should be reported , if you get any errors see procedure below !

 

Backup ssg Database separately as backup , do the same for otk and any other db if installed :

     mysqldump --databases ssg | gzip > /root/ssg-db-backup.sql.gz

     mysqldump --databases otk_db | gzip > /root/otk-db-backup.sql.gz

     mysqldump --databases mysql  > /root/mysql-db-backup.sql 

 

Stop the mysql service :

     service mysql stop 

 

Delete the MySQL data directory :

      rm -rf /var/lib/mysql/*

 

Create a new inital MySQL data directory :

      mysqld --initialize-insecure --user=mysql

****** if the initialize command doesn't work or just completes with in second and it doesn't create all the files in working directory ( var/lib/mysql ) 
on some gateway 10.1 mysqld may be located under usr/sbin 

so check 
'which mysqld'

after we find out that your mysqld using  'which mysqld' was located under usr/sbin/  initialize of MySQL worked 

usr/sbin/mysqld --initialize-insecure --user=mysql


 



Sample output :

2019-08-02T11:10:54.362652Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).

2019-08-02T11:10:54.365309Z 0 [Warning] You need to use --log-bin to make --binlog-format work.

2019-08-02T11:10:54.677755Z 0 [Warning] InnoDB: New log files created, LSN=45790

2019-08-02T11:10:54.739585Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.

2019-08-02T11:10:54.803364Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 32391ce7-b516-11e9-81c9-005056b17f58.

2019-08-02T11:10:54.806849Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.

2019-08-02T11:10:54.811022Z 0 [Warning] RSA private key file not found: /var/lib/mysql//private_key.pem. Some authentication plugins will not work.

2019-08-02T11:10:54.811045Z 0 [Warning] RSA public key file not found: /var/lib/mysql//public_key.pem. Some authentication plugins will not work.

2019-08-02T11:10:54.811562Z 1 [Warning] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.

 

Start MySQL service: 

      service mysql start 

 

Restore the all databases backup created previously :

      mysql -u root -p < /root/all-db-backup.sql

      Enter password: 

 

(Note : The password is empty as we did not set during init )

 

If you get the following error during the restore :

ERROR 1449 (HY000) at line 8482: The user specified as a definer ('xxxxxxxx'@'%') does not exist

 

Run the following command 

          mysql -root -p  -e 'flush privileges;'

( Note:  root password for MySQL is set to original from backup now )

 

And run the restore  again :

           mysql -u root -p /root/all-db-backup.sql

 

(Note : If the problem persist stop and start MySQL and try to restore again  or start mysql and run flush privileges ) 

 

Restart MySQL service :

      service mysql stop 

      service mysql start 

 

Run mysql_upgrade --force to verify db state :

      mysql_upgrade --force

 

Verify if you can connect to MySQL :

      mysql -u root -p 

 

restart gateway  service 

      service ssg start

 

Verify the ssg and mysqld.log for any errors  :

     cat  /var/log/mysqld.log

     cat /opt/SecureSpan/Gateway/node/default/var/logs/ssg_0_0.log

 

Repeat the procedure on the second node and restart the replication. 

https://techdocs.broadcom.com/us/en/ca-enterprise-software/layer7-api-management/api-gateway/10-1/install-configure-upgrade/configure-a-gateway-cluster/configuring-cluster-database-replication/restart-replication.html


If the current MySQL database is already corrupted :

 

Try to backup the whole MySQL database , if this succeed follow the procedure above .

 

Most of the time this backup will fail on the corrupted system tables and you are unable to create a full backup.  

In that case try to create a fullback by ignoring the errors using the -f option .

 

        mysqldump --all-databases > /root/all-db-backup.sql -f 

 

Ignore the error like :

mysqldump: Got error: 1146: Table 'mysql.engine_cost' doesn't exist when using LOCK TABLES

[root@bledi01-gw94-1 ~]# mysqldump --all-databases > /root/all-db-backup4.sql -f

mysqldump: Got error: 1146: Table 'mysql.engine_cost' doesn't exist when using LOCK TABLES

Error: Couldn't read status information for table engine_cost ()

 

This errors should only occur on system tables not on SSG or OTK tables . 

 

Backup each database separately  like SSG , OTK , do the same for mysql database  in case we run into problems  . 

 

Check existing databases :

     mysql -e "show databases;"

 

     mysqldump --databases ssg  > /root/ssg-db-backup.sql

     mysqldump --databases otk_db > /root/otk-db-backup.sql

     mysqldump --databases mysql  > /root/mysql-db-backup.sql -f

 

Ignore the errors on the backup of the 'mysql' database backup  like :

 

     mysqldump: Got error: 1146: Table 'mysql.engine_cost' doesn't exist when using                                                                                                         

    Error: Couldn't read status information for table engine_cost ()

 

Now continue with procedure 'Initialize MySQL data directory and restore databases'  above  .