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.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.056 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.