MySQL Bugs: #19299: Multiple-Step operation error using Decimal fields

Bug #19299	Multiple-Step operation error using Decimal fields
Submitted:	24 Apr 2006 14:36	Modified:	17 Jul 2006 12:49
Reporter:	Emiliano Sosa	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	My ODBC 3.51	OS:	Windows (Windows 2000 advanced server)
Assigned to:	Bogdan Degtyariov	CPU Architecture:	Any

Description:
  Just doing a "SELECT *" to a table with a "Decimal" typed field set to a size of 10,4 with blank values on registers, generates a "Multiple-step operation. Check each status" error.  I worked around it changing the field to float.  It started to happen when i upgraded to the latest MySQL server version as of April, Wednesday 19th 2006. I'm using Delphi 7 (patched), ADO(latest) and myODBC 3.51.

How to repeat:
Just do "select * from mytable"  when mytable has a Decimal field set to 10,4 with blank values. Its so easy to reproduce that maybe i'm missing something (?)

Hi Emiliano. Thanks for your problem report.
In order to help you I will need some more info:
  MySQL server version
  Storage engine used
  MyODBC connector version (3.51.?)
  Is your server located on localhost?
  DDL statement(s) used to produce this problem
  Datadump from tables used to produce this problem
  Are you connecting through BDE?
  ADO version
  Sample Delphi (D6 and up) project demonstrating this problem
Also, we are currently exploring ADO missbehavior mentioned in:
ms-help://MS.VSCC.v80/MS.MSDN.v80/MS.WIN32COM.v10.en/ado270/htm/mdcondatasection.htm
*Managing Pending Changes*
An update *always* contains the *entire* original row data followed by the changed row data. The changed row may contain all of the columns or only those columns that have actually changed. 

It seems, as of recent, ADO is not using "SQLPrimaryKeys" or "SQLStatistics" functions resulting in problems with updating tables with DECIMAL, BLOB... fields.

I seem to have a similar case using VB6.

"Microsoft Cursor Engine", -2147217887, "Multiple-step operation generated errors. Check each status value."

The query is:

SELECT BatchID, cAllShipmentsPaid, cCarrierSCAC, cCurrencyType, cFormType,  
  cInvNumber, cMultipleShipments, cSupportDocsPresent, DocumentID,  
  dtInvDate, nAddressID, nApprovalStatusID, nCarrierAddressID, nInvAmount,  
  nShipperID, nStatusID, RowID
 FROM lpsfrtinv
 WHERE RowID = 1716;

If I remove the column nInvAmount from the return list, it works fine. The only thing different about this column that I can see is that it has type DECIMAL(10,4).

The error occurs on the open statement:

    Set OpenForwReadRst = New ADODB.Recordset
    OpenForwReadRst.CursorLocation = adUseClient
    OpenForwReadRst.Open _
        Source:=strSQL, _
        ActiveConnection:=cnn, _
        CursorType:=adOpenForwardOnly, _
        LockType:=adLockReadOnly

Connection string is:

DRIVER={MySQL ODBC 3.51 Driver};SERVER=apicvs;PORT=3307; 
DATABASE=lps;UID=xxxx;PWD=xxxx;OPTION=16427

Environment:

Server: Solaris 9
MySQL 5.0.21 
InnoDB
Client: Windows2000
MyODBC 3.51.10
ADO 2.8

Table definition:

