diff options
author | Junqi Xie <junqi_xie@outlook.com> | 2023-03-12 13:55:30 +0800 |
---|---|---|
committer | Daniel Black <daniel@mariadb.org> | 2023-04-12 11:04:29 +1000 |
commit | d20a96f9c1c0240eac2ad8520a04f06e218c4e0a (patch) | |
tree | 2a9bfbd680ebe5079c29dbf11225d249ea1d490b /mysql-test/suite/innodb/r | |
parent | 4472a7b4ff79853d931288c1ab8d49bf130e3908 (diff) | |
download | mariadb-git-d20a96f9c1c0240eac2ad8520a04f06e218c4e0a.tar.gz |
MDEV-21921 Make transaction_isolation and transaction_read_only into system variables
In MariaDB, we have a confusing problem where:
* The transaction_isolation option can be set in a configuration file, but it cannot be set dynamically.
* The tx_isolation system variable can be set dynamically, but it cannot be set in a configuration file.
Therefore, we have two different names for the same thing in different contexts. This is needlessly confusing, and it complicates the documentation. The same thing applys for transaction_read_only.
MySQL 5.7 solved this problem by making them into system variables. https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-20.html
This commit takes a similar approach by adding new system variables and marking the original ones as deprecated. This commit also resolves some legacy problems related to SET STATEMENT and transaction_isolation.
Diffstat (limited to 'mysql-test/suite/innodb/r')
-rw-r--r-- | mysql-test/suite/innodb/r/innodb-blob.result | 8 | ||||
-rw-r--r-- | mysql-test/suite/innodb/r/innodb.result | 12 | ||||
-rw-r--r-- | mysql-test/suite/innodb/r/innodb_bug40360.result | 2 | ||||
-rw-r--r-- | mysql-test/suite/innodb/r/innodb_bug49164.result | 4 | ||||
-rw-r--r-- | mysql-test/suite/innodb/r/innodb_bug53756.result | 28 | ||||
-rw-r--r-- | mysql-test/suite/innodb/r/innodb_lock_wait_timeout_1.result | 6 | ||||
-rw-r--r-- | mysql-test/suite/innodb/r/innodb_mysql.result | 4 | ||||
-rw-r--r-- | mysql-test/suite/innodb/r/insert_into_empty.result | 4 | ||||
-rw-r--r-- | mysql-test/suite/innodb/r/temporary_table.result | 20 |
9 files changed, 44 insertions, 44 deletions
diff --git a/mysql-test/suite/innodb/r/innodb-blob.result b/mysql-test/suite/innodb/r/innodb-blob.result index 8173d30e92c..63c52f2e6e5 100644 --- a/mysql-test/suite/innodb/r/innodb-blob.result +++ b/mysql-test/suite/innodb/r/innodb-blob.result @@ -29,8 +29,8 @@ INSERT INTO t1 VALUES (3,REPEAT('c',50000)); connection con1; SET DEBUG_SYNC='now WAIT_FOR rec_not_blob'; SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -SELECT @@tx_isolation; -@@tx_isolation +SELECT @@transaction_isolation; +@@transaction_isolation READ-UNCOMMITTED SELECT a, RIGHT(b,20) FROM t1; a RIGHT(b,20) @@ -60,8 +60,8 @@ UPDATE t3 SET c=REPEAT('f',3000) WHERE a=1; connect con1,localhost,root,,; SET DEBUG_SYNC='now WAIT_FOR go_sel'; SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -SELECT @@tx_isolation; -@@tx_isolation +SELECT @@transaction_isolation; +@@transaction_isolation READ-UNCOMMITTED SELECT a, RIGHT(b,20), RIGHT(c,20) FROM t3; a RIGHT(b,20) RIGHT(c,20) diff --git a/mysql-test/suite/innodb/r/innodb.result b/mysql-test/suite/innodb/r/innodb.result index a4c1115bfd4..b3e1eec3382 100644 --- a/mysql-test/suite/innodb/r/innodb.result +++ b/mysql-test/suite/innodb/r/innodb.result @@ -1017,8 +1017,8 @@ SET sql_mode = default; create table t1 (id int unsigned not null auto_increment, code tinyint unsigned not null, name char(20) not null, primary key (id), key (code), unique (name)) engine=innodb; BEGIN; SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; -SELECT @@tx_isolation,@@global.tx_isolation; -@@tx_isolation @@global.tx_isolation +SELECT @@transaction_isolation, @@global.transaction_isolation; +@@transaction_isolation @@global.transaction_isolation SERIALIZABLE REPEATABLE-READ insert into t1 (code, name) values (1, 'Tim'), (1, 'Monty'), (2, 'David'); select id, code, name from t1 order by id; @@ -3141,7 +3141,7 @@ CONNECT c1,localhost,root,,; CONNECT c2,localhost,root,,; connection c1; SET binlog_format='MIXED'; -SET TX_ISOLATION='read-committed'; +SET TRANSACTION_ISOLATION='read-committed'; SET AUTOCOMMIT=0; DROP TABLE IF EXISTS t1, t2; Warnings: @@ -3152,7 +3152,7 @@ SELECT * FROM t2; a connection c2; SET binlog_format='MIXED'; -SET TX_ISOLATION='read-committed'; +SET TRANSACTION_ISOLATION='read-committed'; SET AUTOCOMMIT=0; INSERT INTO t1 VALUES (1); COMMIT; @@ -3166,13 +3166,13 @@ CONNECT c1,localhost,root,,; CONNECT c2,localhost,root,,; connection c1; SET binlog_format='MIXED'; -SET TX_ISOLATION='read-committed'; +SET TRANSACTION_ISOLATION='read-committed'; SET AUTOCOMMIT=0; SELECT * FROM t2; a connection c2; SET binlog_format='MIXED'; -SET TX_ISOLATION='read-committed'; +SET TRANSACTION_ISOLATION='read-committed'; SET AUTOCOMMIT=0; INSERT INTO t1 VALUES (2); COMMIT; diff --git a/mysql-test/suite/innodb/r/innodb_bug40360.result b/mysql-test/suite/innodb/r/innodb_bug40360.result index ef4cf463903..46456bde3c8 100644 --- a/mysql-test/suite/innodb/r/innodb_bug40360.result +++ b/mysql-test/suite/innodb/r/innodb_bug40360.result @@ -1,4 +1,4 @@ -SET TX_ISOLATION='READ-COMMITTED'; +SET TRANSACTION_ISOLATION='READ-COMMITTED'; CREATE TABLE bug40360 (a INT) engine=innodb; INSERT INTO bug40360 VALUES (1); DROP TABLE bug40360; diff --git a/mysql-test/suite/innodb/r/innodb_bug49164.result b/mysql-test/suite/innodb/r/innodb_bug49164.result index 857cd2851f2..8cac84b4eda 100644 --- a/mysql-test/suite/innodb/r/innodb_bug49164.result +++ b/mysql-test/suite/innodb/r/innodb_bug49164.result @@ -1,7 +1,7 @@ connect con1,localhost,root,,; connect con2,localhost,root,,; connection con1; -SET tx_isolation = 'READ-COMMITTED'; +SET transaction_isolation = 'READ-COMMITTED'; CREATE TABLE bug49164 (a INT, b BIGINT, c TINYINT, PRIMARY KEY (a, b)) ENGINE=InnoDB; insert into bug49164 values (1,1,1), (2,2,2), (3,3,3); @@ -21,7 +21,7 @@ a b c begin; update bug49164 set c=7; connection con2; -SET tx_isolation = 'READ-COMMITTED'; +SET transaction_isolation = 'READ-COMMITTED'; begin; select * from bug49164; a b c diff --git a/mysql-test/suite/innodb/r/innodb_bug53756.result b/mysql-test/suite/innodb/r/innodb_bug53756.result index daa6113f8d4..100d37154ce 100644 --- a/mysql-test/suite/innodb/r/innodb_bug53756.result +++ b/mysql-test/suite/innodb/r/innodb_bug53756.result @@ -9,8 +9,8 @@ COMMIT; # Start a transaction in the default connection for isolation. START TRANSACTION; -SELECT @@tx_isolation; -@@tx_isolation +SELECT @@transaction_isolation; +@@transaction_isolation READ-COMMITTED SELECT * FROM bug_53756; pk c1 @@ -20,38 +20,38 @@ pk c1 4 44 connect con1,localhost,root,,; START TRANSACTION; -SELECT @@tx_isolation; -@@tx_isolation +SELECT @@transaction_isolation; +@@transaction_isolation READ-COMMITTED DELETE FROM bug_53756 WHERE pk=1; connect con2,localhost,root,,; START TRANSACTION; -SELECT @@tx_isolation; -@@tx_isolation +SELECT @@transaction_isolation; +@@transaction_isolation READ-COMMITTED DELETE FROM bug_53756 WHERE pk=2; connect con3,localhost,root,,; START TRANSACTION; -SELECT @@tx_isolation; -@@tx_isolation +SELECT @@transaction_isolation; +@@transaction_isolation READ-COMMITTED UPDATE bug_53756 SET c1=77 WHERE pk=3; connect con4,localhost,root,,; START TRANSACTION; -SELECT @@tx_isolation; -@@tx_isolation +SELECT @@transaction_isolation; +@@transaction_isolation READ-COMMITTED UPDATE bug_53756 SET c1=88 WHERE pk=4; connect con5,localhost,root,,; START TRANSACTION; -SELECT @@tx_isolation; -@@tx_isolation +SELECT @@transaction_isolation; +@@transaction_isolation READ-COMMITTED INSERT INTO bug_53756 VALUES(5, 55); connect con6,localhost,root,,; START TRANSACTION; -SELECT @@tx_isolation; -@@tx_isolation +SELECT @@transaction_isolation; +@@transaction_isolation READ-COMMITTED INSERT INTO bug_53756 VALUES(6, 66); connection con1; diff --git a/mysql-test/suite/innodb/r/innodb_lock_wait_timeout_1.result b/mysql-test/suite/innodb/r/innodb_lock_wait_timeout_1.result index 797c30d700f..52b85cf64e2 100644 --- a/mysql-test/suite/innodb/r/innodb_lock_wait_timeout_1.result +++ b/mysql-test/suite/innodb/r/innodb_lock_wait_timeout_1.result @@ -87,9 +87,9 @@ drop table t1, t2; # handler::unlock_row() in InnoDB does nothing. # Thus in order to reproduce the condition that led to the # warning, one needs to relax isolation by either -# setting a weaker tx_isolation value, or by turning on +# setting a weaker transaction_isolation value, or by turning on # the unsafe replication switch. -set @@session.tx_isolation="read-committed"; +set @@session.transaction_isolation="read-committed"; # Prepare data. We need a table with a unique index, # for join_read_key to be used. The other column # allows to control what passes WHERE clause filter. @@ -354,7 +354,7 @@ ERROR HY000: Lock wait timeout exceeded; try restarting transaction connection default; commit; disconnect con1; -set @@session.tx_isolation=default; +set @@session.transaction_isolation=default; drop table t1; # # End of 5.1 tests diff --git a/mysql-test/suite/innodb/r/innodb_mysql.result b/mysql-test/suite/innodb/r/innodb_mysql.result index 7d5dc38f52c..18814e26c82 100644 --- a/mysql-test/suite/innodb/r/innodb_mysql.result +++ b/mysql-test/suite/innodb/r/innodb_mysql.result @@ -2301,10 +2301,10 @@ drop table t1, t2; # SET SESSION BINLOG_FORMAT=STATEMENT; SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; -select @@session.sql_log_bin, @@session.binlog_format, @@session.tx_isolation; +select @@session.sql_log_bin, @@session.binlog_format, @@session.transaction_isolation; @@session.sql_log_bin 1 @@session.binlog_format STATEMENT -@@session.tx_isolation READ-COMMITTED +@@session.transaction_isolation READ-COMMITTED CREATE TABLE t1 ( a INT ) ENGINE=InnoDB; INSERT INTO t1 VALUES(1); DROP TABLE t1; diff --git a/mysql-test/suite/innodb/r/insert_into_empty.result b/mysql-test/suite/innodb/r/insert_into_empty.result index 45f3dd7654c..7bbbc2c5d43 100644 --- a/mysql-test/suite/innodb/r/insert_into_empty.result +++ b/mysql-test/suite/innodb/r/insert_into_empty.result @@ -41,7 +41,7 @@ SET GLOBAL innodb_adaptive_hash_index = @save_ahi; # CREATE TEMPORARY TABLE t (c INT) ENGINE=InnoDB; CREATE TEMPORARY TABLE t2 (c INT) ENGINE=InnoDB; -SET tx_read_only=1; +SET transaction_read_only=1; BEGIN; INSERT INTO t2 VALUES(0); INSERT INTO t VALUES(0); @@ -53,7 +53,7 @@ COMMIT; INSERT INTO t VALUES(0); DROP TEMPORARY TABLE t,t2; ERROR 25006: Cannot execute statement in a READ ONLY transaction -SET tx_read_only=0; +SET transaction_read_only=0; DROP TEMPORARY TABLE t,t2; # # MDEV-24818 Optimize multiple INSERT into empty table diff --git a/mysql-test/suite/innodb/r/temporary_table.result b/mysql-test/suite/innodb/r/temporary_table.result index ffcee726f0d..e90302daba4 100644 --- a/mysql-test/suite/innodb/r/temporary_table.result +++ b/mysql-test/suite/innodb/r/temporary_table.result @@ -709,12 +709,12 @@ UPDATE t1 SET a= 2; COMMIT; DROP TABLE t1; CREATE TEMPORARY TABLE t(c INT) ENGINE=InnoDB; -SET SESSION tx_read_only=TRUE; +SET SESSION TRANSACTION_READ_ONLY=TRUE; LOCK TABLE test.t READ; SELECT * FROM t; c INSERT INTO t VALUES(0xADC3); -SET SESSION tx_read_only=FALSE; +SET SESSION TRANSACTION_READ_ONLY=FALSE; DROP TABLE t; CREATE TEMPORARY TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB; INSERT INTO t1 VALUES (1); @@ -748,27 +748,27 @@ COMMIT; DROP TABLE t1; CREATE TEMPORARY TABLE tmp (a INT) ENGINE=InnoDB; INSERT INTO tmp () VALUES (),(); -SET TX_READ_ONLY= 1; +SET TRANSACTION_READ_ONLY= 1; INSERT INTO tmp SELECT * FROM tmp; -SET TX_READ_ONLY= 0; +SET TRANSACTION_READ_ONLY= 0; DROP TABLE tmp; SET sql_mode=''; -SET GLOBAL tx_read_only=TRUE; +SET GLOBAL TRANSACTION_READ_ONLY=TRUE; CREATE TEMPORARY TABLE t (c INT); -SET SESSION tx_read_only=DEFAULT; +SET SESSION TRANSACTION_READ_ONLY=DEFAULT; INSERT INTO t VALUES(1); INSERT INTO t SELECT * FROM t; -SET SESSION tx_read_only=FALSE; -SET GLOBAL tx_read_only=OFF; +SET SESSION TRANSACTION_READ_ONLY=FALSE; +SET GLOBAL TRANSACTION_READ_ONLY=OFF; DROP TABLE t; CREATE TEMPORARY TABLE t(a INT); -SET SESSION tx_read_only=ON; +SET SESSION TRANSACTION_READ_ONLY=ON; LOCK TABLE t READ; SELECT COUNT(*)FROM t; COUNT(*) 0 INSERT INTO t VALUES (0); -SET SESSION tx_read_only=OFF; +SET SESSION TRANSACTION_READ_ONLY=OFF; DROP TABLE t; CREATE TEMPORARY TABLE t (a INT) ENGINE=InnoDB; INSERT INTO t VALUES (1); |