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 | |
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')
65 files changed, 1059 insertions, 810 deletions
diff --git a/mysql-test/suite/binlog/r/binlog_stm_do_db.result b/mysql-test/suite/binlog/r/binlog_stm_do_db.result index c39404aef55..391158b1723 100644 --- a/mysql-test/suite/binlog/r/binlog_stm_do_db.result +++ b/mysql-test/suite/binlog/r/binlog_stm_do_db.result @@ -1,6 +1,6 @@ RESET MASTER; -SET @old_isolation_level= @@session.tx_isolation; -SET @@session.tx_isolation= 'READ-COMMITTED'; +SET @old_isolation_level= @@session.transaction_isolation; +SET @@session.transaction_isolation= 'READ-COMMITTED'; CREATE DATABASE b42829; use b42829; CREATE TABLE t1 (x int, y int) engine=InnoDB; @@ -43,4 +43,4 @@ master-bin.000001 # Gtid # # GTID #-#-# master-bin.000001 # Query # # use `b42829`; CREATE TABLE t2 (x int, y int) engine=InnoDB DROP DATABASE b42829; DROP DATABASE b42829_filtered; -SET @@session.tx_isolation= @old_isolation_level; +SET @@session.transaction_isolation= @old_isolation_level; diff --git a/mysql-test/suite/binlog/t/binlog_stm_do_db.test b/mysql-test/suite/binlog/t/binlog_stm_do_db.test index 3ed1734f18d..c83753eb68d 100644 --- a/mysql-test/suite/binlog/t/binlog_stm_do_db.test +++ b/mysql-test/suite/binlog/t/binlog_stm_do_db.test @@ -19,7 +19,7 @@ # # The test is implemented as follows: # -# i) set tx_isolation to read-committed. +# i) set transaction_isolation to read-committed. # # ii) create two databases (one filtered other not - using # binlog-do-db) @@ -38,8 +38,8 @@ -- source include/have_innodb.inc -- source include/have_binlog_format_statement.inc RESET MASTER; # clear up binlogs -SET @old_isolation_level= @@session.tx_isolation; -SET @@session.tx_isolation= 'READ-COMMITTED'; +SET @old_isolation_level= @@session.transaction_isolation; +SET @@session.transaction_isolation= 'READ-COMMITTED'; -- let $engine= InnoDB -- let $filtered= b42829_filtered @@ -87,4 +87,4 @@ source include/show_binlog_events.inc; -- eval DROP DATABASE $not_filtered -- eval DROP DATABASE $filtered -SET @@session.tx_isolation= @old_isolation_level; +SET @@session.transaction_isolation= @old_isolation_level; diff --git a/mysql-test/suite/engines/rr_trx/include/rr_init.test b/mysql-test/suite/engines/rr_trx/include/rr_init.test index 7d08c4565e2..579df37dc06 100644 --- a/mysql-test/suite/engines/rr_trx/include/rr_init.test +++ b/mysql-test/suite/engines/rr_trx/include/rr_init.test @@ -17,7 +17,7 @@ if (`SELECT @@default_storage_engine LIKE 'InnoDB' AND @@version LIKE '%6.%'`) SHOW VARIABLES LIKE 'default_storage_engine'; # Verify default isolation level -SHOW VARIABLES LIKE 'tx_isolation'; +SHOW VARIABLES LIKE 'transaction_isolation'; # # Create table for keeping track of test metadata/statistics (counters etc.). diff --git a/mysql-test/suite/engines/rr_trx/r/init_innodb.result b/mysql-test/suite/engines/rr_trx/r/init_innodb.result index 292575a6610..cecaeab4902 100644 --- a/mysql-test/suite/engines/rr_trx/r/init_innodb.result +++ b/mysql-test/suite/engines/rr_trx/r/init_innodb.result @@ -2,9 +2,9 @@ SET @@default_storage_engine = 'InnoDB'; SHOW VARIABLES LIKE 'default_storage_engine'; Variable_name Value storage_engine InnoDB -SHOW VARIABLES LIKE 'tx_isolation'; +SHOW VARIABLES LIKE 'transaction_isolation'; Variable_name Value -tx_isolation REPEATABLE-READ +transaction_isolation REPEATABLE-READ CREATE TABLE statistics ( tx_errors INTEGER NOT NULL ); diff --git a/mysql-test/suite/galera/r/MDEV-29142.result b/mysql-test/suite/galera/r/MDEV-29142.result index 2528520e236..2e567666c47 100644 --- a/mysql-test/suite/galera/r/MDEV-29142.result +++ b/mysql-test/suite/galera/r/MDEV-29142.result @@ -11,7 +11,7 @@ call mtr.add_suppression("WSREP: Failed to open table mysql.wsrep_streaming_log call mtr.add_suppression("WSREP: Failed to open SR table for write"); call mtr.add_suppression("WSREP: Failed to recover SR transactions from schema: wsrep_on : 0"); call mtr.add_suppression("WSREP: Inconsistency detected: Inconsistent by consensus on.*"); -SET @@global.tx_read_only = ON; +SET @@global.transaction_read_only = ON; SET default_storage_engine = SEQUENCE; create table t1 (c1 int); ERROR HY000: Can't create table `test`.`t1` (errno: 131 "Command not supported by the engine") diff --git a/mysql-test/suite/galera/t/MDEV-29142.test b/mysql-test/suite/galera/t/MDEV-29142.test index 7e9776b005b..316b7f26000 100644 --- a/mysql-test/suite/galera/t/MDEV-29142.test +++ b/mysql-test/suite/galera/t/MDEV-29142.test @@ -18,7 +18,7 @@ call mtr.add_suppression("WSREP: Failed to open SR table for write"); call mtr.add_suppression("WSREP: Failed to recover SR transactions from schema: wsrep_on : 0"); call mtr.add_suppression("WSREP: Inconsistency detected: Inconsistent by consensus on.*"); -SET @@global.tx_read_only = ON; +SET @@global.transaction_read_only = ON; --error 0,1286 SET default_storage_engine = SEQUENCE; --error 1005 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); diff --git a/mysql-test/suite/innodb/t/innodb-blob.test b/mysql-test/suite/innodb/t/innodb-blob.test index 301966fb41d..9ae13ca2fec 100644 --- a/mysql-test/suite/innodb/t/innodb-blob.test +++ b/mysql-test/suite/innodb/t/innodb-blob.test @@ -60,7 +60,7 @@ 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; +SELECT @@transaction_isolation; # this one should see (3,NULL_BLOB) SELECT a, RIGHT(b,20) FROM t1; @@ -98,7 +98,7 @@ 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; +SELECT @@transaction_isolation; SELECT a, RIGHT(b,20), RIGHT(c,20) FROM t3; set debug_sync='now SIGNAL go_upd'; diff --git a/mysql-test/suite/innodb/t/innodb.test b/mysql-test/suite/innodb/t/innodb.test index 9211a51dcfc..bb2eef7ba0c 100644 --- a/mysql-test/suite/innodb/t/innodb.test +++ b/mysql-test/suite/innodb/t/innodb.test @@ -724,7 +724,7 @@ create table t1 (id int unsigned not null auto_increment, code tinyint unsigned BEGIN; SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; -SELECT @@tx_isolation,@@global.tx_isolation; +SELECT @@transaction_isolation, @@global.transaction_isolation; insert into t1 (code, name) values (1, 'Tim'), (1, 'Monty'), (2, 'David'); select id, code, name from t1 order by id; COMMIT; @@ -2381,7 +2381,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; CREATE TABLE t1 ( a int ) ENGINE=InnoDB; @@ -2389,7 +2389,7 @@ CREATE TABLE t2 LIKE t1; SELECT * FROM t2; 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; @@ -2401,12 +2401,12 @@ 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; 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/t/innodb_bug40360.test b/mysql-test/suite/innodb/t/innodb_bug40360.test index f5187d55092..8e7b309e621 100644 --- a/mysql-test/suite/innodb/t/innodb_bug40360.test +++ b/mysql-test/suite/innodb/t/innodb_bug40360.test @@ -5,7 +5,7 @@ -- source include/not_embedded.inc -SET TX_ISOLATION='READ-COMMITTED'; +SET TRANSACTION_ISOLATION='READ-COMMITTED'; # This is the default since MySQL 5.1.29 SET BINLOG_FORMAT='STATEMENT'; diff --git a/mysql-test/suite/innodb/t/innodb_bug49164.test b/mysql-test/suite/innodb/t/innodb_bug49164.test index 159475ba10f..f19fb519168 100644 --- a/mysql-test/suite/innodb/t/innodb_bug49164.test +++ b/mysql-test/suite/innodb/t/innodb_bug49164.test @@ -8,7 +8,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; @@ -25,7 +25,7 @@ update bug49164 set c=7; connection con2; -SET tx_isolation = 'READ-COMMITTED'; +SET transaction_isolation = 'READ-COMMITTED'; begin; select * from bug49164; commit; diff --git a/mysql-test/suite/innodb/t/innodb_bug53756.test b/mysql-test/suite/innodb/t/innodb_bug53756.test index a676868aea7..86633e1c124 100644 --- a/mysql-test/suite/innodb/t/innodb_bug53756.test +++ b/mysql-test/suite/innodb/t/innodb_bug53756.test @@ -25,37 +25,37 @@ COMMIT; --echo --echo # Start a transaction in the default connection for isolation. START TRANSACTION; -SELECT @@tx_isolation; +SELECT @@transaction_isolation; SELECT * FROM bug_53756; --connect (con1,localhost,root,,) START TRANSACTION; -SELECT @@tx_isolation; +SELECT @@transaction_isolation; DELETE FROM bug_53756 WHERE pk=1; --connect (con2,localhost,root,,) START TRANSACTION; -SELECT @@tx_isolation; +SELECT @@transaction_isolation; DELETE FROM bug_53756 WHERE pk=2; --connect (con3,localhost,root,,) START TRANSACTION; -SELECT @@tx_isolation; +SELECT @@transaction_isolation; UPDATE bug_53756 SET c1=77 WHERE pk=3; --connect (con4,localhost,root,,) START TRANSACTION; -SELECT @@tx_isolation; +SELECT @@transaction_isolation; UPDATE bug_53756 SET c1=88 WHERE pk=4; --connect (con5,localhost,root,,) START TRANSACTION; -SELECT @@tx_isolation; +SELECT @@transaction_isolation; INSERT INTO bug_53756 VALUES(5, 55); --connect (con6,localhost,root,,) START TRANSACTION; -SELECT @@tx_isolation; +SELECT @@transaction_isolation; INSERT INTO bug_53756 VALUES(6, 66); --connection con1 diff --git a/mysql-test/suite/innodb/t/innodb_lock_wait_timeout_1.test b/mysql-test/suite/innodb/t/innodb_lock_wait_timeout_1.test index ea31d4a14c5..56a86a2c4d9 100644 --- a/mysql-test/suite/innodb/t/innodb_lock_wait_timeout_1.test +++ b/mysql-test/suite/innodb/t/innodb_lock_wait_timeout_1.test @@ -108,9 +108,9 @@ drop table t1, t2; --echo # handler::unlock_row() in InnoDB does nothing. --echo # Thus in order to reproduce the condition that led to the --echo # warning, one needs to relax isolation by either ---echo # setting a weaker tx_isolation value, or by turning on +--echo # setting a weaker transaction_isolation value, or by turning on --echo # the unsafe replication switch. -set @@session.tx_isolation="read-committed"; +set @@session.transaction_isolation="read-committed"; --echo # Prepare data. We need a table with a unique index, --echo # for join_read_key to be used. The other column @@ -236,7 +236,7 @@ connection default; commit; disconnect con1; -set @@session.tx_isolation=default; +set @@session.transaction_isolation=default; drop table t1; --echo # diff --git a/mysql-test/suite/innodb/t/innodb_mysql.test b/mysql-test/suite/innodb/t/innodb_mysql.test index d495186db25..c4453302166 100644 --- a/mysql-test/suite/innodb/t/innodb_mysql.test +++ b/mysql-test/suite/innodb/t/innodb_mysql.test @@ -41,7 +41,7 @@ drop table t1, t2; SET SESSION BINLOG_FORMAT=STATEMENT; SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; -query_vertical select @@session.sql_log_bin, @@session.binlog_format, @@session.tx_isolation; +query_vertical select @@session.sql_log_bin, @@session.binlog_format, @@session.transaction_isolation; CREATE TABLE t1 ( a INT ) ENGINE=InnoDB; INSERT INTO t1 VALUES(1); DROP TABLE t1; diff --git a/mysql-test/suite/innodb/t/insert_into_empty.test b/mysql-test/suite/innodb/t/insert_into_empty.test index 8452cecf700..60f04133c82 100644 --- a/mysql-test/suite/innodb/t/insert_into_empty.test +++ b/mysql-test/suite/innodb/t/insert_into_empty.test @@ -47,7 +47,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); @@ -60,7 +60,7 @@ INSERT INTO t VALUES(0); --error ER_CANT_EXECUTE_IN_READ_ONLY_TRANSACTION DROP TEMPORARY TABLE t,t2; -SET tx_read_only=0; +SET transaction_read_only=0; DROP TEMPORARY TABLE t,t2; --echo # diff --git a/mysql-test/suite/innodb/t/temporary_table.test b/mysql-test/suite/innodb/t/temporary_table.test index 6b2cd6b9b2c..6f3f293f2a2 100644 --- a/mysql-test/suite/innodb/t/temporary_table.test +++ b/mysql-test/suite/innodb/t/temporary_table.test @@ -541,11 +541,11 @@ 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; 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; @@ -585,27 +585,27 @@ 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; 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; diff --git a/mysql-test/suite/innodb_fts/r/innodb_fts_transaction.result b/mysql-test/suite/innodb_fts/r/innodb_fts_transaction.result index fc6a1d1afe7..f372652cb70 100644 --- a/mysql-test/suite/innodb_fts/r/innodb_fts_transaction.result +++ b/mysql-test/suite/innodb_fts/r/innodb_fts_transaction.result @@ -534,8 +534,8 @@ INSERT INTO t1 (a,b) VALUES ('Sævör grét', 'áðan því úlpan var ónýt'); connect con2,localhost,root,,; SET NAMES UTF8; -select @@session.tx_isolation; -@@session.tx_isolation +select @@session.transaction_isolation; +@@session.transaction_isolation REPEATABLE-READ CREATE FULLTEXT INDEX idx on t1 (a,b); INSERT INTO t1 (a,b) VALUES @@ -649,8 +649,8 @@ INSERT INTO t1 (a,b) VALUES ('Я могу есть стекло', 'оно мне Mне вредит'); connect con2,localhost,root,,; SET NAMES UTF8; -select @@session.tx_isolation; -@@session.tx_isolation +select @@session.transaction_isolation; +@@session.transaction_isolation REPEATABLE-READ CREATE FULLTEXT INDEX idx on t1 (a,b); INSERT INTO t1 (a,b) VALUES @@ -794,8 +794,8 @@ INSERT INTO t1 (a,b) VALUES ('Sævör grét', 'áðan því úlpan var ónýt'); connect con2,localhost,root,,; SET NAMES UTF8; -select @@session.tx_isolation; -@@session.tx_isolation +select @@session.transaction_isolation; +@@session.transaction_isolation REPEATABLE-READ CREATE FULLTEXT INDEX idx on t1 (a,b); INSERT INTO t1 (a,b) VALUES @@ -914,8 +914,8 @@ INSERT INTO t1 (a,b) VALUES ('Я могу есть стекло', 'оно мне Mне вредит'); connect con2,localhost,root,,; SET NAMES UTF8; -select @@session.tx_isolation; -@@session.tx_isolation +select @@session.transaction_isolation; +@@session.transaction_isolation REPEATABLE-READ CREATE FULLTEXT INDEX idx on t1 (a,b); INSERT INTO t1 (a,b) VALUES diff --git a/mysql-test/suite/innodb_fts/t/innodb_fts_transaction.test b/mysql-test/suite/innodb_fts/t/innodb_fts_transaction.test index 026aeb635cd..463de63c3f9 100644 --- a/mysql-test/suite/innodb_fts/t/innodb_fts_transaction.test +++ b/mysql-test/suite/innodb_fts/t/innodb_fts_transaction.test @@ -608,7 +608,7 @@ INSERT INTO t1 (a,b) VALUES --connect (con2,localhost,root,,) SET NAMES UTF8; -select @@session.tx_isolation; +select @@session.transaction_isolation; # Create the FTS index again CREATE FULLTEXT INDEX idx on t1 (a,b); @@ -704,7 +704,7 @@ INSERT INTO t1 (a,b) VALUES --connect (con2,localhost,root,,) SET NAMES UTF8; -select @@session.tx_isolation; +select @@session.transaction_isolation; # Create the FTS index again CREATE FULLTEXT INDEX idx on t1 (a,b); @@ -823,7 +823,7 @@ INSERT INTO t1 (a,b) VALUES --connect (con2,localhost,root,,) SET NAMES UTF8; -select @@session.tx_isolation; +select @@session.transaction_isolation; # Create the FTS index again CREATE FULLTEXT INDEX idx on t1 (a,b); @@ -919,7 +919,7 @@ INSERT INTO t1 (a,b) VALUES --connect (con2,localhost,root,,) SET NAMES UTF8; -select @@session.tx_isolation; +select @@session.transaction_isolation; # Create the FTS index again CREATE FULLTEXT INDEX idx on t1 (a,b); diff --git a/mysql-test/suite/innodb_gis/r/repeatable_spatial.result b/mysql-test/suite/innodb_gis/r/repeatable_spatial.result index 065312f612f..561b1f77ae7 100644 --- a/mysql-test/suite/innodb_gis/r/repeatable_spatial.result +++ b/mysql-test/suite/innodb_gis/r/repeatable_spatial.result @@ -19,8 +19,8 @@ COUNT(*) connect con1,localhost,root,,; connection con1; SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ; -SELECT @@tx_isolation; -@@tx_isolation +SELECT @@transaction_isolation; +@@transaction_isolation REPEATABLE-READ SELECT COUNT(*) FROM tab; COUNT(*) @@ -32,8 +32,8 @@ c1 ST_AsText(c2) ST_AsText(c3) ST_AsText(c4) ST_AsText(c5) connect con2,localhost,root,,; connection con2; SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; -SELECT @@tx_isolation; -@@tx_isolation +SELECT @@transaction_isolation; +@@transaction_isolation READ-COMMITTED START TRANSACTION; INSERT INTO tab(c1,c2,c3,c4,c5) @@ -106,8 +106,8 @@ COUNT(*) connect con1,localhost,root,,; connection con1; SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; -SELECT @@tx_isolation; -@@tx_isolation +SELECT @@transaction_isolation; +@@transaction_isolation REPEATABLE-READ SELECT COUNT(*) FROM tab; COUNT(*) @@ -119,8 +119,8 @@ c1 ST_AsText(c2) ST_AsText(c3) ST_AsText(c4) ST_AsText(c5) connect con2,localhost,root,,; connection con2; SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; -SELECT @@tx_isolation; -@@tx_isolation +SELECT @@transaction_isolation; +@@transaction_isolation READ-COMMITTED START TRANSACTION; INSERT INTO tab(c1,c2,c3,c4,c5) diff --git a/mysql-test/suite/innodb_gis/r/rtree_concurrent_srch.result b/mysql-test/suite/innodb_gis/r/rtree_concurrent_srch.result index 81b82691876..9d25d0e96b4 100644 --- a/mysql-test/suite/innodb_gis/r/rtree_concurrent_srch.result +++ b/mysql-test/suite/innodb_gis/r/rtree_concurrent_srch.result @@ -58,8 +58,8 @@ insert into t1 select * from t1; insert into t1 select * from t1; connection a; set session transaction isolation level serializable; -select @@tx_isolation; -@@tx_isolation +select @@transaction_isolation; +@@transaction_isolation SERIALIZABLE start transaction; set @g1 = ST_GeomFromText('Polygon((100 100, 100 110, 110 110, 110 100, 100 100))'); @@ -69,8 +69,8 @@ count(*) connect b,localhost,root,,; set session transaction isolation level serializable; set session innodb_lock_wait_timeout = 1; -select @@tx_isolation; -@@tx_isolation +select @@transaction_isolation; +@@transaction_isolation SERIALIZABLE insert into t1 select * from t1; insert into t1 select * from t1; @@ -92,8 +92,8 @@ connection b; select @@innodb_lock_wait_timeout; @@innodb_lock_wait_timeout 1 -select @@tx_isolation; -@@tx_isolation +select @@transaction_isolation; +@@transaction_isolation SERIALIZABLE insert into t1 select * from t1; ERROR HY000: Lock wait timeout exceeded; try restarting transaction @@ -120,8 +120,8 @@ insert into t1 select * from t1; insert into t1 select * from t1; connection a; set session transaction isolation level serializable; -select @@tx_isolation; -@@tx_isolation +select @@transaction_isolation; +@@transaction_isolation SERIALIZABLE start transaction; set @g1 = ST_GeomFromText('Polygon((100 100, 100 110, 110 110, 110 100, 100 100))'); @@ -131,8 +131,8 @@ count(*) connection b; set session transaction isolation level serializable; set session innodb_lock_wait_timeout = 1; -select @@tx_isolation; -@@tx_isolation +select @@transaction_isolation; +@@transaction_isolation SERIALIZABLE insert into t1 select * from t1; insert into t1 select * from t1; @@ -158,8 +158,8 @@ connection b; select @@innodb_lock_wait_timeout; @@innodb_lock_wait_timeout 1 -select @@tx_isolation; -@@tx_isolation +select @@transaction_isolation; +@@transaction_isolation SERIALIZABLE INSERT INTO t1 VALUES (1, ST_GeomFromText('LineString(8 105, 200 105)')); ERROR HY000: Lock wait timeout exceeded; try restarting transaction @@ -227,8 +227,8 @@ end| CALL insert_t1(0, 1000); connection a; set session transaction isolation level serializable; -select @@tx_isolation; -@@tx_isolation +select @@transaction_isolation; +@@transaction_isolation SERIALIZABLE start transaction; set @g1 = ST_GeomFromText('Polygon((800 800, 800 1000, 1000 1000, 1000 800, 800 800))'); diff --git a/mysql-test/suite/innodb_gis/t/repeatable_spatial.test b/mysql-test/suite/innodb_gis/t/repeatable_spatial.test index 3152911b257..16372dc14dc 100644 --- a/mysql-test/suite/innodb_gis/t/repeatable_spatial.test +++ b/mysql-test/suite/innodb_gis/t/repeatable_spatial.test @@ -40,7 +40,7 @@ connection con1; SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ; -SELECT @@tx_isolation; +SELECT @@transaction_isolation; # Record count should be 1 SELECT COUNT(*) FROM tab; @@ -54,7 +54,7 @@ connection con2; SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; -SELECT @@tx_isolation; +SELECT @@transaction_isolation; START TRANSACTION; @@ -155,7 +155,7 @@ connection con1; SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; -SELECT @@tx_isolation; +SELECT @@transaction_isolation; # Record count should be 1 SELECT COUNT(*) FROM tab; @@ -169,7 +169,7 @@ connection con2; SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; -SELECT @@tx_isolation; +SELECT @@transaction_isolation; START TRANSACTION; diff --git a/mysql-test/suite/innodb_gis/t/rtree_concurrent_srch.test b/mysql-test/suite/innodb_gis/t/rtree_concurrent_srch.test index c4a6448f387..017c650fca3 100644 --- a/mysql-test/suite/innodb_gis/t/rtree_concurrent_srch.test +++ b/mysql-test/suite/innodb_gis/t/rtree_concurrent_srch.test @@ -79,7 +79,7 @@ insert into t1 select * from t1; # Connection 'a' will place predicate lock on almost all pages connection a; set session transaction isolation level serializable; -select @@tx_isolation; +select @@transaction_isolation; start transaction; set @g1 = ST_GeomFromText('Polygon((100 100, 100 110, 110 110, 110 100, 100 100))'); select count(*) from t1 where MBRwithin(t1.c2, @g1); @@ -89,7 +89,7 @@ connect (b,localhost,root,,); set session transaction isolation level serializable; set session innodb_lock_wait_timeout = 1; -select @@tx_isolation; +select @@transaction_isolation; insert into t1 select * from t1; insert into t1 select * from t1; @@ -115,7 +115,7 @@ select count(*) from t1 where MBRwithin(t1.c2, @g1); connection b; select @@innodb_lock_wait_timeout; -select @@tx_isolation; +select @@transaction_isolation; --error ER_LOCK_WAIT_TIMEOUT insert into t1 select * from t1; @@ -146,7 +146,7 @@ insert into t1 select * from t1; # Connection 'a' will place predicate lock on almost all pages connection a; set session transaction isolation level serializable; -select @@tx_isolation; +select @@transaction_isolation; start transaction; set @g1 = ST_GeomFromText('Polygon((100 100, 100 110, 110 110, 110 100, 100 100))'); select count(*) from t1 where MBRwithin(t1.c2, @g1); @@ -156,7 +156,7 @@ connection b; set session transaction isolation level serializable; set session innodb_lock_wait_timeout = 1; -select @@tx_isolation; +select @@transaction_isolation; insert into t1 select * from t1; insert into t1 select * from t1; @@ -181,7 +181,7 @@ select count(*) from t1 where MBRIntersects(t1.c2, @g1); connection b; select @@innodb_lock_wait_timeout; -select @@tx_isolation; +select @@transaction_isolation; # this should conflict with the "MBRIntersects" predicate lock in session "a" --error ER_LOCK_WAIT_TIMEOUT @@ -264,7 +264,7 @@ CALL insert_t1(0, 1000); # Connection 'a' will place predicate lock on root and last leaf page connection a; set session transaction isolation level serializable; -select @@tx_isolation; +select @@transaction_isolation; start transaction; set @g1 = ST_GeomFromText('Polygon((800 800, 800 1000, 1000 1000, 1000 800, 800 800))'); select count(*) from t1 where MBRwithin(t1.c2, @g1); diff --git a/mysql-test/suite/innodb_zip/r/bug56680.result b/mysql-test/suite/innodb_zip/r/bug56680.result index 3c325351c3a..45c768eeca5 100644 --- a/mysql-test/suite/innodb_zip/r/bug56680.result +++ b/mysql-test/suite/innodb_zip/r/bug56680.result @@ -1,4 +1,4 @@ -SET GLOBAL tx_isolation='REPEATABLE-READ'; +SET GLOBAL transaction_isolation='REPEATABLE-READ'; CREATE TABLE bug56680( a INT AUTO_INCREMENT PRIMARY KEY, b CHAR(1), @@ -28,7 +28,7 @@ connection default; SELECT b FROM bug56680; b x -SET GLOBAL tx_isolation='READ-UNCOMMITTED'; +SET GLOBAL transaction_isolation='READ-UNCOMMITTED'; INSERT INTO bug56680 SELECT 0,b,c FROM bug56680; INSERT INTO bug56680 SELECT 0,b,c FROM bug56680; INSERT INTO bug56680 SELECT 0,b,c FROM bug56680; diff --git a/mysql-test/suite/innodb_zip/r/index_large_prefix.result b/mysql-test/suite/innodb_zip/r/index_large_prefix.result index 81bdcdeb740..0c6a9104f7c 100644 --- a/mysql-test/suite/innodb_zip/r/index_large_prefix.result +++ b/mysql-test/suite/innodb_zip/r/index_large_prefix.result @@ -10,8 +10,8 @@ show warnings; Level Code Message begin; update worklog5743 set a = (repeat("x", 17000)); -select @@session.tx_isolation; -@@session.tx_isolation +select @@session.transaction_isolation; +@@session.transaction_isolation REPEATABLE-READ connect con1,localhost,root,,; select a = repeat("x", 17000) from worklog5743; @@ -22,8 +22,8 @@ a = repeat("b", 16000) 1 connect con2,localhost,root,,; SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -select @@session.tx_isolation; -@@session.tx_isolation +select @@session.transaction_isolation; +@@session.transaction_isolation READ-UNCOMMITTED select a = repeat("x", 17000) from worklog5743; a = repeat("x", 17000) @@ -42,8 +42,8 @@ insert into worklog5743 values(9, repeat("a", 10000)); begin; update worklog5743 set a1 = 1000; connection con1; -select @@session.tx_isolation; -@@session.tx_isolation +select @@session.transaction_isolation; +@@session.transaction_isolation REPEATABLE-READ explain select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9; id select_type table type possible_keys key key_len ref rows Extra @@ -53,8 +53,8 @@ a1 a2 = repeat("a", 10000) 9 1 connection con2; SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -select @@session.tx_isolation; -@@session.tx_isolation +select @@session.transaction_isolation; +@@session.transaction_isolation READ-UNCOMMITTED select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9; a1 a2 = repeat("a", 10000) @@ -68,8 +68,8 @@ insert into worklog5743 values(9, repeat("a", 10000)); begin; update worklog5743 set a1 = 1000; connection con1; -select @@session.tx_isolation; -@@session.tx_isolation +select @@session.transaction_isolation; +@@session.transaction_isolation REPEATABLE-READ explain select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9; id select_type table type possible_keys key key_len ref rows Extra @@ -79,8 +79,8 @@ a1 a2 = repeat("a", 10000) 9 1 connection con2; SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -select @@session.tx_isolation; -@@session.tx_isolation +select @@session.transaction_isolation; +@@session.transaction_isolation READ-UNCOMMITTED select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9; a1 a2 = repeat("a", 10000) @@ -258,8 +258,8 @@ select a1, left(a2, 20) from worklog5743_16; a1 left(a2, 20) 1000 aaaaaaaaaaaaaaaaaaaa connection con1; -select @@session.tx_isolation; -@@session.tx_isolation +select @@session.transaction_isolation; +@@session.transaction_isolation REPEATABLE-READ explain select a1, left(a2, 20) from worklog5743_1 where a1 = 9; id select_type table type possible_keys key key_len ref rows Extra @@ -293,8 +293,8 @@ a1 left(a2, 20) 9 aaaaaaaaaaaaaaaaaaaa connection con2; SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -select @@session.tx_isolation; -@@session.tx_isolation +select @@session.transaction_isolation; +@@session.transaction_isolation READ-UNCOMMITTED select a1, left(a2, 20) from worklog5743_1 where a1 = 9; a1 left(a2, 20) @@ -367,8 +367,8 @@ repeat("a", 3068)); begin; update worklog5743 set a1 = 1000; connection con1; -select @@session.tx_isolation; -@@session.tx_isolation +select @@session.transaction_isolation; +@@session.transaction_isolation REPEATABLE-READ explain select a1 from worklog5743 where a1 = 9; id select_type table type possible_keys key key_len ref rows Extra @@ -378,8 +378,8 @@ a1 9 connection con2; SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -select @@session.tx_isolation; -@@session.tx_isolation +select @@session.transaction_isolation; +@@session.transaction_isolation READ-UNCOMMITTED select a1 from worklog5743 where a1 = 9; a1 @@ -399,8 +399,8 @@ insert into worklog5743 values(repeat("a", 20000)); begin; insert into worklog5743 values(repeat("b", 20000)); update worklog5743 set a = (repeat("x", 25000)); -select @@session.tx_isolation; -@@session.tx_isolation +select @@session.transaction_isolation; +@@session.transaction_isolation REPEATABLE-READ connection con1; select a = repeat("a", 20000) from worklog5743; @@ -409,8 +409,8 @@ a = repeat("a", 20000) disconnect con1; connection con2; SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -select @@session.tx_isolation; -@@session.tx_isolation +select @@session.transaction_isolation; +@@session.transaction_isolation READ-UNCOMMITTED select a = repeat("x", 25000) from worklog5743; a = repeat("x", 25000) diff --git a/mysql-test/suite/innodb_zip/r/index_large_prefix_4k.result b/mysql-test/suite/innodb_zip/r/index_large_prefix_4k.result index d1e7220b0ba..1f3b72ec2c9 100644 --- a/mysql-test/suite/innodb_zip/r/index_large_prefix_4k.result +++ b/mysql-test/suite/innodb_zip/r/index_large_prefix_4k.result @@ -13,8 +13,8 @@ Level Code Message SET sql_mode= default; begin; update worklog5743 set a = (repeat("x", 17000)); -select @@session.tx_isolation; -@@session.tx_isolation +select @@session.transaction_isolation; +@@session.transaction_isolation REPEATABLE-READ connect con1,localhost,root,,; select a = repeat("x", 17000) from worklog5743; @@ -25,8 +25,8 @@ a = repeat("b", 16000) 1 connect con2,localhost,root,,; SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -select @@session.tx_isolation; -@@session.tx_isolation +select @@session.transaction_isolation; +@@session.transaction_isolation READ-UNCOMMITTED select a = repeat("x", 17000) from worklog5743; a = repeat("x", 17000) @@ -45,8 +45,8 @@ insert into worklog5743 values(9, repeat("a", 10000)); begin; update worklog5743 set a1 = 1111; connection con1; -select @@session.tx_isolation; -@@session.tx_isolation +select @@session.transaction_isolation; +@@session.transaction_isolation REPEATABLE-READ explain select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9; id select_type table type possible_keys key key_len ref rows Extra @@ -56,8 +56,8 @@ a1 a2 = repeat("a", 10000) 9 1 connection con2; SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -select @@session.tx_isolation; -@@session.tx_isolation +select @@session.transaction_isolation; +@@session.transaction_isolation READ-UNCOMMITTED select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9; a1 a2 = repeat("a", 10000) @@ -71,8 +71,8 @@ insert into worklog5743 values(9, repeat("a", 10000)); begin; update worklog5743 set a1 = 2222; connection con1; -select @@session.tx_isolation; -@@session.tx_isolation +select @@session.transaction_isolation; +@@session.transaction_isolation REPEATABLE-READ explain select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9; id select_type table type possible_keys key key_len ref rows Extra @@ -82,8 +82,8 @@ a1 a2 = repeat("a", 10000) 9 1 connection con2; SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -select @@session.tx_isolation; -@@session.tx_isolation +select @@session.transaction_isolation; +@@session.transaction_isolation READ-UNCOMMITTED select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9; a1 a2 = repeat("a", 10000) @@ -183,8 +183,8 @@ select a1, left(a2, 20) from worklog5743_4; a1 left(a2, 20) 1000 aaaaaaaaaaaaaaaaaaaa connection con1; -select @@session.tx_isolation; -@@session.tx_isolation +select @@session.transaction_isolation; +@@session.transaction_isolation REPEATABLE-READ explain select a1, left(a2, 20) from worklog5743_1 where a1 = 9; id select_type table type possible_keys key key_len ref rows Extra @@ -206,8 +206,8 @@ a1 left(a2, 20) 9 aaaaaaaaaaaaaaaaaaaa connection con2; SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -select @@session.tx_isolation; -@@session.tx_isolation +select @@session.transaction_isolation; +@@session.transaction_isolation READ-UNCOMMITTED select a1, left(a2, 20) from worklog5743_1 where a1 = 9; a1 left(a2, 20) @@ -261,8 +261,8 @@ insert into worklog5743 values(9, repeat("a", 764)); begin; update worklog5743 set a1 = 4444; connection con1; -select @@session.tx_isolation; -@@session.tx_isolation +select @@session.transaction_isolation; +@@session.transaction_isolation REPEATABLE-READ explain select a1 from worklog5743 where a1 = 9; id select_type table type possible_keys key key_len ref rows Extra @@ -272,8 +272,8 @@ a1 9 connection con2; SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -select @@session.tx_isolation; -@@session.tx_isolation +select @@session.transaction_isolation; +@@session.transaction_isolation READ-UNCOMMITTED select a1 from worklog5743 where a1 = 9; a1 @@ -289,8 +289,8 @@ insert into worklog5743 values(repeat("a", 20000)); begin; insert into worklog5743 values(repeat("b", 20000)); update worklog5743 set a = (repeat("x", 25000)); -select @@session.tx_isolation; -@@session.tx_isolation +select @@session.transaction_isolation; +@@session.transaction_isolation REPEATABLE-READ connection con1; select a = repeat("a", 20000) from worklog5743; @@ -299,8 +299,8 @@ a = repeat("a", 20000) disconnect con1; connection con2; SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -select @@session.tx_isolation; -@@session.tx_isolation +select @@session.transaction_isolation; +@@session.transaction_isolation READ-UNCOMMITTED select a = repeat("x", 25000) from worklog5743; a = repeat("x", 25000) diff --git a/mysql-test/suite/innodb_zip/r/index_large_prefix_8k.result b/mysql-test/suite/innodb_zip/r/index_large_prefix_8k.result index ef5b5984f3c..3474fb637c2 100644 --- a/mysql-test/suite/innodb_zip/r/index_large_prefix_8k.result +++ b/mysql-test/suite/innodb_zip/r/index_large_prefix_8k.result @@ -16,8 +16,8 @@ Note 1071 Specified key was too long; max key length is 1536 bytes SET sql_mode= default; begin; update worklog5743 set a = (repeat("x", 17000)); -select @@session.tx_isolation; -@@session.tx_isolation +select @@session.transaction_isolation; +@@session.transaction_isolation REPEATABLE-READ connect con1,localhost,root,,; select a = repeat("x", 17000) from worklog5743; @@ -28,8 +28,8 @@ a = repeat("b", 16000) 1 connect con2,localhost,root,,; SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -select @@session.tx_isolation; -@@session.tx_isolation +select @@session.transaction_isolation; +@@session.transaction_isolation READ-UNCOMMITTED select a = repeat("x", 17000) from worklog5743; a = repeat("x", 17000) @@ -48,8 +48,8 @@ insert into worklog5743 values(9, repeat("a", 10000)); begin; update worklog5743 set a1 = 1000; connection con1; -select @@session.tx_isolation; -@@session.tx_isolation +select @@session.transaction_isolation; +@@session.transaction_isolation REPEATABLE-READ explain select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9; id select_type table type possible_keys key key_len ref rows Extra @@ -59,8 +59,8 @@ a1 a2 = repeat("a", 10000) 9 1 connection con2; SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -select @@session.tx_isolation; -@@session.tx_isolation +select @@session.transaction_isolation; +@@session.transaction_isolation READ-UNCOMMITTED select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9; a1 a2 = repeat("a", 10000) @@ -74,8 +74,8 @@ insert into worklog5743 values(9, repeat("a", 10000)); begin; update worklog5743 set a1 = 1000; connection con1; -select @@session.tx_isolation; -@@session.tx_isolation +select @@session.transaction_isolation; +@@session.transaction_isolation REPEATABLE-READ explain select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9; id select_type table type possible_keys key key_len ref rows Extra @@ -85,8 +85,8 @@ a1 a2 = repeat("a", 10000) 9 1 connection con2; SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -select @@session.tx_isolation; -@@session.tx_isolation +select @@session.transaction_isolation; +@@session.transaction_isolation READ-UNCOMMITTED select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9; a1 a2 = repeat("a", 10000) @@ -217,8 +217,8 @@ select a1, left(a2, 20) from worklog5743_8; a1 left(a2, 20) 1000 aaaaaaaaaaaaaaaaaaaa connection con1; -select @@session.tx_isolation; -@@session.tx_isolation +select @@session.transaction_isolation; +@@session.transaction_isolation REPEATABLE-READ explain select a1, left(a2, 20) from worklog5743_1 where a1 = 9; id select_type table type possible_keys key key_len ref rows Extra @@ -246,8 +246,8 @@ a1 left(a2, 20) 9 aaaaaaaaaaaaaaaaaaaa connection con2; SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -select @@session.tx_isolation; -@@session.tx_isolation +select @@session.transaction_isolation; +@@session.transaction_isolation READ-UNCOMMITTED select a1, left(a2, 20) from worklog5743_1 where a1 = 9; a1 left(a2, 20) @@ -295,8 +295,8 @@ update worklog5743 set a1 = 1000; begin; update worklog5743 set a1 = 1000; connection con1; -select @@session.tx_isolation; -@@session.tx_isolation +select @@session.transaction_isolation; +@@session.transaction_isolation REPEATABLE-READ explain select a1 from worklog5743 where a1 = 9; id select_type table type possible_keys key key_len ref rows Extra @@ -305,8 +305,8 @@ select a1 from worklog5743 where a1 = 9; a1 connection con2; SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -select @@session.tx_isolation; -@@session.tx_isolation +select @@session.transaction_isolation; +@@session.transaction_isolation READ-UNCOMMITTED select a1 from worklog5743 where a1 = 9; a1 @@ -325,8 +325,8 @@ insert into worklog5743 values(repeat("a", 20000)); begin; insert into worklog5743 values(repeat("b", 20000)); update worklog5743 set a = (repeat("x", 25000)); -select @@session.tx_isolation; -@@session.tx_isolation +select @@session.transaction_isolation; +@@session.transaction_isolation REPEATABLE-READ connection con1; select a = repeat("a", 20000) from worklog5743; @@ -335,8 +335,8 @@ a = repeat("a", 20000) disconnect con1; connection con2; SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -select @@session.tx_isolation; -@@session.tx_isolation +select @@session.transaction_isolation; +@@session.transaction_isolation READ-UNCOMMITTED select a = repeat("x", 25000) from worklog5743; a = repeat("x", 25000) diff --git a/mysql-test/suite/innodb_zip/r/prefix_index_liftedlimit.result b/mysql-test/suite/innodb_zip/r/prefix_index_liftedlimit.result index 43a63a29e71..d29082ad9d4 100644 --- a/mysql-test/suite/innodb_zip/r/prefix_index_liftedlimit.result +++ b/mysql-test/suite/innodb_zip/r/prefix_index_liftedlimit.result @@ -793,16 +793,16 @@ col_1_text = REPEAT("a", 200) col_2_text = REPEAT("o", 200) 1 1 0 1 connection con1; -select @@session.tx_isolation; -@@session.tx_isolation +select @@session.transaction_isolation; +@@session.transaction_isolation REPEATABLE-READ SELECT col_1_text = REPEAT("b", 200) , col_2_text = REPEAT("o", 200) FROM worklog5743; col_1_text = REPEAT("b", 200) col_2_text = REPEAT("o", 200) 0 1 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -select @@session.tx_isolation; -@@session.tx_isolation +select @@session.transaction_isolation; +@@session.transaction_isolation READ-UNCOMMITTED SELECT col_1_text = REPEAT("b", 200) , col_2_text = REPEAT("o", 200) FROM worklog5743; @@ -864,8 +864,8 @@ col_1_text = REPEAT("a", 200) col_2_text = REPEAT("o", 200) COMMIT; connection con1; SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -select @@session.tx_isolation; -@@session.tx_isolation +select @@session.transaction_isolation; +@@session.transaction_isolation READ-UNCOMMITTED SELECT col_1_text = REPEAT("b", 200) , col_2_text = REPEAT("o", 200) FROM worklog5743; diff --git a/mysql-test/suite/innodb_zip/t/bug56680.test b/mysql-test/suite/innodb_zip/t/bug56680.test index 0d441dea0f5..adb632afe37 100644 --- a/mysql-test/suite/innodb_zip/t/bug56680.test +++ b/mysql-test/suite/innodb_zip/t/bug56680.test @@ -15,7 +15,7 @@ -- error 0,ER_UNKNOWN_SYSTEM_VARIABLE SET GLOBAL innodb_change_buffering_debug = 1; -- enable_query_log -SET GLOBAL tx_isolation='REPEATABLE-READ'; +SET GLOBAL transaction_isolation='REPEATABLE-READ'; CREATE TABLE bug56680( a INT AUTO_INCREMENT PRIMARY KEY, @@ -50,7 +50,7 @@ SELECT b FROM bug56680; # For the rest of this test, use the READ UNCOMMITTED isolation level # to see what exists in the secondary index. -SET GLOBAL tx_isolation='READ-UNCOMMITTED'; +SET GLOBAL transaction_isolation='READ-UNCOMMITTED'; # Create enough rows for the table, so that the insert buffer will be # used for modifying the secondary index page. There must be multiple diff --git a/mysql-test/suite/innodb_zip/t/index_large_prefix.test b/mysql-test/suite/innodb_zip/t/index_large_prefix.test index 5c1e31a2db7..65634542c1f 100644 --- a/mysql-test/suite/innodb_zip/t/index_large_prefix.test +++ b/mysql-test/suite/innodb_zip/t/index_large_prefix.test @@ -40,7 +40,7 @@ update worklog5743 set a = (repeat("x", 17000)); # Start a new session to select the column to force it build # an earlier version of the clustered index through undo log. So it should # just see the result of repeat("b", 16000) -select @@session.tx_isolation; +select @@session.transaction_isolation; --connect (con1,localhost,root,,) select a = repeat("x", 17000) from worklog5743; select a = repeat("b", 16000) from worklog5743; @@ -49,7 +49,7 @@ select a = repeat("b", 16000) from worklog5743; # should see the uncommitted update --connect (con2,localhost,root,,) SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -select @@session.tx_isolation; +select @@session.transaction_isolation; select a = repeat("x", 17000) from worklog5743; # Roll back the transaction @@ -73,7 +73,7 @@ update worklog5743 set a1 = 1000; # Do a select from another connection that would use the secondary index --connection con1 -select @@session.tx_isolation; +select @@session.transaction_isolation; explain select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9; select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9; @@ -81,7 +81,7 @@ select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9; # row with a1 = 9 --connection con2 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -select @@session.tx_isolation; +select @@session.transaction_isolation; select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9; --connection default @@ -103,7 +103,7 @@ update worklog5743 set a1 = 1000; # Do a select from another connection that would use the secondary index --connection con1 -select @@session.tx_isolation; +select @@session.transaction_isolation; explain select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9; select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9; @@ -111,7 +111,7 @@ select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9; # row with a1 = 9 --connection con2 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -select @@session.tx_isolation; +select @@session.transaction_isolation; select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9; --connection default @@ -247,7 +247,7 @@ select a1, left(a2, 20) from worklog5743_16; # Do a select from another connection that would use the secondary index --connection con1 -select @@session.tx_isolation; +select @@session.transaction_isolation; explain select a1, left(a2, 20) from worklog5743_1 where a1 = 9; explain select a1, left(a2, 20) from worklog5743_2 where a1 = 9; explain select a1, left(a2, 20) from worklog5743_4 where a1 = 9; @@ -263,7 +263,7 @@ select a1, left(a2, 20) from worklog5743_16 where a1 = 9; # row with a1 = 9 --connection con2 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -select @@session.tx_isolation; +select @@session.transaction_isolation; select a1, left(a2, 20) from worklog5743_1 where a1 = 9; select a1, left(a2, 20) from worklog5743_2 where a1 = 9; select a1, left(a2, 20) from worklog5743_4 where a1 = 9; @@ -318,14 +318,14 @@ update worklog5743 set a1 = 1000; # Do a select from another connection that would use the secondary index --connection con1 -select @@session.tx_isolation; +select @@session.transaction_isolation; explain select a1 from worklog5743 where a1 = 9; select a1 from worklog5743 where a1 = 9; # Do read uncommitted, it would show there is no row with a1 = 9 --connection con2 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -select @@session.tx_isolation; +select @@session.transaction_isolation; select a1 from worklog5743 where a1 = 9; --connection default @@ -358,14 +358,14 @@ update worklog5743 set a = (repeat("x", 25000)); # Start a new session to select the table to force it build # an earlier version of the cluster index through undo log -select @@session.tx_isolation; +select @@session.transaction_isolation; --connection con1 select a = repeat("a", 20000) from worklog5743; --disconnect con1 --connection con2 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -select @@session.tx_isolation; +select @@session.transaction_isolation; select a = repeat("x", 25000) from worklog5743; --disconnect con2 diff --git a/mysql-test/suite/innodb_zip/t/index_large_prefix_4k.test b/mysql-test/suite/innodb_zip/t/index_large_prefix_4k.test index 8ada226779a..960ad1ebcf5 100644 --- a/mysql-test/suite/innodb_zip/t/index_large_prefix_4k.test +++ b/mysql-test/suite/innodb_zip/t/index_large_prefix_4k.test @@ -41,7 +41,7 @@ update worklog5743 set a = (repeat("x", 17000)); # Start a new session to select the column to force it build # an earlier version of the clustered index through undo log. So it should # just see the result of repeat("b", 16000) -select @@session.tx_isolation; +select @@session.transaction_isolation; --connect (con1,localhost,root,,) select a = repeat("x", 17000) from worklog5743; select a = repeat("b", 16000) from worklog5743; @@ -50,7 +50,7 @@ select a = repeat("b", 16000) from worklog5743; # should see the uncommitted update --connect (con2,localhost,root,,) SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -select @@session.tx_isolation; +select @@session.transaction_isolation; select a = repeat("x", 17000) from worklog5743; # Roll back the transaction @@ -74,7 +74,7 @@ update worklog5743 set a1 = 1111; # Do a select from another connection that would use the secondary index --connection con1 -select @@session.tx_isolation; +select @@session.transaction_isolation; explain select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9; select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9; @@ -82,7 +82,7 @@ select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9; # row with a1 = 9 --connection con2 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -select @@session.tx_isolation; +select @@session.transaction_isolation; select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9; --connection default @@ -104,7 +104,7 @@ update worklog5743 set a1 = 2222; # Do a select from another connection that would use the secondary index --connection con1 -select @@session.tx_isolation; +select @@session.transaction_isolation; explain select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9; select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9; @@ -112,7 +112,7 @@ select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9; # row with a1 = 9 --connection con2 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -select @@session.tx_isolation; +select @@session.transaction_isolation; select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9; --connection default @@ -195,7 +195,7 @@ select a1, left(a2, 20) from worklog5743_4; # Do a select from another connection that would use the secondary index --connection con1 -select @@session.tx_isolation; +select @@session.transaction_isolation; explain select a1, left(a2, 20) from worklog5743_1 where a1 = 9; explain select a1, left(a2, 20) from worklog5743_2 where a1 = 9; explain select a1, left(a2, 20) from worklog5743_4 where a1 = 9; @@ -207,7 +207,7 @@ select a1, left(a2, 20) from worklog5743_4 where a1 = 9; # row with a1 = 9 --connection con2 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -select @@session.tx_isolation; +select @@session.transaction_isolation; select a1, left(a2, 20) from worklog5743_1 where a1 = 9; select a1, left(a2, 20) from worklog5743_2 where a1 = 9; select a1, left(a2, 20) from worklog5743_4 where a1 = 9; @@ -293,14 +293,14 @@ update worklog5743 set a1 = 4444; # Do a select from another connection that would use the secondary index --connection con1 -select @@session.tx_isolation; +select @@session.transaction_isolation; explain select a1 from worklog5743 where a1 = 9; select a1 from worklog5743 where a1 = 9; # Do read uncommitted, it would show there is no row with a1 = 9 --connection con2 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -select @@session.tx_isolation; +select @@session.transaction_isolation; select a1 from worklog5743 where a1 = 9; --connection default @@ -327,14 +327,14 @@ update worklog5743 set a = (repeat("x", 25000)); # Start a new session to select the table to force it build # an earlier version of the cluster index through undo log -select @@session.tx_isolation; +select @@session.transaction_isolation; --connection con1 select a = repeat("a", 20000) from worklog5743; --disconnect con1 --connection con2 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -select @@session.tx_isolation; +select @@session.transaction_isolation; select a = repeat("x", 25000) from worklog5743; --disconnect con2 diff --git a/mysql-test/suite/innodb_zip/t/index_large_prefix_8k.test b/mysql-test/suite/innodb_zip/t/index_large_prefix_8k.test index e20de5e3e93..23fb37e8d73 100644 --- a/mysql-test/suite/innodb_zip/t/index_large_prefix_8k.test +++ b/mysql-test/suite/innodb_zip/t/index_large_prefix_8k.test @@ -42,7 +42,7 @@ update worklog5743 set a = (repeat("x", 17000)); # Start a new session to select the column to force it build # an earlier version of the clustered index through undo log. So it should # just see the result of repeat("b", 16000) -select @@session.tx_isolation; +select @@session.transaction_isolation; --connect (con1,localhost,root,,) select a = repeat("x", 17000) from worklog5743; select a = repeat("b", 16000) from worklog5743; @@ -51,7 +51,7 @@ select a = repeat("b", 16000) from worklog5743; # should see the uncommitted update --connect (con2,localhost,root,,) SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -select @@session.tx_isolation; +select @@session.transaction_isolation; select a = repeat("x", 17000) from worklog5743; # Roll back the transaction @@ -75,7 +75,7 @@ update worklog5743 set a1 = 1000; # Do a select from another connection that would use the secondary index --connection con1 -select @@session.tx_isolation; +select @@session.transaction_isolation; explain select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9; select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9; @@ -83,7 +83,7 @@ select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9; # row with a1 = 9 --connection con2 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -select @@session.tx_isolation; +select @@session.transaction_isolation; select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9; --connection default @@ -105,7 +105,7 @@ update worklog5743 set a1 = 1000; # Do a select from another connection that would use the secondary index --connection con1 -select @@session.tx_isolation; +select @@session.transaction_isolation; explain select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9; select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9; @@ -113,7 +113,7 @@ select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9; # row with a1 = 9 --connection con2 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -select @@session.tx_isolation; +select @@session.transaction_isolation; select a1, a2 = repeat("a", 10000) from worklog5743 where a1 = 9; --connection default @@ -217,7 +217,7 @@ select a1, left(a2, 20) from worklog5743_8; # Do a select from another connection that would use the secondary index --connection con1 -select @@session.tx_isolation; +select @@session.transaction_isolation; explain select a1, left(a2, 20) from worklog5743_1 where a1 = 9; explain select a1, left(a2, 20) from worklog5743_2 where a1 = 9; explain select a1, left(a2, 20) from worklog5743_4 where a1 = 9; @@ -231,7 +231,7 @@ select a1, left(a2, 20) from worklog5743_8 where a1 = 9; # row with a1 = 9 --connection con2 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -select @@session.tx_isolation; +select @@session.transaction_isolation; select a1, left(a2, 20) from worklog5743_1 where a1 = 9; select a1, left(a2, 20) from worklog5743_2 where a1 = 9; select a1, left(a2, 20) from worklog5743_4 where a1 = 9; @@ -314,14 +314,14 @@ update worklog5743 set a1 = 1000; # Do a select from another connection that would use the secondary index --connection con1 -select @@session.tx_isolation; +select @@session.transaction_isolation; explain select a1 from worklog5743 where a1 = 9; select a1 from worklog5743 where a1 = 9; # Do read uncommitted, it would show there is no row with a1 = 9 --connection con2 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -select @@session.tx_isolation; +select @@session.transaction_isolation; select a1 from worklog5743 where a1 = 9; --connection default @@ -353,14 +353,14 @@ update worklog5743 set a = (repeat("x", 25000)); # Start a new session to select the table to force it build # an earlier version of the cluster index through undo log -select @@session.tx_isolation; +select @@session.transaction_isolation; --connection con1 select a = repeat("a", 20000) from worklog5743; --disconnect con1 --connection con2 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -select @@session.tx_isolation; +select @@session.transaction_isolation; select a = repeat("x", 25000) from worklog5743; --disconnect con2 diff --git a/mysql-test/suite/innodb_zip/t/prefix_index_liftedlimit.test b/mysql-test/suite/innodb_zip/t/prefix_index_liftedlimit.test index 32d7eacd29f..de27ddce658 100644 --- a/mysql-test/suite/innodb_zip/t/prefix_index_liftedlimit.test +++ b/mysql-test/suite/innodb_zip/t/prefix_index_liftedlimit.test @@ -722,11 +722,11 @@ SELECT col_1_text = REPEAT("a", 200) , col_2_text = REPEAT("o", 200) FROM worklog5743; --connection con1 -select @@session.tx_isolation; +select @@session.transaction_isolation; SELECT col_1_text = REPEAT("b", 200) , col_2_text = REPEAT("o", 200) FROM worklog5743; SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -select @@session.tx_isolation; +select @@session.transaction_isolation; SELECT col_1_text = REPEAT("b", 200) , col_2_text = REPEAT("o", 200) FROM worklog5743; SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; @@ -789,7 +789,7 @@ WHERE info='COMMIT'; --connection con1 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -select @@session.tx_isolation; +select @@session.transaction_isolation; SELECT col_1_text = REPEAT("b", 200) , col_2_text = REPEAT("o", 200) FROM worklog5743; SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; diff --git a/mysql-test/suite/optimizer_unfixed_bugs/r/bug41029.result b/mysql-test/suite/optimizer_unfixed_bugs/r/bug41029.result index eaad67de20c..b04b7551af0 100644 --- a/mysql-test/suite/optimizer_unfixed_bugs/r/bug41029.result +++ b/mysql-test/suite/optimizer_unfixed_bugs/r/bug41029.result @@ -15,8 +15,8 @@ create table t1 (dummy int primary key, a int unique, b int) engine=innodb; insert into t1 values(1,1,1),(3,3,3),(5,5,5); commit; set session transaction isolation level repeatable read; -select @@tx_isolation; -@@tx_isolation +select @@transaction_isolation; +@@transaction_isolation REPEATABLE-READ start transaction; select * from t1 where a > 2 for update; diff --git a/mysql-test/suite/optimizer_unfixed_bugs/t/bug41029.test b/mysql-test/suite/optimizer_unfixed_bugs/t/bug41029.test index b7c0dc5332c..ba5eea05909 100644 --- a/mysql-test/suite/optimizer_unfixed_bugs/t/bug41029.test +++ b/mysql-test/suite/optimizer_unfixed_bugs/t/bug41029.test @@ -22,7 +22,7 @@ create table t1 (dummy int primary key, a int unique, b int) engine=innodb; insert into t1 values(1,1,1),(3,3,3),(5,5,5); commit; set session transaction isolation level repeatable read; -select @@tx_isolation; +select @@transaction_isolation; start transaction; select * from t1 where a > 2 for update; diff --git a/mysql-test/suite/perfschema/r/transaction.result b/mysql-test/suite/perfschema/r/transaction.result index 9a7eb5b3eb5..c516a4543c2 100644 --- a/mysql-test/suite/perfschema/r/transaction.result +++ b/mysql-test/suite/perfschema/r/transaction.result @@ -39,8 +39,8 @@ CALL clear_transaction_tables(); # ======================================================================== # connection con1; -SELECT @@global.tx_isolation; -@@global.tx_isolation +SELECT @@global.transaction_isolation; +@@global.transaction_isolation REPEATABLE-READ SELECT @@global.autocommit; @@global.autocommit @@ -48,8 +48,8 @@ SELECT @@global.autocommit; SELECT @@global.binlog_format; @@global.binlog_format ROW -SELECT @@tx_isolation; -@@tx_isolation +SELECT @@transaction_isolation; +@@transaction_isolation REPEATABLE-READ SELECT @@autocommit; @@autocommit diff --git a/mysql-test/suite/perfschema/t/transaction.test b/mysql-test/suite/perfschema/t/transaction.test index b616460374e..d950139f7d6 100644 --- a/mysql-test/suite/perfschema/t/transaction.test +++ b/mysql-test/suite/perfschema/t/transaction.test @@ -90,10 +90,10 @@ CALL clear_transaction_tables(); --echo # ======================================================================== --echo # --connection con1 -SELECT @@global.tx_isolation; +SELECT @@global.transaction_isolation; SELECT @@global.autocommit; SELECT @@global.binlog_format; -SELECT @@tx_isolation; +SELECT @@transaction_isolation; SELECT @@autocommit; SELECT @@binlog_format; diff --git a/mysql-test/suite/rpl/r/rpl_parallel_optimistic.result b/mysql-test/suite/rpl/r/rpl_parallel_optimistic.result index eff939512e2..e8d377d5d2d 100644 --- a/mysql-test/suite/rpl/r/rpl_parallel_optimistic.result +++ b/mysql-test/suite/rpl/r/rpl_parallel_optimistic.result @@ -270,7 +270,7 @@ connection server_2; include/stop_slave.inc SET @old_format= @@GLOBAL.binlog_format; SET GLOBAL binlog_format= MIXED; -SET @old_isolation= @@GLOBAL.tx_isolation; +SET @old_isolation= @@GLOBAL.transaction_isolation; SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED; SET GLOBAL slave_parallel_threads=0; SET GLOBAL slave_parallel_threads=10; @@ -346,7 +346,7 @@ a b 10 10 include/stop_slave.inc SET GLOBAL binlog_format= @old_format; -SET GLOBAL tx_isolation= @old_isolation; +SET GLOBAL transaction_isolation= @old_isolation; include/start_slave.inc *** MDEV-7888: ANALYZE TABLE does wakeup_subsequent_commits(), causing wrong binlog order and parallel replication hang *** connection server_1; diff --git a/mysql-test/suite/rpl/r/rpl_parallel_optimistic_nobinlog.result b/mysql-test/suite/rpl/r/rpl_parallel_optimistic_nobinlog.result index 4172abb17d9..28c5ceb1f6c 100644 --- a/mysql-test/suite/rpl/r/rpl_parallel_optimistic_nobinlog.result +++ b/mysql-test/suite/rpl/r/rpl_parallel_optimistic_nobinlog.result @@ -6,7 +6,7 @@ CREATE TABLE t2 (a int PRIMARY KEY, b INT) ENGINE=InnoDB; INSERT INTO t1 VALUES (1,0), (2,0), (3,0); INSERT INTO t2 VALUES (1,0), (2,0); connection server_2; -SET @old_isolation= @@GLOBAL.tx_isolation; +SET @old_isolation= @@GLOBAL.transaction_isolation; SET @old_parallel_threads=@@GLOBAL.slave_parallel_threads; include/stop_slave.inc SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED; @@ -82,7 +82,7 @@ a b 10 10 connection server_2; include/stop_slave.inc -SET GLOBAL tx_isolation= @old_isolation; +SET GLOBAL transaction_isolation= @old_isolation; SET GLOBAL slave_parallel_mode=@old_parallel_mode; SET GLOBAL slave_parallel_threads=@old_parallel_threads; include/start_slave.inc diff --git a/mysql-test/suite/rpl/r/rpl_xa.result b/mysql-test/suite/rpl/r/rpl_xa.result index 061c7b360d0..91e2a1f3ab5 100644 --- a/mysql-test/suite/rpl/r/rpl_xa.result +++ b/mysql-test/suite/rpl/r/rpl_xa.result @@ -225,7 +225,7 @@ drop table t1, t2, t3, tm; create table t1 (a int primary key, b int unique) engine=innodb; insert t1 values (1,1),(3,3),(5,5); connection slave; -set session tx_isolation='repeatable-read'; +set session transaction_isolation='repeatable-read'; start transaction; select * from t1; a b diff --git a/mysql-test/suite/rpl/r/rpl_xa_gtid_pos_auto_engine.result b/mysql-test/suite/rpl/r/rpl_xa_gtid_pos_auto_engine.result index 35625cc7026..66300e4d6d3 100644 --- a/mysql-test/suite/rpl/r/rpl_xa_gtid_pos_auto_engine.result +++ b/mysql-test/suite/rpl/r/rpl_xa_gtid_pos_auto_engine.result @@ -234,7 +234,7 @@ drop table t1, t2, t3, tm; create table t1 (a int primary key, b int unique) engine=innodb; insert t1 values (1,1),(3,3),(5,5); connection slave; -set session tx_isolation='repeatable-read'; +set session transaction_isolation='repeatable-read'; start transaction; select * from t1; a b diff --git a/mysql-test/suite/rpl/t/rpl_parallel_optimistic.test b/mysql-test/suite/rpl/t/rpl_parallel_optimistic.test index f5e48282326..81c787bba5b 100644 --- a/mysql-test/suite/rpl/t/rpl_parallel_optimistic.test +++ b/mysql-test/suite/rpl/t/rpl_parallel_optimistic.test @@ -272,7 +272,7 @@ eval SELECT IF($retry1=$retry2, "Ok, no retry", SET @old_format= @@GLOBAL.binlog_format; # Use MIXED format; we cannot binlog ROW events on slave in STATEMENT format. SET GLOBAL binlog_format= MIXED; -SET @old_isolation= @@GLOBAL.tx_isolation; +SET @old_isolation= @@GLOBAL.transaction_isolation; SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED; # Reset the worker threads to make the new settings take effect. SET GLOBAL slave_parallel_threads=0; @@ -317,7 +317,7 @@ SELECT * FROM t2 ORDER BY a; --source include/stop_slave.inc SET GLOBAL binlog_format= @old_format; -SET GLOBAL tx_isolation= @old_isolation; +SET GLOBAL transaction_isolation= @old_isolation; --source include/start_slave.inc diff --git a/mysql-test/suite/rpl/t/rpl_parallel_optimistic_nobinlog.test b/mysql-test/suite/rpl/t/rpl_parallel_optimistic_nobinlog.test index ee0de499edf..b8df2ffae46 100644 --- a/mysql-test/suite/rpl/t/rpl_parallel_optimistic_nobinlog.test +++ b/mysql-test/suite/rpl/t/rpl_parallel_optimistic_nobinlog.test @@ -14,7 +14,7 @@ INSERT INTO t2 VALUES (1,0), (2,0); --connection server_2 --sync_with_master -SET @old_isolation= @@GLOBAL.tx_isolation; +SET @old_isolation= @@GLOBAL.transaction_isolation; SET @old_parallel_threads=@@GLOBAL.slave_parallel_threads; --source include/stop_slave.inc SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED; @@ -62,7 +62,7 @@ SELECT * FROM t2 ORDER BY a; --connection server_2 --source include/stop_slave.inc -SET GLOBAL tx_isolation= @old_isolation; +SET GLOBAL transaction_isolation= @old_isolation; SET GLOBAL slave_parallel_mode=@old_parallel_mode; SET GLOBAL slave_parallel_threads=@old_parallel_threads; --source include/start_slave.inc diff --git a/mysql-test/suite/rpl/t/rpl_xa.inc b/mysql-test/suite/rpl/t/rpl_xa.inc index d22d2d2ef3d..30e7a4bbe4a 100644 --- a/mysql-test/suite/rpl/t/rpl_xa.inc +++ b/mysql-test/suite/rpl/t/rpl_xa.inc @@ -363,7 +363,7 @@ sync_slave_with_master; # set a strong isolation level to keep the read view below. # alternatively a long-running select can do that too even in read-committed -set session tx_isolation='repeatable-read'; +set session transaction_isolation='repeatable-read'; start transaction; # opens a read view to disable purge on the slave select * from t1; diff --git a/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result b/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result index b9b466c8264..02658d1b365 100644 --- a/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result +++ b/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result @@ -3892,6 +3892,16 @@ NUMERIC_BLOCK_SIZE 1024 ENUM_VALUE_LIST NULL READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED +VARIABLE_NAME TRANSACTION_ISOLATION +VARIABLE_SCOPE SESSION +VARIABLE_TYPE ENUM +VARIABLE_COMMENT Default transaction isolation level +NUMERIC_MIN_VALUE NULL +NUMERIC_MAX_VALUE NULL +NUMERIC_BLOCK_SIZE NULL +ENUM_VALUE_LIST READ-UNCOMMITTED,READ-COMMITTED,REPEATABLE-READ,SERIALIZABLE +READ_ONLY NO +COMMAND_LINE_ARGUMENT NULL VARIABLE_NAME TRANSACTION_PREALLOC_SIZE VARIABLE_SCOPE SESSION VARIABLE_TYPE BIGINT UNSIGNED @@ -3902,10 +3912,20 @@ NUMERIC_BLOCK_SIZE 1024 ENUM_VALUE_LIST NULL READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED +VARIABLE_NAME TRANSACTION_READ_ONLY +VARIABLE_SCOPE SESSION +VARIABLE_TYPE BOOLEAN +VARIABLE_COMMENT Default transaction access mode. If set to OFF, the default, access is read/write. If set to ON, access is read-only. The SET TRANSACTION statement can also change the value of this variable. See SET TRANSACTION and START TRANSACTION. +NUMERIC_MIN_VALUE NULL +NUMERIC_MAX_VALUE NULL +NUMERIC_BLOCK_SIZE NULL +ENUM_VALUE_LIST OFF,ON +READ_ONLY NO +COMMAND_LINE_ARGUMENT NULL VARIABLE_NAME TX_ISOLATION VARIABLE_SCOPE SESSION VARIABLE_TYPE ENUM -VARIABLE_COMMENT Default transaction isolation level +VARIABLE_COMMENT Default transaction isolation level.This variable is deprecated and will be removed in a future release. NUMERIC_MIN_VALUE NULL NUMERIC_MAX_VALUE NULL NUMERIC_BLOCK_SIZE NULL @@ -3915,7 +3935,7 @@ COMMAND_LINE_ARGUMENT NULL VARIABLE_NAME TX_READ_ONLY VARIABLE_SCOPE SESSION VARIABLE_TYPE BOOLEAN -VARIABLE_COMMENT Default transaction access mode. If set to OFF, the default, access is read/write. If set to ON, access is read-only. The SET TRANSACTION statement can also change the value of this variable. See SET TRANSACTION and START TRANSACTION. +VARIABLE_COMMENT Default transaction access mode. If set to OFF, the default, access is read/write. If set to ON, access is read-only. The SET TRANSACTION statement can also change the value of this variable. See SET TRANSACTION and START TRANSACTION.This variable is deprecated and will be removed in a future release. NUMERIC_MIN_VALUE NULL NUMERIC_MAX_VALUE NULL NUMERIC_BLOCK_SIZE NULL diff --git a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result index 914fdbbf5cb..d0deace1cce 100644 --- a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result +++ b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result @@ -4702,6 +4702,16 @@ NUMERIC_BLOCK_SIZE 1024 ENUM_VALUE_LIST NULL READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED +VARIABLE_NAME TRANSACTION_ISOLATION +VARIABLE_SCOPE SESSION +VARIABLE_TYPE ENUM +VARIABLE_COMMENT Default transaction isolation level +NUMERIC_MIN_VALUE NULL +NUMERIC_MAX_VALUE NULL +NUMERIC_BLOCK_SIZE NULL +ENUM_VALUE_LIST READ-UNCOMMITTED,READ-COMMITTED,REPEATABLE-READ,SERIALIZABLE +READ_ONLY NO +COMMAND_LINE_ARGUMENT NULL VARIABLE_NAME TRANSACTION_PREALLOC_SIZE VARIABLE_SCOPE SESSION VARIABLE_TYPE BIGINT UNSIGNED @@ -4712,10 +4722,20 @@ NUMERIC_BLOCK_SIZE 1024 ENUM_VALUE_LIST NULL READ_ONLY NO COMMAND_LINE_ARGUMENT REQUIRED +VARIABLE_NAME TRANSACTION_READ_ONLY +VARIABLE_SCOPE SESSION +VARIABLE_TYPE BOOLEAN +VARIABLE_COMMENT Default transaction access mode. If set to OFF, the default, access is read/write. If set to ON, access is read-only. The SET TRANSACTION statement can also change the value of this variable. See SET TRANSACTION and START TRANSACTION. +NUMERIC_MIN_VALUE NULL +NUMERIC_MAX_VALUE NULL +NUMERIC_BLOCK_SIZE NULL +ENUM_VALUE_LIST OFF,ON +READ_ONLY NO +COMMAND_LINE_ARGUMENT NULL VARIABLE_NAME TX_ISOLATION VARIABLE_SCOPE SESSION VARIABLE_TYPE ENUM -VARIABLE_COMMENT Default transaction isolation level +VARIABLE_COMMENT Default transaction isolation level.This variable is deprecated and will be removed in a future release. NUMERIC_MIN_VALUE NULL NUMERIC_MAX_VALUE NULL NUMERIC_BLOCK_SIZE NULL @@ -4725,7 +4745,7 @@ COMMAND_LINE_ARGUMENT NULL VARIABLE_NAME TX_READ_ONLY VARIABLE_SCOPE SESSION VARIABLE_TYPE BOOLEAN -VARIABLE_COMMENT Default transaction access mode. If set to OFF, the default, access is read/write. If set to ON, access is read-only. The SET TRANSACTION statement can also change the value of this variable. See SET TRANSACTION and START TRANSACTION. +VARIABLE_COMMENT Default transaction access mode. If set to OFF, the default, access is read/write. If set to ON, access is read-only. The SET TRANSACTION statement can also change the value of this variable. See SET TRANSACTION and START TRANSACTION.This variable is deprecated and will be removed in a future release. NUMERIC_MIN_VALUE NULL NUMERIC_MAX_VALUE NULL NUMERIC_BLOCK_SIZE NULL diff --git a/mysql-test/suite/sys_vars/r/transaction_isolation_basic.result b/mysql-test/suite/sys_vars/r/transaction_isolation_basic.result new file mode 100644 index 00000000000..ef5d60072a9 --- /dev/null +++ b/mysql-test/suite/sys_vars/r/transaction_isolation_basic.result @@ -0,0 +1,141 @@ +SET @global_start_value = @@global.transaction_isolation; +SELECT @global_start_value; +@global_start_value +REPEATABLE-READ +SET @session_start_value = @@session.transaction_isolation; +SELECT @session_start_value; +@session_start_value +REPEATABLE-READ +'#--------------------FN_DYNVARS_183_01------------------------#' +SET @@global.transaction_isolation = 'READ-UNCOMMITTED'; +SET @@global.transaction_isolation = DEFAULT; +SELECT @@global.transaction_isolation; +@@global.transaction_isolation +REPEATABLE-READ +SET @@session.transaction_isolation = DEFAULT; +SELECT @@session.transaction_isolation; +@@session.transaction_isolation +REPEATABLE-READ +'#---------------------FN_DYNVARS_183_02-------------------------#' +SET @@global.transaction_isolation = NULL; +ERROR 42000: Variable 'transaction_isolation' can't be set to the value of 'NULL' +SET @@global.transaction_isolation = ''; +ERROR 42000: Variable 'transaction_isolation' can't be set to the value of '' +SET @@session.transaction_isolation = NULL; +ERROR 42000: Variable 'transaction_isolation' can't be set to the value of 'NULL' +SET @@session.transaction_isolation = ''; +ERROR 42000: Variable 'transaction_isolation' can't be set to the value of '' +'#--------------------FN_DYNVARS_183_03------------------------#' +SET @@global.transaction_isolation = 'READ-UNCOMMITTED'; +SELECT @@global.transaction_isolation; +@@global.transaction_isolation +READ-UNCOMMITTED +SET @@global.transaction_isolation = 'read-COMMITTED'; +SELECT @@global.transaction_isolation; +@@global.transaction_isolation +READ-COMMITTED +SET @@global.transaction_isolation = 'REPEATABLE-READ'; +SELECT @@global.transaction_isolation; +@@global.transaction_isolation +REPEATABLE-READ +SET @@global.transaction_isolation = 'SERIALIZable'; +SELECT @@global.transaction_isolation; +@@global.transaction_isolation +SERIALIZABLE +SET @@session.transaction_isolation = 'READ-UNCOMMITTED'; +SELECT @@session.transaction_isolation; +@@session.transaction_isolation +READ-UNCOMMITTED +SET @@session.transaction_isolation = 'READ-COMMITTED'; +SELECT @@session.transaction_isolation; +@@session.transaction_isolation +READ-COMMITTED +SET @@session.transaction_isolation = 'REPEATABLE-READ'; +SELECT @@session.transaction_isolation; +@@session.transaction_isolation +REPEATABLE-READ +SET @@session.transaction_isolation = 'serializable'; +SELECT @@session.transaction_isolation; +@@session.transaction_isolation +SERIALIZABLE +'#--------------------FN_DYNVARS_183_04-------------------------#' +SET @@global.transaction_isolation = -1; +ERROR 42000: Variable 'transaction_isolation' can't be set to the value of '-1' +SET @@global.transaction_isolation = READUNCOMMITTED; +ERROR 42000: Variable 'transaction_isolation' can't be set to the value of 'READUNCOMMITTED' +SET @@global.transaction_isolation = 'REPEATABLE'; +ERROR 42000: Variable 'transaction_isolation' can't be set to the value of 'REPEATABLE' +SET @@global.transaction_isolation = OFF; +ERROR 42000: Variable 'transaction_isolation' can't be set to the value of 'OFF' +SET @@global.transaction_isolation = ON; +ERROR 42000: Variable 'transaction_isolation' can't be set to the value of 'ON' +SET @@global.transaction_isolation = 'NON-SERIALIZABLE'; +ERROR 42000: Variable 'transaction_isolation' can't be set to the value of 'NON-SERIALIZABLE' +SET @@transaction_isolation = -1; +ERROR 42000: Variable 'transaction_isolation' can't be set to the value of '-1' +SET @@transaction_isolation = READUNCOMMITTED; +ERROR 42000: Variable 'transaction_isolation' can't be set to the value of 'READUNCOMMITTED' +SET @@transaction_isolation = 'REPEATABLE'; +ERROR 42000: Variable 'transaction_isolation' can't be set to the value of 'REPEATABLE' +SET @@transaction_isolation = 'NONE'; +ERROR 42000: Variable 'transaction_isolation' can't be set to the value of 'NONE' +SET @@transaction_isolation = 'ALL'; +ERROR 42000: Variable 'transaction_isolation' can't be set to the value of 'ALL' +'#-------------------FN_DYNVARS_183_05----------------------------#' +SELECT @@session.transaction_isolation = VARIABLE_VALUE +FROM INFORMATION_SCHEMA.SESSION_VARIABLES +WHERE VARIABLE_NAME='transaction_isolation'; +@@session.transaction_isolation = VARIABLE_VALUE +1 +'#----------------------FN_DYNVARS_183_06------------------------#' +SELECT @@global.transaction_isolation = VARIABLE_VALUE +FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES +WHERE VARIABLE_NAME='transaction_isolation'; +@@global.transaction_isolation = VARIABLE_VALUE +1 +'#---------------------FN_DYNVARS_183_07-------------------------#' +SET @@global.transaction_isolation = 0; +SELECT @@global.transaction_isolation; +@@global.transaction_isolation +READ-UNCOMMITTED +SET @@global.transaction_isolation = 1; +SELECT @@global.transaction_isolation; +@@global.transaction_isolation +READ-COMMITTED +SET @@global.transaction_isolation = 2; +SELECT @@global.transaction_isolation; +@@global.transaction_isolation +REPEATABLE-READ +SET @@global.transaction_isolation = 3; +SELECT @@global.transaction_isolation; +@@global.transaction_isolation +SERIALIZABLE +SET @@global.transaction_isolation = 0.4; +ERROR 42000: Incorrect argument type to variable 'transaction_isolation' +'#---------------------FN_DYNVARS_183_08----------------------#' +SET @@global.transaction_isolation = TRUE; +SELECT @@global.transaction_isolation; +@@global.transaction_isolation +READ-COMMITTED +SET @@global.transaction_isolation = FALSE; +SELECT @@global.transaction_isolation; +@@global.transaction_isolation +READ-UNCOMMITTED +'#---------------------FN_DYNVARS_183_09----------------------#' +SET transaction_isolation = 'REPEATABLE-READ'; +SET session transaction_isolation = 'REPEATABLE-READ'; +SELECT @@transaction_isolation; +@@transaction_isolation +REPEATABLE-READ +SET global transaction_isolation = 'REPEATABLE-READ'; +SELECT @@global.transaction_isolation; +@@global.transaction_isolation +REPEATABLE-READ +SET @@global.transaction_isolation = @global_start_value; +SELECT @@global.transaction_isolation; +@@global.transaction_isolation +REPEATABLE-READ +SET @@session.transaction_isolation = @session_start_value; +SELECT @@session.transaction_isolation; +@@session.transaction_isolation +REPEATABLE-READ diff --git a/mysql-test/suite/sys_vars/r/tx_isolation_func.result b/mysql-test/suite/sys_vars/r/transaction_isolation_func.result index 3f6bd0b485b..b27050a0f50 100644 --- a/mysql-test/suite/sys_vars/r/tx_isolation_func.result +++ b/mysql-test/suite/sys_vars/r/transaction_isolation_func.result @@ -1,6 +1,6 @@ ** Setup ** -SET @session_tx_isolation = @@SESSION.tx_isolation; -SET @global_tx_isolation = @@GLOBAL.tx_isolation; +SET @session_transaction_isolation = @@SESSION.transaction_isolation; +SET @global_transaction_isolation = @@GLOBAL.transaction_isolation; connect con0,localhost,root,,; connection con0; SET SESSION AUTOCOMMIT = OFF; @@ -20,10 +20,10 @@ INSERT INTO t1 VALUES(22, 22); INSERT INTO t1 VALUES(24, 24); '#----------------------------FN_DYNVARS_184_01--------------------------------------#' connection con0; -SET SESSION tx_isolation = 'READ-UNCOMMITTED'; +SET SESSION transaction_isolation = 'READ-UNCOMMITTED'; set binlog_format=mixed; connection con1; -SET SESSION tx_isolation = 'READ-UNCOMMITTED'; +SET SESSION transaction_isolation = 'READ-UNCOMMITTED'; set binlog_format=mixed; connection con0; START TRANSACTION; @@ -114,9 +114,9 @@ connection con0; COMMIT; '#----------------------------FN_DYNVARS_184_03--------------------------------------#' connection con0; -SET SESSION tx_isolation = 'READ-COMMITTED'; +SET SESSION transaction_isolation = 'READ-COMMITTED'; connection con1; -SET SESSION tx_isolation = 'READ-COMMITTED'; +SET SESSION transaction_isolation = 'READ-COMMITTED'; connection con0; START TRANSACTION; SELECT * FROM t1 WHERE a % 2 = 0 FOR UPDATE; @@ -168,9 +168,9 @@ connection con0; COMMIT; '#----------------------------FN_DYNVARS_184_04--------------------------------------#' connection con0; -SET SESSION tx_isolation = 'REPEATABLE-READ'; +SET SESSION transaction_isolation = 'REPEATABLE-READ'; connection con1; -SET SESSION tx_isolation = 'REPEATABLE-READ'; +SET SESSION transaction_isolation = 'REPEATABLE-READ'; connection con0; START TRANSACTION; SELECT * FROM t1 WHERE a % 2 = 0 FOR UPDATE; @@ -332,9 +332,9 @@ connection con0; COMMIT; '#----------------------------FN_DYNVARS_184_07--------------------------------------#' connection con0; -SET SESSION tx_isolation = 'SERIALIZABLE'; +SET SESSION transaction_isolation = 'SERIALIZABLE'; connection con1; -SET SESSION tx_isolation = 'SERIALIZABLE'; +SET SESSION transaction_isolation = 'SERIALIZABLE'; connection con0; START TRANSACTION; SELECT * FROM t1 WHERE a % 2 = 0 FOR UPDATE; @@ -363,40 +363,40 @@ COMMIT; connection con0; COMMIT; '#----------------------------FN_DYNVARS_184_08--------------------------------------#' -SET GLOBAL tx_isolation = 'READ-UNCOMMITTED'; +SET GLOBAL transaction_isolation = 'READ-UNCOMMITTED'; connect con_int1,localhost,root,,; connection con_int1; -SELECT @@SESSION.tx_isolation; -@@SESSION.tx_isolation +SELECT @@SESSION.transaction_isolation; +@@SESSION.transaction_isolation READ-UNCOMMITTED READ-UNCOMMITTED Expected -SET SESSION tx_isolation = 'SERIALIZABLE'; +SET SESSION transaction_isolation = 'SERIALIZABLE'; connect con_int2,localhost,root,,; connection con_int2; -SELECT @@SESSION.tx_isolation; -@@SESSION.tx_isolation +SELECT @@SESSION.transaction_isolation; +@@SESSION.transaction_isolation READ-UNCOMMITTED READ-UNCOMMITTED Expected -SET SESSION tx_isolation = 'REPEATABLE-READ'; +SET SESSION transaction_isolation = 'REPEATABLE-READ'; connection con_int2; -SELECT @@SESSION.tx_isolation; -@@SESSION.tx_isolation +SELECT @@SESSION.transaction_isolation; +@@SESSION.transaction_isolation REPEATABLE-READ REPEATABLE-READ Expected connection con_int1; -SELECT @@SESSION.tx_isolation; -@@SESSION.tx_isolation +SELECT @@SESSION.transaction_isolation; +@@SESSION.transaction_isolation SERIALIZABLE SERIALIZABLE Expected -SELECT @@GLOBAL.tx_isolation; -@@GLOBAL.tx_isolation +SELECT @@GLOBAL.transaction_isolation; +@@GLOBAL.transaction_isolation READ-UNCOMMITTED READ-UNCOMMITTED Expected connection default; disconnect con_int1; disconnect con_int2; -SET @@SESSION.tx_isolation = @session_tx_isolation; -SET @@GLOBAL.tx_isolation = @global_tx_isolation; +SET @@SESSION.transaction_isolation = @session_transaction_isolation; +SET @@GLOBAL.transaction_isolation = @global_transaction_isolation; connection default; disconnect con0; disconnect con1; diff --git a/mysql-test/suite/sys_vars/r/transaction_read_only_basic.result b/mysql-test/suite/sys_vars/r/transaction_read_only_basic.result new file mode 100644 index 00000000000..b0d1d8bcab7 --- /dev/null +++ b/mysql-test/suite/sys_vars/r/transaction_read_only_basic.result @@ -0,0 +1,181 @@ +#################################################################### +# START OF transaction_read_only TESTS # +#################################################################### +############################################################# +# Save initial value # +############################################################# +SET @start_global_value = @@global.transaction_read_only; +SELECT @start_global_value; +@start_global_value +0 +SET @start_session_value = @@session.transaction_read_only; +SELECT @start_session_value; +@start_session_value +0 +######################################################################## +# Display the DEFAULT value of transaction_read_only # +######################################################################## +SET @@global.transaction_read_only = ON; +SET @@global.transaction_read_only = DEFAULT; +SELECT @@global.transaction_read_only; +@@global.transaction_read_only +0 +SET @@session.transaction_read_only = ON; +SET @@session.transaction_read_only = DEFAULT; +SELECT @@session.transaction_read_only; +@@session.transaction_read_only +0 +############################################################################### +# Change the value of transaction_read_only to a valid value for GLOBAL Scope # +############################################################################### +SET @@global.transaction_read_only = ON; +SELECT @@global.transaction_read_only; +@@global.transaction_read_only +1 +SET @@global.transaction_read_only = OFF; +SELECT @@global.transaction_read_only; +@@global.transaction_read_only +0 +SET @@global.transaction_read_only = 0; +SELECT @@global.transaction_read_only; +@@global.transaction_read_only +0 +SET @@global.transaction_read_only = 1; +SELECT @@global.transaction_read_only; +@@global.transaction_read_only +1 +SET @@global.transaction_read_only = TRUE; +SELECT @@global.transaction_read_only; +@@global.transaction_read_only +1 +SET @@global.transaction_read_only = FALSE; +SELECT @@global.transaction_read_only; +@@global.transaction_read_only +0 +################################################################################ +# Change the value of transaction_read_only to a valid value for SESSION Scope # +################################################################################ +SET @@session.transaction_read_only = ON; +SELECT @@session.transaction_read_only; +@@session.transaction_read_only +1 +SET @@session.transaction_read_only = OFF; +SELECT @@session.transaction_read_only; +@@session.transaction_read_only +0 +SET @@session.transaction_read_only = 0; +SELECT @@session.transaction_read_only; +@@session.transaction_read_only +0 +SET @@session.transaction_read_only = 1; +SELECT @@session.transaction_read_only; +@@session.transaction_read_only +1 +SET @@session.transaction_read_only = TRUE; +SELECT @@session.transaction_read_only; +@@session.transaction_read_only +1 +SET @@session.transaction_read_only = FALSE; +SELECT @@session.transaction_read_only; +@@session.transaction_read_only +0 +################################################################# +# Change the value of transaction_read_only to an invalid value # +################################################################# +SET @@global.transaction_read_only = 'ONN'; +ERROR 42000: Variable 'transaction_read_only' can't be set to the value of 'ONN' +SET @@global.transaction_read_only = "OFFF"; +ERROR 42000: Variable 'transaction_read_only' can't be set to the value of 'OFFF' +SET @@global.transaction_read_only = TTRUE; +ERROR 42000: Variable 'transaction_read_only' can't be set to the value of 'TTRUE' +SET @@global.transaction_read_only = FELSE; +ERROR 42000: Variable 'transaction_read_only' can't be set to the value of 'FELSE' +SET @@global.transaction_read_only = -1024; +ERROR 42000: Variable 'transaction_read_only' can't be set to the value of '-1024' +SET @@global.transaction_read_only = 65536; +ERROR 42000: Variable 'transaction_read_only' can't be set to the value of '65536' +SET @@global.transaction_read_only = 65530.34; +ERROR 42000: Incorrect argument type to variable 'transaction_read_only' +SET @@global.transaction_read_only = test; +ERROR 42000: Variable 'transaction_read_only' can't be set to the value of 'test' +SET @@session.transaction_read_only = ONN; +ERROR 42000: Variable 'transaction_read_only' can't be set to the value of 'ONN' +SET @@session.transaction_read_only = ONF; +ERROR 42000: Variable 'transaction_read_only' can't be set to the value of 'ONF' +SET @@session.transaction_read_only = OF; +ERROR 42000: Variable 'transaction_read_only' can't be set to the value of 'OF' +SET @@session.transaction_read_only = 'OFN'; +ERROR 42000: Variable 'transaction_read_only' can't be set to the value of 'OFN' +SET @@session.transaction_read_only = -2; +ERROR 42000: Variable 'transaction_read_only' can't be set to the value of '-2' +SET @@session.transaction_read_only = 65530.34; +ERROR 42000: Incorrect argument type to variable 'transaction_read_only' +SET @@session.transaction_read_only = 65550; +ERROR 42000: Variable 'transaction_read_only' can't be set to the value of '65550' +SET @@session.transaction_read_only = test; +ERROR 42000: Variable 'transaction_read_only' can't be set to the value of 'test' +SELECT @@session.transaction_read_only; +@@session.transaction_read_only +0 +#################################################################### +# Check if the value in GLOBAL Table matches value in variable # +#################################################################### +SELECT IF(@@global.transaction_read_only, "ON", "OFF") = VARIABLE_VALUE +FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES +WHERE VARIABLE_NAME='transaction_read_only'; +IF(@@global.transaction_read_only, "ON", "OFF") = VARIABLE_VALUE +1 +#################################################################### +# Check if the value in SESSION Table matches value in variable # +#################################################################### +SELECT IF(@@session.transaction_read_only, "ON", "OFF") = VARIABLE_VALUE +FROM INFORMATION_SCHEMA.SESSION_VARIABLES +WHERE VARIABLE_NAME='transaction_read_only'; +IF(@@session.transaction_read_only, "ON", "OFF") = VARIABLE_VALUE +1 +############################################################################### +# Check if accessing variable with and without GLOBAL point to same variable # +############################################################################### +SET @@transaction_read_only = OFF; +SET @@global.transaction_read_only = ON; +SELECT @@transaction_read_only = @@global.transaction_read_only; +@@transaction_read_only = @@global.transaction_read_only +0 +############################################################################## +# Check if accessing variable with SESSION,LOCAL and without SCOPE points # +# to same session variable # +############################################################################## +SET @@transaction_read_only = ON; +SELECT @@transaction_read_only = @@local.transaction_read_only; +@@transaction_read_only = @@local.transaction_read_only +1 +SELECT @@local.transaction_read_only = @@session.transaction_read_only; +@@local.transaction_read_only = @@session.transaction_read_only +1 +############################################################################### +# Check if transaction_read_only can be accessed with and without @@ sign # +############################################################################### +# @@session is synonym for SESSION +SET @@session.transaction_read_only= 0; +# Without modifier, SET changes session variable +SET transaction_read_only = 1; +SELECT @@transaction_read_only; +@@transaction_read_only +1 +# name1.name2 refers to database_name.table_name +SELECT session.transaction_read_only; +ERROR 42S02: Unknown table 'session' in field list +#################################### +# Restore initial value # +#################################### +SET @@global.transaction_read_only = @start_global_value; +SELECT @@global.transaction_read_only; +@@global.transaction_read_only +0 +SET @@session.transaction_read_only = @start_session_value; +SELECT @@session.transaction_read_only; +@@session.transaction_read_only +0 +######################################################### +# END OF transaction_read_only TESTS # +######################################################### diff --git a/mysql-test/suite/sys_vars/r/tx_compatibility.result b/mysql-test/suite/sys_vars/r/tx_compatibility.result new file mode 100644 index 00000000000..4cf1cd61a09 --- /dev/null +++ b/mysql-test/suite/sys_vars/r/tx_compatibility.result @@ -0,0 +1,139 @@ +# +# MDEV-21921 Make transaction_isolation and transaction_read_only into +# system variables +# +SET @saved_global_isolation= @@global.transaction_isolation; +SET @saved_global_read_only= @@global.transaction_read_only; +# Case 1: Check the influence of --transaction_* on +# @@session.transaction_* and @@global.transaction_*, +# @@session.tx_*, @@global.tx_*. +SELECT @@session.transaction_isolation, @@global.transaction_isolation, +@@session.tx_isolation, @@global.tx_isolation; +@@session.transaction_isolation @@global.transaction_isolation @@session.tx_isolation @@global.tx_isolation +SERIALIZABLE SERIALIZABLE SERIALIZABLE SERIALIZABLE +SHOW GLOBAL VARIABLES LIKE '%_isolation'; +Variable_name Value +transaction_isolation SERIALIZABLE +tx_isolation SERIALIZABLE +SHOW SESSION VARIABLES LIKE '%_isolation'; +Variable_name Value +transaction_isolation SERIALIZABLE +tx_isolation SERIALIZABLE +SELECT @@session.transaction_read_only, @@global.transaction_read_only, +@@session.tx_read_only, @@global.tx_read_only; +@@session.transaction_read_only @@global.transaction_read_only @@session.tx_read_only @@global.tx_read_only +1 1 1 1 +SHOW GLOBAL VARIABLES LIKE '%_read_only'; +Variable_name Value +transaction_read_only ON +tx_read_only ON +SHOW SESSION VARIABLES LIKE '%_read_only'; +Variable_name Value +transaction_read_only ON +tx_read_only ON +# Case 2: Check that the change to tx_* is reflected to transaction_*. +SET tx_isolation= 'REPEATABLE-READ'; +Warnings: +Warning 1287 '@@tx_isolation' is deprecated and will be removed in a future release. Please use '@@transaction_isolation' instead +SET @@global.tx_isolation= 'SERIALIZABLE'; +Warnings: +Warning 1287 '@@tx_isolation' is deprecated and will be removed in a future release. Please use '@@transaction_isolation' instead +SELECT @@session.tx_isolation, @@global.tx_isolation, +@@session.transaction_isolation, @@global.transaction_isolation; +@@session.tx_isolation @@global.tx_isolation @@session.transaction_isolation @@global.transaction_isolation +REPEATABLE-READ SERIALIZABLE REPEATABLE-READ SERIALIZABLE +SHOW GLOBAL VARIABLES LIKE '%_isolation'; +Variable_name Value +transaction_isolation SERIALIZABLE +tx_isolation SERIALIZABLE +SHOW SESSION VARIABLES LIKE '%_isolation'; +Variable_name Value +transaction_isolation REPEATABLE-READ +tx_isolation REPEATABLE-READ +SET STATEMENT tx_isolation= 'SERIALIZABLE' FOR SHOW SESSION VARIABLES LIKE '%_isolation'; +Variable_name Value +transaction_isolation SERIALIZABLE +tx_isolation SERIALIZABLE +Warnings: +Warning 1287 '@@tx_isolation' is deprecated and will be removed in a future release. Please use '@@transaction_isolation' instead +Warning 1287 '@@tx_isolation' is deprecated and will be removed in a future release. Please use '@@transaction_isolation' instead +SHOW SESSION VARIABLES LIKE '%_isolation'; +Variable_name Value +transaction_isolation REPEATABLE-READ +tx_isolation REPEATABLE-READ +SET tx_read_only= OFF; +Warnings: +Warning 1287 '@@tx_read_only' is deprecated and will be removed in a future release. Please use '@@transaction_read_only' instead +SET @@global.tx_read_only= ON; +Warnings: +Warning 1287 '@@tx_read_only' is deprecated and will be removed in a future release. Please use '@@transaction_read_only' instead +SELECT @@session.tx_read_only, @@global.tx_read_only, +@@session.transaction_read_only, @@global.transaction_read_only; +@@session.tx_read_only @@global.tx_read_only @@session.transaction_read_only @@global.transaction_read_only +0 1 0 1 +SHOW GLOBAL VARIABLES LIKE '%_read_only'; +Variable_name Value +transaction_read_only ON +tx_read_only ON +SHOW SESSION VARIABLES LIKE '%_read_only'; +Variable_name Value +transaction_read_only OFF +tx_read_only OFF +SET STATEMENT tx_read_only= ON FOR SHOW SESSION VARIABLES LIKE '%_read_only'; +Variable_name Value +transaction_read_only ON +tx_read_only ON +Warnings: +Warning 1287 '@@tx_read_only' is deprecated and will be removed in a future release. Please use '@@transaction_read_only' instead +Warning 1287 '@@tx_read_only' is deprecated and will be removed in a future release. Please use '@@transaction_read_only' instead +SHOW SESSION VARIABLES LIKE '%_read_only'; +Variable_name Value +transaction_read_only OFF +tx_read_only OFF +# Case 3: Check that the change to transaction_* is reflected to tx_*. +SET transaction_isolation= 'SERIALIZABLE'; +SET @@global.transaction_isolation= 'REPEATABLE-READ'; +SELECT @@session.tx_isolation, @@global.tx_isolation, +@@session.transaction_isolation, @@global.transaction_isolation; +@@session.tx_isolation @@global.tx_isolation @@session.transaction_isolation @@global.transaction_isolation +SERIALIZABLE REPEATABLE-READ SERIALIZABLE REPEATABLE-READ +SHOW GLOBAL VARIABLES LIKE '%_isolation'; +Variable_name Value +transaction_isolation REPEATABLE-READ +tx_isolation REPEATABLE-READ +SHOW SESSION VARIABLES LIKE '%_isolation'; +Variable_name Value +transaction_isolation SERIALIZABLE +tx_isolation SERIALIZABLE +SET STATEMENT transaction_isolation= 'REPEATABLE-READ' FOR SHOW SESSION VARIABLES LIKE '%_isolation'; +Variable_name Value +transaction_isolation REPEATABLE-READ +tx_isolation REPEATABLE-READ +SHOW SESSION VARIABLES LIKE '%_isolation'; +Variable_name Value +transaction_isolation SERIALIZABLE +tx_isolation SERIALIZABLE +SET transaction_read_only= ON; +SET @@global.transaction_read_only= OFF; +SELECT @@session.tx_read_only, @@global.tx_read_only, +@@session.transaction_read_only, @@global.transaction_read_only; +@@session.tx_read_only @@global.tx_read_only @@session.transaction_read_only @@global.transaction_read_only +1 0 1 0 +SHOW GLOBAL VARIABLES LIKE '%_read_only'; +Variable_name Value +transaction_read_only OFF +tx_read_only OFF +SHOW SESSION VARIABLES LIKE '%_read_only'; +Variable_name Value +transaction_read_only ON +tx_read_only ON +SET STATEMENT transaction_read_only= OFF FOR SHOW SESSION VARIABLES LIKE '%_read_only'; +Variable_name Value +transaction_read_only OFF +tx_read_only OFF +SHOW SESSION VARIABLES LIKE '%_read_only'; +Variable_name Value +transaction_read_only ON +tx_read_only ON +SET @@global.transaction_isolation= @saved_global_isolation; +SET @@global.transaction_read_only= @saved_global_read_only; diff --git a/mysql-test/suite/sys_vars/r/tx_isolation_basic.result b/mysql-test/suite/sys_vars/r/tx_isolation_basic.result deleted file mode 100644 index f6ee2c92fa5..00000000000 --- a/mysql-test/suite/sys_vars/r/tx_isolation_basic.result +++ /dev/null @@ -1,141 +0,0 @@ -SET @global_start_value = @@global.tx_isolation; -SELECT @global_start_value; -@global_start_value -REPEATABLE-READ -SET @session_start_value = @@session.tx_isolation; -SELECT @session_start_value; -@session_start_value -REPEATABLE-READ -'#--------------------FN_DYNVARS_183_01------------------------#' -SET @@global.tx_isolation = 'READ-UNCOMMITTED'; -SET @@global.tx_isolation = DEFAULT; -SELECT @@global.tx_isolation; -@@global.tx_isolation -REPEATABLE-READ -SET @@session.tx_isolation = DEFAULT; -SELECT @@session.tx_isolation; -@@session.tx_isolation -REPEATABLE-READ -'#---------------------FN_DYNVARS_183_02-------------------------#' -SET @@global.tx_isolation = NULL; -ERROR 42000: Variable 'tx_isolation' can't be set to the value of 'NULL' -SET @@global.tx_isolation = ''; -ERROR 42000: Variable 'tx_isolation' can't be set to the value of '' -SET @@session.tx_isolation = NULL; -ERROR 42000: Variable 'tx_isolation' can't be set to the value of 'NULL' -SET @@session.tx_isolation = ''; -ERROR 42000: Variable 'tx_isolation' can't be set to the value of '' -'#--------------------FN_DYNVARS_183_03------------------------#' -SET @@global.tx_isolation = 'READ-UNCOMMITTED'; -SELECT @@global.tx_isolation; -@@global.tx_isolation -READ-UNCOMMITTED -SET @@global.tx_isolation = 'read-COMMITTED'; -SELECT @@global.tx_isolation; -@@global.tx_isolation -READ-COMMITTED -SET @@global.tx_isolation = 'REPEATABLE-READ'; -SELECT @@global.tx_isolation; -@@global.tx_isolation -REPEATABLE-READ -SET @@global.tx_isolation = 'SERIALIZable'; -SELECT @@global.tx_isolation; -@@global.tx_isolation -SERIALIZABLE -SET @@session.tx_isolation = 'READ-UNCOMMITTED'; -SELECT @@session.tx_isolation; -@@session.tx_isolation -READ-UNCOMMITTED -SET @@session.tx_isolation = 'READ-COMMITTED'; -SELECT @@session.tx_isolation; -@@session.tx_isolation -READ-COMMITTED -SET @@session.tx_isolation = 'REPEATABLE-READ'; -SELECT @@session.tx_isolation; -@@session.tx_isolation -REPEATABLE-READ -SET @@session.tx_isolation = 'serializable'; -SELECT @@session.tx_isolation; -@@session.tx_isolation -SERIALIZABLE -'#--------------------FN_DYNVARS_183_04-------------------------#' -SET @@global.tx_isolation = -1; -ERROR 42000: Variable 'tx_isolation' can't be set to the value of '-1' -SET @@global.tx_isolation = READUNCOMMITTED; -ERROR 42000: Variable 'tx_isolation' can't be set to the value of 'READUNCOMMITTED' -SET @@global.tx_isolation = 'REPEATABLE'; -ERROR 42000: Variable 'tx_isolation' can't be set to the value of 'REPEATABLE' -SET @@global.tx_isolation = OFF; -ERROR 42000: Variable 'tx_isolation' can't be set to the value of 'OFF' -SET @@global.tx_isolation = ON; -ERROR 42000: Variable 'tx_isolation' can't be set to the value of 'ON' -SET @@global.tx_isolation = 'NON-SERIALIZABLE'; -ERROR 42000: Variable 'tx_isolation' can't be set to the value of 'NON-SERIALIZABLE' -SET @@tx_isolation = -1; -ERROR 42000: Variable 'tx_isolation' can't be set to the value of '-1' -SET @@tx_isolation = READUNCOMMITTED; -ERROR 42000: Variable 'tx_isolation' can't be set to the value of 'READUNCOMMITTED' -SET @@tx_isolation = 'REPEATABLE'; -ERROR 42000: Variable 'tx_isolation' can't be set to the value of 'REPEATABLE' -SET @@tx_isolation = 'NONE'; -ERROR 42000: Variable 'tx_isolation' can't be set to the value of 'NONE' -SET @@tx_isolation = 'ALL'; -ERROR 42000: Variable 'tx_isolation' can't be set to the value of 'ALL' -'#-------------------FN_DYNVARS_183_05----------------------------#' -SELECT @@session.tx_isolation = VARIABLE_VALUE -FROM INFORMATION_SCHEMA.SESSION_VARIABLES -WHERE VARIABLE_NAME='tx_isolation'; -@@session.tx_isolation = VARIABLE_VALUE -1 -'#----------------------FN_DYNVARS_183_06------------------------#' -SELECT @@global.tx_isolation = VARIABLE_VALUE -FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES -WHERE VARIABLE_NAME='tx_isolation'; -@@global.tx_isolation = VARIABLE_VALUE -1 -'#---------------------FN_DYNVARS_183_07-------------------------#' -SET @@global.tx_isolation = 0; -SELECT @@global.tx_isolation; -@@global.tx_isolation -READ-UNCOMMITTED -SET @@global.tx_isolation = 1; -SELECT @@global.tx_isolation; -@@global.tx_isolation -READ-COMMITTED -SET @@global.tx_isolation = 2; -SELECT @@global.tx_isolation; -@@global.tx_isolation -REPEATABLE-READ -SET @@global.tx_isolation = 3; -SELECT @@global.tx_isolation; -@@global.tx_isolation -SERIALIZABLE -SET @@global.tx_isolation = 0.4; -ERROR 42000: Incorrect argument type to variable 'tx_isolation' -'#---------------------FN_DYNVARS_183_08----------------------#' -SET @@global.tx_isolation = TRUE; -SELECT @@global.tx_isolation; -@@global.tx_isolation -READ-COMMITTED -SET @@global.tx_isolation = FALSE; -SELECT @@global.tx_isolation; -@@global.tx_isolation -READ-UNCOMMITTED -'#---------------------FN_DYNVARS_183_09----------------------#' -SET tx_isolation = 'REPEATABLE-READ'; -SET session tx_isolation = 'REPEATABLE-READ'; -SELECT @@tx_isolation; -@@tx_isolation -REPEATABLE-READ -SET global tx_isolation = 'REPEATABLE-READ'; -SELECT @@global.tx_isolation; -@@global.tx_isolation -REPEATABLE-READ -SET @@global.tx_isolation = @global_start_value; -SELECT @@global.tx_isolation; -@@global.tx_isolation -REPEATABLE-READ -SET @@session.tx_isolation = @session_start_value; -SELECT @@session.tx_isolation; -@@session.tx_isolation -REPEATABLE-READ diff --git a/mysql-test/suite/sys_vars/r/tx_read_only_basic.result b/mysql-test/suite/sys_vars/r/tx_read_only_basic.result deleted file mode 100644 index 3750145083f..00000000000 --- a/mysql-test/suite/sys_vars/r/tx_read_only_basic.result +++ /dev/null @@ -1,181 +0,0 @@ -#################################################################### -# START OF tx_read_only TESTS # -#################################################################### -############################################################# -# Save initial value # -############################################################# -SET @start_global_value = @@global.tx_read_only; -SELECT @start_global_value; -@start_global_value -0 -SET @start_session_value = @@session.tx_read_only; -SELECT @start_session_value; -@start_session_value -0 -######################################################################## -# Display the DEFAULT value of tx_read_only # -######################################################################## -SET @@global.tx_read_only = ON; -SET @@global.tx_read_only = DEFAULT; -SELECT @@global.tx_read_only; -@@global.tx_read_only -0 -SET @@session.tx_read_only = ON; -SET @@session.tx_read_only = DEFAULT; -SELECT @@session.tx_read_only; -@@session.tx_read_only -0 -############################################################################## -# Change the value of tx_read_only to a valid value for GLOBAL Scope # -############################################################################## -SET @@global.tx_read_only = ON; -SELECT @@global.tx_read_only; -@@global.tx_read_only -1 -SET @@global.tx_read_only = OFF; -SELECT @@global.tx_read_only; -@@global.tx_read_only -0 -SET @@global.tx_read_only = 0; -SELECT @@global.tx_read_only; -@@global.tx_read_only -0 -SET @@global.tx_read_only = 1; -SELECT @@global.tx_read_only; -@@global.tx_read_only -1 -SET @@global.tx_read_only = TRUE; -SELECT @@global.tx_read_only; -@@global.tx_read_only -1 -SET @@global.tx_read_only = FALSE; -SELECT @@global.tx_read_only; -@@global.tx_read_only -0 -############################################################################### -# Change the value of tx_read_only to a valid value for SESSION Scope # -############################################################################### -SET @@session.tx_read_only = ON; -SELECT @@session.tx_read_only; -@@session.tx_read_only -1 -SET @@session.tx_read_only = OFF; -SELECT @@session.tx_read_only; -@@session.tx_read_only -0 -SET @@session.tx_read_only = 0; -SELECT @@session.tx_read_only; -@@session.tx_read_only -0 -SET @@session.tx_read_only = 1; -SELECT @@session.tx_read_only; -@@session.tx_read_only -1 -SET @@session.tx_read_only = TRUE; -SELECT @@session.tx_read_only; -@@session.tx_read_only -1 -SET @@session.tx_read_only = FALSE; -SELECT @@session.tx_read_only; -@@session.tx_read_only -0 -################################################################ -# Change the value of tx_read_only to an invalid value # -################################################################ -SET @@global.tx_read_only = 'ONN'; -ERROR 42000: Variable 'tx_read_only' can't be set to the value of 'ONN' -SET @@global.tx_read_only = "OFFF"; -ERROR 42000: Variable 'tx_read_only' can't be set to the value of 'OFFF' -SET @@global.tx_read_only = TTRUE; -ERROR 42000: Variable 'tx_read_only' can't be set to the value of 'TTRUE' -SET @@global.tx_read_only = FELSE; -ERROR 42000: Variable 'tx_read_only' can't be set to the value of 'FELSE' -SET @@global.tx_read_only = -1024; -ERROR 42000: Variable 'tx_read_only' can't be set to the value of '-1024' -SET @@global.tx_read_only = 65536; -ERROR 42000: Variable 'tx_read_only' can't be set to the value of '65536' -SET @@global.tx_read_only = 65530.34; -ERROR 42000: Incorrect argument type to variable 'tx_read_only' -SET @@global.tx_read_only = test; -ERROR 42000: Variable 'tx_read_only' can't be set to the value of 'test' -SET @@session.tx_read_only = ONN; -ERROR 42000: Variable 'tx_read_only' can't be set to the value of 'ONN' -SET @@session.tx_read_only = ONF; -ERROR 42000: Variable 'tx_read_only' can't be set to the value of 'ONF' -SET @@session.tx_read_only = OF; -ERROR 42000: Variable 'tx_read_only' can't be set to the value of 'OF' -SET @@session.tx_read_only = 'OFN'; -ERROR 42000: Variable 'tx_read_only' can't be set to the value of 'OFN' -SET @@session.tx_read_only = -2; -ERROR 42000: Variable 'tx_read_only' can't be set to the value of '-2' -SET @@session.tx_read_only = 65530.34; -ERROR 42000: Incorrect argument type to variable 'tx_read_only' -SET @@session.tx_read_only = 65550; -ERROR 42000: Variable 'tx_read_only' can't be set to the value of '65550' -SET @@session.tx_read_only = test; -ERROR 42000: Variable 'tx_read_only' can't be set to the value of 'test' -SELECT @@session.tx_read_only; -@@session.tx_read_only -0 -#################################################################### -# Check if the value in GLOBAL Table matches value in variable # -#################################################################### -SELECT IF(@@global.tx_read_only, "ON", "OFF") = VARIABLE_VALUE -FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES -WHERE VARIABLE_NAME='tx_read_only'; -IF(@@global.tx_read_only, "ON", "OFF") = VARIABLE_VALUE -1 -#################################################################### -# Check if the value in SESSION Table matches value in variable # -#################################################################### -SELECT IF(@@session.tx_read_only, "ON", "OFF") = VARIABLE_VALUE -FROM INFORMATION_SCHEMA.SESSION_VARIABLES -WHERE VARIABLE_NAME='tx_read_only'; -IF(@@session.tx_read_only, "ON", "OFF") = VARIABLE_VALUE -1 -############################################################################### -# Check if accessing variable with and without GLOBAL point to same variable # -############################################################################### -SET @@tx_read_only = OFF; -SET @@global.tx_read_only = ON; -SELECT @@tx_read_only = @@global.tx_read_only; -@@tx_read_only = @@global.tx_read_only -0 -############################################################################## -# Check if accessing variable with SESSION,LOCAL and without SCOPE points # -# to same session variable # -############################################################################## -SET @@tx_read_only = ON; -SELECT @@tx_read_only = @@local.tx_read_only; -@@tx_read_only = @@local.tx_read_only -1 -SELECT @@local.tx_read_only = @@session.tx_read_only; -@@local.tx_read_only = @@session.tx_read_only -1 -############################################################################### -# Check if tx_read_only can be accessed with and without @@ sign # -############################################################################### -# @@session is synonym for SESSION -SET @@session.tx_read_only= 0; -# Without modifier, SET changes session variable -SET tx_read_only = 1; -SELECT @@tx_read_only; -@@tx_read_only -1 -# name1.name2 refers to database_name.table_name -SELECT session.tx_read_only; -ERROR 42S02: Unknown table 'session' in field list -#################################### -# Restore initial value # -#################################### -SET @@global.tx_read_only = @start_global_value; -SELECT @@global.tx_read_only; -@@global.tx_read_only -0 -SET @@session.tx_read_only = @start_session_value; -SELECT @@session.tx_read_only; -@@session.tx_read_only -0 -######################################################### -# END OF tx_read_only TESTS # -######################################################### diff --git a/mysql-test/suite/sys_vars/t/tx_isolation_basic.test b/mysql-test/suite/sys_vars/t/transaction_isolation_basic.test index 5b484cb9b1b..3f0e836141e 100644 --- a/mysql-test/suite/sys_vars/t/tx_isolation_basic.test +++ b/mysql-test/suite/sys_vars/t/transaction_isolation_basic.test @@ -1,6 +1,6 @@ -################# mysql-test\t\tx_isolation_basic.test ######################## +######## mysql-test\suite\sys_vars\t\transaction_isolation_basic.test ######### # # -# Variable Name: tx_isolation # +# Variable Name: transaction_isolation # # Scope: GLOBAL | SESSION # # Access Type: Dynamic # # Data Type: enumeration # @@ -11,7 +11,7 @@ # Creation Date: 2008-02-07 # # Author: Rizwan # # # -# Description: Test Cases of Dynamic System Variable tx_isolation # +# Description: Test Cases of Dynamic System Variable transaction_isolation # # that checks the behavior of this variable in the following ways# # * Default Value # # * Valid & Invalid values # @@ -26,31 +26,31 @@ --source include/load_sysvars.inc ######################################################################## -# START OF tx_isolation TESTS # +# START OF transaction_isolation TESTS # ######################################################################## ######################################################################### -# Saving initial value of tx_isolation in a temporary variable # +# Saving initial value of transaction_isolation in a temporary variable # ######################################################################### -SET @global_start_value = @@global.tx_isolation; +SET @global_start_value = @@global.transaction_isolation; SELECT @global_start_value; -SET @session_start_value = @@session.tx_isolation; +SET @session_start_value = @@session.transaction_isolation; SELECT @session_start_value; --echo '#--------------------FN_DYNVARS_183_01------------------------#' #################################################################### -# Display the DEFAULT value of tx_isolation # +# Display the DEFAULT value of transaction_isolation # #################################################################### -SET @@global.tx_isolation = 'READ-UNCOMMITTED'; -SET @@global.tx_isolation = DEFAULT; -SELECT @@global.tx_isolation; +SET @@global.transaction_isolation = 'READ-UNCOMMITTED'; +SET @@global.transaction_isolation = DEFAULT; +SELECT @@global.transaction_isolation; -SET @@session.tx_isolation = DEFAULT; -SELECT @@session.tx_isolation; +SET @@session.transaction_isolation = DEFAULT; +SELECT @@session.transaction_isolation; --echo '#---------------------FN_DYNVARS_183_02-------------------------#' ######################################################### @@ -58,97 +58,97 @@ SELECT @@session.tx_isolation; ######################################################### --Error ER_WRONG_VALUE_FOR_VAR -SET @@global.tx_isolation = NULL; +SET @@global.transaction_isolation = NULL; --Error ER_WRONG_VALUE_FOR_VAR -SET @@global.tx_isolation = ''; +SET @@global.transaction_isolation = ''; --Error ER_WRONG_VALUE_FOR_VAR -SET @@session.tx_isolation = NULL; +SET @@session.transaction_isolation = NULL; --Error ER_WRONG_VALUE_FOR_VAR -SET @@session.tx_isolation = ''; +SET @@session.transaction_isolation = ''; --echo '#--------------------FN_DYNVARS_183_03------------------------#' ##################################################################### -# Change the value of tx_isolation to a valid value # +# Change the value of transaction_isolation to a valid value # ##################################################################### -SET @@global.tx_isolation = 'READ-UNCOMMITTED'; -SELECT @@global.tx_isolation; +SET @@global.transaction_isolation = 'READ-UNCOMMITTED'; +SELECT @@global.transaction_isolation; -SET @@global.tx_isolation = 'read-COMMITTED'; -SELECT @@global.tx_isolation; +SET @@global.transaction_isolation = 'read-COMMITTED'; +SELECT @@global.transaction_isolation; -SET @@global.tx_isolation = 'REPEATABLE-READ'; -SELECT @@global.tx_isolation; +SET @@global.transaction_isolation = 'REPEATABLE-READ'; +SELECT @@global.transaction_isolation; -SET @@global.tx_isolation = 'SERIALIZable'; -SELECT @@global.tx_isolation; +SET @@global.transaction_isolation = 'SERIALIZable'; +SELECT @@global.transaction_isolation; -SET @@session.tx_isolation = 'READ-UNCOMMITTED'; -SELECT @@session.tx_isolation; +SET @@session.transaction_isolation = 'READ-UNCOMMITTED'; +SELECT @@session.transaction_isolation; -SET @@session.tx_isolation = 'READ-COMMITTED'; -SELECT @@session.tx_isolation; +SET @@session.transaction_isolation = 'READ-COMMITTED'; +SELECT @@session.transaction_isolation; -SET @@session.tx_isolation = 'REPEATABLE-READ'; -SELECT @@session.tx_isolation; +SET @@session.transaction_isolation = 'REPEATABLE-READ'; +SELECT @@session.transaction_isolation; -SET @@session.tx_isolation = 'serializable'; -SELECT @@session.tx_isolation; +SET @@session.transaction_isolation = 'serializable'; +SELECT @@session.transaction_isolation; --echo '#--------------------FN_DYNVARS_183_04-------------------------#' ####################################################################### -# Change the value of tx_isolation to invalid value # +# Change the value of transaction_isolation to invalid value # ####################################################################### # for global scope --Error ER_WRONG_VALUE_FOR_VAR -SET @@global.tx_isolation = -1; +SET @@global.transaction_isolation = -1; --Error ER_WRONG_VALUE_FOR_VAR -SET @@global.tx_isolation = READUNCOMMITTED; +SET @@global.transaction_isolation = READUNCOMMITTED; --Error ER_WRONG_VALUE_FOR_VAR -SET @@global.tx_isolation = 'REPEATABLE'; +SET @@global.transaction_isolation = 'REPEATABLE'; --Error ER_WRONG_VALUE_FOR_VAR -SET @@global.tx_isolation = OFF; +SET @@global.transaction_isolation = OFF; --Error ER_WRONG_VALUE_FOR_VAR -SET @@global.tx_isolation = ON; +SET @@global.transaction_isolation = ON; --Error ER_WRONG_VALUE_FOR_VAR -SET @@global.tx_isolation = 'NON-SERIALIZABLE'; +SET @@global.transaction_isolation = 'NON-SERIALIZABLE'; # for session scope --Error ER_WRONG_VALUE_FOR_VAR -SET @@tx_isolation = -1; +SET @@transaction_isolation = -1; --Error ER_WRONG_VALUE_FOR_VAR -SET @@tx_isolation = READUNCOMMITTED; +SET @@transaction_isolation = READUNCOMMITTED; --Error ER_WRONG_VALUE_FOR_VAR -SET @@tx_isolation = 'REPEATABLE'; +SET @@transaction_isolation = 'REPEATABLE'; --Error ER_WRONG_VALUE_FOR_VAR -SET @@tx_isolation = 'NONE'; +SET @@transaction_isolation = 'NONE'; --Error ER_WRONG_VALUE_FOR_VAR -SET @@tx_isolation = 'ALL'; +SET @@transaction_isolation = 'ALL'; --echo '#-------------------FN_DYNVARS_183_05----------------------------#' ######################################################################### # Check if the value in session Table matches value in variable # ######################################################################### -SELECT @@session.tx_isolation = VARIABLE_VALUE +SELECT @@session.transaction_isolation = VARIABLE_VALUE FROM INFORMATION_SCHEMA.SESSION_VARIABLES -WHERE VARIABLE_NAME='tx_isolation'; +WHERE VARIABLE_NAME='transaction_isolation'; --echo '#----------------------FN_DYNVARS_183_06------------------------#' ######################################################################### # Check if the value in GLOBAL Table matches value in variable # ######################################################################### -SELECT @@global.tx_isolation = VARIABLE_VALUE +SELECT @@global.transaction_isolation = VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES -WHERE VARIABLE_NAME='tx_isolation'; +WHERE VARIABLE_NAME='transaction_isolation'; --echo '#---------------------FN_DYNVARS_183_07-------------------------#' @@ -157,56 +157,56 @@ WHERE VARIABLE_NAME='tx_isolation'; ################################################################### # test if variable accepts 0,1,2 -SET @@global.tx_isolation = 0; -SELECT @@global.tx_isolation; +SET @@global.transaction_isolation = 0; +SELECT @@global.transaction_isolation; -SET @@global.tx_isolation = 1; -SELECT @@global.tx_isolation; +SET @@global.transaction_isolation = 1; +SELECT @@global.transaction_isolation; -SET @@global.tx_isolation = 2; -SELECT @@global.tx_isolation; +SET @@global.transaction_isolation = 2; +SELECT @@global.transaction_isolation; -SET @@global.tx_isolation = 3; -SELECT @@global.tx_isolation; +SET @@global.transaction_isolation = 3; +SELECT @@global.transaction_isolation; --Error ER_WRONG_TYPE_FOR_VAR -SET @@global.tx_isolation = 0.4; +SET @@global.transaction_isolation = 0.4; --echo '#---------------------FN_DYNVARS_183_08----------------------#' ################################################################### # Check if TRUE and FALSE values can be used on variable # ################################################################### -SET @@global.tx_isolation = TRUE; -SELECT @@global.tx_isolation; -SET @@global.tx_isolation = FALSE; -SELECT @@global.tx_isolation; +SET @@global.transaction_isolation = TRUE; +SELECT @@global.transaction_isolation; +SET @@global.transaction_isolation = FALSE; +SELECT @@global.transaction_isolation; --echo '#---------------------FN_DYNVARS_183_09----------------------#' -######################################################################## -# Check if tx_isolation can be accessed with and without @@ sign # -######################################################################## +########################################################################### +# Check if transaction_isolation can be accessed with and without @@ sign # +########################################################################### -SET tx_isolation = 'REPEATABLE-READ'; +SET transaction_isolation = 'REPEATABLE-READ'; #using SET SESSION|GLOBAL syntax -SET session tx_isolation = 'REPEATABLE-READ'; -SELECT @@tx_isolation; +SET session transaction_isolation = 'REPEATABLE-READ'; +SELECT @@transaction_isolation; -SET global tx_isolation = 'REPEATABLE-READ'; -SELECT @@global.tx_isolation; +SET global transaction_isolation = 'REPEATABLE-READ'; +SELECT @@global.transaction_isolation; ############################## # Restore initial value # ############################## -SET @@global.tx_isolation = @global_start_value; -SELECT @@global.tx_isolation; +SET @@global.transaction_isolation = @global_start_value; +SELECT @@global.transaction_isolation; -SET @@session.tx_isolation = @session_start_value; -SELECT @@session.tx_isolation; +SET @@session.transaction_isolation = @session_start_value; +SELECT @@session.transaction_isolation; #################################################################### -# END OF tx_isolation TESTS # +# END OF transaction_isolation TESTS # #################################################################### diff --git a/mysql-test/suite/sys_vars/t/tx_isolation_func-master.opt b/mysql-test/suite/sys_vars/t/transaction_isolation_func-master.opt index 67166a46cd1..67166a46cd1 100644 --- a/mysql-test/suite/sys_vars/t/tx_isolation_func-master.opt +++ b/mysql-test/suite/sys_vars/t/transaction_isolation_func-master.opt diff --git a/mysql-test/suite/sys_vars/t/tx_isolation_func.test b/mysql-test/suite/sys_vars/t/transaction_isolation_func.test index 164422be06a..085d59cb0ee 100644 --- a/mysql-test/suite/sys_vars/t/tx_isolation_func.test +++ b/mysql-test/suite/sys_vars/t/transaction_isolation_func.test @@ -1,6 +1,6 @@ -############# mysql-test\t\tx_isolation_func.test ####################################### +############## mysql-test\suite\sys_vars\t\transaction_isolation_func.test ############## # # -# Variable Name: tx_isolation # +# Variable Name: transaction_isolation # # Scope: GLOBAL & SESSION # # Access Type: Dynamic # # Data Type: ENUMERATION # @@ -11,7 +11,7 @@ # Creation Date: 2008-02-25 # # Author: Sharique Abdullah # # # -# Description: Test Cases of Dynamic System Variable "tx_isolation" # +# Description: Test Cases of Dynamic System Variable "transaction_isolation" # # that checks behavior of this variable in the following ways # # * Functionality based on different values # # # @@ -26,8 +26,8 @@ # Setup # -SET @session_tx_isolation = @@SESSION.tx_isolation; -SET @global_tx_isolation = @@GLOBAL.tx_isolation; +SET @session_transaction_isolation = @@SESSION.transaction_isolation; +SET @global_transaction_isolation = @@GLOBAL.transaction_isolation; # # Creating connections @@ -68,11 +68,11 @@ INSERT INTO t1 VALUES(24, 24); # Testing for value READ-UNCOMMITTED # connection con0; -SET SESSION tx_isolation = 'READ-UNCOMMITTED'; +SET SESSION transaction_isolation = 'READ-UNCOMMITTED'; set binlog_format=mixed; connection con1; -SET SESSION tx_isolation = 'READ-UNCOMMITTED'; +SET SESSION transaction_isolation = 'READ-UNCOMMITTED'; set binlog_format=mixed; # @@ -139,10 +139,10 @@ COMMIT; # Testing for value READ-COMMITTED # connection con0; -SET SESSION tx_isolation = 'READ-COMMITTED'; +SET SESSION transaction_isolation = 'READ-COMMITTED'; connection con1; -SET SESSION tx_isolation = 'READ-COMMITTED'; +SET SESSION transaction_isolation = 'READ-COMMITTED'; # # Testing WHERE on keys using % on even rows @@ -177,10 +177,10 @@ COMMIT; # Testing for value REPEATABLE-READ # connection con0; -SET SESSION tx_isolation = 'REPEATABLE-READ'; +SET SESSION transaction_isolation = 'REPEATABLE-READ'; connection con1; -SET SESSION tx_isolation = 'REPEATABLE-READ'; +SET SESSION transaction_isolation = 'REPEATABLE-READ'; # # Testing WHERE on keys using % on even rows @@ -279,10 +279,10 @@ COMMIT; # Testing for value SERIALIZABLE # connection con0; -SET SESSION tx_isolation = 'SERIALIZABLE'; +SET SESSION transaction_isolation = 'SERIALIZABLE'; connection con1; -SET SESSION tx_isolation = 'SERIALIZABLE'; +SET SESSION transaction_isolation = 'SERIALIZABLE'; # # Testing WHERE on keys using # on even rows @@ -321,33 +321,33 @@ COMMIT; # Session data integrity check & GLOBAL Value check # -SET GLOBAL tx_isolation = 'READ-UNCOMMITTED'; +SET GLOBAL transaction_isolation = 'READ-UNCOMMITTED'; connect (con_int1,localhost,root,,); connection con_int1; -SELECT @@SESSION.tx_isolation; +SELECT @@SESSION.transaction_isolation; --echo READ-UNCOMMITTED Expected -SET SESSION tx_isolation = 'SERIALIZABLE'; +SET SESSION transaction_isolation = 'SERIALIZABLE'; connect (con_int2,localhost,root,,); connection con_int2; -SELECT @@SESSION.tx_isolation; +SELECT @@SESSION.transaction_isolation; --echo READ-UNCOMMITTED Expected -SET SESSION tx_isolation = 'REPEATABLE-READ'; +SET SESSION transaction_isolation = 'REPEATABLE-READ'; connection con_int2; -SELECT @@SESSION.tx_isolation; +SELECT @@SESSION.transaction_isolation; --echo REPEATABLE-READ Expected connection con_int1; -SELECT @@SESSION.tx_isolation; +SELECT @@SESSION.transaction_isolation; --echo SERIALIZABLE Expected -SELECT @@GLOBAL.tx_isolation; +SELECT @@GLOBAL.transaction_isolation; --echo READ-UNCOMMITTED Expected connection default; @@ -359,8 +359,8 @@ disconnect con_int2; # Cleanup # -SET @@SESSION.tx_isolation = @session_tx_isolation; -SET @@GLOBAL.tx_isolation = @global_tx_isolation; +SET @@SESSION.transaction_isolation = @session_transaction_isolation; +SET @@GLOBAL.transaction_isolation = @global_transaction_isolation; connection default; disconnect con0; diff --git a/mysql-test/suite/sys_vars/t/tx_read_only_basic.test b/mysql-test/suite/sys_vars/t/transaction_read_only_basic.test index 0ebfcc31a89..ba8d65a53f1 100644 --- a/mysql-test/suite/sys_vars/t/tx_read_only_basic.test +++ b/mysql-test/suite/sys_vars/t/transaction_read_only_basic.test @@ -1,6 +1,6 @@ -############## mysql-test\t\tx_read_only_basic.test ########################### +######## mysql-test\suite\sys_vars\t\transaction_read_only_basic.test ######### # # -# Variable Name: tx_read_only # +# Variable Name: transaction_read_only # # Scope: GLOBAL & SESSION # # Access Type: Dynamic # # Data Type: boolean # @@ -11,7 +11,7 @@ # Creation Date: 2012-01-12 # # Author: joh # # # -# Description: Test Cases of Dynamic System Variable tx_read_only # +# Description: Test Cases of Dynamic System Variable transaction_read_only # # that checks the behavior of this variable in the following ways# # * Default Value # # * Valid & Invalid values # @@ -26,7 +26,7 @@ --source include/load_sysvars.inc --echo #################################################################### ---echo # START OF tx_read_only TESTS # +--echo # START OF transaction_read_only TESTS # --echo #################################################################### @@ -34,101 +34,101 @@ --echo # Save initial value # --echo ############################################################# -SET @start_global_value = @@global.tx_read_only; +SET @start_global_value = @@global.transaction_read_only; SELECT @start_global_value; -SET @start_session_value = @@session.tx_read_only; +SET @start_session_value = @@session.transaction_read_only; SELECT @start_session_value; --echo ######################################################################## ---echo # Display the DEFAULT value of tx_read_only # +--echo # Display the DEFAULT value of transaction_read_only # --echo ######################################################################## -SET @@global.tx_read_only = ON; -SET @@global.tx_read_only = DEFAULT; -SELECT @@global.tx_read_only; +SET @@global.transaction_read_only = ON; +SET @@global.transaction_read_only = DEFAULT; +SELECT @@global.transaction_read_only; -SET @@session.tx_read_only = ON; -SET @@session.tx_read_only = DEFAULT; -SELECT @@session.tx_read_only; +SET @@session.transaction_read_only = ON; +SET @@session.transaction_read_only = DEFAULT; +SELECT @@session.transaction_read_only; ---echo ############################################################################## ---echo # Change the value of tx_read_only to a valid value for GLOBAL Scope # ---echo ############################################################################## +--echo ############################################################################### +--echo # Change the value of transaction_read_only to a valid value for GLOBAL Scope # +--echo ############################################################################### -SET @@global.tx_read_only = ON; -SELECT @@global.tx_read_only; -SET @@global.tx_read_only = OFF; -SELECT @@global.tx_read_only; -SET @@global.tx_read_only = 0; -SELECT @@global.tx_read_only; -SET @@global.tx_read_only = 1; -SELECT @@global.tx_read_only; -SET @@global.tx_read_only = TRUE; -SELECT @@global.tx_read_only; -SET @@global.tx_read_only = FALSE; -SELECT @@global.tx_read_only; +SET @@global.transaction_read_only = ON; +SELECT @@global.transaction_read_only; +SET @@global.transaction_read_only = OFF; +SELECT @@global.transaction_read_only; +SET @@global.transaction_read_only = 0; +SELECT @@global.transaction_read_only; +SET @@global.transaction_read_only = 1; +SELECT @@global.transaction_read_only; +SET @@global.transaction_read_only = TRUE; +SELECT @@global.transaction_read_only; +SET @@global.transaction_read_only = FALSE; +SELECT @@global.transaction_read_only; ---echo ############################################################################### ---echo # Change the value of tx_read_only to a valid value for SESSION Scope # ---echo ############################################################################### +--echo ################################################################################ +--echo # Change the value of transaction_read_only to a valid value for SESSION Scope # +--echo ################################################################################ -SET @@session.tx_read_only = ON; -SELECT @@session.tx_read_only; -SET @@session.tx_read_only = OFF; -SELECT @@session.tx_read_only; -SET @@session.tx_read_only = 0; -SELECT @@session.tx_read_only; -SET @@session.tx_read_only = 1; -SELECT @@session.tx_read_only; -SET @@session.tx_read_only = TRUE; -SELECT @@session.tx_read_only; -SET @@session.tx_read_only = FALSE; -SELECT @@session.tx_read_only; - - ---echo ################################################################ ---echo # Change the value of tx_read_only to an invalid value # ---echo ################################################################ +SET @@session.transaction_read_only = ON; +SELECT @@session.transaction_read_only; +SET @@session.transaction_read_only = OFF; +SELECT @@session.transaction_read_only; +SET @@session.transaction_read_only = 0; +SELECT @@session.transaction_read_only; +SET @@session.transaction_read_only = 1; +SELECT @@session.transaction_read_only; +SET @@session.transaction_read_only = TRUE; +SELECT @@session.transaction_read_only; +SET @@session.transaction_read_only = FALSE; +SELECT @@session.transaction_read_only; + + +--echo ################################################################# +--echo # Change the value of transaction_read_only to an invalid value # +--echo ################################################################# --Error ER_WRONG_VALUE_FOR_VAR -SET @@global.tx_read_only = 'ONN'; +SET @@global.transaction_read_only = 'ONN'; --Error ER_WRONG_VALUE_FOR_VAR -SET @@global.tx_read_only = "OFFF"; +SET @@global.transaction_read_only = "OFFF"; --Error ER_WRONG_VALUE_FOR_VAR -SET @@global.tx_read_only = TTRUE; +SET @@global.transaction_read_only = TTRUE; --Error ER_WRONG_VALUE_FOR_VAR -SET @@global.tx_read_only = FELSE; +SET @@global.transaction_read_only = FELSE; --Error ER_WRONG_VALUE_FOR_VAR -SET @@global.tx_read_only = -1024; +SET @@global.transaction_read_only = -1024; --Error ER_WRONG_VALUE_FOR_VAR -SET @@global.tx_read_only = 65536; +SET @@global.transaction_read_only = 65536; --Error ER_WRONG_TYPE_FOR_VAR -SET @@global.tx_read_only = 65530.34; +SET @@global.transaction_read_only = 65530.34; --Error ER_WRONG_VALUE_FOR_VAR -SET @@global.tx_read_only = test; +SET @@global.transaction_read_only = test; --Error ER_WRONG_VALUE_FOR_VAR -SET @@session.tx_read_only = ONN; +SET @@session.transaction_read_only = ONN; --Error ER_WRONG_VALUE_FOR_VAR -SET @@session.tx_read_only = ONF; +SET @@session.transaction_read_only = ONF; --Error ER_WRONG_VALUE_FOR_VAR -SET @@session.tx_read_only = OF; +SET @@session.transaction_read_only = OF; --Error ER_WRONG_VALUE_FOR_VAR -SET @@session.tx_read_only = 'OFN'; +SET @@session.transaction_read_only = 'OFN'; --Error ER_WRONG_VALUE_FOR_VAR -SET @@session.tx_read_only = -2; +SET @@session.transaction_read_only = -2; --Error ER_WRONG_TYPE_FOR_VAR -SET @@session.tx_read_only = 65530.34; +SET @@session.transaction_read_only = 65530.34; --Error ER_WRONG_VALUE_FOR_VAR -SET @@session.tx_read_only = 65550; +SET @@session.transaction_read_only = 65550; --Error ER_WRONG_VALUE_FOR_VAR -SET @@session.tx_read_only = test; -SELECT @@session.tx_read_only; +SET @@session.transaction_read_only = test; +SELECT @@session.transaction_read_only; --echo #################################################################### @@ -136,26 +136,26 @@ SELECT @@session.tx_read_only; --echo #################################################################### -SELECT IF(@@global.tx_read_only, "ON", "OFF") = VARIABLE_VALUE +SELECT IF(@@global.transaction_read_only, "ON", "OFF") = VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES -WHERE VARIABLE_NAME='tx_read_only'; +WHERE VARIABLE_NAME='transaction_read_only'; --echo #################################################################### --echo # Check if the value in SESSION Table matches value in variable # --echo #################################################################### -SELECT IF(@@session.tx_read_only, "ON", "OFF") = VARIABLE_VALUE +SELECT IF(@@session.transaction_read_only, "ON", "OFF") = VARIABLE_VALUE FROM INFORMATION_SCHEMA.SESSION_VARIABLES -WHERE VARIABLE_NAME='tx_read_only'; +WHERE VARIABLE_NAME='transaction_read_only'; --echo ############################################################################### --echo # Check if accessing variable with and without GLOBAL point to same variable # --echo ############################################################################### -SET @@tx_read_only = OFF; -SET @@global.tx_read_only = ON; -SELECT @@tx_read_only = @@global.tx_read_only; +SET @@transaction_read_only = OFF; +SET @@global.transaction_read_only = ON; +SELECT @@transaction_read_only = @@global.transaction_read_only; --echo ############################################################################## @@ -163,37 +163,37 @@ SELECT @@tx_read_only = @@global.tx_read_only; --echo # to same session variable # --echo ############################################################################## -SET @@tx_read_only = ON; -SELECT @@tx_read_only = @@local.tx_read_only; -SELECT @@local.tx_read_only = @@session.tx_read_only; +SET @@transaction_read_only = ON; +SELECT @@transaction_read_only = @@local.transaction_read_only; +SELECT @@local.transaction_read_only = @@session.transaction_read_only; --echo ############################################################################### ---echo # Check if tx_read_only can be accessed with and without @@ sign # +--echo # Check if transaction_read_only can be accessed with and without @@ sign # --echo ############################################################################### --echo # @@session is synonym for SESSION -SET @@session.tx_read_only= 0; +SET @@session.transaction_read_only= 0; --echo # Without modifier, SET changes session variable -SET tx_read_only = 1; -SELECT @@tx_read_only; +SET transaction_read_only = 1; +SELECT @@transaction_read_only; --echo # name1.name2 refers to database_name.table_name --Error ER_UNKNOWN_TABLE -SELECT session.tx_read_only; +SELECT session.transaction_read_only; --echo #################################### --echo # Restore initial value # --echo #################################### -SET @@global.tx_read_only = @start_global_value; -SELECT @@global.tx_read_only; -SET @@session.tx_read_only = @start_session_value; -SELECT @@session.tx_read_only; +SET @@global.transaction_read_only = @start_global_value; +SELECT @@global.transaction_read_only; +SET @@session.transaction_read_only = @start_session_value; +SELECT @@session.transaction_read_only; --echo ######################################################### ---echo # END OF tx_read_only TESTS # +--echo # END OF transaction_read_only TESTS # --echo ######################################################### diff --git a/mysql-test/suite/sys_vars/t/tx_compatibility-master.opt b/mysql-test/suite/sys_vars/t/tx_compatibility-master.opt new file mode 100644 index 00000000000..f22a5ebb640 --- /dev/null +++ b/mysql-test/suite/sys_vars/t/tx_compatibility-master.opt @@ -0,0 +1,2 @@ +--transaction-isolation=SERIALIZABLE +--transaction-read-only diff --git a/mysql-test/suite/sys_vars/t/tx_compatibility.test b/mysql-test/suite/sys_vars/t/tx_compatibility.test new file mode 100644 index 00000000000..2521c366050 --- /dev/null +++ b/mysql-test/suite/sys_vars/t/tx_compatibility.test @@ -0,0 +1,68 @@ +--echo # +--echo # MDEV-21921 Make transaction_isolation and transaction_read_only into +--echo # system variables +--echo # + +SET @saved_global_isolation= @@global.transaction_isolation; +SET @saved_global_read_only= @@global.transaction_read_only; + +--echo # Case 1: Check the influence of --transaction_* on +--echo # @@session.transaction_* and @@global.transaction_*, +--echo # @@session.tx_*, @@global.tx_*. + +SELECT @@session.transaction_isolation, @@global.transaction_isolation, + @@session.tx_isolation, @@global.tx_isolation; +SHOW GLOBAL VARIABLES LIKE '%_isolation'; +SHOW SESSION VARIABLES LIKE '%_isolation'; + +SELECT @@session.transaction_read_only, @@global.transaction_read_only, + @@session.tx_read_only, @@global.tx_read_only; +SHOW GLOBAL VARIABLES LIKE '%_read_only'; +SHOW SESSION VARIABLES LIKE '%_read_only'; + +--echo # Case 2: Check that the change to tx_* is reflected to transaction_*. + +SET tx_isolation= 'REPEATABLE-READ'; +SET @@global.tx_isolation= 'SERIALIZABLE'; +SELECT @@session.tx_isolation, @@global.tx_isolation, +@@session.transaction_isolation, @@global.transaction_isolation; +SHOW GLOBAL VARIABLES LIKE '%_isolation'; +SHOW SESSION VARIABLES LIKE '%_isolation'; + +SET STATEMENT tx_isolation= 'SERIALIZABLE' FOR SHOW SESSION VARIABLES LIKE '%_isolation'; +SHOW SESSION VARIABLES LIKE '%_isolation'; + +SET tx_read_only= OFF; +SET @@global.tx_read_only= ON; +SELECT @@session.tx_read_only, @@global.tx_read_only, +@@session.transaction_read_only, @@global.transaction_read_only; +SHOW GLOBAL VARIABLES LIKE '%_read_only'; +SHOW SESSION VARIABLES LIKE '%_read_only'; + +SET STATEMENT tx_read_only= ON FOR SHOW SESSION VARIABLES LIKE '%_read_only'; +SHOW SESSION VARIABLES LIKE '%_read_only'; + +--echo # Case 3: Check that the change to transaction_* is reflected to tx_*. + +SET transaction_isolation= 'SERIALIZABLE'; +SET @@global.transaction_isolation= 'REPEATABLE-READ'; +SELECT @@session.tx_isolation, @@global.tx_isolation, +@@session.transaction_isolation, @@global.transaction_isolation; +SHOW GLOBAL VARIABLES LIKE '%_isolation'; +SHOW SESSION VARIABLES LIKE '%_isolation'; + +SET STATEMENT transaction_isolation= 'REPEATABLE-READ' FOR SHOW SESSION VARIABLES LIKE '%_isolation'; +SHOW SESSION VARIABLES LIKE '%_isolation'; + +SET transaction_read_only= ON; +SET @@global.transaction_read_only= OFF; +SELECT @@session.tx_read_only, @@global.tx_read_only, +@@session.transaction_read_only, @@global.transaction_read_only; +SHOW GLOBAL VARIABLES LIKE '%_read_only'; +SHOW SESSION VARIABLES LIKE '%_read_only'; + +SET STATEMENT transaction_read_only= OFF FOR SHOW SESSION VARIABLES LIKE '%_read_only'; +SHOW SESSION VARIABLES LIKE '%_read_only'; + +SET @@global.transaction_isolation= @saved_global_isolation; +SET @@global.transaction_read_only= @saved_global_read_only; |