MySQL Bugs: #8296: VARBINARY: Silent data type change when size too large

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

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           |
+--------------+

Note that this bug is related to Bug#8295 and also to Bug#7417.

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

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.

Fixed in 5.0.6.

Noted in 5.0.6 changelog and "silent column
conversions" section.