Bug #67613 ExecuteReader throws NullReferenceException when using variables in queries
Submitted: 16 Nov 2012 14:54 Modified: 20 Dec 2012 16:28
Reporter: Dieter Bernauer-Schilcher Email Updates:
Status: No Feedback Impact on me:
Category:Connector / NET Severity:S2 (Serious)
Version:6.6.4 OS:Windows
Assigned to: CPU Architecture:Any

[16 Nov 2012 14:54] Dieter Bernauer-Schilcher
using query's like this to get rownumbers:
SELECT @rownum:=@rownum+1  as ROWID,ivr.pid  as Project ,SUM(ivr.duration)/60 as Minutes,SUM(ivr.calls) as Calls FROM cdr_dailysum as ivr   left join project p  on ivr.pid=p.pid left join Users u on p.userID=u.ID ,
(SELECT @rownum:=0) r WHERE ((ivr.DATE>='2012-11-01'  AND ivr.DATE<='2012-11-30'  )) GROUP BY ivr.pid  ORDER BY ivr.pid

and passing this query through MysqlCommand.ExecuteReader(CommandBehavior.CloseConnection)  results in:
Fatal error encountered during command execution.
InnerException {"Parameter '@rownum' must be defined."} System.Exception {MySql.Data.MySqlClient.MySqlException}.

Query can be Executed in querybrowser without problems returning proper results

Mysql version is 5.5.28-1.
Behaves like BUG #58652 that is "fixed" since 2010. LOL.

How to repeat:

Do simple query like this:
string query="SELECT @rownum:=@rownum+1  as ROWID, ID from table ,(SELECT @rownum:=0) r";
            MySqlCommand cmd = new MySqlCommand(query);
            cmd.CommandType = CommandType.Text;
            cmd.CommandTimeout = 60;

            cmd.Connection = MyConnectionString;

            IDataReader rdr;

                rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            catch(MySqlException x)
[20 Nov 2012 16:28] Fernando Gonzalez.Sanchez
Hi, you need to add "Allow User Variables=true" to your connection string
(so the @vars not get interpreted like parameters).
[21 Dec 2012 1:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".