MySQL Bugs: #16307: @@Identity returning incorrect value

Bug #16307	@@Identity returning incorrect value
Submitted:	9 Jan 2006 17:44	Modified:	7 Aug 2006 0:57
Reporter:	[ name withheld ]	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	1.0.7	OS:	Windows (Windows XP and Server2003)
Assigned to:	Reggie Burnett	CPU Architecture:	Any

Description:
When I submit an INSERT to the database, then immediately try to get the AutoIncrement value of the Primary Key column for the row just inserted via a SELECT @@IDENTITY command, it returns an incorrect value. For my current database, the actual values for the inserted rows are now just above 11,100 (confirmed by looking at the db table via SqlYog) but the SELECT @@IDENTITY command is consistently returning values over 54,000.

There are no triggers or any other updates occurring between the INSERT and the SELECT @@IDENTITY command. Below is my code.'

This feature worked fine in all Connector/Net releases until 1.0.7 (on both MySQL Server 4.x and 5.x). I am using Connector/Net 1.0.7, Visual Studio .NET 2003, ASP.NET 1.1, C# and MySQL 5.0.15.

Neil

How to repeat:
Here is my code. Note that the CallReportNum is the primary key and autoincrement column. I supply it with a dummy value of 0 and the database has always correctly given it the autoincrement value upon insertions.

public static int AddReport(secure.dsReports ds)
{

int intCallNum;
string ConnectString = settings.ConnectionString;
string strSQL;

strSQL = "INSERT INTO treports(OrgNum, CallReportNum, Stamp, WasRealCall, WasHangup) " +
"VALUES (?OrgNum, ?CallReportNum, ?Stamp, ?WasRealCall, ?WasHangup)";

MySql.Data.MySqlClient.MySqlConnection cn = new MySqlConnection(ConnectString);
MySql.Data.MySqlClient.MySqlCommand cmd = new MySqlCommand(strSQL, cn);
MySql.Data.MySqlClient.MySqlParameterCollection pc = cmd.Parameters;
			
pc.Add("?OrgNum", MySqlDbType.Int32, 0, "OrgNum");
pc.Add("?CallReportNum", MySqlDbType.Int32, 0, "CallReportNum");
pc.Add("?Stamp", MySqlDbType.String, 0, "Stamp");
pc.Add("?WasRealCall", MySqlDbType.String, 0, "WasRealCall");
pc.Add("?WasHangup", MySqlDbType.String, 0, "WasHangup");

MySqlDataAdapter da = new MySqlDataAdapter();
da.InsertCommand = cmd;

da.Update(ds.treports);

strSQL = "SELECT @@IDENTITY AS 'Identity';";
MySqlCommand cmd2 = new MySqlCommand(strSQL, cn);
cn.Open();
MySqlDataReader dr = cmd2.ExecuteReader();
dr.Read();
intCallNum = Int32.Parse(dr.GetValue(0).ToString());
dr.Close();
cn.Close();

return intCallNum

}

Thanks for the bug report. I was able to reproduce the bug. My test case is attached.

Neil

Do you have to have a certain number of records in the table before it starts failing?  I have been unable to reproduce this using the following NUnit test case.  Perhaps you can modify the test case to show the problem?  Also, do you still get the problem with later versions of 5.0?  

[Test]
public void AutoIncrement()
{
    execSQL("DROP TABLE IF EXISTS test");
    execSQL("CREATE TABLE test (testID int(11) NOT NULL auto_increment, testName varchar(100) default '', " +
                    "PRIMARY KEY  (testID)) ENGINE=InnoDB DEFAULT CHARSET=latin1");
    MySqlCommand cmd = new MySqlCommand("INSERT INTO test VALUES (NULL, 'test')", conn);
     cmd.ExecuteNonQuery();
     cmd.CommandText = "SELECT @@IDENTITY as 'Identity'";
    MySqlDataReader reader = null;
    try
      {
          reader = cmd.ExecuteReader();
          reader.Read();
          int ident = Int32.Parse(reader.GetValue(0).ToString());
          Assert.AreEqual(1, ident);
      }
      catch (Exception ex)
      {
         Assert.Fail(ex.Message);
      }
      finally
      {
         if (reader != null)
         reader.Close();
      }
}

Unable to repeat.  Can you forward a chunk of code that reproduces the problem?

hello-
thanks for your note. this goes back about 7 months and i have since migrated my code considerably, and reverted to 1.0.6, due to this issue. i no longer have any code samples to provide, but i believe the initial researcher at mysql did in fact reproduce this issue. sorry i can't be of further help.
neil

Neil

The person who reproduced it is no longer with MySQL and, contrary to his note on the bug report, he did not attach the test case to it.  I've been unable to reproduce so I'm going to leave it cannot repeat.  We are about to release 1.0.8 and have already released 5.0.0 alpha 1.  If you are able, we would appreciate you  testing these releases and letting us know if you still see any issues.

Thanks
Reggie