Bug #92820 mysqlpump should use DROP USER IF EXISTS when --add-drop-user is enabled

• Submitted: 17 Oct 2018 9:38
• Modified: 24 Jul 7:55
• Reporter: Yoshimi Takahashi
• Status: Won't fix
• Category: MySQL Server: mysqlpump Command-line Client
• Severity: S4 (Feature request)
• Version: MySQL5.7, MySQL8.0
• OS: Any
• 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] MySQL Verification Team

HI,

Thank you for your report.

This is a fully justified feature request.

Verified as reported.

[24 Jul 7:55] Georgi Kodinov

Posted by developer:
 
mysqlpump is deprecated and removed in later versions. 

I'd suggest using mysqldump instead. It has the --add-drop-table option.