[MDEV-6769] DROP TRIGGER IF NOT EXIST binlogged on master but not on slave - JIRA

Details

Type: Bug
Status: Closed
Priority: Major
Resolution: Fixed
Affects Version/s: 5.5.40, 10.0.14
Fix Version/s: 10.0.17
Component/s: Replication, Triggers
Labels:
None

Description

Consider this statement, when the trigger does not exist:

DROP TRIGGER IF EXISTS mytrig

This statement is binlogged on the master, but when replicated to a slave it
is not binlogged on the slave.

This causes binlogs to get out of sync and potentially breaking
replication. For example, when using GTID, if a slave stops after replicating
such a statement, it may not be able to connect to another server which is
missing that statement in its binlog.

Here is a test case that reproduces the problem:

--source include/have_innodb.inc
--let $rpl_topology=1->2,1->3
--source include/rpl_init.inc

connection server_1;

SET @old_strict= @@GLOBAL.gtid_strict_mode;
SET GLOBAL gtid_strict_mode=1;
CREATE TABLE t1 (a INT PRIMARY KEY, b VARCHAR(10)) ENGINE=InnoDB;
INSERT INTO t1 VALUES (1, "s1");

save_master_pos;

connection server_2;
sync_with_master;
--source include/stop_slave.inc
SET @old_strict= @@GLOBAL.gtid_strict_mode;
SET GLOBAL gtid_strict_mode=1;
CHANGE MASTER TO MASTER_USE_GTID=CURRENT_POS;
--source include/start_slave.inc

connection server_3;
sync_with_master;
--source include/stop_slave.inc
SET @old_strict= @@GLOBAL.gtid_strict_mode;
SET GLOBAL gtid_strict_mode=1;
CHANGE MASTER TO MASTER_USE_GTID=CURRENT_POS;
--source include/start_slave.inc


--connection server_1
# The bug is that this statement is binlogged on the master, but not on the
# slave. This means that the binlogs get out of sync, breaking GTID replication.
DROP TRIGGER IF EXISTS no_exist_trig;

SHOW BINLOG EVENTS;
SELECT @@GLOBAL.gtid_slave_pos;
SELECT @@GLOBAL.gtid_binlog_pos;
SELECT @@GLOBAL.gtid_current_pos;
--save_master_pos

--connection server_2
--sync_with_master

--source include/stop_slave.inc
SHOW BINLOG EVENTS;
SELECT @@GLOBAL.gtid_slave_pos;
SELECT @@GLOBAL.gtid_binlog_pos;
SELECT @@GLOBAL.gtid_current_pos;

--connection server_1
INSERT INTO t1 VALUES (2, "s1");
SELECT * FROM t1 ORDER BY a;
--save_master_pos

--connection server_3
--sync_with_master
SHOW BINLOG EVENTS;
SELECT @@GLOBAL.gtid_slave_pos;
SELECT @@GLOBAL.gtid_binlog_pos;
SELECT @@GLOBAL.gtid_current_pos;
--source include/save_master_gtid.inc

--connection server_2
# Switch S2 to use S3 as the master.
# Here we get the bug: S2's position is the GTID of DROP IF EXISTS, but this
# does not exist on S3, so we get an error (as we should) in strict mode.

--replace_result $SERVER_MYPORT_3 SERVER_MYPORT_3
eval CHANGE MASTER TO master_host = '127.0.0.1', master_port = $SERVER_MYPORT_3,
     MASTER_USE_GTID=CURRENT_POS;
--source include/start_slave.inc
--source include/sync_with_master_gtid.inc
SELECT * FROM t1 ORDER BY a;


# Clean up.
--connection server_2
--source include/stop_slave.inc
SET GLOBAL gtid_strict_mode= @old_strict;
--replace_result $SERVER_MYPORT_1 SERVER_MYPORT_1
eval CHANGE MASTER TO master_host = '127.0.0.1', master_port = $SERVER_MYPORT_1,
     MASTER_USE_GTID=CURRENT_POS;
--source include/start_slave.inc

--connection server_3
--source include/stop_slave.inc
SET GLOBAL gtid_strict_mode= @old_strict;
--source include/start_slave.inc

--connection server_1
DROP TABLE t1;
SET GLOBAL gtid_strict_mode= @old_strict;

--source include/rpl_end.inc

Requires the following to be put in <testname>.cnf:

!include ../my.cnf

[mysqld.1]
log-slave-updates
loose-innodb

[mysqld.2]
log-slave-updates
loose-innodb

[mysqld.3]
log-slave-updates
loose-innodb

[ENV]
SERVER_MYPORT_3=		@mysqld.3.port
SERVER_MYSOCK_3=		@mysqld.3.socket

Gliffy Diagrams

Attachments

Activity

Hide

Permalink

Kristian Nielsen added a comment - 2014-09-23 15:36 - edited

I noticed this code in mysql_execute_command():

  if (unlikely(thd->slave_thread))
  {
    if (lex->sql_command == SQLCOM_DROP_TRIGGER)
    {
      add_table_for_trigger(thd, thd->lex->spname, 1, &all_tables);
      if (!all_tables)
      {
        /*
          If table name cannot be loaded,
          it means the trigger does not exists possibly because
          CREATE TRIGGER was previously skipped for this trigger
          according to slave filtering rules.
          Returning success without producing any errors in this case.
        */
        DBUG_RETURN(0);
      }

This might be related, but I don't know much about the trigger code.

Show

Kristian Nielsen added a comment - 2014-09-23 15:36 - edited I noticed this code in mysql_execute_command(): if (unlikely(thd->slave_thread)) { if (lex->sql_command == SQLCOM_DROP_TRIGGER) { add_table_for_trigger(thd, thd->lex->spname, 1, &all_tables); if (!all_tables) { /* If table name cannot be loaded, it means the trigger does not exists possibly because CREATE TRIGGER was previously skipped for this trigger according to slave filtering rules. Returning success without producing any errors in this case. */ DBUG_RETURN(0); } This might be related, but I don't know much about the trigger code.

People

Assignee:

Sergei Golubchik

Reporter:

Kristian Nielsen

Votes:

0 Vote for this issue

Watchers:

1 Start watching this issue

Dates

Created:

2014-09-23 15:32

Updated:

2015-02-20 23:53

Resolved:

2015-02-20 23:53

Time Tracking

Estimated:

Not Specified

Remaining:

Logged:

Agile

View on Board