Description:
When we use mysqlpump with --add-drop-database or --add-drop-table option,
DROP DATABASE/TABLE IF EXISTS statement is written in dump file.
However, if --add-drop-user option, simple "DROP USER" statement is written.
So if that user don't exist in the server you want to import dump,
importing is failed with below error.
ERROR 1396 (HY000) at line 26: Operation DROP USER failed for '***'@'***'
[environment]
$ rpm -qa | grep -i mysql
mysql-community-libs-5.7.23-1.el7.x86_64
mysql-community-server-5.7.23-1.el7.x86_64
mysql57-community-release-el7-11.noarch
MySQL-python-1.2.5-1.el7.x86_64
mysql-community-common-5.7.23-1.el7.x86_64
mysql-community-client-5.7.23-1.el7.x86_64
mysql-community-libs-compat-5.7.23-1.el7.x86_64
How to repeat:
mysql> CREATE USER `test01`@`%`;
mysql> GRANT ALL ON *.* TO `test01`@`%`;
mysql> exit
$ mysqlpump -u root -p --users --set-gtid-purged=OFF --add-drop-user > ./pump_test.sql
$ grep "USER 'test01'" ./pump_test.sql
DROP USER 'test01'@'%';
CREATE USER 'test01'@'%' IDENTIFIED WITH 'mysql_native_password' AS '******' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
Try importing pump_test.sql at other server
$ mysql -u root -p < ./pump_test.sql
ERROR 1396 (HY000) at line 26: Operation DROP USER failed for 'test01'@'%'
Suggested fix:
mysqlpump should use DROP USER IF EXISTS statement when --add-drop-user is enabled