MySQL Bugs: #37422: CREATE TABLE fails for VARCHAR(21845) UTF8

Bug #37422	CREATE TABLE fails for VARCHAR(21845) UTF8
Submitted:	16 Jun 2008 7:35	Modified:	18 Apr 2011 9:54
Reporter:	Jan Kneschke	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	5.0, 5.1	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
CREATE TABLE is with one VARCHAR(21845) UTF8 column only fails with:

  ERROR 1118 (42000): Row size too large. The maximum row size for the used 
  table type, not counting BLOBs, is 65535. You have to change some columns 
  to TEXT or BLOBs

21845 * 3 is 65535 and matches the max-row-size.

A VARCHAR(21844) is created as expected, a VARCHAR(21846) is turned into a TEXT (as expected).

How to repeat:
> CREATE TABLE `varchar64000_utf8` ( 
  `email` varchar(64000) CHARACTER SET utf8 DEFAULT NULL);
Query OK, 0 rows affected, 1 warning (0.08 sec)

> SHOW WARNINGS;
| Note  | 1246 | Converting column 'email' from VARCHAR to TEXT |

> CREATE TABLE `varchar21845_utf8` ( 
  `email` varchar(21845) CHARACTER SET utf8 DEFAULT NULL);
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs

Suggested fix:
allow 21845 _or_ turned it into a TEXT field.

the row size, including length specifiers and such, not raw data. 

21845*3 is 65535, add two more bytes for string length encoding

ergh, disregard my comment, missed the 'or', need morning coffee

Verified as described by using bzr tree for 5.0 and 5.1

5.0.66-log and 5.1.26-rc

CREATE TABLE `varchar21845_utf8` (`email` varchar(21845) CHARACTER SET utf8 DEFAULT NULL)engine=innodb;

ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs

Consider here, that the text says max size is 65535 which means: byte_length <= 65535

Testing with 6.0 bzr tree (6.0.6-alpha):

CREATE TABLE `varchar21845_utf8` (`email` varchar(21845) CHARACTER SET utf8 DEFAULT NULL);
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+------------------------------------------------+
| Level | Code | Message                                        |
+-------+------+------------------------------------------------+
| Note  | 1246 | Converting column 'email' from VARCHAR to TEXT | 
+-------+------+------------------------------------------------+
1 row in set (0.00 sec)

MySQL 6.0 will convert it. But here it isn't byte_length/number_of_character <= 65535/3 it is byte_length/number_of_character < 65536/4.

Means in 6.0 varchar(16383) will stay varchar and varchar(16384) will get text.

This of course is very confusing too.

See http://dev.mysql.com/doc/refman/5.1/en/column-count-limit.html for factors that get counted in the 65,535 bytes.

You need to account for the length bytes *and* whether the column allows NULL.

Nevertheless, it would be friendlier to convert the 21845-length column to TEXT than to produce an error. Right now, 21845 is an ugly edge case.

See also Bug#37041, it's likely to suffer from the same cause.

Hello!

This bug report describes exactly the same issue as bug #37041
"'SILENT COLUMN CHANGE' FAILS IN SOME LENGTH".
Therefore I am closing this bug as a duplicate of the latter.