MySQL Bugs: #34621: ODBC - Update fails, if a "ORDER BY" clause is specified

Bug #34621	ODBC - Update fails, if a "ORDER BY" clause is specified
Submitted:	16 Feb 2008 21:05	Modified:	23 Feb 2008 7:04
Reporter:	Diego Milazzo	Email Updates:
Status:	Verified	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	ODBC 5.1.2 - MySQL 5.0.51a	OS:	Windows (XP with SP2)
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	ODBC, order, order by, UPDATE

Description:
In the code below, when I try to update I have an error:

80004005 (-2147467259)
 - Insufficient key column information for updating (or similar)

It seems to be related to the "ORDER BY" clause, if I remove, it works!

My config:
- MySQL 5.0.51a
- Connector ODBC 5.1.2
- VB6
- Windows XP with SP2

How to repeat:
CREATE SCHEMA IF NOT EXISTS `DBTest` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
USE `DBTest`;

CREATE  TABLE IF NOT EXISTS `DBTest`.`Catg` (
  `ID` INT UNSIGNED NOT NULL ,
  `DESC` VARCHAR(60) NOT NULL ,
  PRIMARY KEY (`ID`) );

CREATE  TABLE IF NOT EXISTS `DBTest`.`Art` (
  `ID` INT UNSIGNED NOT NULL ,
  `ID_CATG` INT UNSIGNED NOT NULL ,
  `DESC` VARCHAR(60) NOT NULL ,
  PRIMARY KEY (`ID`, `ID_CATG`) ,
  INDEX fk_Art_Catg (`ID_CATG` ASC) ,
  CONSTRAINT `fk_Art_Catg`
    FOREIGN KEY (`ID_CATG`)
    REFERENCES `DBTest`.`Catg` (`ID`));

INSERT INTO  `DBTest`.`Catg` VALUES (1, 'DESCCATG1'),(2, 'DESCCATG2');
INSERT INTO  `DBTest`.`Art` VALUES (1, 1, 'DESCART1'),(2, 1, 'DESCART2'),(3, 2, 'DESCART3');

VB Code:
Private Sub cmdTest_Click()
    Dim oConnDB As ADODB.Connection
    Dim oRS As ADODB.Recordset
    Dim sQuery As String

    On Error GoTo ErrHandler
    
    ' Connection
    Set oConnDB = New ADODB.Connection
    oConnDB.ConnectionString = "Driver={MySQL ODBC 5.1 Driver};Server=localhost;Database=DBTest;User=root;Password=notime;Option=35;"
    oConnDB.Properties("PROMPT") = adPromptNever
    oConnDB.Open

    sQuery = "SELECT Art.ID, Art.DESC, Art.ID_CATG, Catg.DESC AS DESC_CATG " & _
             "FROM Art, Catg WHERE Art.ID_CATG=Catg.ID ORDER BY Art.ID"

    'sQuery = "SELECT Art.ID, Art.DESC, Art.ID_CATG, Catg.DESC AS DESC_CATG " & _
    '         "FROM Art, Catg WHERE Art.ID_CATG=Catg.ID"

    Set oRS = New ADODB.Recordset
    oRS.CursorLocation = adUseClient
    oRS.Open sQuery, oConnDB, adOpenStatic, adLockOptimistic

    oRS.MoveFirst
    oRS.Fields("DESC").Value = "newdesc"
    oRS.Update

ExitPoint:
    Set oRS = Nothing
    Set oConnDB = Nothing

    Exit Sub
ErrHandler:
    MsgBox Hex(Err.Number) & " - " & Err.Description
    Resume ExitPoint
End Sub

...

Do a new comment here saying what are you want to correct.

No, nothing.
This is sufficient.

trace file of this test case against sql server

Attachment: SQL.LOG (application/octet-stream, text), 109.95 KiB.

Try with latest components...

a. MySQL Server 5.0.67
b. ODBC 5.1.5
c. VB6 SP6
d. WindowsXP SP3

http://dev.mysql.com/downloads/mysql/5.0.html#win32

http://dev.mysql.com/downloads/connector/odbc/5.1.html#win32

http://www.microsoft.com/downloads/details.aspx?FamilyID=9ef9bf70-dfe1-42a1-a4c8-39718c7e3...

http://www.microsoft.com/downloads/details.aspx?FamilyID=68c48dad-bc34-40be-8d85-6bb4f56f5...

Verified with Connector/ODBC 5.1.8