Restoring to mariadb10.1 fails the first time, but succeeds when restoring again after recreating the database - Source

Revision: 127768
User: kosuke shinoda
Date: 2023-07-11 15:21

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

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

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:
<<code>>
// 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
<</code>>

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

Answer

Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.

Restoring to mariadb10.1 fails the first time, but succeeds when restoring again after recreating the database - Source

Answer

Products

Services

Pricing

Resources

About Us

Download MariaDB