TRIGGER 异常调用导致数据出现重复统计

mawangmay · 2024 年11 月 6 日 15:09

【使用环境】生产环境
【 OB or 其他组件】5.7.25-OceanBase_CE-v4.3.3.0
【使用版本】5.7.25-OceanBase_CE-v4.3.3.0
【问题描述】触发器会根据订单金额统计用户的充值金额，发现异常，添加日志后，发现很久之前的订单还会自动触发触发器。
【复现路径】问题出现前后相关操作

1038358873200662614-18 实际支付时间是2024-11-05 19:47:59 可是6号 11:30:03 ，12:30:03 ，13:30:03 都触发器，系统逻辑昨天的数据昨天已经入库了今天不会入库所以不会触发触发器而且表有唯一键约束，单笔也只会入库成功一次。

【附件及日志】推荐使用OceanBase敏捷诊断工具obdiag收集诊断信息,详情参见链接（右键跳转查看）：

CREATE TABLE pay_success_flow (
event_time int(11) DEFAULT NULL COMMENT ‘事件时间’,
mfr_id int(11) DEFAULT NULL COMMENT ‘流水标识’,
account_id bigint(20) DEFAULT NULL COMMENT ‘账号id’,
uid bigint(20) DEFAULT NULL COMMENT ‘用户游戏uid’,
channel_id varchar(20) DEFAULT NULL COMMENT ‘渠道id’,
product_id varchar(40) DEFAULT NULL COMMENT ‘商品id’,
out_trade_no varchar(40) DEFAULT NULL COMMENT ‘订单id’,
total_amount double DEFAULT NULL COMMENT ‘金额’,
subject varchar(64) DEFAULT NULL COMMENT ‘商品标题’,
os_type smallint(6) DEFAULT NULL COMMENT ‘客户系统类型’,
flag tinyint(4) DEFAULT NULL COMMENT ‘标记’,
install_source tinyint(4) DEFAULT NULL COMMENT ‘安装源’,
pay_mode tinyint(4) DEFAULT NULL COMMENT ‘支付方式’,
pay_sdk_type tinyint(4) DEFAULT NULL COMMENT ‘支付SDK类型’,
pay_platform tinyint(4) DEFAULT NULL COMMENT ‘支付平台’,
order_number varchar(50) DEFAULT NULL COMMENT ‘外部订单号’,
first_charge_time int(11) DEFAULT NULL COMMENT ‘首次付费时间’,
start_time bigint(20) DEFAULT NULL COMMENT ‘购买开始时间’,
biz int(11) DEFAULT NULL COMMENT ‘商品类型’,
extra varchar(512) DEFAULT NULL COMMENT ‘商品ID’,
plat_trade_no varchar(64) DEFAULT NULL COMMENT ‘平台订单id’,
src_amount double DEFAULT NULL COMMENT ‘原始金额’,
UNIQUE KEY uk_out_trade_no (out_trade_no) BLOCK_SIZE 16384 LOCAL,
KEY ix_event_time (event_time) BLOCK_SIZE 16384 LOCAL,
KEY ix_account_id (account_id) BLOCK_SIZE 16384 LOCAL
) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = ‘zstd_1.3.8’ REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 COMMENT = ‘支付成功流水’;

– 定义触发器
DELIMITER //
CREATE TRIGGER sp_update_user_pay_data AFTER INSERT ON pay_success_flow FOR EACH ROW begin

	CALL sp_update_user_pay_data(NEW.out_trade_no, NEW.account_id, NEW.uid, NEW.total_amount, NEW.src_amount, NEW.channel_id, NEW.pay_sdk_type, FROM_UNIXTIME(NEW.event_time,'%Y-%m-%d'));

end; //

DELIMITER ;

