Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Duplicate
-
10.5.9
-
None
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.
Attachments
Issue Links
- duplicates
-
-
- Closed
-
- relates to
-
-
- Closed
-
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:
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