|
Stopping and starting the slave configured to use file/position replication and having gtid_ignore_duplicates enabled, will cause events not to be applied.
GTID replication does not seem to be affected.
Test case:
CREATE TABLE `tbl` (
|
`Id` int(11) NOT NULL AUTO_INCREMENT,
|
`name` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
|
`InsertDate` datetime DEFAULT CURRENT_TIMESTAMP,
|
PRIMARY KEY (`Id`)
|
) ;
|
|
DROP PROCEDURE IF EXISTS usp_insert_data;
|
DELIMITER //
|
create PROCEDURE usp_insert_data()
|
begin
|
declare cnt int;
|
set cnt=1;
|
|
SET @@auto_increment_increment=1;
|
SET @@auto_increment_offset=1;
|
|
truncate table test.tbl;
|
|
while cnt < 100000
|
do
|
insert into test.tbl(name) select 'aaa';
|
set cnt=cnt+1;
|
end while;
|
end//
|
DELIMITER ;
|
|
CALL usp_insert_data;
|
On the slave, while the procedure is running on the master, we can see this:
MariaDB [test]> SELECT SLEEP(5) INTO @z; SELECT COUNT(*) AS "Starting..." FROM tbl; SELECT SLEEP(5) INTO @z; SELECT COUNT(*) AS "After 5 seconds" FROM tbl; STOP SLAVE; SELECT COUNT(*) AS "After STOPSLAVE" FROM tbl; SELECT SLEEP(15) INTO @z; START SLAVE; SELECT COUNT(*) AS "After STARTSLAVE" FROM tbl;
|
Query OK, 1 row affected (5.00 sec)
|
|
+-------------+
|
| Starting... |
|
+-------------+
|
| 12024 |
|
+-------------+
|
1 row in set (0.01 sec)
|
|
Query OK, 1 row affected (5.00 sec)
|
|
+-----------------+
|
| After 5 seconds |
|
+-----------------+
|
| 20575 |
|
+-----------------+
|
1 row in set (0.00 sec)
|
|
Query OK, 0 rows affected (0.03 sec)
|
|
+-----------------+
|
| After STOPSLAVE |
|
+-----------------+
|
| 20581 |
|
+-----------------+
|
1 row in set (0.00 sec)
|
|
Query OK, 1 row affected (15.00 sec)
|
|
Query OK, 0 rows affected (0.00 sec)
|
|
+------------------+
|
| After STARTSLAVE |
|
+------------------+
|
| 20581 |
|
+------------------+
|
1 row in set (0.01 sec)
|
|
MariaDB [test]> SELECT COUNT(*) AS totalMissing, MIN(missing.seq) AS start, MAX(missing.seq) AS end FROM seq_1_to_99999 AS missing LEFT JOIN tbl ON missing.seq = tbl.id WHERE tbl.id IS NULL;
|
|
+--------------+-------+-------+
|
| totalMissing | start | end |
|
+--------------+-------+-------+
|
| 7889 | 22423 | 30311 |
|
+--------------+-------+-------+
|
1 row in set (0.09 sec)
|
|
MariaDB [test]>
|
The relay logs seem to have all the events:
[root@fileposrepl slave]# mysqlbinlog relay.000004 | grep "SET INSERT_ID" | wc -l
|
30311
|
[root@fileposrepl slave]# mysqlbinlog relay.000005 | grep "SET INSERT_ID" | wc -l
|
69688
|
[root@fileposrepl slave]#
|
The workaround seems to just disable gtid_ignore_duplicates:
MariaDB [test]> SELECT @@gtid_ignore_duplicates;
|
+--------------------------+
|
| @@gtid_ignore_duplicates |
|
+--------------------------+
|
| 0 |
|
+--------------------------+
|
1 row in set (0.00 sec)
|
MariaDB [test]> SELECT COUNT(*) FROM tbl;
|
+----------+
|
| COUNT(*) |
|
+----------+
|
| 99999 |
|
+----------+
|
1 row in set (0.03 sec)
|
|
MariaDB [test]>
|
Thanks!
|