Bug #71376 restart of slave instance fail in GTID replication if we use replicate-ignore-db
Submitted: 14 Jan 2014 16:18 Modified: 12 Mar 2014 9:47
Reporter: Frédéric Condette Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Replication Severity:S1 (Critical)
Version:5.6.14, 5.6.15 OS:Linux
Assigned to: CPU Architecture:Any
Tags: GTID, purge binary logs, replicate-ignore-db, replication

[14 Jan 2014 16:18] Frédéric Condette
Description:
Our slave instance fails to restart its replication if we use the replicate-ignore-db parameter and make a purge binary logs on master with this error :
Got fatal error 1236 from master when reading data from binary log: 'The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires.'

This is due to a "hole" in the Executed_Gtid_Set when we submit a transaction like "Create database if not exists" for the ignored database

example of show slave status :
Executed_Gtid_Set: 6562ad37-7499-11e3-bb2e-005056a22534:1-11801:11804:11809:11812-11816:11819-11822:11824

I suppose that, when we restart the slave instance and restart the replication, mysql tries to find the missing transactions in master's binary logs.

How to repeat:
- don't replicate database "A" with : replicate-ignore-db=A
On the master :
* create database if not exists B;
* create database if not exists A;
* create database if not exists B;
* flush logs;
* purge binary logs to '[last binlog file]';

On the slave :
* Stop mysql server
* Start mysql server
* Start replication

Suggested fix:
The replication's process should consider that it has executed transactions that have been ignored to not make "holes" in the Executed_Gtid_Set and thus not to try to research the missing GTIDs.
[16 Jan 2014 19:59] Sveta Smirnova
Thank you for the report.

I can not repeat described behavior. In which exact time did you start and stop slave? Was it after PURGE command, after CREATE DATABASE or when? Please clarify.
[17 Jan 2014 10:14] Frédéric Condette
How to reproduce this problem

Attachment: Example_GTID.txt (text/plain), 8.58 KiB.

[17 Jan 2014 10:15] Frédéric Condette
Hello,

Thanks for your reply.

I have attached a file that described the whole procedure to reproduce this problem

Regards
[17 Jan 2014 10:49] MySQL Verification Team
Hello Frédéric,

Thank you for the bug report and test case.
Verified as described on 5.6.15.

Thanks,
Umesh
[17 Jan 2014 10:56] MySQL Verification Team
// Setup master/slave replication
// GTID replication started with

bin/mysqld_safe --defaults-file=./M71376.cnf --user=mysql --gtid_mode=ON --log-bin --log-slave-updates --enforce-gtid-consistency &

bin/mysqld_safe --defaults-file=./S71376.cnf --user=mysql --gtid_mode=ON --log-bin --log-slave-updates --enforce-gtid-consistency --sync-master-info=1 --master-info-repository=TABLE --relay-log-info-repository=TABLE --replicate-ignore-db=example &

// Rest steps followed from the testcase provided by reporter
// http://dev.mysql.com/doc/refman/5.6/en/replication-gtids-howto.html
[21 Jan 2014 6:25] MySQL Verification Team
Bug #71340 marked as duplicate of this one.
[24 Jan 2014 1:22] qinglin zhang
the reason of this is that : The purged_set of master is not a subset of the executed_set of slave due to ignored binlog event at  slave;
[12 Mar 2014 9:47] Jon Stephens
Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

Documented fix as follows in the MySQL 5.6.18 and 5.7.5 changelogs:

    When gtid_mode=ON, and a transaction is filtered out on the slave, 
    the GTID of the transaction is still logged on the slave as an 
    "empty" transaction (consisting of a GTID followed by BEGIN and
    then COMMIT). This is necessary to prevent the transaction from 
    being retransmitted the next time the slave reconnects or is 
    involved in a failover. The current fix addresses two issues 
    relating to such "empty" transactions:

        ·No empty transaction was generated for CREATE TEMPORARY 
        TABLE or DROP TEMPORARY TABLE statements.
        ·If the slave used a database filter (--replicate-do-db or
        --replicate-ignore-db option), no empty transaction was 
        generated.

