MySQL Bugs: #19871: varchar(65535) column cannot CREATE.

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

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;

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)

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.

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.)".

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)

MySQL 5.0 and up attempts to avoid silent column definition changes
when possible.

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.