MDEV-30388 : Assertion `!wsrep_has_changes(thd) || (thd->lex->sql_command == SQLCOM_CREATE_TABLE && !thd->is_current_stmt_binlog_format_row()) || thd->wsrep_cs().transaction().state() == wsrep::transaction::s_aborted' failed

Problem for Galera is the fact that sequences are not really
transactional. Sequence operation is committed immediately
in sql_sequence.cd and later Galera could find out that
we have changes but actual statement is not there anymore.

Therefore, we must make some restrictions what kind
of sequences Galera can support.

(1) Galera cluster supports only sequences implemented
by InnoDB storage engine. This is because Galera replication
supports currently only InnoDB.

(2) We do not allow LOCK TABLE on sequence object and
we do not allow sequence creation under LOCK TABLE, instead
lock is released and we issue warning.

(3) We allow sequences with NOCACHE definition or with
INCREMEMENT BY 0 CACHE=n definition. This makes sure that
sequence values are unique accross Galera cluster.

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

13 files changed, 516 insertions, 74 deletions

diff --git a/mysql-test/suite/galera/r/MDEV-18832.result b/mysql-test/suite/galera/r/MDEV-18832.result
index 2e0872b9f2e..676ee4bf337 100644
--- a/mysql-test/suite/galera/r/MDEV-18832.result
+++ b/mysql-test/suite/galera/r/MDEV-18832.result
@@ -1,17 +1,14 @@
 connection node_2;
 connection node_1;
-CREATE SEQUENCE Seq1_1  START WITH 1  INCREMENT BY 1;
+CREATE SEQUENCE Seq1_1  START WITH 1  INCREMENT BY 1 NOCACHE;
 CREATE TABLE t1 (Id int(11) NOT NULL, PRIMARY KEY (Id));
 INSERT INTO t1 VALUES (NEXT VALUE FOR Seq1_1);
 INSERT INTO t1 VALUES (NEXT VALUE FOR Seq1_1);
 INSERT INTO t1 VALUES (NEXT VALUE FOR Seq1_1);
 INSERT INTO t1 VALUES (NEXT VALUE FOR Seq1_1);
 DROP SEQUENCE Seq1_1;
-CREATE SEQUENCE Seq1_1  START WITH 1  INCREMENT BY 1;
+CREATE SEQUENCE Seq1_1  START WITH 1  INCREMENT BY 1 NOCACHE;
 INSERT INTO t1 VALUES (NEXT VALUE FOR Seq1_1);
 ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
 DROP SEQUENCE Seq1_1;
 DROP TABLE t1;
-CALL mtr.add_suppression("SEQUENCES declared without `NOCACHE` will not behave correctly in galera cluster.");
-connection node_2;
-CALL mtr.add_suppression("SEQUENCES declared without `NOCACHE` will not behave correctly in galera cluster.");
diff --git a/mysql-test/suite/galera/r/MDEV-27862.result b/mysql-test/suite/galera/r/MDEV-27862.result
index 25b7bc6cfd2..d1aa9e2ca91 100644
--- a/mysql-test/suite/galera/r/MDEV-27862.result
+++ b/mysql-test/suite/galera/r/MDEV-27862.result
@@ -1,10 +1,5 @@
 connection node_2;
 connection node_1;
-CREATE SEQUENCE seq_nocache ENGINE=InnoDB;
-DROP SEQUENCE seq_nocache;
-CALL mtr.add_suppression("SEQUENCES declared without `NOCACHE` will not behave correctly in galera cluster.");
-connection node_2;
-CALL mtr.add_suppression("SEQUENCES declared without `NOCACHE` will not behave correctly in galera cluster.");
 connection node_1;
 CREATE SEQUENCE seq NOCACHE ENGINE=InnoDB;
 SELECT NEXTVAL(seq) = 1;
diff --git a/mysql-test/suite/galera/r/galera_sequences.result b/mysql-test/suite/galera/r/galera_sequences.result
index da669e6774e..ec3adec8924 100644
--- a/mysql-test/suite/galera/r/galera_sequences.result
+++ b/mysql-test/suite/galera/r/galera_sequences.result
@@ -1,11 +1,6 @@
 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
@@ -15,14 +10,14 @@ SHOW CREATE SEQUENCE seq;
 Table	Create Table
 seq	CREATE SEQUENCE `seq` start with 1 minvalue 1 maxvalue 1000000 increment by 0 cache 1000 nocycle ENGINE=InnoDB
 connection node_1;
-ALTER SEQUENCE seq MAXVALUE = 10000;
+ALTER SEQUENCE seq MAXVALUE = 10000 NOCACHE;
 SHOW CREATE SEQUENCE seq;
 Table	Create Table
-seq	CREATE SEQUENCE `seq` start with 1 minvalue 1 maxvalue 10000 increment by 0 cache 1000 nocycle ENGINE=InnoDB
+seq	CREATE SEQUENCE `seq` start with 1 minvalue 1 maxvalue 10000 increment by 0 nocache nocycle ENGINE=InnoDB
 connection node_2;
 SHOW CREATE SEQUENCE seq;
 Table	Create Table
-seq	CREATE SEQUENCE `seq` start with 1 minvalue 1 maxvalue 10000 increment by 0 cache 1000 nocycle ENGINE=InnoDB
+seq	CREATE SEQUENCE `seq` start with 1 minvalue 1 maxvalue 10000 increment by 0 nocache nocycle ENGINE=InnoDB
 connection node_1;
 DROP SEQUENCE seq;
 SHOW CREATE SEQUENCE seq;
@@ -31,25 +26,26 @@ connection node_2;
 SHOW CREATE SEQUENCE seq;
 ERROR 42S02: Table 'test.seq' doesn't exist
 connection node_1;
-CREATE SEQUENCE Seq1_1     START WITH 1     INCREMENT BY 1;
+CREATE SEQUENCE Seq1_1     START WITH 1     INCREMENT BY 1 NOCACHE;
 select NEXT VALUE FOR Seq1_1;
 NEXT VALUE FOR Seq1_1
 1
 alter table Seq1_1 engine=myisam;
+ERROR 42000: This version of MariaDB doesn't yet support 'Galera cluster does support only InnoDB sequences'
 select NEXT VALUE FOR Seq1_1;
 NEXT VALUE FOR Seq1_1
-1001
+2
 alter table Seq1_1 engine=innodb;
 select NEXT VALUE FOR Seq1_1;
 NEXT VALUE FOR Seq1_1
-2001
+3
 connection node_2;
 SHOW CREATE SEQUENCE Seq1_1;
 Table	Create Table
-Seq1_1	CREATE SEQUENCE `Seq1_1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 1000 nocycle ENGINE=InnoDB
+Seq1_1	CREATE SEQUENCE `Seq1_1` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 nocache nocycle ENGINE=InnoDB
 select NEXT VALUE FOR Seq1_1;
 NEXT VALUE FOR Seq1_1
