connection node_2; connection node_1; connection node_1; connection node_2; Performing State Transfer on a server that has been shut down cleanly and restarted connection node_1; CREATE TABLE t1 (id int not null primary key,f1 CHAR(255)) ENGINE=InnoDB; SET AUTOCOMMIT=OFF; START TRANSACTION; INSERT INTO t1 VALUES (1,'node1_committed_before'); INSERT INTO t1 VALUES (2,'node1_committed_before'); INSERT INTO t1 VALUES (3,'node1_committed_before'); INSERT INTO t1 VALUES (4,'node1_committed_before'); INSERT INTO t1 VALUES (5,'node1_committed_before'); COMMIT; connection node_2; SET AUTOCOMMIT=OFF; START TRANSACTION; INSERT INTO t1 VALUES (6,'node2_committed_before'); INSERT INTO t1 VALUES (7,'node2_committed_before'); INSERT INTO t1 VALUES (8,'node2_committed_before'); INSERT INTO t1 VALUES (9,'node2_committed_before'); INSERT INTO t1 VALUES (10,'node2_committed_before'); COMMIT; Shutting down server ... connection node_1; SET AUTOCOMMIT=OFF; START TRANSACTION; INSERT INTO t1 VALUES (11,'node1_committed_during'); INSERT INTO t1 VALUES (12,'node1_committed_during'); INSERT INTO t1 VALUES (13,'node1_committed_during'); INSERT INTO t1 VALUES (14,'node1_committed_during'); INSERT INTO t1 VALUES (15,'node1_committed_during'); COMMIT; START TRANSACTION; INSERT INTO t1 VALUES (16,'node1_to_be_committed_after'); INSERT INTO t1 VALUES (17,'node1_to_be_committed_after'); INSERT INTO t1 VALUES (18,'node1_to_be_committed_after'); INSERT INTO t1 VALUES (19,'node1_to_be_committed_after'); INSERT INTO t1 VALUES (20,'node1_to_be_committed_after'); connect node_1a_galera_st_shutdown_slave, 127.0.0.1, root, , test, $NODE_MYPORT_1; SET AUTOCOMMIT=OFF; START TRANSACTION; INSERT INTO t1 VALUES (21,'node1_to_be_rollbacked_after'); INSERT INTO t1 VALUES (22,'node1_to_be_rollbacked_after'); INSERT INTO t1 VALUES (23,'node1_to_be_rollbacked_after'); INSERT INTO t1 VALUES (24,'node1_to_be_rollbacked_after'); INSERT INTO t1 VALUES (25,'node1_to_be_rollbacked_after'); connection node_2; Starting server ... SET AUTOCOMMIT=OFF; START TRANSACTION; INSERT INTO t1 VALUES (26,'node2_committed_after'); INSERT INTO t1 VALUES (27,'node2_committed_after'); INSERT INTO t1 VALUES (28,'node2_committed_after'); INSERT INTO t1 VALUES (29,'node2_committed_after'); INSERT INTO t1 VALUES (30,'node2_committed_after'); COMMIT; connection node_1; INSERT INTO t1 VALUES (31,'node1_to_be_committed_after'); INSERT INTO t1 VALUES (32,'node1_to_be_committed_after'); INSERT INTO t1 VALUES (33,'node1_to_be_committed_after'); INSERT INTO t1 VALUES (34,'node1_to_be_committed_after'); INSERT INTO t1 VALUES (35,'node1_to_be_committed_after'); COMMIT; SET AUTOCOMMIT=OFF; START TRANSACTION; INSERT INTO t1 VALUES (36,'node1_committed_after'); INSERT INTO t1 VALUES (37,'node1_committed_after'); INSERT INTO t1 VALUES (38,'node1_committed_after'); INSERT INTO t1 VALUES (39,'node1_committed_after'); INSERT INTO t1 VALUES (40,'node1_committed_after'); COMMIT; connection node_1a_galera_st_shutdown_slave; INSERT INTO t1 VALUES (41,'node1_to_be_rollbacked_after'); INSERT INTO t1 VALUES (42,'node1_to_be_rollbacked_after'); INSERT INTO t1 VALUES (43,'node1_to_be_rollbacked_after'); INSERT INTO t1 VALUES (44,'node1_to_be_rollbacked_after'); INSERT INTO t1 VALUES (45,'node1_to_be_rollbacked_after'); ROLLBACK; SET AUTOCOMMIT=ON; SET SESSION wsrep_sync_wait=15; SELECT COUNT(*) AS EXPECT_15 FROM t1; EXPECT_15 35 SELECT * from t1; id f1 1 node1_committed_before 2 node1_committed_before 3 node1_committed_before 4 node1_committed_before 5 node1_committed_before 6 node2_committed_before 7 node2_committed_before 8 node2_committed_before 9 node2_committed_before 10 node2_committed_before 11 node1_committed_during 12 node1_committed_during 13 node1_committed_during 14 node1_committed_during 15 node1_committed_during 16 node1_to_be_committed_after 17 node1_to_be_committed_after 18 node1_to_be_committed_after 19 node1_to_be_committed_after 20 node1_to_be_committed_after 26 node2_committed_after 27 node2_committed_after 28 node2_committed_after 29 node2_committed_after 30 node2_committed_after 31 node1_to_be_committed_after 32 node1_to_be_committed_after 33 node1_to_be_committed_after 34 node1_to_be_committed_after 35 node1_to_be_committed_after 36 node1_committed_after 37 node1_committed_after 38 node1_committed_after 39 node1_committed_after 40 node1_committed_after SELECT COUNT(*) = 0 FROM (SELECT COUNT(*) AS c, f1 FROM t1 GROUP BY f1 HAVING c NOT IN (5, 10)) AS a1; COUNT(*) = 0 1 COMMIT; connection node_1; SET AUTOCOMMIT=ON; SET SESSION wsrep_sync_wait=15; SELECT COUNT(*) AS EXPECT_15 FROM t1; EXPECT_15 35 SELECT * from t1; id f1 1 node1_committed_before 2 node1_committed_before 3 node1_committed_before 4 node1_committed_before 5 node1_committed_before 6 node2_committed_before 7 node2_committed_before 8 node2_committed_before 9 node2_committed_before 10 node2_committed_before 11 node1_committed_during 12 node1_committed_during 13 node1_committed_during 14 node1_committed_during 15 node1_committed_during 16 node1_to_be_committed_after 17 node1_to_be_committed_after 18 node1_to_be_committed_after 19 node1_to_be_committed_after 20 node1_to_be_committed_after 26 node2_committed_after 27 node2_committed_after 28 node2_committed_after 29 node2_committed_after 30 node2_committed_after 31 node1_to_be_committed_after 32 node1_to_be_committed_after 33 node1_to_be_committed_after 34 node1_to_be_committed_after 35 node1_to_be_committed_after 36 node1_committed_after 37 node1_committed_after 38 node1_committed_after 39 node1_committed_after 40 node1_committed_after SELECT COUNT(*) = 0 FROM (SELECT COUNT(*) AS c, f1 FROM t1 GROUP BY f1 HAVING c NOT IN (5, 10)) AS a1; COUNT(*) = 0 1 DROP TABLE t1; COMMIT; Performing State Transfer on a server that starts from a clean var directory This is accomplished by shutting down node #2 and removing its var directory before restarting it connection node_1; CREATE TABLE t1 (id int not null primary key,f1 CHAR(255)) ENGINE=InnoDB; SET AUTOCOMMIT=OFF; START TRANSACTION; INSERT INTO t1 VALUES (1,'node1_committed_before'); INSERT INTO t1 VALUES (2,'node1_committed_before'); INSERT INTO t1 VALUES (3,'node1_committed_before'); INSERT INTO t1 VALUES (4,'node1_committed_before'); INSERT INTO t1 VALUES (5,'node1_committed_before'); COMMIT; connection node_2; SET AUTOCOMMIT=OFF; START TRANSACTION; INSERT INTO t1 VALUES (6,'node2_committed_before'); INSERT INTO t1 VALUES (7,'node2_committed_before'); INSERT INTO t1 VALUES (8,'node2_committed_before'); INSERT INTO t1 VALUES (9,'node2_committed_before'); INSERT INTO t1 VALUES (10,'node2_committed_before'); COMMIT; Shutting down server ... connection node_1; Cleaning var directory ... SET AUTOCOMMIT=OFF; START TRANSACTION; INSERT INTO t1 VALUES (11,'node1_committed_during'); INSERT INTO t1 VALUES (12,'node1_committed_during'); INSERT INTO t1 VALUES (13,'node1_committed_during'); INSERT INTO t1 VALUES (14,'node1_committed_during'); INSERT INTO t1 VALUES (15,'node1_committed_during'); COMMIT; START TRANSACTION; INSERT INTO t1 VALUES (16,'node1_to_be_committed_after'); INSERT INTO t1 VALUES (17,'node1_to_be_committed_after'); INSERT INTO t1 VALUES (18,'node1_to_be_committed_after'); INSERT INTO t1 VALUES (19,'node1_to_be_committed_after'); INSERT INTO t1 VALUES (20,'node1_to_be_committed_after'); connect node_1a_galera_st_clean_slave, 127.0.0.1, root, , test, $NODE_MYPORT_1; SET AUTOCOMMIT=OFF; START TRANSACTION; INSERT INTO t1 VALUES (21,'node1_to_be_rollbacked_after'); INSERT INTO t1 VALUES (22,'node1_to_be_rollbacked_after'); INSERT INTO t1 VALUES (23,'node1_to_be_rollbacked_after'); INSERT INTO t1 VALUES (24,'node1_to_be_rollbacked_after'); INSERT INTO t1 VALUES (25,'node1_to_be_rollbacked_after'); connection node_2; Starting server ... SET AUTOCOMMIT=OFF; START TRANSACTION; INSERT INTO t1 VALUES (26,'node2_committed_after'); INSERT INTO t1 VALUES (27,'node2_committed_after'); INSERT INTO t1 VALUES (28,'node2_committed_after'); INSERT INTO t1 VALUES (29,'node2_committed_after'); INSERT INTO t1 VALUES (30,'node2_committed_after'); COMMIT; connection node_1; INSERT INTO t1 VALUES (31,'node1_to_be_committed_after'); INSERT INTO t1 VALUES (32,'node1_to_be_committed_after'); INSERT INTO t1 VALUES (33,'node1_to_be_committed_after'); INSERT INTO t1 VALUES (34,'node1_to_be_committed_after'); INSERT INTO t1 VALUES (35,'node1_to_be_committed_after'); COMMIT; SET AUTOCOMMIT=OFF; START TRANSACTION; INSERT INTO t1 VALUES (36,'node1_committed_after'); INSERT INTO t1 VALUES (37,'node1_committed_after'); INSERT INTO t1 VALUES (38,'node1_committed_after'); INSERT INTO t1 VALUES (39,'node1_committed_after'); INSERT INTO t1 VALUES (40,'node1_committed_after'); COMMIT; connection node_1a_galera_st_clean_slave; INSERT INTO t1 VALUES (41,'node1_to_be_rollbacked_after'); INSERT INTO t1 VALUES (42,'node1_to_be_rollbacked_after'); INSERT INTO t1 VALUES (43,'node1_to_be_rollbacked_after'); INSERT INTO t1 VALUES (44,'node1_to_be_rollbacked_after'); INSERT INTO t1 VALUES (45,'node1_to_be_rollbacked_after'); ROLLBACK; SET AUTOCOMMIT=ON; SET SESSION wsrep_sync_wait=15; SELECT COUNT(*) AS EXPECT_35 FROM t1; EXPECT_35 35 SELECT * from t1; id f1 1 node1_committed_before 2 node1_committed_before 3 node1_committed_before 4 node1_committed_before 5 node1_committed_before 6 node2_committed_before 7 node2_committed_before 8 node2_committed_before 9 node2_committed_before 10 node2_committed_before 11 node1_committed_during 12 node1_committed_during 13 node1_committed_during 14 node1_committed_during 15 node1_committed_during 16 node1_to_be_committed_after 17 node1_to_be_committed_after 18 node1_to_be_committed_after 19 node1_to_be_committed_after 20 node1_to_be_committed_after 26 node2_committed_after 27 node2_committed_after 28 node2_committed_after 29 node2_committed_after 30 node2_committed_after 31 node1_to_be_committed_after 32 node1_to_be_committed_after 33 node1_to_be_committed_after 34 node1_to_be_committed_after 35 node1_to_be_committed_after 36 node1_committed_after 37 node1_committed_after 38 node1_committed_after 39 node1_committed_after 40 node1_committed_after SELECT COUNT(*) = 0 FROM (SELECT COUNT(*) AS c, f1 FROM t1 GROUP BY f1 HAVING c NOT IN (5, 10)) AS a1; COUNT(*) = 0 1 COMMIT; connection node_1; SET AUTOCOMMIT=ON; SET SESSION wsrep_sync_wait=15; SELECT COUNT(*) AS EXPECT_35 FROM t1; EXPECT_35 35 SELECT * from t1; id f1 1 node1_committed_before 2 node1_committed_before 3 node1_committed_before 4 node1_committed_before 5 node1_committed_before 6 node2_committed_before 7 node2_committed_before 8 node2_committed_before 9 node2_committed_before 10 node2_committed_before 11 node1_committed_during 12 node1_committed_during 13 node1_committed_during 14 node1_committed_during 15 node1_committed_during 16 node1_to_be_committed_after 17 node1_to_be_committed_after 18 node1_to_be_committed_after 19 node1_to_be_committed_after 20 node1_to_be_committed_after 26 node2_committed_after 27 node2_committed_after 28 node2_committed_after 29 node2_committed_after 30 node2_committed_after 31 node1_to_be_committed_after 32 node1_to_be_committed_after 33 node1_to_be_committed_after 34 node1_to_be_committed_after 35 node1_to_be_committed_after 36 node1_committed_after 37 node1_committed_after 38 node1_committed_after 39 node1_committed_after 40 node1_committed_after SELECT COUNT(*) = 0 FROM (SELECT COUNT(*) AS c, f1 FROM t1 GROUP BY f1 HAVING c NOT IN (5, 10)) AS a1; COUNT(*) = 0 1 DROP TABLE t1; COMMIT; disconnect node_2; disconnect node_1;