[MDEV-21624] Unique index length is able to exceed max key length - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Critical
Resolution: Not a Bug
Affects Version/s: 10.4.12
Fix Version/s: N/A
Component/s: Data Definition - Alter Table, Storage Engine - InnoDB
Labels:
None

Description

It appears that unique indexes are able to exceed the max key length in some cases.

For example, if we tried to add the following primary key, then it would fail:

CREATE OR REPLACE TABLE tab (

  col1 smallint(6) DEFAULT NULL,

  col2 smallint(6) DEFAULT NULL,

  col3 bigint(20) DEFAULT NULL,

  col4 bigint(20) DEFAULT NULL,

  col5 varchar(64) DEFAULT NULL,

  col6 varchar(64) DEFAULT NULL,

  col7 varchar(128) DEFAULT NULL,

  col8 varchar(20) DEFAULT NULL,

  col9 varchar(128) DEFAULT NULL,

  col10 varchar(128) DEFAULT NULL,

  col11 varchar(2000) DEFAULT NULL,

  col12 double DEFAULT NULL,

  col13 double DEFAULT NULL,

  col14 double DEFAULT NULL,

  col15 double DEFAULT NULL,

  col16 tinyint(4) DEFAULT NULL,

  col17 double DEFAULT NULL,

  col18 double DEFAULT NULL,

  KEY col4_idx (col4)

) DEFAULT CHARACTER SET = utf8mb4;

ALTER TABLE tab

   ADD PRIMARY KEY

   (col1, col2, col3, col4, col5, col6, col7, col8, col9, col10, col11);

And for example, if we tried to add the following non-unique secondary index, then it would also fail:

CREATE OR REPLACE TABLE tab (

  col1 smallint(6) DEFAULT NULL,

  col2 smallint(6) DEFAULT NULL,

  col3 bigint(20) DEFAULT NULL,

  col4 bigint(20) DEFAULT NULL,

  col5 varchar(64) DEFAULT NULL,

  col6 varchar(64) DEFAULT NULL,

  col7 varchar(128) DEFAULT NULL,

  col8 varchar(20) DEFAULT NULL,

  col9 varchar(128) DEFAULT NULL,

  col10 varchar(128) DEFAULT NULL,

  col11 varchar(2000) DEFAULT NULL,

  col12 double DEFAULT NULL,

  col13 double DEFAULT NULL,

  col14 double DEFAULT NULL,

  col15 double DEFAULT NULL,

  col16 tinyint(4) DEFAULT NULL,

  col17 double DEFAULT NULL,

  col18 double DEFAULT NULL,

  KEY col4_idx (col4)

) DEFAULT CHARACTER SET = utf8mb4;

CREATE INDEX tab_idx

   ON tab

   (col1, col2, col3, col4, col5, col6, col7, col8, col9, col10, col11);

We see the following error in both cases:

ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes

However, for some reason, if we tried to add the following unique index, then it would succeed:

CREATE OR REPLACE TABLE tab (

  col1 smallint(6) DEFAULT NULL,

  col2 smallint(6) DEFAULT NULL,

  col3 bigint(20) DEFAULT NULL,

  col4 bigint(20) DEFAULT NULL,

  col5 varchar(64) DEFAULT NULL,

  col6 varchar(64) DEFAULT NULL,

  col7 varchar(128) DEFAULT NULL,

  col8 varchar(20) DEFAULT NULL,

  col9 varchar(128) DEFAULT NULL,

  col10 varchar(128) DEFAULT NULL,

  col11 varchar(2000) DEFAULT NULL,

  col12 double DEFAULT NULL,

  col13 double DEFAULT NULL,

  col14 double DEFAULT NULL,

  col15 double DEFAULT NULL,

  col16 tinyint(4) DEFAULT NULL,

  col17 double DEFAULT NULL,

  col18 double DEFAULT NULL,

  KEY col4_idx (col4)

) DEFAULT CHARACTER SET = utf8mb4;

CREATE UNIQUE INDEX tab_unq_idx

   ON tab

   (col1, col2, col3, col4, col5, col6, col7, col8, col9, col10, col11);

Is it intentional for the unique index to have a different max key length?

Attachments

Issue Links

is caused by

MDEV-371 Unique indexes for blobs

Closed

relates to

MDEV-20194 Warnings inconsistently issued upon CHECK on table from older versions

Closed

MDEV-23530 ERROR 1071: Specified key was too long; max key length is 1000/2000 bytes on CREATE TABLE ... UNIQUE KEY ... USING HASH

