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数据持久化。这个库现在已经不实用了。

建议换成：

Hangfire.MySql.Core

这个库

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 `<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;