michaeldg,

In the description, you refer to "duplicate statements" and "unique statements". What do you mean by that?

Since it is in the context of gtid_ignore_duplicates, I would assume that you are talking about duplicate GTIDs. But even if the option is wrongly applied when GTID is not enabled, in the described scenario there should be no GTID duplicates. You have one master and one replication channel, where the duplicates come from?

Please also specify the exact command that you use to "setup replication between two nodes".

elenst, sorry for the late reply here.

With duplicate statements I mean:
UPDATE table SET a=a+1 WHERE id=1
UPDATE table SET a=a+1 WHERE id=1
UPDATE table SET a=a+1 WHERE id=1

With unique statements I mean:
UPDATE table SET a=a+1,b="023033 random from bash 312133" WHERE id=1
UPDATE table SET a=a+1,b="342442423 random from bash 5434453" WHERE id=1
UPDATE table SET a=a+1,b="9767887 random from bash 988778" WHERE id=1

GTID is the funny thing about this. I do not use it and still gtid_ignore_duplicates has effect. So I'm not talking about duplicate ID's

I set up replication by:
RESET MASTER;
GRANT REPLICATION SLAVE ON . to 'repl'@'%' identified by 'repl';

CHANGE MASTER TO MASTER_HOST='machine', MASTER_USER='repl', MASTER_PASSWORD='repl';
START SLAVE;

I verified that master_use_gitd=no; the machines were all completely fresh.

I did not verify if this occurs with GTID SBR as well.

Let me know if you have any more questions!

It seems likely that something else is going on here.

As Elena wrote there are no duplicate gtids here. But you are using MyISAM tables in a scary way:

INSERT INTO t_myisam SELECT ... FROM t_myisam LIMIT 10000

I wonder if there is any guarantee that this will work correctly? The problem is if the locking is enough to ensure that the LIMIT will select the same number of rows? Probably not the same rows, though that should in principle not matter. It could also be a limitation/bug with parallel replication and MyISAM.

Does the problem still occur if gtid_ignore_duplicates=off? Does it occur if using InnoDB tables?

knielsen, the problem does not occur if gtid_ignore_duplicates is off.

I did not check if it also happened with InnoDB tables. elenst, could you try to reproduce it? I am off for customers the next 4 weeks.

michaeldg, nice catch, it is very much reproducible.

Even though, indeed, there are no duplicates of any kind, replication seems to misbehave badly when gtid_ignore_duplicates=1 and replication does not actually use GTID.

That is:

• gtid_ignore_duplicates=0, MASTER_USE_GTID=no - OK
• gtid_ignore_duplicates=0, MASTER_USE_GTID=current_pos (or slave_pos) - OK
• gtid_ignore_duplicates=1, MASTER_USE_GTID=current_pos (or slave_pos) - OK
• gtid_ignore_duplicates=1, MASTER_USE_GTID=no - PROBLEM

Parallel replication is irrelevant.
Identical statements are irrelevant.
Concurrency on master is irrelevant.
replicate_ignore_db is irrelevant.

The basic scenario is:

• master produces a number of binlog events;
• slave (configured with gtid_ignore_duplicates) starts replicating, everything goes well;
• STOP SLAVE / START SLAVE is executed on the slave – it skips events after that.

--source include/master-slave.inc

--source include/have_binlog_format_statement.inc

--let $count= 2000

--connection slave

--source include/stop_slave.inc

SET GLOBAL gtid_ignore_duplicates = 1;

CHANGE MASTER TO master_use_gtid=no;

--connection master

CREATE TABLE t1 (i INT);

--let $run= $count

--echo # Run $run INSERT statements

--disable_query_log

while ($run)

{

    --eval INSERT INTO t1 VALUES ($run)

    --dec $run

}

--enable_query_log

SELECT COUNT(*) FROM t1;

--save_master_pos

--connection slave

--source include/start_slave.inc

--source include/stop_slave.inc

--source include/start_slave.inc

--sync_with_master

--echo #

--echo # Here we synchronized with master and should have $count records:

--echo #

SELECT COUNT(*) FROM t1;

--echo #

--echo # Checking the status

--echo #

--let $status_items= Read_Master_Log_Pos,Exec_Master_Log_Pos,Using_Gtid,Slave_IO_Running,Slave_SQL_Running

--source include/show_slave_status.inc

# Cleanup

--connection master

DROP TABLE t1;

--sync_slave_with_master

--source include/rpl_end.inc

--connection slave

SET GLOBAL gtid_ignore_duplicates = DEFAULT;

knielsen, do you want to take it?

Ok, now I see it. So this is a race between the IO thread connect and the SQL
thread when not using GTID.

In this case, the IO thread overwrites the slave gtid position with the gtid
position of the IO thread. This causes skip of any transactions fetched
earlier but not yet applied.

I wonder if the solution is to just disable --gtid-ignore-duplicates in
non-gtid mode?

http://lists.askmonty.org/pipermail/commits/2016-December/010145.html

That patch fixes the problem from the test case.

It seems kind of reasonable that --gtid-ignore-duplicates should only do
something when master_use_gtid!=no, but I still think such a change in 10.0
requires a review. Elena, you being still on the inside, maybe you can find
a reviewer...

• Kristian.

knielsen, do I understand correctly that with this patch the value of the variable will remain intact, it just will be ignored if GTIDs are not used for replication?

Just clarifying because when I read "disable --gtid-ignore-duplicates in non-gtid mode" first, it sounded to me like the variable would be forcefully unset, which would be a wrong thing to do; but I don't see anything like that in the patch.

> do I understand correctly that with this patch the value of the variable
> will remain intact, it just will be ignored if GTIDs are not used for
> replication?

Correct.

Specifically, with the patch, if a slave-master connection has
MASTER_USE_GTID=no, then that connection will ignore any
--gtid-ignore-duplicates=1 setting. While another slave-master connection
with MASTER_USE_GTID=slave_pos|current_pos will still honor
--gtid-ignore-duplicates.

knielsen, thanks.

plinux, could you please review the patch?

Hi everybody!

Sorry to bother you but I think a little more investigation may be required. Does this occur due to GTID not being enabled? Can we guarantee that this does not occur if GTID is enabled?

Thanks,
Mcihaël

And thank you, elenst, for getting to the bottom of this, filtering out the complexity of what I submitted.

This particular bug will not occur when GTID is enabled, because the
offending code in the IO thread that overwrites the slave position is only
run in non-GTID case:

  if (mi->using_gtid != Master_info::USE_GTID_NO)

    ...

  else

  {

    /*

      If we are not using GTID to connect this time, then instead request

      the corresponding GTID position from the master, so that the user

      can reconnect the next time using MASTER_GTID_POS=AUTO.

    query.append("SELECT binlog_gtid_pos('");

    if (!mysql_real_query(mysql, query.c_ptr_safe(), query.length()) &&

        (master_res= mysql_store_result(mysql)) &&

        (master_row= mysql_fetch_row(master_res)) &&

        (master_row[0] != NULL))

    {

      rpl_global_gtid_slave_state->load(mi->io_thd, master_row[0],

                                        strlen(master_row[0]), false, false);

    }

Ah ok, excellent. Thank you knielsen!

knielsen I reviewed the patch here: http://lists.askmonty.org/pipermail/commits/2016-December/010145.html
You can push it.

Pushed to 10.0