[MDEV-27806] Galera crash when "create a table as select" Created: 2022-02-11  Updated: 2023-12-12  Resolved: 2023-12-12

Status: Closed
Project: MariaDB Server
Component/s: Data Definition - Create Table, Galera, Replication
Affects Version/s: 10.5.12, 10.6.5, 10.4
Fix Version/s: 10.4.33, 10.5.24, 10.6.17, 10.11.7, 11.0.5, 11.1.4, 11.2.3

Type: Bug Priority: Major
Reporter: yongjian.wu Assignee: Julius Goryavsky
Resolution: Fixed Votes: 0
Labels: crash, galera
Environment:

Galera cluster

Issue Links:
Duplicate
is duplicated by MDEV-32858 CTAS with non-empty table is crashing... Closed
Relates
relates to MDEV-25970 SIGSEGV in __memcmp_avx2_movbe Open
relates to MDEV-32780 galera_as_slave_replay: assertion in ... In Testing

 Description   

Hi
i met a trouble could you please help?
when i use the query create table as select * from; the galera db nodes crash.
but there are some case if the table is null the query can be execute success but if the table have rows the query can be success on the current node but crash on the others.

is there any config to avoid this ,or this is a limitation in the mariadb galera?

please help check the below:

no problem

 
mysql@galeradb1:~$ mysql -uroot -p
 
Enter password:
 
Welcome to the MySQL monitor.  Commands end with ; or \g.
 
Your MySQL connection id is 22
 
Server version: 5.5.5-10.5.12-MariaDB-debug-log Source distribution
 
 
 
 
 
 
 
(root@localhost) [cmsdb] 01:42:48> select count(*) from lbtest2;
 
+----------+
 
| count(*) |
 
+----------+
 
|       10 |
 
+----------+
 
1 row in set (0.01 sec)
 
 
 
(root@localhost) [cmsdb] 01:42:50> select count(*) from lbtest3;
 
+----------+
 
| count(*) |
 
+----------+
 
|        0 |
 
+----------+
 
1 row in set (0.01 sec)
 
 
 
(root@localhost) [cmsdb] 01:42:52> create table test1 as select * from lbtest3;
 
Query OK, 0 rows affected (0.02 sec)
 
Records: 0  Duplicates: 0  Warnings: 0

no problem ^^

problem

 
(root@localhost) [cmsdb] 01:43:08> create table test2 as select * from lbtest2;
 
Query OK, 10 rows affected (0.02 sec)
 
Records: 10  Duplicates: 0  Warnings: 0


2022-02-11  1:43:53 2 [ERROR] mysqld: Error writing file '/opt/maria10.1/binlog/BINLOG' (errno: 1950 "Unknown error 1950")
 
mysqld: /opt/newsource/mariadb-10.5.12/wsrep-lib/src/transaction.cpp:665: int wsrep::transaction::before_rollback(): Assertion `state() == s_executing || state() == s_preparing || state() == s_prepared || state() == s_must_abort || state() == s_aborting || state() == s_cert_failed || state() == s_must_replay' failed.
 
220211  1:43:53 [ERROR] mysqld got signal 6 ;


(root@localhost) [cmsdb] 01:50:48> select count(*) from test111;
 
+----------+
 
| count(*) |
 
+----------+
 
|        1 |
 
+----------+
 
1 row in set (0.01 sec)
 
 
 
(root@localhost) [cmsdb] 01:51:06> create table test1111 as select * from test111;
 
Query OK, 1 row affected (0.02 sec)
 
Records: 1  Duplicates: 0  Warnings: 0


2022-02-11  1:51:28 2 [ERROR] mariadbd: Error writing file '/opt/maria10.1/binlog/BINLOG' (errno: 1950 "Unknown error 1950")
 
mariadbd: /opt/newsource/mariadb-10.5.12/wsrep-lib/src/transaction.cpp:665: int wsrep::transaction::before_rollback(): Assertion `state() == s_executing || state() == s_preparing || state() == s_prepared || state() == s_must_abort || state() == s_aborting || state() == s_cert_failed || state() == s_must_replay' failed.
 
220211  1:51:28 [ERROR] mysqld got signal 6 ;



 Comments   
Comment by Ramesh Sivaraman [ 2023-11-22 ]

janlindstrom Reproduced the issue on latest 10.5.24 build. CTAS with non-empty table is crashing Galera secondary nodes. The crash occurs when secondary nodes try to write CTAS to the binlog.

create table t1(id int);
 
insert into t1 select seq from seq_1_to_10 ;
 
CREATE TABLE t2 AS SELECT * FROM t1;

Leads to

10.5.24 de31ca6a212118bd29876f964497050e446bda02 (Debug)

 
2023-11-22 10:03:49 17 [ERROR] mysqld: Error writing file 'binlog' (errno: 1950 "Unknown error 1950")
 
2023-11-22 10:03:49 17 [Note] WSREP: log_and_order has failed 17 0
 
