ALTER TABLE...IMPORT TABLESPACE does not work with INDEX DESC

innodb.import_cfg                        [ fail ]  Found warnings/errors in server log file!

        Test ended at 2024-10-16 10:53:40

line

2024-10-16 10:53:26 4 [ERROR] InnoDB: index records in a wrong order in `b` of table `test`.`t1`: TUPLE (info_bits=0, 2 fields): {[4]    (0x80000001),[4]    (0x80000001)}, COMPACT RECORD(info_bits=0, 2 fields): {[4]    (0x80000002),[4]    (0x80000002)}

2024-10-16 10:53:26 4 [ERROR] InnoDB: Flagged corruption of `b` in table `test`.`t1` in CHECK TABLE-check index

2024-10-16 10:53:26 4 [ERROR] InnoDB: index records in a wrong order in `b` of table `test`.`t1`: TUPLE (info_bits=0, 2 fields): {[4]    (0x80000001),[4]    (0x80000001)}, COMPACT RECORD(info_bits=0, 2 fields): {[4]    (0x80000002),[4]    (0x80000002)}

2024-10-16 10:53:26 4 [ERROR] InnoDB: Flagged corruption of `b` in table `test`.`t1` in CHECK TABLE-check index

2024-10-16 10:53:26 4 [ERROR] InnoDB: index records in a wrong order in `b` of table `test`.`t1`: TUPLE (info_bits=0, 2 fields): {[4]    (0x80000002),[4]    (0x80000002)}, COMPACT RECORD(info_bits=0, 2 fields): {[4]    (0x80000001),[4]    (0x80000001)}

2024-10-16 10:53:26 4 [ERROR] InnoDB: Flagged corruption of `b` in table `test`.`t1` in CHECK TABLE-check index

2024-10-16 10:53:26 4 [ERROR] InnoDB: index records in a wrong order in `b` of table `test`.`t1`: TUPLE (info_bits=0, 2 fields): {[4]    (0x80000002),[4]    (0x80000002)}, COMPACT RECORD(info_bits=0, 2 fields): {[4]    (0x80000001),[4]    (0x80000001)}

2024-10-16 10:53:26 4 [ERROR] InnoDB: Flagged corruption of `b` in table `test`.`t1` in CHECK TABLE-check index

--- mysql-8.0.40/mysql-test/suite/innodb/t/import_cfg.test

+++ 10.11/mysql-test/suite/innodb/t/import_cfg.test

@@ -1,3 +1,5 @@

+--source include/have_innodb.inc

+

 --echo #

 --echo # Bug#30128418: IMPORT TABLESPACE MUST CHECK DESCENDING INDEX DEFINITION

 --echo #

@@ -17,7 +19,7 @@

 --echo ###

 --echo # Create a table with a normal ascending secondary key and export it.

-CREATE TABLE t1 (a int, b int, PRIMARY KEY (a), KEY (b));

+CREATE TABLE t1 (a int, b int, PRIMARY KEY (a), KEY (b)) ENGINE=InnoDB;

 INSERT INTO t1 VALUES (1, 1);

 INSERT INTO t1 VALUES (2, 2);

 --sorted_result

@@ -55,7 +57,7 @@

 --echo ###

 --echo # Create a table with a descending secondary key and export it.

-CREATE TABLE t1 (a int, b int, PRIMARY KEY (a), KEY (b DESC));

+CREATE TABLE t1 (a int, b int, PRIMARY KEY (a), KEY (b DESC)) ENGINE=InnoDB;

 INSERT INTO t1 VALUES (1, 1);

 INSERT INTO t1 VALUES (2, 2);

 --sorted_result

@@ -112,7 +114,7 @@

 --echo # Recreate the table without the DESC attribute on the secondary key.

 DROP TABLE t1;

-CREATE TABLE t1 (a int, b int, PRIMARY KEY (a), KEY (b));

+CREATE TABLE t1 (a int, b int, PRIMARY KEY (a), KEY (b)) ENGINE=InnoDB;

 SHOW CREATE TABLE t1;

 --echo # Discard the table which deletes the IBD file.

@@ -144,7 +146,7 @@

 --echo ###

 --echo # Recreate the table without the DESC attribute on the secondary key.

-CREATE TABLE t1 (a int, b int, PRIMARY KEY (a), KEY (b));

+CREATE TABLE t1 (a int, b int, PRIMARY KEY (a), KEY (b)) ENGINE=InnoDB;

 SHOW CREATE TABLE t1;

 --echo # Discard the table which deletes the IBD file.

@@ -176,7 +178,7 @@

 --echo ###

 --echo # Recreate the table with the DESC attribute on the secondary key.

-CREATE TABLE t1 (a int, b int, PRIMARY KEY (a), KEY (b DESC));

