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 Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: mysqlpump Command-line Client Severity:S3 (Non-critical)
Version: OS:Any
Assigned to: 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