mirror of
https://github.com/MariaDB/server.git
synced 2025-02-05 05:12:17 +01:00
14685b10df
The motivation of introducing the parameter innodb_purge_rseg_truncate_frequency in mysql/mysql-server@28bbd66ea5 and mysql/mysql-server@8fc2120fed seems to have been to avoid stalls due to freeing undo log pages or truncating undo log tablespaces. In MariaDB Server, innodb_undo_log_truncate=ON should be a much lighter operation than in MySQL, because it will not involve any log checkpoint. Another source of performance stalls should be trx_purge_truncate_rseg_history(), which is shrinking the history list by freeing the undo log pages whose undo records have been purged. To alleviate that, we will introduce a purge_truncation_task that will offload this from the purge_coordinator_task. In that way, the next innodb_purge_batch_size pages may be parsed and purged while the pages from the previous batch are being freed and the history list being shrunk. The processing of innodb_undo_log_truncate=ON will still remain the responsibility of the purge_coordinator_task. purge_coordinator_state::count: Remove. We will ignore innodb_purge_rseg_truncate_frequency, and act as if it had been set to 1 (the maximum shrinking frequency). purge_coordinator_state::do_purge(): Invoke an asynchronous task purge_truncation_callback() to free the undo log pages. purge_sys_t::iterator::free_history(): Free those undo log pages that have been processed. This used to be a part of trx_purge_truncate_history(). purge_sys_t::clone_end_view(): Take a new value of purge_sys.head as a parameter, so that it will be updated while holding exclusive purge_sys.latch. This is needed for race-free access to the field in purge_truncation_callback(). Reviewed by: Vladislav Lesin
386 lines
12 KiB
Text
386 lines
12 KiB
Text
--source include/have_innodb.inc
|
|
--source include/have_sequence.inc
|
|
|
|
SET default_storage_engine= innodb;
|
|
|
|
--echo #
|
|
--echo # Bug 21922176 - PREBUILT->SEARCH_TUPLE CREATED WITHOUT INCLUDING
|
|
--echo # THE NUMBER OF VIRTUAL COLUMNS
|
|
--echo #
|
|
|
|
CREATE TABLE t1 (a INT, a1 INT GENERATED ALWAYS AS (a) VIRTUAL, a2 INT
|
|
GENERATED ALWAYS AS (a) VIRTUAL, a3 INT GENERATED ALWAYS AS (a) VIRTUAL, a4
|
|
INT GENERATED ALWAYS AS (a) VIRTUAL, a5 INT GENERATED ALWAYS AS (a) VIRTUAL,
|
|
a6 INT GENERATED ALWAYS AS (a) VIRTUAL, a7 INT GENERATED ALWAYS AS (a)
|
|
VIRTUAL, a8 INT GENERATED ALWAYS AS (a) VIRTUAL, a9 INT GENERATED ALWAYS AS
|
|
(a) VIRTUAL, INDEX(a1, a2, a3, a4, a5, a6, a7, a8, a9)) ;
|
|
|
|
INSERT INTO t1(a) VALUES(10);
|
|
|
|
SELECT * FROM t1 WHERE a1=10 AND a2 = 10 AND a3 =10 AND a4 = 10 AND a5=10 AND
|
|
a6=10 AND a7=10 AND a8=10 AND a9=10;
|
|
|
|
DROP TABLE t1;
|
|
|
|
--echo #
|
|
--echo # Bug 22572997 - GCOL:INNODB: FAILING ASSERTION: N < REC_OFFS_N_FIELDS(
|
|
--echo # OFFSETS)
|
|
--echo #
|
|
SET @@SESSION.sql_mode=0;
|
|
|
|
CREATE TABLE t1(
|
|
c1 int(1)AUTO_INCREMENT,
|
|
c2 int(1),
|
|
c3 int(1)GENERATED ALWAYS AS ((c2 + c2)) VIRTUAL,
|
|
c4 int(1)GENERATED ALWAYS AS ((c3 + c2)) VIRTUAL,
|
|
c5 date,
|
|
c6 date GENERATED ALWAYS AS((c5 + interval 30 day)) VIRTUAL,
|
|
c7 DATE,
|
|
c8 time,
|
|
c9 DATE GENERATED ALWAYS AS(addtime(c7,c8)) VIRTUAL,
|
|
c10 time GENERATED ALWAYS AS(addtime(c7,c8)) VIRTUAL,
|
|
c11 DATE GENERATED ALWAYS AS(addtime(c9,c8)) VIRTUAL,
|
|
c12 CHAR(1),
|
|
c13 CHAR(1)GENERATED ALWAYS AS (concat(c12,c12)) VIRTUAL,
|
|
c14 CHAR(2)GENERATED ALWAYS AS (concat(c13,'x')) VIRTUAL,
|
|
PRIMARY KEY(c1),
|
|
KEY c4_6(c4,c11)
|
|
)ENGINE=InnoDB DEFAULT CHARSET=latin1;
|
|
|
|
CREATE TABLE t2(
|
|
c1 int(1)AUTO_INCREMENT,
|
|
c2 int(1),
|
|
c3 int(1)GENERATED ALWAYS AS ((c2 + c2)) VIRTUAL,
|
|
c4 int(1)GENERATED ALWAYS AS ((c3 + c2)) VIRTUAL,
|
|
c5 date,
|
|
c6 date GENERATED ALWAYS AS((c5 + interval 30 day)) VIRTUAL,
|
|
c6a date GENERATED ALWAYS AS((c6 + interval 30 day)) VIRTUAL,
|
|
c7 DATE,
|
|
c8 time,
|
|
c9 DATE GENERATED ALWAYS AS(addtime(c7,c8)) VIRTUAL,
|
|
c10 time GENERATED ALWAYS AS(addtime(c7,c8)) VIRTUAL,
|
|
c11 DATE GENERATED ALWAYS AS(addtime(c9,c8)) VIRTUAL,
|
|
c11a time GENERATED ALWAYS AS(addtime(c7,c10)) VIRTUAL,
|
|
c12 CHAR(1),
|
|
c13 CHAR(2)GENERATED ALWAYS AS (concat(RTRIM(c12),RTRIM(c12))) VIRTUAL,
|
|
c14 CHAR(4)GENERATED ALWAYS AS (concat(c13,'x')) VIRTUAL,
|
|
PRIMARY KEY(c1),
|
|
KEY c13(c13),
|
|
KEY c4_6(c4,c11)
|
|
)ENGINE=InnoDB DEFAULT CHARSET=latin1;
|
|
INSERT INTO t2(c1,c2,c5,c7,c8,c12)VALUES (0,0,0,0,0,'v');
|
|
|
|
CREATE TABLE t3(
|
|
c1 int(1)AUTO_INCREMENT,
|
|
c2 int(1),
|
|
c3 int(1)GENERATED ALWAYS AS ((c2 + c2)) VIRTUAL,
|
|
c4 int(1)GENERATED ALWAYS AS ((c3 + c2)) VIRTUAL,
|
|
c5 date,
|
|
c7 DATE,
|
|
c8 time,
|
|
c9 DATE GENERATED ALWAYS AS(addtime(c7,c8)) VIRTUAL,
|
|
c11 DATE GENERATED ALWAYS AS(addtime(c9,c8)) VIRTUAL,
|
|
c12 CHAR(1),
|
|
PRIMARY KEY(c1),
|
|
KEY c4_6(c4,c11)
|
|
)ENGINE=InnoDB DEFAULT CHARSET=latin1;
|
|
INSERT INTO t3(c1,c2,c5,c7,c8,c12)VALUES
|
|
(0,0,0,0,0,'q'),(0,0,0,0,0,'g'),(0,0,0,0,0,'l'),(0,0,0,0,0,1),(0,0,0,0,0,'v'),
|
|
(0,1,0,0,0,'c'),(0,0,0,0,0,'x');
|
|
|
|
UPDATE
|
|
t2 AS O1,t3 AS O2
|
|
SET O1.c12=1
|
|
WHERE O1.c14 NOT IN
|
|
(
|
|
SELECT
|
|
DISTINCT I1.c14 AS y
|
|
FROM t1 AS I1
|
|
ORDER BY I1.c14);
|
|
|
|
SET @@SESSION.sql_mode=default;
|
|
--source ../../innodb/include/wait_all_purged.inc
|
|
|
|
DROP TABLE t1, t2, t3;
|
|
|
|
--echo #
|
|
--echo # Bug 22650296 - ASSERTION IN INNOBASE_BUILD_COL_MAP, ALTER
|
|
--echo #
|
|
CREATE TABLE `ibstd_08` (
|
|
`nc00577` tinyint(4) DEFAULT NULL,
|
|
`nc07844` varchar(41) DEFAULT NULL,
|
|
`gc01908` point NOT NULL,
|
|
`nc04156` char(17) DEFAULT NULL,
|
|
`nc09536` longblob NOT NULL,
|
|
`nc09231` decimal(10,0) NOT NULL,
|
|
`a` int(11) NOT NULL,
|
|
`b` varchar(198) NOT NULL,
|
|
`nc04560` mediumtext,
|
|
`c` char(187) DEFAULT NULL,
|
|
`vbidxcol` char(3) GENERATED ALWAYS AS (substr(`b`,1,3)) VIRTUAL,
|
|
`gc00881` polygon NOT NULL,
|
|
`nc05121` int(11) NOT NULL DEFAULT '85941481',
|
|
KEY `a` (`a`),
|
|
KEY `b` (`b`(3),`a`),
|
|
KEY `c` (`c`(99),`b`(25)),
|
|
KEY `b_2` (`b`(5),`c`(10),`a`),
|
|
KEY `vbidxcol` (`vbidxcol`),
|
|
KEY `a_2` (`a`,`vbidxcol`),
|
|
KEY `vbidxcol_2` (`vbidxcol`),
|
|
FULLTEXT KEY `ftsic` (`c`,`b`)
|
|
) ENGINE=InnoDB;
|
|
|
|
|
|
ALTER TABLE ibstd_08 ADD COLUMN nc07006 BIGINT AUTO_INCREMENT NOT NULL , ADD KEY auto_nc07006(nc07006);
|
|
|
|
DROP TABLE ibstd_08;
|
|
|
|
--echo #
|
|
--echo # Bug 22899305 - GCOLS: FAILING ASSERTION: !(COL->PRTYPE & 256)
|
|
--echo # AND SEGFAULT
|
|
--echo #
|
|
set sql_mode="";
|
|
create table t (a int) engine=innodb;
|
|
create table s (
|
|
b int generated always as (1) virtual,
|
|
c int,
|
|
d int generated always as (1) virtual,
|
|
key (d)
|
|
) engine=innodb;
|
|
|
|
insert into t(a) values ((select d from s for update));
|
|
insert into s(c) values ('');
|
|
|
|
SET sql_mode = default;
|
|
drop table if exists t,s;
|
|
|
|
--echo #
|
|
--echo # Bug 23014521 - GCOL:INNODB: FAILING ASSERTION: !IS_V
|
|
--echo #
|
|
CREATE TABLE t1 (
|
|
col1 int(11) NOT NULL,
|
|
col2 int(11) DEFAULT NULL,
|
|
col3 int(11) NOT NULL,
|
|
col4 int(11) DEFAULT NULL,
|
|
col5 int(11) GENERATED ALWAYS AS ((col1 % col4)) VIRTUAL,
|
|
col6 int(11) GENERATED ALWAYS AS ((col2 - col4)) VIRTUAL,
|
|
col5x int(11) GENERATED ALWAYS AS ((col3 / col2)) VIRTUAL,
|
|
col6b varchar(20) GENERATED ALWAYS AS (col2) VIRTUAL,
|
|
col6x int(11) GENERATED ALWAYS AS ((col2 % col1)) VIRTUAL,
|
|
col7 int(11) GENERATED ALWAYS AS ((col6x + col5x)) VIRTUAL,
|
|
col8 int(11) GENERATED ALWAYS AS ((col5x / col5)) VIRTUAL,
|
|
col7x int(11) GENERATED ALWAYS AS ((col5x + col5)) VIRTUAL,
|
|
col8x int(11) GENERATED ALWAYS AS ((col5 / col5x)) VIRTUAL,
|
|
col9 text,
|
|
col2b varchar(20) GENERATED ALWAYS AS (col4) VIRTUAL,
|
|
col8a int(11) GENERATED ALWAYS AS (col2) VIRTUAL,
|
|
col4b varchar(20) GENERATED ALWAYS AS (col4) VIRTUAL,
|
|
col1c int(11) GENERATED ALWAYS AS ((col2 * col1)) VIRTUAL,
|
|
extra int(11) DEFAULT NULL,
|
|
col5c int(11) GENERATED ALWAYS AS ((col1 / col1)) VIRTUAL,
|
|
col6a bigint(20) GENERATED ALWAYS AS ((col3 / col1)) VIRTUAL,
|
|
col1a varchar(20) GENERATED ALWAYS AS (col6) VIRTUAL,
|
|
col6c int(11) GENERATED ALWAYS AS ((col2 % col2)) VIRTUAL,
|
|
col7c bigint(20) GENERATED ALWAYS AS ((col2 / col1)) VIRTUAL,
|
|
col2c int(11) GENERATED ALWAYS AS ((col5 % col5)) VIRTUAL,
|
|
col1b int(11) GENERATED ALWAYS AS ((col1 / col2)) VIRTUAL,
|
|
col3b bigint(20) GENERATED ALWAYS AS ((col6x % col6)) VIRTUAL,
|
|
UNIQUE KEY idx7 (col1,col3,col2),
|
|
UNIQUE KEY uidx (col9(10)),
|
|
KEY idx15 (col9(10) DESC,col2 DESC),
|
|
KEY idx10 (col9(10) DESC,col1 DESC),
|
|
KEY idx11 (col6x DESC),
|
|
KEY idx6 (col9(10) DESC,col7 DESC),
|
|
KEY idx14 (col6 DESC)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
|
|
|
|
# Cannot add virtual column along with create FULLTEXT index with
|
|
# adding a hidden FTS_DOC_ID column (which require a table rebuild)
|
|
--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
|
|
ALTER TABLE t1 ADD COLUMN col7a INT GENERATED ALWAYS AS (col5x % col6x)
|
|
VIRTUAL, ADD FULLTEXT KEY ftidx ( col9 ), algorithm=inplace;
|
|
|
|
# This will add a hidden FTS_DOC_ID column
|
|
CREATE FULLTEXT INDEX idx ON t1(col9);
|
|
|
|
# Since there is no table rebuild needed, now the alter would be sucessful
|
|
ALTER TABLE t1 ADD COLUMN col7a INT GENERATED ALWAYS AS (col5x % col6x)
|
|
VIRTUAL, ADD FULLTEXT KEY ftidx ( col9 ), algorithm=inplace;
|
|
|
|
DROP TABLE t1;
|
|
|
|
CREATE TABLE t1 (
|
|
col1 int(11) NOT NULL,
|
|
col2 int(11) DEFAULT NULL,
|
|
col3 int(11) NOT NULL,
|
|
col4 int(11) DEFAULT NULL) engine=innodb;
|
|
|
|
# This secondary key idx will be coverted to a new Primary Key, thus a table
|
|
# rebuild. It is blocked since there is an adding of virtual columns
|
|
--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
|
|
ALTER TABLE t1 ADD COLUMN col7a INT GENERATED ALWAYS AS (col1 % col2)
|
|
VIRTUAL, ADD UNIQUE index idx (col1), algorithm=inplace;
|
|
|
|
DROP TABLE t1;
|
|
|
|
--echo #
|
|
--echo # Bug 27122803 - BACKPORT FIX FOR BUG 25899959 TO MYSQL-5.7
|
|
--echo #
|
|
CREATE TABLE t1 (col1 int(10)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
ALTER TABLE t1 ADD col2 char(21) AS (col1 * col1), ADD INDEX n (col2);
|
|
SHOW CREATE TABLE t1;
|
|
DROP TABLE t1;
|
|
|
|
--echo #
|
|
--echo # Bug #27968952 INNODB CRASH/CORRUPTION WITH TEXT PREFIX INDEXES
|
|
--echo #
|
|
|
|
CREATE TABLE t1(
|
|
a INT NOT NULL UNIQUE,
|
|
b INT NOT NULL,
|
|
c TEXT GENERATED ALWAYS AS (a <> b) VIRTUAL,
|
|
d TEXT NOT NULL,
|
|
UNIQUE KEY (c(1)), KEY(d(1))
|
|
) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
|
|
SET @t = REPEAT('t',@@innodb_page_size);
|
|
INSERT INTO t1 (a,b,d) VALUES (1,0,@t), (0,0,@t);
|
|
--error ER_DUP_ENTRY
|
|
UPDATE t1 SET b = a;
|
|
REPLACE INTO t1 SET a = 0, b = 1, d = 'd';
|
|
SELECT * FROM t1;
|
|
CHECK TABLE t1;
|
|
DROP TABLE t1;
|
|
|
|
--disable_query_log
|
|
SET @save_innodb_read_only_compressed=@@GLOBAL.innodb_read_only_compressed;
|
|
SET GLOBAL innodb_read_only_compressed=OFF;
|
|
--enable_query_log
|
|
CREATE TABLE t1(
|
|
a VARCHAR(1000) GENERATED ALWAYS AS ('1') VIRTUAL,
|
|
b VARCHAR(1000) NOT NULL,
|
|
c VARCHAR(1000) GENERATED ALWAYS AS (b) STORED,
|
|
KEY (b(1)),
|
|
KEY (a(1))
|
|
) ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1;
|
|
INSERT INTO t1(b) VALUES(REPEAT('b',1000));
|
|
DELETE FROM t1;
|
|
--disable_query_log
|
|
SET GLOBAL innodb_read_only_compressed=@save_innodb_read_only_compressed;
|
|
--enable_query_log
|
|
DROP TABLE t1;
|
|
|
|
--echo #
|
|
--echo # Bug #22990029 GCOLS: INCORRECT BEHAVIOR
|
|
--echo # AFTER DATA INSERTED WITH IGNORE KEYWORD
|
|
--echo #
|
|
|
|
CREATE TABLE t1(a INT PRIMARY KEY, b INT, vb DATE AS(b) VIRTUAL, KEY(vb))
|
|
ENGINE=InnoDB;
|
|
INSERT IGNORE INTO t1 (a,b) VALUES(1,20190132);
|
|
SELECT * FROM t1;
|
|
BEGIN;
|
|
DELETE FROM t1;
|
|
INSERT INTO t1 (a,b) VALUES(1,20190123);
|
|
SELECT * FROM t1;
|
|
ROLLBACK;
|
|
SELECT * FROM t1;
|
|
CHECK TABLE t1;
|
|
DROP TABLE t1;
|
|
|
|
--echo #
|
|
--echo # MDEV-25872 InnoDB: Assertion failure in row_merge_read_clustered_index
|
|
--echo # upon ALTER on table with indexed virtual columns
|
|
--echo #
|
|
|
|
CREATE TABLE t1 (
|
|
id BIGINT AUTO_INCREMENT PRIMARY KEY,
|
|
a INT,
|
|
va INT ZEROFILL AS (a) VIRTUAL,
|
|
b TIMESTAMP,
|
|
c CHAR(204),
|
|
vc CHAR(8),
|
|
KEY(vc,c(64),b,va)
|
|
) ENGINE=InnoDB CHARACTER SET utf32;
|
|
INSERT INTO t1 (id) SELECT NULL FROM seq_1_to_75;
|
|
INSERT IGNORE INTO t1 (id, a) VALUES (NULL, -1);
|
|
--error ER_WARN_DATA_OUT_OF_RANGE
|
|
ALTER TABLE t1 FORCE;
|
|
DROP TABLE t1;
|
|
|
|
--echo #
|
|
--echo # MDEV-24713 Assertion `dict_table_is_comp(index->table)' failed
|
|
--echo # in row_merge_buf_add()
|
|
--echo #
|
|
CREATE TABLE t1 (id INT PRIMARY KEY, a CHAR(3),
|
|
b CHAR(8) AS (a) VIRTUAL, KEY(b))
|
|
ROW_FORMAT=REDUNDANT ENGINE=InnoDB
|
|
CHARACTER SET utf8;
|
|
INSERT INTO t1 (id,a) VALUES (1,'foo');
|
|
OPTIMIZE TABLE t1;
|
|
DROP TABLE t1;
|
|
|
|
--echo #
|
|
--echo # MDEV-20154 Assertion `len <= col->len || ((col->mtype) == 5
|
|
--echo # || (col->mtype) == 14)' failed in row_merge_buf_add
|
|
--echo #
|
|
|
|
CREATE TABLE t1 (
|
|
a VARCHAR(2500),
|
|
b VARCHAR(2499) AS (a) VIRTUAL
|
|
) ENGINE=InnoDB;
|
|
INSERT INTO t1 (a) VALUES ('foo');
|
|
|
|
ALTER TABLE t1 MODIFY a VARCHAR(2600), ALGORITHM=INPLACE;
|
|
ALTER TABLE t1 ADD KEY (b), ALGORITHM=INPLACE;
|
|
|
|
--echo # Cleanup
|
|
DROP TABLE t1;
|
|
|
|
--echo # End of 10.2 tests
|
|
|
|
--echo #
|
|
--echo # MDEV-29299 SELECT from table with vcol index reports warning
|
|
--echo #
|
|
|
|
CREATE TABLE t(fld1 INT NOT NULL,
|
|
fld2 INT AS (100/fld1) VIRTUAL,
|
|
KEY(fld1), KEY(fld2));
|
|
CREATE TABLE t_odd(id int);
|
|
INSERT INTO t(fld1) VALUES(1), (2);
|
|
|
|
--connect stop_purge,localhost,root
|
|
# This prevents purge for records in t
|
|
START TRANSACTION WITH CONSISTENT SNAPSHOT;
|
|
INSERT INTO t_odd VALUES(10000);
|
|
|
|
--connection default
|
|
UPDATE IGNORE t SET fld1= 3 WHERE fld1= 2;
|
|
UPDATE IGNORE t SET fld1= 4 WHERE fld1= 3;
|
|
UPDATE IGNORE t SET fld1= 0 WHERE fld1= 4;
|
|
SELECT fld2 FROM t FORCE INDEX(fld2);
|
|
SELECT fld2 FROM t FORCE INDEX(fld1);
|
|
|
|
--disconnect stop_purge
|
|
DROP TABLE t, t_odd;
|
|
|
|
--echo #
|
|
--echo # MDEV-29753 An error is wrongly reported during INSERT with vcol index
|
|
--echo # See also Bug #22990029
|
|
--echo #
|
|
|
|
CREATE TABLE t(pk INT PRIMARY KEY,
|
|
fld1 INT NOT NULL,
|
|
fld2 INT AS (100/fld1) VIRTUAL,
|
|
KEY(fld1), KEY(fld2));
|
|
INSERT IGNORE t(pk, fld1) VALUES(1, 0);
|
|
SELECT * FROM t;
|
|
BEGIN;
|
|
DELETE FROM t;
|
|
INSERT INTO t (pk, fld1) VALUES(1,1);
|
|
SELECT * FROM t;
|
|
|
|
--echo # Cleanup
|
|
ROLLBACK;
|
|
DROP TABLE t;
|
|
|
|
--echo # End of 10.3 tests
|