Closed.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html
[15 May 2014 15:40] Van Stokes
OK. Since the "fix" hasn't been released yet, what is the solution for those of us running 5.6.17? How do we inform the slave to skip the holes?
[2 Jun 2014 13:50] Laurynas Biveinis
$ bzr log -r 5860
------------------------------------------------------------
revno: 5860
committer: Sven Sandberg <sven.sandberg@oracle.com>
branch nick: 5.6
timestamp: Tue 2014-03-11 16:02:15 +0100
message:
  Bug#18145032: NO EMPTY TRANSACTION IS CREATED FOR A FILTERED CREATE TEMPORARY TABLE WITH GTIDS
  Bug#18095502: NO EMPTY TRANSACTION IS CREATED FOR REPLICATE-IGNORE-DB OR REPLICATE-DO-DB
  
  Background:
  When gtid_mode=on, and a transaction is filtered out on the slave,
  the GTID of the transaction is still logged on the slave as a so-called
  empty transaction (just a GTID followed by BEGIN and then COMMIT). This
  is necessary to prevent the transaction from being re-transmitted next
  time the slave reconnects or does a fail-over.
  
  Symptom:
   1. If a CREATE TEMPORARY TABLE or DROP TEMPORARY TABLE statement was
      filtered out, no empty transaction was generated.
   2. If the slave has a database filter (--replicate-[do|ignore]-db),
      no empty transaction was generated.
  
  Analysis:
   1. Empty transactions are normally generated after a committing statement
      ends, i.e., after an explicit COMMIT statement or after a statement that
      causes an implicit commit.
  
      However, CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE have very
      special semantics when it comes to implicit commit. The statements do
      not have the implicit commit flag set and will not commit an ongoing
      transaction. However, they cannot be rolled back.
  
      So when executed out of transactional context, they effectively perform
      a commit after execution.
  
      When GTID_MODE=ON, these statements are only allowed outside
      transactional context, and they are not written to the binary log
      within BEGIN/COMMIT statements. Therefore, when such a statement
      has been executed and filtered out, an empty transaction must be
      explicitly committed.
  
      The reason for the bug is that the current condition for checking
      if empty transaction should be generated is that
      stmt_causes_implicit_commit returns true.
  
   2. Empty transactions are generated in mysql_parse. However,
      database filters were evaluated before mysql_parse was even
      called.
  
  Fix:
   1. When checking if an empty transaction should be generated, also check
      explicitly for CREATE/DROP TEMPORARY.
  
   2. Move database filter checking into mysql_parse.
  
  Further bugs:
   3. In the special case that a statement has an 'unexpected' error code
      according to the check in log_event.cc:unexpected_error_code(),
      mysql_parse() is not executed and instead mysql_test_parse_for_slave()
      is executed. This is for the sole purpose of parsing without executing,
      so that an error is only generated on the slave for non-filtered
      tables. Thus, when moving the filter rule checking from
      do_apply_event() into mysql_parse(), we also need to perform the same
      check in mysql_test_parse_for_slave().
  
  Test framework fixes:
  - To ease testing of this and other GTID-related bugs, I added the
    test framework files gtid_step_reset.inc and gtid_step_assert.inc.
    gtid_step_assert.inc asserts that the statements executed since
    last gtid_step_reset.inc generated a given number of GTIDs. This
    in turn needed some extra functions in gtid_utils.inc, and I also
    cleaned up and simplified gtid_utils a little and added a test
    case for it.
  - Small improvement in mysql-test/include/wait_for_slave_sql_to_stop.inc
    to allow comments after the error code(s).
[21 Jul 2014 12:12] Sveta Smirnova
Bug #70048 was marked as duplicate of this one.