MySQL Bugs: #71376: restart of slave instance fail in GTID replication if we use replicate-ignore-db

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

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.

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.

How to reproduce this problem

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

Hello,

Thanks for your reply.

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

Regards

Hello Frédéric,

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

Thanks,
Umesh

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

Bug #71340 marked as duplicate of this one.

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;

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

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?

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

Bug #70048 was marked as duplicate of this one.