+CREATE TABLE t1 (a int, b int, PRIMARY KEY (a), KEY (b DESC)) ENGINE=InnoDB;

 SHOW CREATE TABLE t1;

 --echo # Discard the table which deletes the IBD file.

@@ -190,17 +192,16 @@

 --echo # CFG file, we assume it is ascending, which is different from the exported

 --echo # table. The error message will complain about Index b field b.

 --echo # The import will abort and delete the cfg file.

---error ER_TABLE_SCHEMA_MISMATCH

 ALTER TABLE t1 IMPORT TABLESPACE;

+CHECK TABLE t1 EXTENDED;

 DROP TABLE t1;

---remove_file $MYSQLD_DATADIR/$DB/t1.ibd

 --echo ###

 --echo ### IMPORT TEST #4 Ascending key IBD and v4 CFG to Discarded Descending Key IBD

 --echo ###

 --echo # Recreate the table with the DESC attribute on the secondary key.

-CREATE TABLE t1 (a int, b int, PRIMARY KEY (a), KEY (b DESC));

+CREATE TABLE t1 (a int, b int, PRIMARY KEY (a), KEY (b DESC)) ENGINE=InnoDB;

 SHOW CREATE TABLE t1;

 --echo # Discard the table which deletes the IBD file.

@@ -213,17 +214,16 @@

 --echo # Attempt to import the table.  Since the DESC flag if the discarded table

 --echo # is different from the exported table, the error message will complain about

 --echo # Index b field b. The import will abort and delete the cfg file.

---error ER_TABLE_SCHEMA_MISMATCH

 ALTER TABLE t1 IMPORT TABLESPACE;

+CHECK TABLE t1 EXTENDED;

 DROP TABLE t1;

---remove_file $MYSQLD_DATADIR/$DB/t1.ibd

 --echo ###

 --echo ### IMPORT TEST #5: Descending key IBD and v3 CFG to Discarded Ascending Key IBD

 --echo ###

 --echo # Recreate the table without the DESC attribute on the secondary key.

-CREATE TABLE t1 (a int, b int, PRIMARY KEY (a), KEY (b));

+CREATE TABLE t1 (a int, b int, PRIMARY KEY (a), KEY (b)) ENGINE=InnoDB;

 SHOW CREATE TABLE t1;

 --echo # Discard the table which deletes the IBD file.

@@ -259,14 +259,12 @@

 --sorted_result

 SELECT * FROM t1;

 SELECT * FROM t1 order by a;

---skip_if_hypergraph  # Does not use the index leading to the error.

 SELECT * FROM t1 order by b;

 INSERT INTO t1 VALUES (3, 3);

 INSERT INTO t1 VALUES (4, 4);

 --sorted_result

 SELECT * FROM t1;

 SELECT * FROM t1 order by a;

---skip_if_hypergraph  # Does not use the index leading to the error.

 SELECT * FROM t1 order by b;

 DROP TABLE t1;

@@ -275,7 +273,7 @@

 --echo ###

 --echo # Recreate the table without the DESC attribute on the secondary key.

-CREATE TABLE t1 (a int, b int, PRIMARY KEY (a), KEY (b));

+CREATE TABLE t1 (a int, b int, PRIMARY KEY (a), KEY (b)) ENGINE=InnoDB;

 SHOW CREATE TABLE t1;

 --echo # Discard the table which deletes the IBD file.

@@ -288,17 +286,16 @@

 --echo # Attempt to import the table.  Since the DESC flag if the discarded table

 --echo # is different from the exported table, the error message will complain about

 --echo # column b in index b. The import will abort and delete the cfg file.

---error ER_TABLE_SCHEMA_MISMATCH

 ALTER TABLE t1 IMPORT TABLESPACE;

+CHECK TABLE t1 EXTENDED;

 DROP TABLE t1;

---remove_file $MYSQLD_DATADIR/$DB/t1.ibd

 --echo ###

 --echo ### IMPORT TEST #7: Descending key IBD and v3 CFG to Discarded Descending Key IBD

 --echo ###

 --echo # Recreate the table with the DESC attribute on the secondary key.

-CREATE TABLE t1 (a int, b int, PRIMARY KEY (a), KEY (b DESC));

+CREATE TABLE t1 (a int, b int, PRIMARY KEY (a), KEY (b DESC)) ENGINE=InnoDB;

 SHOW CREATE TABLE t1;

 --echo # Discard the table which deletes the IBD file.

@@ -311,17 +308,16 @@

 --echo # Attempt to import the table.  Since the DESC flag is not available in the

 --echo # CFG file, we wrongly assume it is ascending. Since this is different from

 --echo # the exported table, the error message will complain about column b in index b.

---error ER_TABLE_SCHEMA_MISMATCH

 ALTER TABLE t1 IMPORT TABLESPACE;