– 存储过程
CREATE PROCEDURE sp_update_user_pay_data(IN pout_trade_no varchar(64),IN paccount_id bigint(20),IN puid bigint(20),IN ptotal_amount double,IN psrc_amount double,IN channel varchar(32),IN ppay_sdk_type int(10),IN eventTime date)
MODIFIES SQL DATA
BEGIN
DECLARE sysGroupId SMALLINT(6) DEFAULT 0;
DECLARE userUid bigint DEFAULT 0;
DECLARE totalPay DOUBLE DEFAULT 0;
DECLARE totalDivide DOUBLE DEFAULT 0;
DECLARE playerMinimum SMALLINT(6) DEFAULT 0;
DECLARE playerMinimumNew SMALLINT(6) DEFAULT 0;
DECLARE effectTime date DEFAULT eventTime;
DECLARE device_model varchar(50) DEFAULT ‘’;

		update data_bi_turtle.bi_user_pay set user_charged = CAST((user_charged) AS DECIMAL(18,2)) + CAST((psrc_amount) AS DECIMAL(18,2)),user_charged_num=user_charged_num+1 where uid = puid;

	insert into data_bi_turtle.bi_user_pay_log(uid, account_id, user_charged, user_charged_num, out_trade_no, event_date) 
SELECT uid, account_id, user_charged, user_charged_num,pout_trade_no, now() from data_bi_turtle.bi_user_pay where uid = puid;

END

【SOP系列 22 】——故障诊断第一步(自助诊断和诊断信息收集)
@论坛小助手
【备注】基于 LLM 和开源文档 RAG 的论坛小助手已开放测试，在发帖时输入 [@论坛小助手] 即可召唤小助手，欢迎试用！

论坛小助手 · 2024 年11 月 6 日 15:10

用户问题: 在生产环境中，OceanBase 5.7.25-OceanBase_CE-v4.3.3.0 版本的触发器导致数据重复统计，具体表现为旧订单在后续日期再次触发触发器，如何解决？

推荐使用 obdiag 工具进行日志采集，以便我们能够更好地了解触发器的行为和可能的原因。

日志采集场景: SQL 执行出错
日志采集命令: obdiag gather scene run --scene=observer.sql_err --env “{db_connect=’-h127.0.0.1 -P2881 -utest@test -p****** -Dtest’, trace_id=‘Yxx’}”

请您提供一些信息以便我们更好地帮助您：

请提供触发器的具体定义，包括触发器的名称、触发时间（BEFORE/AFTER）、触发事件（INSERT/UPDATE/DELETE）以及触发器的触发条件和操作。
请提供触发器所关联的表结构，包括表名、字段及其类型。
请提供触发器触发时的 SQL 语句，特别是那些可能导致旧订单再次触发触发器的语句。
请提供触发器触发前后表中数据的变化情况，以便我们了解数据重复统计的具体表现。
请提供触发器触发的时间点和频率，以及触发器是否在特定时间段内频繁触发。
请提供触发器触发时的系统日志，特别是与触发器相关的日志信息。

更多信息请查看:

概述

附上敏捷诊断工具 obdiag 使用帮助链接

淇铭 · 2024 年11 月 6 日 16:50

逻辑如下：
. 系统操作入库数据的时间是2024-11-05 19:47:59
. 触发器触发操作的入库时间是2024-11-06 11:30:03 ，12:30:03 ，13:30:03
. 今天没有系统操作入库数据

1、ob库的架构
SELECT * FROM oceanbase.DBA_OB_ZONES;
2、是否有主备库？主库数据是否同步到备库数据？备库是否有触发器？

mawangmay · 2024 年11 月 7 日 09:00

单机部署，不存在主备库

mawangmay · 2024 年11 月 7 日 09:01

11-5 19:47:59入库的时候应该也触发了后来发现数据对账失败才添加的日志后发现如下的时间是多触发的。

论坛小助手 · 2024 年11 月 7 日 09:04

根据您的描述，触发器在初次插入数据时正常工作，但在后续时间点（如 11-6 11:30:03, 12:30:03, 13:30:03）再次触发，导致数据重复统计。考虑到您提到的是单机部署，不存在主备库的情况，我们可以从以下几个方面进行排查：

1. 检查触发器定义

确保触发器定义没有问题，特别是触发条件和触发时间。您提供的触发器定义如下：

DELIMITER //
CREATE TRIGGER `sp_update_user_pay_data` AFTER INSERT ON `pay_success_flow` FOR EACH ROW 
BEGIN
    CALL sp_update_user_pay_data(
        NEW.out_trade_no, 
        NEW.account_id, 
        NEW.uid, 
        NEW.total_amount, 
        NEW.src_amount, 
        NEW.channel_id, 
        NEW.pay_sdk_type, 
        FROM_UNIXTIME(NEW.event_time, '%Y-%m-%d')
    );
