Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Incomplete
-
10.4.23, 10.2
-
None
Description
|
|
DC1 DC2 DC3
|
|
A-B-C----GTID Replication---->D-E-F----GTID Replication---->G
|
|
|
[DC1] A,B,C 3 Node Galera cluster
|
[DC2] D,E,F 3 Node Galera Cluster
|
[DC3] G 1 Node Galera Cluster
|
|
|
DC1--DC2 C-->D GTID Replication
|
DC2--DC3 F-->G GTID Replication
|
|
Followed the basic rule for Enabling WSREP GTID Mode https://mariadb.com/kb/en/using-mariadb-gtids-with-mariadb-galera-cluster/#enabling-wsrep-gtid-mode.
Issue starts from here taking mysqldump backup on Node F while Node A under write load and restoring the same on Node G , lead to duplicate entry.
Node F
[root@vmc_f tmp]# mysqldump -u root -proot --master-data=2 --all-databases>/tmp/fullbackup.sql
|
[root@vmc_f tmp]# head -30 fullbackup.sql
|
-- MySQL dump 10.16 Distrib 10.2.32-MariaDB, for Linux (x86_64)
|
--
|
-- Host: localhost Database:
|
-- ------------------------------------------------------
|
-- Server version 10.2.32-MariaDB-log
|
|
|
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
|
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
|
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
|
/*!40101 SET NAMES utf8 */;
|
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
|
/*!40103 SET TIME_ZONE='+00:00' */;
|
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
|
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
|
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
|
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
|
|
|
--
|
-- Position to start replication or point-in-time recovery from
|
--
|
|
|
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql_bin_f.000001', MASTER_LOG_POS=330876;
|
|
|
--
|
-- GTID to start replication from
|
--
|
|
|
-- SET GLOBAL gtid_slave_pos='100-1-286,200-2-117';
|
|
|
--
|
|
|
|
|
[root@vmc_f tmp]# scp fullbackup.sql root@192.168.47.107:/tmp
|
root@192.168.47.107's password:
|
fullbackup.sql 100% 65KB 7.3MB/s 00:00
|
|
Node G
|
|
MariaDB [(none)]> stop slave;
|
Query OK, 0 rows affected (0.00 sec)
|
|
|
MariaDB [(none)]> reset slave;
|
Query OK, 0 rows affected (0.00 sec)
|
|
|
[root@vmc_g ~]# ls -ltrh /tmp/fullbackup.sql
|
-rw-r--r-- 1 root root 65K Mar 21 00:37 /tmp/fullbackup.sql
|
|
|
[root@vmc_g ~]# mysql -u root -proot </tmp/fullbackup.sql
|
|
|
MariaDB [(none)]> select count(*) from sbtest.sbtest1;
|
+----------+
|
| count(*) |
|
+----------+
|
| 140 |
|
+----------+
|
1 row in set (0.00 sec)
|
|
|
|
|
MariaDB [(none)]> select @@gtid_binlog_pos, @@gtid_binlog_state,@@gtid_current_pos,@@gtid_slave_pos;
|
+--------------------------------+--------------------------------+--------------------------------+----------------------+
|
| @@gtid_binlog_pos | @@gtid_binlog_state | @@gtid_current_pos | @@gtid_slave_pos |
|
+--------------------------------+--------------------------------+--------------------------------+----------------------+
|
| 100-1-1223,200-2-117,300-3-242 | 100-1-1223,200-2-117,300-3-242 | 100-1-1223,200-2-117,300-3-242 | 100-1-1223,200-2-117 |
|
+--------------------------------+--------------------------------+--------------------------------+----------------------+
|
1 row in set (0.00 sec)
|
|
|
MariaDB [(none)]> SET GLOBAL gtid_slave_pos='100-1-286,200-2-117';
|
Query OK, 0 rows affected, 1 warning (0.00 sec)
|
|
|
MariaDB [(none)]> show warnings;
|
+---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Level | Code | Message |
|
+---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Warning | 1948 | Specified value for @@gtid_slave_pos contains no value for replication domain 300. This conflicts with the binary log which contains GTID 300-3-242. If MASTER_GTID_POS=CURRENT_POS is used, the binlog position will override the new value of @@gtid_slave_pos |
|
+---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
1 row in set (0.00 sec)
|
|
|
|
|
MariaDB [(none)]> select @@gtid_binlog_pos, @@gtid_binlog_state,@@gtid_current_pos,@@gtid_slave_pos;
|
+--------------------------------+--------------------------------+-------------------------------+---------------------+
|
| @@gtid_binlog_pos | @@gtid_binlog_state | @@gtid_current_pos | @@gtid_slave_pos |
|
+--------------------------------+--------------------------------+-------------------------------+---------------------+
|
| 100-1-1223,200-2-117,300-3-242 | 100-1-1223,200-2-117,300-3-242 | 100-1-286,200-2-117,300-3-242 | 100-1-286,200-2-117 |
|
+--------------------------------+--------------------------------+-------------------------------+---------------------+
|
1 row in set (0.00 sec)
|
|
|
|
|
MariaDB [(none)]> change master to master_host='192.168.47.106', master_user='replica', master_password='replica', master_use_gtid=slave_pos;
|
Query OK, 0 rows affected (0.02 sec)
|
|
|
MariaDB [(none)]> start slave;
|
Query OK, 0 rows affected (0.01 sec)
|
|
|
MariaDB [(none)]> show slave status\G
|
*************************** 1. row ***************************
|
Slave_IO_State: Waiting for master to send event
|
Master_Host: 192.168.47.106
|
Master_User: replica
|
Master_Port: 3306
|
Connect_Retry: 60
|
Master_Log_File: mysql_bin_f.000001
|
Read_Master_Log_Pos: 1587649
|
Relay_Log_File: vmc_g-relay-bin.000002
|
Relay_Log_Pos: 817
|
Relay_Master_Log_File: mysql_bin_f.000001
|
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 sbtest.sbtest1; Duplicate entry '5085' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql_bin_f.000001, end_log_pos 43693
|
Skip_Counter: 0
|
Exec_Master_Log_Pos: 42853
|
Relay_Log_Space: 1406302
|
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 sbtest.sbtest1; Duplicate entry '5085' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql_bin_f.000001, end_log_pos 43693
|
Replicate_Ignore_Server_Ids:
|
Master_Server_Id: 2
|
Master_SSL_Crl:
|
Master_SSL_Crlpath:
|
Using_Gtid: Slave_Pos
|
Gtid_IO_Pos: 100-1-1223,200-2-117
|
Replicate_Do_Domain_Ids:
|
Replicate_Ignore_Domain_Ids:
|
Parallel_Mode: conservative
|
SQL_Delay: 0
|
SQL_Remaining_Delay: NULL
|
Slave_SQL_Running_State:
|
1 row in set (0.00 sec)
|
|
|
|
|
MariaDB [(none)]> select * from sbtest.sbtest1 where id=5085;
|
+------+------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
|
| id | k | c | pad |
|
+------+------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
|
| 5085 | 5037 | 39476658110-32559687743-47203182949-51304521021-75485891409-50717849795-47190185045-16047806543-87533311936-70471326632 | 52490813876-70767371353-94467376165-15821586959-80410098309 |
|
+------+------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
|
1 row in set (0.00 sec)
|
|
|
MariaDB [(none)]> select count(*) from sbtest.sbtest1;
|
+----------+
|
| count(*) |
|
+----------+
|
| 140 |
|
+----------+
|
1 row in set (0.00 sec)
|
|
Attachments
Issue Links
- blocks
-
-
- Closed
-