Bug #92820 mysqlpump should use DROP USER IF EXISTS when --add-drop-user is enabled
Submitted: 17 Oct 2018 9:38 Modified: 21 Nov 2018 16:40
Reporter: Yoshimi Takahashi Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: mysqlpump Command-line Client Severity:S4 (Feature request)
Version:MySQL5.7, MySQL8.0 OS:Any
Assigned to: CPU Architecture:Any
Tags: mysqlpump

[17 Oct 2018 9:38] Yoshimi Takahashi
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
[21 Nov 2018 16:40] Sinisa Milivojevic
HI,

Thank you for your report.

This is a fully justified feature request.

Verified as reported.