Description:
Running a Drop / Create Procedure script through Connector/NET will end a transaction prematurely.
How to repeat:
The following C# program will cause this error to occur.
using System;
using System.Collections.Generic;
using System.Text;
using MySql.Data.MySqlClient;
namespace TransactionTest
{
class Program
{
static void Main(string[] args)
{
MySqlConnection connection = new MySqlConnection(
"Data Source=localhost;User Id=blank_user;Password=blank_user_pass;Database=BlankTest");
connection.Open();
MySqlTransaction trans = connection.BeginTransaction();
MySqlCommand procedureCommand = new MySqlCommand(@"
DROP PROCEDURE IF EXISTS NewMain;
CREATE PROCEDURE NewMain (
DataPoint1 varchar(50),
DataPoint2 int
)
BEGIN
INSERT INTO MainObject(DataPoint1, DataPoint2, LastUpdated) VALUES (DataPoint1, DataPoint2, NOW());
SELECT @@IDENTITY;
END", connection, trans);
procedureCommand.ExecuteNonQuery();
MySqlCommand command = new MySqlCommand("Call NewMain('String', 5)", connection, trans);
command.ExecuteNonQuery();
MySqlCommand readTransCommand = new MySqlCommand("SELECT * FROM MainObject", connection, trans);
using (MySqlDataReader reader1 = readTransCommand.ExecuteReader())
{
int irows = 0;
while (reader1.Read())
irows++;
Console.WriteLine(irows); // Should write 1, and does.
}
trans.Rollback();
MySqlCommand readCommand = new MySqlCommand("SELECT * FROM MainObject", connection, trans);
using (MySqlDataReader reader1 = readTransCommand.ExecuteReader())
{
int irows = 0;
while (reader1.Read())
irows++;
Console.WriteLine(irows); // Should write 0, writes 1 instead.
}
connection.Close();
}
}
}
--------------
The following table was used:
DROP TABLE IF EXISTS MainObject;
CREATE TABLE MainObject (
MainId int NOT NULL AUTO_INCREMENT PRIMARY KEY,
CreateTime timestamp NOT NULL,
LastUpdated datetime DEFAULT NULL,
DataPoint1 varchar(50) NOT NULL,
DataPoint2 int NOT NULL,
DataPoint3 real DEFAULT NULL
);
Suggested fix:
This is not an issue in the mysql client (when the commands are typed directly into the parser), so it's probably something internal to Connector.NET and the way its handling queries.
Description: Running a Drop / Create Procedure script through Connector/NET will end a transaction prematurely. How to repeat: The following C# program will cause this error to occur. using System; using System.Collections.Generic; using System.Text; using MySql.Data.MySqlClient; namespace TransactionTest { class Program { static void Main(string[] args) { MySqlConnection connection = new MySqlConnection( "Data Source=localhost;User Id=blank_user;Password=blank_user_pass;Database=BlankTest"); connection.Open(); MySqlTransaction trans = connection.BeginTransaction(); MySqlCommand procedureCommand = new MySqlCommand(@" DROP PROCEDURE IF EXISTS NewMain; CREATE PROCEDURE NewMain ( DataPoint1 varchar(50), DataPoint2 int ) BEGIN INSERT INTO MainObject(DataPoint1, DataPoint2, LastUpdated) VALUES (DataPoint1, DataPoint2, NOW()); SELECT @@IDENTITY; END", connection, trans); procedureCommand.ExecuteNonQuery(); MySqlCommand command = new MySqlCommand("Call NewMain('String', 5)", connection, trans); command.ExecuteNonQuery(); MySqlCommand readTransCommand = new MySqlCommand("SELECT * FROM MainObject", connection, trans); using (MySqlDataReader reader1 = readTransCommand.ExecuteReader()) { int irows = 0; while (reader1.Read()) irows++; Console.WriteLine(irows); // Should write 1, and does. } trans.Rollback(); MySqlCommand readCommand = new MySqlCommand("SELECT * FROM MainObject", connection, trans); using (MySqlDataReader reader1 = readTransCommand.ExecuteReader()) { int irows = 0; while (reader1.Read()) irows++; Console.WriteLine(irows); // Should write 0, writes 1 instead. } connection.Close(); } } } -------------- The following table was used: DROP TABLE IF EXISTS MainObject; CREATE TABLE MainObject ( MainId int NOT NULL AUTO_INCREMENT PRIMARY KEY, CreateTime timestamp NOT NULL, LastUpdated datetime DEFAULT NULL, DataPoint1 varchar(50) NOT NULL, DataPoint2 int NOT NULL, DataPoint3 real DEFAULT NULL ); Suggested fix: This is not an issue in the mysql client (when the commands are typed directly into the parser), so it's probably something internal to Connector.NET and the way its handling queries.