-3001
+4
 connection node_1;
 DROP SEQUENCE Seq1_1;
 connection node_1;
@@ -107,3 +103,167 @@ DROP TABLE t1;
 DROP SEQUENCE sq1;
 DROP SEQUENCE sq2;
 SET SESSION wsrep_OSU_method='TOI';
+CREATE TABLE t (f INT) engine=innodb;
+LOCK TABLE t WRITE;
+CREATE OR REPLACE SEQUENCE t MAXVALUE=13 INCREMENT BY 1 NOCACHE engine=innodb;
+Warnings:
+Warning	138	Galera cluster does not support LOCK TABLE on SEQUENCES. Lock is released.
+LOCK TABLE t WRITE;
+ERROR 42000: This version of MariaDB doesn't yet support 'LOCK TABLE on SEQUENCES in Galera cluster'
+INSERT INTO t VALUES (0,0,1,1,1,0,0,0);
+SELECT * from t;
+next_not_cached_value	minimum_value	maximum_value	start_value	increment	cache_size	cycle_option	cycle_count
+0	0	1	1	1	0	0	0
+SELECT NEXTVAL(t);
+NEXTVAL(t)
+0
+UNLOCK TABLES;
+DROP TABLE t;
+CREATE SEQUENCE t INCREMENT BY 0 NOCACHE ENGINE=INNODB;
+CREATE TABLE t1(a int not null primary key default nextval(t),
+b int) engine=innodb;
+INSERT INTO t1(b) VALUES (1),(2),(3);
+SELECT * FROM t1;
+a	b
+1	1
+3	2
+5	3
+connection node_2;
+SELECT * FROM t1;
+a	b
+1	1
+3	2
+5	3
+INSERT INTO t1(b) VALUES (4),(5),(6);
+SELECT * FROM t1;
+a	b
+1	1
+3	2
+5	3
+8	4
+10	5
+12	6
+connection node_1;
+SELECT * FROM t1;
+a	b
+1	1
+3	2
+5	3
+8	4
+10	5
+12	6
+DROP TABLE t1;
+DROP SEQUENCE t;
+CREATE SEQUENCE t ENGINE=MYISAM;
+ERROR 42000: This version of MariaDB doesn't yet support 'Galera cluster does support only InnoDB sequences'
+CREATE SEQUENCE t INCREMENT BY 1 NOCACHE ENGINE=INNODB;
+CREATE TABLE t1(a int not null primary key default nextval(t), b int) engine=innodb;
+connection node_2;
+# Wait DDL to replicate
+connection node_1;
+SELECT @@auto_increment_increment;
+@@auto_increment_increment
+2
+SELECT @@auto_increment_offset;
+@@auto_increment_offset
+1
+SET SESSION wsrep_sync_wait=0;
+connection node_2;
+SELECT @@auto_increment_increment;
+@@auto_increment_increment
+2
+SELECT @@auto_increment_offset;
+@@auto_increment_offset
+2
+SET SESSION wsrep_sync_wait=0;
+connection node_1;
+connection node_2;
+connection node_1;
+DROP SEQUENCE t;
+DROP TABLE t1;
+CREATE SEQUENCE t INCREMENT BY 0 NOCACHE ENGINE=INNODB;
+DROP SEQUENCE t;
+CREATE SEQUENCE t INCREMENT BY 1 CACHE=20 ENGINE=INNODB;
+ERROR 42000: This version of MariaDB doesn't yet support 'In Galera if you use CACHE you should set INCREMENT BY 0 to behave correctly in a cluster'
+CREATE SEQUENCE t INCREMENT BY 0 CACHE=20 ENGINE=INNODB;
+CREATE TABLE t1(a int not null primary key default nextval(t), b int) engine=innodb;
+connection node_2;
+# Wait DDL to replicate
+connection node_1;
+SET SESSION wsrep_sync_wait=0;
+connection node_2;
+SET SESSION wsrep_sync_wait=0;
+connection node_1;
+connection node_2;
+connection node_1;
+DROP SEQUENCE t;
+DROP TABLE t1;
+CREATE SEQUENCE t INCREMENT BY 0 CACHE=20 ENGINE=INNODB;
+ALTER TABLE t ENGINE=MYISAM;
+ERROR 42000: This version of MariaDB doesn't yet support 'Galera cluster does support only InnoDB sequences'
+ALTER SEQUENCE t INCREMENT BY 1 CACHE=10;
+ERROR 42000: This version of MariaDB doesn't yet support 'In Galera if you use CACHE you should set INCREMENT BY 0 to behave correctly in a cluster'
+ALTER SEQUENCE t INCREMENT BY 1 NOCACHE;
+ALTER SEQUENCE t INCREMENT BY 0 NOCACHE;
+ALTER SEQUENCE t INCREMENT BY 0 CACHE=10;
+DROP SEQUENCE t;
+CREATE SEQUENCE t INCREMENT BY 0 CACHE=20 ENGINE=INNODB;
+CREATE TABLE t1(a int not null primary key default nextval(t), b int) engine=innodb;
+BEGIN;
+INSERT INTO t1(b) VALUES (1);
+INSERT INTO t1(b) VALUES (2);
+INSERT INTO t1(b) VALUES (3);
+INSERT INTO t1(b) VALUES (4);
+INSERT INTO t1(a,b) VALUES (2,2);
+INSERT INTO t1(a,b) VALUES (3,2);
+ERROR 23000: Duplicate entry '3' for key 'PRIMARY'
+ROLLBACK;
+SELECT * FROM t1;
+a	b
+SELECT NEXTVAL(t);
+NEXTVAL(t)
+9
+connection node_2;
+SELECT * FROM t1;
+a	b
+SELECT NEXTVAL(t);
+NEXTVAL(t)
+2
+connection node_1;
+DROP TABLE t1;
+DROP SEQUENCE t;
+CREATE SEQUENCE t INCREMENT BY 0 CACHE=20 ENGINE=INNODB;
+CREATE TABLE t1(a int not null primary key default nextval(t), b int) engine=innodb;
+BEGIN;
+INSERT INTO t1(b) VALUES (1);
+INSERT INTO t1(b) VALUES (2);
+INSERT INTO t1(b) VALUES (3);
+INSERT INTO t1(b) VALUES (4);
+INSERT INTO t1(a,b) VALUES (2,2);
+INSERT INTO t1(a,b) VALUES (3,2);
+ERROR 23000: Duplicate entry '3' for key 'PRIMARY'
+COMMIT;
+SELECT * FROM t1;
+a	b
+1	1
+2	2
+3	2
+5	3
+7	4
+SELECT NEXTVAL(t);
+NEXTVAL(t)
+9
+connection node_2;
+SELECT * FROM t1;
+a	b
+1	1
+2	2
+3	2
+5	3
+7	4
+SELECT NEXTVAL(t);
+NEXTVAL(t)
+42
+connection node_1;
+DROP TABLE t1;
+DROP SEQUENCE t;
diff --git a/mysql-test/suite/galera/t/MDEV-18832.test b/mysql-test/suite/galera/t/MDEV-18832.test
index ba93761435a..d60be151142 100644
--- a/mysql-test/suite/galera/t/MDEV-18832.test
+++ b/mysql-test/suite/galera/t/MDEV-18832.test
@@ -1,23 +1,15 @@
 --source include/galera_cluster.inc
 --source include/have_innodb.inc
 