Open

Activity

Ascending order - Click to sort in descending order

Geoff Montee (Inactive) added a comment - 2020-01-31 23:57

This issue does not affect MariaDB 10.2.31 or MariaDB 10.3.22. It only seems to affect MariaDB 10.4.12.

Geoff Montee (Inactive) added a comment - 2020-01-31 23:57 This issue does not affect MariaDB 10.2.31 or MariaDB 10.3.22. It only seems to affect MariaDB 10.4.12.

Marko Mäkelä added a comment - 2020-02-01 07:08

There should not be anything special about unique indexes in InnoDB. Even for non-leaf pages, PRIMARY KEY columns are included, as reported in
MySQL Bug#68546 InnoDB stores PRIMARY KEY fields in unique secondary index non-leaf pages.
I think that this should be investigated and addressed as part of ~~MDEV-20194~~.
I have provided some review comments related to fixing wrong maximum-size calculations.

Marko Mäkelä added a comment - 2020-02-01 07:08 There should not be anything special about unique indexes in InnoDB. Even for non-leaf pages, PRIMARY KEY columns are included, as reported in MySQL Bug#68546 InnoDB stores PRIMARY KEY fields in unique secondary index non-leaf pages . I think that this should be investigated and addressed as part of MDEV-20194 . I have provided some review comments related to fixing wrong maximum-size calculations.

Sergei Golubchik added a comment - 2020-02-03 07:21

~~MDEV-371~~ ?

Sergei Golubchik added a comment - 2020-02-03 07:21 MDEV-371 ?

Marko Mäkelä added a comment - 2020-08-25 11:32

Yes, this is caused by ~~MDEV-371~~. I verified it by executing the following query after creating the unique index:

SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS WHERE NAME LIKE 'DB%';

For the unique index, an index on a hidden virtual column DB_ROW_HASH_1 would be created instead.

I do not think that there is anything that can be fixed in the storage engine, and I do not know if this can be treated as a bug at all. Maybe this should simply be treated as a documentation issue?

Marko Mäkelä added a comment - 2020-08-25 11:32 Yes, this is caused by MDEV-371 . I verified it by executing the following query after creating the unique index: SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS WHERE NAME LIKE 'DB%' ; For the unique index, an index on a hidden virtual column DB_ROW_HASH_1 would be created instead. I do not think that there is anything that can be fixed in the storage engine, and I do not know if this can be treated as a bug at all. Maybe this should simply be treated as a documentation issue?

Marko Mäkelä added a comment - 2020-08-25 11:35

Actually, to work around bugs related to ~~MDEV-371~~ or indexed virtual columns (~~MDEV-5800~~), it might be useful to be able to disable the ~~MDEV-371~~ logic on DDL, perhaps by an optimizer_switch or sql_mode, or by some magic comment in the DDL statement?

Marko Mäkelä added a comment - 2020-08-25 11:35 Actually, to work around bugs related to MDEV-371 or indexed virtual columns ( MDEV-5800 ), it might be useful to be able to disable the MDEV-371 logic on DDL, perhaps by an optimizer_switch or sql_mode , or by some magic comment in the DDL statement?

Sergei Golubchik added a comment - 2020-08-27 07:21

It's not a bug, MariaDB since 10.4 allows creating unique constraints of arbitrary length. A constraint is a logical object in the database and since 10.4 you can specify that "values in these columns should be unique" without thinking about implementation details and limitations of a particular storage engine.

The second statement failing is a bug, though. Creating non-unique indexes should not fail with "key too long" — the server automatically shortens the index to fit. And for some reason this doesn't happen in your example.

Sergei Golubchik added a comment - 2020-08-27 07:21 It's not a bug, MariaDB since 10.4 allows creating unique constraints of arbitrary length. A constraint is a logical object in the database and since 10.4 you can specify that "values in these columns should be unique" without thinking about implementation details and limitations of a particular storage engine. The second statement failing is a bug, though. Creating non-unique indexes should not fail with "key too long" — the server automatically shortens the index to fit. And for some reason this doesn't happen in your example.

People

Assignee:: Sachin Setiya (Inactive)

Reporter:: Geoff Montee (Inactive)

Votes:: 0 Vote for this issue

Watchers:: 5 Start watching this issue

Dates

Created:: 2020-01-31 23:27

Updated:: 2024-07-07 22:14

Resolved:: 2020-08-27 07:21

Git Integration

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

MariaDB Server