I'm attempting to build bread crumbs from a self referencing table. The query works perfectly fine in MySQL Workbench, but when run in application, the query fails with
Unhandled Exception: MySql.Data.MySqlClient.MySqlException: 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 ':= Parent
FROM
Codebook
WHERE
id = _id
) as ParentId,
' at line 6
I have looked for the right syntax for my server version (Percona Server (GPL), Release rel30.2, Revision 38.2). I'm hosting the DB off of GoDaddy. The query is as follows:
var query = @"SELECT T2.* FROM (
SELECT
@r as _id,
(
SELECT
@r := Parent
FROM
Codebook
WHERE
id = _id
) as ParentId,
@l := @l + 1 as lvl
FROM
Codebook
WHERE
@r <> 0
) T1
JOIN Codebook T2
ON T1._id = T2.Id
ORDER BY T1.lvl DESC";
return Query(query, new
{
r = childId,
l = 0
});
Where Query(query
is a wrapper method for dapper that uses a connection string stored in the appsettings of the website.
What am I doing wrong? Is there anyway I can be doing it better?
You're using the the same syntax for your dapper parameters @l
and @r
and your MySQL user variables @l
and @r
. Dapper is likely resolving the variables in every case, causing nonsensical statements like...
0 := 0 + 1 as lvl
for @l
childId := Parent
for @r
...and thus the MySQLException.
To fix this, simply rename your MySQL user variable to some value that doesn't match either of your Dapper parameters in this query.
I was able to reproduce this MySQLException in my local environment, and verify that changing the user variable resolves the issue.
To reproduce the MySQLException: name both @l
.
private int givenSameNames_ExpectMySQLException(IDbConnection conn)
{
return conn.Query<int>("SELECT @l := @l + 1", new
{
l = 1
}).FirstOrDefault();
}
To generate a good result: simply rename @l
to something like @test
.
private int givenDifferentNames_ExpectSuccess(IDbConnection conn)
{
return conn.Query<int>("SELECT @test := @l + 1", new
{
l = 1
}).FirstOrDefault();
}
Do this for both @l
and @r
-- the dapper parameters and the user variables need distinct names.
Allow User Variables=True
in MySQL Connection StringInclude Allow User Variables=True
in the MySQL Connection string you're using with the MySQL connector. After resolving the naming issues, check this if you see an error noting that the MySQL user variable needs to be defined. This is false by default and user variables will not work in your query unless this is true.