[MDEV-35169] ALTER TABLE...IMPORT TABLESPACE does not work with INDEX DESC - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.8(EOL), 10.9(EOL), 10.10(EOL), 10.11, 11.4
Fix Version/s: 10.11.11, 11.4.5, 11.7.2
Component/s: Storage Engine - InnoDB
Labels:
- regression

Description

When a test is imported and adjusted from MySQL 8.0.40, it would fail as follows:

10.11 8a6a4c947a0ca3d2fdca752d7440bdc5c6c83e37
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

Here is the adjustment that I applied to the test:

--- 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;

At least the following difference of the .result file when compared to MySQL 8.0.40 highlights the problem:

 # 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;

This is something that I overlooked in ~~MDEV-13756~~. We should do something to support descending indexes in the .cfg files. I'd prefer a minimal change, because in the long term I want to get rid of these files, in MDEV-11658.

Attachments

Issue Links

is caused by

MDEV-13756 Implement descending index: KEY (a DESC, b ASC)

Closed

relates to

MDEV-23571 InnoDB does not raise a warning if ALTER TABLE index operations are optimized away

Open

MDEV-35849 InnoDB: index records in a wrong order upon CHECK with DESC index

Closed

Activity

Ascending order - Click to sort in descending order

Elena Stepanova added a comment - 2025-01-14 20:33

For a note, it doesn't fail on any IMPORT TABLESPACE, but on non-matching ones – that is, if the tablespace was exported from a table with an ASC index, but imported into a table with a DESC index (that's what the relevant part of the MySQL test does, as far as I understand), and vice versa.

10.8.8
--source include/have_innodb.inc

--let $datadir= `select @@datadir`

create table t1 (pk int primary key, a int, key(a desc)) engine=InnoDB;
insert into t1 values (1,10),(2,20),(3,15);
flush table t1 for export;
--copy_file $datadir/test/t1.ibd $datadir/test/t1.ibd.desc
--copy_file $datadir/test/t1.cfg $datadir/test/t1.cfg.desc
unlock tables;
drop table t1;

create table t1 (pk int primary key, a int, key(a asc)) engine=InnoDB;
insert into t1 values (1,10),(2,20),(3,15);
flush table t1 for export;
--copy_file $datadir/test/t1.ibd $datadir/test/t1.ibd.asc
--copy_file $datadir/test/t1.cfg $datadir/test/t1.cfg.asc
unlock tables;
drop table t1;

--echo #
--echo #Import desc tablespace into desc frm
--echo #
create table t1 (pk int primary key, a int, key(a desc)) engine=InnoDB;
alter table t1 discard tablespace;
--copy_file $datadir/test/t1.ibd.desc $datadir/test/t1.ibd
--copy_file $datadir/test/t1.cfg.desc $datadir/test/t1.cfg
alter table t1 import tablespace;
check table t1 extended;
drop table t1;

--echo #
--echo #Import asc tablespace into desc frm
--echo #
create table t1 (pk int primary key, a int, key(a desc)) engine=InnoDB;
alter table t1 discard tablespace;
--copy_file $datadir/test/t1.ibd.asc $datadir/test/t1.ibd
--copy_file $datadir/test/t1.cfg.asc $datadir/test/t1.cfg
alter table t1 import tablespace;
check table t1 extended;
drop table t1;

--echo #
--echo #Import desc tablespace into asc frm
--echo #
create table t1 (pk int primary key, a int, key(a asc)) engine=InnoDB;
alter table t1 discard tablespace;
--copy_file $datadir/test/t1.ibd.desc $datadir/test/t1.ibd
--copy_file $datadir/test/t1.cfg.desc $datadir/test/t1.cfg
alter table t1 import tablespace;
check table t1 extended;
drop table t1;

--echo #
--echo #Import asc tablespace into asc frm
--echo #
create table t1 (pk int primary key, a int, key(a asc)) engine=InnoDB;
alter table t1 discard tablespace;
--copy_file $datadir/test/t1.ibd.asc $datadir/test/t1.ibd
--copy_file $datadir/test/t1.cfg.asc $datadir/test/t1.cfg
alter table t1 import tablespace;
check table t1 extended;
drop table t1;

10.8.8
#Import desc tablespace into desc frm
...
check table t1 extended;
Table Op Msg_type Msg_text
test.t1 check status OK
...
#Import asc tablespace into desc frm
...
check table t1 extended;
Table Op Msg_type Msg_text
test.t1 check Warning InnoDB: The B-tree of index a is corrupted.
test.t1 check error Corrupt
...
#Import desc tablespace into asc frm
...
Table Op Msg_type Msg_text
test.t1 check Warning InnoDB: The B-tree of index a is corrupted.
test.t1 check error Corrupt
...
#Import asc tablespace into asc frm
...
check table t1 extended;
Table Op Msg_type Msg_text
test.t1 check status OK

