OCS Inventory Agent关联MariaDB触发器触发500错误求助

核心问题分析 #

1. INSERT触发器执行失败（CDC表为空+500错误的根源） #

MIDDLEWAREV2_CDC的主键是(HARDWARE_ID, NAME)，但NAME字段定义为varchar(255) DEFAULT NULL。InnoDB的主键字段不允许为NULL，如果OCS Inventory Agent上报的记录中NAME为空，触发器执行INSERT操作时会触发主键约束错误，导致原插入事务回滚，同时CDC表无法写入数据。

另外，INSERT...ON DUPLICATE KEY UPDATE的触发逻辑依赖主键/唯一键冲突：只有当新插入的HARDWARE_ID和NAME与CDC表中已有记录完全匹配时才会执行更新，否则执行插入。如果上报数据的NAME始终为NULL，插入操作会持续失败。

2. DELETE触发器逻辑缺陷 #

当前DELETE触发器的条件：

DELETE FROM MIDDLEWAREV2_CDC WHERE ID NOT IN (select ID from MIDDLEWAREV2) AND HARDWARE_ID = OLD.HARDWARE_ID;

• 效率极低：每次删除都要全表扫描MIDDLEWAREV2，且ID并非MIDDLEWAREV2_CDC的主键，可能存在重复值，无法准确定位要删除的记录。
• 逻辑偏差：应该通过MIDDLEWAREV2_CDC的主键(HARDWARE_ID, NAME)定位原删除记录对应的CDC条目，而非通过ID关联。

分步修复方案 #

步骤1：修复MIDDLEWAREV2_CDC表结构 #

选择以下两种方案之一，解决主键包含可空字段的问题：

-- 方案1：强制NAME字段不为NULL（业务允许的情况下优先选择）
ALTER TABLE MIDDLEWAREV2_CDC MODIFY COLUMN NAME varchar(255) NOT NULL;

-- 方案2：调整CDC表主键为(HARDWARE_ID, ID)（如果ID是唯一标识）
ALTER TABLE MIDDLEWAREV2_CDC DROP PRIMARY KEY, ADD PRIMARY KEY (HARDWARE_ID, ID);

步骤2：修正INSERT触发器 #

根据调整后的CDC表主键，优化触发器逻辑（以方案2为例）：

DELIMITER |
DROP TRIGGER IF EXISTS AUTO_UPSERT_MIDDLEWAREV2;
CREATE TRIGGER AUTO_UPSERT_MIDDLEWAREV2 AFTER INSERT ON `MIDDLEWAREV2`
FOR EACH ROW
BEGIN
  INSERT INTO MIDDLEWAREV2_CDC (ID, HARDWARE_ID, NAME, TYPE, SERVER_NAME, SERVER_BINARY_PATH, PORT, STATE, SERVER_VERSION)
  VALUES (NEW.ID, NEW.HARDWARE_ID, NEW.NAME, NEW.TYPE, NEW.SERVER_NAME, NEW.SERVER_BINARY_PATH, NEW.PORT, NEW.STATE, NEW.SERVER_VERSION)
  ON DUPLICATE KEY UPDATE 
    TYPE = NEW.TYPE, 
    SERVER_NAME = NEW.SERVER_NAME, 
    PORT = NEW.PORT, 
    STATE = NEW.STATE, 
    SERVER_VERSION = NEW.SERVER_VERSION;
END;
|
DELIMITER ;

注：ON DUPLICATE KEY UPDATE中无需更新主键字段，冲突时这些字段值本身已匹配

步骤3：修正DELETE触发器 #

替换为精准定位的逻辑：

DELIMITER |
DROP TRIGGER IF EXISTS AUTO_DELETE_MIDDLEWAREV2;
CREATE TRIGGER AUTO_DELETE_MIDDLEWAREV2 AFTER DELETE ON `MIDDLEWAREV2`
FOR EACH ROW
BEGIN
  DELETE FROM MIDDLEWAREV2_CDC WHERE HARDWARE_ID = OLD.HARDWARE_ID AND NAME = OLD.NAME;
END;
|
DELIMITER ;

步骤4：验证触发器执行 #

手动模拟OCS Agent的操作流程，验证触发器是否正常工作：

-- 插入测试记录
INSERT INTO MIDDLEWAREV2 (HARDWARE_ID, NAME, TYPE) VALUES (1, 'TestTomcat', 'WebServer');
-- 检查CDC表是否写入数据
SELECT * FROM MIDDLEWAREV2_CDC WHERE HARDWARE_ID = 1;
-- 删除测试记录
DELETE FROM MIDDLEWAREV2 WHERE HARDWARE_ID = 1 AND NAME = 'TestTomcat';
-- 检查CDC表是否删除对应记录
SELECT * FROM MIDDLEWAREV2_CDC WHERE HARDWARE_ID = 1;

步骤5：开启MariaDB详细错误日志 #

如果问题仍存在，修改MariaDB配置文件（如my.cnf）开启详细错误记录：

[mysqld]
log_error = /var/log/mariadb/mariadb.log
log_error_verbosity = 3

重启MariaDB后，再次触发OCS Agent上报，查看日志中的具体错误信息。

内容的提问来源于stack exchange，提问作者Arnaud DUBOIS