| Bug #113400 | mysqldump generates incorrect sql when skipping generated invisible pk | ||
|---|---|---|---|
| Submitted: | 12 Dec 2023 14:12 | Modified: | 15 Dec 2023 13:44 |
| Reporter: | Wen He (OCA) | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: mysqldump Command-line Client | Severity: | S2 (Serious) |
| Version: | 8.0 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | mysqldump | ||
[12 Dec 2023 16:22]
MySQL Verification Team
Hi Mr. He, Thank you for your bug report. However, we were not able to repeat your test case with our release binary 8.0.35: * mysqlpump returned the following error: mysqlpump is deprecated and will be removed in a future version. Use mysqldump instead. * We have got this output from SHOW CREATE TABLE: t1 CREATE TABLE `t1` (\n `f` int DEFAULT NULL\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; Hence, first of all, index is not displayed. Much more important, mysqlpump is no longer maintained. Unsupported.
[13 Dec 2023 3:37]
Wen He
I simplified the case, and changed mysqlpump to mysqldump. Auctually, the bug has nothing to do with the result of `show create table`. The bug is mysqldump generates incorrect sql for recovery. # mysqldump_bug.test CREATE TABLE t1 (my_row_id bigint unsigned NOT NULL AUTO_INCREMENT INVISIBLE, f INT, PRIMARY KEY(my_row_id)); ALTER TABLE t1 ADD INDEX(my_row_id, f); INSERT INTO t1 VALUES (1), (3), (7), (8), (4); --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR --exec $MYSQL_DUMP --databases test --skip-generated-invisible-primary-key > "$MYSQLTEST_VARDIR/tmp/test.sql" DROP TABLE t1; --exec $MYSQL < $MYSQLTEST_VARDIR/tmp/test.sql remove_file $MYSQLTEST_VARDIR/tmp/test.sql; DROP TABLE t1;
[13 Dec 2023 3:42]
Wen He
test.sql generated by mysqldump has a following sql. CREATE TABLE `t1` ( `f` int DEFAULT NULL, KEY `my_row_id` (`my_row_id`,`f`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; It causes the mysqldump_bug.test fail with the following error. CURRENT_TEST: main.mysqlpump_bug ERROR 1072 (42000) at line 33: Key column 'my_row_id' doesn't exist in table mysqltest: At line 10: Command "$MYSQL < $MYSQLTEST_VARDIR/tmp/test.sql" failed.
[13 Dec 2023 10:32]
MySQL Verification Team
Hi Mr. He, We have run your test case with mysqldump with 8.0.35. On the import of the test2.sql generated by mysqldump into mysql, we get the following error: ERROR 1072 (42000) at line 33: Key column 'my_row_id' doesn't exist in table If you think that this is a bug, please, explain why ...... Thanks in advance.
[13 Dec 2023 10:49]
MySQL Verification Team
Hi Mr. He, Thank you very much for your bug report. As you can see, we repeated the problem you reported. This is a bug in 8.0 and 8.2 production releases. This is now a verified bug report.
[14 Dec 2023 3:11]
Wen He
This patch includes the fix provided in Bug#111292. (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.
Contribution: Bug-113400-mysqlpump-generates-incorrect-sql-when-sk.patch (application/octet-stream, text), 2.52 KiB.
[14 Dec 2023 11:24]
MySQL Verification Team
Hi Mr. He, Thank you for your contribution. This is now a verfied bug with a contribution from the OCA developer.
[14 Dec 2023 15:04]
Georgi Kodinov
mysqlpump is deprecated: https://dev.mysql.com/doc/refman/8.0/en/mysqlpump.html says: mysqlpump is deprecated as of MySQL 8.0.34; expect it to be removed in a future version of MySQL. You can use such MySQL programs as mysqldump and MySQL Shell to perform logical backups, dump databases, and similar tasks instead.
[15 Dec 2023 8:38]
Wen He
First, mysqldump has the same issue. I have already changed mysqlpump to mysqldump in the simplified case. Second, I think if mysqldump generates a sql with invalid sql syntax, it needs a fix.
[15 Dec 2023 13:44]
MySQL Verification Team
Hi, Sorry for the mistake ....... It is corrected now .....
Description: mysqlpump generates incorrect sql with --skip-generated-invisible-primary-key option. How to repeat: ./mtr main.mysqlpump_bug # The following is the content of mysqlpump_bug.test -- source include/no_valgrind_without_big.inc SET @saved_global_sql_generate_invisible_primary_key = @@global.sql_generate_invisible_primary_key; SET @saved_session_sql_generate_invisible_primary_key = @@session.sql_generate_invisible_primary_key; # User created column "my_row_id" with generated invisible primary key column # properties and primary key on it, is treated as generated invisible primary # key column. CREATE TABLE t1 (my_row_id bigint unsigned NOT NULL AUTO_INCREMENT INVISIBLE, f INT, PRIMARY KEY(my_row_id)); ALTER TABLE t1 ADD INDEX(my_row_id, f); INSERT INTO t1 VALUES (1), (3), (7), (8), (4); SET SESSION sql_generate_invisible_primary_key=ON; CREATE TABLE t2(f1 INT INVISIBLE, f2 INT); INSERT INTO t2(f1, f2) VALUES (10, 20), (20, 30); --echo # Test case to verify table dump without generated primary key. --echo # Dump complete "test" database *without* generated invisible primary --echo # key information using mysqlpump tool. --replace_result $MYSQLTEST_VARDIR MYSQLTEST_VARDIR --exec $MYSQL_PUMP --databases test --skip-generated-invisible-primary-key > "$MYSQLTEST_VARDIR/tmp/test2.sql" DROP TABLE t1, t2; --echo # Load from the test.t3 dump created using mysqlpump tool." --exec $MYSQL < $MYSQLTEST_VARDIR/tmp/test2.sql --echo # Table t1 should *not* list generated primary key information. SHOW CREATE TABLE t1; SELECT * FROM t1; SELECT f FROM t1; --echo # Cleanup remove_file $MYSQLTEST_VARDIR/tmp/test2.sql; DROP TABLE t1, t2; SET GLOBAL sql_generate_invisible_primary_key = @saved_global_sql_generate_invisible_primary_key; SET SESSION sql_generate_invisible_primary_key = @saved_session_sql_generate_invisible_primary_key;