Issue Summary: Galera remote node encounter "Inconsistency detected" error when CTAS DDL with transaction fragment


We encountered node inconsistency issue in one of our Galera cluster.  After checking, the problem is related to CTAS "Create Table As Select" DDL.  We have Galera transaction fragment in the case.

A simple test case is prepared and no issue when transaction fragment is disabled.

We need transaction fragment enabled since vendor application need to run a large transaction (>2GB).

Test case is attached: galera-transation-fragment-ctas-ddl-inconsistent.txt

DB error log about the inconsistency: galera-transation-fragment-ctas-ddl-inconsistent.mysqld.node2.log


-- ##################################################################################################
## TOC
-- set galera cluster in centos 7
-- table preparation
-- enable transaction fragment "wsrep_trx_fragment_size"
-- #### test case: create table as select with 10,000 rows
-- #### test case: create table as select with 20,000 rows -- got node 2 inconsistent
-- disable transaction fragment "wsrep_trx_fragment_size"
-- #### test case: create table as select with 20,000 rows -- no issue


-- ##################################################################################################
-- set galera cluster in centos 7


#### install Galera and MariaDB rpm in 3 nodes.  MariaDB 10.6.7 and Galera-4 26.4.11 are used in this test case.


#### set IP in all 3 nodes.  Please change the IP according to your own environment
export NODE1_IP=192.168.159.131
export NODE2_IP=192.168.159.132
export NODE3_IP=192.168.159.133



#### node 1 

rm -rf /etc/my.cnf.d/galera.cnf
touch /var/log/mysqld.log ; chown mysql:mysql /var/log/mysqld.log

echo "[mysqld]"                                                                >> /etc/my.cnf.d/galera.cnf
echo "log_error=/var/log/mysqld.log"                                           >> /etc/my.cnf.d/galera.cnf
echo "default_storage_engine=InnoDB"                                           >> /etc/my.cnf.d/galera.cnf
echo "binlog_format=row"                                                       >> /etc/my.cnf.d/galera.cnf
echo "innodb_autoinc_lock_mode=2"                                              >> /etc/my.cnf.d/galera.cnf
echo ""                                                                        >> /etc/my.cnf.d/galera.cnf
echo "# Galera cluster configuration"                                          >> /etc/my.cnf.d/galera.cnf
echo "wsrep_on=ON"                                                             >> /etc/my.cnf.d/galera.cnf
echo "wsrep_provider=/usr/lib64/galera-4/libgalera_smm.so"                     >> /etc/my.cnf.d/galera.cnf
echo "wsrep_cluster_address=\"gcomm://${NODE1_IP},${NODE2_IP},${NODE3_IP}\" "  >> /etc/my.cnf.d/galera.cnf
echo "wsrep_cluster_name=mariadb-galera-cluster"                               >> /etc/my.cnf.d/galera.cnf
echo "wsrep_sst_method=mariabackup"                                            >> /etc/my.cnf.d/galera.cnf
echo "wsrep_sst_auth=\"mariabackup:mypassword123$\" "                          >> /etc/my.cnf.d/galera.cnf
echo ""                                                                        >> /etc/my.cnf.d/galera.cnf
echo "# Cluster node configuration"                                            >> /etc/my.cnf.d/galera.cnf
echo "wsrep_node_address=${NODE1_IP}"                                          >> /etc/my.cnf.d/galera.cnf
echo "wsrep_node_name=galera-db-01"                                            >> /etc/my.cnf.d/galera.cnf


systemctl stop    firewalld
systemctl disable firewalld

systemctl stop mariadb
systemctl set-environment _WSREP_NEW_CLUSTER='--wsrep-new-cluster'
systemctl start mariadb
systemctl unset-environment _WSREP_NEW_CLUSTER


mysql

CREATE USER 'mariabackup'@'localhost' IDENTIFIED BY 'mypassword123$';
GRANT RELOAD, PROCESS, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'mariabackup'@'localhost';



#### node 2

rm -rf /etc/my.cnf.d/galera.cnf
touch /var/log/mysqld.log ; chown mysql:mysql /var/log/mysqld.log