CREATE TABLE `lpsfrtinv` (
  `RowID` int(10) unsigned NOT NULL auto_increment,
  `nVersionSequence` int(10) unsigned default '1',
  `cInvNumber` varchar(25) default NULL,
  `dtInvDate` date default NULL,
  `nInvAmount` decimal(10,4) default NULL,
  `cCarrierSCAC` varchar(4) default NULL,
  `nShipperID` int(10) unsigned default NULL,
  `cMultipleShipments` char(1) default NULL,
  `cFormType` varchar(4) default NULL,
  `nSourceID` int(10) unsigned default NULL,
  `cSupportDocsPresent` char(1) default NULL,
  `nAddressID` int(10) unsigned default NULL,
  `cAllShipmentsPaid` char(1) default NULL,
  `cInternational` char(1) default NULL,
  `cCurrencyType` char(3) default NULL,
  `BatchID` datetime default NULL,
  `cBatchName` varchar(50) default NULL,
  `nApprovalStatusID` int(10) unsigned default NULL,
  `nStatusID` int(10) unsigned default NULL,
  `nCarrierAddressID` int(10) unsigned default NULL,
  `cAPIEDITransactionID` varchar(15) default NULL,
  `cInvoiceSequenceNumber` varchar(50) default NULL,
  `nCarrierAccountID` int(10) unsigned default NULL,
  `DocumentID` varchar(20) default NULL,
  PRIMARY KEY  (`RowID`),
  KEY `FK_lpsfrtinv_SouID` (`nSourceID`),
  KEY `FK_lpsfrtinv_StatusID` (`nStatusID`),
  KEY `cInvNumber` (`cInvNumber`),
  KEY `nInvAmount` (`nInvAmount`),
  KEY `cCarrierSCAC` (`cCarrierSCAC`),
  KEY `nShipperID` (`nShipperID`),
  KEY `cFormType` (`cFormType`),
  KEY `cCurrencyType` (`cCurrencyType`),
  KEY `BatchID` (`BatchID`),
  KEY `dtInvDate` (`dtInvDate`),
  KEY `lpsfrtinvcBatchName` (`cBatchName`),
  KEY `FK_lpsfrtinv_AddID` (`nAddressID`),
  KEY `FK_lpsfrtinv_ASID` (`nApprovalStatusID`),
  KEY `FK_lpsfrtinv_CAddrID` (`nCarrierAddressID`),
  KEY `nShipperIDRowID` (`nShipperID`,`RowID`),
  KEY `cInvNumberAmtSCACStatus` (`cInvNumber`, `nInvAmount`, 
         `cCarrierSCAC`, `nStatusID`),
  KEY `nSourceIDBatchName` (`nSourceID`,`cBatchName`),
  KEY `nShipperIDStatusID` (`nShipperID`,`nStatusID`),
  KEY `RowIDStatusID` (`RowID`,`nStatusID`),
  KEY `FK_Inv_CAID` (`nCarrierAccountID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Sample data:

INSERT INTO lpsfrtinv(RowID, nVersionSequence, cInvNumber, dtInvDate, 
  nInvAmount, cCarrierSCAC, nShipperID,cMultipleShipments, cFormType, 
  nSourceID, cSupportDocsPresent, nAddressID, cAllShipmentsPaid,
  cInternational, cCurrencyType, BatchID, cBatchName, nApprovalStatusID, 
  nStatusID, nCarrierAddressID,cAPIEDITransactionID, cInvoiceSequenceNumber, 
  nCarrierAccountID, DocumentID)
 VALUES(1716, 1, 'Bug-2146-3', '2006-05-19', '103.5400', 'MGUL', 33, 'N', 'INV', 
  1, 'Y', 5169, '', 'N', 'USD','2005-05-19 11:10:12', 'LPSFRT_20060519115519.1', 
  NULL, 6, 17, NULL, NULL, NULL, 'lpsfrtinva0180540616');

Hello all.
Lisbeth, can you attach small VB or VS project demonstrating this behavior on data you provided? Can you reproduce this problem with latest MyODBC driver (3.51.12)?

The problem persists with MyODBC 3.51.12.
I do not experience the problem when the server version is 4.1.18.

I will get a project for you by tomorrow.

In case it makes any difference, the table was created as rev 4.1 and the server was recently upgraded to 5.0.  I understand there are supposed to be some changes in how DECIMALs are stored.

http://dev.mysql.com/doc/refman/5.0/en/precision-math-decimal-changes.html

Lisbeth, did you follow proper upgrade procedure?

It was out DBA who did the upgrade, so I can't answer that question.  His email is Jeff.Hill@apioutsourcing.com

I have a test project.  How do I send it to you?  I'm not allowed to add files to this bug.

Test case provided by Lisbeth Kellogg

Attachment: testproject.zip (application/x-zip-compressed, text), 2.09 KiB.

Comment on test case:

Of course, you will need to change the connect string to fit your installation.  The "Command1" button runs the bad query.  The "Command2" button runs the good query.  "Clear" and "Quit" do the obvious.

Installing MyODBC 3.51.12 seems to fix the problem on some computers but not others.  I'm beginning to think it's a difference between Win2000 and WinXP.

Hi all. Sorry I wasn't able to repeat the problem.
I simplified test case to make it work in VS2005. 
mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.0.23-debug |
+--------------+
1 row in set (0.00 sec)
on Suse 10
MyODBC 3.51.12 GA
WinXP SP2 fully updated

    Sub Main()

        Dim strConnectString As String
        strConnectString = "DRIVER={MySQL ODBC 3.51 Driver};" & _
                           "SERVER=munja;PORT=3307;DATABASE=test;UID=root;PWD=;" & _
                           "OPTION=16683;Pooling=true;Min Pool Size=5;Max Pool Size=60;" & _
                           "Connect Timeout=120"

        Dim Q2 As String  
        Q2 = "SELECT BatchID, cAllShipmentsPaid, cCarrierSCAC, cCurrencyType, cFormType," & _
                 "  cInvNumber, cMultipleShipments, cSupportDocsPresent, DocumentID," & _
                 "  dtInvDate, nAddressID, nApprovalStatusID, nCarrierAddressID, nInvAmount," & _
                 "  nShipperID , nStatusID, RowID" & _
                 " From lpsfrtinv" & _
                 " WHERE RowID = 1716;"

        Dim cnTest As ADODB.Connection
        Dim OpenForwReadRst As ADODB.Recordset

        cnTest = New ADODB.Connection

        cnTest.Open(strConnectString)

        OpenForwReadRst = New ADODB.Recordset
        OpenForwReadRst.Open(Q2, cnTest, CursorTypeEnum.adOpenKeyset, LockTypeEnum.adLockOptimistic, ADODB.CommandTypeEnum.adCmdText)

        MsgBox(OpenForwReadRst("nInvAmount").Value)

        OpenForwReadRst = Nothing
        cnTest = Nothing
    End Sub

Output with my lib versions ... As you can see, it exited without error:
'18883.vshost.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_32\mscorlib\2.0.0.0__b77a5c561934e089\mscorlib.dll', Skipped loading symbols. 

Module is optimized and the debugger option 'Just My Code' is enabled.
'18883.vshost.exe' (Managed): Loaded 

'C:\WINDOWS\assembly\GAC_MSIL\Microsoft.VisualStudio.HostingProcess.Utilities\8.0.0.0__b03f5f7f11d50a3a\Microsoft.VisualStudio.HostingProces

s.Utilities.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'18883.vshost.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\System.Windows.Forms\2.0.0.0__b77a5c561934e089\System.Windows.Forms.dll', 

Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'18883.vshost.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\System\2.0.0.0__b77a5c561934e089\System.dll', Skipped loading symbols. 

Module is optimized and the debugger option 'Just My Code' is enabled.
'18883.vshost.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\System.Drawing\2.0.0.0__b03f5f7f11d50a3a\System.Drawing.dll', Skipped 

loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'18883.vshost.exe' (Managed): Loaded 

'C:\WINDOWS\assembly\GAC_MSIL\Microsoft.VisualStudio.HostingProcess.Utilities.Sync\8.0.0.0__b03f5f7f11d50a3a\Microsoft.VisualStudio.HostingP

rocess.Utilities.Sync.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'18883.vshost.exe' (Managed): Loaded 'C:\TEMP\odbc18883\bin\18883.vshost.exe', Skipped loading symbols. Module is optimized and the debugger 

option 'Just My Code' is enabled.
'18883.vshost.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_32\System.Data\2.0.0.0__b77a5c561934e089\System.Data.dll', Skipped loading 

symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'18883.vshost.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\System.Xml\2.0.0.0__b77a5c561934e089\System.Xml.dll', Skipped loading 

symbols. Module is optimized and the debugger option 'Just My Code' is enabled.
'18883.vshost.exe' (Managed): Loaded 'C:\WINDOWS\assembly\GAC\ADODB\7.0.3300.0__b03f5f7f11d50a3a\ADODB.dll', Skipped loading symbols. Module 

is optimized and the debugger option 'Just My Code' is enabled.
'18883.vshost.exe' (Managed): Loaded 

'C:\WINDOWS\assembly\GAC_MSIL\Microsoft.VisualBasic\8.0.0.0__b03f5f7f11d50a3a\Microsoft.VisualBasic.dll', Skipped loading symbols. Module is 

optimized and the debugger option 'Just My Code' is enabled.
The thread 0x958 has exited with code 0 (0x0).
'18883.vshost.exe' (Managed): Loaded 'C:\TEMP\odbc18883\bin\18883.exe', Symbols loaded.
The thread '<No Name>' (0xab8) has exited with code 0 (0x0).
The thread '<No Name>' (0x814) has exited with code 0 (0x0).
The program '[2416] 18883.vshost.exe: Managed' has exited with code 0 (0x0).

Do you have anything to add to this test case?

Test case result

Attachment: Result.jpg (image/jpeg, text), 3.76 KiB.

Test case libraries

Attachment: libs.jpg (image/jpeg, text), 26.64 KiB.

I note you ran the test under WinXP SP2.  I'm using Win2000 SP4 with all updates.

Lisbeth, I can not have all platforms that exist at my disposal... I will try to run test on my home w2k machine in about an hour. If system calls are different on XP and W2k there should be article about it on MS site being their responsability to inform others of things like that.

Hi, I'm unable to set up the same environment on my W2k machine making testing impossible. Can you please try compiling my test code on one of your's W2k machines and see what happens?

Hi, since your compilation attempt failed I have one more idea for you to try. Single out one of the offending W2k machines and roll-back SP4. Inform me of results.

We talked through the alternatives here and decided to upgrade all of the machines to Windows2003 Server.

Sorry for not replying. Did your upgrade helped?

These are production machines.  It will several weeks before we can get through the OS upgrade process with proper testing.  We have been holding the production upgrade to MySQL 5 pending testing.  I will keep you posted on our progress.

RE Your Comment Dated [9 Jun 8:18]:

We have located a Win2000 machine that does not have the problem with 3.51.12, but it has SP4 installed, so that can't be the problem.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Hello Lisbeth. Are those machines identical (cloned) or you were setting up one by one? What is the ratio of working w2ksp4 machines to not-working w2ksp4 machines (ie. exhibiting the problem)?

The machines are set up one-by-one.  I don't have a large enough sample to give you a ratio at this point.

Lisbeth, I understand. Thanks for all the help you provided. I would really like you to post your findings when the sample is large enough.

Posting in behalf of Lisbeth:

Can you help me understand the enclosed screenshot?  (You may wish to
add it to the bug as a file.)
These are the property pages from MyODBC3.DLL on two different Win2000
SP4 computers.  The one on the left does not work.  The one on the right
does work.  Both of them have version number 3.51.12.0, but the sizes
and creation dates are much different.  Do you have any idea what this
means?

Thanks so much for all the help you have been giving me!
---
Lisbeth Kellogg

Screenshot of problem provided by Lisbeth

Attachment: screenshot.jpg (image/jpeg, text), 154.44 KiB.

Lisbeth, I don't know what's up with your non-functioning machine but sizes and description on my machine is like in your second case:
  Size:  1.48 MB (1,552,384 bytes)
  Comments: provides core driver functionality
The size of myodbc3.ddl with debug support is 1.99 MB (2,088,960 bytes). 
I would suggest replacing myodbc files on non-functioning machine.
Thanks very much on info you're providing.

I believe I now understand the problem.

We have got two different installers here.  
One is named "MyODBC-3.51.12-win32.exe" and is dated 6/23/2005.
The other is named "mysql-connector-odbc-3.51.12-win32.msi" and is 6/7/2006.

These two installers result in the two different DLLs noted above.  To add to confusion, uninstalling the older software and installing the newer does not seem to replace the DLL.  I had to go to the System32 directory and manually delete the DLL to get the installation to work properly.

Now that the correct DLL is installed, the problem has gone away.

It seems that for people who are still experiencing problems, it is necessary to check the date of the DLL and not just its version number.

This issue may be related to bugs 10103, 10293, 11035, 11795, 12515, 12599, 13691, and 16257.