1

I am upgrading from mariadb5.5 to 10.1. I'm trying to restore the dump file output from 5.5 to 10.1, but I'm suffering from the phenomenon that the first restore always fails, and the restore succeeds after deleting and recreating the database. The content of the error is

ERROR 1449 (HY000) at line 44811: The user specified as a definer ('batch'@'localhost') does not exist

But 'batch'@'localhost' does exist and indeed a second restore succeeds with the same dump file without adding the user. The commands I'm actually using are:

// in mariadb5.5
$ mysqldump -u root -p -x -h localhost -R --opt --quick databasename | gzip > /root/backup_db/databasename.sql.gz
$ mysqldump -u root -p -x -h localhost --allow-keywords mysql | gzip > /root/backup_db/mysql.sql.gz

// After installing mariadb10.1
$ mysql_secure_installation
$ mysql_upgrade -u root -p
$ mysql -u root -p -h localhost
# create database databasename;
# \q
// After decompressing the compressed dump file
$ mysql -u root -p -h localhost mysql < mysql.sql
$ mysql -u root -p -h localhost databasename< databasename.sql // always fails here

$ mysql -u root -p -h localhost
# drop database databasename;
# create database databasename;
# \q

$ mysql -u root -p -h localhost databasename< databasename.sql // success here

Please let me know if there are any possible causes or things to check. Thank you.

Thinking that net_buffer_length is insufficient,

net_buffer_length=1024000　

I tried to restore by changing to , but it failed. Max_allowed_packet at that time was

max_allowed_packet=100MB.

snks
  • 13
  • 2
  • How does it fail? What is the SQL error? Why are you only upgrading as far as 10.1? Its been [out of maintenance for quite a while](https://mariadb.org/about/#maintenance-policy) – danblack Jul 10 '23 at 21:54
  • @danblack thank you. I have a view in my dump file, but it fails when I try to restore the view. the cause of the failure `ERROR 1449 (HY000) at line 44811: The user specified as a definer ('batch'@'localhost') does not exist` is displayed, but this user exists. The reason why we only upgrade to mariadb10.1 is to match the existing environment. – snks Jul 11 '23 at 00:08
  • Is there a `FLUSH PRIVILEGES` at the end of the `mysql.sql` file? It might be added only if the `--all-databases` is used on the dump. As `mysql_upgrade -u root -p` update changes the `mysql.*` tables and does a `FLUSH PRIVILEGES`, use this between restoring `mysql` and `databasename` – danblack Jul 11 '23 at 00:40
  • @danblack thank you. As you said, when I executed `mysql_upgrade` between restoring `mysql` and restoring `databasename`, the error disappeared. I'm really thankful to you. – snks Jul 11 '23 at 14:43
  • You're welcome. As I should a have done that as an answer, its there now. [please accept](https://stackoverflow.com/help/someone-answers) so its obvious and searchable to other users as a verified solution. – danblack Jul 12 '23 at 00:41

1 Answers1

1

When restoring a dump of a mysql database taken separately (as opposed to --all-databases which does special handling), a FLUSH PRIVILEGES is needed to restore that dump to the running configuration of the database.

The mysql_upgrade, now called mariadb-upgrade in 10.4+, alters the mysql.* tables to correspond to the new features of the mariadb version and issues a FLUSH PRIVILEGES.

So when restoring to a new major version, run mysql_upgrade after restoring the mysql database. If restoring to the same version, a FLUSH PRIVILEGES after restoration is sufficient.

danblack
  • 12,130
  • 2
  • 22
  • 41