MySQL Bugs: #45329: Autoincrement within Transaction

Bug #45329	Autoincrement within Transaction
Submitted:	4 Jun 2009 11:38	Modified:	22 Jul 2009 9:36
Reporter:	Wolfgang Ahrens	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / NET	Severity:	S3 (Non-critical)
Version:	6.03, 6.04	OS:	Windows (XP)
Assigned to:		CPU Architecture:	Any
Tags:	autoincrement; rollback ; transaction

Description:
When doing an "Insert" within a transaction then the autoincrement value will increase regardless of the rollback transaction.

The rollback of the insert-command works fine.

How to repeat:
begin transaction;

insert ...

rollback transaction;

Hi Wolfgang and thanks for your report. I would like to see full test case attached and some info on MySQL server and OS / .NET FW used.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Server OS : MS Server2003
SQL-Server: 5.1.33
MS Visual Studio 2008
Net FW 3.5 + 2.0

Thanks Wolfgang. Now I would like to see your test case attached.

'CREATE TABLE `main` (
'  `Id` int(11) NOT NULL AUTO_INCREMENT,
'  `Date` date NOT NULL DEFAULT '1000-01-01',
'  PRIMARY KEY (`Id`)
') ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;

'CREATE TABLE `sub` (
'  `Id` int(11) NOT NULL AUTO_INCREMENT,
'  `Text` text NOT NULL,
'  PRIMARY KEY (`Id`)
') ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

Imports MySql.Data.MySqlClient

Module Module1
  Dim sqlDBConn As MySqlConnection
  Dim sqlCmd As New MySqlCommand("show tables", sqlDBConn)

  Sub Main()
    Dim connStr As String
    Dim i0 As Integer = 0
    Dim i1 As Integer = 0

    connStr = String.Format("server={0};user id={1}; password={2}; database={3}; pooling=false", _
      "sbosrv", "root", "weser", "test") ' change to your values
    Try
      sqlDBConn = New MySqlConnection(connStr)
      sqlDBConn.Open()
    Catch ex As MySqlException
      Console.WriteLine("Server nicht erreichbar : " + ex.Message)
    End Try

    Dim myTrans As MySqlTransaction
    myTrans = sqlDBConn.BeginTransaction

    Try
      sqlCmd = New MySqlCommand("insert into main values(0, '2009-01-01')", sqlDBConn)
      sqlCmd.Transaction = myTrans

      sqlCmd.ExecuteNonQuery()
      i0 = sqlCmd.LastInsertedId

      Dim _s As String = "test" & Chr(146) ' this is the error 

      sqlCmd.CommandText = "insert into sub values(0,'" & _s & "')"
      sqlCmd.ExecuteNonQuery()
      i1 = sqlCmd.LastInsertedId

      myTrans.Commit()

    Catch ex As Exception
      myTrans.Rollback()
      Console.WriteLine("Error in transaction :" & ex.ToString)
    End Try
    sqlCmd.Dispose()
    sqlDBConn.Close()

    Console.WriteLine("master =" & i0.ToString)
    Console.WriteLine("sub =" & i1.ToString)

    While Console.Read = 0
    End While
  End Sub

End Module

Thanks Wolfgang. Looking into this.

Wolfgang, this is not a bug but expected behavior that happens in every RDBMS we know. Generated values are not a part of transaction and they don't care about other statements. As AI value of 8 was generated it is expected for 8 to be returned, rollback or not.

Same behavior is observed in mysql command line client:
CREATE TABLE b45329_1 (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `Date` date NOT NULL DEFAULT '1000-01-01',
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;
mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                0 |
+------------------+
1 row in set (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                0 |
+------------------+
1 row in set (0.00 sec)

mysql> insert into b45329_1 values(0, '2009-01-01');
Query OK, 1 row affected (0.00 sec)

mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                8 |
+------------------+
1 row in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.01 sec)

mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                8 |  << 8 was indeed generated
+------------------+
1 row in set (0.00 sec)

mysql> select * from b45329_1;
Empty set (0.00 sec) << So it is rolled back

Sorry, but before i used a different connector and i never had the problem that an autoincrement value was increased when the transaction was aborted.

Otherwise autoincrement can not be used for invoice numbers, etc.

Wolfgang, true. Do *not* use AI values for invoice numbers. Since invoices usually have few different types, what I did, on my previous job, was to calculate Invoice#/Type pair and leave AI Id for searches/updates.

I'm curious, what have you used before? I do not know of SQL server that will work the way you want it.

Hallo Tonci,

the NET-Connector i used before was "MySQLDriverCS.dll".

http://sourceforge.net/project/shownotes.php?release_id=256810

This connector does not produce this useless increments.

Wolfgang

Wolfgang, thanks for the info but you're missing the point...
>> This connector does not produce this useless increments.

Even mysql cl client produces increments as that's how MySQL server works. Nothing to do with c/NET. It seems obvious to me that your old client was using some sort of client-side caching and not sending commands to server at all...