内规同步流程分析与排查方案

-- 1. 查看同步记录表最新记录时间
SELECT MAX(create_time) AS last_sync_time,
       COUNT(*) AS total_records
FROM hg_law_regulation_sync_record
WHERE deleted = 0;

-- 2. 查看最近30天同步记录分布
SELECT DATE(create_time) AS sync_date,
       COUNT(*) AS record_count,
       SUM(CASE WHEN result_code = 'S' THEN 1 ELSE 0 END) AS success_count,
       SUM(CASE WHEN result_code = 'E' THEN 1 ELSE 0 END) AS error_count
FROM hg_law_regulation_sync_record
WHERE deleted = 0
  AND create_time >= DATE_SUB(NOW(), INTERVAL 30 DAY)
GROUP BY DATE(create_time)
ORDER BY sync_date DESC;

-- 3. 查看当前流水号
SELECT * FROM reg_increment WHERE id = 1;

-- 4. 查看最近的错误记录
SELECT id, trace_id, regs_id, regs_name,
       result_code, error_msg, resp_code,
       sync_status, status, create_time
FROM hg_law_regulation_sync_record
WHERE deleted = 0
ORDER BY create_time DESC
LIMIT 20;

-- 5. sync_status 分布统计
SELECT sync_status,
       CASE sync_status
         WHEN '0' THEN '未同步'
         WHEN '1' THEN '同步中(可能卡死)'
         WHEN '2' THEN '同步成功'
         WHEN '3' THEN '同步失败'
       END AS status_desc,
       COUNT(*) AS cnt
FROM hg_law_regulation_sync_record
WHERE deleted = 0
GROUP BY sync_status;

-- 6. 检查卡在"同步中"的记录 (sync_status=1 可能是服务重启导致)
SELECT id, trace_id, regs_id, regs_name,
       sync_status, create_time, update_time
FROM hg_law_regulation_sync_record
WHERE deleted = 0 AND sync_status = '1'
ORDER BY create_time DESC;

-- 7. 检查符合 Stage 2 处理条件但未被处理的记录
SELECT COUNT(*) AS pending_count
FROM hg_law_regulation_sync_record
WHERE deleted = 0
  AND del_int = '0'
  AND status = 3
  AND resp_code = '000000'
  AND result_code = 'S'
  AND sync_status IN ('0', '3');

-- 8. 检查 Quartz trigger 状态
SELECT TRIGGER_NAME, TRIGGER_STATE,
       FROM_UNIXTIME(PREV_FIRE_TIME/1000) AS prev_fire,
       FROM_UNIXTIME(NEXT_FIRE_TIME/1000) AS next_fire
FROM QRTZ_TRIGGERS
WHERE TRIGGER_NAME IN (
  'sync-reg-data-job-trigger',
  'update-reg-data-job-trigger',
  'sync-data-job-trigger'
);

-- 9. 检查最新法规记录时间 (确认 Stage 2 输出)
SELECT MAX(create_time) AS last_record_time
FROM hg_law_regulation_record
WHERE type = 2 AND source_type = 3;

-- 10. 如需修复卡死数据 (sync_status=1 -> 3 让它重试)
-- UPDATE hg_law_regulation_sync_record
-- SET sync_status = '3'
-- WHERE sync_status = '1' AND deleted = 0;