Bobcares

Access to system table ‘mysql.innodb_index_stats’ is rejected | Solution Revealed

by | Oct 24, 2022

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_statsCopy 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_statsCopy Code
. However, sometimes, as in the case when restoring the older DB into a Fresh installation, we may see the below error:

access to system table 'mysql.innodb_index_stats' is rejected

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/AMPPSCopy 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.sqlCopy Code

4. Move the database. Then run the below code on the terminal:

/Applications/Ampps/mysql/bin/mysql -u root -p --force < new-database.sqlCopy 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.dbCopy Code
--ignore-table=db_name.tbl_nameCopy 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.sqlCopy Code

Then restore the backup on the new database using the code below:

mysql -u root -p < dump.sqlCopy 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.

GET STARTED

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *

Speed issues driving customers away?
We’ve got your back!