-CREATE SEQUENCE Seq1_1  START WITH 1  INCREMENT BY 1;
+CREATE SEQUENCE Seq1_1  START WITH 1  INCREMENT BY 1 NOCACHE;
 CREATE TABLE t1 (Id int(11) NOT NULL, PRIMARY KEY (Id));
 INSERT INTO t1 VALUES (NEXT VALUE FOR Seq1_1);
 INSERT INTO t1 VALUES (NEXT VALUE FOR Seq1_1);
 INSERT INTO t1 VALUES (NEXT VALUE FOR Seq1_1);
 INSERT INTO t1 VALUES (NEXT VALUE FOR Seq1_1);
 DROP SEQUENCE Seq1_1;
-CREATE SEQUENCE Seq1_1  START WITH 1  INCREMENT BY 1;
+CREATE SEQUENCE Seq1_1  START WITH 1  INCREMENT BY 1 NOCACHE;
 --error ER_DUP_ENTRY
 INSERT INTO t1 VALUES (NEXT VALUE FOR Seq1_1);
 DROP SEQUENCE Seq1_1;
 DROP TABLE t1;
-
-# Supress warning for SEQUENCES that are declared without `NOCACHE` introduced with MDEV-27862
-
-CALL mtr.add_suppression("SEQUENCES declared without `NOCACHE` will not behave correctly in galera cluster.");
-
---connection node_2
-
-CALL mtr.add_suppression("SEQUENCES declared without `NOCACHE` will not behave correctly in galera cluster.");
diff --git a/mysql-test/suite/galera/t/MDEV-27862.test b/mysql-test/suite/galera/t/MDEV-27862.test
index 89d3465b91f..d23ce95d47e 100644
--- a/mysql-test/suite/galera/t/MDEV-27862.test
+++ b/mysql-test/suite/galera/t/MDEV-27862.test
@@ -1,17 +1,6 @@
 --source include/galera_cluster.inc
 --source include/have_innodb.inc
 
