Hangfire大家都很熟了兴猩，在.net 下用處很多缀蹄，最近在asp.net core 3.1 使用hangfire的過程中,遇到很多問題，現(xiàn)在記錄下來蛀醉，分享給下一位衅码。
1.現(xiàn)在的開發(fā)環(huán)境：
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;得以解決
你的連接字符串看起來應(yīng)該像這樣：
Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;
AllowUserVariables=True;
網(wǎng)上搜索到的逝段，有提示變量為Allow User Variables=true（注意中間有空格），經(jīng)我實踐是不行的奶躯，需要使用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"}
經(jīng)查账嚎，原來我們是引用：Hangfire.MySqlStorage
這個庫來實現(xiàn)mysql版本下的hangfire數(shù)據(jù)持久化儡蔓。這個庫現(xiàn)在已經(jīng)不實用了。
建議換成：
Hangfire.MySql.Core
這個庫
4.換成以上庫以后喂江，使用方式如下：
services.AddHangfire(x => x.UseStorage(new MySqlStorage(Configuration.GetConnectionString("Hangfire"),new MySqlStorageOptions(){TablePrefix = "Custom"})));
支持表前綴為custom,不需要添加下劃線，表會自動創(chuàng)建custom_的表涨岁。
5.以下異常：
MySqlException: Table ' custom__distributedlock' doesn't exist
要注意筐付，表腳本要跟著修改一下：
從原來12張表新增了一張表卵惦，變?yōu)?3張表瓦戚，
增加了表：DistributedLock
github上有腳本丛塌，為了方便大家畜疾，我把腳本貼一下印衔，直接用這個修改就可以了：
-- ----------------------------
-- Table structure for `Job`
-- ----------------------------
CREATE TABLE `<tableprefix>_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 `<tableprefix>_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 `<tableprefix>_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 `<tableprefix>_DistributedLock` (
`Resource` varchar(100) NOT NULL,
`CreatedAt` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- ----------------------------
-- Table structure for `Hash`
-- ----------------------------
CREATE TABLE `<tableprefix>_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 `<tableprefix>_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 `<tableprefix>_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 `<tableprefix>_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 `<tableprefix>_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 `<tableprefix>_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 `<tableprefix>_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 `<tableprefix>_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;
