Bug #69148 clarifications on bytes vs chars for extending varchars via online alter table

• Submitted: 5 May 2013 13:22
• Modified: 6 Jul 2015 15:24
• Reporter: Shane Bester (Platinum Quality Contributor)
• Status: Not a Bug
• Category: MySQL Server: Documentation
• Severity: S3 (Non-critical)
• Version: 5.7.2
• OS: Any
• Assigned to: Paul DuBois
• CPU Architecture: Any

[5 May 2013 13:22] Shane Bester

Description:
The manual section at http://dev.mysql.com/doc/refman/5.7/en/innodb-create-index-overview.html talks about extending varchar fields, but doesn't make it clear that characters and bytes are not the same.  Furthermore, the example given doesn't explicitly state the character set of the column/table/database.

The maximum chars that can be extended to such that the "length byte" remains 1, for 1,2,3,4-byte charsets are 255, 127, 85 and 63 respectively.

For example, this would fail, because of utf32 charset used:

mysql> alter table t4 algorithm=inplace, change a a varchar(64);
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.

How to repeat:
drop table if exists t1,t2,t3,t4;
create table t1(a varchar(1))engine=innodb charset latin1; #1 byte per char
create table t2(a varchar(1))engine=innodb charset big5;   #2 byte per char
create table t3(a varchar(1))engine=innodb charset utf8;   #3 byte per char
create table t4(a varchar(1))engine=innodb charset utf32;  #4 byte per char

#these are the upper count of chars supported by online DDL for above tables:
alter table t1 algorithm=inplace, change a a varchar(255);
alter table t2 algorithm=inplace, change a a varchar(127);
alter table t3 algorithm=inplace, change a a varchar(85);
alter table t4 algorithm=inplace, change a a varchar(63);

Suggested fix:
review and clarify the section;
"Extending VARCHAR size using an in-place ALTER TABLE statement" 

http://dev.mysql.com/doc/refman/5.7/en/innodb-create-index-overview.html

[6 Jul 2015 15:07] MySQL Verification Team

also http://bugs.mysql.com/bug.php?id=77630

[6 Jul 2015 15:24] Paul DuBois

For information about the distinction between bytes and characters, see:
http://dev.mysql.com/doc/refman/5.7/en/string-type-overview.html

And also the subsections of:
http://dev.mysql.com/doc/refman/5.7/en/string-types.html

To find the maximum number of bytes used by characters of a given character set, see the Maxlen column of SHOW CHARACTER SET output:
http://dev.mysql.com/doc/refman/5.7/en/show-character-set.html