Bug #15776 32-bit signed int used for length of blob
Submitted: 15 Dec 2005 16:14 Modified: 7 May 2008 19:36
Reporter: Paul Ryland Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1.17-BK, 4.1.15 OS:Linux (Linux)
Assigned to: Chad MILLER CPU Architecture:Any
Tags: Contribution
Triage: D5 (Feature request)

[15 Dec 2005 16:14] Paul Ryland
Description:
This command successfully works:

CREATE TABLE fishy ( data BLOB(2147483647) );

These commands fail:

mysql> CREATE TABLE fishy ( data BLOB(2147483648) );
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '2147483648) )' at line 1

mysql> CREATE TABLE fishy ( `data` BLOB(4294967295) );
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '4294967295) )' at line 1

I have MySQL running on AMD64 in a 64-bit environment.

How to repeat:
CREATE TABLE fishy ( data BLOB(2147483648) );

Suggested fix:
There is probably a 32-bit signed int somewhere in the SQL parser.
[16 Dec 2005 10:52] Valeriy Kravchuk
Thank you for a problem report. Verified just as described on 4.1.17-BK (ChangeSet@1.2461, 2005-12-15 18:48:08+03:00) on (32-bit) Linux:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.17

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE fishy ( data BLOB(100000) );
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE fishy2 ( data BLOB(1000000000) );
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE fishy3 ( data BLOB(2000000000) );
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE fishy4 ( data BLOB(2147483647));
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE fishy4 ( data BLOB(2147483648) );
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '2147483648) )' at line 1

So, yes, that 2147483648 is a "magic number", and the manual (http://dev.mysql.com/doc/refman/4.1/en/string-type-overview.html) says nothing about it, really:

"LONGBLOB

A BLOB column with a maximum length of 4,294,967,295 or 4GB. Up to MySQL 3.23, the client/server protocol and MyISAM tables had a limit of 16MB per communication packet or table row. From MySQL 4.0, the maximum allowed length of LONGBLOB columns depends on the configured maximum packet size in the client/server protocol and available memory."

Yes, my max_allowed_packet is smaller:

mysql> select @@max_allowed_packet;
+----------------------+
| @@max_allowed_packet |
+----------------------+
|              1048576 |
+----------------------+
1 row in set (0.00 sec)

But I do not understand, how can it influence the statement parsing, not execution of further INSERTs.

So, I is either a bug (limitation) in code or, at least, a documentation request. This limit, if intended even for some platfroms, should be documented.
[30 May 2007 16:42] Martin Friebe
suggest patch / includes test

Attachment: blob_len.patch (text/x-patch), 22.89 KiB.

[30 May 2007 16:55] Martin Friebe
Explanation for the patch:

The initial problem is in sql_yacc.yy: "opt_len" the parser only matche NUM tokens. wich have a signed range only.

Extending this range also requires some string to number conversation, and some fields on the objects to be extended. 

It would have been possible to create a UNUM token limiting at unsigned 32 bit. However limiting the max number in the parser leads to inconsistent behaviour.
Example (with int fields, opt_len also applies to int)

create table t1 (a int(10000));
ERROR 1074 (42000): Column length too big for column 'a' (max = 255); use BLOB or TEXT instead

create table t1 (a int(1000000000000));
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1000000000000))' at line 1

So allowing any number, will now always lead to the "to long" error, which is more meaningful.

The bug also applies to "cast('x' as char(n)))". if n is a bigger number cast returns a text/blob field (existing behaviour). but again at 2147483648 an error would happen.

If you specify a number over 32 bit unsigned range for a blob/text, it will currently display the same error msg as for char(). (This also needed extension in order to deal with the higher max value)

This error msg is missleading as it suggests to use a blob/text instead.

I understand in 5.0 no new errors can be added, as they would allocate error-numbers that are used in higher versions of mysql. And this would lead to a conflict.
In 5.2 a new error message should probably be added?
[30 May 2007 17:11] Martin Friebe
Just to say, the bug also happened in mysql 5.

The patch is for 5.0
[10 Aug 2007 22:15] 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/commits/32411

