MySQL Bugs: #46054: Setting local variables causes error

Bug #46054	Setting local variables causes error
Submitted:	8 Jul 2009 19:35	Modified:	10 Jul 2009 6:17
Reporter:	Todd Brewer	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	6.0.4.0	OS:	Windows (Vista 64)
Assigned to:		CPU Architecture:	Any
Tags:	.NET. Connector, Local variables

Description:
Using a local variable returns a "Fatal error encountered during command execution." error.

How to repeat:
Example:

SET @StartDate = NOW();
SELECT @StartDate;

This same type of code works fine in the 5.x version of the connector.

I get this error when I do a simple DataAdaptor.Fill(Table).

Hi Todd and thanks for your report.

Unfortunately, you provided neither enough info nor test case so I had to guess around...

"Worked in 5.X" is in no way enough as there was a change in processing this problem in, I think, 5.2.3 or so.

Now, using 5.2 latest sources and sources in trunk (6.1.0 but for your case it's 6.0.5 code) on Win2k8x64 against remote MySQL server 5.1.31 I am unable to repeat the problem:
            MySqlConnection conn = new MySqlConnection();
            conn.ConnectionString = "DataSource=***;Database=test;UserID=***;Password=***;PORT=***;Allow Zero Datetime=True;allow user variables = true";
            conn.Open();

            MySqlCommand cmdCreateTable = new MySqlCommand("SET @StartDate = NOW()", conn);
            cmdCreateTable.CommandTimeout = 0;
            cmdCreateTable.ExecuteNonQuery();
            cmdCreateTable.CommandText = "SELECT @StartDate";

            DataTable dt = new DataTable();
            MySqlDataAdapter da = new MySqlDataAdapter(cmdCreateTable);
            da.Fill(dt);

            DisplayData(dt); //irrelevant
            dt.Clear();
            dt.Dispose();
            da.Dispose();
            cmdCreateTable.Dispose();
            conn.Close();

            Console.WriteLine("Done.");
Output:
@StartDate = System.Byte[]
============================
Done.

Example showing this bug

Attachment: bug-data-46054-46055.zip (application/zip, text), 36.78 KiB.

I have attached a zipped Visual Studio 2008 project showing this bug.

For this bug, click on the "Local Variables" button.

Again, this works fine when I run this same application using the 5.1.5 connector.  As soon as I installed the 6.0.4 connector I had several reports break and I narrowed it down to this issue, and the issue outlined in Bug #46055.

By the way, I can replicate this using the following servers:

5.0.36 enterprise
5.0.67 community
5.1.36 community

Todd, you did not check on my test case. From what I see in attached code, you're still missing "allow user variables = true" connection option. Please add it and retest.

Further more, you will get BINARY data out of first query (which is expected and documented) so you should rewrite it as follows for your code to work:

            string sQuery =
                @"SET @StartDate = NOW();
                SELECT CAST(@StartDate AS CHAR) AS Now_Is;";
            RunQuery(sQuery);

Todd, providing you made above mentioned changes, problem is not repeatable with your test case. So, closing again.
Let me check the other report.