mirror of
https://github.com/MariaDB/server.git
synced 2026-03-01 14:08:41 +01:00
3de6d11321
- use shared key for sequence update certification - employ native replication's code to apply changes for sequences which handles all corner cases properly - fix the tests to allow more transactions using sequences to be accepted That way the sequence is always updated to the maximum value independent of the order of updates, and shared certification keys allow to improve acceptance ratio of concurrent transactions that use sequences. It's reflected in the test changes.
378 lines
9.3 KiB
Text
378 lines
9.3 KiB
Text
--source include/galera_cluster.inc
|
|
--source include/have_innodb.inc
|
|
--source include/have_sequence.inc
|
|
--source include/have_aria.inc
|
|
|
|
--disable_ps2_protocol
|
|
#
|
|
# 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
|
|
SHOW CREATE SEQUENCE Seq1_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;
|
|
# Don't use NOCACHE as it may produce duplicate entries from multiple nodes
|
|
CREATE SEQUENCE t INCREMENT BY 0 CACHE 1 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
|
|
INSERT INTO t1(b) values (1);
|
|
--connection node_2
|
|
INSERT INTO t1(b) values (2);
|
|
INSERT INTO t1(b) values (2);
|
|
--connection node_1
|
|
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
|
|
INSERT INTO t1(b) values (1),(2),(3),(4),(5),(6),(7),(8),(9);
|
|
--connection node_2
|
|
INSERT INTO t1(b) values (21),(22),(23),(24),(25),(26),(27),(28),(29);
|
|
INSERT INTO t1(b) values (21),(22),(23),(24),(25),(26),(27),(28),(29);
|
|
--connection node_1
|
|
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;
|
|
|
|
--connection node_2
|
|
--let $wsrep_sync_wait_orig_2 = `SELECT @@wsrep_sync_wait`
|
|
SET SESSION wsrep_sync_wait=15;
|
|
|
|
--connection node_1
|
|
|
|
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);
|
|
|
|
--disable_query_log
|
|
--eval SET SESSION wsrep_sync_wait = $wsrep_sync_wait_orig_2
|
|
--enable_query_log
|
|
|
|
--connection node_1
|
|
DROP TABLE t1;
|
|
DROP SEQUENCE t;
|
|
|
|
--echo
|
|
--echo MDEV-33245 SIGSEGV in wsrep_check_sequence | Sql_cmd_alter_sequence::execute
|
|
--echo
|
|
|
|
CREATE TABLE t (a INT) ENGINE=InnoDB;
|
|
INSERT INTO t VALUES (0);
|
|
|
|
--error ER_PARSE_ERROR
|
|
CREATE TABLE t1 (c VARCHAR) ENGINE=InnoDB;
|
|
--error ER_NOT_SUPPORTED_YET
|
|
ALTER SEQUENCE IF EXISTS t MINVALUE=1;
|
|
|
|
DROP TABLE t;
|
|
|
|
--echo
|
|
--echo MDEV-32631:
|
|
--echo
|
|
|
|
CREATE OR REPLACE TABLE t1(c INT ) ENGINE=ARIA;
|
|
SET SESSION WSREP_OSU_METHOD=RSU;
|
|
--error ER_NOT_SUPPORTED_YET
|
|
INSERT INTO t1 SELECT seq,concat(seq,1) FROM seq_1_to_100;
|
|
SET SESSION WSREP_OSU_METHOD=TOI;
|
|
DROP TABLE t1;
|
|
|
|
--echo
|
|
--echo End of 10.5 tests
|