记一次因mysql触发器引发的故障

发布时间：2022-07-07 12:43 所属栏目：115 来源：互联网

导读：报错信息表示是由于mysql的函数和触发器引起的，问了下公司开发，他们表示函数功能已经测试上线好久了，没有问题，而触发器是这周刚上的。于是，赶紧进入生产的DB服务器进行查看： mysql use wendi; Database changed mysql SHOW TRIGGERS\G; ... **********

　　报错信息表示是由于mysql的函数和触发器引起的，问了下公司开发，他们表示函数功能已经测试上线好久了，没有问题，而触发器是这周刚上的。于是，赶紧进入生产的DB服务器进行查看：

　　mysql> use wendi;
　　Database changed
　　mysql> SHOW TRIGGERS\G;
　　...
　　*************************** 2. row ***************************
　　             Trigger: cl_borrow_before_insert_tigger
　　               Event: INSERT
　　               Table: cl_borrow
　　           Statement: begin
　　 set @channel_id = (select channel_id from cl_user where user_id = new.user_id);
　　 -- if @channel_id is not null and new.channel_id is null THEN
　　 --   update cl_borrow set channel_id=@channel_id where borrow_id = new.borrow_id;
　　 -- end if;
　　 insert into cl_borrow_status_log (user_id,borrow_id,status_old,status_new,audit_user_id,audit_remark,create_time,channel_id) values (new.user_id,new.borrow_id,null,new.status,new.audit_user_id,new.audit_remark,UNIX_TIMESTAMP(now()),@channel_id);
　　end
　　              Timing: BEFORE
　　             Created: NULL
　　            sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
　　             Definer: root@%
　　character_set_client: utf8
　　collation_connection: utf8_general_ci
　　 Database Collation: utf8_general_ci
　　*************************** 3. row ***************************
　　             Trigger: cl_borrow_after_insert_trigger
　　               Event: INSERT
　　               Table: cl_borrow
　　           Statement: begin
　　 set @channel_id = (select channel_id from cl_user where user_id = new.user_id);
　　 -- if @channel_id is not null and new.channel_id is null THEN
　　 --   update cl_borrow set channel_id=@channel_id where borrow_id = new.borrow_id;
　　 -- end if;
　　 insert into cl_borrow_status_log (user_id,borrow_id,status_old,status_new,audit_user_id,audit_remark,create_time,channel_id) values (new.user_id,new.borrow_id,null,new.status,new.audit_user_id,new.audit_remark,UNIX_TIMESTAMP(now()),@channel_id);
　　end
　　              Timing: AFTER
　　             Created: NULL
　　            sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
　　             Definer: root@%
　　character_set_client: utf8
　　collation_connection: utf8_general_ci
　　 Database Collation: utf8_general_ci
　　*************************** 4. row ***************************
　　             Trigger: cl_borrow_after_update_trigger
　　               Event: UPDATE
　　               Table: cl_borrow
　　           Statement: begin
　　 if old.status != new.status then
　　    set @channel_id = (select channel_id from cl_user where user_id = new.user_id);
　　    insert into cl_borrow_status_log (user_id,borrow_id,status_old,status_new,audit_user_id,audit_remark,create_time,channel_id) values (new.user_id,new.borrow_id,old.status,new.status,new.audit_user_id,new.audit_remark,UNIX_TIMESTAMP(now()),@channel_id);
　　 end if;
　　end
　　              Timing: AFTER
　　             Created: NULL
　　            sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
　　             Definer: root@%
　　character_set_client: utf8
　　collation_connection: utf8_general_ci
　　 Database Collation: utf8_general_ci
　　*************************** 5. row ***************************
　　             Trigger: cl_borrow_status_log
　　               Event: INSERT
　　               Table: cl_borrow_status_log
　　           Statement: BEGIN
　　 update cl_borrow set double_audit_user_id = new.audit_user_id,double_audit_time=new.create_time where borrow_id=new.borrow_id ;
　　end
　　              Timing: AFTER
　　             Created: NULL
　　            sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
　　             Definer: root@%
　　character_set_client: utf8
　　collation_connection: utf8_general_ci
　　 Database Collation: utf8_general_ci
　　    ...
　　    11 rows in set (0.00 sec)
　　如上，总共有11条触发器。为了不影响业务，我决定先将触发器备份，然后将其删除。