echo "[mysqld]"                                                                >> /etc/my.cnf.d/galera.cnf
echo "log_error=/var/log/mysqld.log"                                           >> /etc/my.cnf.d/galera.cnf
echo "default_storage_engine=InnoDB"                                           >> /etc/my.cnf.d/galera.cnf
echo "binlog_format=row"                                                       >> /etc/my.cnf.d/galera.cnf
echo "innodb_autoinc_lock_mode=2"                                              >> /etc/my.cnf.d/galera.cnf
echo ""                                                                        >> /etc/my.cnf.d/galera.cnf
echo "# Galera cluster configuration"                                          >> /etc/my.cnf.d/galera.cnf
echo "wsrep_on=ON"                                                             >> /etc/my.cnf.d/galera.cnf
echo "wsrep_provider=/usr/lib64/galera-4/libgalera_smm.so"                     >> /etc/my.cnf.d/galera.cnf
echo "wsrep_cluster_address=\"gcomm://${NODE1_IP},${NODE2_IP},${NODE3_IP}\" "  >> /etc/my.cnf.d/galera.cnf
echo "wsrep_cluster_name=mariadb-galera-cluster"                               >> /etc/my.cnf.d/galera.cnf
echo "wsrep_sst_method=mariabackup"                                            >> /etc/my.cnf.d/galera.cnf
echo "wsrep_sst_auth=\"mariabackup:mypassword123$\" "                          >> /etc/my.cnf.d/galera.cnf
echo ""                                                                        >> /etc/my.cnf.d/galera.cnf
echo "# Cluster node configuration"                                            >> /etc/my.cnf.d/galera.cnf
echo "wsrep_node_address=${NODE2_IP}"                                          >> /etc/my.cnf.d/galera.cnf
echo "wsrep_node_name=galera-db-02"                                            >> /etc/my.cnf.d/galera.cnf


systemctl stop    firewalld
systemctl disable firewalld

systemctl stop  mariadb
systemctl start mariadb


#### node 3 - arbitrator

systemctl stop    firewalld
systemctl disable firewalld


rm -rf /etc/garbd.cnf

echo "group=\"mariadb-galera-cluster\" "                         >> /etc/garbd.cnf
echo "address=\"gcomm://${NODE1_IP},${NODE2_IP},${NODE3_IP}\" "  >> /etc/garbd.cnf
echo "log=\"/var/log/garbd.log\" "                               >> /etc/garbd.cnf


garbd --cfg /etc/garbd.cnf


-- ##################################################################################################


-- ##################################################################################################
-- table preparation


create database testdb1 ;


create table testdb1.user_table1 ( id int primary key, name varchar(100), create_date datetime ) ;

insert into testdb1.user_table1 (id, name, create_date) values 
(  1, 'abcdefghijklmnopqrstuvwxyz-ABCDEFGHIJKLMNOPQRSTUVWXYZ-1' , STR_TO_DATE('2022-01-02 03:04:05', '%Y-%m-%d %H:%i:%s') ) , 
(  2, 'abcdefghijklmnopqrstuvwxyz-ABCDEFGHIJKLMNOPQRSTUVWXYZ-2' , STR_TO_DATE('2022-01-02 03:04:05', '%Y-%m-%d %H:%i:%s') ) , 
(  3, 'abcdefghijklmnopqrstuvwxyz-ABCDEFGHIJKLMNOPQRSTUVWXYZ-3' , STR_TO_DATE('2022-01-02 03:04:05', '%Y-%m-%d %H:%i:%s') ) , 
(  4, 'abcdefghijklmnopqrstuvwxyz-ABCDEFGHIJKLMNOPQRSTUVWXYZ-4' , STR_TO_DATE('2022-01-02 03:04:05', '%Y-%m-%d %H:%i:%s') ) , 
(  5, 'abcdefghijklmnopqrstuvwxyz-ABCDEFGHIJKLMNOPQRSTUVWXYZ-5' , STR_TO_DATE('2022-01-02 03:04:05', '%Y-%m-%d %H:%i:%s') ) , 
(  6, 'abcdefghijklmnopqrstuvwxyz-ABCDEFGHIJKLMNOPQRSTUVWXYZ-6' , STR_TO_DATE('2022-01-02 03:04:05', '%Y-%m-%d %H:%i:%s') ) , 
(  7, 'abcdefghijklmnopqrstuvwxyz-ABCDEFGHIJKLMNOPQRSTUVWXYZ-7' , STR_TO_DATE('2022-01-02 03:04:05', '%Y-%m-%d %H:%i:%s') ) , 
(  8, 'abcdefghijklmnopqrstuvwxyz-ABCDEFGHIJKLMNOPQRSTUVWXYZ-8' , STR_TO_DATE('2022-01-02 03:04:05', '%Y-%m-%d %H:%i:%s') ) , 
(  9, 'abcdefghijklmnopqrstuvwxyz-ABCDEFGHIJKLMNOPQRSTUVWXYZ-9' , STR_TO_DATE('2022-01-02 03:04:05', '%Y-%m-%d %H:%i:%s') ) , 
( 10, 'abcdefghijklmnopqrstuvwxyz-ABCDEFGHIJKLMNOPQRSTUVWXYZ-10', STR_TO_DATE('2022-01-02 03:04:05', '%Y-%m-%d %H:%i:%s') ) 
;
commit ;


