How to resolve MySQL database errors on system tables after removing ibdata files in MYSQL 5.7 and above !
Article ID: 137801
Updated On:
Products
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
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] [email protected] 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.
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
[[email protected] ~]# 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 .
Feedback
