[MDEV-34951] Innodb index corruption issue when renaming key name with same letter to upper case - Jira

Details

Type: Bug
Status: Confirmed (View Workflow)
Priority: Critical
Resolution: Unresolved
Affects Version/s: 10.5, 10.6, 10.11, 11.2(EOL), 11.4, 11.6(EOL), 11.7(EOL), 11.8, 12.0
Fix Version/s: 10.5, 10.6, 10.11, 11.4, 11.8
Component/s: Data Definition - Alter Table, Storage Engine - InnoDB
Labels:
- index_corruption

Description

test case

--source include/have_innodb.inc

CREATE TABLE t1 (a INT,b INT,KEY(a),KEY(b)) ENGINE=INNODB;

ALTER TABLE t1 RENAME KEY b TO B;

DELETE FROM t1;

Leads to

CREATE TABLE t1 (a INT,b INT,KEY(a),KEY(b)) ENGINE=INNODB;

ALTER TABLE t1 RENAME KEY b TO B;

DELETE FROM t1;

Warnings:

Warning	1082	InnoDB: Table test/t1 contains 2 indexes inside InnoDB, which is different from the number of indexes 2 defined in the MariaDB

main.mytest                              [ fail ]  Found warnings/errors in server log file!

        Test ended at 2024-09-18 07:30:39

line

2024-09-18  7:30:39 4 [ERROR] Cannot find index B in InnoDB index dictionary.

2024-09-18  7:30:39 4 [ERROR] InnoDB indexes are inconsistent with what defined in .frm for table ./test/t1

2024-09-18  7:30:39 4 [ERROR] InnoDB could not find key no 1 with name B from dict cache for table test/t1

2024-09-18  7:30:39 4 [ERROR] InnoDB: Table test/t1 contains 2 indexes inside InnoDB, which is different from the number of indexes 2 defined in the MariaDB  Have you mixed up .frm files from different installations? See https://mariadb.com/kb/en/innodb-troubleshooting/

Attachments

Activity

Ascending order - Click to sort in descending order

Roel Van de Paar added a comment - 2025-01-25 04:07

Ran into the same. This looks quite serious?

--source include/have_innodb.inc

CREATE TABLE t (c INT UNIQUE) ENGINE=InnoDB;

ALTER TABLE t RENAME KEY c TO C;

ALTER TABLE t MODIFY C INT;

SHOW WARNINGS;

CS 11.4.5 6be42c7276cb342df81e18aa53868623a89abeec (Debug)
11.4.5-dbg>SHOW WARNINGS;
+---------+------+--------------------------------------------------------------------------------------------------------------------------------+
\| Level \| Code \| Message \|
+---------+------+--------------------------------------------------------------------------------------------------------------------------------+
\| Warning \| 1082 \| InnoDB: Table test/t contains 1 indexes inside InnoDB, which is different from the number of indexes 1 defined in the MariaDB \|
\| Warning \| 1082 \| InnoDB: Table test/t contains 2 indexes inside InnoDB, which is different from the number of indexes 1 defined in the MariaDB \|
+---------+------+--------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)

CS 11.4.5 6be42c7276cb342df81e18aa53868623a89abeec (Debug)
2025-01-25 15:05:16 4 [ERROR] Cannot find index C in InnoDB index dictionary.
2025-01-25 15:05:16 4 [ERROR] InnoDB indexes are inconsistent with what defined in .frm for table ./test/t
2025-01-25 15:05:16 4 [ERROR] InnoDB could not find key no 0 with name C from dict cache for table test/t
2025-01-25 15:05:16 4 [ERROR] InnoDB: Table test/t contains 1 indexes inside InnoDB, which is different from the number of indexes 1 defined in the .frm file. See https://mariadb.com/kb/en/innodb-troubleshooting/

2025-01-25 15:05:16 4 [ERROR] InnoDB: Table test/t contains 2 indexes inside InnoDB, which is different from the number of indexes 1 defined in the .frm file. See https://mariadb.com/kb/en/innodb-troubleshooting/