mysqld: /test/10.5_dbg/wsrep-lib/src/transaction.cpp:659: int wsrep::transaction::before_rollback(): Assertion `state() == s_executing || state() == s_preparing || state() == s_prepared || state() == s_must_abort || state() == s_aborting || state() == s_cert_failed || state() == s_must_replay' failed.
 
231122 10:03:49 [ERROR] mysqld got signal 6 ;
 
Sorry, we probably made a mistake, and this is a bug.

CTAS binlog event from secondary node

#231122 10:03:49 server id 101  end_log_pos 908 CRC32 0x85386dfc        GTID 200-101-4 ddl
 
/*!100001 SET @@session.gtid_seq_no=4*//*!*/;
 
# at 908
 
#231122 10:03:49 server id 101  end_log_pos 1029 CRC32 0xe3c0fb1e       Query   thread_id=22    exec_time=0     error_code=0
 
SET TIMESTAMP=1700640229/*!*/;
 
SET @@session.auto_increment_increment=3, @@session.auto_increment_offset=1/*!*/;
 
CREATE TABLE `t2` (
 
  `id` int(11) DEFAULT NULL
 
)
 
/*!*/;
 
DELIMITER ;
 
# End of log file
 
ROLLBACK /* added by mysqlbinlog */;
 
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
 
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

CTAS binlog event from primary node

#231122 10:03:49 server id 101  end_log_pos 948 CRC32 0xe77d9c4d        GTID 200-101-4 ddl
 
/*!100001 SET @@session.gtid_seq_no=4*//*!*/;
 
START TRANSACTION
 
/*!*/;
 
# at 948
 
#231122 10:03:49 server id 101  end_log_pos 1069 CRC32 0xca9fe797       Query   thread_id=22    exec_time=0     error_code=0
 
SET TIMESTAMP=1700640229/*!*/;
 
SET @@session.auto_increment_increment=3, @@session.auto_increment_offset=1/*!*/;
 
CREATE TABLE `t2` (
 
  `id` int(11) DEFAULT NULL
 
)
 
/*!*/;
 
# at 1069
 
# at 1127
 
#231122 10:03:49 server id 101  end_log_pos 1127 CRC32 0x6d6742d0       Annotate_rows:
 
#Q> CREATE TABLE t2 AS SELECT * FROM t1
 
#231122 10:03:49 server id 101  end_log_pos 1172 CRC32 0xd3af8157       Table_map: `test`.`t2` mapped to number 30
 
# at 1172
 
#231122 10:03:49 server id 101  end_log_pos 1255 CRC32 0x663e7ea4       Write_rows: table id 30 flags: STMT_END_F
 
 
 
BINLOG '
 
5bVdZRNlAAAALQAAAJQEAAAAAB4AAAAAAAEABHRlc3QAAnQyAAEDAAFXga/T
 
5bVdZRdlAAAAUwAAAOcEAAAAAB4AAAAAAAEAAf/+AQAAAP4CAAAA/gMAAAD+BAAAAP4FAAAA/gYA
 
AAD+BwAAAP4IAAAA/gkAAAD+CgAAAKR+PmY=
 
'/*!*/;
 
# Number of rows: 10
 
# at 1255
 
#231122 10:03:49 server id 101  end_log_pos 1286 CRC32 0xb1f05fc2       Xid = 7
 
COMMIT/*!*/;
 
DELIMITER ;
 
# End of log file
 
ROLLBACK /* added by mysqlbinlog */;
 
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
 
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

Comment by Ramesh Sivaraman [ 2023-11-22 ]

janlindstrom Could not reproduce the issue on latest 10.4 build, but after executing CTAS on primary node gtid increased twice on secondary node
primary node

10.4.33 e39c497c809511bcc37a658405c7aa4b5be2cf6a (Debug)

 
10.4.33-dbg>show variables like 'gtid_binlog_pos';
 
+-----------------+-----------+
 
| Variable_name   | Value     |
 
+-----------------+-----------+
 
| gtid_binlog_pos | 200-101-3 |
 
+-----------------+-----------+
 
1 row in set (0.001 sec)
 
 
 
10.4.33-dbg>CREATE TABLE t2 AS SELECT * FROM t1;
 
Query OK, 10 rows affected (0.029 sec)
 
Records: 10  Duplicates: 0  Warnings: 0
 
 
 
10.4.33-dbg>show variables like 'gtid_binlog_pos';
 
+-----------------+-----------+
 
| Variable_name   | Value     |
 
+-----------------+-----------+
 
| gtid_binlog_pos | 200-101-4 |
 
+-----------------+-----------+
 
1 row in set (0.001 sec)
 
 
 
10.4.33-dbg>

Secondary node

node2:root@localhost> show variables like 'gtid_binlog_pos';
 
+-----------------+-----------+
 
| Variable_name   | Value     |
 
+-----------------+-----------+
 
| gtid_binlog_pos | 200-101-3 |
 
+-----------------+-----------+
 
1 row in set (0.001 sec)
 
 
 
node2:root@localhost> show variables like 'gtid_binlog_pos';
 
+-----------------+-----------+
 
| Variable_name   | Value     |
 
+-----------------+-----------+
 
| gtid_binlog_pos | 200-101-5 |
 
+-----------------+-----------+
 
1 row in set (0.001 sec)
 
 
 
node2:root@localhost>

Comment by Daniele Sciascia [ 2023-11-30 ]

A pull request to fix this issue has been created and is ready for review.

Comment by Julius Goryavsky [ 2023-12-12 ]

Fix merged with head revision: https://github.com/MariaDB/server/commit/61daac54d62491fac9b699bccd639494da38a72f

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