Bug #106153 mysqlpump should use CREATE TABLE IF NOT EXISTS
Submitted: 13 Jan 2022 1:23 Modified: 13 Jan 2022 18:21
Reporter: Vinicius Malvestio Grippa Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: mysqlpump Command-line Client Severity:S3 (Non-critical)
Version:5.7, 8.0 OS:Any
Assigned to: CPU Architecture:Any

[13 Jan 2022 1:23] Vinicius Malvestio Grippa
Description:
Currently mysqlpump writes the DDL like the following:
CREATE TABLE `mysql`.`engine_cost` (
`engine_name` varchar(64) NOT NULL,
`device_type` int NOT NULL,
`cost_name` varchar(64) NOT NULL,
`cost_value` float DEFAULT NULL,
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`comment` varchar(1024) DEFAULT NULL,
`default_value` float GENERATED ALWAYS AS ((case `cost_name` when _utf8mb3'io_block_read_cost' then 1.0 when _utf8mb3'memory_block_read_cost' then 0.25 else NULL end)) VIRTUAL,
PRIMARY KEY (`cost_name`,`engine_name`,`device_type`)
) /*!50100 TABLESPACE `mysql` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 STATS_PERSISTENT=0 ROW_FORMAT=DYNAMIC

The only way to make the dump work is by forcing it:

# time mysql < backup.out --force
ERROR 1050 (42S01) at line 19: Table 'component' already exists
ERROR 1062 (23000) at line 26: Duplicate entry '1' for key 'component.PRIMARY'
ERROR 1050 (42S01) at line 27: Table 'engine_cost' already exists
ERROR 1050 (42S01) at line 38: Table 'func' already exists
ERROR 1062 (23000) at line 46: Duplicate entry 'io_block_read_cost-default-0' for key 'engine_cost.PRIMARY'
ERROR 1050 (42S01) at line 47: Table 'help_category' already exists
ERROR 1062 (23000) at line 55: Duplicate entry '0' for key 'help_category.PRIMARY'
ERROR 1050 (42S01) at line 56: Table 'help_keyword' already exists
ERROR 1061 (42000) at line 63: Duplicate key name 'name'
ERROR 1050 (42S01) at line 64: Table 'help_relation' already exists
ERROR 1062 (23000) at line 70: Duplicate entry '108' for key 'help_keyword.PRIMARY'
ERROR 1062 (23000) at line 71: Duplicate entry '802' for key 'help_keyword.PRIMARY'
ERROR 1062 (23000) at line 72: Duplicate entry '158' for key 'help_keyword.PRIMARY'
ERROR 1062 (23000) at line 73: Duplicate entry '645' for key 'help_keyword.PRIMARY'
ERROR 1061 (42000) at line 75: Duplicate key name 'name'
ERROR 1050 (42S01) at line 76: Table 'help_topic' already exists
ERROR 1062 (23000) at line 86: Duplicate entry '0-0' for key 'help_relation.PRIMARY'
ERROR 1062 (23000) at line 87: Duplicate entry '78-80' for key 'help_relation.PRIMARY'
ERROR 1062 (23000) at line 88: Duplicate entry '244-237' for key 'help_relation.PRIMARY'
ERROR 1062 (23000) at line 89: Duplicate entry '438-398' for key 'help_relation.PRIMARY'
ERROR 1062 (23000) at line 90: Duplicate entry '561-580' for key 'help_relation.PRIMARY'
ERROR 1062 (23000) at line 91: Duplicate entry '625-509' for key 'help_relation.PRIMARY'
ERROR 1062 (23000) at line 92: Duplicate entry '721-612' for key 'help_relation.PRIMARY'
ERROR 1062 (23000) at line 93: Duplicate entry '859-605' for key 'help_relation.PRIMARY'
ERROR 1062 (23000) at line 94: Duplicate entry '957-673' for key 'help_relation.PRIMARY'
ERROR 1050 (42S01) at line 99: Table 'password_history' already exists
ERROR 1062 (23000) at line 107: Duplicate entry '0' for key 'help_topic.PRIMARY'
ERROR 1050 (42S01) at line 108: Table 'plugin' already exists
ERROR 1062 (23000) at line 114: Duplicate entry '250' for key 'help_topic.PRIMARY'
ERROR 1050 (42S01) at line 115: Table 'replication_asynchronous_connection_failover' already exists
ERROR 1061 (42000) at line 126: Duplicate key name 'Channel_name'
ERROR 1050 (42S01) at line 127: Table 'replication_asynchronous_connection_failover_managed' already exists
ERROR 1050 (42S01) at line 135: Table 'replication_group_configuration_version' already exists
ERROR 1062 (23000) at line 141: Duplicate entry '500' for key 'help_topic.PRIMARY'
ERROR 1061 (42000) at line 143: Duplicate key name 'name'
ERROR 1062 (23000) at line 144: Duplicate entry 'replication_group_member_actions' for key 'replication_group_configuration_version.PRIMARY'
ERROR 1050 (42S01) at line 145: Table 'replication_group_member_actions' already exists
ERROR 1062 (23000) at line 155: Duplicate entry 'mysql_disable_super_read_only_if_primary-AFTER_PRIMARY_ELECTION' for key 'replication_group_member_actions.PRIMARY'
ERROR 1061 (42000) at line 157: Duplicate key name 'event'
ERROR 1050 (42S01) at line 158: Table 'server_cost' already exists
ERROR 1062 (23000) at line 167: Duplicate entry 'disk_temptable_create_cost' for key 'server_cost.PRIMARY'
ERROR 1050 (42S01) at line 168: Table 'servers' already exists
ERROR 1050 (42S01) at line 181: Table 'slave_worker_info' already exists
ERROR 1050 (42S01) at line 198: Table 'time_zone' already exists
ERROR 1050 (42S01) at line 204: Table 'time_zone_leap_second' already exists
ERROR 1050 (42S01) at line 210: Table 'time_zone_name' already exists
ERROR 1050 (42S01) at line 216: Table 'time_zone_transition' already exists
ERROR 1050 (42S01) at line 223: Table 'time_zone_transition_type' already exists

How to repeat:
Take a complete backup and try to restore:

$ mysqlpump --default-parallelism=32 --all-databases > backup.out

Suggested fix:
Tables and databases should produce statements in the same way as mysqldump. In this way it would not throw "already exists" errors.
[13 Jan 2022 11:11] MySQL Verification Team
Hello Vinicius,

Thank you for report and valuable feedback.
Workaround/Alternative way is to use --add-drop-table option which write a DROP TABLE statement before each CREATE TABLE statement to avoid the error at the time of import.

##
-- 
bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.36 MySQL Community Server (GPL)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> use test
Database changed
mysql> create table t(id int not null primary key);
Query OK, 0 rows affected (0.01 sec)

mysql> \q
Bye

###
bin/mysqlpump -uroot -S /tmp/mysql_ushastry.sock --default-parallelism=32 test
-- Dump created by MySQL pump utility, version: 5.7.36, linux-glibc2.12 (x86_64)
-- Dump start time: Thu Jan 13 12:06:24 2022
-- Server version: 5.7.36

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE;
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET @@SESSION.SQL_LOG_BIN= 0;
SET @OLD_TIME_ZONE=@@TIME_ZONE;
SET TIME_ZONE='+00:00';
SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT;
SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS;
SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION;
SET NAMES utf8mb4;
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `test` /*!40100 DEFAULT CHARACTER SET latin1 */;
CREATE TABLE `test`.`t` (
`id` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
;
Dump progress: 1/1 tables, 0/0 rows
SET TIME_ZONE=@OLD_TIME_ZONE;
SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT;
SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS;
SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
SET SQL_MODE=@OLD_SQL_MODE;
-- Dump end time: Thu Jan 13 12:06:24 2022
Dump completed in 11 milliseconds

####
bin/mysqlpump -uroot -S /tmp/mysql_ushastry.sock --default-parallelism=32 --add-drop-table test
-- Dump created by MySQL pump utility, version: 5.7.36, linux-glibc2.12 (x86_64)
-- Dump start time: Thu Jan 13 12:09:16 2022
-- Server version: 5.7.36

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE;
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET @@SESSION.SQL_LOG_BIN= 0;
SET @OLD_TIME_ZONE=@@TIME_ZONE;
SET TIME_ZONE='+00:00';
SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT;
SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS;
SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION;
SET NAMES utf8mb4;
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `test` /*!40100 DEFAULT CHARACTER SET latin1 */;
DROP TABLE IF EXISTS `test`.`t`;
CREATE TABLE `test`.`t` (
`id` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
;
Dump progress: 1/1 tables, 0/0 rows
SET TIME_ZONE=@OLD_TIME_ZONE;
SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT;
SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS;
SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
SET SQL_MODE=@OLD_SQL_MODE;
-- Dump end time: Thu Jan 13 12:09:16 2022
Dump completed in 8 milliseconds

regards,
Umesh
[13 Jan 2022 18:21] Vinicius Malvestio Grippa
Hi Umesh,

Thanks for providing the workaround. Maybe having --add-drop-table enabled by default? So behavior would be similar to mysqldump?