1 files changed, 316 insertions, 0 deletions
diff --git a/mysql-test/suite/galera/r/galera_sequences.result b/mysql-test/suite/galera/r/galera_sequences.result
new file mode 100644
index 00000000..1a5219c2
--- /dev/null
+++ b/mysql-test/suite/galera/r/galera_sequences.result
@@ -0,0 +1,316 @@
+connection node_2;
+connection node_1;
+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
+seq	CREATE SEQUENCE `seq` start with 1 minvalue 1 maxvalue 1000000 increment by 0 cache 1000 nocycle ENGINE=InnoDB
+connection node_2;
+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 NOCACHE;
+SHOW CREATE SEQUENCE seq;
+Table	Create Table
+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 nocache nocycle ENGINE=InnoDB
+connection node_1;
+DROP SEQUENCE seq;
+SHOW CREATE SEQUENCE seq;
+ERROR 42S02: Table 'test.seq' doesn't exist
+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 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 'non-InnoDB sequences in Galera cluster'
+select NEXT VALUE FOR Seq1_1;
+NEXT VALUE FOR Seq1_1
+2
+alter table Seq1_1 engine=innodb;
+select NEXT VALUE FOR Seq1_1;
+NEXT VALUE FOR Seq1_1
+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 nocache nocycle ENGINE=InnoDB
+select NEXT VALUE FOR Seq1_1;
+NEXT VALUE FOR Seq1_1
+4
+connection node_1;
+DROP SEQUENCE Seq1_1;
+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;
+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;
+connection node_2;
+SET SESSION AUTOCOMMIT=0;
+SET SESSION wsrep_OSU_method='RSU';
+CREATE TABLE t1(c1 VARCHAR(10));
+create temporary sequence sq1 NOCACHE engine=innodb;
+create sequence sq2 NOCACHE engine=innodb;
+COMMIT;
+SET SESSION wsrep_OSU_method='TOI';
+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_1;
+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_2;
+SET SESSION AUTOCOMMIT=1;
+DROP TABLE t1;
+DROP SEQUENCE sq1;
+DROP SEQUENCE sq2;
+connection node_1;
+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 'non-InnoDB sequences in Galera cluster'
+SHOW CREATE SEQUENCE t;
+ERROR 42S02: Table 'test.t' doesn't exist
+SHOW CREATE TABLE t;
+ERROR 42S02: Table 'test.t' doesn't exist
+connection node_2;
+SHOW CREATE SEQUENCE t;
+ERROR 42S02: Table 'test.t' doesn't exist
+SHOW CREATE TABLE t;
+ERROR 42S02: Table 'test.t' doesn't exist
+connection node_1;
+CREATE SEQUENCE t NOCACHE ENGINE=InnoDB;
+ALTER TABLE t ENGINE=MyISAM;
+ERROR 42000: This version of MariaDB doesn't yet support 'non-InnoDB sequences in Galera cluster'
+SHOW CREATE SEQUENCE t;
+Table	Create Table
+t	CREATE SEQUENCE `t` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 nocache nocycle ENGINE=InnoDB
+SHOW CREATE TABLE t;
+Table	Create Table
+t	CREATE TABLE `t` (
+  `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;
+SHOW CREATE SEQUENCE t;
+Table	Create Table
+t	CREATE SEQUENCE `t` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 nocache nocycle ENGINE=InnoDB
+SHOW CREATE TABLE t;
+Table	Create Table
+t	CREATE TABLE `t` (
+  `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_1;
+DROP SEQUENCE t;
+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 'CACHE without INCREMENT BY 0 in Galera 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 'non-InnoDB sequences in Galera cluster'
+ALTER SEQUENCE t INCREMENT BY 1 CACHE=10;
+ERROR 42000: This version of MariaDB doesn't yet support 'CACHE without INCREMENT BY 0 in Galera 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;