Bug #8295 VARCHAR: Silent data type change when size too large
Submitted: 3 Feb 2005 17:05 Modified: 12 May 2005 20:31
Reporter: Trudy Pelzer Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.3-alpha-debug OS:Linux (SuSE 9.1)
Assigned to: Ramil Kalimullin CPU Architecture:Any

[3 Feb 2005 17:05] Trudy Pelzer
Description:
When a VARCHAR declaration becomes greater than
the maximum allotted size (65535 bytes), MySQL
changes the data type to TEXT (or one of its variants).
This should not happen: all silent column data type
changes should not longer occur. Instead, the server
should reject the CREATE/ALTER statement, with a
message that indicates VARCHAR's maximum size 
has been reached.

How to repeat:
mysql> create table t1 (col1 varchar(65532));
Query OK, 0 rows affected (0.01 sec)
-- This is the expected result

mysql> create table t2 (col1 varchar(65533));
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> create table t3 (col1 varchar(65534));
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> create table t4 (col1 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
-- For tables t2, t3, and t4, this is the expected result, although
the error message may lead to confusion. (I'd like to come up
with a message that indicates VARCHAR is limited to 65535 bytes
less the row/column headers, but that's a separate issue.)

mysql> create table t5 (col1 varchar(65536));
Query OK, 0 rows affected, 1 warning (0.01 sec)
-- This is the incorrect result. As soon as the maximum size
of 65535 is exceeded, no data type change should be
made; the server should return SQLSTATE 42000, just as for
tables t2, t3, and t4 above.

mysql> show warnings;
+-------+------+-----------------------------------------------+
| Level | Code | Message                                       |
+-------+------+-----------------------------------------------+
| Note  | 1246 | Converting column 'col1' from VARCHAR to TEXT |
+-------+------+-----------------------------------------------+
1 row in set (0.03 sec)
-- This result should never occur.

mysql> show tables;
+--------------+
| Tables_in_tp |
+--------------+
| t1           |
| t5           |
+--------------+
[3 Feb 2005 17:11] Miguel Solorzano
Thank you for the bug report.
[3 Feb 2005 17:16] Trudy Pelzer
Note that this bug is related to Bug#8296 and also to Bug#7417.
[9 May 2005 16:07] Ramil Kalimullin
Fixed in 5.0.6.
[12 May 2005 10:55] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/24811
[12 May 2005 20:31] Paul Dubois
Noted in 5.0.6 changelog and "silent column
conversions" section.