Bug #41160 myODBC 3.51.18+ and mySQL Server 4.0.26 updatable query issue
Submitted: 1 Dec 2008 21:05 Modified: 8 Dec 2008 14:57
Reporter: Brian Mueller Email Updates:
Status: Won't fix Impact on me:
None 
Category:Connector / ODBC Severity:S2 (Serious)
Version: OS:Windows (XP SP2)
Assigned to: CPU Architecture:Any
Tags: 3.51.18, 4.0.26, MyODBC, MySQL, server

[1 Dec 2008 21:05] Brian Mueller
Description:
Unable to perform updatable query using myODBC driver versions from 3.51.18.  Using ADO 2.7 from within Visual Basic 6 SP6 is using a client side cursor.  This setup works fine from 3.51.17 down.  

Previously we were using ODBC 3.51.12 with mySQL Server 4.0.26 with VB6 and client side queries to do updatable queries using the method and algorithm described here: http://dev.mysql.com/tech-resources/articles/vb-blob-handling.html

This worked until we updated the ODBC driver to 3.51.26  

Confirmed that it works fine with mySQL 5 and the existing VB6 code.
Confirmed that it broke between 3.51.17 and 3.51.18

VB Error message is

Run-time error '-2147217887 (80040e21)':

Multiple-step operation generated errors.  Check each status value.

Line where error occurred is: 

oRS("datastream") = oStream.Read

How to repeat:
Using VB6 create a connection to a database with cursor set to adUseClient
Attempt to add binary data using an insertable query.... as in...

(I have full sample application if you can tell me how to get it to you)

Private Sub cmdSave_Click()
    Dim oRS As ADODB.Recordset
    Set oRS = New ADODB.Recordset
    
    Dim oStream As ADODB.Stream
    Set oStream = New ADODB.Stream
    
    oStream.Type = adTypeBinary
    oRS.Open "SELECT dataname, datastream FROM binary_data WHERE 1=0", conn, adOpenKeyset, adLockOptimistic
    oRS.AddNew
    
    oStream.Open
    oStream.LoadFromFile txtFilename.Text
    
    oRS("dataname") = txtName.Text
    oRS("datastream") = oStream.Read
    
    oRS.Update
    
    oStream.Close
    oRS.Close
    Set oStream = Nothing
    Set oRS = Nothing
End Sub
[1 Dec 2008 21:07] Brian Mueller
I should specify that the data in the updatable field is a blob as defined by the following table schema

CREATE TABLE binary_data (
  dataname varchar(50) default NULL,
  datastream mediumblob
) TYPE=InnoDB;
[2 Dec 2008 14:09] Tonci Grgin
Hello Brian and thanks for another report.

