hangfire.mysql.core_asp.net core 3.1+mysql8.0+Hangfire遇到的异常解决记

Hangfire大家都很熟了，在.net 下用处很多，最近在asp.net core 3.1 使用hangfire的过程中,遇到很多问题，现在记录下来，分享给下一位。

1.现在的开发环境：

asp.net core 3.1

hangfire.aspnetcore 1.7.9

mysql 8.0.12

hangfire.mysql.core 2.2.5

2.遇到的第一个异常解决：

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

这个我查了一些资料，解决办法是在连接字符串添加AllowUserVariables=True;得以解决

你的连接字符串看起来应该像这样：

Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;

AllowUserVariables=True;

网上搜索到的，有提示变量为Allow User Variables=true(注意中间有空格)，经我实践是不行的，需要使用AllowUserVariables=True;(中间没有空格) 这样的方式。

3.遇到下面的类似的异常：

{"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"}

经查，原来我们是引用：Hangfire.MySqlStorage

这个库来实现mysql版本下的hangfire数据持久化。这个库现在已经不实用了。

建议换成：

这个库

4.换成以上库以后，使用方式如下：

services.AddHangfire(x => x.UseStorage(new MySqlStorage(Configuration.GetConnectionString("Hangfire"),new MySqlStorageOptions(){TablePrefix = "Custom"})));

支持表前缀为custom,不需要添加下划线，表会自动创建custom_的表。

5.以下异常：

MySqlException: Table ' custom__distributedlock' doesn't exist

要注意，表脚本要跟着修改一下：

从原来12张表新增了一张表，变为13张表，

增加了表：DistributedLock

github上有脚本，为了方便大家，我把脚本贴一下，直接用这个修改就可以了：

-- ----------------------------

-- Table structure for `Job`

-- ----------------------------

CREATE TABLE `_Job` (

`Id` int(11) NOT NULL AUTO_INCREMENT,

`StateId` int(11) DEFAULT NULL,

`StateName` varchar(20) DEFAULT NULL,

`InvocationData` longtext NOT NULL,

`Arguments` longtext NOT NULL,

`CreatedAt` datetime NOT NULL,

`ExpireAt` datetime DEFAULT NULL,

PRIMARY KEY (`Id`),

KEY `IX_Job_StateName` (`StateName`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------

-- Table structure for `Counter`

-- ----------------------------

CREATE TABLE `_Counter` (

`Id` int(11) NOT NULL AUTO_INCREMENT,

`Key` varchar(100) NOT NULL,

`Value` int(11) NOT NULL,

`ExpireAt` datetime DEFAULT NULL,

PRIMARY KEY (`Id`),

KEY `IX_Counter_Key` (`Key`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `_AggregatedCounter` (

Id int(11) NOT NULL AUTO_INCREMENT,

`Key` varchar(100) NOT NULL,

`Value` int(11) NOT NULL,

ExpireAt datetime DEFAULT NULL,

PRIMARY KEY (`Id`),

UNIQUE KEY `IX_CounterAggregated_Key` (`Key`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------

-- Table structure for `DistributedLock`

-- ----------------------------

CREATE TABLE `_DistributedLock` (

`Resource` varchar(100) NOT NULL,

`CreatedAt` datetime NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------

-- Table structure for `Hash`

-- ----------------------------

CREATE TABLE `_Hash` (

`Id` int(11) NOT NULL AUTO_INCREMENT,

`Key` varchar(100) NOT NULL,

`Field` varchar(40) NOT NULL,

`Value` longtext,

`ExpireAt` datetime DEFAULT NULL,

PRIMARY KEY (`Id`),

UNIQUE KEY `IX_Hash_Key_Field` (`Key`,`Field`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------

-- Table structure for `JobParameter`

-- ----------------------------

CREATE TABLE `_JobParameter` (

`Id` int(11) NOT NULL AUTO_INCREMENT,

`JobId` int(11) NOT NULL,

`Name` varchar(40) NOT NULL,

`Value` longtext,

PRIMARY KEY (`Id`),

CONSTRAINT `IX_JobParameter_JobId_Name` UNIQUE (`JobId`,`Name`),

KEY `FK_JobParameter_Job` (`JobId`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------

-- Table structure for `JobQueue`

-- ----------------------------

CREATE TABLE `_JobQueue` (

`Id` int(11) NOT NULL AUTO_INCREMENT,

`JobId` int(11) NOT NULL,

`Queue` varchar(50) NOT NULL,

`FetchedAt` datetime DEFAULT NULL,

`FetchToken` varchar(36) DEFAULT NULL,

PRIMARY KEY (`Id`),

INDEX `IX_JobQueue_QueueAndFetchedAt` (`Queue`,`FetchedAt`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------

-- Table structure for `JobState`

-- ----------------------------

CREATE TABLE `_JobState` (

`Id` int(11) NOT NULL AUTO_INCREMENT,

`JobId` int(11) NOT NULL,

`Name` varchar(20) NOT NULL,

`Reason` varchar(100) DEFAULT NULL,

`CreatedAt` datetime NOT NULL,

`Data` longtext,

PRIMARY KEY (`Id`),

KEY `FK_JobState_Job` (`JobId`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------

-- Table structure for `Server`

-- ----------------------------

CREATE TABLE `_Server` (

`Id` varchar(100) NOT NULL,

`Data` longtext NOT NULL,

`LastHeartbeat` datetime DEFAULT NULL,

PRIMARY KEY (`Id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------

-- Table structure for `Set`

-- ----------------------------

CREATE TABLE `_Set` (

`Id` int(11) NOT NULL AUTO_INCREMENT,

`Key` varchar(100) NOT NULL,

`Value` varchar(256) NOT NULL,

`Score` float NOT NULL,

`ExpireAt` datetime DEFAULT NULL,

PRIMARY KEY (`Id`),

UNIQUE KEY `IX_Set_Key_Value` (`Key`,`Value`)

) ENGINE=InnoDB CHARSET=utf8mb4;

CREATE TABLE `_State`

(

Id int(11) NOT NULL AUTO_INCREMENT,

JobId int(11) NOT NULL,

Name varchar(20) NOT NULL,

Reason varchar(100) NULL,

CreatedAt datetime NOT NULL,

Data longtext NULL,

PRIMARY KEY (`Id`),

KEY `FK_HangFire_State_Job` (`JobId`)

) ENGINE=InnoDB CHARSET=utf8mb4;

CREATE TABLE `_List`

(

`Id` int(11) NOT NULL AUTO_INCREMENT,

`Key` varchar(100) NOT NULL,

`Value` longtext NULL,

`ExpireAt` datetime NULL,

PRIMARY KEY (`Id`)

) ENGINE=InnoDB CHARSET=utf8mb4;