Bug #8296 VARBINARY: Silent data type change when size too large
Submitted: 3 Feb 2005 17:14 Modified: 12 May 2005 20:30
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:14] Trudy Pelzer
Description:
When a VARBINARY declaration becomes greater than
the maximum allotted size (65535 bytes), MySQL
changes the data type to BLOB (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 VARBINARY's maximum size 
has been reached.

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

mysql> create table t2 (col1 varbinary(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 varbinary(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 varbinary(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 VARBINARY is limited to 65535 bytes
less the row/column headers, but that's a separate issue.)

mysql> create table t5 (col1 varbinary(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 BLOB |
+-------+------+-----------------------------------------------+
1 row in set (0.00 sec)
-- This result should never occur.

mysql> show tables;
+--------------+
| Tables_in_tp |
+--------------+
| t1           |
| t5           |
+--------------+
[3 Feb 2005 17:15] Trudy Pelzer
Note that this bug is related to Bug#8295 and also to Bug#7417.
[10 Mar 2005 15:05] 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/22899
[7 May 2005 14:51] Trudy Pelzer
Per Monty's comment, the job is to:

1) If sql_mode='traditional', there is no column data type change.
That is, the following returns an error:
CREATE TABLE t1 (col1 VARBINARY(65537));

2) If sql_mode='', there is a column data type change, with a warning generated:
CREATE TABLE t1 (col1 VARBINARY(65537));
results in a col1 BLOB field and the current warning message.
[9 May 2005 16:07] Ramil Kalimullin
Fixed in 5.0.6.
[12 May 2005 20:30] Paul Dubois
Noted in 5.0.6 changelog and "silent column
conversions" section.