+CHECK TABLE t1 EXTENDED;

 DROP TABLE t1;

---remove_file $MYSQLD_DATADIR/$DB/t1.ibd

 --echo ###

 --echo ### IMPORT TEST #8: Descending key IBD and v4 CFG to Discarded Descending Key IBD

 --echo ###

 --echo # Recreate the table with the DESC attribute on the secondary key.

-CREATE TABLE t1 (a int, b int, PRIMARY KEY (a), KEY (b DESC));

+CREATE TABLE t1 (a int, b int, PRIMARY KEY (a), KEY (b DESC)) ENGINE=InnoDB;

 SHOW CREATE TABLE t1;

 --echo # Discard the table which deletes the IBD file.

@@ -358,13 +354,9 @@

 --copy_file $MYSQLD_DATADIR/$DB/t1.cfg.descend.v99 $MYSQLD_DATADIR/$DB/t1.cfg

 --echo # Attempt to import the table.

---error ER_IMP_INCOMPATIBLE_CFG_VERSION

 ALTER TABLE t1 IMPORT TABLESPACE;

 SHOW WARNINGS;

---remove_file $MYSQLD_DATADIR/$DB/t1.ibd

---remove_file $MYSQLD_DATADIR/$DB/t1.cfg

-

 --echo # Cleanup

 DROP TABLE t1;

 --remove_file $MYSQLD_DATADIR/$DB/t1.ibd.ascend

@@ -385,7 +377,7 @@

 --echo # ----------------------------------------------------------------

 --echo # Create source table t1 and add a column INSTANTly

-CREATE TABLE t1(id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY);

+CREATE TABLE t1(id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY) ENGINE=InnoDB;

 ALTER TABLE t1 ADD COLUMN v1 VARCHAR(255), ALGORITHM=INSTANT;

 ALTER TABLE t1 ADD COLUMN v2 VARCHAR(255), ALGORITHM=INSTANT;

@@ -401,8 +393,8 @@

 SELECT * from t1 limit 10;

 SELECT COUNT(*) from t1;

-SELECT NAME, N_COLS, INSTANT_COLS FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME="test/t1";

-SELECT NAME, POS, HAS_DEFAULT from information_Schema.innodb_columns WHERE NAME='v1' OR NAME='v2';

+SELECT NAME, N_COLS FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME="test/t1";

+SELECT NAME, POS from information_Schema.innodb_sys_columns WHERE NAME='v1' OR NAME='v2';

 --echo # Flush the table and store CFG/IBD files to temp

 FLUSH TABLE t1 FOR EXPORT;

@@ -422,13 +414,13 @@

 ALTER TABLE t2 IMPORT TABLESPACE;

 SELECT * from t2 limit 10;

 SELECT COUNT(*) from t2;

-SELECT NAME, N_COLS, INSTANT_COLS FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME="test/t1" OR NAME="test/t2";

-SELECT NAME, POS, HAS_DEFAULT from information_Schema.innodb_columns WHERE NAME='v1' OR NAME='v2';

+SELECT NAME, N_COLS FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME="test/t1" OR NAME="test/t2";

+SELECT NAME, POS from information_Schema.innodb_sys_columns WHERE NAME='v1' OR NAME='v2';

 DROP TABLE t2;

 --echo # Create table t2 with one column added INSTANTly

-CREATE TABLE t2(id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, v1 VARCHAR(255));

+CREATE TABLE t2(id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, v1 VARCHAR(255)) ENGINE=InnoDB;

 ALTER TABLE t2 ADD COLUMN v2 VARCHAR(255);

 ALTER TABLE t2 DISCARD TABLESPACE;

@@ -437,14 +429,12 @@

 --copy_file $MYSQLD_DATADIR/t1.ibd_back $MYSQLD_DATADIR/test/t2.ibd

 --echo # IMPORT should fail as INSTANT METADATA doesn't match.

---error ER_TABLE_SCHEMA_MISMATCH

 ALTER TABLE t2 IMPORT TABLESPACE;

+CHECK TABLE t2 EXTENDED;

 DROP TABLE t2;

---remove_file $MYSQLD_DATADIR/test/t2.cfg

---remove_file $MYSQLD_DATADIR/test/t2.ibd

 --echo # Create table t2 with two column added INSTANTly

-CREATE TABLE t2(id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY);

+CREATE TABLE t2(id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY) ENGINE=InnoDB;

 ALTER TABLE t2 ADD COLUMN v1 VARCHAR(255);

 ALTER TABLE t2 ADD COLUMN v2 VARCHAR(255);

 ALTER TABLE t2 DISCARD TABLESPACE;

@@ -457,8 +447,8 @@

 ALTER TABLE t2 IMPORT TABLESPACE;

 SELECT * from t2 limit 10;

 SELECT COUNT(*) from t2;

