MySQL Bugs: #92675: Cannot restore full database of 5.7.23 to fresh install of 8.0.12

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:	MySQL Verification Team	CPU Architecture:	x86 (x86_64)
Tags:	InnodB Issue for restoring DB

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?

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.

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

Yes, both innodb tables.