MySQL Bugs: #19052: Auto-increment field not updated in recordset after inserting

Bug #19052	Auto-increment field not updated in recordset after inserting
Submitted:	12 Apr 2006 13:01	Modified:	24 Jan 2008 8:55
Reporter:	Adrian Sandor	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S3 (Non-critical)
Version:	3.51.12	OS:	Windows (Windows XP)
Assigned to:	Jess Balint	CPU Architecture:	Any
Tags:	ADO, ODBC5-RC

Description:
I'm using MySQL 4.1.11 and MyODBC 3.51.12 and coding in ASP (javascript) with ADO. I want to insert a record through a recordset, and then get the generated ID (auto-increment) of the new record, back from the recordset.
This works with other database servers; however, with MySQL and MyODBC the result is undefined (the "undefined" value in javascript).

How to repeat:
Create a table:

CREATE TABLE `tst` (
  `ID` int(11) NOT NULL auto_increment,
  `Name` varchar(50) default NULL,
  PRIMARY KEY  (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

And run this code:

<%@Language=JavaScript%>
<!--METADATA TYPE="typelib" uuid="00000205-0000-0010-8000-00AA006D2EA4" -->
<%
var cnn=Server.CreateObject("ADODB.Connection");
cnn.Open("DSN=sysmysql");
cnn.Execute("USE test");
var rst=Server.CreateObject("ADODB.Recordset");
rst.Open("SELECT * FROM tst", cnn, adOpenForwardOnly, adLockPessimistic);
rst.AddNew();
rst("Name")="blah";
rst.Update();
Response.Write(rst("ID").Value+"<br>");
%>

(assuming that sysmysql is a system dsn that connects to mysql, and the database is called `test`)
The code is in javascript ASP, but it should be trivial to translate it to vbscript or vb if needed.

The actual result is "undefined"; the expected result is the ID of the new record (e.g. 2)

Suggested fix:
When Update() is called on the recordset (and succeeds), the auto-increment field should be updated with the generated value (i.e. LAST_INSERT_ID() )

Hi. Thanks for your problem report. Can you please do the same like I asked for #19065?

I tried all cursor types (adOpenDynamic, adOpenForwardOnly, adOpenKeyset,
adOpenStatic and adOpenUnspecified) and the result is exactly the same.

I'm not sure what else I can do (see my reply for the other bug).

Adrian, we are back to #19065... VB is working. Can you please try:
.AddNew("Id", 0) and inform me of result.

I tried it, although it doesn't make much sense to me.
Here's the vbs code:

Option Explicit
Const DSN = "DSN=sysmysql"
Dim cnxDatabase
Dim strSQL

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

' querying data
strSQL = "SELECT * FROM tst"

Dim rs
Set rs = CreateObject("ADODB.Recordset")
With rs
    .ActiveConnection = cnxDatabase
    .LockType = 3
    .CursorType = 1
    .Open(strSQL)
    .AddNew "ID", 0
    .Update
End With

WScript.Echo rs("ID").Value

The displayed value is 0; in the database, a record is created with an auto-generated ID and a NULL Name.

Hi Adrian.
I was unable to reproduce this problem in VB:
<cut>
        rs = New ADODB.Recordset
        With rs
            .ActiveConnection = cnxDatabase
            .LockType = LockTypeEnum.adLockOptimistic
            .CursorType = CursorTypeEnum.adOpenKeyset
            .CursorLocation = CursorLocationEnum.adUseClient

            .Open(strSQL)

            .AddNew("Name", "blabla")
            .Update()
        End With
        MsgBox(rs("ID").Value)
and I get 2, 3 and so on...
MySQL server version is 4.1.18-nt-max-log.
Other considerations on VB and VBS behavior I posted in #19065. Please follow the link to #15277 also, it may prove helpfull since I believe that this comes from MS update on your system.

> I was unable to reproduce this problem in VB

But have you reproduced it from scripting?

> Other considerations on VB and VBS behavior I posted in #19065

I replied there

Adrian, we are pushing on this scripting issue but with no definite results so far...

Verified as described by reporter with scripts provided.

This bug is easily reproduced in MSAccess 2003. Insert a record manually before running this code. I'd recommend upgrading to Severe!

Dim rs As dao.Recordset, i As Integer

Set rs = CurrentDb.OpenRecordset("tst")
For i = 1 To 2
   With rs
      .AddNew
      !name = "Blah"
      .Update
      MsgBox (!id)
   End With
Next
rs.Close

I need to know the record id that i have just created, but i have a concurrent environement, how could i get this?

The same thing happens to me using ADO on windows 2K. (I am using C++ & don't have code I can post)

When is this going to be addressed? It is really very important to be able to add a new record via ODBC, and to be able to retrieve the new autogenerated ID. S3 severity seems too low.

Completly agree with you, for me it is a priority because y can't write foreign keys.
I would have to be higer priority than Severity: S3 (Non-critical)
who has ODBC 3.51.11?

Hello all, I hope everybody is using 3.51.17GA. Please check myodbc3.h file and notice:
#define FLAG_AUTO_IS_NULL     (FLAG_SAFE << 6) /* 8388608 Enables SQL_AUTO_IS_NULL */

What happens if you add 8388608 to your OPTIONs (OPTION=1+2+8388608 ...)?

This is working fine in 5.1 beta with both static and dynamic cursors. If you are affected by this issue, please try out the new driver.

Script test case

Attachment: 19052.vbs (application/octet-stream, text), 1.79 KiB.

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Explanation: Attached test case shows everything works in both versions of MyODBC. Jess, if you have objections to test case and/or my ruling, please reopen the report.