[MDEV-13530] VARBINARY doesn't convert to to BLOB for sizes 65533, 65534 and 65535 Created: 2017-08-15  Updated: 2017-10-13  Resolved: 2017-10-13

Status: Closed
Project: MariaDB Server
Component/s: Data Definition - Alter Table, Data types
Affects Version/s: 5.5, 10.0, 10.1, 10.2
Fix Version/s: 5.5.58, 10.0.33, 10.1.29, 10.2.10, 10.3.3

Type: Bug Priority: Major
Reporter: Thomas Guyot-Sionnest Assignee: Alexander Barkov
Resolution: Fixed Votes: 0
Labels: upstream

Issue Links:
Relates
relates to MDEV-9319 ALTER from a bigger to a smaller blob... Closed
relates to MDEV-12809 Bad column type created for TEXT(1431... Closed
Sprint: 5.5.58

 Description   

As pointed out here: https://github.com/bitcoin-abe/bitcoin-abe/issues/227#issuecomment-322396729

Maria auto-convert large VARBINARY to BLOB when they exceed maximum VARBINARY length, but for length 65533, 65534 and 65535 Maria refuses to create the column and fails to convert to BLOB

It does convert to blob when length is >65535. For the broken ones it seems it doesn't take into account 3 bytes overhead (BTW I would expect 2... why the 3rd?)

Example:

MariaDB [abe_test]> CREATE TABLE t1 (c1 VARBINARY(65532)); DROP TABLE IF EXISTS t1;
 
Query OK, 0 rows affected (1.95 sec)
 
 
 
Query OK, 0 rows affected (0.29 sec)
 
 
 
MariaDB [abe_test]> CREATE TABLE t1 (c1 VARBINARY(65533)); DROP TABLE IF EXISTS t1;
 
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
 
Query OK, 0 rows affected, 1 warning (0.00 sec)
 
 
 
MariaDB [abe_test]> CREATE TABLE t1 (c1 VARBINARY(65534)); DROP TABLE IF EXISTS t1;
 
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
 
Query OK, 0 rows affected, 1 warning (0.00 sec)
 
 
 
MariaDB [abe_test]> CREATE TABLE t1 (c1 VARBINARY(65535)); DROP TABLE IF EXISTS t1;
 
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
 
Query OK, 0 rows affected, 1 warning (0.00 sec)
 
 
 
MariaDB [abe_test]> CREATE TABLE t1 (c1 VARBINARY(65536)); DROP TABLE IF EXISTS t1;
 
Query OK, 0 rows affected, 1 warning (0.94 sec)
 
 
 
Query OK, 0 rows affected (0.14 sec)
 
 
 
MariaDB [abe_test]>



 Comments   
Comment by Alice Sherepa [ 2017-08-15 ]

Thanks for the report! The same with VARCHAR.
On MariaDB 10.2.8:

MariaDB [test]> CREATE TABLE tz (c1 VARBINARY(65532));
 
Query OK, 0 rows affected (0.14 sec)
 
 
 
MariaDB [test]> CREATE TABLE tz1 (c1 VARBINARY(65533));
 
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
 
 
 
MariaDB [test]> CREATE TABLE tk1 (c1 VARCHAR(65533));
 
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
 
 
 
MariaDB [test]> CREATE TABLE tz1 (c1 VARBINARY(65535));
 
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
 
 
 
MariaDB [test]> CREATE TABLE tz1 (c1 VARBINARY(65536));
 
ERROR 1074 (42000): Column length too big for column 'c1' (max = 65535); use BLOB or TEXT instead

Comment by Sergei Golubchik [ 2017-08-15 ]

alice, is it an upstream bug?

Comment by Alice Sherepa [ 2017-08-15 ]

upstream bug, reproducible on MySQL 5.7.18 and 5.6.23

Comment by Thomas Guyot-Sionnest [ 2017-08-17 ]

Alice, have you tried in upstream with VARBINARY >= 65536? I have a report that on some MySQL version VARBINARY(100000) does not auto-convert to MEDIUMBLOB. It's while testing this that I noticed the bug with sizes 65533-65535.

I'm trying to find out what version that was...

Comment by Alice Sherepa [ 2017-08-17 ]

yes, on 5.7.18 there was an error, but only because there was another sql_mode then on 5.6.23, where was only warning about converting to BLOB

ERROR 1074 (42000): Column length too big for column 'c1' (max = 65535); use BLOB or TEXT instead

Comment by Sergei Golubchik [ 2017-10-12 ]

ok to push

Generated at Thu Feb 08 08:06:18 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.