MySQL Bugs: #26985: Recordset Update() fails in 5.1 ODBC connector when using adUseClient cursor

Bug #26985	Recordset Update() fails in 5.1 ODBC connector when using adUseClient cursor
Submitted:	9 Mar 2007 2:49	Modified:	14 Dec 2007 15:58
Reporter:	Michael Pryor (Candidate Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S1 (Critical)
Version:	5.1.0-alpha	OS:	Windows (Windows)
Assigned to:	Jess Balint	CPU Architecture:	Any
Tags:	ADO

Description:
Using the AddNew/Update functions of ADO MyODBC generates an error:

[MySQL][MyODBC 5.00.12][MySQL] 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.`A` (`b`) VALUES ('foo123')' at line 1  

How to repeat:
For code to reproduce do the following:

mysql> create database test;
mysql> use database test;
mysql> create table A ( b varchar(30) );

Create a file called test.asp in your c:\inetpub\wwwroot directory (or any other
virtual directory)

Put this in it:
<%

Dim db: Set db = Server.CreateObject("ADODB.Connection")
db.Open "DRIVER={MySQL Connector/ODBC v5};server=localhost;uid=root;pwd=password;database=test;Option=3;"

Dim rs: Set rs = Server.CreateObject( "ADODB.Recordset" )
rs.CursorLocation = 3
rs.Open "SELECT b from A where b = ''", db, 1, 3
rs.AddNew
rs("b") = "foo123"
rs.Update

%>
Done.

Note the 1 is adOpenKeyset, and the 3 is adLockOptimistic in the rs.Open call.  This is standard for this kind of query (our app has been using it with MyODBC v3 for a few years now).  There was a similar bug when MySQL 5 came out, but adding .CursorLocation = 3 to all code of this form fixed that.

Hi Michael and thanks for your report. I was unable to verify it using MySQL server 5.0.34BK on Win XP Pro SP2 localhost. If you have more info to provide, please reopen the report.

Test case:
Option Explicit
Const DSN = "Driver={MySQL connector/ODBC v5};Uid=root;Pwd=;Server=localhost;Database=test;OPTION=1 + 2 + 32"

Const adOpenForwardOnly = 0
Const adOpenKeyset = 1
Const adOpenDynamic = 2
Const adOpenStatic = 3
Const adOpenUnspecified = -1

Const adUseNone = 1
Const adUseServer = 2
Const adUseClient = 3

Const adLockReadOnly = 1
Const adLockPessimistic = 2
Const adLockOptimistic = 3

'---- ParameterDirectionEnum Values ----
Const adParamUnknown = &H0000
Const adParamInput = &H0001
Const adParamOutput = &H0002
Const adParamInputOutput = &H0003
Const adParamReturnValue = &H0004

'---- CommandTypeEnum Values ----
Const adCmdUnknown = &H0008
Const adCmdText = &H0001
Const adCmdTable = &H0002
Const adCmdStoredProc = &H0004
Const adCmdFile = &H0100
Const adCmdTableDirect = &H0200

Const adInteger=3
Const adDate=7
Const adVarChar=200

Dim cnxDatabase
Dim strSQL

' connecting database
Set cnxDatabase = CreateObject("ADODB.Connection")
cnxDatabase.Open(DSN)

' querying data
strSQL = "SELECT * FROM bug26985"

Dim rs
Set rs = CreateObject("ADODB.Recordset")
With rs
    .ActiveConnection = cnxDatabase
    .LockType = adLockOptimistic 'see definitions above
    .CursorType = adOpenKeyset 'see definitions above
    .Open(strSQL)
End With
rs.AddNew
rs("b") = "foo123"
rs.Update
rs.Close

Set rs = Nothing
Set cnxDatabase = Nothing

C:\mysql507\bin>mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.34-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table bug26985 ( b varchar(30) );
Query OK, 0 rows affected (0.08 sec)

mysql> select * from bug26985;
+--------+
| b      |
+--------+
| foo123 |
+--------+
1 row in set (0.00 sec)

mysql>

General query log:
070309 11:18:43	      6 Connect     root@localhost on test
		      6 Query       SET NAMES utf8
		      6 Query       SELECT database()
		      6 Query       SELECT database()
		      6 Query       SELECT database()
		      6 Query       SELECT * FROM bug26985
		      7 Connect     root@localhost on test
		      7 Query       SET NAMES utf8
		      7 Query       INSERT INTO bug26985 ( b) VALUES('foo123')
		      7 Quit       
		      6 Quit

Tonci,

You forgot the line

rs.CursorLocation = 3

This is necessary to work around a bug in earlier versions of MyODBC.
(See http://bugs.mysql.com/bug.php?id=13776 )

You are correct, without this line it works.  With it, it fails.  But without it, older versions of MyODBC and MySQL 5 will not work because of bug 13776

Michael you are right.
Modifying code like you suggested
With rs
    .ActiveConnection = cnxDatabase
    .LockType = adLockOptimistic '3
    .CursorType = 2 'adOpenStatic 3, adOpenKeyset   1
    .CursorLocation = 3 'adUseClient
    .Open(strSQL)
leads to error reported.
The problem seems to occur only with CursorLocation = 3 'adUseClient while options 1 and 2 work as can be seen from general query log:
CS cursor:
070312  8:54:26	      1 Connect     root@localhost on test
		      1 Query       SET NAMES utf8
		      1 Query       SELECT database()
		      1 Query       SELECT database()
		      1 Query       SELECT database()
070312  8:54:27	      1 Query       SELECT * FROM bug26985
		      2 Connect     root@localhost on test
		      2 Query       SET NAMES utf8
		      2 Query       SELECT COLUMN_NAME, SEQ_IN_INDEX FROM   INFORMATION_SCHEMA.STATISTICS WHERE  TABLE_SCHEMA='def' AND TABLE_NAME='bug26985' AND INDEX_NAME='PRIMARY' ORDER BY SEQ_IN_INDEX
070312  8:54:28	      2 Quit       
		      3 Connect     root@localhost on test
		      3 Query       SET NAMES utf8
070312  8:54:29	      3 Query       SELECT * FROM   INFORMATION_SCHEMA.STATISTICS WHERE  TABLE_SCHEMA='def' AND TABLE_NAME='bug26985'
		      3 Quit       
		      4 Connect     root@localhost on test
		      4 Query       SET NAMES utf8
		      5 Connect     root@localhost on test
		      5 Query       SET NAMES utf8
070312  8:54:52	      5 Quit       
		      4 Query       INSERT INTO `def`.`test`.`bug26985` (`b`) VALUES ('ABCDEFGH')
		      4 Quit       
070312  8:55:11	      1 Quit 

SS or Undefined cursor location:
070312  8:59:23	     14 Connect     root@localhost on test
		     14 Query       SET NAMES utf8
		     14 Query       SELECT database()
		     14 Query       SELECT database()
		     14 Query       SELECT database()
		     14 Query       SELECT * FROM bug26985
		     15 Connect     root@localhost on test
		     15 Query       SET NAMES utf8
070312  8:59:24	     15 Query       INSERT INTO bug26985 ( b) VALUES('foo123123')
		     15 Quit       
		     14 Quit

200707131016_mysql-bin.000172_242162957

Hi Guys,

It would be very helpful to us if this could be resolved. We really need the UTF-8
abilities of Connector/ODBC 5 and this is preventing us from being able to use this. Thank
you in advance.

Updating this case as it still exists in the current shipping 5.1 alpha version

Hi Michael,

many thanks for you help.

Regards,

Susanne

Bug #27903 and Bug #27961 are duplicates of this bug here.

Michael,

Thanks for your patience with this.  A couple of things you should make note of, first you are specifying a Keyset Cursor.  I'm not sure if you are depending on this capability or not but I don't believe this is a possibility with adUseClient cursor.  You will be resigned to adOpenStatic.  If you need dynamic capabilities, you will need to use adUseServer and add option 32 to your connection string, although this won't get you Keyset either, it will instead open as AdOpenDynamic.

Also the failure here doesn't appear to be with addNew().  The failure occurs when Update() is called, which is unfortunately quite a bit worse.  Since this does appear to be working with a ServerSide cursor (adUseServer) this might be a workaround for you, if you can use it.

Upgrading priority to P2 since Update() is essential functionality, however there is the Server Side cursor workaround.

I do not believe it can be said at this point that bug #27903 is a duplicate of this one as stated above.  UpdateBatch() is fundametally different from Update() so until Update() works this can't be proven.

A test case for this issue has been added to the ADO test suite:
connectors-svnroot/odbc-testing/ado-conformance/trunk/bugs/26985.vbs

The workaround for Bug #13776 requires a client side cursor.

As well, the documents on the MySQL site at
http://dev.mysql.com/tech-resources/articles/vb-cursors-and-locks.html
basically say "every single app (except for one) uses client side cursors because they are better".

I'm paraphrasing, but effectively because of bug 13776, use a server side cursor is not an acceptable solution.

odbc trace for ADO bug test

Attachment: 26985.LOG (application/octet-stream, text), 100.36 KiB.

see case #32064 which I discovered while trying to work around this bug.

Fix committed and will be released in 5.1.1.

Does the fix Michael's original bug? There seemed to be some back and forth towards the end of this case as to whether a resolution satisfactory to Michael's use was possible.

A note has been added to the 5.1.1 changelog: 

Recordset Update() fails when using adUseClient cursor.

This bug is still present, albeit in a different form.

I'm using out of the box software, OpenWiki, which works perfectly with 3.51 ODBC driver.

The behavior now is that after an .update(), a new record is inserted but TEXT fields are set to null.  The data types of varchar, integer, and datetime are updated without any problem.

Here is the code from owdb.asp that doesn't work.  Note, no error is raised.  The update happens, just with null data.

vRS.Open "openwiki_revisions", conn, adOpenKeyset, adLockOptimistic, adCmdTable
vRS.AddNew
vRS("wrv_name")       = gPage
vRS("wrv_revision")   = vRevision
vRS("wrv_current")    = 1
vRS("wrv_status")     = vStatus
vRS("wrv_timestamp")  = Now()
vRS("wrv_minoredit")  = pMinorEdit
vRS("wrv_host")       = vHost
vRS("wrv_agent")      = vUserAgent
vRS("wrv_by")         = vBy
vRS("wrv_byalias")    = vByAlias
vRS("wrv_comment")    = pComment
vRS("wrv_text")       = pText
vRS("wrv_AuthorIndex") = vAuthorIndex
vRS.Update
vRS.Close

The wrv_comment and wrv_text fields are of data type TEXT, and are the ones that get set to null.

I downgraded to the 3.51 driver in order to fix this.

Environment:
MySQL 5.0.45
Windows Server 2003

Eric,
Likely the same as bug#37649, please try a snapshot build from:
http://downloads.mysql.com/snapshots.php

If the problem persists, open a new bug report.