-# Report WARNING when SEQUENCE is created without `NOCACHE`
-
-CREATE SEQUENCE seq_nocache ENGINE=InnoDB;
-DROP SEQUENCE seq_nocache;
-
-CALL mtr.add_suppression("SEQUENCES declared without `NOCACHE` will not behave correctly in galera cluster.");
-
---connection node_2
-
-CALL mtr.add_suppression("SEQUENCES declared without `NOCACHE` will not behave correctly in galera cluster.");
-
 # NEXTVAL
 
 --connection node_1
diff --git a/mysql-test/suite/galera/t/galera_sequences.cnf b/mysql-test/suite/galera/t/galera_sequences.cnf
index 98e724fb2d0..3a0543e3d34 100644
--- a/mysql-test/suite/galera/t/galera_sequences.cnf
+++ b/mysql-test/suite/galera/t/galera_sequences.cnf
@@ -3,7 +3,11 @@
 [mysqld.1]
 log-bin
 log-slave-updates
+auto-increment-increment=2
+auto-increment-offset=1
 
 [mysqld.2]
 log-bin
 log-slave-updates
+auto-increment-increment=2
+auto-increment-offset=2
diff --git a/mysql-test/suite/galera/t/galera_sequences.test b/mysql-test/suite/galera/t/galera_sequences.test
index 613823d83e9..2582c204435 100644
--- a/mysql-test/suite/galera/t/galera_sequences.test
+++ b/mysql-test/suite/galera/t/galera_sequences.test
@@ -6,13 +6,6 @@
 #
 
 --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;
 
@@ -20,7 +13,7 @@ SHOW CREATE SEQUENCE seq;
 SHOW CREATE SEQUENCE seq;
 
 --connection node_1
-ALTER SEQUENCE seq MAXVALUE = 10000;
+ALTER SEQUENCE seq MAXVALUE = 10000 NOCACHE;
 SHOW CREATE SEQUENCE seq;
 
 --connection node_2
@@ -39,8 +32,9 @@ SHOW CREATE SEQUENCE seq;
 # MDEV-18848 : Galera: 10.4 node crashed with Assertion `client_state.transaction().active()` after altering SEQUENCE table's engine to myisam and back to innodb
 #
 --connection node_1
-CREATE SEQUENCE Seq1_1     START WITH 1     INCREMENT BY 1;
+CREATE SEQUENCE Seq1_1     START WITH 1     INCREMENT BY 1 NOCACHE;
 select NEXT VALUE FOR Seq1_1;
