Bug #37041 'Silent column change' fails in some length.
Submitted: 28 May 2008 14:47 Modified: 30 Jun 2011 16:44
Reporter: Meiji KIMURA Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DDL Severity:S3 (Non-critical)
Version:5.0.51,5.1.51,5.5.7 OS:Any
Assigned to: CPU Architecture:Any

[28 May 2008 14:47] Meiji KIMURA
Description:
In MySQL, some data type of DDL are changed into column silently.

http://dev.mysql.com/doc/refman/4.1/en/silent-column-changes.html
http://dev.mysql.com/doc/refman/5.0/en/silent-column-changes.html

When I define column as varchar, data type sometimes will be changed.
(It depends on length of varchar) But when I defined varchar column in specific length(from 65533 to 65535), it faild.

How to repeat:
All you have to do is defining tables using these procedure.
There are 4-test cases to failed.

drop table if exists test65532;
create table test65532 ( a varchar(65532)) CHARSET=latin1;

--NG from 65533 to 65535
drop table if exists test65533;
create table test65533 ( a varchar(65533)) CHARSET=latin1;

drop table if exists test65534;
create table test65534 ( a varchar(65534)) CHARSET=latin1;

drop table if exists test65535;
create table test65535 ( a varchar(65535)) CHARSET=latin1;

drop table if exists test65536;
create table test65536 ( a varchar(65536)) CHARSET=latin1;

drop table if exists test65537;
create table test65537 ( a varchar(65537)) CHARSET=latin1;

drop table if exists test65538;
create table test65538 ( a varchar(65538)) CHARSET=latin1;

drop table if exists test21844;
create table test24844 ( a varchar(21844)) CHARSET=utf8;

--NG in this case, same as create table test65535 ( a varchar(65535)) CHARSET=latin1;
drop table if exists test21845;
create table test24845 ( a varchar(21845)) CHARSET=utf8;

drop table if exists test21846;
create table test24846 ( a varchar(21846)) CHARSET=utf8;

Suggested fix:
If length is 65533-byte or above, change data type into midium text.
[28 May 2008 14:59] Meiji KIMURA
mysql> --NG from 65533 to 65535
mysql> drop table if exists test65533;
Query OK, 0 rows affected, 1 warning (0.06 sec)

mysql> create table test65533 ( a varchar(65533)) CHARSET=latin1;
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
mysql>
mysql> drop table if exists test65534;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table test65534 ( a varchar(65534)) CHARSET=latin1;
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
mysql>
mysql> drop table if exists test65535;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table test65535 ( a varchar(65535)) CHARSET=latin1;
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
mysql> --NG in this case, same as create table test65535 ( a varchar(65535)) CHARSET=latin1;
mysql> drop table if exists test21845;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table test24845 ( a varchar(21845)) CHARSET=utf8;
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
[29 May 2008 10:42] Akiko Yonemura
It seems to be related to a length prefix of variable-length string which is internally used by MySQL server.

MySQL server can convert only when 65535 or above is specified to a column length. However if you specify 65534, the actual length becomes over 65535 because the actual length is the sum of the string length and a length prefix (two bytes here).

MySQL should convert it considering the internal used bytes.
[18 Nov 2010 1:18] Meiji KIMURA
http://dev.mysql.com/doc/refman/5.1/en/silent-column-changes.html

I did the same test for MySQL 5.1.51, the results are same.
[18 Nov 2010 1:57] Meiji KIMURA
http://dev.mysql.com/doc/refman/5.5/en/silent-column-changes.html

I did the same test, but result the same. (I modified some typo and comment).

drop table if exists test65532;
create table test65532 ( a varchar(65532)) CHARSET=latin1;

-- NG from 65533 to 65535
drop table if exists test65533;
create table test65533 ( a varchar(65533)) CHARSET=latin1;

drop table if exists test65534;
create table test65534 ( a varchar(65534)) CHARSET=latin1;

drop table if exists test65535;
create table test65535 ( a varchar(65535)) CHARSET=latin1;

drop table if exists test65536;
create table test65536 ( a varchar(65536)) CHARSET=latin1;

drop table if exists test65537;
create table test65537 ( a varchar(65537)) CHARSET=latin1;

drop table if exists test65538;
create table test65538 ( a varchar(65538)) CHARSET=latin1;

drop table if exists test21844;
create table test21844 ( a varchar(21844)) CHARSET=utf8;

-- NG in this case, same as create table test65535 ( a varchar(65535)) CHARSET=latin1;
drop table if exists test21845;
create table test21845 ( a varchar(21845)) CHARSET=utf8;

drop table if exists test21846;
create table test21846 ( a varchar(21846)) CHARSET=utf8;

But new character set 'utf8mb4' works well.

-- Add test cases for new character set utf8mb4
drop table if exists test16382;
create table test16382 ( a varchar(16382)) CHARSET=utf8mb4;

-- OK in this case, same as create table test65535 ( a varchar(65535)) CHARSET=latin1;
drop table if exists test16383;
create table test16383 ( a varchar(16383)) CHARSET=utf8mb4;

drop table if exists test16384;
create table test16384 ( a varchar(16384)) CHARSET=utf8mb4;
[30 Jun 2011 16:44] Paul DuBois
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

The observed failures are not so much "silent conversion" failures as a conflict between what is legal as a column specification (e.g., VARCHAR(65535)) and the internal row-size limit of 65,535 bytes per row. For example, a VARCHAR(65535) column also requires two bytes in the row to record the actual column length, so it exceeds the permitted row size. A MyISAM table would also require space to record that the column can be NULL, if it's nullable.

A VARCHAR(65533) NOT NULL column takes 65,533 bytes + two bytes to record the length, so it fits within the row-size limit (assuming no other columns in the tablel).

This is explained at: http://dev.mysql.com/doc/refman/5.5/en/column-count-limit.html

I've added cross references to that section to the "silent changes" and "storage requirements" sections.