MDEV-25045 : Assertion `client_state_.mode() != wsrep::client_state::m_toi' failed in int wsrep::transaction::before_commit()

CREATE [TEMPORARY] SEQUENCE is internally CREATE+INSERT (initial value)
and it is replicated using statement based replication. In Galera
we use either TOI or RSU so we should skip commit time hooks
for it.

Signed-off-by: Julius Goryavsky <julius.goryavsky@mariadb.com>

5 files changed, 133 insertions, 7 deletions

diff --git a/mysql-test/suite/galera/r/galera_sequences.result b/mysql-test/suite/galera/r/galera_sequences.result
index 7276cb8dbde..da669e6774e 100644
--- a/mysql-test/suite/galera/r/galera_sequences.result
+++ b/mysql-test/suite/galera/r/galera_sequences.result
@@ -1,6 +1,11 @@
 connection node_2;
 connection node_1;
 connection node_1;
+CALL mtr.add_suppression("SEQUENCES declared without `NOCACHE` will not behave correctly in galera cluster.");
+CALL mtr.add_suppression("WSREP: CREATE TABLE isolation failure");
+connection node_2;
+CALL mtr.add_suppression("SEQUENCES declared without `NOCACHE` will not behave correctly in galera cluster.");
+connection node_1;
 CREATE SEQUENCE `seq` start with 1 minvalue 1 maxvalue 1000000 increment by 0 cache 1000 nocycle ENGINE=InnoDB;
 SHOW CREATE SEQUENCE seq;
 Table	Create Table
@@ -47,6 +52,58 @@ NEXT VALUE FOR Seq1_1
 3001
 connection node_1;
 DROP SEQUENCE Seq1_1;
-CALL mtr.add_suppression("SEQUENCES declared without `NOCACHE` will not behave correctly in galera cluster.");
+connection node_1;
+CREATE TABLE t2 (d CHAR(1)KEY);
+SET SESSION autocommit=0;
+INSERT INTO t2 VALUES(1);
+CREATE TEMPORARY SEQUENCE seq1 NOCACHE ENGINE=INNODB;
+CREATE SEQUENCE seq2 NOCACHE ENGINE=INNODB;
+COMMIT;
+SET SESSION AUTOCOMMIT=1;
+SHOW CREATE TABLE seq1;
+Table	Create Table
+seq1	CREATE TEMPORARY TABLE `seq1` (
+  `next_not_cached_value` bigint(21) NOT NULL,
+  `minimum_value` bigint(21) NOT NULL,
+  `maximum_value` bigint(21) NOT NULL,
+  `start_value` bigint(21) NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used',
+  `increment` bigint(21) NOT NULL COMMENT 'increment value',
+  `cache_size` bigint(21) unsigned NOT NULL,
+  `cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed',
+  `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done'
+) ENGINE=InnoDB SEQUENCE=1
 connection node_2;
-CALL mtr.add_suppression("SEQUENCES declared without `NOCACHE` will not behave correctly in galera cluster.");
+SHOW CREATE SEQUENCE seq1;
+ERROR 42S02: Table 'test.seq1' doesn't exist
+SHOW CREATE SEQUENCE seq2;
+Table	Create Table
+seq2	CREATE SEQUENCE `seq2` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 nocache nocycle ENGINE=InnoDB
+connection node_1;
+SET SESSION autocommit=1;
+DROP SEQUENCE seq1;
+DROP SEQUENCE seq2;
+DROP TABLE t2;
+SET SESSION AUTOCOMMIT=0;
+SET SESSION wsrep_OSU_method='RSU';
+CREATE TABLE t1(c1 VARCHAR(10));
+INSERT INTO t1 (c1) VALUES('');
+create temporary sequence sq1 NOCACHE engine=innodb;
+create sequence sq2 NOCACHE engine=innodb;
+COMMIT;
+SHOW CREATE SEQUENCE sq1;
+Table	Create Table
+sq1	CREATE SEQUENCE `sq1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 nocache nocycle ENGINE=InnoDB
+SHOW CREATE SEQUENCE sq2;
+Table	Create Table
+sq2	CREATE SEQUENCE `sq2` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 nocache nocycle ENGINE=InnoDB
+connection node_2;
+SHOW CREATE SEQUENCE sq1;
+ERROR 42S02: Table 'test.sq1' doesn't exist
+SHOW CREATE SEQUENCE sq2;
+ERROR 42S02: Table 'test.sq2' doesn't exist
+connection node_1;
+SET SESSION AUTOCOMMIT=1;
+DROP TABLE t1;
+DROP SEQUENCE sq1;
+DROP SEQUENCE sq2;
+SET SESSION wsrep_OSU_method='TOI';
diff --git a/mysql-test/suite/galera/t/galera_sequences.cnf b/mysql-test/suite/galera/t/galera_sequences.cnf
new file mode 100644
index 00000000000..98e724fb2d0
--- /dev/null
+++ b/mysql-test/suite/galera/t/galera_sequences.cnf
@@ -0,0 +1,9 @@
+!include ../galera_2nodes.cnf
+
+[mysqld.1]
+log-bin
+log-slave-updates
+
+[mysqld.2]
+log-bin
+log-slave-updates
diff --git a/mysql-test/suite/galera/t/galera_sequences.test b/mysql-test/suite/galera/t/galera_sequences.test
index d469cc73516..faa3b46d2a7 100644
--- a/mysql-test/suite/galera/t/galera_sequences.test
+++ b/mysql-test/suite/galera/t/galera_sequences.test
@@ -5,6 +5,13 @@
 #
 
 --connection node_1