+--error ER_NOT_SUPPORTED_YET
 alter table Seq1_1 engine=myisam;
 select NEXT VALUE FOR Seq1_1;
 alter table Seq1_1 engine=innodb;
@@ -98,3 +92,227 @@ DROP TABLE t1;
 DROP SEQUENCE sq1;
 DROP SEQUENCE sq2;
 SET SESSION wsrep_OSU_method='TOI';
+
+#
+# MDEV-30388 Assertion `!wsrep_has_changes(thd) || (thd->lex->sql_command == SQLCOM_CREATE_TABLE
+# && !thd->is_current_stmt_binlog_format_row()) ||
+# thd->wsrep_cs().transaction().state() == wsrep::transaction::s_aborted' failed
+#
+
+CREATE TABLE t (f INT) engine=innodb;
+LOCK TABLE t WRITE;
+CREATE OR REPLACE SEQUENCE t MAXVALUE=13 INCREMENT BY 1 NOCACHE engine=innodb;
+--error ER_NOT_SUPPORTED_YET
+LOCK TABLE t WRITE;
+INSERT INTO t VALUES (0,0,1,1,1,0,0,0);
+SELECT * from t;
+SELECT NEXTVAL(t);
+UNLOCK TABLES;
+DROP TABLE t;
+
+CREATE SEQUENCE t INCREMENT BY 0 NOCACHE ENGINE=INNODB;
+CREATE TABLE t1(a int not null primary key default nextval(t),
+b int) engine=innodb;
+INSERT INTO t1(b) VALUES (1),(2),(3);
+SELECT * FROM t1;
+
+--connection node_2
+SELECT * FROM t1;
+INSERT INTO t1(b) VALUES (4),(5),(6);
+SELECT * FROM t1;
+
+--connection node_1
+SELECT * FROM t1;
+DROP TABLE t1;
+DROP SEQUENCE t;
+
+#
+# Test Galera SEQUENCE support
+#
+#
+# No MyISAM SEQUENCES
+#
+--error ER_NOT_SUPPORTED_YET
+CREATE SEQUENCE t ENGINE=MYISAM;
+CREATE SEQUENCE t INCREMENT BY 1 NOCACHE ENGINE=INNODB;
+CREATE TABLE t1(a int not null primary key default nextval(t), b int) engine=innodb;
+
+--connection node_2
+--echo # Wait DDL to replicate
+--let $wait_condition = SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 't'
+--source include/wait_condition.inc
+--let $wait_condition = SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 't1'
+--source include/wait_condition.inc
+
+#
+# Below we do not care order of INSERTs we care only that values are unique
+#
+--connection node_1
+SELECT @@auto_increment_increment;
+SELECT @@auto_increment_offset;
+--let $wsrep_sync_wait_orig_1 = `SELECT @@wsrep_sync_wait`
+SET SESSION wsrep_sync_wait=0;
+
+--connection node_2
+SELECT @@auto_increment_increment;
+SELECT @@auto_increment_offset;
+--let $wsrep_sync_wait_orig_2 = `SELECT @@wsrep_sync_wait`
+SET SESSION wsrep_sync_wait=0;
+
+--let $count = 20
+--disable_query_log
+while ($count)
+{
+--connection node_1
+--error 0,ER_LOCK_WAIT_TIMEOUT
+INSERT INTO t1(b) values (1);
+--connection node_2
+--error 0,ER_LOCK_WAIT_TIMEOUT
+INSERT INTO t1(b) values (2);
+--error 0,ER_LOCK_WAIT_TIMEOUT
+INSERT INTO t1(b) values (2);
+--connection node_1
+--error 0,ER_LOCK_WAIT_TIMEOUT
+INSERT INTO t1(b) values (1);
+--dec $count
+}
+--enable_query_log
+
+--connection node_1
+--disable_query_log
+--eval SET SESSION wsrep_sync_wait = $wsrep_sync_wait_orig_1
+--enable_query_log
+
+--connection node_2
+--disable_query_log
+--eval SET SESSION wsrep_sync_wait = $wsrep_sync_wait_orig_2
+--enable_query_log
+
+--connection node_1
+DROP SEQUENCE t;
+DROP TABLE t1;
+CREATE SEQUENCE t INCREMENT BY 0 NOCACHE ENGINE=INNODB;
+DROP SEQUENCE t;
+--error ER_NOT_SUPPORTED_YET
+CREATE SEQUENCE t INCREMENT BY 1 CACHE=20 ENGINE=INNODB;
+
+CREATE SEQUENCE t INCREMENT BY 0 CACHE=20 ENGINE=INNODB;
+CREATE TABLE t1(a int not null primary key default nextval(t), b int) engine=innodb;
+
+--connection node_2
+--echo # Wait DDL to replicate
+--let $wait_condition = SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 't'
+--source include/wait_condition.inc
+--let $wait_condition = SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 't1'
+--source include/wait_condition.inc
+
+#
+# Below we do not care order of INSERTs we care only that values are unique
+#
+--connection node_1
+--let $wsrep_sync_wait_orig_1 = `SELECT @@wsrep_sync_wait`
+SET SESSION wsrep_sync_wait=0;
+
+--connection node_2
+--let $wsrep_sync_wait_orig_2 = `SELECT @@wsrep_sync_wait`
+SET SESSION wsrep_sync_wait=0;
+
+--let $count = 5
+--disable_query_log
+while ($count)
+{
+--connection node_1
+--error 0,ER_LOCK_WAIT_TIMEOUT
+INSERT INTO t1(b) values (1),(2),(3),(4),(5),(6),(7),(8),(9);
+--connection node_2
+--error 0,ER_LOCK_WAIT_TIMEOUT
+INSERT INTO t1(b) values (21),(22),(23),(24),(25),(26),(27),(28),(29);
+--error 0,ER_LOCK_WAIT_TIMEOUT
+INSERT INTO t1(b) values (21),(22),(23),(24),(25),(26),(27),(28),(29);
+--connection node_1
+--error 0,ER_LOCK_WAIT_TIMEOUT
+INSERT INTO t1(b) values (1),(2),(3),(4),(5),(6),(7),(8),(9);
+--dec $count
+}
+--enable_query_log
+
+--connection node_1
+--disable_query_log
+--eval SET SESSION wsrep_sync_wait = $wsrep_sync_wait_orig_1
+--enable_query_log
+
+--connection node_2
+--disable_query_log
+--eval SET SESSION wsrep_sync_wait = $wsrep_sync_wait_orig_2
+--enable_query_log
+
+--connection node_1
+DROP SEQUENCE t;
+DROP TABLE t1;
+
+#
+# Test ALTER table to sequence and ALTER SEQUENCE
+#
+CREATE SEQUENCE t INCREMENT BY 0 CACHE=20 ENGINE=INNODB;
+--error ER_NOT_SUPPORTED_YET
+ALTER TABLE t ENGINE=MYISAM;
+--error ER_NOT_SUPPORTED_YET
+ALTER SEQUENCE t INCREMENT BY 1 CACHE=10;
+ALTER SEQUENCE t INCREMENT BY 1 NOCACHE;
+ALTER SEQUENCE t INCREMENT BY 0 NOCACHE;
+ALTER SEQUENCE t INCREMENT BY 0 CACHE=10;
+DROP SEQUENCE t;
+
+#
+# Test transactions
+#
+CREATE SEQUENCE t INCREMENT BY 0 CACHE=20 ENGINE=INNODB;
+CREATE TABLE t1(a int not null primary key default nextval(t), b int) engine=innodb;
+#
+# ROLLBACK TRX
+#
+BEGIN;
+INSERT INTO t1(b) VALUES (1);
+INSERT INTO t1(b) VALUES (2);
+INSERT INTO t1(b) VALUES (3);
+INSERT INTO t1(b) VALUES (4);
+INSERT INTO t1(a,b) VALUES (2,2);
+--error ER_DUP_ENTRY
+INSERT INTO t1(a,b) VALUES (3,2);
+ROLLBACK;
+SELECT * FROM t1;
+SELECT NEXTVAL(t);
+
+--connection node_2
+SELECT * FROM t1;
+SELECT NEXTVAL(t);
+
+--connection node_1
+DROP TABLE t1;
+DROP SEQUENCE t;
+
+CREATE SEQUENCE t INCREMENT BY 0 CACHE=20 ENGINE=INNODB;
+CREATE TABLE t1(a int not null primary key default nextval(t), b int) engine=innodb;
+#
+# COMMIT TRX
+#
+BEGIN;
+INSERT INTO t1(b) VALUES (1);
+INSERT INTO t1(b) VALUES (2);
+INSERT INTO t1(b) VALUES (3);
+INSERT INTO t1(b) VALUES (4);
+INSERT INTO t1(a,b) VALUES (2,2);
+--error ER_DUP_ENTRY
+INSERT INTO t1(a,b) VALUES (3,2);
+COMMIT;
+
+SELECT * FROM t1;
+SELECT NEXTVAL(t);
+
+--connection node_2
+SELECT * FROM t1;
+SELECT NEXTVAL(t);
+
+--connection node_1
+DROP TABLE t1;
+DROP SEQUENCE t;
diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc
index c95993e1604..48d4f0cbd55 100644
--- a/sql/sql_parse.cc
+++ b/sql/sql_parse.cc
@@ -2951,6 +2951,16 @@ retry:
         if (result)
           goto err;
       }
