MySQL Bugs: #91290: not able to recover backup taken with mysqlpump

Bug #91290	not able to recover backup taken with mysqlpump
Submitted:	16 Jun 2018 18:04	Modified:	18 Jun 2018 8:48
Reporter:	Shiv Iyer	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: mysqlpump Command-line Client	Severity:	S3 (Non-critical)
Version:	8.0.11	OS:	CentOS (CentOS Linux release 7.4.1708 (Core))
Assigned to:		CPU Architecture:	Any
Tags:	ERROR 1050 (42S01) at line 23: Table 'departments' already exists

Description:
I did a MySQL backup using utility mysqlpump on MySQL Server 8.0.11 on CentOS Linux release 7.4.1708 (Core) backup completes warning "mysqlpump: [WARNING] (3719) 'utf8' is currently an alias for the character set UTF8MB3, which will be replaced by UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous." , This is happening even when my char set is "UTF8MB4 " . I have database for this test from https://github.com/datacharmer/test_db . When I try to recover, I am getting even more weird error "ERROR 1050 (42S01) at line 23: Table 'departments' already exists"  , I have copied steps below for your reference  

How to repeat:
Backup with mysqlpump
-------------------------
[root@localhost Backup-Database]# mysqlpump -u root -p employees > backup.sql 
Enter password: 
Dump progress: 1/2 tables, 0/331361 rows
Dump progress: 2/6 tables, 414500/3912510 rows
Dump progress: 4/6 tables, 768877/3912510 rows
Dump progress: 4/6 tables, 1253877/3912510 rows
Dump progress: 5/6 tables, 1687935/3912510 rows
mysqlpump: [WARNING] (3719) 'utf8' is currently an alias for the character set UTF8MB3, which will be replaced by UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
mysqlpump: [WARNING] (3719) 'utf8' is currently an alias for the character set UTF8MB3, which will be replaced by UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
Dump progress: 5/6 tables, 2280935/3912510 rows
Dump progress: 5/6 tables, 2813185/3912510 rows
Dump progress: 5/6 tables, 3394685/3912510 rows
Dump progress: 5/6 tables, 3917185/3912510 rows
Dump completed in 8555

Restore backup
-----------------
[root@localhost Backup-Database]# mysql -u root -p employees_pump < backup.sql
Enter password: 
ERROR 1050 (42S01) at line 23: Table 'departments' already exists

Please use --add-drop-table. This is not default set when invoking mysqlpump.

Hello Shiv,

Thank you for the report.
By default add-drop-table is false, hence DROP TABLE IF EXISTS.. missing from the dump file.

[umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-8.0.11: bin/mysqlpump --help
.
.
add-drop-database                 FALSE
add-drop-table                    FALSE
add-drop-user                     FALSE
add-locks                         FALSE
all-databases                     FALSE

As Satish suggested, please use add-drop-table with mysqlpump.

###
[umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-8.0.11/test_db-master: ../bin/mysql -uroot -S /tmp/mysql_ushastry.sock < employees.sql
INFO
CREATING DATABASE STRUCTURE
INFO
storage engine: InnoDB
INFO
LOADING departments
INFO
LOADING employees
INFO
LOADING dept_emp
INFO
LOADING dept_manager
INFO
LOADING titles
INFO
LOADING salaries
data_load_time_diff
00:01:22

[umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-8.0.11: bin/mysqlpump -uroot -S /tmp/mysql_ushastry.sock employees > backup.sql
Dump progress: 1/1 tables, 0/9 rows
mysqlpump: [WARNING] (3719) 'utf8' is currently an alias for the character set UTF8MB3, which will be replaced by UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
mysqlpump: [WARNING] (3719) 'utf8' is currently an alias for the character set UTF8MB3, which will be replaced by UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
Dump progress: 5/6 tables, 1702935/3911791 rows
Dump progress: 5/6 tables, 2751935/3911791 rows
Dump progress: 5/6 tables, 3882935/3911791 rows
Dump completed in 3208

[umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-8.0.11: vi backup.sql
[umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-8.0.11: bin/mysql -uroot -S /tmp/mysql_ushastry.sock < backup.sql
ERROR 1050 (42S01) at line 23: Table 'departments' already exists

### You can observe same in 5.7.22 as well

[umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: bin/mysqlpump -uroot -S /tmp/mysql_ushastry.sock employees > backup.sql
Dump progress: 1/3 tables, 0/331603 rows
Dump progress: 4/6 tables, 1658877/3912479 rows
Dump progress: 5/6 tables, 2720685/3912479 rows
Dump progress: 5/6 tables, 3790185/3912479 rows
Dump completed in 3353 milliseconds
[umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22:
[umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: bin/mysql -uroot -S /tmp/mysql_ushastry.sock < backup.sql
[umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: bin/mysqlpump -uroot -S /tmp/mysql_ushastry.sock employees --add-drop-table > backup.sql
Dump progress: 1/2 tables, 0/331579 rows
Dump progress: 4/6 tables, 1678127/3912479 rows
Dump progress: 5/6 tables, 2718185/3912479 rows
Dump progress: 5/6 tables, 3782685/3912479 rows
Dump completed in 3372 milliseconds
[umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: bin/mysql -uroot -S /tmp/mysql_ushastry.sock < backup.sql
[umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22:

But I agree that "warning" should not have been thrown in this case.

Thanks,
Umesh