-SELECT NAME, N_COLS, INSTANT_COLS FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME="test/t1" OR NAME="test/t2";

-SELECT NAME, POS, HAS_DEFAULT from information_Schema.innodb_columns WHERE NAME='v1' OR NAME='v2';

+SELECT NAME, N_COLS FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME="test/t1" OR NAME="test/t2";

+SELECT NAME, POS from information_Schema.innodb_sys_columns WHERE NAME='v1' OR NAME='v2';

 DROP TABLE t2;

 DROP TABLE t1;

@@ -469,7 +459,7 @@

 --echo # Source table doesn't have INSTANT columns but target table does

 --echo # ----------------------------------------------------------------

 --echo # Create source table t1 and add a column INSTANTly

-CREATE TABLE t1(id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, v1 VARCHAR(255), v2 VARCHAR(255));

+CREATE TABLE t1(id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, v1 VARCHAR(255), v2 VARCHAR(255)) ENGINE=InnoDB;

 --echo # Insert some data into t1

 --disable_query_log

@@ -483,8 +473,8 @@

 SELECT * from t1 limit 10;

 SELECT COUNT(*) from t1;

-SELECT NAME, N_COLS, INSTANT_COLS FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME="test/t1";

-SELECT NAME, POS, HAS_DEFAULT from information_Schema.innodb_columns WHERE NAME='v1' OR NAME='v2';

+SELECT NAME, N_COLS FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME="test/t1";

+SELECT NAME, POS from information_Schema.innodb_sys_columns WHERE NAME='v1' OR NAME='v2';

 --echo # Flush the table and store CFG/IBD files to temp

 FLUSH TABLE t1 FOR EXPORT;

@@ -493,7 +483,7 @@

 UNLOCK TABLES;

 --echo # Create table t2 with no INSTANTly added columns

-CREATE TABLE t2(id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, v1 VARCHAR(255), v2 VARCHAR(255));

+CREATE TABLE t2(id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, v1 VARCHAR(255), v2 VARCHAR(255)) ENGINE=InnoDB;

 ALTER TABLE t2 DISCARD TABLESPACE;

 --echo # Copy CFG/IBD file from temp

@@ -505,7 +495,7 @@

 DROP TABLE t2;

 --echo # Create table t2 with 1 INSTANTly added columns

-CREATE TABLE t2(id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, v1 VARCHAR(255));

+CREATE TABLE t2(id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, v1 VARCHAR(255)) ENGINE=InnoDB;

 ALTER TABLE t2 ADD COLUMN v2 VARCHAR(255), ALGORITHM=INSTANT;

 ALTER TABLE t2 DISCARD TABLESPACE;

@@ -514,14 +504,12 @@

 --copy_file $MYSQLD_DATADIR/t1.ibd_back $MYSQLD_DATADIR/test/t2.ibd

 --echo # IMPORT should fail.

---error ER_TABLE_SCHEMA_MISMATCH

 ALTER TABLE t2 IMPORT TABLESPACE;

+CHECK TABLE t2 EXTENDED;

 DROP TABLE t2;

---remove_file $MYSQLD_DATADIR/test/t2.cfg

---remove_file $MYSQLD_DATADIR/test/t2.ibd

 --echo # Create table t2 with 2 INSTANTly added columns

-CREATE TABLE t2(id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY);

+CREATE TABLE t2(id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY) ENGINE=InnoDB;

 ALTER TABLE t2 ADD COLUMN v1 VARCHAR(255), ALGORITHM=INSTANT;

 ALTER TABLE t2 ADD COLUMN v2 VARCHAR(255), ALGORITHM=INSTANT;

 ALTER TABLE t2 DISCARD TABLESPACE;

@@ -531,11 +519,9 @@

 --copy_file $MYSQLD_DATADIR/t1.ibd_back $MYSQLD_DATADIR/test/t2.ibd

 --echo # IMPORT should fail.

---error ER_TABLE_SCHEMA_MISMATCH

 ALTER TABLE t2 IMPORT TABLESPACE;

+CHECK TABLE t2 EXTENDED;

 DROP TABLE t2;

---remove_file $MYSQLD_DATADIR/test/t2.cfg

---remove_file $MYSQLD_DATADIR/test/t2.ibd

 --echo # Cleanup

 DROP TABLE t1;

 # is different from the exported table, the error message will complain about

 # column b in index b. The import will abort and delete the cfg file.

 ALTER TABLE t1 IMPORT TABLESPACE;

-ERROR HY000: Schema mismatch (Index b field b is ascending which does not match metadata file which is descending)

+CHECK TABLE t1 EXTENDED;

+Table	Op	Msg_type	Msg_text

+test.t1	check	Warning	InnoDB: The B-tree of index b is corrupted.

+test.t1	check	error	Corrupt

 DROP TABLE t1;