+
+#ifdef WITH_WSREP
+      if (WSREP(thd) && table->table->s->table_type == TABLE_TYPE_SEQUENCE)
+      {
+        my_error(ER_NOT_SUPPORTED_YET, MYF(0),
+                 "LOCK TABLE on SEQUENCES in Galera cluster");
+        goto err;
+      }
+#endif
+
     }
     /*
        Check privileges of view tables here, after views were opened.
diff --git a/sql/sql_sequence.cc b/sql/sql_sequence.cc
index ce95d7baeeb..2904749d1e6 100644
--- a/sql/sql_sequence.cc
+++ b/sql/sql_sequence.cc
@@ -311,11 +311,6 @@ bool sequence_insert(THD *thd, LEX *lex, TABLE_LIST *org_table_list)
       DBUG_RETURN(TRUE);
   }
 
-#ifdef WITH_WSREP
-  if (WSREP_ON && seq->cache != 0)
-    WSREP_WARN("CREATE SEQUENCES declared without `NOCACHE` will not behave correctly in galera cluster.");
-#endif
-
   /* If not temporary table */
   if (!temporary_table)
   {
@@ -925,21 +920,24 @@ bool Sql_cmd_alter_sequence::execute(THD *thd)
                    0, 0))
     DBUG_RETURN(TRUE);                  /* purecov: inspected */
 
