Bug #42493 Transactions broken when using Batch Commands

• Submitted: 30 Jan 2009 21:36
• Modified: 11 Feb 2009 17:12
• Reporter: Ael Malinka
• Status: Not a Bug
• Category: Connector / NET
• Severity: S1 (Critical)
• Version: 5.1.2 & 5.2.2
• OS: Any
• CPU Architecture: Any
• Tags: AllowBatch, transaction

[30 Jan 2009 21:36] Ael Malinka

Description:
When connecting to a DB with AllowBatch enabled running queries inside a Transaction does not seem to do anything.  So somehow AllowBatch option is bypassing Transaction securities

How to repeat:
1) Create "asdf" database on mysql server

2) Create C# Application similar to below

3) Execute

4) Find that test table was not only created but also has 1 row with val = "test" despite lack of commit and even with an explicit rollback after reader parseing

using System;
using System.Collections.Generic;
using MySql.Data.MySqlClient;

namespace MySqlCmd
{
	class CmdTest
	{
		public static void Main(string[] Args)
		{
			string cs = "";
			MySqlConnectionStringBuilder build = new MySqlConnectionStringBuilder();
			build.AllowBatch = true;
			build.Database = "asdf";
			build.UserID = "root";
			build.Server = "mysql";
			build.Port = 3306;
			cs = build.ConnectionString;
			build.Clear();
			using (MySqlConnection con = new MySqlConnection(cs))
			{
				con.Open();
				using (MySqlTransaction trans = con.BeginTransaction())
				{
					try
					{
						using (MySqlCommand cmd = new MySqlCommand(
							"CREATE TABLE IF NOT EXISTS test(" +
								"id INT AUTO_INCREMENT PRIMARY KEY," +
								"val VARCHAR(255)" +
							");" +
							"INSERT INTO test VALUES(NULL, 'test');" +
							"SELECT * FROM test;",
							con,
							trans))
						{
							List<Dictionary<string, object>> ret = new List<Dictionary<string, object>>();
							using (MySqlDataReader rdr = cmd.ExecuteReader())
							{
								while (rdr.Read())
								{
									Dictionary<string, object> row = new Dictionary<string, object>();
									for (int x = 0; x < rdr.FieldCount; x++)
									{
										row.Add(rdr.GetName(x), rdr.GetValue(x));
									}
									ret.Add(row);
								}
							}
						}
					}
					catch (Exception ex)
					{
						Console.WriteLine(ex.Message);
					}
				}
			}
		}
	}
}

[11 Feb 2009 16:57] Tonci Grgin

Hi Ael and thanks for your report.

There *is* something fishy happening and it's not completely due to AllowBatch...

    6 Connect	root@localhost on test
    6 Query	SHOW VARIABLES
    6 Query	SHOW COLLATION
    6 Query	SET character_set_results=NULL
    6 Init DB	test
    6 Query	SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
    6 Query	BEGIN
    6 Query	CREATE TABLE IF NOT EXISTS bug42493(id INT AUTO_INCREMENT PRIMARY KEY,val VARCHAR(255));INSERT INTO bug42493 VALUES(NULL, 'test');SELECT * FROM bug42493
    6 Query	ROLLBACK
    6 Quit

Produces following:
mysql> select * from bug42493;
+----+------+
| id | val  |
+----+------+
|  1 | test |
+----+------+
1 row in set (0.00 sec)

mysql> show create table bug42493;
+----------+--------------------------------------------------------------------
--------------------------------------------------------------------------------
-------------------------------+
| Table    | Create Table

                               |
+----------+--------------------------------------------------------------------
--------------------------------------------------------------------------------
-------------------------------+
| bug42493 | CREATE TABLE `bug42493` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `val` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 |
+----------+--------------------------------------------------------------------
--------------------------------------------------------------------------------
-------------------------------+
1 row in set (0.00 sec)

Verified as described with test case attached with MySQL server 5.1.30 on w2k8 x64 localhost using latest c/NET 5.2 sources.

[11 Feb 2009 17:15] Tonci Grgin

http://dev.mysql.com/doc/refman/5.1/en/implicit-commit.html:
  CREATE finished previous transaction
Now this is first part of your problem. Second part would be using autocommit. Please check this with mysql cl. client:
mysql> show variables like "%auto%";
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
--
| autocommit                  | ON    |
--
+-----------------------------+-------+
7 rows in set (0.00 sec)

So this is actually expected behavior and not a bug.