mirror of
https://github.com/MariaDB/server.git
synced 2025-07-18 01:08:14 +02:00
e913f4e11e
Problem was that total order isolation (TOI) is started before we know sequence implementing storage engine. This led to situation where table implementing persistent storate for sequence in case of MyISAM was created on applier causing errors later in test execution. Therefore, in both CREATE SEQUENCE and ALTER TABLE to implementing persistent storage we need to check implementing storage engine after open_tables and this check must be done in both master and applier, because if implementing storage engine is MyISAM it does not support rollback. Added tests to make sure that if sequence implementing storage engine is MyISAM or we try to alter it to MyISAM user gets error and changes are not replicated. Signed-off-by: Julius Goryavsky <julius.goryavsky@mariadb.com>
316 lines
10 KiB
Text
316 lines
10 KiB
Text
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;
|