記一次由mysql觸發(fā)器引發(fā)的故障

上周六到公司上班，剛坐下沒(méi)多久，公司業(yè)務(wù)傳過(guò)消息說(shuō)，用戶borrow表信息無(wú)法更新。查看網(wǎng)站報(bào)錯(cuò)如下：

報(bào)錯(cuò)信息表示是由于mysql的函數(shù)和觸發(fā)器引起的，問(wèn)了下公司開(kāi)發(fā)，他們表示函數(shù)功能已經(jīng)測(cè)試上線好久了，沒(méi)有問(wèn)題，而觸發(fā)器是這周剛上的。于是，趕緊進(jìn)入生產(chǎn)的DB服務(wù)器進(jìn)行查看：

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條觸發(fā)器。為了不影響業(yè)務(wù)，我決定先將觸發(fā)器備份，然后將其刪除。

1，備份mysql觸發(fā)器：

mysqldump --triggers -R -ndt -uroot -p cashloan> wenditrigger.sql

這里復(fù)習(xí)下mysqldump命令：

--triggers: Dump triggers for each dumped table. (Defaults to on; use --skip-triggers to disable.)

這個(gè)是默認(rèn)值，mysqldump默認(rèn)會(huì)導(dǎo)出觸發(fā)器。（如果不想備份觸發(fā)器使用--skip-triggers即可）

-R, --routines: Dump stored routines (functions and procedures).

導(dǎo)出存儲(chǔ)過(guò)程以及函數(shù)。

-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.

不創(chuàng)建建庫(kù)語(yǔ)句，只對(duì)數(shù)據(jù)進(jìn)行導(dǎo)出。

-d, --no-data No row information.

不導(dǎo)出數(shù)據(jù)，只導(dǎo)出表結(jié)構(gòu)。

-t, --no-create-info Don't write table creation info.

不導(dǎo)出建表語(yǔ)句，只導(dǎo)出數(shù)據(jù)。

2，查看備份內(nèi)容:

[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 ;

可以看到觸發(fā)器已經(jīng)備份好了。

3，刪除觸發(fā)器：

因?yàn)楫?dāng)時(shí)情況緊急，首要任務(wù)是將業(yè)務(wù)恢復(fù)，所以就把觸發(fā)器全部刪除了。
刪除暫時(shí)沒(méi)找到批量的方法，還好數(shù)據(jù)只有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;
...

至此，業(yè)務(wù)終于恢復(fù)了。

小結(jié)：

1，MySQL觸發(fā)器屬于隱式調(diào)用，往往會(huì)在你不知道的情況下做出許多操作，從而增加系統(tǒng)的復(fù)雜程度。
2，復(fù)雜MySQL觸發(fā)器會(huì)嵌套使用，這就有可能產(chǎn)生死鎖，本例就是個(gè)印證，borrow表觸發(fā)插入其他表，而插入其他表的操作又會(huì)觸發(fā)borrow表更新，這就產(chǎn)生了死鎖，導(dǎo)致borrow表無(wú)法被更新。

MySQL觸發(fā)器簡(jiǎn)介：

觸發(fā)器是一種與表操作有關(guān)的數(shù)據(jù)庫(kù)對(duì)象，當(dāng)觸發(fā)器所在表上出現(xiàn)指定事件時(shí)，將調(diào)用該對(duì)象，即表的操作事件觸發(fā)表上的觸發(fā)器的執(zhí)行。
觸發(fā)器語(yǔ)法：

CREATE TRIGGER trigger_name
trigger_time
trigger_event ON table_name
FOR EACH ROW
trigger_statement

trigger_name:觸發(fā)器名稱
trigger_time:觸發(fā)器觸發(fā)時(shí)機(jī)(BEFORE/AFTER)
trigger_event: 觸發(fā)事件(INSERT,UPDATE,DELETE)
table_name: 建立觸發(fā)器的表名稱
trigger_statement: 觸發(fā)器程序體，可以為單一的SQL語(yǔ)句，也可以是包含BEGIN,END在內(nèi)的多條語(yǔ)句。
FOR EACH ROW: 行級(jí)觸發(fā)

參考文章：
https://www.cnblogs.com/duodushu/p/5446384.html