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

OceanBase 技术问题
#1

【 使用环境 】生产环境
【 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收集诊断信息,详情参见链接（右键跳转查看）：

image

image
image902×214 7.18 KB

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 的论坛小助手已开放测试，在发帖时输入 [@论坛小助手] 即可召唤小助手，欢迎试用！

#3

用户问题: 在生产环境中，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’}”

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

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

更多信息请查看:

  1. 概述

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

#4

逻辑如下：
. 系统操作入库数据的时间是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、是否有主备库？ 主库数据是否同步到备库数据？ 备库是否有触发器？

#5

单机部署，不存在主备库

#6

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

#7

根据您的描述，触发器在初次插入数据时正常工作，但在后续时间点（如 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'}"

总结

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

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

检索到的文档如下:

  1. 物理备库同步过程中卡住
  2. 创建备租户前准备
  3. 数据备份失败
  4. 发起全量数据备份
  5. 定时触发合并
  6. SQL FAQ
  7. obcdc 常见问题

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

#8

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

#9

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

#10

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

#12

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 = ‘用户交易统计数据日志’;

#13

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

#14

好的 我们测试一下

#15

这个表bi_user_pay 给几条数据 但是必须和pay_success_flow这条数据 有关系 要不然没办法测试

#18

INSERT INTO bi_user_pay(uid, account_id, channel_id, user_first_charged, user_charged, user_charged_num) VALUES (1033233401676566526, 101406, ‘default’, 0, 1000, 2);

INSERT INTO bi_user_pay(uid, account_id, channel_id, user_first_charged, user_charged, user_charged_num) VALUES (1033233401676566527, 101407, ‘default’, 0, 200, 2);
INSERT INTO bi_user_pay(uid, account_id, channel_id, user_first_charged, user_charged, user_charged_num) VALUES (1033233401676566528, 101408, ‘default’, 0, 100, 1);

INSERT INTO bi_user_pay(uid, account_id, channel_id, user_first_charged, user_charged, user_charged_num) VALUES (1037489994438023583, 232222, ‘webapk’, 0, 500, 1);

#19

pay_success_flow 这个表我看有唯一健 按理说你插入同样的数据 会报错插入不进去的 程序那边也会报错才对呀 你把observer.log的日志发一下 看一下有没有昨天的日志文件 发出来
pay_success_flow这个表的数据 目前没有重复数据吧 我怀疑是不是你们程序上 有删除pay_success_flow这个表的数据 因为删除不会触发触发器 改变什么数据
ERROR 1062 (23000): Duplicate entry ‘2238065340241678577-99’ for key ‘uk_out_trade_no’

#20

对 所以我觉得不是我程序在重复入库 而是数据库有问题

#21

数据库 还是比较严谨的 基本上不会出现这个问题 你可以写一个删除pay_success_flow这个表的数据触发器 重新建一个日志表 把删除的pay_success_flow这个表的数据记录下来 看看是不是你们的程序在操作删除数据 就知道了 数据库不会去删除数据的

#22

我这边的数据pay_success_flow只会新增不会修改删除 ， 我之前用其他版本 同样的程序也没出现过问题。这个业务系统用的是4.3.3这个版本 然后就发现了这个问题。

#23

你可以写一下触发器 不管是程序删除或者数据库重复入库 我们可以验证一下 这样有助于排查问题

#24

你把observer.log的日志 也发一下 如果今天有数据入库的话 发今天的日志 如果没有发昨天的日志