weixin_39604685
weixin_39604685
2020-12-09 06:51

ServiceHeartbeat table causes three exceptions on every launch

I have been using the forum for over a month now, and everytime I redeploy the app, or the iis app pool starts an new instance three exceptions are thrown when updating the heartbeat services table.

A week or so ago I asked StackOverflow for assistance. They could not reproduce the issue that I am having with the forum.

The stackoverflow question describes the issue but I can assist further if you like.

To reproduce the issue,

  1. Pull down master
  2. Run the forum for the first time and set it up.
  3. Check the error log to see the following error

Error: 11/07/2017 12:50:20 - SqlException: Violation of PRIMARY KEY constraint 'PK_pf_ServiceHeartbeat'. Cannot insert duplicate key in object 'dbo.pf_ServiceHeartbeat'. The duplicate key value is (PopForums.Email.EmailApplicationService, MACHINENAME).
The statement has been terminated.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite, String methodName)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at PopForums.Data.Sql.Repositories.ServiceHeartbeatRepository.<>c__DisplayClass3_0.b__0(DbConnection connection) in E:\Projects\Forum\src\PopForums.Sql\Repositories\ServiceHeartbeatRepository.cs:line 40
at PopForums.Data.Sql.Extensions.Using(DbConnection connection, Action`1 action) in E:\Projects\Forum\src\PopForums.Sql\Extensions.cs:line 53
at PopForums.Data.Sql.Repositories.ServiceHeartbeatRepository.RecordHeartbeat1(String serviceName, String machineName, DateTime lastRun) in E:\Projects\Forum\src\PopForums.Sql\Repositories\ServiceHeartbeatRepository.cs:line 32
at PopForums.Services.ServiceHeartbeatService.Add(String serviceName, String machineName) in E:\Projects\Forum\src\PopForums\Services\ServiceHeartbeatService.cs:line 37
at PopForums.Services.ServiceHeartbeatService.RecordHeartbeat(String serviceName, String machineName) in E:\Projects\Forum\src\PopForums\Services\ServiceHeartbeatService.cs:line 25
at PopForums.Services.ApplicationServiceBase.Execute(Object sender) in E:\Projects\Forum\src\PopForums\Services\ApplicationServiceBase.cs:line 50

HelpLink.ProdName: Microsoft SQL Server
HelpLink.ProdVer: 13.00.4422
HelpLink.EvtSrc: MSSQLServer
HelpLink.EvtID: 2627
HelpLink.BaseHelpUrl: http://go.microsoft.com/fwlink
HelpLink.LinkId: 20476

During debugging I also noticed running the PopForums.sql script returns the following warning which sounded like it could be related


Warning! The maximum key length for a clustered index is 900 bytes. The index 'PK_pf_ServiceHeartbeat'
 has maximum length of 1024 bytes. For some combination of large values, the insert/update
  operation will fail.

Having updated the script to reduce the clustered key size this solved the warning message but not the error. (Although I do think it is work fixing too) See here

I have managed to solve the error however it is just using SqlConnection See below


public void RecordHeartbeat(string serviceName, string machineName, DateTime lastRun)
{
    using (SqlConnection conn = (SqlConnection)_sqlObjectFactory.GetConnection())
    {
        conn.Open();
        using (var tran = conn.BeginTransaction())
        {
            using (var comm = new SqlCommand("DELETE FROM pf_ServiceHeartbeat WHERE ServiceName =  AND MachineName = ", conn, tran))
            {
                comm.Parameters.AddWithValue("", serviceName);
                comm.Parameters.AddWithValue("", machineName);
                comm.ExecuteNonQuery();
                comm.CommandText = "INSERT INTO pf_ServiceHeartbeat (ServiceName, MachineName, LastRun) VALUES (, , )";
                comm.Parameters.AddWithValue("", lastRun);
                comm.ExecuteNonQuery();
            }
            tran.Commit();
        }
    }
}

该提问来源于开源项目:POPWorldMedia/POPForums

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

7条回答

  • weixin_39604685 weixin_39604685 5月前

    you may find this issue informative if your starting to look at PopForums on master.

    I would suggest using the following work around until looks into the issue

    
        public void RecordHeartbeat(string serviceName, string machineName, DateTime lastRun)
        {
          // HACK: I am using vanilla sql connections rather than the inbuilt POPForums connection
          // due to an issue with database primary key execptions on start up. See more here:
          // https://github.com/POPWorldMedia/POPForums/issues/56
          using (SqlConnection conn = (SqlConnection)_sqlObjectFactory.GetConnection())
          {
            conn.Open();
            using (var tran = conn.BeginTransaction())
            {
              using (var comm = new SqlCommand("DELETE FROM pf_ServiceHeartbeat WHERE ServiceName =  AND MachineName = ", conn, tran))
              {
                comm.Parameters.AddWithValue("", serviceName);
                comm.Parameters.AddWithValue("", machineName);
                comm.ExecuteNonQuery();
                comm.CommandText = "INSERT INTO pf_ServiceHeartbeat (ServiceName, MachineName, LastRun) VALUES (, , )";
                comm.Parameters.AddWithValue("", lastRun);
                comm.ExecuteNonQuery();
              }
              tran.Commit();
            }
          }
        }
    
    点赞 评论 复制链接分享
  • weixin_39980234 weixin_39980234 5月前

    I already committed a change to the table.

    点赞 评论 复制链接分享
  • weixin_39604685 weixin_39604685 5月前

    This issue has not been fixed. I have just pulled down master changed the column size to 75 to avoid encountering #61 and there are still heart beat exceptions been thrown.

    I think we need to reopen this issue

    点赞 评论 复制链接分享
  • weixin_39604685 weixin_39604685 5月前

    this issue has still not been fixes see above comment

    点赞 评论 复制链接分享
  • weixin_39980234 weixin_39980234 5月前

    You need to update the schema. It won't fix itself if your db has already been created.

    点赞 评论 复制链接分享
  • weixin_39604685 weixin_39604685 5月前

    When checking if this issue has been fixed I deleted my previous test database. Hence it would pick up the new schema. The issue was not fixed.

    点赞 评论 复制链接分享
  • weixin_39980234 weixin_39980234 5月前

    You'll have to give me more than "it's not fixed" because it works for me in three different places.

    点赞 评论 复制链接分享

相关推荐