Description:
As per the mysql documentation,
https://dev.mysql.com/doc/refman/5.7/en/replication-features-drop-if-exists.html
"DROP ... IF EXISTS statements for stored programs (stored procedures and functions, triggers, and events) are also replicated, even if the stored program to be dropped does not exist on the master."
In replication, a slave with log-slave-updates=ON writes received statements from masters to its binary log.
But this not happening for DROP TRIGGER IF EXISTS statement when it's executed on the master.
DROP..IF EXISTS works as expected as documented for stored procedures and functions but not for the trigger.
How to repeat:
Setup master-slave replication:
on-slave
[mysqld]
log-bin=mysql-bin
log-slave-updates=ON
Test:
master [localhost] {msandbox} (test) > drop table if exists foo;
Query OK, 0 rows affected, 1 warning (0.01 sec)
master [localhost] {msandbox} (test) > drop trigger if exists foo;
Query OK, 0 rows affected, 1 warning (0.01 sec)
master [localhost] {msandbox} (test) > show warnings\G
*************************** 1. row ***************************
Level: Note
Code: 1360
Message: Trigger does not exist
1 row in set (0.00 sec)
master [localhost] {msandbox} (test) > drop function if exists foo;
Query OK, 0 rows affected, 1 warning (0.01 sec)
master [localhost] {msandbox} (test) > drop procedure if exists foo;
Query OK, 0 rows affected, 1 warning (0.01 sec)
master [localhost] {msandbox} (test) > SHOW BINLOG EVENTS IN 'mysql-bin.000003';
+------------------+-----+----------------+-----------+-------------+------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+------------------------------------------------------------------+
| mysql-bin.000003 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.27-log, Binlog ver: 4 |
| mysql-bin.000003 | 123 | Previous_gtids | 1 | 154 | |
| mysql-bin.000003 | 154 | Anonymous_Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000003 | 219 | Query | 1 | 345 | use `test`; DROP TABLE IF EXISTS `foo` /* generated by server */ |
| mysql-bin.000003 | 345 | Anonymous_Gtid | 1 | 410 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000003 | 410 | Query | 1 | 503 | use `test`; drop trigger if exists foo |
| mysql-bin.000003 | 503 | Anonymous_Gtid | 1 | 568 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000003 | 568 | Query | 1 | 662 | use `test`; drop function if exists foo |
| mysql-bin.000003 | 662 | Anonymous_Gtid | 1 | 727 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000003 | 727 | Query | 1 | 822 | use `test`; drop procedure if exists foo |
+------------------+-----+----------------+-----------+-------------+------------------------------------------------------------------+
10 rows in set (0.01 sec)
--- Missing entry for a trigger in slave binary log----
slave1 [localhost] {msandbox} ((none)) > SHOW BINLOG EVENTS IN 'mysql-bin.000003';
+------------------+-----+----------------+-----------+-------------+------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+------------------------------------------------------------------+
| mysql-bin.000003 | 4 | Format_desc | 101 | 123 | Server ver: 5.7.27-log, Binlog ver: 4 |
| mysql-bin.000003 | 123 | Previous_gtids | 101 | 154 | |
| mysql-bin.000003 | 154 | Anonymous_Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000003 | 219 | Query | 1 | 345 | use `test`; DROP TABLE IF EXISTS `foo` /* generated by server */ |
| mysql-bin.000003 | 345 | Anonymous_Gtid | 1 | 410 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000003 | 410 | Query | 1 | 504 | use `test`; drop function if exists foo |
| mysql-bin.000003 | 504 | Anonymous_Gtid | 1 | 569 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000003 | 569 | Query | 1 | 664 | use `test`; drop procedure if exists foo |
+------------------+-----+----------------+-----------+-------------+------------------------------------------------------------------+
8 rows in set (0.01 sec)
Suggested fix:
DROP..IF EXISTS should work the same way for the trigger with the given scenario as it works for stored procedures and functions.