　　1，备份mysql触发器：
　　mysqldump --triggers -R -ndt -uroot -p cashloan> wenditrigger.sql
　　这里复习下mysqldump命令：

　　--triggers: Dump triggers for each dumped table. (Defaults to on; use --skip-triggers to disable.)
　　这个是默认值，mysqldump默认会导出触发器。（如果不想备份触发器使用--skip-triggers即可）

　　-R, --routines: Dump stored routines (functions and procedures).
　　导出存储过程以及函数。

　　-n, --no-create-db Suppress the CREATE DATABASE ... IF EXISTS statement that normally is output for each dumped database if --all-databases or --databases is given.
　　不创建建库语句，只对数据进行导出。

　　-d, --no-data No row information.
　　不导出数据，只导出表结构。

　　-t, --no-create-info Don't write table creation info.
　　不导出建表语句，只导出数据。

　　2，查看备份内容:
　　[root@DB ~]$ less wenditrigger.sql
　　-- MySQL dump 10.13 Distrib 5.6.20, for linux-glibc2.5 (x86_64)
　　...
　　DELIMITER ;;
　　/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`%`*/ /*!50003 TRIGGER `__test_trigger_update` AFTER INSERT ON `__test` FOR EACH ROW begin
　　 if new.user_id=100 THEN
　　    update __test set tian='@@@@' where id=new.id;
　　 end if;
　　 insert into __test2 (id,tian,user_id) values (new.id,new.tian,new.user_id);
　　end */;;
　　...
　　/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`%`*/ /*!50003 TRIGGER `cl_borrow_before_insert_tigger` BEFORE INSERT ON `cl_borrow` FOR EACH ROW begin
　　 set @channel_id = (select channel_id from cl_user where user_id = new.user_id);
　　 -- if @channel_id is not null and new.channel_id is null THEN
　　 --   update cl_borrow set channel_id=@channel_id where borrow_id = new.borrow_id;
　　 -- end if;
　　 insert into cl_borrow_status_log (user_id,borrow_id,status_old,status_new,audit_user_id,audit_remark,create_time,channel_id) values (new.user_id,new.borrow_id,null,new.status,new.audit_user_id,new.audit_remark,UNIX_TIMESTAMP(now()),@channel_id);
　　end */;;
　　DELIMITER ;
　　可以看到触发器已经备份好了。

　　3，删除触发器：
　　因为当时情况紧急，首要任务是将业务恢复，所以就把触发器全部删除了。
　　删除暂时没找到批量的方法，还好数据只有11条，一条一条删吧。

　　...
　　mysql> drop trigger cl_borrow_after_insert_trigger;
　　mysql> drop trigger cl_borrow_after_update_trigger;
　　mysql> drop trigger cl_borrow_status_log;
　　mysql> drop trigger cl_installment_after_insert_trigger;
　　...
　　至此，业务终于恢复了。

　　小结：
　　1，MySQL触发器属于隐式调用，往往会在你不知道的情况下做出许多操作，从而增加系统的复杂程度。
　　2，复杂MySQL触发器会嵌套使用，这就有可能产生死锁，本例就是个印证，borrow表触发插入其他表，而插入其他表的操作又会触发borrow表更新，这就产生了死锁，导致borrow表无法被更新。

　　MySQL触发器简介：
　　触发器是一种与表操作有关的数据库对象，当触发器所在表上出现指定事件时，将调用该对象，即表的操作事件触发表上的触发器的执行。
　　触发器语法：

　　CREATE TRIGGER trigger_name
　　trigger_time
　　trigger_event ON table_name
　　FOR EACH ROW
　　trigger_statement
　　trigger_name:触发器名称
　　trigger_time:触发器触发时机(BEFORE/AFTER)
　　trigger_event: 触发事件(INSERT,UPDATE,DELETE)
　　table_name: 建立触发器的表名称
　　trigger_statement: 触发器程序体，可以为单一的SQL语句，也可以是包含BEGIN,END在内的多条语句。
　　FOR EACH ROW: 行级触发

（编辑：ASP站长网）