[MDEV-29203] Incorrect information in file: ... #sql-alter ... .frm on ALTER when adding unique hash index, ERROR 1901 Created: 2022-07-29  Updated: 2023-11-28

Status: Confirmed
Project: MariaDB Server
Component/s: Server
Affects Version/s: 10.4, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10, 10.11, 11.0, 11.1
Fix Version/s: 10.4, 10.5, 10.6, 10.11, 11.0, 11.1

Type: Bug Priority: Major
Reporter: Roel Van de Paar Assignee: Oleksandr Byelkin
Resolution: Unresolved Votes: 0
Labels: corruption, regression

Issue Links:
Duplicate
is duplicated by MDEV-28192 ERROR 1901 During ALTER Leading to fr... Closed
is duplicated by MDEV-28238 Incorrect information in file: './tes... Closed
Problem/Incident
is caused by MDEV-371 Unique indexes for blobs Closed

 Description   

Present in InnoDB, MyISAM, Aria but not in MEMORY.

CREATE TABLE t (b INT AUTO_INCREMENT KEY);
 
CREATE UNIQUE INDEX i USING HASH ON t(b);

or

CREATE TABLE t (b INT AUTO_INCREMENT KEY);
 
ALTER TABLE t ADD UNIQUE id USING HASH (b);

Leads to:

10.10.0 e1caa4bd5e8b4645944b85d4b603bf9fc9ef6ca4 (Optimized)

 
10.10.0-opt>ALTER TABLE t ADD UNIQUE id USING HASH (b);
 
ERROR 1901 (HY000): Function or expression 'AUTO_INCREMENT' cannot be used in the USING HASH clause of `b`

10.10.0 e1caa4bd5e8b4645944b85d4b603bf9fc9ef6ca4 (Optimized)

 
2022-07-29 18:36:43 4 [ERROR] mysqld: Incorrect information in file: './test/#sql-alter-d1832-4.frm'

Bug confirmed present in:
MariaDB: 10.4.26 (dbg), 10.4.26 (opt), 10.5.17 (dbg), 10.5.17 (opt), 10.6.9 (dbg), 10.6.9 (opt), 10.7.5 (dbg), 10.7.5 (opt), 10.8.4 (dbg), 10.8.4 (opt), 10.9.2 (dbg), 10.9.2 (opt), 10.10.0 (dbg), 10.10.0 (opt)

Bug (or feature/syntax) confirmed not present in:
MariaDB: 10.3.36 (dbg), 10.3.36 (opt)
MySQL: 5.5.62 (dbg), 5.5.62 (opt), 5.6.51 (dbg), 5.6.51 (opt), 5.7.38 (dbg), 5.7.38 (opt), 8.0.29 (dbg), 8.0.29 (opt)

 Comments   
Comment by Silver Asu [ 2023-05-30 ]

Is there any workaround for fixing this table? Everything (SHOW CREATE TABLE, DESCRIBE, mysqldump, etc) gives this error:

ERROR 1901 (HY000): Function or expression 'AUTO_INCREMENT' cannot be used in the USING HASH clause of `id`

Comment by Roel Van de Paar [ 2023-06-05 ]

BB That is interesting. In the testcase above, only the ALTER fails, but the table itself remains healthy. Do you have a testcase which causes permanent table corruption? (Even if it is long, we can reduce it).

Generated at Thu Feb 08 10:06:42 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.