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

Bug #45502	error if "Allow Batch=False"
Submitted:	15 Jun 16:05	Modified:	18 Jun 10:59
Reporter:	Miguel K
Status:	Closed
Category:	Connector/Net	Severity:	S2 (Serious)
Version:	6.0.4	OS:	Microsoft Windows (Server 2008)
Assigned to:	Reggie Burnett	Target Version:
Triage:	D2 (Serious)

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