END; //
DELIMITER ;

2. 检查存储过程定义

确保存储过程 sp_update_user_pay_data 没有逻辑错误。您提供的存储过程定义如下：

CREATE PROCEDURE `sp_update_user_pay_data`(
    IN `pout_trade_no` varchar(64),
    IN `paccount_id` bigint(20),
    IN `puid` bigint(20),
    IN `ptotal_amount` double,
    IN `psrc_amount` double,
    IN `channel` varchar(32),
    IN `ppay_sdk_type` int(10),
    IN `eventTime` date
)
MODIFIES SQL DATA
BEGIN
    DECLARE sysGroupId SMALLINT(6) DEFAULT 0;    
    DECLARE userUid bigint DEFAULT 0;
    DECLARE totalPay DOUBLE DEFAULT 0;
    DECLARE totalDivide DOUBLE DEFAULT 0;
    DECLARE playerMinimum SMALLINT(6) DEFAULT 0;
    DECLARE playerMinimumNew SMALLINT(6) DEFAULT 0;
    DECLARE effectTime date DEFAULT eventTime;
    DECLARE device_model varchar(50) DEFAULT '';

    -- 更新用户充值数据
    UPDATE data_bi_turtle.bi_user_pay 
    SET 
        user_charged = CAST((user_charged) AS DECIMAL(18,2)) + CAST((psrc_amount) AS DECIMAL(18,2)),
        user_charged_num = user_charged_num + 1 
    WHERE uid = puid;

    -- 插入日志
    INSERT INTO data_bi_turtle.bi_user_pay_log (
        uid, 
        account_id, 
        user_charged, 
        user_charged_num, 
        out_trade_no, 
        event_date
    ) 
    SELECT 
        uid, 
        account_id, 
        user_charged, 
        user_charged_num, 
        pout_trade_no, 
        NOW() 
    FROM data_bi_turtle.bi_user_pay 
    WHERE uid = puid;
END

3. 检查数据插入操作

确保数据插入操作没有重复执行。您可以检查 pay_success_flow 表的插入日志，确认是否有重复的插入操作。例如，可以查看 pay_success_flow 表的 event_time 字段，确认是否有重复的记录。

SELECT * FROM pay_success_flow WHERE out_trade_no = '1038358873200662614-18';

4. 检查触发器日志

如果您启用了触发器日志，可以查看触发器的执行日志，确认触发器的触发时间和频率。如果没有启用触发器日志，建议启用并重新测试。

5. 检查定时任务

确保没有定时任务或其他脚本在后续时间点再次插入相同的数据。您可以检查系统的定时任务配置，例如 cron 任务。

6. 检查应用程序逻辑

确保应用程序逻辑没有在后续时间点再次调用插入操作。您可以检查应用程序的日志，确认是否有重复的插入请求。

7. 检查唯一键约束

虽然您提到表有唯一键约束，但仍然建议检查唯一键约束是否生效。您可以尝试手动插入重复的数据，确认是否会被唯一键约束阻止。

INSERT INTO pay_success_flow (out_trade_no, ...) VALUES ('1038358873200662614-18', ...);

8. 检查系统日志

使用 obdiag 工具收集系统日志，特别是与触发器相关的日志信息，以便进一步分析。

obdiag gather scene run --scene=observer.sql_err --env "{db_connect='-h127.0.0.1 -P2881 -utest@test -p****** -Dtest', trace_id='Yxx'}"

总结

检查触发器和存储过程定义，确保没有逻辑错误。
检查数据插入操作，确认是否有重复的插入记录。
检查触发器日志，确认触发器的触发时间和频率。
检查定时任务，确保没有定时任务在后续时间点再次插入相同的数据。
检查应用程序逻辑，确保应用程序逻辑没有在后续时间点再次调用插入操作。
检查唯一键约束，确认唯一键约束是否生效。
收集系统日志，使用 obdiag 工具收集系统日志，以便进一步分析。

希望以上步骤能帮助您解决问题。如果问题仍然存在，请提供更多的日志信息以便进一步分析。

检索到的文档如下:

(小助手的答复已结束，如未能解决您的问题，请继续提问并等待其他同学的回复，谢谢！)

mawangmay · 2024 年11 月 7 日 09:10

