Bug #30890 Wrong field type for longtext is returned to Visual Foxpro, data are truncated
Submitted: 7 Sep 2007 10:56 Modified: 14 Feb 2008 16:33
Reporter: Jaromir Karmazin Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / ODBC Severity:S3 (Non-critical)
Version:5.1 OS:Microsoft Windows (XP SP2)
Assigned to: Jess Balint CPU Architecture:Any

[7 Sep 2007 10:56] Jaromir Karmazin
Description:
When retrieving data from a LONGTEXT field, receive CHARACTER (255) field type in Visual Foxpro 7.0 and naturally data are truncated to 255 characters. This happens only with MySQL 5.0.x (5.0.45 on WinXP/Debian Linux).
With the same data on MySQL 4.0.17-nt and the same version of MyODBC there is no problem - receive MEMO field in VFP. 

How to repeat:
- create a table with LONGTEXT field
- connect to database with the table
- insert a record with the LONGTEXT field value longer than 255 characters
- select data from the table and you will receive only first 255 characters of the field and the field type in the result cursor will be CHARACTER (255)

* here some VFP code *
m.lnConnect=SQLCONNECT(....)
m.lcData=REPLICATE("a",255)+REPLICATE("b",255)
=SQLEXEC(m.lnConnect,"INSERT INTO mytable (myfield) VALUES (?m.lcData)"
=SQLEXEC(m.lnConnect,"SELECT * FROM mytable","mycursor")
SELECT mycursor
BROWSE 
USE
=SQLDISCONNECT(m.lnConnect)

Suggested fix:
Have no clue. There might be something wrong between MySQL 5.0.x and MyODBC 3.51.x. With the MySQL 4.0.x and older it goes well.
[7 Sep 2007 11:13] Susanne Ebrecht
Hi Jaromir,

thanks for writing a bug report.
Did you try it with MySQL version 5.1 too?

Regards,

Susanne
[7 Sep 2007 11:41] Jaromir Karmazin
Hi Susanne,
I've just downloaded MySQL 5.1.21-beta-community and tested it. The problem is as same as with MySQL MySQL 5.0.x.
[7 Sep 2007 12:53] Sveta Smirnova
test case

Attachment: bug30890.c (text/plain), 1.66 KiB.

[7 Sep 2007 12:54] Sveta Smirnova
Thank you for the report.

I can not repeat described behaviour using attached C API test case. Please provide ODBC test case you can repeat error with.
[7 Sep 2007 14:40] Tonci Grgin
Hi Jaromir. 
FoxPro column mapping is nothing we can fix. There is a lot of nonsense there but the problem you reported here is duplicate of Bug#9111. Please see the solution there. There are a lot of similar reports on FoxPro, you might benefit from searching bugsdb.
Also, there will be new release of MyODBC in next few days so you might test with it.
[7 Sep 2007 17:38] Jaromir Karmazin
Hi Tonci,
thank you all for your replies. 
I understand you cannot fix Visual Foxpro column mappings. On the other hand why it's ok with older MySQL versions (with the same version of Visual Foxpro and the same version of MyODBC driver)? There must have been something you changed.
The Bug#9111 you've mentioned is about something different and it has been solved by using "Don't optimize column width" MyODBC option several years ago. 

I'm not alone with this problem. Some people from the Czech Visual Foxpro community confirmed the same trouble.

To Sveta Smirnova:
It's obvious you cannot reproduce described bug without Visual Foxpro. I have done several tests before I posted the bug here. Longtext data are stored well even I can retrieve them using for example MySQL Query Browser. The trouble is Visual Foxpro column mapping. The wrong mapping causes data truncation. 255 characters is the maximum for Visual Foxpro CHARACTER field type. It should be mapped to Visual Foxpro MEMO field type as it used to be prior MySQL 5.0.x.
[8 Sep 2007 20:47] Tonci Grgin
Hi Jaromir.

For my part of analysis I still think I'm right but as I don't have much experience with FoxPro I'll assign Jess.

For Sveta's part, there was a private request from our colleague which made Sveta do the client check, nothing else.

So, let's see what Jess will say.
[10 Sep 2007 6:47] Jaromir Karmazin
I have to add something. I did the same tests against MySQL 4.1.x and had the same problem. So a change was between MySQL 4.0.x and MySQL 4.1.x.
I have never used MySQL 4.1.x. and that's why I supposed the problem (bug)started with MySQL 5.x.
During tests I noticed that MyODBC driver returns for LONGTEXT field following values:
MySQL 4.0.17: data_type=-1,type_name="text", column_size=16777215, buffer_length=16777215
MySQL 5.0.45: data_type="text", column_size=-1, buffer_length=-1

That's the difference which in my opinion causes the problem.
[10 Sep 2007 6:51] Jaromir Karmazin
Sorry, in my previous comment is a mistake.
The correct line is:
MySQL 5.0.45: data_type=-1, type_name="text", column_size=-1, buffer_length=-1
[10 Sep 2007 19:22] Jim Winstead
Could this be related to Bug #13776? (In a nutshell, 5.0 started reporting the size of a LONGBLOB/LONGTEXT column to be 4GB, and some ODBC applications apparently only expect a value up to 2GB.)
[11 Sep 2007 6:34] Jaromir Karmazin
Hi Jim,
You are right - this is related to Bug #13776 (with different symptoms). Most probably the column size of LONGTEXT fields returned from MyODBC is too high to maintain in Visual Foxpro and it causes wrong field type mapping. In Bug #13776 there is a workaround for ADO connection only. Could You possibly add another flag or change somehow FLAG_COLUMN_SIZE_S32 to work with ODBC connection?
[11 Sep 2007 16:12] Jim Winstead
Closing this as a duplicate of Bug #13766. When the fix for that bug is pushed, there will be a new DSN option for capping the size of fields ("Limit column size to signed 32-bit range").
[11 Sep 2007 16:13] Jim Winstead
Oops, I mean Bug #13776 in the previous comment.
[11 Sep 2007 16:19] Jaromir Karmazin
Jim, thank You very much. It'll help a lot.
[18 Dec 2007 12:10] Jaromir Karmazin
I have noticed a new checkbox "Limit reported column size to signed 32-bit range" starting with version 3.51.22. Unfortunately it doesn't work to solve my problem. Longtext values are still truncated. 
Don't know why but the problem disappears using MyODBC 5.01.01 which I've tested being a bit hopeless.
[18 Dec 2007 12:52] Tonci Grgin
Jaromir, from what I see the fix is committed in 5.1 tree only even though it's in 3.51 changelog... Checkbox you're seeing is just new Windows GUI. I'll have to consult on this with Jess and Jim later.
[19 Dec 2007 7:31] Tonci Grgin
Jaromir, I have consulted my colleagues and found out that patch you are mentioning ("Limit reported column size to signed 32-bit range") is indeed pushed  to 3.51 and *not* to 5.1. So it appears this bug is not a duplicate of Bug#13776 and MyODBC 5.1 probably works correctly because of descriptors. In any case this needs more investigation and I'm reopening report.

Now, please attach to this report DM traces (Control Panel/Administrative Tools/Data Sources (ODBC), "Tracing" tab, start tracing now) for *both* MyODBC 3.51 and 5.1 so we can check for differences. Maybe a general query log from server would help too (start MySQL server with --log option).
[19 Dec 2007 9:22] Jaromir Karmazin
ODBC trace log for MyODBC 3.51

Attachment: myodbc_3_51.log (application/octet-stream, text), 56.47 KiB.

[19 Dec 2007 9:22] Jaromir Karmazin
ODBC trace log for MyODBC 3.51 with "limit column size.." flag

Attachment: myodbc_3_51_with32bitflag.log (application/octet-stream, text), 56.10 KiB.

[19 Dec 2007 9:23] Jaromir Karmazin
ODBC trace log for MyODBC 5.1

Attachment: myodbc_5_1.log (application/octet-stream, text), 57.24 KiB.

[19 Dec 2007 9:24] Jaromir Karmazin
MySQL general query log

Attachment: MySQL.LOG (application/octet-stream, text), 1011 bytes.

[19 Dec 2007 9:29] Jaromir Karmazin
Hi Tonci.
Traces and log attached.
In the trace from MyODBC 3.51 there is a line with "...String data, right truncated (501)". It obviously causes my problem.
[19 Dec 2007 16:47] Jess Balint
Jaromir, That error is not what causes your problem. It's an extremely common pattern for ODBC applications to retrieve the length of a string before the actual data, resulting in that message.
[19 Dec 2007 18:02] Jaromir Karmazin
Hi Jess,
the very same ODBC application doesn't truncate string data in one of the following situations:
1) I use MyODBC 5.1
2) I use MyODBC 3.51 and data are on MySQL prior version 4.1

