[MDEV-25653] Replication between two cluster fails on ADD PARTITION Created: 2021-05-11  Updated: 2021-06-11  Resolved: 2021-05-14

Status: Closed
Project: MariaDB Server
Component/s: Galera, Partitioning, Replication
Affects Version/s: 10.5.9
Fix Version/s: 10.4.19, 10.5.10

Type: Bug Priority: Major
Reporter: Hartmut Holzgraefe Assignee: Hartmut Holzgraefe
Resolution: Duplicate Votes: 2
Labels: None

Attachments: HTML File Vagrantfile     File galera.cnf    
Issue Links:
Duplicate
duplicates MDEV-24956 ALTER TABLE not replicated with Galer... Closed
Relates
relates to MDEV-24956 ALTER TABLE not replicated with Galer... Closed

 Description   

Having two Galera clusters, with async replication between them, an

ALTER TABLE ... ADD PARTITION

gets only applied on the actual replication slave node in the slave cluster, but not the other cluster node(s) in the slave cluster.

How to reproduce;

  • create two Galera clusters with most basic configuration
  • set up one node in the first cluster as replication master
  • set up one node in the 2nd cluster as replication slave
  • create a partitioned table
  • insert some rows
  • add a new partition
  • check SHOW CREATE TABLE on the slave node in the 2nd cluster -> the new partition is there
  • check SHOW CREATE TABLE on other node(s) in the 2nd cluster -> the partition is NOT there
  • insert a row on the 1st cluster that will end up in the newly added partition
  • the slave node in the 2nd cluster will insert that row just fine, but all other nodes in the cluster will fail as they are missing the new partition

Configuration:

[mysqld]
 
server-id=11
 
binlog-format=ROW
 
log-bin
 
log-slave-updates=1
 
gtid-domain-id=23
 
bind-address=0.0.0.0
 
wsrep_on=ON
 
wsrep_provider=/usr/lib/galera/libgalera_smm.so
 
wsrep_cluster_name=test_cluster_1
 
wsrep_cluster_address=gcomm://10.10.99.11,10.10.99.12,
 
wsrep_sst_method=mariabackup
 
wsrep_sst_auth=sstuser:secret
 
wsrep_node_address=10.10.99.11
 
wsrep_node_name=node-11

server_id, wsrep_node_address and wsrep_node_name differ by by node, and wsrep_cluster_name, wsrep_cluster_address differ between the two clusters.

The SQL statements I use for testing are:

CREATE TABLE t1
 
(
 
  id INT PRIMARY KEY,
 
  msg VARCHAR(100)
 
)
 
ENGINE=innodb CHARACTER SET latin1
 
PARTITION BY RANGE(id)
 
(
 
PARTITION p1 VALUES LESS THAN(10),
 
PARTITION p2 VALUES LESS THAN(20),
 
PARTITION p3 VALUES LESS THAN(30)
 
) ;
 
 
 
INSERT INTO t1 VALUES( 1,'abc');
 
INSERT INTO t1 VALUES(11,'abc');
 
INSERT INTO t1 VALUES(21,'abc');
 
 
 
ALTER TABLE t1 ADD PARTITION (PARTITION p4 VALUES LESS THAN (40));
 
 
 
INSERT INTO t1 VALUES(31,'abc');

The secondary node on the replicating cluster fails with the following messages on the last INSERT:

May 11 14:40:03 slave-2 mariadbd[4594]: 2021-05-11 14:40:03 1 [ERROR] Slave SQL: Could not execute Write_rows_v1 event on table test.t1; Table has no partition for value 31, Error_code: 1526; handler error HA_ERR_NO_PARTITIO
 
May 11 14:40:03 slave-2 mariadbd[4594]: 2021-05-11 14:40:03 1 [Warning] WSREP: Event 3 Write_rows_v1 apply failed: 160, seqno 10
 
May 11 14:40:03 slave-2 mariadbd[4594]: 2021-05-11 14:40:03 0 [Note] WSREP: Member 0(node-22) initiates vote on fc310aa4-b265-11eb-ab6f-5793db4dd292:10,aea788d19269c9dd:  Table has no partition for value 31, Error_code: 1526
 
May 11 14:40:03 slave-2 mariadbd[4594]: 2021-05-11 14:40:03 0 [Note] WSREP: Votes over fc310aa4-b265-11eb-ab6f-5793db4dd292:10:
 