目前存在的问题，订单表有唯一键约束，所以同一订单只能入库一次，所以单笔订单只会触发一次触发器操作。而通过上面的日志看，同一笔订单触发了多次触发器，所以暂时怀疑是数据库有某些异常或者重试逻辑。并非用户业务问题。自己手动重试将这笔订单手动多次入库，是不会出现多次触发触发器的操作的。会响应预期的主键冲突入库失败。

mawangmay · 2024 年11 月 7 日 09:26

其他有异常的数据都有同一个问题就是上一次和这次间隔的时间是1个小时，是否可以通过这个线索去排查相关数据库定时自动重试或者逻辑。

淇铭 · 2024 年11 月 7 日 09:41

你能提供点数据么？我们测试一下看看是否为逻辑问题或着数据库问题

mawangmay · 2024 年11 月 7 日 10:12

（帖子已被作者删除）

mawangmay · 2024 年11 月 7 日 10:14

INSERT INTO pay_success_flow(event_time, mfr_id, account_id, uid, channel_id, product_id, out_trade_no, total_amount, subject, os_type, flag, install_source, pay_mode, pay_sdk_type, pay_platform, order_number, first_charge_time, start_time, biz, extra, plat_trade_no, src_amount) VALUES (1730737353, 57, 232222, 1037489994438023583, ‘webapk’, ‘5_100’, ‘2238065340241678577-99’, 100, ‘100.00道具’, 1, 0, 0, 5, 9, 0, ‘’, 0, 0, 18, ‘{“code”:“106”,“client_ip”:“127.0.0.1”,“account_id”:232222,“extra_percentage”:0,“factor”:0,“vip”:0,“id”:5,“extra_give”:0,“osType”:“ANDROID”,“product”:“ppppppp”,“vn_bank_code”:0,“free_gold”:0,“first_charge_time”:0,“pay_channle_id”:1,“energy_factor”:0,“pay_sdk_type”:9,“pay_gold”:0,“currency”:“PPP”}’, ‘2238065340241678577-99’, 100);

CREATE TABLE bi_user_pay (
uid bigint(20) NOT NULL COMMENT ‘UID’,
account_id bigint(20) NOT NULL COMMENT ‘账户ID’,
channel_id varchar(32) DEFAULT NULL COMMENT ‘渠道ID’,
user_first_charged double NOT NULL DEFAULT ‘0’ COMMENT ‘首次充值金额’,
user_charged double NOT NULL DEFAULT ‘0’ COMMENT ‘充值金额’,
user_charged_num int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘充值次数’,
PRIMARY KEY (uid),
KEY ix_account_id (account_id) BLOCK_SIZE 16384 LOCAL
) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = ‘zstd_1.3.8’ REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 COMMENT = ‘用户交易统计数据’;

CREATE TABLE bi_user_pay_log (
id bigint(20) NOT NULL AUTO_INCREMENT COMMENT ‘id’,
uid bigint(20) NOT NULL COMMENT ‘UID’,
account_id bigint(20) NOT NULL COMMENT ‘account_id’,
user_charged double NOT NULL DEFAULT ‘0’ COMMENT ‘充值金额’,
user_charged_num int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘充值次数’,
user_exchanged double NOT NULL DEFAULT ‘0’ COMMENT ‘兑换金额’,
user_agent_exchanged double NOT NULL DEFAULT ‘0’ COMMENT ‘代理兑换金额’,
event_date datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
out_trade_no varchar(64) DEFAULT NULL,
PRIMARY KEY (id),
KEY ix_uid (uid) BLOCK_SIZE 16384 LOCAL,
KEY ix_account_id (account_id) BLOCK_SIZE 16384 LOCAL
) AUTO_INCREMENT = 55446 AUTO_INCREMENT_MODE = ‘ORDER’ DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = ‘zstd_1.3.8’ REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 COMMENT = ‘用户交易统计数据日志’;

mawangmay · 2024 年11 月 7 日 10:22

我这边手动执行数据是没问题的会触发唯一键约束像是数据库内部执行或者内部进行的触发器调用。现在我这边的数据库还是存在一直重复入库的情况。

淇铭 · 2024 年11 月 7 日 10:28

好的我们测试一下

TRIGGER 异常调用 导致 数据出现重复统计