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:
Category:Connector / NET Severity:S1 (Critical)
Version:5.2.5 or any 6.0.x beta OS:Any
Assigned to: CPU Architecture:Any

[21 Apr 2009 21:46] Jorge Bastos
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

[21 Apr 2009 21:49] Jorge Bastos
Sample app

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

[21 Apr 2009 21:50] Jorge Bastos
Driver's used to test, renamed the name of them to contein their versions

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

[21 Apr 2009 21:57] Jorge Bastos
The server versions were 5.0.77 and 5.1.31 x64, but i think the problem is in the driver only.
[21 Apr 2009 22:00] Jorge Bastos
Forgot to say, the table's sctructure are inside the sample app.
[22 Apr 2009 6:01] Tonci Grgin
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?
[22 Apr 2009 6:04] Tonci Grgin
Probably you'll need "allow batch=true" too.
[22 Apr 2009 6:11] Tonci Grgin
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)
    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.CommandText = sql2

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

    mycmd.CommandText = sql

Please read changelogs especially when moving from such old versions to newest ones.
[22 Apr 2009 10:30] Jorge Bastos
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.
[22 Apr 2009 12:03] Tonci Grgin
No problem Jorge. Thanks for you interest in MySQL.