| Bug #115113 | VARCHAR column extension leads to larger index than allowed in compact row_form | ||
|---|---|---|---|
| Submitted: | 24 May 2024 8:28 | Modified: | 19 Jun 2024 10:11 |
| Reporter: | Gabriel Barcelo | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: Data Dictionary | Severity: | S3 (Non-critical) |
| Version: | 8.0.37;8.4.0 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | compact, corruption, utf8mb4 | ||
[24 May 2024 8:28]
Gabriel Barcelo
[24 May 2024 10:06]
MySQL Verification Team
Hi Mr. Barcelo, Thank you for your bug report ...... However, we can not repeat it with our 8.0.37 binary. We get the following error: ERROR 1709 (HY000) at line 20: Index column size too large. The maximum column size is 767 bytes. Line 20 is: ALTER TABLE test123 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; Can't repeat.
[27 May 2024 6:58]
Gabriel Barcelo
Error is still reproducible, need `my.ini` as in pastebin link (subs. paths as needed). I initialized MySQL with `my.ini` from pastebin already populated if it matters...
PS [F:\mysql-8.0.37-winx64_testing_bug] > .\bin\mysqld.exe --initialize-insecure
PS [F:\mysql-8.0.37-winx64_testing_bug] > .\bin\mysqld.exe
More compact script execution:
1st
create database test;
use test;
set global innodb_default_row_format='compact';
CREATE TABLE `test123` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`comment` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
insert into test123(comment) values('kncfosdncfvlknsadkvnalksdnvlkansdlkvcnalkdsncvlkasnfcklanskldnlaksndklasndlkanslkdnaslkndlkasndklanslkdnkncfosdncfvlknsadkvnalksdnvlkanpllkvcnalkdsncvlkasnfcklanskldnlaksndklasndlkanslkdnaslkndlkasndklanslkdnkncfoplncfvlknsadkvnalkplnvlkanpllkvcnalkdsncq');
show index from test123;
create index idx123 on test123 (`comment`);
ALTER DATABASE test CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE test123 MODIFY COLUMN comment VARCHAR(191);
ALTER TABLE test123 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- Corruption will start here:
ALTER TABLE test123 MODIFY COLUMN comment VARCHAR(255);
-- Ret OK, BUT WHY THIS IS ALLOWED!
-- Query OK, 0 rows affected (0.00 sec)
-- Records: 0 Duplicates: 0 Warnings: 0
SHUTDOWN;
-- OR RESTART MYSQLD instance. This will silently kill our table!
2nd (after 1 restart)
-- START AGAIN MYSQL
use test
select * from test123;
-- TABLE KO!: ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes.
-- Now table is corrupted, data cannot be retrieved from this table, nor in recovery mode.
[27 May 2024 7:41]
Gabriel Barcelo
Reproduce with `podman` and Linux arm64v8 (if amd64 change image to 'mysql:8.0.37')
mkdir -p mysql-8_0_37_bug/data
cd mysql-8_0_37_bug
podman pull arm64v8/mysql:8.0.37
# Server
podman run -v ./data:/var/lib/mysql --name myBUG -e MYSQL_ROOT_PASSWORD="" -e MYSQL_ALLOW_EMPTY_PASSWORD=True -d arm64v8/mysql:8.0.37 --sql_mode=""
sleep 1
# client (exec same container)
podman exec -it myBUG mysql -hmyBUG -uroot
# CORRUPTION SCRIPT (script 1)
create database test;
use test;
set global innodb_default_row_format='compact';
CREATE TABLE `test123` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`comment` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
insert into test123(comment) values('kncfosdncfvlknsadkvnalksdnvlkansdlkvcnalkdsncvlkasnfcklanskldnlaksndklasndlkanslkdnaslkndlkasndklanslkdnkncfosdncfvlknsadkvnalksdnvlkanpllkvcnalkdsncvlkasnfcklanskldnlaksndklasndlkanslkdnaslkndlkasndklanslkdnkncfoplncfvlknsadkvnalkplnvlkanpllkvcnalkdsncq');
show index from test123;
create index idx123 on test123 (`comment`);
ALTER DATABASE test CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE test123 MODIFY COLUMN comment VARCHAR(191);
ALTER TABLE test123 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- Corruption will start here:
ALTER TABLE test123 MODIFY COLUMN comment VARCHAR(255);
-- Ret OK, BUT WHY THIS IS ALLOWED!
-- Query OK, 0 rows affected (0.00 sec)
-- Records: 0 Duplicates: 0 Warnings: 0
SHUTDOWN;
-- OR RESTART MYSQLD instance. This will silently kill our table!
podman start myBUG
podman exec -it myBUG mysql -hmyBUG -uroot
# 2nd (after 1 restart)
-- START AGAIN MYSQL
use test
select * from test123;
-- TABLE KO!: ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes.
-- Now table is corrupted, data cannot be retrieved from this table, nor in recovery mode.
## Clean
podman kill myBUG
podman rm myBUG
rm -rf data/*
podman image rm arm64v8/mysql:8.0.37
[27 May 2024 7:52]
Gabriel Barcelo
I was able to reproduce the bug using the arm64v8/mysql:8.4 (mysql:8.4) container, following the same procedure as before. Best, Gabriel Barceló
[27 May 2024 9:34]
MySQL Verification Team
Hi MR .Barcelo, We do not use containers for testing. We were not able to repeat it with 8.4.0.
[27 May 2024 10:24]
MySQL Verification Team
Hi Mr. Barcelo, Still can't repeat. We get this error from your latest script: ERROR 1406 (22001) at line 7: Data too long for column 'comment' at row 1 Then we shutdown and re-login and we get the following: show tables; +----------------+ | Tables_in_test | +----------------+ | t123 | +----------------+ 1 row in set (0.00 sec) select * from t123; +----+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | id | comment | +----+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 1 | kncfosdncfvlknsadkvnalksdnvlkansdlkvcnalkdsncvlkasnfcklanskldnlaksndklasndlkanslkdnaslkndlkasndklanslkdnkncfosdncfvlknsadkvnalksdnvlkanpllkvcnalkdsncvlkasnfcklanskldnlaksndklasndlkanslkdnaslkndlkasndklanslkdnkncfoplncfvlknsadkvnalkplnvlkanpllkvcnalkdsncq | +----+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ Can't repeat.
[27 May 2024 11:05]
Gabriel Barcelo
To reproduce the bug, it’s necessary to change the column’s length effectively. It’s irrelevant whether data is truncated or not. If the command 'ALTER TABLE test123 MODIFY COLUMN comment VARCHAR(191);’ results in an error rather than a warning, it could indicate a different configuration (e.g., no sql_mode="" or non-default InnoDB settings). However, the bug is still reproducible with a shorter row insertion. More details are provided below.
I was under the impression that row truncation is achieved with sql_mode="" when the row data exceeds the new row length. This setting is present in my.ini, which is a copy of the configuration from the server that crashed. However, as a MySQL novice, I’m not entirely sure about this.
You might want to try inserting shorter rows instead of full 255-character ones, making sql_mode="" optional. Here’s the full SQL script for reproducing the bug:
create database test;
use test;
set global innodb_default_row_format='compact';
CREATE TABLE `test123` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`comment` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
-- note short row, we can truncate WITHOUT --sql_mode=""
insert into test123(comment) values('short row!');
show index from test123;
create index idx123 on test123 (`comment`);
ALTER DATABASE test CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE test123 MODIFY COLUMN comment VARCHAR(191);
ALTER TABLE test123 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- Corruption will start here:
ALTER TABLE test123 MODIFY COLUMN comment VARCHAR(255);
-- Ret OK, BUT WHY THIS IS ALLOWED!
-- Query OK, 0 rows affected (0.00 sec)
-- Records: 0 Duplicates: 0 Warnings: 0
SHUTDOWN;
-- OR RESTART MYSQLD instance. This will silently kill our table!
Following these steps might not result in an error. I'm not sure what else to suggest. I've reproduced this issue in the following environments:
- The original bug occurred in a production environment on Windows Server 2019 with MySQL 8.0.25, using the my.ini configuration from the original post.
- I reproduced the bug on Windows 11 using mysqld.exe with the my.ini configuration from the original post.
- I reproduced the bug on a Podman machine (MacOS) without a rare .ini, only --sql_mode="". I used the 'official' Linux arm64v8/mysql:8.0.37 container (oraclelinux9?). I also tried the short row insertion variant without sql_mode="".
- I reproduced the bug on a Podman machine (MacOS) without a rare .ini, only --sql_mode="". - I used the 'official' Linux arm64v8/mysql:8.4 container (oraclelinux9?). I didn't test the short row insertion variant.
- I believe the same issue would occur with an amd64 container or a 'native' amd64 daemon, although I haven't tested this. If I have time, I might try it on a VM.
Even though containers aren't typically used for testing, the fact that I can reproduce the same behavior in a completely different environments and with different binaries suggests that this bug is present in MySQL builds from 8.0 to 8.4.
[27 May 2024 11:09]
MySQL Verification Team
Hi, Still can't repeat. Even with sql_mode="", which is not supported. We can not verify a report without repeating it. Can't repeat.
[27 May 2024 11:18]
Gabriel Barcelo
data dir with corrupted table test123
Attachment: data_bug115113.tar.xz (application/x-xz, text), 2.37 MiB.
[27 May 2024 11:20]
Gabriel Barcelo
I am sorry to not be able to help more with this, left a corrupted data dir from 8.0.37 `podman` reproduction as proof-of-concept if it's of any use. Best, Gabriel
[27 May 2024 11:26]
MySQL Verification Team
Sorry, Mr. Barcelo, We can verify only those reports that we can reproduce with our own binary on the operating systems that we support. This could be a problem with your hardware , system software , build, use of container or any other of many possible factors .........
[29 May 2024 17:49]
Pranay Motupalli
I am able to reproduce this issue using the below steps on community provided binary:
Environment:
##########
GNU/Linux 4.18.0-193.el8.x86_64
Binary Used: mysql-8.4.0-linux-glibc2.28-x86_64.tar.xz from MySQL community Downloads
mysql [localhost:8400] {msandbox} ((none)) > show global variables like '%version%';
+-----------------------------+------------------------------+
| Variable_name | Value |
+-----------------------------+------------------------------+
| admin_tls_version | TLSv1.2,TLSv1.3 |
| explain_json_format_version | 1 |
| innodb_version | 8.4.0 |
| protocol_version | 10 |
| replica_type_conversions | |
| slave_type_conversions | |
| tls_version | TLSv1.2,TLSv1.3 |
| version | 8.4.0 |
| version_comment | MySQL Community Server - GPL |
| version_compile_machine | x86_64 |
| version_compile_os | Linux |
| version_compile_zlib | 1.2.13 |
+-----------------------------+------------------------------+
12 rows in set (0.00 sec)
Repro Steps:
############
Set sql_mode='';
Drop database testing;
Create database testing;
Use testing;
set global innodb_default_row_format='compact';
CREATE TABLE `test123` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`comment` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
insert into test123(comment) values('kncfosdncfvlknsadkvnalksdnvlkansdlkvcnalkdsncvlkasnfcklanskldnlaksndklasndlkanslkdnaslkndlkasndklanslkdnkncfosdncfvlknsadkvnalksdnvlkanpllkvcnalkdsncvlkasnfcklanskldnlaksndklasndlkanslkdnaslkndlkasndklanslkdnkncfoplncfvlknsadkvnalkplnvlkanpllkvcnalkdsncq');
insert into test123(comment) values('kncfosdncfvlknsadkvnalwrdnvlkansdlkvcnalkdsncvlkasnfcklanskldnlawrndklasndlkanslkdnaslkndlkasndklanslkdnkncfosdncfvlknsadkvnalwrdnvlkansdlkvcnalkdsncvlkasnfcklanskldnlawrndklasndlkanslkdnaslkndlkasndklanslkdnkncfosdncfvlknsadkvnalwrdnvlkansdlkvcnalkdsncq');
show index from test123;
create index idx123 on test123 (`comment`);
ALTER DATABASE testing CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE test123 MODIFY COLUMN comment VARCHAR(191);
ALTER TABLE test123 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE test123 MODIFY COLUMN comment VARCHAR(255);
Select * from test123;
-- Kill and restart ---
kill -15 `pidof mysqld`
--- Reconnect and try accessing the table or drop the table ----
mysql [localhost:8400] {msandbox} ((testing)) > select * from test123;
ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes.
mysql [localhost:8400] {msandbox} ((testing)) > alter table test123 engine=innodb;
ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes.
mysql [localhost:8400] {msandbox} ((testing)) > alter table test123 algorithm=copy;
ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes.
mysql [localhost:8400] {msandbox} ((testing)) > Drop database testing;
ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes.
[30 May 2024 10:05]
MySQL Verification Team
HI, The problem is with sql_mode. When you set to to blank, then you introduce changes that are already obsolete. This is all explained in our Reference Manual. Can't repeat with supported SQL mode.
[30 May 2024 12:47]
Gabriel Barcelo
Sr. Motupalli may you repeat without sql_mode="" and with shorter inserts? Like:
insert into test123(comment) values('my short row');
[30 May 2024 16:21]
Pranay Motupalli
Thanks for your reply.
1. Regarding sql_mode, I understand that data truncation is expected and we expect to see some difference in the data when sql_mode is not set to strict mode. However, it shouldn’t lead to a situation where I cannot access the table for ever.
2. Setting sql_mode to any other value other than Strict mode also reproduces this issue with larger strings.
3. With default sql_mode, I am able to reproduce the issue. Below are the steps with default sql_mode.
--- With Default Sql Mode value ---
mysql [localhost:8400] {msandbox} (testing) > select @@sql_mode;
+-----------------------------------------------------------------------------------------------------------------------+
| @@sql_mode |
+-----------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql [localhost:8400] {msandbox} ((none)) > Create database testing;
Query OK, 1 row affected (0.00 sec)
mysql [localhost:8400] {msandbox} ((none)) >
mysql [localhost:8400] {msandbox} ((none)) > Use testing;
Database changed
mysql [localhost:8400] {msandbox} (testing) > set global innodb_default_row_format='compact';
Query OK, 0 rows affected (0.00 sec)
mysql [localhost:8400] {msandbox} (testing) > CREATE TABLE `test123` (
-> `id` int unsigned NOT NULL AUTO_INCREMENT,
-> `comment` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT '',
-> PRIMARY KEY (`id`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
Query OK, 0 rows affected, 3 warnings (0.02 sec)
mysql [localhost:8400] {msandbox} (testing) > insert into test123(comment) values('abc');
Query OK, 1 row affected (0.00 sec)
mysql [localhost:8400] {msandbox} (testing) > insert into test123(comment) values('def');
Query OK, 1 row affected (0.01 sec)
mysql [localhost:8400] {msandbox} (testing) > create index idx123 on test123 (`comment`);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql [localhost:8400] {msandbox} (testing) > ALTER DATABASE testing CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
Query OK, 1 row affected (0.01 sec)
mysql [localhost:8400] {msandbox} (testing) > ALTER TABLE test123 MODIFY COLUMN comment VARCHAR(191);
Query OK, 2 rows affected (0.04 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql [localhost:8400] {msandbox} (testing) > ALTER TABLE test123 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
Query OK, 2 rows affected (0.04 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql [localhost:8400] {msandbox} (testing) > ALTER TABLE test123 MODIFY COLUMN comment VARCHAR(255);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql [localhost:8400] {msandbox} (testing) > Select * from test123;
+----+---------+
| id | comment |
+----+---------+
| 1 | abc |
| 2 | def |
+----+---------+
2 rows in set (0.00 sec)
mysql [localhost:8400] {msandbox} (testing) > \q
Bye
[ec2-user@ip-172-31-54-9 ~]$ ./send_kill
Attempting normal termination --- kill -15 2135539
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.4.0 MySQL Community Server - GPL
Copyright (c) 2000, 2024, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql [localhost:8400] {msandbox} ((none)) > use testing;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql [localhost:8400] {msandbox} (testing) > Select * from test123;
ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes.
[30 May 2024 17:45]
Pranay Motupalli
BTW, the below metadata query also fails after the restart
mysql [localhost:8400] {msandbox} ((none)) > select * from INFORMATION_SCHEMA.INNODB_TABLES;
ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes.
[31 May 2024 10:31]
MySQL Verification Team
Hi Mr. Motupalli, Would it be hard for your just to send me a test case. Without any output of the results ???? Thanks a lot in advance.
[19 Jun 2024 1:13]
Pranay Motupalli
[ec2-user@ip-172-31-54-9 mysql-test]$ ./mtr ./t/Bug_115113.test --nocheck-testcases
Logging: ./mtr ./t/Bug_115113.test --nocheck-testcases
MySQL Version 8.4.0
Checking supported features
Using 'all' suites
Collecting tests
Checking leftover processes
Removing old var directory
Creating var directory '/home/ec2-user/opt/mysql/8.4.0/mysql-test/var'
Installing system database
Using parallel: 1
==============================================================================
TEST NAME RESULT TIME (ms) COMMENT
------------------------------------------------------------------------------
# Check the initial SQL mode
SELECT @@sql_mode;
@@sql_mode
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
# Create a database and set the default row format
CREATE DATABASE testing;
USE testing;
SET GLOBAL innodb_default_row_format='compact';
# Create a table with a VARCHAR column
CREATE TABLE test123 (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
comment VARCHAR(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT '',
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
# Insert some data into the table
INSERT INTO test123 (comment) VALUES ('abc');
INSERT INTO test123 (comment) VALUES ('def');
# Create an index on the VARCHAR column
CREATE INDEX idx123 ON test123 (comment);
# Change the database character set and collation
ALTER DATABASE testing CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
# Modify the column to handle the new character set
ALTER TABLE test123 MODIFY COLUMN comment VARCHAR(191);
ALTER TABLE test123 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE test123 MODIFY COLUMN comment VARCHAR(255);
# Select data from the table
SELECT * FROM test123;
id comment
1 abc
2 def
# Simulate a server restart
# restart
# Check if the index is still usable after the restart
USE testing;
SELECT * FROM test123;
[ 50%] main.Bug_115113 [ fail ]
Test ended at 2024-06-19 01:12:36
CURRENT_TEST: main.Bug_115113
mysqltest: At line 46: Query 'SELECT * FROM test123' failed.
ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes.
Warnings from just before the error:
Error 1709 Index column size too large. The maximum column size is 767 bytes.
safe_process[2207014]: Child process: 2207015, exit: 1
- the logfile can be found in '/home/ec2-user/opt/mysql/8.4.0/mysql-test/var/log/main.Bug_115113/Bug_115113.log'
[100%] shutdown_report [ pass ]
------------------------------------------------------------------------------
The servers were restarted 0 times
The servers were reinitialized 0 times
Spent 0.000 of 9 seconds executing testcases
Completed: Failed 1/2 tests, 50.00% were successful.
Failing test(s): main.Bug_115113
The log files in var/log may give you some hint of what went wrong.
If you want to report this error, please read first the documentation
at http://dev.mysql.com/doc/mysql/en/mysql-test-suite.html
mysql-test-run: *** ERROR: there were failing test cases
[19 Jun 2024 1:14]
Pranay Motupalli
Test case:
--source include/have_innodb_16k.inc
--disable_warnings
--echo # Check the initial SQL mode
SELECT @@sql_mode;
--echo # Create a database and set the default row format
CREATE DATABASE testing;
USE testing;
SET GLOBAL innodb_default_row_format='compact';
--echo # Create a table with a VARCHAR column
CREATE TABLE test123 (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
comment VARCHAR(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT '',
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
--echo # Insert some data into the table
INSERT INTO test123 (comment) VALUES ('abc');
INSERT INTO test123 (comment) VALUES ('def');
--echo # Create an index on the VARCHAR column
CREATE INDEX idx123 ON test123 (comment);
--echo # Change the database character set and collation
ALTER DATABASE testing CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
--echo # Modify the column to handle the new character set
ALTER TABLE test123 MODIFY COLUMN comment VARCHAR(191);
ALTER TABLE test123 CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE test123 MODIFY COLUMN comment VARCHAR(255);
--echo # Select data from the table
SELECT * FROM test123;
--echo # Simulate a server restart
--source include/restart_mysqld.inc
--echo # Check if the index is still usable after the restart
USE testing;
# --error ER_INDEX_COLUMN_TOO_LONG
SELECT * FROM test123;
--echo # Cleanup
#--error ER_INDEX_COLUMN_TOO_LONG
DROP DATABASE testing;
--enable_warnings
[19 Jun 2024 10:11]
MySQL Verification Team
Hi Mr. Barcelo, Thank you for the test case. We got the following error: ------------------------------ CURRENT_TEST: main.bug_115113 mysqltest: At line 25: Query 'CREATE INDEX idx123 ON test123 (comment)' failed. ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes. safe_process[22598]: Child process: 22599, exit: 1 ----------------------------- However, according to our Reference Manual: https://dev.mysql.com/doc/refman/8.4/en/innodb-row-format.html this is expected behaviour. Not a bug.
[19 Jun 2024 18:17]
Pranay Motupalli
May I know which exact binary and arch you used to test this?
[20 Jun 2024 9:18]
MySQL Verification Team
Hi Mr. Motupali, Of course that you may know. I am using the only official MySQL binaries, available from https://dev.mysql.com. I am using 8.0.37 and 8.4.0 for Oracle Linux 8.9 and latest macOS.
