[MDEV-13530] VARBINARY doesn't convert to to BLOB for sizes 65533, 65534 and 65535 - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL)
Fix Version/s: 5.5.58, 10.0.33, 10.1.29, 10.2.10, 10.3.3
Component/s: Data Definition - Alter Table, Data types
Labels:
- upstream

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

Attachments

Issue Links

relates to

MDEV-9319 ALTER from a bigger to a smaller blob type truncates too much data

Closed

MDEV-12809 Bad column type created for TEXT(1431655798) CHARACTER SET utf8

Closed

Activity

Ascending order - Click to sort in descending order

Alice Sherepa added a comment - 2017-08-15 11:54 - edited

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

Alice Sherepa added a comment - 2017-08-15 11:54 - edited 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

Sergei Golubchik added a comment - 2017-08-15 19:05

alice, is it an upstream bug?

Sergei Golubchik added a comment - 2017-08-15 19:05 alice , is it an upstream bug?

Alice Sherepa added a comment - 2017-08-15 21:24

upstream bug, reproducible on MySQL 5.7.18 and 5.6.23

Alice Sherepa added a comment - 2017-08-15 21:24 upstream bug, reproducible on MySQL 5.7.18 and 5.6.23

Thomas Guyot-Sionnest added a comment - 2017-08-17 17:25 - edited

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

Thomas Guyot-Sionnest added a comment - 2017-08-17 17:25 - edited 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...

Alice Sherepa added a comment - 2017-08-17 19:34

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

Alice Sherepa added a comment - 2017-08-17 19:34 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

Sergei Golubchik added a comment - 2017-10-12 14:04

ok to push

Sergei Golubchik added a comment - 2017-10-12 14:04 ok to push

People

Assignee:: Alexander Barkov

Reporter:: Thomas Guyot-Sionnest

Votes:: 0 Vote for this issue

Watchers:: 4 Start watching this issue

Dates

Created:: 2017-08-15 10:32

Updated:: 2017-10-13 03:07

Resolved:: 2017-10-13 03:07

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server