mirror of
https://github.com/MariaDB/server.git
synced 2025-07-17 00:38:14 +02:00
25737dbab7
Problem was that transacton was BF-aborted after certification succeeded and transaction tried to rollback and during rollback binlog stmt cache containing sequence value reservations was written into binlog. Transaction must replay because certification succeeded but transaction must not be written into binlog yet, it will be done during commit after the replay. Fix is to skip binlog write if transaction must replay and in replay we need to reset binlog stmt cache. Signed-off-by: Julius Goryavsky <julius.goryavsky@mariadb.com>
343 lines
11 KiB
Text
343 lines
11 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;
|
|
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
|
|
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;
|
|
connection node_2;
|
|
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;
|
|
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;
|
|
|
|
MDEV-33245 SIGSEGV in wsrep_check_sequence | Sql_cmd_alter_sequence::execute
|
|
|
|
CREATE TABLE t (a INT) ENGINE=InnoDB;
|
|
INSERT INTO t VALUES (0);
|
|
CREATE TABLE t1 (c VARCHAR) ENGINE=InnoDB;
|
|
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') ENGINE=InnoDB' at line 1
|
|
ALTER SEQUENCE IF EXISTS t MINVALUE=1;
|
|
ERROR 42000: This version of MariaDB doesn't yet support 'CACHE without INCREMENT BY 0 in Galera cluster'
|
|
DROP TABLE t;
|
|
|
|
MDEV-32631:
|
|
|
|
CREATE OR REPLACE TABLE t1(c INT ) ENGINE=ARIA;
|
|
SET SESSION WSREP_OSU_METHOD=RSU;
|
|
INSERT INTO t1 SELECT seq,concat(seq,1) FROM seq_1_to_100;
|
|
ERROR 42000: This version of MariaDB doesn't yet support 'RSU on this table engine'
|
|
SET SESSION WSREP_OSU_METHOD=TOI;
|
|
DROP TABLE t1;
|
|
|
|
End of 10.5 tests
|