Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.0.21
-
Centos 7.1
-
Can result in unexpected behaviour
Description
I have two Mariadb 10.0.21 servers replicating from each others. log_slave_updates
is enabled everywhere to have full log information on each server.
A third Mariadb 10.0.21 server shall provide some filtered mysqlbinlog output to server1 to replicate it to server 2.
Without log_slave_updates on server 2 everything works fine.
A Table like
CREATE TABLE `Table1` (
`PKey` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`Wert1` INT(11) NOT NULL,
`Text1` VARCHAR(50) NOT NULL COLLATE 'utf8_bin',
`ConfCol` INT(11) NOT NULL DEFAULT '0',
`server_id` INT(11) NOT NULL DEFAULT 0,
`local_id` INT(11) NOT NULL DEFAULT 1,
`group_id` INT(11) NOT NULL DEFAULT 1,
PRIMARY KEY (`PKey`)
)
is identical on all servers, a dataset with PKey=104 doesn't exist on each server.
on server 1 with domainid=1 and serverid=1 before the error:
MariaDB [(none)]> show slave status\G;
|
*************************** 1. row ***************************
|
Slave_IO_State: Waiting for master to send event
|
Master_Host: 192.168.0.146
|
Master_User: replu
|
Master_Port: 3306
|
Connect_Retry: 60
|
Master_Log_File: mysql.000056
|
Read_Master_Log_Pos: 788
|
Relay_Log_File: cent7_1-relay-bin.000002
|
Relay_Log_Pos: 888
|
Relay_Master_Log_File: mysql.000056
|
Slave_IO_Running: Yes
|
Slave_SQL_Running: Yes
|
Replicate_Do_DB:
|
Replicate_Ignore_DB:
|
Replicate_Do_Table:
|
Replicate_Ignore_Table:
|
Replicate_Wild_Do_Table:
|
Replicate_Wild_Ignore_Table:
|
Last_Errno: 0
|
Last_Error:
|
Skip_Counter: 0
|
Exec_Master_Log_Pos: 788
|
Relay_Log_Space: 1187
|
Until_Condition: None
|
Until_Log_File:
|
Until_Log_Pos: 0
|
Master_SSL_Allowed: No
|
Master_SSL_CA_File:
|
Master_SSL_CA_Path:
|
Master_SSL_Cert:
|
Master_SSL_Cipher:
|
Master_SSL_Key:
|
Seconds_Behind_Master: 0
|
Master_SSL_Verify_Server_Cert: No
|
Last_IO_Errno: 0
|
Last_IO_Error:
|
Last_SQL_Errno: 0
|
Last_SQL_Error:
|
Replicate_Ignore_Server_Ids:
|
Master_Server_Id: 2
|
Master_SSL_Crl:
|
Master_SSL_Crlpath:
|
Using_Gtid: Current_Pos
|
Gtid_IO_Pos: 1-1-43748,2-2-58,4-4-72
|
1 row in set (0.00 sec)
|
 |
