MySQL Bugs: #13927: Multiple Records to same Table in Transaction Problem

Bug #13927	Multiple Records to same Table in Transaction Problem
Submitted:	11 Oct 2005 14:10	Modified:	9 Dec 2005 19:14
Reporter:	Brandon Schenz	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S3 (Non-critical)
Version:	1.0.6	OS:	Windows (Windows XP)
Assigned to:	Reggie Burnett	CPU Architecture:	Any

Description:
When I write multiple records to the same table in a transaction the first record is written for each record when commited instead of each unique record that I send.

How to repeat:
Here is what I am doing to reproduce it:

Database:
 `test_transaction`.CREATE DATABASE `test_transaction` /*!40100 DEFAULT CHARACTER SET latin1 */;

Table:
DROP TABLE IF EXISTS `test_transaction`.`table1`;
CREATE TABLE  `test_transaction`.`table1` (
  `intTable1ID` int(10) unsigned NOT NULL auto_increment,
  `strTable1Description` varchar(45) NOT NULL,
  PRIMARY KEY  (`intTable1ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Stored Procedure:
DELIMITER $$

DROP PROCEDURE IF EXISTS `test_transaction`.`uspAddTable1Record`$$
CREATE PROCEDURE  `test_transaction`.`uspAddTable1Record`(
  p_strTable1Description    VARCHAR(45)
)
BEGIN
  INSERT INTO Table1 (strTable1Description)
  VALUES(p_strTable1Description);

  SELECT Last_Insert_ID();
END$$

DELIMITER ;

VB.NET Code:
Imports MySql.Data.MySqlClient

Module Module1

	Sub Main()
		Dim myConnection As New MySqlConnection("Data Source=127.0.0.1;database=test_transaction;uid=root;pwd=yorktown;pooling=false;")
		myConnection.Open()

		Dim myCommand As MySqlCommand = myConnection.CreateCommand()
		Dim myTrans As MySqlTransaction

		' Start a local transaction
		myTrans = myConnection.BeginTransaction()
		' Must assign both transaction object and connection
		' to Command object for a pending local transaction
		myCommand.Connection = myConnection
		myCommand.Transaction = myTrans

		Try
			myCommand.CommandText = "uspAddTable1Record"
			myCommand.CommandType = CommandType.StoredProcedure

			myCommand.Parameters.Add("?p_strTable1Description", "Test from VB.NET - Record 1")
			myCommand.Parameters("?p_strTable1Description").Direction = ParameterDirection.Input

			Dim drCommandResults As MySqlDataReader = myCommand.ExecuteReader()
			Dim intTable1ID As Integer

			' Should be 1 and only 1 record returned
			If drCommandResults.Read = True Then
				' Get the new ID
				intTable1ID = Convert.ToInt32(drCommandResults.Item(0))
			End If

			drCommandResults.Close()

			myCommand.CommandText = "uspAddTable1Record"
			myCommand.CommandType = CommandType.StoredProcedure

			myCommand.Parameters.Add("?p_strTable1Description", "Test from VB.NET - Record 2")
			myCommand.Parameters("?p_strTable1Description").Direction = ParameterDirection.Input

			Dim drCommandResults2 As MySqlDataReader = myCommand.ExecuteReader()
			Dim intTable2ID As Integer

			' Should be 1 and only 1 record returned
			If drCommandResults2.Read = True Then
				' Get the new ID
				intTable2ID = Convert.ToInt32(drCommandResults2.Item(0))
			End If

			drCommandResults2.Close()

			myTrans.Commit()
			Console.WriteLine("Both records are written to database.")
		Catch e As Exception
			Try
				myTrans.Rollback()
			Catch ex As MySqlException
				If Not myTrans.Connection Is Nothing Then
					Console.WriteLine(ex.ToString)
				End If
			End Try

			Console.WriteLine(e.ToString)
			Console.WriteLine("Neither record was written to database.")
		Finally
			myConnection.Close()
		End Try
	End Sub

End Module

Suggested fix:
Not sure if this is a MySQL Issue, or a connector issue.

Thanks for bug report. I was able to reproduce the bug.

mysql> select * from `test_transaction`.`table1`;
+-------------+----------------------------+
| intTable1ID | strTable1Description       |
+-------------+----------------------------+
|           1 | Test from VB.NET -Record 1 |
|           2 | Test from VB.NET -Record 1 |
+-------------+----------------------------+
2 rows in set (0.00 sec)

Test case is attached.

Test case

Attachment: 13927.zip (application/x-zip-compressed, text), 5.92 KiB.

Possible not a bug, or at least not related to transactions.

Clearing the paramters between the two Stored Procedures not returns expected results.  Is this a bug ot not?  I would think though that when we assign a new value to the paramter in the second set of statements that these new values should be used.

I got the same results without using transactions (two of the same record being written), so I changed my vb.net code as follows:

Module Module1

	Sub Main()
		Dim myConnection As New MySqlConnection("Data
Source=127.0.0.1;database=test_transaction;uid=root;pwd=yorktown;pooling=false;"
)
		myConnection.Open()

		Dim myCommand As MySqlCommand = myConnection.CreateCommand()
		Dim myTrans As MySqlTransaction

		' Start a local transaction
		myTrans = myConnection.BeginTransaction()
		' Must assign both transaction object and connection
		' to Command object for a pending local transaction
		myCommand.Connection = myConnection
		myCommand.Transaction = myTrans

		Try
			myCommand.CommandText = "uspAddTable1Record"
			myCommand.CommandType = CommandType.StoredProcedure

			myCommand.Parameters.Add("?p_strTable1Description", "Test from VB.NET -
Record 1")
			myCommand.Parameters("?p_strTable1Description").Direction =
ParameterDirection.Input

			Dim drCommandResults As MySqlDataReader = myCommand.ExecuteReader()
			Dim intTable1ID As Integer

			' Should be 1 and only 1 record returned
			If drCommandResults.Read = True Then
				' Get the new ID
				intTable1ID = Convert.ToInt32(drCommandResults.Item(0))
			End If

			drCommandResults.Close()

                        ' Clear the Parameters of the Command - THIS ALLOWS IT TO WORK AS EXPECTED
                        myCommand.Paramters.Clear()

			myCommand.CommandText = "uspAddTable1Record"
			myCommand.CommandType = CommandType.StoredProcedure

			myCommand.Parameters.Add("?p_strTable1Description", "Test from VB.NET -
Record 2")
			myCommand.Parameters("?p_strTable1Description").Direction =
ParameterDirection.Input

			Dim drCommandResults2 As MySqlDataReader = myCommand.ExecuteReader()
			Dim intTable2ID As Integer

			' Should be 1 and only 1 record returned
			If drCommandResults2.Read = True Then
				' Get the new ID
				intTable2ID = Convert.ToInt32(drCommandResults2.Item(0))
			End If

			drCommandResults2.Close()

			myTrans.Commit()
			Console.WriteLine("Both records are written to database.")
		Catch e As Exception
			Try
				myTrans.Rollback()
			Catch ex As MySqlException
				If Not myTrans.Connection Is Nothing Then
					Console.WriteLine(ex.ToString)
				End If
			End Try

			Console.WriteLine(e.ToString)
			Console.WriteLine("Neither record was written to database.")
		Finally
			myConnection.Close()
		End Try
	End Sub

End Module

Fixed in 1.0.7

Problem was that the parameter collection object's Add() method was adding the parameter to the list rather than first checking to see if the object already exists.  The fix was to update the value of the existing parameter object, if one exists.

Noted in 1.0.7 changelog.