Bug #77630 Online Alter changing varchar column size error
Submitted: 6 Jul 2015 13:16 Modified: 7 Jul 2015 13:57
Reporter: Shahriyar Rzayev Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: DDL Severity:S3 (Non-critical)
Version:5.7.7-rc OS:CentOS (7)
Assigned to: CPU Architecture:Any

[6 Jul 2015 13:16] Shahriyar Rzayev
Description:
As documentation states in:

https://dev.mysql.com/doc/refman/5.7/en/mysql-nutshell.html

Section --> InnoDB enhancements.  These InnoDB enhancements were added.

"As a result, in-place ALTER TABLE only supports increasing VARCHAR size from 0 to 255 bytes or increasing VARCHAR size from a value equal to or greater than 256 bytes."

We can alter varchar column size from 0 to 255, and from >=256.
And we can not decrease this value with ALGORITHM=INPLACE.

So sample table data:

mysql> select * from t1 limit 10;
+------+------------+
| id   | c1         |
+------+------------+
|    0 | 3CB4E01F4F |
|    1 | F4C27ECC4D |
|    2 | CE3D9676C2 |
|    3 | 6E58C417F0 |
|    4 | E32A2A3463 |
|    5 | 1E8CE4A267 |
|    6 | 7391F147C9 |
|    7 | F84F1A9FFD |
|    8 | 01263F8D8A |
|    9 | DAEE58641B |
+------+------------+
10 rows in set (0.04 sec)

Table structure:

mysql> show create table t1;
+-------+-----------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                          |
+-------+-----------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL,
  `c1` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

First alter:

mysql> ALTER TABLE t1 ALGORITHM=INPLACE, CHANGE COLUMN c1 c1 VARCHAR(85);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

Second:

mysql> ALTER TABLE t1 ALGORITHM=INPLACE, CHANGE COLUMN c1 c1 VARCHAR(150);
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.

Even it is impossible to change value to 86:

mysql> ALTER TABLE t1 ALGORITHM=INPLACE, CHANGE COLUMN c1 c1 VARCHAR(86);
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.

How to repeat:
I have provided sample steps for checking.

Suggested fix:
Maybe much more descriptive internal documentation should be added or some explanation from your side.
[6 Jul 2015 15:05] MySQL Verification Team
also see http://bugs.mysql.com/bug.php?id=69148
[6 Jul 2015 15:07] MySQL Verification Team
Hi!

Thank you for your report.

This does not look like a bug. If you have a VARCHAR column that is 86 characters long and you use UTF8 as a character set, you are already over the limit. Each character using UTF8 character set takes 3 bytes. You will find that info in our manual. So, 86 characters * 3 bytes / character = 258 bytes !!!! Hence, you are already above the limit.

I hope that this is clear.
[7 Jul 2015 4:15] Shahriyar Rzayev
When using ALGORITHM=INPLACE varchar "size" calculation is based on unicode for eg, it will take 3 bytes with UTF8 charset. 

Maybe not a bug but What about adding this into documentation?

Documentation states -> "For VARCHAR values of 0 to 255, one length byte is required to encode the value. For VARCHAR values of 256 bytes or more, two length bytes are required"

https://dev.mysql.com/doc/refman/5.7/en/innodb-create-index-overview.html#innodb-online-dd...

https://dev.mysql.com/doc/refman/5.7/en/innodb-create-index-overview.html
[7 Jul 2015 13:57] MySQL Verification Team
Sorry, but you have misunderstood the point.

MySQL ALWAYS takes 3 bytes for a single UTF8 character, regardless of what command, statement or query is in question.

Simply, in order to store every single UTF8 character, you need 3 bytes.

As simple as that.