Dashboard: error viewing enqueued jobs - "error in your SQL syntax"

[a-h]

Steps to reproduce

• Schedule a few jobs, wait for them to be enqueued
• Access dashboard at: http://localhost:5000/hangfire/jobs/enqueued

Environment

• .NET Core 2.0 (OSX)
• MySQL 5.6 Docker container

    <PackageReference Include="Hangfire.AspNetCore" Version="1.6.17" />
    <PackageReference Include="Hangfire.MySqlStorage" Version="1.1.0-beta" />
    <PackageReference Include="Microsoft.AspNetCore.All" Version="2.0.0" />

Error message

fail: Microsoft.AspNetCore.Server.Kestrel[13]
      Connection id "0HL9C0U6FAMTF", Request id "0HL9C0U6FAMTF:000000AD": An unhandled exception was thrown by the application.
MySql.Data.MySqlClient.MySqlException (0x80004005): 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 'WHERE 1 = 0)' at line 4 ---> MySql.Data.MySqlClient.MySqlException (0x80004005): 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 'WHERE 1 = 0)

[a-h]

If there's nothing enqueued, then there's no error.

[constant-wip]

I have also experienced this one. The error occurs in our MySQL version 5.6 database but when I tried it in MySQL version 5.7 database it works fine. Maybe the reason could be the package and MySQL version compatibility.

[a-h]

Thanks @projectzerotohero, that's likely the source of the problem. I was testing with a local MySQL 5.6 database, since I had intended to use Hangfire with AWS Aurora RDS which is "wire compatible" with MySQL 5.6.

I ended up writing a scheduler to run Lambdas in AWS at various intervals and points in the future instead of using Hangfire.

[V2Blessy]

I'm getting this issue when trying to view Jobs (when it has something enqueued), Retries and Recurring Jobs. Dashboard does not throw any error, but both Realtime graph and History graph show nothing. Jobs, Retries and Recurring Jobs, all show the correct count beside them but gives exception on the actual page itself. I also checked my mysql db and found that all hangfire tables were created correctly and the hangfirejob table also has data (jobs i scheduled). I'm using mysql version: 8.0.12

Observed 2 types of errors:

1. HangfireJob table error during cross join

   • Observed on pages jobs/Scheduled, jobs/processing, jobs/succeeded, jobs/failed, jobs/deleted
   • Also observed on hangfire/jobs/enqueued when there are enqueued jobs. If no enqueued job then this page comes up fine with sub links for Enqueued, Scheduled etc, which again as mentioned above throw below exception: 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 'rank from HangfireJob j cross join (SELECT @rownum := 0) r left joi' at line 2

2. HangfireSet table error

   • Observed on pages jobs/awaiting, hangfire/retries 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 'rank from HangfireSet, (select @rownum := 0) r wh' at line 3

Please let me know if there's a solution to this.

[josematheus10]

I need help I'm having the same errors as mentioned by @V2Blessy

[wadereye]

I get the same error . enviorment: asp.net core 3.1 Hangfire.aspnetcore 1.7.9 Hanfire.mysqlstore 2.0 mysql 8.0

when vsisit the url http://localhost:9519/hangfire/recurring

First I Get the error like : {"code":401,"data":"","message":"Parameter '@rownum' must be defined. To use this as a variable, set 'Allow User Variables=true' in the connection string."}

I Found the solution for that : Add AllowUserVariables=True; to the connectstring

But when I did that ,I found the error like that: {"code":401,"data":"","message":"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 'rank\r\n\t from Hangfire_Set,\r\n (select @rownum := 0) r \r\n w' at line 3"}

[josematheus10]

I get the same error . enviorment: asp.net core 3.1 Hangfire.aspnetcore 1.7.9 Hanfire.mysqlstore 2.0 mysql 8.0

when vsisit the url http://localhost:9519/hangfire/recurring

First I Get the error like : {"code":401,"data":"","message":"Parameter '@rownum' must be defined. To use this as a variable, set 'Allow User Variables=true' in the connection string."}

I Found the solution for that : Add AllowUserVariables=True; to the connectstring

But when I did that ,I found the error like that: {"code":401,"data":"","message":"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 'rank\r\n\t from Hangfire_Set,\r\n (select @rownum := 0) r \r\n w' at line 3"}

MySQL version 8.0 is not supported. Try with MySQL version 5.7 If you want something more modern use redis

[wadereye]

I get the same error . enviorment: asp.net core 3.1 Hangfire.aspnetcore 1.7.9 Hanfire.mysqlstore 2.0 mysql 8.0 when vsisit the url http://localhost:9519/hangfire/recurring First I Get the error like : {"code":401,"data":"","message":"Parameter '@rownum' must be defined. To use this as a variable, set 'Allow User Variables=true' in the connection string."} I Found the solution for that : Add AllowUserVariables=True; to the connectstring But when I did that ,I found the error like that: {"code":401,"data":"","message":"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 'rank\r\n\t from Hangfire_Set,\r\n (select @rownum := 0) r \r\n w' at line 3"}

MySQL version 8.0 is not supported. Try with MySQL version 5.7 If you want something more modern use redis

Thank you very much!