Bug #53296 LONG BLOB value types are not recognized
Submitted: 29 Apr 2010 19:02 Modified: 16 Nov 2010 2:05
Reporter: Greg Howard Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Data Types Severity:S3 (Non-critical)
Version:5.1.49-bzr OS:Any
Assigned to: Georgi Kodinov CPU Architecture:Any
Triage: Triaged: D2 (Serious)

[29 Apr 2010 19:02] Greg Howard
Description:
When clicking on text fields to view the full string, the window may or may not popup.  No error occurs, but no window pops up.

System:
AMD Athlon Phenom II Quad-Core
8 GB RAM
Win 7 Professional 64-bit

How to repeat:
SET @string1 = "Before you report a bug, ";
SET @string2 = "Before you report a bug, make sure to search for similar bugs using the form at the top of the page or our advanced search page. Also, read the instructions for how to report a bug that someone will want to help fix.

If you aren't sure that what you're about to report is a bug, you should ask for help using one of the means for support listed here.

Failure to follow these instructions may result in your bug simply being marked as \"not a bug\".

If you have a test case (SQL dump, backtrace or similar) as a file, please enter the information about the bug and submit it first - you'll then have a chance to attach files to the bug.

";
SELECT @string1, @string2;

Click either field and choose "Open Value in Viewer".
[3 May 2010 17:43] Johannes Taxacher
verified with example given on all platforms
[4 May 2010 16:06] Alfredo Kojima
This happens only with server 5.1.x, with 5.0 it works as expected.
[4 May 2010 16:30] Alfredo Kojima
in 5.0 the values are returned as VARCHAR and in 5.1 as LONG BLOBs
[4 May 2010 16:36] Alfredo Kojima
Sergei Tkachenko confirms that it's a Connector/C++ bug: sql::ResultSetMetaData::getColumnTypeName returns "UNKNOWN"

I am recategorizing the bug and changing the summary to be clearer for Connector devs.
[28 Jun 2010 12:47] Andrey Hristov
Please provide a short test case which shows the problem when used against 5.0 .

Thank you!
[2 Jul 2010 9:47] Ulf Wendel
Hi,

I am not sure if this is a C/C++ bug. 

Can anybody explain the length reported by the server to me? It says 16777216. I can't find that lenght anywhere at http://dev.mysql.com/doc/refman/5.1/en/string-type-overview.html . If the server wants to tell me that it is is MEDIUMBLOB it shall report 16777215 (NOT 16777216!). 

Ulf

mysql> select version();
Field   1:  `version()` 
Catalog:    `def`       
Database:   ``          
Table:      ``          
Org_table:  ``          
Type:       VAR_STRING  
Collation:  latin1_swedish_ci (8)
Length:     12                   
Max_length: 12                   
Decimals:   31                   
Flags:      NOT_NULL             

+--------------+
| version()    |
+--------------+
| 5.1.45-debug |
+--------------+
1 row in set (0.00 sec)

mysql> SELECT @string1, @string2;
Field   1:  `@string1`           
Catalog:    `def`                
Database:   ``                   
Table:      ``                   
Org_table:  ``                   
Type:       LONG_BLOB            
Collation:  latin1_swedish_ci (8)
Length:     16777216             
Max_length: 25                   
Decimals:   31                   
Flags:                           

Field   2:  `@string2`
Catalog:    `def`     
Database:   ``        
Table:      ``        
Org_table:  ``        
Type:       LONG_BLOB 
Collation:  latin1_swedish_ci (8)
Length:     16777216             
Max_length: 446                  
Decimals:   31                   
Flags:
[2 Jul 2010 11:56] Ulf Wendel
After IRC discussion with Georg, Tonci and others, I dare to call it a server bug. 

The length of 16777216 = 2^32 makes no sense for LONGBLOB columns. The length of LONGBLOB is documented to be <2^32. You can find that in the sources, you can find it at the manual.

Now, you could argue that user variables are something special. So, let's cast the user variable:

mysql> set @string1 = 'abc'; set @string2 = 'ananaaaaaaaaaaaaajjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj';

mysql> SELECT @string1, @string2, CAST(@string1 AS BINARY);
Field   1:  `@string1`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       LONG_BLOB
Collation:  latin1_swedish_ci (8)
Length:     16777216
Max_length: 3
Decimals:   31
Flags:

Field   2:  `@string2`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       LONG_BLOB
Collation:  latin1_swedish_ci (8)
Length:     16777216
Max_length: 245
Decimals:   31
Flags:

Field   3:  `CAST(@string1 AS BINARY)`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       LONG_BLOB
Collation:  binary (63)
Length:     16777216
Max_length: 3
Decimals:   31
Flags:      BINARY

The CAST function is used to convert the user variable into a BINARY. My understanding is that this shall return (depending on length) any of the documented SQL data types, say a MEDIUMBLOB. Again, there is no SQL data type for which the maximum column length is 2^32. 

Ulf
[2 Jul 2010 12:35] Valeriy Kravchuk
Verified just as described in the last comment:

valeriy-kravchuks-macbook-pro:5.1 openxs$ bin/mysql -uroot --column-type test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.1.49-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> set @string1 = 'abc'; set @string2 =
Query OK, 0 rows affected (0.36 sec)

    -> 'ananaaaaaaaaaaaaajjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjjj';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @string1, @string2, CAST(@string1 AS BINARY);
Field   1:  `@string1`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       LONG_BLOB
Collation:  latin1_swedish_ci (8)
Length:     16777216
Max_length: 3
Decimals:   31
Flags:      

Field   2:  `@string2`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       LONG_BLOB
Collation:  latin1_swedish_ci (8)
Length:     16777216
Max_length: 245
Decimals:   31
Flags:      

Field   3:  `CAST(@string1 AS BINARY)`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       LONG_BLOB
Collation:  binary (63)
Length:     16777216
Max_length: 3
Decimals:   31
Flags:      BINARY 
...
[16 Aug 2010 12:44] Georgi Kodinov
I've executed the following piece of .test file through mysqltest: 
SET @string1 = "Before you report a bug, ";
SET @string2 = "Before you report a bug, make sure to search for similar
bugs using the
form at the top of the page or our advanced search page. Also, read the
instructions for
how to report a bug that someone will want to help fix.

If you aren't sure that what you're about to report is a bug, you should ask
for help
using one of the means for support listed here.

Failure to follow these instructions may result in your bug simply being
marked as \"not
a bug\".

If you have a test case (SQL dump, backtrace or similar) as a file, please
enter the
information about the bug and submit it first - you'll then have a chance to
attach files
to the bug.

";

--enable_metadata
SELECT @string1, @string2;
--disable_metadata
 
To get the following metadata:
+Catalog        Database        Table   Table_alias     Column  Column_alias   Type     Length  Max length      Is_null Flags   Decimals        Charsetnr
+def                                    @string1        251     16777216       25       Y       0       31      8
+def                                    @string2        251     16777216       638      Y       0       31      8

Note the 16777216 number I've got.

According to our documentation (http://dev.mysql.com/doc/refman/5.1/en/string-type-overview.html) : 
" MEDIUMBLOB

A BLOB column with a maximum length of 16,777,215 (224 – 1) bytes. Each MEDIUMBLOB value is stored using a three-byte length prefix that indicates the number of bytes in the value.

 MEDIUMTEXT [CHARACTER SET charset_name] [COLLATE collation_name]

A TEXT column with a maximum length of 16,777,215 (224 – 1) characters. The effective maximum length is less if the value contains multi-byte characters. Each MEDIUMTEXT value is stored using a three-byte length prefix that indicates the number of bytes in the value."
[16 Aug 2010 13:43] 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/115802

3483 Georgi Kodinov	2010-08-16
      Bug #53296: LONG BLOB value types are not recognized
      
      Fixed the length of system variables to be 2^24 - 1 
      as it is documented for MEDIUMBLOB instead of 
      2^24.
[16 Aug 2010 14:02] Georgi Kodinov
Related to bug #32124
[24 Sep 2010 14:03] 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/119054

3518 Georgi Kodinov	2010-08-16
      Bug #53296: LONG BLOB value types are not recognized
      
      Fixed the length of system variables to be 2^24 - 1 
      as it is documented for MEDIUMBLOB instead of 
      2^24.
[30 Sep 2010 10:37] 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/119501

3519 Georgi Kodinov	2010-08-16
      Bug #53296: LONG BLOB value types are not recognized
      
      Fixed the length of system variables to be 2^24 - 1 
      as it is documented for MEDIUMBLOB instead of 
      2^24.
[27 Oct 2010 16:59] Paul Dubois
Noted in 5.1.52, 5.5.7 changelogs.

The max_length metadata value of MEDIUMBLOB types was reported as 1
byte greater than the correct value.
[1 Nov 2010 19:01] Bugs System
Pushed into mysql-5.1 5.1.53 (revid:build@mysql.com-20101101184443-o2olipi8vkaxzsqk) (version source revid:build@mysql.com-20101101184443-o2olipi8vkaxzsqk) (merge vers: 5.1.53) (pib:21)
[9 Nov 2010 19:47] Bugs System
Pushed into mysql-5.5 5.5.7-rc (revid:sunanda.menon@sun.com-20101109182959-otkxq8vo2dcd13la) (version source revid:marko.makela@oracle.com-20100824081003-v4ecy0tga99cpxw2) (merge vers: 5.1.50) (pib:21)
[13 Nov 2010 16:14] Bugs System
Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:marko.makela@oracle.com-20100824081003-v4ecy0tga99cpxw2) (merge vers: 5.1.50) (pib:21)
[13 Nov 2010 16:38] Bugs System
Pushed into mysql-next-mr (revid:alexander.nozdrin@oracle.com-20101113160336-atmtmfb3mzm4pz4i) (version source revid:marko.makela@oracle.com-20100824081003-v4ecy0tga99cpxw2) (pib:21)