+CALL mtr.add_suppression("SEQUENCES declared without `NOCACHE` will not behave correctly in galera cluster.");
+CALL mtr.add_suppression("WSREP: CREATE TABLE isolation failure");
+--connection node_2
+
+CALL mtr.add_suppression("SEQUENCES declared without `NOCACHE` will not behave correctly in galera cluster.");
+
+--connection node_1
 CREATE SEQUENCE `seq` start with 1 minvalue 1 maxvalue 1000000 increment by 0 cache 1000 nocycle ENGINE=InnoDB;
 SHOW CREATE SEQUENCE seq;
 
@@ -45,8 +52,48 @@ select NEXT VALUE FOR Seq1_1;
 --connection node_1
 DROP SEQUENCE Seq1_1;
 
-CALL mtr.add_suppression("SEQUENCES declared without `NOCACHE` will not behave correctly in galera cluster.");
+#
+# MDEV-24045 : Assertion client_state_.mode() != wsrep::client_state::m_toi failed in int wsrep::transaction::before_commit()
+#
 
+--connection node_1
+CREATE TABLE t2 (d CHAR(1)KEY);
+SET SESSION autocommit=0;
+INSERT INTO t2 VALUES(1);
+CREATE TEMPORARY SEQUENCE seq1 NOCACHE ENGINE=INNODB;
+CREATE SEQUENCE seq2 NOCACHE ENGINE=INNODB;
+COMMIT;
+SET SESSION AUTOCOMMIT=1;
+SHOW CREATE TABLE seq1;
 --connection node_2
-
-CALL mtr.add_suppression("SEQUENCES declared without `NOCACHE` will not behave correctly in galera cluster.");
+--error ER_NO_SUCH_TABLE
+SHOW CREATE SEQUENCE seq1;
+SHOW CREATE SEQUENCE seq2;
+--connection node_1
+SET SESSION autocommit=1;
+DROP SEQUENCE seq1;
+DROP SEQUENCE seq2;
+DROP TABLE t2;
+#
+# Case2
+#
+SET SESSION AUTOCOMMIT=0;
+SET SESSION wsrep_OSU_method='RSU';
+CREATE TABLE t1(c1 VARCHAR(10));
+INSERT INTO t1 (c1) VALUES('');
+create temporary sequence sq1 NOCACHE engine=innodb;
+create sequence sq2 NOCACHE engine=innodb;
+COMMIT;
+SHOW CREATE SEQUENCE sq1;
+SHOW CREATE SEQUENCE sq2;
+--connection node_2
+--error ER_NO_SUCH_TABLE
+SHOW CREATE SEQUENCE sq1;
+--error ER_NO_SUCH_TABLE
+SHOW CREATE SEQUENCE sq2;
+--connection node_1
+SET SESSION AUTOCOMMIT=1;
+DROP TABLE t1;
+DROP SEQUENCE sq1;
+DROP SEQUENCE sq2;
+SET SESSION wsrep_OSU_method='TOI';
diff --git a/mysql-test/suite/galera_3nodes/galera_2x3nodes.cnf b/mysql-test/suite/galera_3nodes/galera_2x3nodes.cnf
index 9f99adbd711..cd7a892f4c9 100644
--- a/mysql-test/suite/galera_3nodes/galera_2x3nodes.cnf
+++ b/mysql-test/suite/galera_3nodes/galera_2x3nodes.cnf
@@ -55,7 +55,7 @@ wsrep-on=1
 #galera_port=@OPT.port
 #ist_port=@OPT.port
 #sst_port=@OPT.port
-wsrep_cluster_address='gcomm://
+wsrep_cluster_address=gcomm://
 wsrep_provider_options='repl.causal_read_timeout=PT90S;base_port=@mysqld.4.#galera_port;evs.suspect_timeout=PT10S;evs.inactive_timeout=PT30S;evs.install_timeout=PT15S;pc.wait_prim_timeout=PT60S;gcache.size=10M'
 wsrep_node_address='127.0.0.1:@mysqld.4.#galera_port'
 wsrep_node_incoming_address=127.0.0.1:@mysqld.4.port
diff --git a/sql/wsrep_trans_observer.h b/sql/wsrep_trans_observer.h
index f1d0eebf6dd..5f799d56c05 100644
--- a/sql/wsrep_trans_observer.h
+++ b/sql/wsrep_trans_observer.h
@@ -1,4 +1,4 @@
-/* Copyright 2016-2022 Codership Oy <http://www.codership.com>
+/* Copyright 2016-2023 Codership Oy <http://www.codership.com>
 
    This program is free software; you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
@@ -214,6 +214,19 @@ static inline bool wsrep_run_commit_hook(THD* thd, bool all)
     }
     mysql_mutex_unlock(&thd->LOCK_thd_data);
   }
+
+  mysql_mutex_lock(&thd->LOCK_thd_data);
+  /* Transaction creating sequence is TOI or RSU,
+  CREATE [TEMPORARY] SEQUENCE = CREATE + INSERT (initial value)
+  and replicated using statement based replication, thus
+  the commit hooks will be skipped */
+  if (ret &&
+      (thd->wsrep_cs().mode() == wsrep::client_state::m_toi ||
+       thd->wsrep_cs().mode() == wsrep::client_state::m_rsu) &&
+      thd->lex->sql_command == SQLCOM_CREATE_SEQUENCE)
+    ret= false;
+  mysql_mutex_unlock(&thd->LOCK_thd_data);
+
   DBUG_PRINT("wsrep", ("return: %d", ret));
   DBUG_RETURN(ret);
 }