Errror 3554 while importing a .sql file

Question

ERROR 3554 (HY000) at line 318: Access to system table 'mysql.innodb_index_stats' is rejected.

Operation failed with exitcode 1 11:27:20 Import of C:\Users\VELOXSHOP\Downloads\dumpfilename.sql has finished with 1 errors

How do I allow acess to that table?

Fertilize

Answer 1

You'll need to make a new dump/backup of your old database, this time remove those innodb tables from your target. You can do this by using --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

Then you should be able to restore your backup on the new database using the command below:

mysql -u root -p < dump.sql

Slight

Answer 2

You can also circumvent this error using the --force option which causes mysql client to continue despite errors.

Becoming

This is probably best as my old database is corrupted and don't have access - aka why I'm restoring a backup. – Patriciate

Answer 3

Try to add -f to your command like so:

mysql -u root -p -f < dump.sql

-f means --force.

This did the trick for me!

Pilot

Isn't that what Prazlin was also saying? – Gaze

Answer 4

It seems to be restricted in Mysql 8. Remove the insert statement from the sql file. You may have to use sed if the file is very large

https://mcmap.net/q/325275/-how-to-ignore-certain-mysql-tables-when-importing-a-database

https://bugs.mysql.com/bug.php?id=92675

Spirituous

Answer 5

That is a MySQL system table and it's unlikely that you should be inserting records into it directly. MySQL should update the table when it calculates new statistics for indexes when thresholds pass.

Inspect line 318 and figure out why it's trying to insert into that table.

Kelm

Answer 6

I had this issue when I made an export (mysqldump) using MySQL 5.7 and then accidentally tried to import it to MySQL 8.0. The correct sequence of events was to import the data into a MySQL 5.7 instance and then upgrade it to 8.0.

Angulation