-#ifdef WITH_WSREP
-  if (WSREP_ON && new_seq->cache != 0)
-    WSREP_WARN("ALTER SEQUENCES declared without `NOCACHE` will not behave correctly in galera cluster.");
-#endif
-
   if (check_grant(thd, ALTER_ACL, first_table, FALSE, 1, FALSE))
     DBUG_RETURN(TRUE);                  /* purecov: inspected */
 
 #ifdef WITH_WSREP
-  if (WSREP_ON && WSREP(thd) &&
-      wsrep_to_isolation_begin(thd, first_table->db.str,
-	                       first_table->table_name.str,
-		               first_table))
-    DBUG_RETURN(TRUE);
+  if (WSREP(thd) && wsrep_thd_is_local(thd))
+  {
+    if (wsrep_check_sequence(thd, new_seq))
+      DBUG_RETURN(TRUE);
+
+    if (wsrep_to_isolation_begin(thd, first_table->db.str,
+                                 first_table->table_name.str,
+                                 first_table))
+    {
+      DBUG_RETURN(TRUE);
+    }
+  }
 #endif /* WITH_WSREP */
+
   if (if_exists())
     thd->push_internal_handler(&no_such_table_handler);
   error= open_and_lock_tables(thd, first_table, FALSE, 0);
diff --git a/sql/sql_table.cc b/sql/sql_table.cc
index 1e88e7722e3..b0f7d848068 100644
--- a/sql/sql_table.cc
+++ b/sql/sql_table.cc
@@ -5301,6 +5301,51 @@ int mysql_create_table_no_lock(THD *thd, const LEX_CSTRING *db,
   return res;
 }
 
+#ifdef WITH_WSREP
+/** Additional sequence checks for Galera cluster.
+
+@param thd    thread handle
+@param seq    sequence definition
+@retval 0     failure
+@retval 1     success
+*/
+bool wsrep_check_sequence(THD* thd, const sequence_definition *seq)
+{
+    enum legacy_db_type db_type;
+    if (thd->lex->create_info.used_fields & HA_CREATE_USED_ENGINE)
+    {
+      db_type= thd->lex->create_info.db_type->db_type;
+    }
+    else
+    {
+      const handlerton *hton= ha_default_handlerton(thd);
+      db_type= hton->db_type;
+    }
+
+    // In Galera cluster we support only InnoDB sequences
+    if (db_type != DB_TYPE_INNODB)
+    {
+      my_error(ER_NOT_SUPPORTED_YET, MYF(0),
+               "Galera cluster does support only InnoDB sequences");
+      return(true);
+    }
+
+    // In Galera cluster it is best to use INCREMENT BY 0 with CACHE
+    // or NOCACHE
+    if (seq &&
+	seq->increment &&
+        seq->cache)
+    {
+      my_error(ER_NOT_SUPPORTED_YET, MYF(0),
+               "In Galera if you use CACHE you should set INCREMENT BY 0"
+	       " to behave correctly in a cluster");
+      return(true);
+    }
+
+    return (false);
+}
+#endif /* WITH_WSREP */
+
 /**
   Implementation of SQLCOM_CREATE_TABLE.
 
@@ -5356,6 +5401,15 @@ bool mysql_create_table(THD *thd, TABLE_LIST *create_table,
   if (!opt_explicit_defaults_for_timestamp)
     promote_first_timestamp_column(&alter_info->create_list);
 
+#ifdef WITH_WSREP
+  if (thd->lex->sql_command == SQLCOM_CREATE_SEQUENCE &&
+      WSREP(thd) && wsrep_thd_is_local_toi(thd))
+  {
+    if (wsrep_check_sequence(thd, create_info->seq_create_info))
+      DBUG_RETURN(true);
+  }
+#endif /* WITH_WSREP */
+
   /* We can abort create table for any table type */
   thd->abort_on_warning= thd->is_strict_mode();
 
