MySQL Bugs: #53596: 'Allow User Variables = True'

Bug #53596	'Allow User Variables = True' - ignored in 6.3.1
Submitted:	12 May 2010 10:07	Modified:	12 May 2010 11:21
Reporter:	Jono G	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	6.3.1	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	6.3.1, Connector .NET, Connector/Net

Description:
Hi,

I have Visual Studio 2010.
I have MySQL Connector/NET 6.3.1.

I have a SQL Select query which includes SQL variables inside the select query.
To use these in previous connector/net versions, you had to enable the flag 'Allow User Variables = True' in the connection string.

When this is enabled in the connection string, the error at runtime is "fatal error encountered during command execution".

This error should not show because the flag for user variables = true is set in the connection string.

How to repeat:
The code I have is:

   Dim conn As New MySqlConnection
        Dim myCommand As New MySqlCommand
        Dim myAdapter As New MySqlDataAdapter
        Dim dt As New DataTable

        conn.ConnectionString = MySQLGlobal

        myCommand.Connection = conn
      
        myCommand.CommandText = "select date, @currentdate := date AS rowdate, ((SELECT quantity FROM config_roomtype WHERE id = '3') - (SELECT COUNT(id) FROM reservation_rooms WHERE room_type_id = '3' AND date = rowdate)) AS EQO from availability"

        Try
            conn.Open()
            myCommand.ExecuteNonQuery()
            myAdapter.SelectCommand = myCommand
            myAdapter.Fill(dt)

            dgvAvail.DataSource = dt

            Dim cellStyle As DataGridViewCellStyle = New DataGridViewCellStyle()
            cellStyle.BackColor = Color.Thistle
            cellStyle.Format = "dd/MM/yyyy"

            dgvAvail.Columns("date").DefaultCellStyle = cellStyle
            dgvAvail.Columns("id").Visible = False
            dgvAvail.Columns("date").HeaderText = "Date"

        Catch myerror As MySqlException
            MessageBox.Show("Error Connecting to Database: " & myerror.Message)

        Finally
            conn.Dispose()
        End Try

The only part that is causing the error is the select statement. It's this particular part "@currentdate := date AS rowdate". 

Suggested fix:
Fix 6.3.1 to not ignore the 'allow user variables = true' flag that is set in the connection string.

Hi Jono.

I am wondering if your query:
"select date, @currentdate := date AS rowdate, ((SELECT
quantity FROM config_roomtype WHERE id = '3') - (SELECT COUNT(id) FROM reservation_rooms WHERE room_type_id = '3' AND date = rowdate)) AS EQO from availability

works in command line client at all... Please test the correctness of your query first and then we'll see.

Hi Tonci,

Yes in the MySQL Query browser it returns the rows perfectly fine.
In VS2010 if I remove the @rowdate stuff as mentioned above, then i get no errors. 

Feel free to contact me via email and I can show you via Teamviewer my screen.....

Definitely works given correct query:
    MySqlConnection conn = new MySqlConnection();
    conn.ConnectionString = "DataSource=xx;Database=test;UserID=xx;Password=xx;PORT=xx;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);

and so on...

I have also tested this query in the mysql query browser:

"myCommand.CommandText = "select date, @currentdate := 7 AS rowdate FROM reservation_rooms"

It returns the rows in the mysql query browser.

And this returns the same error when run from VS2010.

So the problem is the '@currentdate := 7 AS rowdate'

That part is not working when used in VS2010 with MySQL Connector/NET 6.3.1

Don't know if that helps?

Can I show you on Teamviewer Tonci?

Thanks.

Jono, don't know what Teamviewer is...