insert into testdb1.user_table1 
select id + 10, name, create_date from testdb1.user_table1 union 
select id + 20, name, create_date from testdb1.user_table1 union 
select id + 30, name, create_date from testdb1.user_table1 union 
select id + 40, name, create_date from testdb1.user_table1 union 
select id + 50, name, create_date from testdb1.user_table1 union 
select id + 60, name, create_date from testdb1.user_table1 union 
select id + 70, name, create_date from testdb1.user_table1 union 
select id + 80, name, create_date from testdb1.user_table1 union 
select id + 90, name, create_date from testdb1.user_table1  
;
commit ;

insert into testdb1.user_table1 
select id + 100, name, create_date from testdb1.user_table1 union 
select id + 200, name, create_date from testdb1.user_table1 union 
select id + 300, name, create_date from testdb1.user_table1 union 
select id + 400, name, create_date from testdb1.user_table1 union 
select id + 500, name, create_date from testdb1.user_table1 union 
select id + 600, name, create_date from testdb1.user_table1 union 
select id + 700, name, create_date from testdb1.user_table1 union 
select id + 800, name, create_date from testdb1.user_table1 union 
select id + 900, name, create_date from testdb1.user_table1  
;
commit ;

insert into testdb1.user_table1 
select id + 1000, name, create_date from testdb1.user_table1 union 
select id + 2000, name, create_date from testdb1.user_table1 union 
select id + 3000, name, create_date from testdb1.user_table1 union 
select id + 4000, name, create_date from testdb1.user_table1 union 
select id + 5000, name, create_date from testdb1.user_table1 union 
select id + 6000, name, create_date from testdb1.user_table1 union 
select id + 7000, name, create_date from testdb1.user_table1 union 
select id + 8000, name, create_date from testdb1.user_table1 union 
select id + 9000, name, create_date from testdb1.user_table1  
;
commit ;


create table testdb1.user_table2 like testdb1.user_table1 ;



-- ##################################################################################################
-- enable transaction fragment "wsrep_trx_fragment_size"


-- set transaction fragment in node 1 & 2

SET global wsrep_trx_fragment_unit='bytes' ;
SET global wsrep_trx_fragment_size=1048576 ;


-- remember to reconnect mysql client


-- ##################################################################################################



-- ##################################################################################################
-- #### test case: create table as select with 10,000 rows

-- prepare  in node 1
truncate table testdb1.user_table2 ;
insert into testdb1.user_table2 select * from testdb1.user_table1 ;
commit ;

-- create table in node 1
drop table testdb1.user_table3 ;
create table testdb1.user_table3 select * from testdb1.user_table2 ;


-- check node 2 DB log
tail /var/log/mysqld.log
-- ##################################################################################################


-- ##################################################################################################
-- #### test case: create table as select with 20,000 rows -- got node 2 inconsistent

-- prepare in node 1
truncate table testdb1.user_table2 ;
insert into testdb1.user_table2 
select id        , name, create_date from testdb1.user_table1 union 
select id + 10000, name, create_date from testdb1.user_table1 
;
commit ;

-- create table in node 1
drop table testdb1.user_table3 ;
create table testdb1.user_table3 select * from testdb1.user_table2 ;


-- check node 2 DB log
tail /var/log/mysqld.log
-- ##################################################################################################


-- ##################################################################################################
-- disable transaction fragment "wsrep_trx_fragment_size"


-- set transaction fragment in node 1 & 2

SET global wsrep_trx_fragment_unit='bytes' ;
SET global wsrep_trx_fragment_size=0 ;


-- remember to reconnect mysql client


-- ##################################################################################################
-- #### test case: create table as select with 20,000 rows -- no issue

-- prepare in node 1
truncate table testdb1.user_table2 ;
insert into testdb1.user_table2 
select id        , name, create_date from testdb1.user_table1 union 
select id + 10000, name, create_date from testdb1.user_table1 
;
commit ;

-- create table in node 1
drop table testdb1.user_table3 ;
create table testdb1.user_table3 select * from testdb1.user_table2 ;


-- check node 2 DB log
tail /var/log/mysqld.log
-- ##################################################################################################