MySQL Bugs: #45502: error if "Allow Batch=False"

Bug #45502	error if "Allow Batch=False"
Submitted:	15 Jun 2009 14:05	Modified:	18 Jun 2009 8:59
Reporter:	Miguel K	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	6.0.4	OS:	Windows (Server 2008)
Assigned to:	Reggie Burnett	CPU Architecture:	Any

Description:
Adding "Allow Batch=False" to the connection string causes the connector to not work.

Error:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SET character_set_results=NULL' at line 1

Omit "Allow Batch=False", it works.
Change to "Allow Batch=True", it works.

In the attached .aspx page, I show three connection strings. Two work, the one with "Allow Batch=True" does not.

Cheers

How to repeat:
<%@ Page LANGUAGE="VB" debug="true" Strict="false" Trace="false" EnableSessionState="false" EnableViewState="false" %>
<%@ Import Namespace = "MySql.Data.MySqlClient" %>
<%@ Import Namespace = "System.Data" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head></head>
<body>
<%
BigList()
%>
</body>
</html>

<Script runat="server">
' works:
'Dim sConnect As String = "Server=LOCALHOST;Database=test;uid=root;pwd=12341234;CHARSET=utf8;"
' works:
'Dim sConnect As String = "Server=LOCALHOST;Database=test;uid=root;pwd=12341234;CHARSET=utf8;Allow Batch=True;"
' does not work:
Dim sConnect As String = "Server=LOCALHOST;Database=test;uid=root;pwd=12341234;CHARSET=utf8;Allow Batch=False;"

Private myConnection2 As New MySqlConnection(sConnect)
Private myCommand2 As New MySqlCommand
Private myReader2 As MySqlDataReader

Private mySQL As String

Sub BigList()
	OpenBigList()

	If MyReader2.HasRows Then
		While myReader2.read()
			Response.write(myReader2("version()"))
		End While

		myReader2.Close
		myReader2 = Nothing
	End If
End Sub

Sub OpenBigList()
		mySQL = "SELECT version()"

		myConnection2.open()
		myCommand2.Connection = MyConnection2
		myCommand2.CommandText = mySQL
		myReader2 = myCommand2.ExecuteReader(system.data.CommandBehavior.CloseConnection)
End Sub
</Script>

Hi Miguel and thanks for your report.

Verified as described using latest SVN sources from trunk. Exception is thrown in command.cs, Ln:440. 5.2 branch is not affected by this problem.

Test case:
      MySqlConnection c = new MySqlConnection();
      c.ConnectionString = "DataSource=***;Database=test;UserID=***;Password=***;PORT=***;logging=True;Allow Batch=False";
      c.Open();
      MySqlCommand cmd = c.CreateCommand();
      cmd.CommandText = "SELECT version();";
      try
      {
           MySqlDataReader dr = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
           while (dr.Read())
           {
                Console.WriteLine(dr.GetDataTypeName(0));
                Console.WriteLine(dr.GetFieldType(0));
                Console.WriteLine(dr.GetValue(0).GetType().FullName);
                Console.WriteLine(dr.GetString(0));
           }
           dr.Close();
           cmd.Dispose();
           if (c.State == ConnectionState.Closed) { 
               Console.WriteLine("Conn closed"); }
           else {
               c.Close();
           }
                
      }
      catch (Exception ex)
      {
          Console.Out.WriteLine(DateTime.UtcNow.ToLongTimeString() + "  " + "Exception: " + ex.Message);
          throw;
      }

"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SET character_set_results=NULL' at line 1"

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/76407

fixed in 6.0.5

An entry was added to the 6.0.5 changelog:

Adding the Allow Batch=False option to the connection string caused Connector/NET to generate the error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL
server version for the right syntax to use near 'SET character_set_results=NULL' at line 1