Roel Van de Paar added a comment - 2025-01-25 04:07 Ran into the same. This looks quite serious? --source include/have_innodb.inc CREATE TABLE t (c INT UNIQUE ) ENGINE=InnoDB; ALTER TABLE t RENAME KEY c TO C; ALTER TABLE t MODIFY C INT ; SHOW WARNINGS; CS 11.4.5 6be42c7276cb342df81e18aa53868623a89abeec (Debug) 11.4.5-dbg>SHOW WARNINGS; +---------+------+--------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+--------------------------------------------------------------------------------------------------------------------------------+ | Warning | 1082 | InnoDB: Table test/t contains 1 indexes inside InnoDB, which is different from the number of indexes 1 defined in the MariaDB | | Warning | 1082 | InnoDB: Table test/t contains 2 indexes inside InnoDB, which is different from the number of indexes 1 defined in the MariaDB | +---------+------+--------------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.000 sec) CS 11.4.5 6be42c7276cb342df81e18aa53868623a89abeec (Debug) 2025-01-25 15:05:16 4 [ERROR] Cannot find index C in InnoDB index dictionary. 2025-01-25 15:05:16 4 [ERROR] InnoDB indexes are inconsistent with what defined in .frm for table ./test/t 2025-01-25 15:05:16 4 [ERROR] InnoDB could not find key no 0 with name C from dict cache for table test/t 2025-01-25 15:05:16 4 [ERROR] InnoDB: Table test/t contains 1 indexes inside InnoDB, which is different from the number of indexes 1 defined in the .frm file. See https://mariadb.com/kb/en/innodb-troubleshooting/ 2025-01-25 15:05:16 4 [ERROR] InnoDB: Table test/t contains 2 indexes inside InnoDB, which is different from the number of indexes 1 defined in the .frm file. See https://mariadb.com/kb/en/innodb-troubleshooting/

Marko Mäkelä added a comment - 2025-01-27 15:54

I think that we need to be careful about this change. As far as I understand, the current version of the patch might break the exceptations regarding case insensitivity in CREATE INDEX and DROP INDEX. That is, it might allow both key b and B to be created simultaneously.

