Shulou(Shulou.com)06/01 Report--

Not long after I sat down to work at the company last Saturday, the company's business reported that the user's borrow table information could not be updated. Check the error report on the website as follows:

The error message is caused by the functions and triggers of mysql. Asked the company to develop, they said that the function has been tested for a long time and there is no problem, while the trigger was launched just this week. Therefore, quickly go to the production DB server to check:

Mysql > use wendi;Database changedmysql > 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: utf8collation_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: utf8collation_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: utf8collation_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: utf8collation_connection: utf8_general_ci Database Collation: utf8_general_ci... 11 rows in set (0.00 sec)

As mentioned above, there are a total of 11 triggers. In order not to affect the business, I decided to back up the trigger and then delete it.

1. Backup mysql trigger: mysqldump-- triggers-R-ndt-uroot-p cashloan > wenditrigger.sql

Review the mysqldump command here:

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

This is the default value, and mysqldump exports triggers by default. (if you do not want to back up the trigger, use-- skip-triggers)

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

Export stored procedures and functions.

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

No database building statements are created, only data are exported.

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

No data is exported, only the table structure is exported.

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

Do not export table building statements, only export data.

2. Check the backup content: [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_ 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

You can see that the trigger has been backed up.

3. Delete trigger:

Because the situation was urgent at that time, the first task was to restore the business, so all the triggers were deleted.

Delete temporarily did not find the method of batch, fortunately, there are only 11 data, delete it one by one.

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

At this point, business has finally resumed.

Summary:

MySQL triggers are called implicitly and often do a lot of operations without your knowledge, thus increasing the complexity of the system.

2. Complex MySQL triggers will be nested and used, which may lead to deadlocks. This example is a proof. Borrow table triggers to insert other tables, while inserting other tables triggers borrow table updates, which results in deadlocks and prevents borrow tables from being updated.

Introduction to MySQL triggers:

A trigger is a database object related to a table operation. When a specified event occurs on the table where the trigger is located, the object will be called, that is, the execution of the trigger on the table's action event trigger publication.

Trigger syntax:

CREATE TRIGGER trigger_nametrigger_timetrigger_event ON table_nameFOR EACH ROWtrigger_statement

Trigger_name: trigger name

Trigger_time: trigger timing (BEFORE/AFTER)

Trigger_event: trigger event (INSERT,UPDATE,DELETE)

Table_name: create the table name of the trigger

Trigger_statement: trigger program body, which can be a single SQL statement or multiple statements including BEGIN,END.

FOR EACH ROW: row-level trigger

Reference article:

Https://www.cnblogs.com/duodushu/p/5446384.html

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.