ChangeSet@1.2500, 2007-08-10 18:14:54-04:00, cmiller@zippy.cornsilk.net +8 -0
  Bug#15776: 32-bit signed int used for length of blob
  
  Contributed patch from Martin Friebe, CLA from 2007-02-24.
  
  The parser lacked support for field sizes after signed long,
  when it should extend to 2**32-1.
  
  Now, we correct that limitation, and also make the error handling
  consistent for casts.
[31 Aug 2007 19:27] 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/commits/33547

ChangeSet@1.2500, 2007-08-31 15:24:43-04:00, cmiller@zippy.cornsilk.net +7 -0
  Bug#15776: 32-bit signed int used for length of blob
  
  Based on contributed patch from Martin Friebe, CLA from 2007-02-24.
  
  The parser lacked support for field sizes after signed long,
  when it should extend to 2**32-1.
  
  Now, we correct that limitation, and also make the error handling
  consistent for casts.
[1 Apr 2008 16:19] 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/commits/44743

ChangeSet@1.2584, 2008-04-01 12:19:20-04:00, cmiller@zippy.cornsilk.net +7 -0
  Bug#15776: 32-bit signed int used for length of blob
  
  Based on contributed patch from Martin Friebe, CLA from 2007-02-24.
  
  The parser lacked support for field sizes after signed long,
  when it should extend to 2**32-1.
  
  Now, we correct that limitation, and also make the error handling
  consistent for casts.
  ---
  Fix minor complaints of Marc Alff, for patch against B-g#15776.
  ---
  Merge zippy.cornsilk.net:/home/cmiller/work/mysql/bug15776/my50-bug15776
  into  zippy.cornsilk.net:/home/cmiller/work/mysql/bug15776/my51-bug15776
  ---
  Merge zippy.cornsilk.net:/home/cmiller/work/mysql/bug15776/my51-bug15776
  into  zippy.cornsilk.net:/home/cmiller/work/mysql/mysql-5.1-build
  ---
  testing
[3 Apr 2008 17:17] Chad MILLER
Queued to 5.0, 5.1, and 6.0 -build trees.
[9 Apr 2008 0:20] 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/commits/45099

ChangeSet@1.2503, 2008-04-08 20:20:03-04:00, cmiller@zippy.cornsilk.net +5 -0
  Follow-up to Bug#15776, test failures on 64-bit linux.
  
  Make maximum blob size to be 2**32-1, regardless of word size.
  
  Fix failure of timestamp with size of 2**31-1.  The method of
  rounding up to the nearest even number would overflow.
[9 Apr 2008 13:15] 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/commits/45130

ChangeSet@1.2503, 2008-04-09 09:14:20-04:00, cmiller@zippy.cornsilk.net +3 -0
  Follow-up to Bug#15776, test failures on 64-bit linux.
  
  Make maximum blob size to be 2**32-1, regardless of word size.
  
  Fix failure of timestamp with size of 2**31-1.  The method of
  rounding up to the nearest even number would overflow.
[9 Apr 2008 15:30] 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/commits/45144

ChangeSet@1.2503, 2008-04-09 11:29:07-04:00, cmiller@zippy.cornsilk.net +4 -0
  Follow-up to Bug#15776, test failures on 64-bit linux.
  
  Make maximum blob size to be 2**32-1, regardless of word size.
  
  Fix failure of timestamp with size of 2**31-1.  The method of
  rounding up to the nearest even number would overflow.
[24 Apr 2008 15:23] Chad MILLER
Queued to 5.0, 5.1, and 6.0 -build trees.  (Though 6.0-build is lagging behind 5.1-build.)
[1 May 2008 10:05] Bugs System
Pushed into 5.1.25-rc
[1 May 2008 10:06] Bugs System
Pushed into 6.0.6-alpha
[1 May 2008 19:13] Bugs System
Pushed into 5.0.62
[7 May 2008 19:36] Paul Dubois
Noted in 5.0.62, 5.1.25, 6.0.6 changelogs.

The parser used signed rather than unsigned values in some cases that
caused legal lengths in column declarations to be rejected.