MySQL Bugs: #44392: Using Variable with insert/select

Bug #44392	Using Variable with insert/select
Submitted:	21 Apr 2009 21:46	Modified:	22 Apr 2009 10:30
Reporter:	Jorge Bastos	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / NET	Severity:	S1 (Critical)
Version:	5.2.5 or any 6.0.x beta	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Hi,
Using a variable with insert to select from in the same statment, doesn't work in 5.2.5 or any or the 6.0x beta series.
the .executenonquery() from mysqlcommand, doesn't even return any exeption, only if i use the 1.x version of the driver it'll work.
I'm going to send a small sample to reproduce the problem.
A few rows in the "a" table are needed so that you can do the test.

How to repeat:
Using this in the console also works very fine (as if using the 1.x driver version). Here's the console output:

mysql> select @var:=0;insert into test.ab select a3,@var:=@var+1 from test.a;
+---------+
| @var:=0 |
+---------+
|       0 |
+---------+
1 row in set (0.00 sec)

Query OK, 493 rows affected (0.00 sec)
Records: 493  Duplicates: 0  Warnings: 0

mysql>

Sample app

Attachment: test with SQL vars.zip (application/force-download, text), 188.46 KiB.

Driver's used to test, renamed the name of them to contein their versions

Attachment: Projects.zip (application/force-download, text), 443.93 KiB.

The server versions were 5.0.77 and 5.1.31 x64, but i think the problem is in the driver only.

Forgot to say, the table's sctructure are inside the sample app.

Hi Jorge and thanks for your report.

Did you use "allow user variables = true"? What happens if you use it? Have you checked general query log from MySQL server? What commands reached MySQL server?

Probably you'll need "allow batch=true" too.

Using latest 6.0 sources from trunk I am unable to repeat this problem:

    Dim strcon As String = "character set=utf8;pooling=false;compress=false;Persist Security Info=False;Connect Timeout=30;Data Source=**;Database=test;User ID=**;Password=**;Port=**;allow user variables = true;allow batch=true"
    Dim sql, sql1, sql2 As String
    Dim mycon As New MySqlConnection(strcon)
    mycon.Open()
    sql = "select @var:=0;insert into test.bug44392b select a3,@var:=@var+1 from test.bug44392a"
    sql1 = "DROP TABLE IF EXISTS bug44392a"
    sql2 = "DROP TABLE IF EXISTS bug44392b"

    Dim mycmd As New MySqlCommand(sql1, mycon)
    mycmd.ExecuteNonQuery()

    mycmd.CommandText = sql2
    mycmd.ExecuteNonQuery()

    sql2 = "CREATE TABLE bug44392a (a int(6) NOT NULL, a3 varchar(111) NOT NULL) ENGINE=MyISAM"
    mycmd.CommandText = sql2
    mycmd.ExecuteNonQuery()
    sql2 = "CREATE TABLE bug44392b (a varchar(100) NOT NULL, b int(5) NOT NULL) ENGINE=MyISAM"
    mycmd.CommandText = sql2
    mycmd.ExecuteNonQuery()

    mycmd.CommandText = sql
    mycmd.ExecuteNonQuery()
    MsgBox("executed")
    mycon.Close()

Please read changelogs especially when moving from such old versions to newest ones.

Ops!
My Fault, yes, "allow user variables = true" did the trick.
I'm sorry, it's true, i didn't read the changelog.
Thanks a lot.

No problem Jorge. Thanks for you interest in MySQL.