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: | |
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
[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?