Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.0(EOL), 10.1(EOL), 10.2(EOL)
-
None
Description
Hi!
gtid_ignore_duplicates ignores statements even when GTID replication is not enabled. I can reproduce it and I can give you access to the VM's where I can reproduce it.
It seams that duplicate STATEMENTS always get ignored and unique STATEMENTS only get ignored when the slave gets restarted.
master config:
server_id=1
|
log-bin
|
replicate_ignore_db=mysql
|
binlog_format=STATEMENT
|
Slave config:
[mysqld]
|
server_id=2
|
gtid_ignore_duplicates=1
|
log-slave-updates
|
log-bin
|
Steps to reproduce:
1. Setup replication between two nodes
2. Create 10 test tables and insert data into them:
CREATE TABLE `test1` (
|
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
|
`b` int(10) unsigned NOT NULL,
|
`c` char(50) NOT NULL,
|
UNIQUE KEY `id` (`id`)
|
) ENGINE=MyISAM AUTO_INCREMENT=3580584 DEFAULT CHARSET=latin1;
|
CREATE TABLE test2 LIKE test1;
|
CREATE TABLE test3 LIKE test1;
|
CREATE TABLE test4 LIKE test1;
|
CREATE TABLE test5 LIKE test1;
|
CREATE TABLE test6 LIKE test1;
|
CREATE TABLE test7 LIKE test1;
|
CREATE TABLE test8 LIKE test1;
|
CREATE TABLE test9 LIKE test1;
|
CREATE TABLE test10 LIKE test1;
|
insert into test1 (b) values(1);
|
insert into test2 (b) values(1);
|
insert into test3 (b) values(1);
|
insert into test4 (b) values(1);
|
insert into test5 (b) values(1);
|
insert into test6 (b) values(1);
|
insert into test7 (b) values(1);
|
insert into test8 (b) values(1);
|
insert into test9 (b) values(1);
|
insert into test10 (b) values(1);
|
3. Put script.sh and start.sh
[root@test1 ~]# cat script.sh
|
#!/bin/bash
|
|
|
DATE=`date`
|
while true; do mysql mariadb -e "INSERT INTO mariadb.$1 (b, c) select RAND()*1000, \"$RANDOM $RANDOM $RANDOM $RANDOM $DATE $RANDOM\" FROM mariadb.$1 LIMIT 10000"; done
|
#!/bin/bash
|
|
|
./script.sh test1&
|
./script.sh test1&
|
./script.sh test1&
|
./script.sh test1&
|
./script.sh test1&
|
./script.sh test2&
|
./script.sh test2&
|
./script.sh test2&
|
./script.sh test2&
|
./script.sh test2&
|
./script.sh test2&
|
./script.sh test2&
|
./script.sh test3&
|
./script.sh test3&
|
./script.sh test3&
|
./script.sh test3&
|
./script.sh test3&
|
./script.sh test3&
|
./script.sh test3&
|
./script.sh test3&
|
./script.sh test3&
|
./script.sh test4&
|
./script.sh test4&
|
./script.sh test4&
|
./script.sh test4&
|
./script.sh test4&
|
./script.sh test4&
|
./script.sh test4&
|
./script.sh test4&
|
./script.sh test4&
|
./script.sh test4&
|
./script.sh test5&
|
./script.sh test5&
|
./script.sh test5&
|
./script.sh test5&
|
./script.sh test5&
|
./script.sh test5&
|
./script.sh test5&
|
./script.sh test5&
|
./script.sh test5&
|
./script.sh test5&
|
./script.sh test5&
|
./script.sh test6&
|
./script.sh test6&
|
./script.sh test6&
|
./script.sh test6&
|
./script.sh test6&
|
./script.sh test6&
|
./script.sh test6&
|
./script.sh test6&
|
./script.sh test6&
|
./script.sh test6&
|
./script.sh test6&
|
./script.sh test7&
|
./script.sh test7&
|
./script.sh test7&
|
./script.sh test7&
|
./script.sh test7&
|
./script.sh test7&
|
./script.sh test7&
|
./script.sh test7&
|
./script.sh test7&
|
./script.sh test7&
|
./script.sh test7&
|
./script.sh test8&
|
./script.sh test8&
|
./script.sh test8&
|
./script.sh test8&
|
./script.sh test8&
|
./script.sh test8&
|
./script.sh test8&
|
./script.sh test8&
|
./script.sh test9&
|
./script.sh test9&
|
./script.sh test9&
|
./script.sh test9&
|
./script.sh test9&
|
./script.sh test9&
|
./script.sh test9&
|
./script.sh test9&
|
./script.sh test10&
|
./script.sh test10&
|
./script.sh test10&
|
./script.sh test10&
|
./script.sh test10&
|
./script.sh test10&
|
./script.sh test10&
|
./script.sh test10&
|
./script.sh test10&
|
./script.sh test10&
|
4. Run start.sh
5. While this is running, start this script to restart the slave every minute:
while true; do mysql -e 'stop slave; set global slave_parallel_threads = 4 - @@slave_parallel_threads; start slave;'; date >> log3.log; for i in {0..10}; do ( echo -n $(date); mysql -e 'show slave status\G' | grep Seconds ) >> log3.log; sleep 2; done; done&
|
I am not sure if changing slave_parallel_threads has anything to do with it. I just realize it is still there after creating this report.
6. Observer the replication gets out of sync:
MariaDB [mariadb]> select id FROM test7 order by id desc limit 1;
|
+----------+
|
| id |
|
+----------+
|
| 11616384 |
|
+----------+
|
1 row in set (0.00 sec)
|
MariaDB [mariadb]> select count(*) FROM test7 where id <= 11616384 order by id desc limit 1;
|
+----------+
|
| count(*) |
|
+----------+
|
| 10606384 |
|
+----------+
|
1 row in set (7.02 sec)
|
On master:
MariaDB [mariadb]> select count(*) FROM test7 where id <= 11616384 order by id desc limit 1;
|
+----------+
|
| count(*) |
|
+----------+
|
| 11616384 |
|
+----------+
|
1 row in set (1 min 13.63 sec)
|
Attachments
Issue Links
- duplicates
-
-
- Closed
-
Activity
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);
|
}
|
knielsen I reviewed the patch here: http://lists.askmonty.org/pipermail/commits/2016-December/010145.html
You can push it.
And thank you, elenst, for getting to the bottom of this, filtering out the complexity of what I submitted.