Elena Stepanova added a comment - 2025-01-14 20:33 For a note, it doesn't fail on any IMPORT TABLESPACE, but on non-matching ones – that is, if the tablespace was exported from a table with an ASC index, but imported into a table with a DESC index (that's what the relevant part of the MySQL test does, as far as I understand), and vice versa. 10.8.8 --source include/have_innodb.inc --let $datadir= `select @@datadir` create table t1 (pk int primary key , a int , key (a desc )) engine=InnoDB; insert into t1 values (1,10),(2,20),(3,15); flush table t1 for export; --copy_file $datadir/test/t1.ibd $datadir/test/t1.ibd.desc --copy_file $datadir/test/t1.cfg $datadir/test/t1.cfg.desc unlock tables; drop table t1; create table t1 (pk int primary key , a int , key (a asc )) engine=InnoDB; insert into t1 values (1,10),(2,20),(3,15); flush table t1 for export; --copy_file $datadir/test/t1.ibd $datadir/test/t1.ibd.asc --copy_file $datadir/test/t1.cfg $datadir/test/t1.cfg.asc unlock tables; drop table t1; --echo # --echo #Import desc tablespace into desc frm --echo # create table t1 (pk int primary key , a int , key (a desc )) engine=InnoDB; alter table t1 discard tablespace; --copy_file $datadir/test/t1.ibd.desc $datadir/test/t1.ibd --copy_file $datadir/test/t1.cfg.desc $datadir/test/t1.cfg alter table t1 import tablespace; check table t1 extended; drop table t1; --echo # --echo #Import asc tablespace into desc frm --echo # create table t1 (pk int primary key , a int , key (a desc )) engine=InnoDB; alter table t1 discard tablespace; --copy_file $datadir/test/t1.ibd.asc $datadir/test/t1.ibd --copy_file $datadir/test/t1.cfg.asc $datadir/test/t1.cfg alter table t1 import tablespace; check table t1 extended; drop table t1; --echo # --echo #Import desc tablespace into asc frm --echo # create table t1 (pk int primary key , a int , key (a asc )) engine=InnoDB; alter table t1 discard tablespace; --copy_file $datadir/test/t1.ibd.desc $datadir/test/t1.ibd --copy_file $datadir/test/t1.cfg.desc $datadir/test/t1.cfg alter table t1 import tablespace; check table t1 extended; drop table t1; --echo # --echo #Import asc tablespace into asc frm --echo # create table t1 (pk int primary key , a int , key (a asc )) engine=InnoDB; alter table t1 discard tablespace; --copy_file $datadir/test/t1.ibd.asc $datadir/test/t1.ibd --copy_file $datadir/test/t1.cfg.asc $datadir/test/t1.cfg alter table t1 import tablespace; check table t1 extended; drop table t1; 10.8.8 #Import desc tablespace into desc frm ... check table t1 extended; Table Op Msg_type Msg_text test.t1 check status OK ... #Import asc tablespace into desc frm ... check table t1 extended; Table Op Msg_type Msg_text test.t1 check Warning InnoDB: The B-tree of index a is corrupted. test.t1 check error Corrupt ... #Import desc tablespace into asc frm ... Table Op Msg_type Msg_text test.t1 check Warning InnoDB: The B-tree of index a is corrupted. test.t1 check error Corrupt ... #Import asc tablespace into asc frm ... check table t1 extended; Table Op Msg_type Msg_text test.t1 check status OK

Thirunarayanan Balathandayuthapani added a comment - 2025-01-23 10:50

https://github.com/MariaDB/server/pull/3784

Thirunarayanan Balathandayuthapani added a comment - 2025-01-23 10:50 https://github.com/MariaDB/server/pull/3784

Marko Mäkelä added a comment - 2025-01-23 11:20

I came up with a simpler change of the .cfg file format. We’re wasting 32 bits for storing the name of an index. We could store a flag "descending fields exist" flag there, to have older server versions flag a clearly recognizable error:

		/* The NUL byte is included in the name length. */

		ulint	len = mach_read_from_4(ptr);

		if (len > OS_FILE_MAX_PATH) {

			ib_errf(thd, IB_LOG_LEVEL_ERROR,

				ER_INNODB_INDEX_CORRUPT,

				"Index name length (" ULINTPF ") is too long, "

				"the meta-data is corrupt", len);

			return(DB_CORRUPTION);

The "field is descending" flag itself could be stored in one of the 32-bit fields that are reserved for prefix_len and fixed_len. More than half of the bits in each is currently wasted, and guaranteed to be 0.

Marko Mäkelä added a comment - 2025-01-23 11:20 I came up with a simpler change of the .cfg file format. We’re wasting 32 bits for storing the name of an index. We could store a flag "descending fields exist" flag there, to have older server versions flag a clearly recognizable error: /* The NUL byte is included in the name length. */ ulint len = mach_read_from_4(ptr); if (len > OS_FILE_MAX_PATH) { ib_errf(thd, IB_LOG_LEVEL_ERROR, ER_INNODB_INDEX_CORRUPT, "Index name length (" ULINTPF ") is too long, " "the meta-data is corrupt" , len); return (DB_CORRUPTION); } The "field is descending" flag itself could be stored in one of the 32-bit fields that are reserved for prefix_len and fixed_len . More than half of the bits in each is currently wasted, and guaranteed to be 0.

Marko Mäkelä added a comment - 2025-01-24 14:20

The latest revision would only repurpose the most significant bit of the 32 bits that until now stored 22 always-zero bits and 10 bits of field->fixed_len. This should allow older versions of MariaDB to parse the .cfg file, ignoring the DESC flag. I have requested thiru to test an ALTER TABLE…IMPORT TABLESPACE of such a .cfg file with MariaDB Server 10.6 and 10.11 without this fix.

Marko Mäkelä added a comment - 2025-01-24 14:20 The latest revision would only repurpose the most significant bit of the 32 bits that until now stored 22 always-zero bits and 10 bits of field->fixed_len . This should allow older versions of MariaDB to parse the .cfg file, ignoring the DESC flag. I have requested thiru to test an ALTER TABLE…IMPORT TABLESPACE of such a .cfg file with MariaDB Server 10.6 and 10.11 without this fix.

People

Assignee:: Thirunarayanan Balathandayuthapani

Reporter:: Marko Mäkelä

Votes:: 0 Vote for this issue

Watchers:: 4 Start watching this issue

Dates

Created:: 2024-10-16 08:03

Updated:: 2025-01-30 10:54

Resolved:: 2025-01-28 09:01

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.