1) Today when restoring one of the DB by mysqldump, it said:
ERROR 1231 (42000) at line 1222: Variable ‘sql_mode’ can’t be set to the value of ‘NO_AUTO_CREATE_USER’
this DB I restore do not have trigger restored, is it a hints.
what should I do to make it restoreable ?
2) our percona xtgraDB Cluster 8.0.19 has a DB called mysql, it is a system default DB and what is it for ?
- The SQL mode NO_AUTO_CREATE_USER was removed in MySQL 8.0. If you are trying to restore a mysqldump from 5.7 to 8.0, this is probably why it is failing. You can find this line in your dump and simply remove it, or use the ‘–force’ option when doing the restore to skip over errors.
2. The “mysql” database contains system information in addition to user, permissions, etc.
“or use the ‘–force’ option when doing the restore to skip over errors.”
I am sorry , how ?
mysql --host=<DB host we want to restore to > -u root -p –force <
“2. The “mysql” database contains system information in addition to user, permissions, etc.”
so we don’t need to restore this one in mysql 5.7 and 8.0.19 ?
is not working for me and my command is:
mysql -u root -p --force < UAT_DB__bak.sql
I can’t help if you don’t provide the full output. What do you mean “is not working”?
same error message ! so the --force can’t help on this situtation.
Then you need to edit your dump file, find the line that contains sql_mode and remove it.
it seems I need to remove NO_AUTO_CREATE_USER, tks. it only involve when the restore is restoring mysql logic.
NO_AUTO_CREATE_USER is a security feature that was introduced in 5.7 but only in 8.0 was enabled by default. If you use a tool like pt-show-grants, this issue is automatically handled for you.