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

[11 Oct 2005 14:10] Brandon Schenz
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.
[12 Oct 2005 7:26] Vasily Kishkin
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.
[12 Oct 2005 7:27] Vasily Kishkin
Test case

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

[14 Oct 2005 14:28] Brandon Schenz
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
[16 Nov 2005 14:57] Reggie Burnett
Fixed in 1.0.7
[9 Dec 2005 19:09] Reggie Burnett
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.
[9 Dec 2005 19:14] Paul DuBois
Noted in 1.0.7 changelog.