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

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:

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.