MySQL Bugs: #111323: Index column size too large. The maximum column size is 767 bytes.

Bug #111323	Index column size too large. The maximum column size is 767 bytes.
Submitted:	7 Jun 2023 20:22	Modified:	12 Jun 2023 16:44
Reporter:	Gavin Towey	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	8.0.28	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Despite similar bugs being fixed already, the conditions that allow this to happen on a table still exists.

Similar bugs:
https://bugs.mysql.com/bug.php?id=102597
https://bugs.mysql.com/bug.php?id=99791

However if you have a table that was already created with row_format=Compact from previous versions, you can still successfully perform a bad ALTER TABLE and corrupt the table.

How to repeat:
1. Starting with MySQL 8.0.21

USE test;
CREATE TABLE bad ( a VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL, UNIQUE KEY (a) ) ENGINE=INNODB ROW_FORMAT=Compact;

2. Upgrade to version 8.0.28

3. Confirm tablespace has server_version = 8.0.21

select space, name, row_format, server_version, space_version, state from INNODB_TABLESPACES where name like '%bad%';
+-------+----------------------------+----------------------+----------------+---------------+--------+
| space | name                       | row_format           | server_version | space_version | state  |
+-------+----------------------------+----------------------+----------------+---------------+--------+
|   854 | test/bad                   | Compact or Redundant | 8.0.21         |             1 | normal |
+-------+----------------------------+----------------------+----------------+---------------+--------+

4. alter the table

USE test;
ALTER TABLE bad MODIFY a VARCHAR(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL;

5. reboot the server

6. everything now fails

select count(*) from test.bad;
ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes.

DROP TABLE test.bad;
ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes.

Suggested fix:
* all DDL operations must check row_format of table for constraint violations prior to making changes.

* DROP / RENAME table should be available even when a table is corrupted.

Hi Mr. Towev,

Thank you for your bug report.

However, we can not repeat it with 8.0.33.

We tested with 8.0.24 and 8.0.33.

But, we got immediately on ALTER TABLE the ERROR 1709.

And this is the expected error. This is from our Reference Manual:

"
The REDUNDANT and COMPACT row formats support a maximum index key prefix length of 767 bytes whereas DYNAMIC and COMPRESSED row formats support an index key prefix length of 3072 bytes. 
"

Regarding DDL commands that do not work, try with FORCE keyword, as per our Ref. Manual.

Can't repeat.

Sorry, I messed up step 1 a bit initially.  It should be:

1. Starting with MySQL 8.0.21

USE test;
SET GLOBAL innodb_default_row_format = 'Compact';
CREATE TABLE bad ( a VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL, UNIQUE KEY (a) ) ENGINE=INNODB;

The change is that the table must be created with the Compact row format BUT WITHOUT using the table create option.  It must be inherited from the global defaults.

After that, you should be able to see the issue when following the rest of the steps.

Hi,

No, we can not repeat it with 8.0.33 even in that manner .......

The same error occurs ......

Steps to reproduce the bug

Attachment: mysql_alter_table_bug_repro.txt (text/plain), 1.47 KiB.

I've added a file which reproduces this bug using docker containers for the mysql server.  These exact steps will show the problem.

Hi,

we are still unable to reproduce it with our own 8.0.33  binary.

Also, do note that each row format in the InnoDB tables have a limit on the index size (in bytes), depending on the row format.

This is all explained in our Reference Manual.

Also, please be notified that we do not accept test cases with Docker or other container software.

> we are still unable to reproduce it with our own 8.0.33  binary.

Are you starting with creating the table in version 8.0.21 or earlier?  This is an important part of exposing the bug.

Hi,

We have created table in 8.0.18.