| Bug #20208 | Blobs greater than 8K are being truncated to 8K | ||
|---|---|---|---|
| Submitted: | 1 Jun 2006 15:36 | Modified: | 11 Jan 2013 12:24 |
| Reporter: | Curtis Beattie | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | Connector / ODBC | Severity: | S2 (Serious) |
| Version: | 3.51.20, 5.1alpha | OS: | Any |
| Assigned to: | Bogdan Degtyariov | CPU Architecture: | Any |
[2 Jun 2006 15:47]
Tonci Grgin
Hi Curtis. Thanks for your problem report. I did start my favourite client, the one I made myself, but can't repeat the error you're getting. Since there's been reports of problems similar to this one I'll check more. Can you post a sample project which demonstrates this behavior?
[2 Jun 2006 16:10]
Curtis Beattie
Unfortunately I don't have any additional code that I will be able to post at this point. The ultimate cause of my problem is that SQLDescribeCol() is reporting that the blob is 8K in size when it is in fact much larger. This can be seen in the ODBC trace log I included. If you have a custom ODBC client, simply call SQLDescribeCol() for column #2 and it will return 8192 instead of something larger (assuming you are calling SQLDescribeCol() on a row that has a 'Results' column larger than 8K). I think this should be easily reproduced without additional source code. I can attach the actual patch I used to fix this problem. Basically I just removed the one line I referenced in utility.c. This should get you directly to the problem code.
[2 Jun 2006 18:25]
Tonci Grgin
Curtis, I'm still unable to reproduce problematic behavior.
Explanation of returned col. type:
sqlext.h #define SQL_LONGVARCHAR (-1)
myodbc1 is my system DSN.
Test output:
---------------
Your MySQL connection id is 8 to server version: 4.1.21-debug-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> DESCRIBE Foo\G
*************************** 1. row ***************************
Field: RecId
Type: int(10) unsigned
Null:
Key: PRI
Default: NULL
Extra: auto_increment
*************************** 2. row ***************************
Field: Results
Type: longtext
Null:
Key:
Default:
Extra:
2 rows in set (0.00 sec)
mysql> select version();
+------------------+
| version() |
+------------------+
| 4.1.21-debug-log |
+------------------+
1 row in set (0.00 sec)
---------------
Test case output:
---------------------------
Connecting to myodbc1
COL -> Results
COL LENGTH -> 23040
COL TYPE -> -1
COL uFlag -> 0
---------------------------
Result: returned lenght and type are expected and correct.
VS2005 code:
rc = SQLExecDirect(hstmt, (SQLCHAR*)"select * from Foo", 18);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
return(PrintError(SQL_HANDLE_STMT, hstmt, rc));
rc = SQLFetch(hstmt);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
return(PrintError(SQL_HANDLE_STMT, hstmt, rc));
SQLINTEGER rs;
SQLSMALLINT cl;
SQLSMALLINT bl;
SQLSMALLINT sLength;
SQLSMALLINT sType;
char pColName[255];
unsigned long lLength;
short sScale;
short uFlag;
rc = SQLDescribeCol(hstmt, 2, (SQLCHAR*)pColName,255,&sLength, &sType, &lLength,&sScale,&uFlag );
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
return(PrintError(SQL_HANDLE_STMT, hstmt, rc));
std::cout << "COL -> " << pColName << std::endl;
std::cout << "COL LENGTH -> " << lLength << std::endl;
std::cout << "COL TYPE -> " << sType << std::endl;
std::cout << "COL uFlag -> " << uFlag << std::endl;
[2 Jun 2006 19:45]
Curtis Beattie
Your test case looks pretty good Tonci and I think you should be able to reproduce the problem. The only think I can think of is that you appear to be using MySQL version 4.1.21-debug-log whereas I was using version 5.0.22 (also tested with 5.0.20). If you look at the code, the length field returned is coming from field->length (field is a MYSQL_FIELD structure if I remember correctly). It appears that in your test case field->length is the proper size whereas in mine it is not (I verified this using gdb). The fact that you are using a 4.x version of MySQL server may explain this as the field->length and field->max_length fields are coming from MySQL server data structures (MYSQL_FIELD) not ODBC data structure (or so I believe). May I suggest you try using one of the later versions of mysql server and retry your test.
[2 Jun 2006 20:46]
Tonci Grgin
Hi Curtis. I tried with latest 5.0.23-debug bk build, results are the same. I am planning to extend this test case a bit more in next few days.
[3 Jun 2006 0:06]
Curtis Beattie
I will try your test code out on my machine ASAP and see if I can still reproduce the problem. I'll offer up one potential explanation. The documentation referenced below says that mysql_fetch_field() will return a blob size of 8K until you call mysql_store_result(). Perhaps in your test case, behind the ODBC facade you are calling mysql_store_result() before your call to SQLDescribeCol(). I believe in our code, we call SQLDescribeCol() very soon after we execute the query in order to allocate the appropriate buffer sizes. http://dev.mysql.com/doc/refman/5.0/en/mysql-fetch-field.html
[3 Jun 2006 14:40]
Tonci Grgin
Hi Curtis. I can provide you with entire case. I think your problem could lie in not using proper ODBC sql type. Try using SQL_LONGVARBINARY which, by definition, does not require length to be passed since length of BLOB is not known, as stated in our manual too.
[5 Jun 2006 15:23]
Curtis Beattie
I've done some more testing and I have some new information.
Firstly, I must appologize as I made some assumptions which made my original test case ineffective.
Use the following table instead of the one I originally suggested:
CREATE TABLE Foo(
RecId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
Results LONGBLOB NOT NULL,
PRIMARY KEY(RecId)
)
TYPE=InnoDB;
Insert into this table using the following SQL:
INSERT INTO Foo VALUES (NULL, COMPRESS('XXX....XX'));
The size of the uncompressed string must be greater than 8K, it doesn't matter if the uncompressed size is smaller.
I think this should allow you to reproduce the problem using the test program you created. If not, I have a test program that I can attach to the issue.
[5 Jun 2006 19:36]
Tonci Grgin
Well Curtis, this is something completely different! Will make new test case.
[7 Jun 2006 17:37]
Curtis Beattie
Have you had any luck with this issue Tonci? Is there any more information I can provide?
[7 Jun 2006 19:29]
Tonci Grgin
Curtis, you'll be notified as soon as we agree upon results of our tests. For now, I'll just say that UNCOMPRESSED_LENGTH(Results) returns correct length / type while UNCOMPRESS(Results) does not.
[8 Jun 2006 15:54]
Bogdan Degtyariov
Test case uses MySQL C API shows that UNCOMPRESS function returns MYSQL_TYPE_VAR_STRING result for both BLOB and VARCHAR
Attachment: mysql-list-fields-test.c (text/x-csrc), 2.46 KiB.
[27 Jun 2006 18:47]
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/8347
[18 Sep 2006 6:50]
Ian Greenhoe
Code patch looks good to me. My only concern: is it possible to add a test?
[24 Nov 2006 21:45]
Bogdan Degtyariov
This will be tested again when mysqlclient library 5.1 is GA release. As MyODBC is being linked against client v5.0 it doesn't make sense to use patched lib yet.
[1 Dec 2006 14:43]
Bogdan Degtyariov
To fix this bug the same approach as in Connector/J was used: when use COMPRESS() the field returned has not table/org_table set. This helps to determine whether it is needed to change the type to BLOB and set the correct length.
[23 Dec 2006 23:09]
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/17367 ChangeSet@1.2333, 2006-12-23 18:08:47-05:00, cmiller@zippy.cornsilk.net +12 -0 Bug#20208: Blobs greater than 8K are being truncated to 8K Backported a change from 5.1 that solves (or rather works around) this "bug". The type is inferred from the max size. For a size of 8192 bytes, we adver- tise that the type is VAR_STRING rather than BLOB. The ODBC connector's behavior is arguably wrong. I don't have the power to change the connector, but I can make 5.0 behave the same as 5.1, which does work.
[8 Feb 2007 21:21]
Bogdan Degtyariov
The fix made recently for MyODBC affects the functionality of crystal reports. So, the bug report will not close until this problem is solved. SQLTables returns wrong types of columns in the result set.
[23 Feb 2007 22:30]
Jim Winstead
patch to fix SQLTables issue
Attachment: bug20208.diff (application/octet-stream, text), 2.63 KiB.
[26 Feb 2007 23:03]
Bogdan Degtyariov
Update of the bugfix for SQLTables issue
Attachment: utility.diff (application/octet-stream, text), 2.64 KiB.
[27 Feb 2007 1:41]
Jim Winstead
looks good to me, but i can't make sense of the comment that starts with 'Metadata should not be ...'. i'm not sure what you mean by metadata.
[6 Mar 2007 22:01]
Jim Winstead
Bogdan has pushed the fix for this into the repository, it will be in the next release of Connector/ODBC.
[9 Mar 2007 11:32]
MC Brown
An entry has been added to the 3.51.14 changelog.
[11 Sep 2007 12:02]
Tonci Grgin
The bug is still present in latest GA release. For details, please see my last private comment.
[11 Jan 2013 12:20]
Hemant Dangi
Issue fixed in mysql server 5.1 release, look at last comment of http://bugs.mysql.com/bug.php?id=26444. For mysql server 5.5.27 UNCOMPRESS function returns type as LONG_BLOB and myodbc returns SQL_LONGVARBINARY. mysql> SELECT RecID, UNCOMPRESSED_LENGTH(Results) AS Len, UNCOMPRESS(Results) AS Res FROM foo; Field 1: `RecID` Table: `foo` Org_table: `foo` Type: LONG Collation: binary (63) Length: 10 Max_length: 1 Decimals: 0 Field 2: `Len` Table: `` Org_table: `` Type: LONGLONG Collation: binary (63) Length: 10 Max_length: 3 Field 3: `Res` Table: `` Org_table: `` Type: LONG_BLOB Collation: binary (63) Length: 16777216 Max_length: 100
Description: I have a database which contains a table that contains a blob column. When I attempt to query data out of this table that is larger than 8K, the resultant data is truncated to 8K. To be more specific, the call to SQLDescribeCol() is indicating that the data in the column is only 8K in size when it is in fact much larger. Here is a snippet of the ODBC trace log: [ODBC][21249][SQLDescribeCol.c][231] Entry: Statement = 0x856b430 Column Number = 2 Column Name = 0xbfff8650 Buffer Length = 30 Name Length = 0xbfff861e Data Type = 0xbfff861c Column Size = 0xbfff8618 Decimal Digits = 0xbfff8616 Nullable = 0xbfff8614 [ODBC][21249][SQLDescribeCol.c][474] Exit:[SQL_SUCCESS] Column Name = [Results] Data Type = 0xbfff861c -> 12 Column Size = 0xbfff8618 -> 8192 Decimal Digits = 0xbfff8616 -> 31 Nullable = 0xbfff8614 -> 0 The problem is that our code is using the size returned by SQLDescribeCol() to allocate a buffer of sufficient size. So, later on, when we call SQLGetData() to retrieve the data from this column the following is being reported: [ODBC][21249][SQLGetData.c][224] Entry: Statement = 0x856b430 Column Number = 2 Target Type = 1 SQL_CHAR Buffer Length = 8193 Target Value = 0x851cfd8 StrLen Or Ind = 0xbfff8734 [ODBC][21249][SQLGetData.c][470] Exit:[SQL_SUCCESS_WITH_INFO] Buffer = [...] Strlen Or Ind = 0xbfff8734 -> 3278936 DIAG [01004] [MySQL][ODBC 3.51 Driver][mysqld-5.0.22-standard]String data, right truncated This bug appears to have been introduced with MyODBC v3.51.12. I have reason to believe this because I found the problem in unireg_to_sql_datatype() in driver/utility.c. I compared version 3.51.11 and 3.51.12 and I simply reverted a change in 3.51.12 and the problem went away. I'll go into more detail in the suggested fix section. How to repeat: Consider the following table. CREATE TABLE Foo( RecId INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, Results LONGTEXT NOT NULL, PRIMARY KEY(RecId) ) TYPE=InnoDB; Insert a record where 'Results' is sufficiently large (over 8K). Ex. INSERT INTO Foo VALUES (NULL, 'XXX....XXX'); Using your favorite ODBC client do: SELECT Results FROM Foo; The 'Results' text will be truncated. If you turn on ODBC trace logging you should see warning message I indicated above. Note: I have tested this on Suggested fix: SQLDescribeCol() is passed a pointer to a integer which indicates the buffer size. This pointer is passed to unireg_to_sql_datatype() via the 'precision' pointer. The first thing that unireg_to_sql_datatype() does is initialize 'precision' to be equal to field->max_length, which in my case is in the 3Meg range (the actual size of the contents of the blob). After unireg_to_sql_datatype() initializes precision it seems to do some special case handling via a switch statement for each type. In v3.51.11, the FIELD_TYPE_VAR_STRING case (the case that is being consider in my example) did not _override_ the precision value but in v3.51.12 it does using the following code: *transfer_length= *precision= *display_size= field->length ? (stmt->dbc->mysql.charset ? field->length/stmt->dbc->mysql.charset->mbmaxlen: field->length): 255; The problem is that field->length has the value 8192 not 3Meg. I'll restate this, field->length has the value 8192 whereas the value field->max_length = 3M (or so). My fix was simply to delete the above code thus reverting back to the way things were done in 3.51.11. Note, I debugged through this using both 3.51.11 and 3.51.12 and the values in the field pointer are identical (ie. field->length and field->max_length are the same in both versions). I have no clue what the above code is attempting to do but it wasn't there prior to 3.51.12 so it seemed reasonable to remove.