This is mission impossible... I had hardest time obtaining VS2003 and absolutely no way of getting copy of VB which is EOL-ed what, 6 years ago?
So, can you please try repeating error in something newer, like VS2005 for example, and inform me of result?
[2 Dec 2008 15:26] Tonci Grgin
Brian, what appears to me is that you have exploited bug in c/ODBC 3.51.17- to work around problem in ODBC specs. I think Microsoft does not have type as large as MEDIUMBLOB thus neither there is one in ODBC specs. So when we fixed Bug#27862 and provided client with correct field lengths, your code broke...
Changelog:
  * Lengths returned by SQLColumns(), SQLDescribeCol(), and SQLColAttribute()
    were often incorrect. These lengths should now conform to the ODBC
    specification. FLAG_FIELD_LENGTH no longer has any effect. The default
    behavior was incorrect. (Bug #27862)

Together with suggestions above, please try using BLOB and see if it breaks too. Also, check for max_allowed_packet in your server. Make sure your biggest blob fits in it.
[2 Dec 2008 15:36] Brian Mueller
Error occurs when changing column type to BLOB.

It should be noted that the error does not occur with the same code when connecting to a mySQL 5 database and using the mediumblob type with connector version 3.51.18 and above.  It appears to be an issue specific to ODBC driver 3.51.18+ and MySQL server 4.
[2 Dec 2008 15:39] Tonci Grgin
Brian, thanks for new info provided. I will continue investigating tomorrow.
[2 Dec 2008 15:42] Tonci Grgin
Just a remark, continued from Bug#41075.
[4 Dec 2008 16:12] Brian Mueller
Any traction on this issue?  Has it been confirmed as an issue with client side cursors and blob updates?
[6 Dec 2008 22:26] Tonci Grgin
Brian, I can confirm that what you reported *is* exactly what happens with my working code when using MySQL server 4.0 but I am not convinced this is the end of story so I'll need some more time to figure this out.
[8 Dec 2008 9:53] Tonci Grgin
Brian.

Although my colleagues managed to work around this the result was garbage so it's not viable.

The root of the problem is in that from 3.51.14 - 17 we were using 4.1 server libraries and from 3.51.18 we use 5.0 server libraries. It is impossible now to link c/ODBC against 4.0 and build to check what's really happening. Also, 4.0 is EOL-ed so I should not be taking this report at all. Please upgrade, I see no problems when using 5.0 server, for example. Further more, MySQL server 4.0 is *not* listed as supported in ODBC section of manual.
[8 Dec 2008 13:45] Brian Mueller
We are working towards upgrading our servers.  Of course with any high scale production environment it takes time and it must by systematic.  Currently we have one of our servers running mySQL 5 and we are working through other issues related to the differences between the SQL in 4 and 5.  As some examples, multi-table deletes are different, casting issues in queries, bit is now a true bit and not a integer, issues with handling of fields in unions.  So, we are working on updating our application to fix all of the issues.  It's simply not possible to just update all of our servers to 5 in one night.  In the process of upgrading our servers to 5 we also updated the connectors on the client systems, this was because the old connector has a problem when doing "SHOW TABLE CREATE" type statements to a 5 server.  I don't see how the bug is in mySQL 4 so I don't believe your argument that it's because of an EOL program applies.  The connector changed and it broke.  It still works with mySQL 5, simple as that.  We really need a fix for this as we cannot work with these issues for the next 6 months while we convert the rest of our servers to 5 and update our code.
[8 Dec 2008 14:54] Tonci Grgin
Brian, I'm simply stating facts while you resume your guessing. I can sympathize with your situation but there's nothing I can do... Further more, the importance this bug bears for you is way beyond it's real importance.

Let's try to clear this up:
While 4.0 and 4.1 were GA c/ODBC (any) was linked against those server cli-libraries. Now it's linked against 5.0 and soon it will be 5.1. We do try to maintain backward compatibility but that's just not an option always. Thus, when 4.0 is EOL-ed we dropped much workarounds needed for c/ODBC to work with this server version and noted such change in manual. It is simply impossible to keep all the obsolete code all the time as none could resume developing c/ODBC then.
Show tables, and other ad-hoc queries, are actually server bug (Bug#10491 I think) which is now fixed both in server and in c/ODBC. I think it's only normal to expect that obsolete code will be removed from c/ODBC as soon as critical mass of users has upgraded their MySQL server installation.

"We are working towards upgrading our servers.  Of course with any high scale production
environment it takes time and it must by systematic.  Currently we have one of our servers
running mySQL 5 and we are working through other issues related to the differences between
the SQL in 4 and 5.  As some examples, multi-table deletes are different, casting issues
in queries, bit is now a true bit and not a integer, issues with handling of fields in
unions.  So, we are working on updating our application to fix all of the issues.  It's
simply not possible to just update all of our servers to 5 in one night.  In the process
of upgrading our servers to 5 we also updated the connectors on the client systems, this
was because the old connector has a problem when doing "SHOW TABLE CREATE" type statements
to a 5 server."

I agree with all of the above and could add a few more, like LEFT JOIN and such.

"I don't see how the bug is in mySQL 4 so I don't believe your argument that it's because of an EOL program applies.  The connector changed and it broke. It still works with mySQL 5, simple as that.  We really need a fix for this as we cannot work with these issues for the next 6 months while we convert the rest of our servers to 5 and update our code."

I *never* said there was a bug, not in server nor in connector! There were numerous changes in server client libraries (protocol...) which we link connector against. That's the problem. In this case I can only suggest that you take support contract and try obtaining special c/ODBC build that will help you overcome problems while you undergo server upgrade.
[8 Dec 2008 14:57] Brian Mueller
If having support changes anything you can look us up under London Computer Systems.  I believe we have purchased around 50 licenses from you over the last 4 years.
[8 Dec 2008 15:02] Tonci Grgin
Brian, it can, in such cases. If you have support contract entitling you on custom connectors builds, support just might be able to squeeze something that can help you out of development. Checking.
[8 Dec 2008 17:17] Tonci Grgin
Brian, Pro OEM license covers completely different grounds than support contract does so I'm unable to help more. I advise you to contact our sales representative Mr. Mike Radencich <mike.radencich@sun.com> who covers Ohio for OEM and see if you can arrange something with him.
[8 Dec 2008 22:56] Jess Balint
Please try a snapshot after the fix for bug#36071 is pushed.