May 11 14:40:03 slave-2 mariadbd[4594]:    aea788d19269c9dd:   1/2
 
May 11 14:40:03 slave-2 mariadbd[4594]: Waiting for more votes.
 
May 11 14:40:03 slave-2 mariadbd[4594]: 2021-05-11 14:40:03 0 [Note] WSREP: Member 1(node-21) responds to vote on fc310aa4-b265-11eb-ab6f-5793db4dd292:10,0000000000000000: Success
 
May 11 14:40:03 slave-2 mariadbd[4594]: 2021-05-11 14:40:03 0 [Note] WSREP: Votes over fc310aa4-b265-11eb-ab6f-5793db4dd292:10:
 
May 11 14:40:03 slave-2 mariadbd[4594]:    0000000000000000:   1/2
 
May 11 14:40:03 slave-2 mariadbd[4594]:    aea788d19269c9dd:   1/2
 
May 11 14:40:03 slave-2 mariadbd[4594]: Winner: 0000000000000000
 
May 11 14:40:03 slave-2 mariadbd[4594]: 2021-05-11 14:40:03 1 [ERROR] WSREP: Inconsistency detected: Inconsistent by consensus on fc310aa4-b265-11eb-ab6f-5793db4dd292:10
 
May 11 14:40:03 slave-2 mariadbd[4594]:          at /home/buildbot/buildbot/build/galera/src/replicator_smm.cpp:process_apply_error():1347

There are no log messages before that, so the ADD PARTITION got ignored silently. When doing SHOW CREATE TABLE on the actual slave in the 2nd cluster I see:

CREATE TABLE `t1` (
 
  `id` int(11) NOT NULL AUTO_INCREMENT,
 
  `msg` varchar(100) DEFAULT NULL,
 
  PRIMARY KEY (`id`)
 
) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=latin1
 
 PARTITION BY RANGE (`id`)
 
(PARTITION `p1` VALUES LESS THAN (10) ENGINE = InnoDB,
 
 PARTITION `p2` VALUES LESS THAN (20) ENGINE = InnoDB,
 
 PARTITION `p3` VALUES LESS THAN (30) ENGINE = InnoDB,
 
 PARTITION `p4` VALUES LESS THAN (40) ENGINE = InnoDB)

as expected, but on the other node I do not see the p4 partition:

CREATE TABLE `t1` (
 
  `id` int(11) NOT NULL AUTO_INCREMENT,
 
  `msg` varchar(100) DEFAULT NULL,
 
  PRIMARY KEY (`id`)
 
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=latin1
 
 PARTITION BY RANGE (`id`)
 
(PARTITION `p1` VALUES LESS THAN (10) ENGINE = InnoDB,
 
 PARTITION `p2` VALUES LESS THAN (20) ENGINE = InnoDB,
 
 PARTITION `p3` VALUES LESS THAN (30) ENGINE = InnoDB)

There was no log output of any kind at the point of time when the ADD PARTITION was executed, only when trying to actually insert data into the new partition the secondary node failed.

 Comments   
Comment by Hartmut Holzgraefe [ 2021-05-11 ]

I attached my Vagrant test setup, consisting of a Vagrantfile setting up all nodes and the replication between them, and a galera.cnf tempate file. For testing you'd need vagrant, virtualbox, and both the files in the same directory.

You can then start up everything with "vagrant up", and log into individual nodes with "vagrant ssh node-name".

node-name are:

  • master-1 for the node on the 1st cluster writing replication binlog
  • master-2 for the 2nd node in the 1st cluster
  • slave-1 for the node in the 2nd cluster replicating from master-1
  • slave-2 for the node in the 2nd cluster where the actual error happens.

After "vagrant up" has completed both clusters and replication between them is set up, but the test table is not created yet.

You can then log into the master with "vagrant ssh master-1" and execute the how-to-reproduce SQL from above, then log into slave-1 and slave-2 to see that the first replicated everything, but that the 2nd does not have the added partition, and fails upon the last INSERT statement

Comment by Mario Karuza (Inactive) [ 2021-05-13 ]

This was fixed with MDEV-24956

Comment by Hartmut Holzgraefe [ 2021-05-14 ]

Duplicate of MDEV-24956

Generated at Thu Feb 08 09:39:19 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.