@@ -10143,6 +10197,15 @@ do_continue:;
   }
 #endif
 
+#ifdef WITH_WSREP
+  if (table->s->sequence && WSREP(thd) &&
+      wsrep_thd_is_local_toi(thd))
+  {
+    if (wsrep_check_sequence(thd, create_info->seq_create_info))
+      DBUG_RETURN(TRUE);
+  }
+#endif /* WITH_WSREP */
+
   /*
     Use copy algorithm if:
     - old_alter_table system variable is set without in-place requested using
diff --git a/sql/sql_table.h b/sql/sql_table.h
index e51b5ec0f0f..2422f4dcb59 100644
--- a/sql/sql_table.h
+++ b/sql/sql_table.h
@@ -285,4 +285,8 @@ extern mysql_mutex_t LOCK_gdl;
 
 bool check_engine(THD *, const char *, const char *, HA_CREATE_INFO *);
 
+#ifdef WITH_WSREP
+bool wsrep_check_sequence(THD* thd, const sequence_definition *seq);
+#endif
+
 #endif /* SQL_TABLE_INCLUDED */
diff --git a/sql/wsrep_mysqld.cc b/sql/wsrep_mysqld.cc
index a95cefc6f0f..a898b554a78 100644
--- a/sql/wsrep_mysqld.cc
+++ b/sql/wsrep_mysqld.cc
@@ -2298,9 +2298,20 @@ int wsrep_to_isolation_begin(THD *thd, const char *db_, const char *table_,
     return -1;
   }
 
+  /* If we are inside LOCK TABLE we release it and give warning. */
+  if (thd->variables.option_bits & OPTION_TABLE_LOCK &&
+      thd->lex->sql_command == SQLCOM_CREATE_SEQUENCE)
+  {
+    thd->locked_tables_list.unlock_locked_tables(thd);
+    thd->variables.option_bits&= ~(OPTION_TABLE_LOCK);
+    push_warning_printf(thd, Sql_condition::WARN_LEVEL_WARN,
+			HA_ERR_UNSUPPORTED,
+			"Galera cluster does not support LOCK TABLE on "
+			"SEQUENCES. Lock is released.");
+  }
   if (wsrep_debug && thd->mdl_context.has_locks())
   {
-    WSREP_DEBUG("thread holds MDL locks at TI begin: %s %llu",
+    WSREP_DEBUG("thread holds MDL locks at TO begin: %s %llu",
                 wsrep_thd_query(thd), thd->thread_id);
   }
 
diff --git a/sql/wsrep_trans_observer.h b/sql/wsrep_trans_observer.h
index 5f799d56c05..2d5e4d3a1f2 100644
--- a/sql/wsrep_trans_observer.h
+++ b/sql/wsrep_trans_observer.h
@@ -414,11 +414,12 @@ int wsrep_after_statement(THD* thd)
 {
   DBUG_ENTER("wsrep_after_statement");
   WSREP_DEBUG("wsrep_after_statement for %lu client_state %s "
-	  " client_mode %s trans_state %s",
-	  thd_get_thread_id(thd),
-	  wsrep::to_c_string(thd->wsrep_cs().state()),
-	  wsrep::to_c_string(thd->wsrep_cs().mode()),
-	  wsrep::to_c_string(thd->wsrep_cs().transaction().state()));
+              " client_mode %s trans_state %s query %s",
+              thd_get_thread_id(thd),
+              wsrep::to_c_string(thd->wsrep_cs().state()),
+              wsrep::to_c_string(thd->wsrep_cs().mode()),
+              wsrep::to_c_string(thd->wsrep_cs().transaction().state()),
+              wsrep_thd_query(thd));
   DBUG_RETURN((thd->wsrep_cs().state() != wsrep::client_state::s_none &&
 	       thd->wsrep_cs().mode() == Wsrep_client_state::m_local) ?
               thd->wsrep_cs().after_statement() : 0);