Bug #113521 mysqlpump generate wrong stmt when deal with AUTO_INCREMENT cloumn

• Submitted: 28 Dec 2023 8:23
• Modified: 29 Dec 2023 13:50
• Reporter: zongyi chen (OCA)
• Status: Not a Bug
• Category: MySQL Server: mysqlpump Command-line Client
• Severity: S3 (Non-critical)
• OS: Any
• CPU Architecture: Any

[28 Dec 2023 8:23] zongyi chen

Description:
when you create table as
CREATE TABLE t1 (f1 int unique AUTO_INCREMENT);

next use mysqlpump as next
/home/czy/open/mysql/bin/mysqlpump -uroot -h127.0.0.1 -P8035 --databases test > "/home/czy/test1.sql"

test1.sql context will be:
...
CREATE TABLE `test`.`t1` (
`f1` int NOT NULL AUTO_INCREMENT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
;
USE `test`;
ALTER TABLE `test`.`t1` ADD UNIQUE KEY `f1` (`f1`);
...

when you try to do next command:
/home/czy/open/mysql/bin/mysql -uroot -h127.0.0.1 -P8035 < "/home/czy/test1.sql"

wile report a error

ERROR 1075 (42000) at line 17: Incorrect table definition; there can be only one auto column and it must be defined as a key

How to repeat:
as Description

Suggested fix:
null

[29 Dec 2023 12:43] MySQL Verification Team

Hello Zongyi chen,

Thank you for the bug report.
Your SQL statement seems wrong. Auto-column must be a key. 
Please try below altered sql statement.

CREATE TABLE `t1` (
`f1` int NOT NULL AUTO_INCREMENT PRIMARY KEY
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
;

Regards,
Ashwini Patil

[29 Dec 2023 13:49] zongyi chen

mysql> CREATE TABLE t1 (f1 int unique AUTO_INCREMENT);
Query OK, 0 rows affected (0.05 sec)

my sql can run.

mysqlpump generate sql can't run , this is the problem

[29 Dec 2023 13:50] zongyi chen

CREATE TABLE `test`.`t1` (
`f1` int NOT NULL AUTO_INCREMENT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
;
USE `test`;
ALTER TABLE `test`.`t1` ADD UNIQUE KEY `f1` (`f1`);

this sql can not run, and this sql is generate by mysqlpump

[29 Dec 2023 13:50] zongyi chen

CREATE TABLE `test`.`t1` (
`f1` int NOT NULL AUTO_INCREMENT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
;
USE `test`;
ALTER TABLE `test`.`t1` ADD UNIQUE KEY `f1` (`f1`);

this sql can not run, and this sql is generate by mysqlpump