MySQL Bugs: #30890: Wrong field type for longtext is returned to Visual Foxpro, data are truncated

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:	Windows (XP SP2)
Assigned to:	Jess Balint	CPU Architecture:	Any

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.

Hi Jaromir,

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

Regards,

Susanne

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.

test case

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

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.

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.

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.

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.

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.

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

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

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?

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

Oops, I mean Bug #13776 in the previous comment.

Jim, thank You very much. It'll help a lot.

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.

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.

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

ODBC trace log for MyODBC 3.51

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

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.

ODBC trace log for MyODBC 5.1

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

MySQL general query log

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

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.

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.

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.

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.

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.

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?

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)

fix + test

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

updated patch, correctly handle 64-bit lengths

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

updated patch, use STMT instead of adding an extra parameter

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

Bug#25024 was marked as a duplicate of this bug

Patch committed as rev1014 and will be released in 5.1.2.

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.