MySQL Bugs: #110968: Table rewrite happens when increasing a varchar length past 63

Bug #110968	Table rewrite happens when increasing a varchar length past 63
Submitted:	9 May 2023 16:58	Modified:	10 May 2023 12:24
Reporter:	James ODonnell	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	8.0.32	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When a varchar(N) (where N is any value 1-63) was altered to a value of 64 or more, it caused the table to be rewritten. This go against what is in the MySQL documentation which states that it is a simple metadata change and should not rewrite the table. You do not need a large table, but the larger the table the easier it is to see the issue.

How to repeat:
mysql> create table varchar_test(blah varchar(10));

mysql> insert into varchar_test(blah) value('a');

mysql> insert into varchar_test select * from varchar_test;
mysql> insert into varchar_test select * from varchar_test;
mysql> insert into varchar_test select * from varchar_test;
mysql> insert into varchar_test select * from varchar_test;
mysql> insert into varchar_test select * from varchar_test;
...... 15 more times

alter table varchar_test modify blah varchar(63);
--notice it is basically instantaneous 
alter table varchar_test modify blah varchar(64);
-- it takes a couple of seconds, at the same time on a different connection, show full processlist
-- you can see the copy to tmp table occurring 

Suggested fix:
Either update the documentation, or setup varchar in a way that changing the length value from < 64 to something >= 64 doesn't do this.

Hi Mr. ODonell,

Thank you very much for your bug report.

However, this is documented.  We suppose you used InnoDB storage engine.

The problem is in the magical number of 255. With a default character set, VARCHAR(64) is larger then 255 bytes. Hence, for 256 until 65535 bytes of length, you require two bytes for the actual length of the string. Hence , that is why a rebuild is necessary. To add one more byte for the actual length of the variable-length strings.

Not a bug.

Hi,

It is actually explained in our Manual:

https://dev.mysql.com/doc/refman/8.0/en/char.html

in the first 4-5 paragraphs.