Parameterized query where parameter needs to be modified mySQL

Question

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?

Answer 1

Use distinct names for Dapper parameters and MySQL user variables

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.

Set Allow User Variables=True in MySQL Connection String

Include 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.

Further Reading

• Connector/Net Connection String Options Reference