Can't Alter Table To Varchar(65535)

[JimZ]

When I attempt to alter a table and change a column to varchar(65535) SQLyog sets it to data type 'mediumtext'. Is this a limitation, bug, or intentional? My understanding is that with MySQL prior to 5.03 varchar was limited to 255 characters, so attempting to set a longer varchar resulted in the selection of one of mediumtext, text, or longtext. But in 5.03 and later it should be possible to create varchar columns of up to 65535 characters.

[peterlaursen]

not quite.

http://dev.mysql.com/doc/refman/5.0/en/sto…quirements.html says

“Prior to MySQL 5.0.3: L + 1 bytes, where L <= M and 0 <= M <= 255. MySQL 5.0.3 and later: L + 1 bytes, where L <= M and 0 <= M <= 255 or L + 2 bytes, where L <= M and 256 <= M <= 65535 (see note below)." note 1) the limitation is in bytes not in characters. So it depends on the character set …

2) “<= 65535" refers to the storage. Some of those bytes are needed for internal adressing inside the tablespace.

Here I can create a varchar(65500) with a latin 1 table. Won't that do for you?

[JimZ]

peterlaursen wrote on Feb 4 2007, 10:17 PM:

not quite.

http://dev.mysql.com/doc/refman/5.0/en/sto…quirements.html says

“Prior to MySQL 5.0.3: L + 1 bytes, where L <= M and 0 <= M <= 255. MySQL 5.0.3 and later: L + 1 bytes, where L <= M and 0 <= M <= 255 or L + 2 bytes, where L <= M and 256 <= M <= 65535 (see note below)." note 1) the limitation is in bytes not in characters. So it depends on the character set …

2) “<= 65535" refers to the storage. Some of those bytes are needed for internal adressing inside the tablespace.

Here I can create a varchar(65500) with a latin 1 table. Won't that do for you?

Ok, that makes sense. I thought it was characters. The table type is utf8, so I suppose they figure up to three bytes per characater. The max lengtht that I can declare is 21843 characters.

One odd bit of behavior, however is that within a certain range I receiver error no. 1118 about the row size being too large. If I try a larger value for the varchar size, SQLyog doesn't complain, but the row is automatically set to mediumtext.

[peterlaursen]

Quote:

SQLyog doesn't complain

No if the server does not, SQLyog does not either.  

The server 'silently' does what it does.  

It does not even produce a warning (try “show warnings;” yourself).  

There is no way for SQLyog to tell that the server changed the SQL 'silently' when executing it.

[Author]

Posts