[MDEV-27432] ASC/DESC primary and unique keys cause index inconsistency between InnoDB and server Created: 2022-01-06  Updated: 2022-01-26  Resolved: 2022-01-07

Status: Closed
Project: MariaDB Server
Component/s: Data Definition - Alter Table, Storage Engine - InnoDB
Affects Version/s: N/A
Fix Version/s: 10.8.1

Type: Bug Priority: Critical
Reporter: Elena Stepanova Assignee: Thirunarayanan Balathandayuthapani
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Problem/Incident
is caused by MDEV-13756 Implement descending index: KEY (a DE... Closed
Relates
relates to MDEV-27374 InnoDB table becomes corrupt after re... Closed

 Description    

--source include/have_innodb.inc
 
 
 
CREATE TABLE t (id INT NOT NULL, UNIQUE(id DESC)) ENGINE=InnoDB;
 
ALTER TABLE t ADD PRIMARY KEY (id), ALGORITHM=INPLACE; # Also NOCOPY, DEFAULT/none
 
SHOW CREATE TABLE t;
 
 
 
# Cleanup
 
DROP TABLE t;

preview-10.8-MDEV-13756-desc-indexes 43444ff5d

 
2022-01-06 16:50:24 4 [ERROR] Found index PRIMARY whose column info does not match that of MariaDB.
 
2022-01-06 16:50:24 4 [ERROR] InnoDB indexes are inconsistent with what defined in .frm for table ./test/t

ALTER is important, direct CREATE with the resulting table definition doesn't cause the error.
ALGORITHM has been added for clarity, absence of any algorithm clause leads to the same result.
Also reproducible with reverse ASC/DESC attributes (ASC unique key and DESC PK).
Not reproducible when both are ASC or both are DESC.
Not reproducible with ALGORITHM=COPY.

 Comments   
Comment by Elena Stepanova [ 2022-01-06 ]

The error isn't bogus, here is a variation of the test case which additionally produces a wrong result:

--source include/have_innodb.inc
 
 
 
CREATE OR REPLACE TABLE t (
 
  id bigint NOT NULL,
 
  c varchar(8) NOT NULL,
 
  UNIQUE KEY id (id)
 
) ENGINE=InnoDB;
 
 
 
ALTER TABLE t ADD PRIMARY KEY (id DESC);
 
 
 
INSERT INTO t VALUES (1,''),(2,'foo'),(3,''),(4,'bar');
 
 
 
SELECT * FROM t WHERE id > 2 OR c IS NULL;
 
 
 
# Cleanup
 
DROP TABLE t;

preview-10.8-MDEV-13756-desc-indexes 43444ff5d

 
SELECT * FROM t WHERE id > 2 OR c IS NULL;
 
id	c
 
DROP TABLE t;
 
bug.t2 'innodb'                          [ fail ]  Found warnings/errors in server log file!
 
        Test ended at 2022-01-06 21:35:15
 
line
 
2022-01-06 21:35:15 4 [ERROR] Found index PRIMARY whose column info does not match that of MariaDB.
 
2022-01-06 21:35:15 4 [ERROR] InnoDB indexes are inconsistent with what defined in .frm for table ./test/t

Comment by Marko Mäkelä [ 2022-01-07 ]

thiru, since you already fixed the similar issue MDEV-27374, could you please fix this one as well? I suspect that something in prepare_inplace_alter_table_dict() or its callees must be changed.

Generated at Thu Feb 08 09:52:52 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.