MySQL Bugs: #20194: ADODB generates wrong update if no primary key listed in appropriate select

Bug #20194	ADODB generates wrong update if no primary key listed in appropriate select
Submitted:	1 Jun 2006 11:34	Modified:	4 Feb 2008 19:06
Reporter:	Bogdan Degtyariov	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	3.51.12	OS:	Windows (Win32)
Assigned to:	Erica Moss	CPU Architecture:	Any

Description:
ADODB recordset update() function updates many records in the table instead of the current record. It generates an UPDATE query that does not contain PK in WHERE clause which can lead wrong record updates. The same script works well for MS SQL and Oracle ODBC drivers, but fails with interBase.

How to repeat:
Create Table: CREATE TABLE `test8651` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(128) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO test8651 (id, name) VALUES (NULL, 'test string 1');
INSERT INTO test8651 (id, name) VALUES (NULL, 'test string 1');
-------------------------------------------------------------------------------
'VB code that illustrates the bug
Sub issue8651()
'VB6 SP4 , ADO 2.7
On Error GoTo TestMySQL_Error
  Dim iFields As Integer
  Dim strSQL As String, strConnect As String
  Dim cnTest As ADODB.Connection
  Dim rsTest As ADODB.Recordset
  
  ' 'CREATE DATABASE TEST' run from MySql client

  strConnect = "Provider=MSDASQL;" & _
                       " DRIVER=MySQL ODBC 3.51 Driver;" & _
                       " SERVER=localhost;" & _
                       " DATABASE=test;" & _
                       " UID=zzzz; PWD=zzzz;"
  
  'Open ADODB connection
  Set cnTest = New ADODB.Connection
  cnTest.Mode = adModeReadWrite
  cnTest.CommandTimeout = 10000
  cnTest.ConnectionTimeout = 20
  cnTest.Open strConnect
  

  strSQL = "SELECT `name` FROM `test8651` where `name`=""test string 1"""
  Set rsTest = New ADODB.Recordset
  rsTest.Open strSQL, cnTest, 1, 3
  
  rsTest("name") = "replaced string"
  rsTest.Update
  
  cnTest.Close: Set cnTest = Nothing
  

  
Exit Sub
TestMySQL_Error:

  Debug.Print Err.Number
  Debug.Print Err.Source
  Debug.Print Err.Description
  Debug.Print strSQL

  MsgBox "Error"
  
  End
  Resume
End Sub

MyODBC driver was tested and compared with MS SQL ODBC driver.
MS SQL driver performs UPDATE operations using SQLPos function, but MyODBC is being called directly SQLExecDirect(hStmt, "UPDATE....");
MyODBC 3.51.x doesn't support SQL_KEYSET_CURSOR_ATTRIBUTES1 and SQL_KEYSET_CURSOR_ATTRIBUTES2 options requested by ADODB.Recordset functions. These options are needed for proper updates for SQLSetPos().
More detailed information is here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcupdating_dat...
Options mentioned above will be supported in MyODBC 5.

I don't see a reason we can't fix SQL_KEYSET_CURSOR_ATTRIBUTES1 in 3.51.

looking at this a little, i don't think this is actually a matter of supporting keyset-driven cursors. ado may just be falling back to trying to use a keyset-driven cursor for other reasons. need to dig into the trace a little more.

This is working fine with 3.51 and 5.1. ADO is not using SQLSetPos() with 3.51, but is with 5.1. The log shows it uses a descriptor with 5.1, but it doesn't seem to me that this should influence the behaviour.

EricM will add an ADO bug test for this in the suite to prevent regression in the future.

The fix for bug#27351 causes ADO to use SQLSetPos() w/ODBC 3.51 driver.

Tested with 3.51.23, and 5.1.1  Problem is resolved

Tested added to ADO conformance tests
/bugs/20194.vbs