Bug #111242 Replica Issue - ('mysql.infoschema'@'localhost') does not exist
Submitted: 1 Jun 2023 12:31 Modified: 6 Jun 2023 15:48
Reporter: Paul Bowden Email Updates:
Status: Unsupported Impact on me:
None 
Category:MySQL Server: Replication Severity:S2 (Serious)
Version:5.7.41 and 8.0.33 OS:Any
Assigned to: MySQL Verification Team CPU Architecture:Any

[1 Jun 2023 12:31] Paul Bowden
Description:
Using a source MySQL server on 5.7.41 and a replica that is running 8.0.33, the following error is displayed when connecting locally on the 8.0.33:

mysql> show databases;
ERROR 1449 (HY000): The user specified as a definer ('mysql.infoschema'@'localhost') does not exist

Running sudo mysqld --defaults-file=mysqld.cnf --user=mysql --upgrade=FORCE --console fixes the issue, but if the replica is reset with another export/import from the source, the error returns again.

How to repeat:
Run source MySQL with version 5.7.41
Run replica MySQL with version 8.0.33
Use mysqldump (5.7 version) on replica to export source database and import with mysql (8.0.33 version).
Run SHOW DATABASES on replica

Expect:

mysql> show databases;
ERROR 1449 (HY000): The user specified as a definer ('mysql.infoschema'@'localhost') does not exist
[5 Jun 2023 15:28] MySQL Verification Team
Hi,

dumping data from 5.7.42 and loading it in 8.0.33 works, and replication works after that. It looks like you dumped `mysql` database and loaded that `mysql` database into 8.0.33. `mysql` database differs between major versions and this is not supported.
[5 Jun 2023 17:06] Paul Bowden
I am using mysqldump with the --all-databases option. What is the recommended way to exclude the mysql database?
[5 Jun 2023 17:40] Paul Bowden
Additionally, the mysql database contains the users. If this database can't be replicated, how are the users to be replicated?
[5 Jun 2023 17:41] MySQL Verification Team
mysqldump do not have option to exclude/ignore database.

You can use mysqlpump that does support this option:

mysqlpump --user=user --password --exclude-databases=mysql --result-file=backup.sqldump

mysqlpump can also be used to export accounts and privileges as logical commands instead of you needing to migrate mysql database so you can recreate users on second server.

or you can use a script with mysqldump like:

IGNOREDBS="'information_schema','mysql'"
SQL="SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT IN (${EXCLUSION_LIST})"
DUMPDBS="--databases"
for DB in `mysql -ANe"${SQL}"`
do
    DUMPDBS="${DUMPDBS} ${DB}"
done
DUMPOPT="--triggers --routines"
mysqldump ${MYSQLDUMP_OPTIONS} ${MYSQLDUMP_DATABASES} --result-file=backup-`date +%Y%m%d%H%M`.sqldump

or you can use MySQL Shell to dump data... but we are now way passed the bugs system and into the support waters, you can contact our support team and they will be happy to help you out

all best
[6 Jun 2023 11:14] Paul Bowden
mysqlpump will be deprecated in 8.0.34 and it is recommended to use mysqldump instead.

https://dev.mysql.com/doc/refman/8.0/en/mysqlpump.html
[6 Jun 2023 14:42] MySQL Verification Team
> mysqlpump will be deprecated in 8.0.34

Yes, and you are dumping 5.7

for 8.0 use the shell method ( https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-dump-instance-schema.ht... ) as I mentioned in the answer

> and it is recommended to use mysqldump instead.

no it is not, recommended is using mysql-shell to dump data
[6 Jun 2023 15:48] Paul Bowden
Thanks for the response.

Is there a MySQL document explaining the full process of replicating between a 5.7 source and a 8.0 replica? I'd imagine this would be quite important to avoid downtime for applications.

I'm not able to find anything except on Percona's website.
[6 Jun 2023 16:10] MySQL Verification Team
AFAIK no. severalnines IIRC wrote about it but not sure how in-depth those writings were. We mostly wrote about direct upgrade e.g. https://dev.mysql.com/blog-archive/inplace-upgrade-from-mysql-5-7-to-mysql-8-0/ .. as for the replication and migration MySQL Shell should solve most of the problems you encounter. On how to do this with shell check out shell documentation (I'm not sure there are examples how to setup replication 5.7 to 8.0) and contact our Support team (or if you do not have support subscription check out mysql forum where we often help users that do not have support subscription), but bugs database is not a place for such discussion.