In MySQL there is a private worklog 922. From memory, it claims that that standard SQL behaves a bit like DCL in the VMS operating system with regard to case sensitivity in file names. That is, when identifiers are not quoted, they are being matched case insensitively. When they are quoted (MySQL and MariaDB prefer the non-standard ` but the SQL standard specifies "), the names would be matched in a case sensitive fashion. The topic of worklog 922 was to implement this for MySQL. To my understanding, this never happened, and instead MySQL and MariaDB use a strange mix of rules.

From the InnoDB point of view, column and index names are case sensitive. The InnoDB data dictionary does not have indexes on them, so even duplicate names (or names differing in case only) would be technically possible. This is mostly about implementing some additional rules around the main memory data structures. File names (or schema and table names) are a bit different; they are controlled by lower_case_table_names and some logic that is specific to Microsoft Windows; see MDEV-34953.

Marko Mäkelä added a comment - 2025-01-27 15:54 I think that we need to be careful about this change. As far as I understand, the current version of the patch might break the exceptations regarding case insensitivity in CREATE INDEX and DROP INDEX . That is, it might allow both key b and B to be created simultaneously. In MySQL there is a private worklog 922. From memory, it claims that that standard SQL behaves a bit like DCL in the VMS operating system with regard to case sensitivity in file names. That is, when identifiers are not quoted, they are being matched case insensitively. When they are quoted (MySQL and MariaDB prefer the non-standard ` but the SQL standard specifies "), the names would be matched in a case sensitive fashion. The topic of worklog 922 was to implement this for MySQL. To my understanding, this never happened, and instead MySQL and MariaDB use a strange mix of rules. From the InnoDB point of view, column and index names are case sensitive. The InnoDB data dictionary does not have indexes on them, so even duplicate names (or names differing in case only) would be technically possible. This is mostly about implementing some additional rules around the main memory data structures. File names (or schema and table names) are a bit different; they are controlled by lower_case_table_names and some logic that is specific to Microsoft Windows; see MDEV-34953 .

Alexander Barkov added a comment - 2025-01-28 05:13

Right, unfortunately we in MySQL/MariaDB never implemented the SQL standard way.

There is no difference between `indent` and ident. I.e. putting an identifier into back ticks does not make it case sensitive.

Case sensitivity depends on object type.

Please find in 11.5+ in sql/lex_ident.h the following identifier hierarchy:

Lex_cstring

  Lex_ident

    Lex_ident_fs               -  case sensitivity depends on the file system case sensitivity

      Lex_ident_db

        Lex_ident_db_normalized

      Lex_ident_table

      Lex_ident_trigger

    Lex_ident_ci               - always case insensitive

      Lex_ident_column   - this one is also used for indexes

      Lex_ident_sys_var

      Lex_ident_user_var

      Lex_ident_ps

      Lex_ident_i_s_db

      Lex_ident_i_s_table

      Lex_ident_window

      Lex_ident_routine

      Lex_ident_partition

      Lex_ident_with_element

      Lex_ident_rpl_filter

      Lex_ident_master_info

      Lex_ident_host

      Lex_ident_locale

      Lex_ident_plugin

      Lex_ident_engine

      Lex_ident_server

      Lex_ident_savepoint

      Lex_ident_charset

So from the server point of view. index names are always case insensitive.

With MyISAM it seems to work fine:

MariaDB [test]> CREATE TABLE t (c INT UNIQUE) ENGINE=MyISAM;

Query OK, 0 rows affected (0.009 sec)

MariaDB [test]> ALTER TABLE t RENAME KEY c TO C;

Query OK, 0 rows affected (0.016 sec)

Records: 0  Duplicates: 0  Warnings: 0

MariaDB [test]> ALTER TABLE t MODIFY C INT;

Query OK, 0 rows affected (0.022 sec)

Records: 0  Duplicates: 0  Warnings: 0

MariaDB [test]> show create table t;

+-------+----------------------------------------------------------------------------------------------------------------------------------------+

| Table | Create Table                                                                                                                           |

+-------+----------------------------------------------------------------------------------------------------------------------------------------+

| t     | CREATE TABLE `t` (

  `C` int(11) DEFAULT NULL,

  UNIQUE KEY `C` (`C`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci |

+-------+----------------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.001 sec)

Alexander Barkov added a comment - 2025-01-28 05:13 Right, unfortunately we in MySQL/MariaDB never implemented the SQL standard way. There is no difference between `indent` and ident. I.e. putting an identifier into back ticks does not make it case sensitive. Case sensitivity depends on object type. Please find in 11.5+ in sql/lex_ident.h the following identifier hierarchy: Lex_cstring Lex_ident Lex_ident_fs - case sensitivity depends on the file system case sensitivity Lex_ident_db Lex_ident_db_normalized Lex_ident_table Lex_ident_trigger Lex_ident_ci - always case insensitive Lex_ident_column - this one is also used for indexes Lex_ident_sys_var Lex_ident_user_var Lex_ident_ps Lex_ident_i_s_db Lex_ident_i_s_table Lex_ident_window Lex_ident_routine Lex_ident_partition Lex_ident_with_element Lex_ident_rpl_filter Lex_ident_master_info Lex_ident_host Lex_ident_locale Lex_ident_plugin Lex_ident_engine Lex_ident_server Lex_ident_savepoint Lex_ident_charset So from the server point of view. index names are always case insensitive. With MyISAM it seems to work fine: MariaDB [test]> CREATE TABLE t (c INT UNIQUE) ENGINE=MyISAM; Query OK, 0 rows affected (0.009 sec) MariaDB [test]> ALTER TABLE t RENAME KEY c TO C; Query OK, 0 rows affected (0.016 sec) Records: 0 Duplicates: 0 Warnings: 0 MariaDB [test]> ALTER TABLE t MODIFY C INT; Query OK, 0 rows affected (0.022 sec) Records: 0 Duplicates: 0 Warnings: 0 MariaDB [test]> show create table t; +-------+----------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+----------------------------------------------------------------------------------------------------------------------------------------+ | t | CREATE TABLE `t` ( `C` int(11) DEFAULT NULL, UNIQUE KEY `C` (`C`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci | +-------+----------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.001 sec)

Marko Mäkelä added a comment - 2025-01-28 09:35

Thank you, bar. I’m assigning this bug to you because you seem to be the expert in this area.

A file system analogy would be that the SQL layer currently incorrectly assumes that the InnoDB data dictionary does not preserve case, like Microsoft FAT, which stored all file names in upper case.

A more fitting analogy would be case-preserving, case-insensitive, like Microsoft VFAT and NTFS, or Apple HFS+.

The SQL layer needs to inform InnoDB when a column or index is being renamed from c to C. For column names, this had been revised in ~~MDEV-23852~~ in the handler::inplace_alter_table API.

Marko Mäkelä added a comment - 2025-01-28 09:35 Thank you, bar . I’m assigning this bug to you because you seem to be the expert in this area. A file system analogy would be that the SQL layer currently incorrectly assumes that the InnoDB data dictionary does not preserve case, like Microsoft FAT, which stored all file names in upper case. A more fitting analogy would be case-preserving, case-insensitive, like Microsoft VFAT and NTFS, or Apple HFS+. The SQL layer needs to inform InnoDB when a column or index is being renamed from c to C. For column names, this had been revised in MDEV-23852 in the handler::inplace_alter_table API.

Roel Van de Paar added a comment - 2025-03-06 08:02

--source include/have_innodb.inc

CREATE TABLE t (c POINT GENERATED ALWAYS AS (POINT(1,1)) UNIQUE) ENGINE=InnoDB;

ALTER TABLE t RENAME KEY c to C;

--error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION

INSERT INTO t VALUES (1);

and

--source include/have_innodb.inc

CREATE TABLE t (c POINT GENERATED ALWAYS AS (POINT(1,1)) UNIQUE) ENGINE=InnoDB;

ALTER TABLE t RENAME KEY c to C;

--error ER_WRONG_VALUE_COUNT_ON_ROW

INSERT INTO t VALUES (1,1);

Both lead to corruption. Example output:

CS 11.4.6 ef966af801afc2a07222b5df65dddd52c77431dd (Debug) Build 15/02/2025
2025-03-06 18:59:19 4 [ERROR] Cannot find index C in InnoDB index dictionary.
2025-03-06 18:59:19 4 [ERROR] InnoDB indexes are inconsistent with what defined in .frm for table ./test/t
2025-03-06 18:59:19 4 [ERROR] InnoDB could not find key no 0 with name C from dict cache for table test/t
2025-03-06 18:59:19 4 [ERROR] InnoDB: Table test/t contains 1 indexes inside InnoDB, which is different from the number of indexes 1 defined in the .frm file. See https://mariadb.com/kb/en/innodb-troubleshooting/

Roel Van de Paar added a comment - 2025-03-06 08:02 --source include/have_innodb.inc CREATE TABLE t (c POINT GENERATED ALWAYS AS (POINT(1,1)) UNIQUE ) ENGINE=InnoDB; ALTER TABLE t RENAME KEY c to C; --error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION INSERT INTO t VALUES (1); and --source include/have_innodb.inc CREATE TABLE t (c POINT GENERATED ALWAYS AS (POINT(1,1)) UNIQUE ) ENGINE=InnoDB; ALTER TABLE t RENAME KEY c to C; --error ER_WRONG_VALUE_COUNT_ON_ROW INSERT INTO t VALUES (1,1); Both lead to corruption. Example output: CS 11.4.6 ef966af801afc2a07222b5df65dddd52c77431dd (Debug) Build 15/02/2025 2025-03-06 18:59:19 4 [ERROR] Cannot find index C in InnoDB index dictionary. 2025-03-06 18:59:19 4 [ERROR] InnoDB indexes are inconsistent with what defined in .frm for table ./test/t 2025-03-06 18:59:19 4 [ERROR] InnoDB could not find key no 0 with name C from dict cache for table test/t 2025-03-06 18:59:19 4 [ERROR] InnoDB: Table test/t contains 1 indexes inside InnoDB, which is different from the number of indexes 1 defined in the .frm file. See https://mariadb.com/kb/en/innodb-troubleshooting/

People

Assignee:: Alexander Barkov

Reporter:: Ramesh Sivaraman

Votes:: 0 Vote for this issue

Watchers:: 4 Start watching this issue

Dates

Created:: 2024-09-18 04:55

Updated:: 2025-03-06 08:03

Git Integration

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

MariaDB Server