0

I have a query that works fine in MySql workbench, but is producing a syntax error "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':= 0+1 AS Rank, z.* 
FROM(SELECT MemberId, Valu' at line 2" when run via MySqlCommand.ExecuteReader. The syntax in question is a variable assignment inside of a select (i.e. SELECT @r := @r + 1). I am using .net connector v.6.9.9 in case that's relevant. Also, here's my connection string: "server=mymysqlserveraddress;port=3306;database=dbName;user id=username;password=********;Convert Zero Datetime=True;Allow User Variables=true"

I have tried setting Allow User Variables=true in the connection string as shown here: Is it possible to use a MySql User Defined Variable in a .NET MySqlCommand?

I looked for other questions that matched this but ultimately, only found one that is the same problem, but for hibernate in java: How can I use MySQL assign operator(:=) in hibernate native query?

Here is the raw sql query that works in MySql Workbench:

SELECT Rank, Value 
FROM (SELECT @r := @r+1 AS Rank, z.* 
      FROM(SELECT MemberId, Value 
           FROM (SELECT Id, MemberId, Value, CreatedDate, EventId 
                 FROM Scores
                 WHERE (LoadTime IS NULL AND EventId = 408)
                 ORDER BY Value DESC
                ) AS sub 
     )z, 
     (SELECT @r:=0)y
)x 
WHERE x.Value<=228000;

The 408 and 228000 are just examples - my C# code constructs the string and then sends it to the server via:

StringBuilder statisticQueryString = new StringBuilder("");
// construct statisticQueryString
using (MySqlCommand cmd = new MySqlCommand(statisticQueryString.ToString(), conn))
{
    cmd.Parameters.AddWithValue("r", 0);

    using (MySqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.SequentialAccess))
    {
        // Iterate through the collection of RankResult items...
    }
}

A sample constructed query (I am printing the final query to the log before sending) that fails with the syntax error above is:

SELECT Rank, Value 
FROM (SELECT @r := @r+1 AS Rank, z.* 
      FROM(SELECT MemberId, Value 
           FROM (SELECT Id, MemberId, Value, CreatedDate, EventId 
                 FROM Scores
                 WHERE (LoadTime IS NULL AND EventId = 408)
                 ORDER BY Value DESC
                ) AS sub 
     )z, 
     (SELECT @r:=0)y
)x 
WHERE x.Value<=228000;

As you can see, the constructed query is identical to the one I can run without errors in MySql workbench.

az2tonez
  • 86
  • 7
  • 2
    I've never tried using session variables and named parameters together in the same query; if it is even supported, which it looks like it is from skimming the topics you referenced, I would assume you would not want to set the session variable's value as though it were a parameter. MySQL is probably seeing `0 := 0 + 1` – Uueerdo May 21 '19 at 20:24
  • I interpreted your comment as the variable was declared in 2 places, so I removed the C# declaration and that fixed it. Thank you! – az2tonez May 21 '19 at 20:42
  • 2
    Yeah, that was what I meant. Parameters get replaced before the query is executed, but you'd want the identifier for the session variable to be preserved. it's unfortunate that MySQL's session variables happen to share an ambiguous format with them. – Uueerdo May 21 '19 at 21:11
  • Got it. Thanks for the additional explanation, I have a better understanding of how parameter replacement works now. – az2tonez May 21 '19 at 21:13
  • For what it's worth to anyone else who runs across this issue, adding `Allow User Variables=true` was also necessary to get this working - without it, an exception is thrown with the message `"Parameter '@r' must be defined."` – az2tonez May 21 '19 at 21:42

1 Answers1

0

The comment by @Uueerdo prompted me to try removing the cmd.Parameters.AddWithValue("r", 0); line from the C# code. This fixed the error and gave me the results I was expecting.

az2tonez
  • 86
  • 7