I suppose this is not caused by application.
[21 Dec 2007 6:58] Tonci Grgin
Jaromir, Jess was just just giving you explanation for "It obviously causes my problem.". In any case I am very confident that if anyone can find out what's wrong here it's Jess.
[21 Dec 2007 8:08] Jaromir Karmazin
Hi Tonci,
I didn't mean to blame anyone for being wrong. I just was not sure whether I had explained my problem enough or not. Sorry to Jess if he understood it in that way. I really very appreciate your work.
[3 Jan 2008 8:00] Jess Balint
Jaromir,
I have Visual Fox Pro 9, but I'm not familiar with exactly how to reproduce this bug. Can you provide the ddl for the test table that you are using? Also, can you describe in more detail how to reproduce this? Are you using remote views?
[3 Jan 2008 9:54] Jaromir Karmazin
Hi Jess,
try this, for example:

CREATE TABLE `testbug`.`mytable` (`myfield` LONGTEXT) ENGINE = MyISAM;

then modify and run VFP code I've posted in "How to repeat" in my first comment.
In this case I cannot use remote view. (In a remote view it's possible to remap field to another type and avoid such problems)
[4 Jan 2008 0:20] Jess Balint
fix + test

Attachment: bug30890.diff (application/octet-stream, text), 3.01 KiB.

[4 Jan 2008 1:08] Jess Balint
updated patch, correctly handle 64-bit lengths

Attachment: bug30890.diff (application/octet-stream, text), 8.53 KiB.

[4 Jan 2008 19:06] Jess Balint
updated patch, use STMT instead of adding an extra parameter

Attachment: bug30890.diff (application/octet-stream, text), 4.93 KiB.

[9 Jan 2008 2:33] Jess Balint
Bug#25024 was marked as a duplicate of this bug
[4 Feb 2008 19:33] Jess Balint
Patch committed as rev1014 and will be released in 5.1.2.
[14 Feb 2008 16:33] MC Brown
A note has been added to the 5.1.2 changelog: 

When accessing column data, FLAG_COLUMN_SIZE_S32 did not limi the octet length or display size reported for fields, causing problems with Microsoft Visual FoxPro.