Bug #92675 Cannot restore full database of 5.7.23 to fresh install of 8.0.12
Submitted: 4 Oct 2018 20:36 Modified: 15 Oct 2018 10:31
Reporter: Dhyan Mishra Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Installing Severity:S1 (Critical)
Version:8.0.12 OS:CentOS
Assigned to: Bogdan Kecman CPU Architecture:x86 (x86_64)
Tags: InnodB Issue for restoring DB

[4 Oct 2018 20:36] Dhyan Mishra
Description:
I am installing a Fresh version of MySQL 8.0.12.  Our existing MySQL is 5.7.23 and have dumped the entire DB into a file. 

When restoring the 5.7.23 DB into a Fresh MySQL 8.0.12 installation I get the error:

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

I tried this multiple times, also tried restoring to MySQL 8.0.11, same error. For 8.0.12 you cannot disable the Innodb otherwise I could temporarily to restore the full DB, and then re-enable it.

How to repeat:
./bin/mysql -uroot --password=passwordhere - h localhost < entire-db-5.7.23-data

Error:

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

Suggested fix:

Is it possible to temporarily disable the Innodb engine so I the DB can fully be restored?
[9 Oct 2018 14:57] Bogdan Kecman
Hi,

What is in line 15915 of entire-db-5.7.23-data file?

If you dumped the mysql.innodb_index_stats you cannot restore that table that's expected. You should make a dump of your db only, not the mysql db with system tables.
[9 Oct 2018 16:48] Dhyan Mishra
I have always dumped the entire DB based with the MySQL Commands:

First Lock the tables then run:

mysqldump -u root -ppassword all-databases > entire-db-5.7.22-data

Then when upgrading ie, from 5.7.22 to 5.7.23, I would install the new server, and restore with:

mysql -uroot --password=passwordhere -h localhost < entire-db-5.7.22-data
using the 5.7.22 data for the new 5.7.23 Server.

This always worked without issues for 5.7.x or even 5.6.x for that matter. However, when trying to restore with a fresh 8.0.12 I get that error. In this case I was restoring 5.7.23 data to an new 8.0.12, hence the entire-db-5.7.23-data. 

So I am hearing that you are suggesting to use the all-databases command, but exclude the mysql.innodb_index_stats and other mysql.innodb tables?  Shouldn't the all-databases command argument exclude that automatically if that is something that should never be backed up and or restored? 

Ok, so looking this up, then this should work:

mysqldump -u USERNAME -pPASSWORD --ignore-table=mysql.innodb_index_stats all-databases  > database-backup.db

--ignore-table=db_name.tbl_name

Man page says:
**********************
Do not dump the given table, which must be specified using both the database and table names. To ignore multiple tables, use this option multiple times. This option also can be used to ignore views. 
**********************

Is the mysql.innodb_index_stats the only table that shouldn't be restore, how about the mysql.innodb_table_stats?

Thanks again,

Dhyan
[15 Oct 2018 10:31] Bogdan Kecman
Yes, both innodb tables.