Thinking of a solution to fix the issue, “Access to system table ‘mysql.innodb_index_stats’ is rejected”? With our MySQL Support Services, bobcares assist you with the solution.
Access to system table ‘mysql.innodb_index_stats’ is rejected. Why?
The
mysql.innodb_index_stats
Copy Code
table, which has many rows for each index, contains information about specific InnoDB Persistent Statistics. We can manually change this table and the associated mysql.innodb_table_stats
Copy Code
. However, sometimes, as in the case when restoring the older DB into a Fresh installation, we may see the below error:
Use-case And Fixes
This section will show some of the use cases and solutions to fix the issue.
Use-case 1
The user was trying to move the web server AMPPS to the latest version. It comes with Mysql 8.0 by default. The user faced the below issue:
"ERROR 3554 (HY000) at line 33128"
Fix
1. Firstly, copy the entire AMPPS folder to another Mac.
2. Chown the folder on the new Mac using the below code:
sudo chown -R $(whoami):admin /Applications/AMPPS
Copy Code
3. Then access the AMPPS. Then run the below code to export the database:
/Application/AMPPS/mysql/bin/mysqldump -u root -p --all-databases > database.sql
Copy Code
4. Move the database. Then run the below code on the terminal:
/Applications/Ampps/mysql/bin/mysql -u root -p --force < new-database.sql
Copy Code
Here, the “–force” allows the issues like this to be ignored.
Use-case 2
The user changed to the latest version of MySQL from the existing version MySQL is 5.7.23. Then dumped the entire DB into a file. However, when restoring the 5.7.23 DB to a new MySQL 8.0.12 installation, the below issue appears:
"ERROR 3554 (HY000) at line 15915"
Fix
Here, the user dumped the mysql.innodb_index_stats and was not able to restore that expected table. So we advise making a dump of the DB only, not the MySQL DB with system tables. The code would look like this:
mysqldump -u USERNAME -pPASSWORD --ignore-table=mysql.innodb_index_stats all-databases > database-backup.db
Copy Code
--ignore-table=db_name.tbl_name
Copy Code
Use-case 3
The user saw the below error while importing a .sql file.
"ERROR 3554 (HY000) at line 318"
Fix
The solution was the same as above. We request to make a new dump/backup of the old DB. Then we also asked to remove those InnoDB tables from the target. We can do this by using the –ignore-table parameter on the command line:
mysqldump -u root -p --all-databases --ignore-table=mysql.innodb_index_stats --ignore-table=mysql.innodb_table_stats > dump.sql
Copy Code
Then restore the backup on the new database using the code below:
mysql -u root -p < dump.sql
Copy Code
[Looking for a solution to another query? We are just a click away.]
Conclusion
To summarize, we have provided three use cases of the error and the available fixes for each case briefly in this article.
PREVENT YOUR SERVER FROM CRASHING!
Never again lose customers to poor server speed! Let us help you.
Our server experts will monitor & maintain your server 24/7 so that it remains lightning fast and secure.
var google_conversion_label = "owonCMyG5nEQ0aD71QM";
0 Comments