# Triggers on MySQL Replica Not working sometimes

**URL:** <https://forums.percona.com/t/triggers-on-mysql-replica-not-working-sometimes/19109>\
**Category:** MySQL & MariaDB\
**Tags:** mysql\
**Created:** [December 14, 2022, 10:34am UTC](https://forums.percona.com/t/triggers-on-mysql-replica-not-working-sometimes/19109 "2022-12-14T10:34:43Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![amol.gupta](https://avatars.discourse-cdn.com/v4/letter/a/3bc359/32.png) [@amol.gupta](https://forums.percona.com/u/amol.gupta)\
**Post date:** [December 14, 2022, 10:34am UTC](https://forums.percona.com/t/triggers-on-mysql-replica-not-working-sometimes/19109/1 "2022-12-14T10:34:43Z")

</div>

We have one table on Master MySQL and it has Replica MySQL server where we have created triggers to maintain audits on the table.

We have create three triggers on a table

1. After insert
2. After update
3. Before delete

There is one stored procedure on Master MySQL server which gets called to insert data into this table and updates are happening through Django ORM

Whenever there is any update or insert through the stored procedure , the after insert or after update trigger does not invokes on replica.

We are using BinLog Format as Mixed

Can you please help why is this happening?

---

<div class="post-metadata">

**Author:** ![Pep\_Pla](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/pep_pla/32/9735_2.png) [@Pep\_Pla](https://forums.percona.com/u/Pep_Pla)\
**Post date:** [December 14, 2022, 12:46pm UTC](https://forums.percona.com/t/triggers-on-mysql-replica-not-working-sometimes/19109/2 "2022-12-14T12:46:09Z")

</div>

Hi @amol.gupta

Thank you for joining the Percona Community.

Mixed Binlog format means that events are usually replicated in _ **statement format** _. When events are replicated using statement, then triggers in the replicas are executed too. But when events are replicated in _ **row format** _, then the triggers are not executed in the replicas.

I suspect that the procedure is not considered safe for execution in statement format and MySQL is running it in row format. This could explain why the updates and inserts are not triggered in the replica.

To validate that theory, could you paste here the code of the procedure? Use [`show create procedure`](https://dev.mysql.com/doc/refman/8.0/en/show-create-procedure.html).

We will also need to know the value of [`log_bin_trust_function_creators`](https://dev.mysql.com/doc/refman/8.0/en/replication-options-binary-log.html#sysvar_log_bin_trust_function_creators).

You can read about the replication formats [here](https://dev.mysql.com/doc/refman/5.7/en/replication-formats.html).

And about replication of stored procedures [here](https://dev.mysql.com/doc/refman/8.0/en/stored-programs-logging.html).

I hope this helps you.

Pep

---

<div class="post-metadata">

**Author:** ![amol.gupta](https://avatars.discourse-cdn.com/v4/letter/a/3bc359/32.png) [@amol.gupta](https://forums.percona.com/u/amol.gupta)\
**Post date:** [December 14, 2022, 1:33pm UTC](https://forums.percona.com/t/triggers-on-mysql-replica-not-working-sometimes/19109/3 "2022-12-14T13:33:12Z")

</div>

Hi Pep

Thank you for the response  
the variable is set as log\_bin\_trust\_function\_creators = 1  
Here is the procedure.

> > CREATE DEFINER=`devrds`@`%` PROCEDURE `prod_gateway_db_4th_Sep_6am`.`initiate_transaction`(
> 
> IN merchant\_key VARCHAR(15), IN sub\_merchant\_id VARCHAR(15), IN txnid VARCHAR(40), IN email VARCHAR(200),  
> IN phone VARCHAR(45), IN firstname VARCHAR(150), IN udf1 VARCHAR(300), IN udf2 VARCHAR(300),  
> IN udf3 VARCHAR(300), IN udf4 VARCHAR(300), IN udf5 VARCHAR(300), IN udf6 VARCHAR(300), IN udf7 VARCHAR(300), IN surl VARCHAR(2000), IN furl VARCHAR(2000),  
> IN start\_time VARCHAR(50), IN address1 VARCHAR(100), IN address2 VARCHAR(100), IN city VARCHAR(50),  
> IN state VARCHAR(50), IN country VARCHAR(50), IN zipcode VARCHAR(10), IN productinfo VARCHAR(45),  
> IN unique\_id VARCHAR(255), IN amount FLOAT(15), IN isMobile INT(5), IN user\_agent VARCHAR(200), IN device VARCHAR(45),  
> IN referrer\_ip VARCHAR(45), IN referrer\_url VARCHAR(2000),IN environment VARCHAR(4),IN split\_payments VARCHAR(255),  
> IN request\_flow VARCHAR(15),IN show\_payment\_mode VARCHAR(255),IN allowed\_cards VARCHAR(255),  
> OUT status VARCHAR(10),OUT message VARCHAR(100),OUT peb\_transaction\_id VARCHAR(15),OUT is\_coupon\_enabled TINYINT(1),OUT cashback\_percent INT)  
> BEGIN
> 
> DECLARE merchant\_id INT;  
> DECLARE is\_subaggregator INT;  
> DECLARE submerchant\_id INT;  
> DECLARE db\_query VARCHAR(1000);  
> DECLARE customer\_id INT;  
> DECLARE transaction\_id INT;  
> DECLARE access\_key VARCHAR(64);
> 
> SET status = “false”;  
> SET access\_key = generate\_access\_key(start\_time);  
> SELECT mi.id,is\_sub\_aggregator,cashback\_status,cashback\_percentage INTO merchant\_id ,is\_subaggregator,is\_coupon\_enabled,cashback\_percent from peb\_merchant\_info mi,peb\_merchant\_settings ms,peb\_gateway\_settings gs WHERE mi.merchant\_settings\_id = ms.id and mi.gateway\_settings\_id = gs.id and mi.key=merchant\_key;
> 
> IF is\_subaggregator = 1 THEN  
> SELECT smi.id,gs.cashback\_status,gs.cashback\_percentage INTO submerchant\_id,is\_coupon\_enabled,cashback\_percent FROM peb\_submerchant\_info smi,peb\_gateway\_settings gs WHERE smi.submerchant\_id = sub\_merchant\_id AND smi.merchant\_id = merchant\_id AND smi.gateway\_settings\_id = gs.id AND is\_active = 1 ;  
> END IF;
> 
> INSERT INTO peb\_customer\_request (merchant\_txn\_id, email, phone, first\_name, product\_info, access\_key, is\_done, timestamp, address1, address2, city, state, country, zipcode, additional1, additional2, additional3, additional4, additional5, additional6, additional7, surl, furl, start\_time, is\_coupon\_enabled, coupon\_list, merchant\_id, unique\_id,split\_payments, request\_flow, show\_payment\_mode, allowed\_cards) VALUES (txnid,email,phone,firstname,productinfo,access\_key,0,NOW(),address1,address2,city,state,country,zipcode,udf1,udf2,udf3,udf4,udf5,udf6,udf7,surl,furl,start\_time,is\_coupon\_enabled,‘’,merchant\_id,unique\_id,split\_payments,request\_flow, show\_payment\_mode, allowed\_cards);
> 
> SET customer\_id = LAST\_INSERT\_ID();  
> SET peb\_transaction\_id = generate\_transaction\_id(environment);
> 
> IF submerchant\_id = ‘’ THEN SET submerchant\_id = NULL;END IF;
> 
> INSERT INTO peb\_transaction\_info(  
> peb\_transaction\_id,  
> amount,  
> peb\_transaction\_status,  
> amount\_tdr,  
> peb\_cash\_back\_percentage,  
> peb\_customer\_id,  
> peb\_merchant\_id,  
> peb\_submerchant\_id,  
> peb\_is\_mobile\_app\_initiated,  
> peb\_user\_agent,  
> peb\_device,  
> peb\_referer\_ip,  
> peb\_referer,  
> peb\_transaction\_date,  
> currency\_type,  
> transaction\_ref\_num,  
> bank\_ref\_num,  
> transaction\_type,  
> error\_msg,  
> checksum,  
> peb\_card\_category,  
> peb\_payout\_id,  
> peb\_pg\_payout\_id,  
> retry\_valid,  
> peb\_settlement\_status,  
> peb\_chargeback\_status,  
> peb\_put\_in\_sheet,  
> pg\_id\_id,  
> is\_stos\_updated,  
> hdfc\_transaction\_status,  
> peb\_name\_on\_card,  
> peb\_upi\_va,  
> peb\_card\_number,  
> peb\_subaggregator\_commission,  
> payment\_category,  
> peb\_refund\_status  
> ) VALUES (  
> peb\_transaction\_id,  
> round(amount,2),  
> ‘preinitiated’,  
> round(amount,2),  
> cashback\_percent,  
> customer\_id,  
> merchant\_id,  
> submerchant\_id,  
> isMobile,  
> user\_agent,  
> device,  
> referrer\_ip,  
> referrer\_url,  
> start\_time,  
> ‘INR’,  
> ‘NA’,  
> ‘NA’,  
> ‘NA’,  
> ‘NA’,  
> ‘NA’,  
> ‘NA’,  
> NULL,  
> ‘’,  
> 0,  
> ‘UT’,  
> ‘NC’,  
> 0,  
> NULL,  
> 0,  
> ‘NA’,  
> ‘NA’,  
> ‘NA’,  
> NULL,  
> 0,  
> ‘DEFAULT’,  
> ‘’  
> );
> 
> SET transaction\_id = LAST\_INSERT\_ID();
> 
> SET status=“true”;  
> SET message=access\_key;
> 
> END

---

<div class="post-metadata">

**Author:** ![amol.gupta](https://avatars.discourse-cdn.com/v4/letter/a/3bc359/32.png) [@amol.gupta](https://forums.percona.com/u/amol.gupta)\
**Post date:** [December 14, 2022, 1:37pm UTC](https://forums.percona.com/t/triggers-on-mysql-replica-not-working-sometimes/19109/4 "2022-12-14T13:37:14Z")

</div>

We have set this to 1  
log\_bin\_trust\_function\_creators

and Here is the code

```auto
CREATE DEFINER=`devrds`@`%` PROCEDURE `prod_gateway_db_4th_Sep_6am`.`initiate_transaction`(

IN merchant_key VARCHAR(15), IN sub_merchant_id VARCHAR(15), IN txnid VARCHAR(40), IN email VARCHAR(200),
IN phone VARCHAR(45), IN firstname VARCHAR(150), IN udf1 VARCHAR(300), IN udf2 VARCHAR(300),
IN udf3 VARCHAR(300), IN udf4 VARCHAR(300), IN udf5 VARCHAR(300), IN udf6 VARCHAR(300), IN udf7 VARCHAR(300), IN surl VARCHAR(2000), IN furl VARCHAR(2000),
IN start_time VARCHAR(50), IN address1 VARCHAR(100), IN address2 VARCHAR(100), IN city VARCHAR(50),
IN state VARCHAR(50), IN country VARCHAR(50), IN zipcode VARCHAR(10), IN productinfo VARCHAR(45),
IN unique_id VARCHAR(255), IN amount FLOAT(15), IN isMobile INT(5), IN user_agent VARCHAR(200), IN device VARCHAR(45),
IN referrer_ip VARCHAR(45), IN referrer_url VARCHAR(2000),IN environment VARCHAR(4),IN split_payments VARCHAR(255),
IN request_flow VARCHAR(15),IN show_payment_mode VARCHAR(255),IN allowed_cards VARCHAR(255),
 OUT status VARCHAR(10),OUT message VARCHAR(100),OUT peb_transaction_id VARCHAR(15),OUT is_coupon_enabled TINYINT(1),OUT cashback_percent INT)
BEGIN

DECLARE merchant_id INT;
DECLARE is_subaggregator INT;
DECLARE submerchant_id INT;
DECLARE db_query VARCHAR(1000);
DECLARE customer_id INT;
DECLARE transaction_id INT;
DECLARE access_key VARCHAR(64);

SET status = "false";
SET access_key = generate_access_key(start_time);
SELECT mi.id,is_sub_aggregator,cashback_status,cashback_percentage INTO merchant_id ,is_subaggregator,is_coupon_enabled,cashback_percent from peb_merchant_info mi,peb_merchant_settings ms,peb_gateway_settings gs WHERE mi.merchant_settings_id = ms.id and mi.gateway_settings_id = gs.id and mi.key=merchant_key;

IF is_subaggregator = 1 THEN
SELECT smi.id,gs.cashback_status,gs.cashback_percentage INTO submerchant_id,is_coupon_enabled,cashback_percent FROM peb_submerchant_info smi,peb_gateway_settings gs WHERE smi.submerchant_id = sub_merchant_id AND smi.merchant_id = merchant_id AND smi.gateway_settings_id = gs.id AND is_active = 1 ;
END IF;

INSERT INTO peb_customer_request (merchant_txn_id, email, phone, first_name, product_info, access_key, is_done, timestamp, address1, address2, city, state, country, zipcode, additional1, additional2, additional3, additional4, additional5, additional6, additional7, surl, furl, start_time, is_coupon_enabled, coupon_list, merchant_id, unique_id,split_payments, request_flow, show_payment_mode, allowed_cards) VALUES (txnid,email,phone,firstname,productinfo,access_key,0,NOW(),address1,address2,city,state,country,zipcode,udf1,udf2,udf3,udf4,udf5,udf6,udf7,surl,furl,start_time,is_coupon_enabled,'[]',merchant_id,unique_id,split_payments,request_flow, show_payment_mode, allowed_cards);

SET customer_id = LAST_INSERT_ID();
SET peb_transaction_id = generate_transaction_id(environment);

IF submerchant_id = '' THEN SET submerchant_id = NULL;END IF;

INSERT INTO peb_transaction_info(
    peb_transaction_id,
    amount,
    peb_transaction_status,
    amount_tdr,
    peb_cash_back_percentage,
    peb_customer_id,
    peb_merchant_id,
    peb_submerchant_id,
    peb_is_mobile_app_initiated,
    peb_user_agent,
    peb_device,
    peb_referer_ip,
    peb_referer,
    peb_transaction_date,
    currency_type,
    transaction_ref_num,
    bank_ref_num,
    transaction_type,
    error_msg,
    checksum,
    peb_card_category,
    peb_payout_id,
    peb_pg_payout_id,
    retry_valid,
    peb_settlement_status,
    peb_chargeback_status,
    peb_put_in_sheet,
    pg_id_id,
    is_stos_updated,
    hdfc_transaction_status,
    peb_name_on_card,
    peb_upi_va,
    peb_card_number,
    peb_subaggregator_commission,
    payment_category,
    peb_refund_status 
) VALUES (
    peb_transaction_id,
    round(amount,2),
    'preinitiated',
    round(amount,2),
    cashback_percent,
    customer_id,
    merchant_id,
    submerchant_id,
    isMobile,
    user_agent,
    device,
    referrer_ip,
    referrer_url,
    start_time,
    'INR',
    'NA',
    'NA',
    'NA',
    'NA',
    'NA',
    'NA',
    NULL,
    '',
    0,
    'UT',
    'NC',
    0,
    NULL,
    0,
    'NA',
    'NA',
    'NA',
    NULL,
    0,
    'DEFAULT',
    ''
);

SET transaction_id = LAST_INSERT_ID();

SET status="true";
SET message=access_key;

END

```

---

<div class="post-metadata">

**Author:** ![Pep\_Pla](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/pep_pla/32/9735_2.png) [@Pep\_Pla](https://forums.percona.com/u/Pep_Pla)\
**Post date:** [December 14, 2022, 2:29pm UTC](https://forums.percona.com/t/triggers-on-mysql-replica-not-working-sometimes/19109/5 "2022-12-14T14:29:57Z")

</div>

Hi,

Looks like your issue can rise for two reasons:

- Bug [45677](https://bugs.mysql.com/bug.php?id=45677) was fixed by making procedure execution unsafe for inserts of more than one row where the table has autoincrement columns. It is also affected by triggers executed.
- The functions `generate_access_key` or `generate_transaction_id` are considered unsafe.

If any of those things happen, the procedure execution is considered unsafe and it is replicated using row format. Row format does not execute the triggers on the replica.

You can verify that the statements are considered unsafe by using `mysqlbinlog` utility to search for calls to the procedure in the binary logs.

Thank you!

Pep

---

<div class="post-metadata">

**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\
**Post date:** [December 14, 2022, 3:57pm UTC](https://forums.percona.com/t/triggers-on-mysql-replica-not-working-sometimes/19109/6 "2022-12-14T15:57:31Z")

</div>

@amol.gupta The best practice is to run in ROW format. This is the most efficient and lest performance impacting way. It is recommended always, to switch to ROW format. As Pep said, in ROW format, the triggers do not execute on replicas, but instead execute on the source as part of the overall transaction. The entire transaction result (rows + trigger result) is written to the binlog and replicated and reproduced on the replica.

---

<div class="post-metadata">

**Author:** ![amol.gupta](https://avatars.discourse-cdn.com/v4/letter/a/3bc359/32.png) [@amol.gupta](https://forums.percona.com/u/amol.gupta)\
**Post date:** [December 14, 2022, 4:04pm UTC](https://forums.percona.com/t/triggers-on-mysql-replica-not-working-sometimes/19109/7 "2022-12-14T16:04:40Z")

</div>

Thank you @Pep_Pla and @matthewb I will make a note of this.

It was really helpful and I will take this up and discuss with team.

Thanks

Amol