Now, for your query. It comes as a surprise such syntax is actually allowed as it is meaningless. Can you tell me what on earth are you trying to achieve by putting "@currentdate := 7" in query?
I need to understand that before proceeding. Also, it would be best if you can make a test query not dependent on any tables you have (and I don't).

Hi Tonci,

Teamviewer is a screen sharer program where I can show you my screen in realtime and you can control my mouse/keyboard.

You can view more here: www.teamviewer.com - or download the program here http://www.teamviewer.com/download/TeamViewerQS.exe and give me your ID once installed so we can screenshare.

In regards to that last query, that was just a point of example to show that a SQL local variable that is generated inside the SQL Select does not work. The original query was a larger one dependant on multiple tables.

If you try this on a table with just a id column:

"select id, @numbercount := 1 AS numbercount, (@numbercount + id) as doublednumbercount FROM reservation_rooms"

In MySQL query it will return 3 columns - id, numbercount and doublednumbercount. It will show the row id, and it will also show a column doublednumbercount which is the total of the current row number + the numbercount variable.

Thanks.

Jono, using teamviewer would be remote debugging. Fir that, you need a support contract.

Now, I've checked your sample and found out that mysql command line client indeed sets user variable value that way, so your claim is legit.
However, writing "select id, @numbercount := 1 AS numbercount, (@numbercount + id) as doublednumbercount FROM reservation_rooms" does not look too intuitive to me. As a workaround, while I test in c/NET, please try following sequence:
SET @numbercount=1;
select id, @numbercount AS numbercount, (@numbercount + id) as
doublednumbercount FROM reservation_rooms"
That should work.

Hi Tonci,

I have tried:

"

myCommand.CommandText = "SET @numbercount=1; select id, @numbercount AS numbercount, (@numbercount + id) as doublednumbercount FROM reservation_rooms;"

"

However same error. The above SQL works in MySQL Query Browser however.

Jono, this problem can not be repeated with latest sources (6.3.x) which I'm obliged to test against.

            //CREATE TABLE `updatetest` (
            //    `Id` bigint(20) NOT NULL AUTO_INCREMENT,
            //    `Version` smallint(5) unsigned NOT NULL,
            //    `UDate` datetime NOT NULL,
            //    PRIMARY KEY (`Id`),
            //    UNIQUE KEY `Id` (`Id`)
            //) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 |
            //+----+---------+---------------------+
            //| Id | Version | UDate               |
            //+----+---------+---------------------+
            //|  1 |       1 | 2009-05-20 09:23:39 |
            //|  2 |       4 | 2010-05-12 13:12:15 |
            //+----+---------+---------------------+

    MySqlConnection conn = new MySqlConnection();
    conn.ConnectionString = "DataSource=**;Database=test;UserID=**;Password=**;PORT=**;Allow Zero Datetime=True;allow user variables = true; respect binary flags=false";
    conn.Open();
    //Case 1, user varibale is properly pre-set
    MySqlCommand cmdCreateTable = new MySqlCommand("SET @numbercount = 1", conn);
    cmdCreateTable.CommandTimeout = 0;
    cmdCreateTable.ExecuteNonQuery();
    cmdCreateTable.CommandText = "SELECT @numbercount AS NrCnt, (@numbercount+1) As NrC_2 FROM updatetest";

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

    DisplayData(dt);
    dt.Clear();
    dt.Dispose();
    da.Dispose();

    //Case 2, user varibale is set in SELECT only
    cmdCreateTable.CommandText = "SELECT Id, @numbercnt := 1 AS NrCn2, (@numbercnt+Version) As NrC_3 FROM updatetest";

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

    DisplayData(dt);
    dt.Clear();
    dt.Dispose();
    da.Dispose();

    conn.Close();

    Console.WriteLine("Done.");

    private static void DisplayData(System.Data.DataTable table)
    {
        foreach (System.Data.DataRow row in table.Rows)
        {
            foreach (System.Data.DataColumn col in table.Columns)
            {
                Console.WriteLine("{0} = {1}", col.ColumnName, row[col].ToString());
            }
            Console.WriteLine("============================");
        }
    }

The data displayed is correct:
NrCnt = 1
NrC_2 = 2
============================
NrCnt = 1
NrC_2 = 2
============================
NrCnt = 
NrC_2 = 
Id = 1
NrCn2 = 1
NrC_3 = 2
============================
NrCnt = 
NrC_2 = 
Id = 2
NrCn2 = 1
NrC_3 = 5
============================
Done.

Tests were done using remote MySQL server 5.1.31 on OpenSolaris x64 box. If still experiencing problems, try upgrading your c/NET.

Verbose output also shows no problems...

mysql Information: 1 : 1: Connection Opened: connection string = 'server=xx;database=test;User Id=xx;password=xx;port=xx;Allow Zero Datetime=True;Allow User Variables=True;Respect Binary Flags=False;logging=True'
mysql Information: 3 : 1: Query Opened: SHOW VARIABLES
mysql Information: 4 : 1: Resultset Opened: field(s) = 2, affected rows = -1, inserted id = -1
mysql Information: 5 : 1: Resultset Closed. Total rows=268, skipped rows=0, size (bytes)=6335
mysql Information: 6 : 1: Query Closed
mysql Information: 3 : 1: Query Opened: SHOW COLLATION
mysql Information: 4 : 1: Resultset Opened: field(s) = 6, affected rows = -1, inserted id = -1
mysql Information: 5 : 1: Resultset Closed. Total rows=127, skipped rows=0, size (bytes)=3958
mysql Information: 6 : 1: Query Closed
mysql Information: 3 : 1: Query Opened: SET NAMES utf8
mysql Information: 4 : 1: Resultset Opened: field(s) = 0, affected rows = 0, inserted id = 0
mysql Information: 5 : 1: Resultset Closed. Total rows=0, skipped rows=0, size (bytes)=0
mysql Information: 6 : 1: Query Closed
mysql Information: 3 : 1: Query Opened: SET character_set_results=NULL
mysql Information: 4 : 1: Resultset Opened: field(s) = 0, affected rows = 0, inserted id = 0
mysql Information: 5 : 1: Resultset Closed. Total rows=0, skipped rows=0, size (bytes)=0
mysql Information: 6 : 1: Query Closed
mysql Information: 10 : 1: Set Database: test
mysql Information: 3 : 1: Query Opened: SET @numbercount = 1
mysql Information: 4 : 1: Resultset Opened: field(s) = 0, affected rows = 0, inserted id = 0
mysql Information: 5 : 1: Resultset Closed. Total rows=0, skipped rows=0, size (bytes)=0
mysql Information: 6 : 1: Query Closed
mysql Information: 3 : 1: Query Opened: SELECT @numbercount AS NrCnt, (@numbercount+1) As NrC_2 FROM updatetest
mysql Information: 4 : 1: Resultset Opened: field(s) = 2, affected rows = -1, inserted id = -1
mysql Information: 5 : 1: Resultset Closed. Total rows=2, skipped rows=0, size (bytes)=8
mysql Information: 6 : 1: Query Closed
NrCnt = 1
NrC_2 = 2
============================
NrCnt = 1
NrC_2 = 2
============================
mysql Information: 3 : 1: Query Opened: SELECT Id, @numcnt := 1 AS NrCn2, (@numcnt+Version) As NrC_3 FROM updatetest
mysql Information: 4 : 1: Resultset Opened: field(s) = 3, affected rows = -1, inserted id = -1
mysql Information: 5 : 1: Resultset Closed. Total rows=2, skipped rows=0, size (bytes)=12
mysql Information: 6 : 1: Query Closed
NrCnt = 
NrC_2 = 
Id = 1
NrCn2 = 1
NrC_3 = 2
============================
NrCnt = 
NrC_2 = 
Id = 2
NrCn2 = 1
NrC_3 = 5
============================
Done.