Not the answer you need?
Register and ask your own question!

Error message when restoring percona xtraDB cluster 8.0.19

DBA100DBA100 Current User Role Patron
hi,

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 ?

Best Answers

Answers

  • matthewbmatthewb Senior [email protected] Percona Staff Role
    1. 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.
  • DBA100DBA100 Current User Role Patron
    "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 < <dump_file.sql>?


    "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 ?

  • DBA100DBA100 Current User Role Patron
    "'--force' "
    is not working for me and my command is:

     mysql -u root -p --force  < UAT_DB__bak.sql
  • matthewbmatthewb Senior [email protected] Percona Staff Role
    I can't help if you don't provide the full output. What do you mean "is not working"?
  • DBA100DBA100 Current User Role Patron
    same error message ! so the --force can't help on this situtation.
  • DBA100DBA100 Current User Role Patron
    it seems I need to remove  NO_AUTO_CREATE_USER, tks. it only involve when the restore is restoring mysql logic.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.