| | | 1 | | using System.Globalization; |
| | | 2 | | using System.Text.RegularExpressions; |
| | | 3 | | using MySqlConnector; |
| | | 4 | | |
| | | 5 | | namespace Services.HangfireService; |
| | | 6 | | |
| | | 7 | | /// <summary> |
| | | 8 | | /// Доступ вотчдога к внутренним таблицам Hangfire (БД ST2_Hangfire) и к XA-состоянию сервера. |
| | | 9 | | /// Работает под тем же пользователем, что и приложение: <c>XA RECOVER</c>/<c>XA ROLLBACK</c> |
| | | 10 | | /// привилегии PROCESS не требуют (в отличие от information_schema.INNODB_TRX). |
| | | 11 | | /// </summary> |
| | 0 | 12 | | public sealed class HangfireSchedulerWatchdogStorage(HangfireStorageConnection hangfireStorage) |
| | | 13 | | { |
| | | 14 | | // XA RECOVER FORMAT='SQL' отдаёт data как X'..',X'..' — принимаем только такую форму, |
| | | 15 | | // потому что XA-выражения не параметризуются и текст уходит в запрос как есть. |
| | 0 | 16 | | private static readonly Regex SqlXidPattern = new("^X'[0-9A-Fa-f]*',X'[0-9A-Fa-f]*',\\d+$", RegexOptions.Compiled); |
| | | 17 | | |
| | | 18 | | public async Task<HangfireWatchdogObservation> ObserveAsync( |
| | | 19 | | DateTime nowUtc, |
| | | 20 | | TimeSpan statelessJobAge, |
| | | 21 | | CancellationToken cancellationToken) |
| | | 22 | | { |
| | 0 | 23 | | await using var connection = new MySqlConnection(hangfireStorage.ConnectionString); |
| | 0 | 24 | | await connection.OpenAsync(cancellationToken); |
| | | 25 | | |
| | 0 | 26 | | return new HangfireWatchdogObservation( |
| | 0 | 27 | | nowUtc, |
| | 0 | 28 | | await ReadRecurringJobsAsync(connection, cancellationToken), |
| | 0 | 29 | | await ReadServerHeartbeatsAsync(connection, cancellationToken), |
| | 0 | 30 | | await ReadPreparedXaAsync(connection, cancellationToken), |
| | 0 | 31 | | await CountStatelessJobsAsync(connection, nowUtc - statelessJobAge, cancellationToken)); |
| | 0 | 32 | | } |
| | | 33 | | |
| | | 34 | | /// <summary>Откатывает осиротевшую PREPARED-транзакцию; возвращает текст ошибки сервера, если не удалось.</summary> |
| | | 35 | | public async Task<string?> RollbackXaAsync(PreparedXaTransaction transaction, CancellationToken cancellationToken) |
| | | 36 | | { |
| | 0 | 37 | | if (!SqlXidPattern.IsMatch(transaction.Xid)) |
| | | 38 | | { |
| | 0 | 39 | | return $"XID в неожиданном формате: {transaction.Xid}"; |
| | | 40 | | } |
| | | 41 | | |
| | | 42 | | try |
| | | 43 | | { |
| | 0 | 44 | | await using var connection = new MySqlConnection(hangfireStorage.ConnectionString); |
| | 0 | 45 | | await connection.OpenAsync(cancellationToken); |
| | 0 | 46 | | await using var cmd = connection.CreateCommand(); |
| | 0 | 47 | | cmd.CommandText = $"XA ROLLBACK {transaction.Xid}"; |
| | 0 | 48 | | await cmd.ExecuteNonQueryAsync(cancellationToken); |
| | 0 | 49 | | return null; |
| | 0 | 50 | | } |
| | | 51 | | catch (MySqlException ex) |
| | | 52 | | { |
| | 0 | 53 | | return ex.Message; |
| | | 54 | | } |
| | 0 | 55 | | } |
| | | 56 | | |
| | | 57 | | /// <summary> |
| | | 58 | | /// Удаляет джобы, у которых планировщик создал запись, но не смог зафиксировать состояние |
| | | 59 | | /// (StateName IS NULL). Hangfire их не видит и не чистит, а при заблокированном планировщике |
| | | 60 | | /// они прибывают по одной в минуту. |
| | | 61 | | /// </summary> |
| | | 62 | | public async Task<int> DeleteStatelessJobsAsync(DateTime olderThanUtc, CancellationToken cancellationToken) |
| | | 63 | | { |
| | 0 | 64 | | await using var connection = new MySqlConnection(hangfireStorage.ConnectionString); |
| | 0 | 65 | | await connection.OpenAsync(cancellationToken); |
| | | 66 | | |
| | 0 | 67 | | await ExecuteAsync(connection, |
| | 0 | 68 | | "DELETE p FROM HangfireJobParameter p INNER JOIN HangfireJob j ON j.Id = p.JobId WHERE j.StateName IS NULL A |
| | 0 | 69 | | olderThanUtc, cancellationToken); |
| | 0 | 70 | | await ExecuteAsync(connection, |
| | 0 | 71 | | "DELETE s FROM HangfireState s INNER JOIN HangfireJob j ON j.Id = s.JobId WHERE j.StateName IS NULL AND j.Cr |
| | 0 | 72 | | olderThanUtc, cancellationToken); |
| | 0 | 73 | | return await ExecuteAsync(connection, |
| | 0 | 74 | | "DELETE FROM HangfireJob WHERE StateName IS NULL AND CreatedAt < @before", |
| | 0 | 75 | | olderThanUtc, cancellationToken); |
| | 0 | 76 | | } |
| | | 77 | | |
| | | 78 | | private static async Task<IReadOnlyList<RecurringJobSnapshot>> ReadRecurringJobsAsync( |
| | | 79 | | MySqlConnection connection, CancellationToken cancellationToken) |
| | | 80 | | { |
| | 0 | 81 | | await using var cmd = connection.CreateCommand(); |
| | 0 | 82 | | cmd.CommandText = """ |
| | 0 | 83 | | SELECT `Key`, Field, Value |
| | 0 | 84 | | FROM HangfireHash |
| | 0 | 85 | | WHERE `Key` LIKE 'recurring-job:%' |
| | 0 | 86 | | AND Field IN ('Cron', 'NextExecution', 'LastExecution') |
| | 0 | 87 | | """; |
| | | 88 | | |
| | 0 | 89 | | var rows = new Dictionary<string, (string? Cron, DateTime? Next, DateTime? Last)>(StringComparer.Ordinal); |
| | 0 | 90 | | await using var reader = await cmd.ExecuteReaderAsync(cancellationToken); |
| | 0 | 91 | | while (await reader.ReadAsync(cancellationToken)) |
| | | 92 | | { |
| | 0 | 93 | | var id = reader.GetString(0)["recurring-job:".Length..]; |
| | 0 | 94 | | var field = reader.GetString(1); |
| | 0 | 95 | | var value = reader.IsDBNull(2) ? null : reader.GetString(2); |
| | 0 | 96 | | rows.TryGetValue(id, out var acc); |
| | 0 | 97 | | acc = field switch |
| | 0 | 98 | | { |
| | 0 | 99 | | "Cron" => (value, acc.Next, acc.Last), |
| | 0 | 100 | | "NextExecution" => (acc.Cron, ParseUtc(value), acc.Last), |
| | 0 | 101 | | "LastExecution" => (acc.Cron, acc.Next, ParseUtc(value)), |
| | 0 | 102 | | _ => acc |
| | 0 | 103 | | }; |
| | 0 | 104 | | rows[id] = acc; |
| | | 105 | | } |
| | | 106 | | |
| | 0 | 107 | | return rows.Select(r => new RecurringJobSnapshot(r.Key, r.Value.Cron, r.Value.Next, r.Value.Last)).ToList(); |
| | 0 | 108 | | } |
| | | 109 | | |
| | | 110 | | private static async Task<IReadOnlyList<DateTime>> ReadServerHeartbeatsAsync( |
| | | 111 | | MySqlConnection connection, CancellationToken cancellationToken) |
| | | 112 | | { |
| | 0 | 113 | | await using var cmd = connection.CreateCommand(); |
| | 0 | 114 | | cmd.CommandText = "SELECT LastHeartbeat FROM HangfireServer WHERE LastHeartbeat IS NOT NULL"; |
| | 0 | 115 | | var result = new List<DateTime>(); |
| | 0 | 116 | | await using var reader = await cmd.ExecuteReaderAsync(cancellationToken); |
| | 0 | 117 | | while (await reader.ReadAsync(cancellationToken)) |
| | | 118 | | { |
| | | 119 | | // Hangfire пишет UTC_TIMESTAMP без признака зоны. |
| | 0 | 120 | | result.Add(DateTime.SpecifyKind(reader.GetDateTime(0), DateTimeKind.Utc)); |
| | | 121 | | } |
| | | 122 | | |
| | 0 | 123 | | return result; |
| | 0 | 124 | | } |
| | | 125 | | |
| | | 126 | | private static async Task<IReadOnlyList<PreparedXaTransaction>> ReadPreparedXaAsync( |
| | | 127 | | MySqlConnection connection, CancellationToken cancellationToken) |
| | | 128 | | { |
| | 0 | 129 | | await using var cmd = connection.CreateCommand(); |
| | 0 | 130 | | cmd.CommandText = "XA RECOVER FORMAT='SQL'"; |
| | 0 | 131 | | var result = new List<PreparedXaTransaction>(); |
| | 0 | 132 | | await using var reader = await cmd.ExecuteReaderAsync(cancellationToken); |
| | 0 | 133 | | while (await reader.ReadAsync(cancellationToken)) |
| | | 134 | | { |
| | 0 | 135 | | var formatId = Convert.ToInt64(reader["formatID"], CultureInfo.InvariantCulture); |
| | 0 | 136 | | var data = Convert.ToString(reader["data"], CultureInfo.InvariantCulture) ?? string.Empty; |
| | 0 | 137 | | result.Add(new PreparedXaTransaction($"{data},{formatId}")); |
| | | 138 | | } |
| | | 139 | | |
| | 0 | 140 | | return result; |
| | 0 | 141 | | } |
| | | 142 | | |
| | | 143 | | private static async Task<int> CountStatelessJobsAsync( |
| | | 144 | | MySqlConnection connection, DateTime olderThanUtc, CancellationToken cancellationToken) |
| | | 145 | | { |
| | 0 | 146 | | await using var cmd = connection.CreateCommand(); |
| | 0 | 147 | | cmd.CommandText = "SELECT COUNT(*) FROM HangfireJob WHERE StateName IS NULL AND CreatedAt < @before"; |
| | 0 | 148 | | cmd.Parameters.AddWithValue("@before", olderThanUtc); |
| | 0 | 149 | | return Convert.ToInt32(await cmd.ExecuteScalarAsync(cancellationToken), CultureInfo.InvariantCulture); |
| | 0 | 150 | | } |
| | | 151 | | |
| | | 152 | | private static async Task<int> ExecuteAsync( |
| | | 153 | | MySqlConnection connection, string sql, DateTime before, CancellationToken cancellationToken) |
| | | 154 | | { |
| | 0 | 155 | | await using var cmd = connection.CreateCommand(); |
| | 0 | 156 | | cmd.CommandText = sql; |
| | 0 | 157 | | cmd.Parameters.AddWithValue("@before", before); |
| | 0 | 158 | | return await cmd.ExecuteNonQueryAsync(cancellationToken); |
| | 0 | 159 | | } |
| | | 160 | | |
| | | 161 | | private static DateTime? ParseUtc(string? value) => |
| | 0 | 162 | | DateTime.TryParse(value, CultureInfo.InvariantCulture, |
| | 0 | 163 | | DateTimeStyles.AdjustToUniversal | DateTimeStyles.AssumeUniversal, out var parsed) |
| | 0 | 164 | | ? parsed |
| | 0 | 165 | | : null; |
| | | 166 | | } |