[MDEV-21624] Unique index length is able to exceed max key length Created: 2020-01-31  Updated: 2021-04-19  Resolved: 2020-08-27

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

Type: Bug Priority: Critical
Reporter: Geoff Montee (Inactive) Assignee: Sachin Setiya (Inactive)
Resolution: Not a Bug Votes: 0
Labels: None

Issue Links:
Problem/Incident
is caused by MDEV-371 Unique indexes for blobs Closed
Relates
relates to MDEV-20194 Warnings inconsistently issued upon C... Closed
relates to MDEV-23530 ERROR 1071: Specified key was too lon... Open

 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?

 Comments   
Comment by Geoff Montee (Inactive) [ 2020-01-31 ]

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

Comment by Marko Mäkelä [ 2020-02-01 ]

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.

Comment by Sergei Golubchik [ 2020-02-03 ]

MDEV-371 ?

Comment by Marko Mäkelä [ 2020-08-25 ]

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?

Comment by Marko Mäkelä [ 2020-08-25 ]

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?

Comment by Sergei Golubchik [ 2020-08-27 ]

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.

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