MySQL Bugs: #110501: Invalid CREATE TABLE statement

Bug #110501	Invalid CREATE TABLE statement
Submitted:	25 Mar 2023 16:12	Modified:	2 Feb 10:18
Reporter:	Sébastien F.	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: mysqlpump Command-line Client	Severity:	S1 (Critical)
Version:	8.0.32	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
mysqlpump generates an invalid CREATE TABLE statement.

How to repeat:
DDL :

create schema test_mysqlpump;
use test_mysqlpump;
create table test (id int primary key);

Dump :

$ mysqlpump -u'name' -p'pwd' test_mysqlpump

Output :

...
CREATE TABLE `test_mysqlpump`."test" (
"id" int NOT NULL,
PRIMARY KEY ("id"),
),
;
...

Suggested fix:
Remove extra commas.

My apologies, I attempted using mysqldump instead of  mysqlpump. Let me confirm quickly and get back to you.

regards,
Umesh

I'm still not seeing the issue.

 bin/mysql -uroot -S /tmp/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.32 MySQL Community Server - GPL

Copyright (c) 2000, 2023, 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 schema test_mysqlpump;
Query OK, 1 row affected (0.01 sec)

mysql> use test_mysqlpump;
Database changed
mysql> create table test (id int primary key);
Query OK, 0 rows affected (0.02 sec)

mysql> \q

- mysqlpump

 bin/mysqlpump -uroot -S /tmp/mysql.sock test_mysqlpump
-- Dump created by MySQL pump utility, version: 8.0.32, Linux (x86_64)
-- Dump start time: Mon Mar 27 07:47:45 2023
-- Server version: 8.0.32

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_mysqlpump` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;
CREATE TABLE `test_mysqlpump`.`test` (
`id` int NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
;
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: Mon Mar 27 07:47:45 2023

-- Looking at your dump, thought ANSI_QUOTE is in effect and hence tried with that:

 bin/mysql -uroot -S /tmp/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.32 MySQL Community Server - GPL

Copyright (c) 2000, 2023, 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> set global sql_mode='ANSI_QUOTES';
Query OK, 0 rows affected (0.00 sec)
mysql> drop database test_mysqlpump;
Query OK, 1 row affected (0.01 sec)

mysql> create schema test_mysqlpump;
Query OK, 1 row affected (0.01 sec)

mysql> use test_mysqlpump;
Database changed
mysql> create table test (id int primary key);
Query OK, 0 rows affected (0.02 sec)

- mysqlpump

 bin/mysqlpump -uroot -S /tmp/mysql.sock test_mysqlpump
-- Dump created by MySQL pump utility, version: 8.0.32, Linux (x86_64)
-- Dump start time: Mon Mar 27 07:48:31 2023
-- Server version: 8.0.32

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_mysqlpump" /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;
CREATE TABLE `test_mysqlpump`."test" (
"id" int NOT NULL,
PRIMARY KEY ("id")
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
;
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: Mon Mar 27 07:48:31 2023
Dump completed in 12

Could you please confirm exact server version, configuration details if it is on default(my.cnf, maybe SQL_MODE set to ANSI_QUOTES etc. ) to reproduce the issue from our end? Thank you.

regards,
Umesh

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Use mysqldump or other solution.

https://dev.mysql.com/doc/refman/8.0/en/mysqlpump.html :

"mysqlpump is deprecated as of MySQL 8.0.34; expect it to be removed in a future version of MySQL."