Bug #19871 varchar(65535) column cannot CREATE.
Submitted: 17 May 2006 8:23 Modified: 17 May 2006 16:22
Reporter: Kei SAKAI Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.21,5.1.9-beta OS:Windows (WindowsXP)
Assigned to: CPU Architecture:Any

[17 May 2006 8:23] Kei SAKAI
Description:
VARCHAR (65535) column in CREATE TABLE causes error.

mysql> create table a (id int, name varchar(65535));
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

 The size that causes error are from 65529 to 65535.
(size 65528 and 65536 are OK.)

mysql> create table b (id int, name varchar(65536));
Query OK, 0 rows affected, 1 warning (0.25 sec)

 It occurrs in latin1 character set, but not occurred in cp932 code.

How to repeat:
create table a (id int, name varchar(65535));
create table a (id int, name varchar(65529)) default charset=latin1;
[17 May 2006 9:11] Tonci Grgin
Hi Kei. Thanks for your problem report.
MySQL is behaving just as it should: http://ftp.plusline.de/mysql/doc/refman/5.0/en/innodb-restrictions.html
"you cannot define a row containing VARCHAR columns with a combined size larger than 65535"
As for second test
mysql> create table b (id int, name varchar(65536));
Query OK, 0 rows affected, 1 warning (0.03 sec)
you forgot to check the warning:
mysql> show warnings;
+-------+------+-----------------------------------------------+
| Level | Code | Message                                       |
+-------+------+-----------------------------------------------+
| Note  | 1246 | Converting column 'name' from VARCHAR to TEXT |
+-------+------+-----------------------------------------------+
1 row in set (0.00 sec)
mysql> describe b\G
*************************** 1. row ***************************
  Field: id
   Type: int(11)
   Null: YES
    Key:
Default: NULL
  Extra:
*************************** 2. row ***************************
  Field: name
   Type: mediumtext
   Null: YES
    Key:
Default: NULL
  Extra:
2 rows in set (0.03 sec)
[17 May 2006 10:53] Kei SAKAI
Hi. Thank you for your reply, Tonci.

I think it is strange behavior.

VARCHAR(65528) --> varchar OK
VARCHAR(65529) --> ERROR
VARCHAR(65530) --> ERROR
 :
VARCHAR(65535) --> ERROR
VARCHAR(65536) --> OK (convert to mediumtext)

And ...

> "you cannot define a row containing VARCHAR columns with a combined size larger than 65535"

 65529 is not larger then 65535 :-)

I hope varchar(65529) not error but convert to mediautext.
[17 May 2006 11:22] Tonci Grgin
Kei, you didn't took in account max row size as stated in manual:
http://ftp.plusline.de/mysql/doc/refman/5.0/en/char.html
"Values in VARCHAR columns are variable-length strings. The length can be specified as a value from 0 to 255 before MySQL 5.0.3, and 0 to 65,535 in 5.0.3 and later versions. (The maximum effective length of a VARCHAR in MySQL 5.0.3 and later is determined by the maximum row size and the character set used. The maximum length overall is 65,532 bytes.)".
[17 May 2006 16:22] Kei SAKAI
Thank you Tonci.
I know the description in the manual.

I say, 
on MySQL, I can execute "CREATE TABE a (b VARCHAR(XX));" ,that XX is 1 to 65528 and over 65536.
Any integer can set to XX but WHY exclude only 65529 to 65535?
convert to mediumtext (instead of error) is better.

CREATE TABLE a ( id int, name varchar(1));     OK
CREATE TABLE a ( id int, name varchar(2));     OK
 :                                             OK
CREATE TABLE a ( id int, name varchar(65528)); OK
CREATE TABLE a ( id int, name varchar(65529)); error <- Why do not convert to text?
CREATE TABLE a ( id int, name varchar(65530)); error 
  :                                            error 
CREATE TABLE a ( id int, name varchar(65535)); error 
CREATE TABLE a ( id int, name varchar(65536)); OK (AUTO convert to mediumtext)
CREATE TABLE a ( id int, name varchar(65537)); OK (AUTO convert to mediumtext)
 :                                             OK (AUTO convert to mediumtext)
[17 May 2006 16:29] Paul DuBois
MySQL 5.0 and up attempts to avoid silent column definition changes
when possible.
[18 May 2006 5:41] Tonci Grgin
Hi Kei.
"CREATE TABLE a ( id int, name varchar(65529)); error <- Why do not convert to
text?"
Because field "name" is actually not too big for varchar but total ROW_SIZE is too big. It has nothing to do with converting field "name". See quote from manual again.