path: root/mysql-test/suite/galera/t/galera_sequences.test

--source include/galera_cluster.inc
--source include/have_innodb.inc

#
# MDEV-19353 : Alter Sequence do not replicate to another nodes with 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;

--connection node_2
SHOW CREATE SEQUENCE seq;

--connection node_1
ALTER SEQUENCE seq MAXVALUE = 10000 NOCACHE;
SHOW CREATE SEQUENCE seq;

--connection node_2
SHOW CREATE SEQUENCE seq;

--connection node_1
DROP SEQUENCE seq;
--error ER_NO_SUCH_TABLE
SHOW CREATE SEQUENCE seq;

--connection node_2
--error ER_NO_SUCH_TABLE
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 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;
select NEXT VALUE FOR Seq1_1;

--connection node_2
SHOW CREATE SEQUENCE Seq1_1;
select NEXT VALUE FOR Seq1_1;

--connection node_1
DROP SEQUENCE Seq1_1;

#
# 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
--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
#
--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;
SHOW CREATE SEQUENCE sq2;
--connection node_1
--error ER_NO_SUCH_TABLE
SHOW CREATE SEQUENCE sq1;
--error ER_NO_SUCH_TABLE
SHOW CREATE SEQUENCE sq2;
--connection node_2
SET SESSION AUTOCOMMIT=1;
DROP TABLE t1;
DROP SEQUENCE sq1;
DROP SEQUENCE sq2;

#
# 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
#
--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;
--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;
--error ER_NO_SUCH_TABLE
SHOW CREATE SEQUENCE t;
--error ER_NO_SUCH_TABLE
SHOW CREATE TABLE t;

--connection node_2
# Verify that above MyISAM sequence does not replicate
--error ER_NO_SUCH_TABLE
SHOW CREATE SEQUENCE t;
--error ER_NO_SUCH_TABLE
SHOW CREATE TABLE t;

--connection node_1
CREATE SEQUENCE t NOCACHE ENGINE=InnoDB;
--error ER_NOT_SUPPORTED_YET
ALTER TABLE t ENGINE=MyISAM;
SHOW CREATE SEQUENCE t;
SHOW CREATE TABLE t;

--connection node_2
SHOW CREATE SEQUENCE t;
SHOW CREATE TABLE t;

--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
--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,ER_LOCK_DEADLOCK
INSERT INTO t1(b) values (1);
--connection node_2
--error 0,ER_LOCK_WAIT_TIMEOUT,ER_LOCK_DEADLOCK
INSERT INTO t1(b) values (2);
--error 0,ER_LOCK_WAIT_TIMEOUT,ER_LOCK_DEADLOCK
INSERT INTO t1(b) values (2);
--connection node_1
--error 0,ER_LOCK_WAIT_TIMEOUT,ER_LOCK_DEADLOCK
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,ER_LOCK_DEADLOCK
INSERT INTO t1(b) values (1),(2),(3),(4),(5),(6),(7),(8),(9);
--connection node_2
--error 0,ER_LOCK_WAIT_TIMEOUT,ER_LOCK_DEADLOCK
INSERT INTO t1(b) values (21),(22),(23),(24),(25),(26),(27),(28),(29);
--error 0,ER_LOCK_WAIT_TIMEOUT,ER_LOCK_DEADLOCK
INSERT INTO t1(b) values (21),(22),(23),(24),(25),(26),(27),(28),(29);
--connection node_1
--error 0,ER_LOCK_WAIT_TIMEOUT,ER_LOCK_DEADLOCK
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;