on server 2 with domainid=2 and serverid=2 before the error:
MariaDB [(none)]> show slave status\G;
|
*************************** 1. row ***************************
|
Slave_IO_State: Waiting for master to send event
|
Master_Host: 192.168.0.145
|
Master_User: replu
|
Master_Port: 3306
|
Connect_Retry: 60
|
Master_Log_File: mysql.000083
|
Read_Master_Log_Pos: 804
|
Relay_Log_File: cent7_2-relay-bin.000002
|
Relay_Log_Pos: 1087
|
Relay_Master_Log_File: mysql.000083
|
Slave_IO_Running: Yes
|
Slave_SQL_Running: Yes
|
Replicate_Do_DB:
|
Replicate_Ignore_DB:
|
Replicate_Do_Table:
|
Replicate_Ignore_Table:
|
Replicate_Wild_Do_Table:
|
Replicate_Wild_Ignore_Table:
|
Last_Errno: 0
|
Last_Error:
|
Skip_Counter: 0
|
Exec_Master_Log_Pos: 804
|
Relay_Log_Space: 1386
|
Until_Condition: None
|
Until_Log_File:
|
Until_Log_Pos: 0
|
Master_SSL_Allowed: No
|
Master_SSL_CA_File:
|
Master_SSL_CA_Path:
|
Master_SSL_Cert:
|
Master_SSL_Cipher:
|
Master_SSL_Key:
|
Seconds_Behind_Master: 0
|
Master_SSL_Verify_Server_Cert: No
|
Last_IO_Errno: 0
|
Last_IO_Error:
|
Last_SQL_Errno: 0
|
Last_SQL_Error:
|
Replicate_Ignore_Server_Ids:
|
Master_Server_Id: 1
|
Master_SSL_Crl:
|
Master_SSL_Crlpath:
|
Using_Gtid: Current_Pos
|
Gtid_IO_Pos: 1-1-43748,2-2-58,4-4-72
|
1 row in set (0.00 sec)
|
Inserting a dataset to the third server with domainid 4 and serverid 4 and pointing mysqlbinlog to the correct file and pos coordinates results in the output:
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
|
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
|
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
|
DELIMITER /*!*/;
|
# at 13442
|
#151117 11:47:23 server id 4 end_log_pos 0 Start: binlog v 4, server v 10.0.21-MariaDB-log created 151117 11:47:23
|
# at 13442
|
#151117 18:19:57 server id 4 end_log_pos 13480 GTID 4-4-73
|
/*!100001 SET @@session.gtid_domain_id=4*//*!*/;
|
/*!100001 SET @@session.server_id=4*//*!*/;
|
/*!100001 SET @@session.gtid_seq_no=73*//*!*/;
|
BEGIN
|
/*!*/;
|
# at 13480
|
# at 13534
|
#151117 18:19:57 server id 4 end_log_pos 13534 Table_map: `Test1`.`Table1` mapped to number 97
|
#151117 18:19:57 server id 4 end_log_pos 13598 Write_rows: table id 97 flags: STMT_END_F
|
### INSERT INTO `Test1`.`Table1`
|
### SET
|
### @1=104
|
### @2=7
|
### @3='HalloDuDa'
|
### @4=8
|
### @5=0
|
### @6=1
|
### @7=1
|
# at 13598
|
#151117 18:19:57 server id 4 end_log_pos 13625 Xid = 814
|
COMMIT/*!*/;
|
DELIMITER ;
|
# End of log file
|
ROLLBACK /* added by mysqlbinlog */;
|
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
|
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
|
parsed and applied on server 1 like :
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/
|
/*!40019 SET @@session.max_insert_delayed_threads=0*/
|
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/
|
/*!100001 SET @@session.gtid_domain_id=4*/
|
/*!100001 SET @@session.server_id=4*/
|
/*!100001 SET @@session.gtid_seq_no=73*/
|
START TRANSACTION
|
INSERT INTO `Test1`.`Table1` VALUES (104,7,'HalloDuDa',8,2,1,1)
|
COMMIT
|
ROLLBACK /* added by mysqlbinlog */
|
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/
|
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/
|
breaks replication on server 1:
MariaDB [(none)]> show slave status\G;
|
*************************** 1. row ***************************
|
Slave_IO_State: Waiting for master to send event
|
Master_Host: 192.168.0.146
|
Master_User: replu
|
Master_Port: 3306
|
Connect_Retry: 60
|
Master_Log_File: mysql.000056
|
Read_Master_Log_Pos: 971
|
Relay_Log_File: cent7_1-relay-bin.000002
|
Relay_Log_Pos: 888
|
Relay_Master_Log_File: mysql.000056
|
Slave_IO_Running: Yes
|
Slave_SQL_Running: No
|
Replicate_Do_DB:
|
Replicate_Ignore_DB:
|
Replicate_Do_Table:
|
Replicate_Ignore_Table:
|
Replicate_Wild_Do_Table:
|
Replicate_Wild_Ignore_Table:
|
Last_Errno: 1062
|
Last_Error: Could not execute Write_rows_v1 event on table Test1.Table1; Duplicate entry '104' for key 'PRIMAR Y', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql.000056, end_log_pos 944
|
Skip_Counter: 0
|
Exec_Master_Log_Pos: 788
|
Relay_Log_Space: 1370
|
Until_Condition: None
|
Until_Log_File:
|
Until_Log_Pos: 0
|
Master_SSL_Allowed: No
|
Master_SSL_CA_File:
|
Master_SSL_CA_Path:
|
Master_SSL_Cert:
|
Master_SSL_Cipher:
|
Master_SSL_Key:
|
Seconds_Behind_Master: NULL
|
Master_SSL_Verify_Server_Cert: No
|
Last_IO_Errno: 0
|
Last_IO_Error:
|
Last_SQL_Errno: 1062
|
Last_SQL_Error: Could not execute Write_rows_v1 event on table Test1.Table1; Duplicate entry '104' for key 'PRIMAR Y', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql.000056, end_log_pos 944
|
Replicate_Ignore_Server_Ids:
|
Master_Server_Id: 2
|
Master_SSL_Crl:
|
Master_SSL_Crlpath:
|
Using_Gtid: Current_Pos
|
Gtid_IO_Pos: 1-1-43748,2-2-58,4-4-73
|
1 row in set (0.00 sec)
|
server 2 is okay:
MariaDB [(none)]> show slave status\G;
|
*************************** 1. row ***************************
|
Slave_IO_State: Waiting for master to send event
|
Master_Host: 192.168.0.145
|
Master_User: replu
|
Master_Port: 3306
|
Connect_Retry: 60
|
Master_Log_File: mysql.000083
|
Read_Master_Log_Pos: 987
|
Relay_Log_File: cent7_2-relay-bin.000002
|
Relay_Log_Pos: 1270
|
Relay_Master_Log_File: mysql.000083
|
Slave_IO_Running: Yes
|
Slave_SQL_Running: Yes
|
Replicate_Do_DB:
|
Replicate_Ignore_DB:
|
Replicate_Do_Table:
|
Replicate_Ignore_Table:
|
Replicate_Wild_Do_Table:
|
Replicate_Wild_Ignore_Table:
|
Last_Errno: 0
|
Last_Error:
|
Skip_Counter: 0
|
Exec_Master_Log_Pos: 987
|
Relay_Log_Space: 1569
|
Until_Condition: None
|
Until_Log_File:
|
Until_Log_Pos: 0
|
Master_SSL_Allowed: No
|
Master_SSL_CA_File:
|
Master_SSL_CA_Path:
|
Master_SSL_Cert:
|
Master_SSL_Cipher:
|
Master_SSL_Key:
|
Seconds_Behind_Master: 0
|
Master_SSL_Verify_Server_Cert: No
|
Last_IO_Errno: 0
|
Last_IO_Error:
|
Last_SQL_Errno: 0
|
Last_SQL_Error:
|
Replicate_Ignore_Server_Ids:
|
Master_Server_Id: 1
|
Master_SSL_Crl:
|
Master_SSL_Crlpath:
|
Using_Gtid: Current_Pos
|
Gtid_IO_Pos: 1-1-43748,2-2-58,4-4-73
|
Both servers applied the Insert 4-4-73. gtid_ignore_duplicates=ON doesn't solve the problem. gtid_strict_mode=OFF.
Pointing Replicate_Ignore_Server_Ids to 4 helps but isn't an option since server 1 and
server 2 are part of a failover scenario.
Why does server 1 try to reapply the dataset from server 2 (log_slave_updates=ON)?