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 | |
| 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.
100 files changed, 1232 insertions, 962 deletions
diff --git a/mysql-test/include/concurrent.inc b/mysql-test/include/concurrent.inc index 1d65549f8f4..f9058c069ca 100644 --- a/mysql-test/include/concurrent.inc +++ b/mysql-test/include/concurrent.inc @@ -30,7 +30,7 @@ SET SQL_MODE=""; # # Show prerequisites for this test. # -SELECT @@global.tx_isolation; +SELECT @@global.transaction_isolation; # # With the transaction isolation level REPEATABLE READ (the default) # or SERIALIZEBLE, InnoDB takes "next-key locks"/"gap locks". This means it @@ -43,7 +43,7 @@ SELECT @@global.tx_isolation; # We use the variable $keep_locks to set the expectations for # lock wait timeouts accordingly. # -let $keep_locks= `SELECT @@global.tx_isolation IN ('REPEATABLE-READ','SERIALIZABLE')`; +let $keep_locks= `SELECT @@global.transaction_isolation IN ('REPEATABLE-READ','SERIALIZABLE')`; --echo # keep_locks == $keep_locks # diff --git a/mysql-test/include/mix2.inc b/mysql-test/include/mix2.inc index 6a6fbcbc2ae..5ea4b79531c 100644 --- a/mysql-test/include/mix2.inc +++ b/mysql-test/include/mix2.inc @@ -776,7 +776,7 @@ eval create table t1 (id int unsigned not null auto_increment, code tinyint unsi 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; diff --git a/mysql-test/main/backup_interaction.result b/mysql-test/main/backup_interaction.result index a6fe7cf448d..e5ddd140516 100644 --- a/mysql-test/main/backup_interaction.result +++ b/mysql-test/main/backup_interaction.result @@ -320,20 +320,20 @@ col1 HANDLER t1_myisam CLOSE; BACKUP STAGE END; drop table t1_innodb,t1_myisam; -# Show the fate and impact of some SET GLOBAL tx_read_only = 1/0 +# Show the fate and impact of some SET GLOBAL transaction_read_only = 1/0 # sliding through the sequence. BACKUP STAGE START; -SET GLOBAL tx_read_only = 1; -SET GLOBAL tx_read_only = 0; +SET GLOBAL transaction_read_only = 1; +SET GLOBAL transaction_read_only = 0; BACKUP STAGE FLUSH; -SET GLOBAL tx_read_only = 1; -SET GLOBAL tx_read_only = 0; +SET GLOBAL transaction_read_only = 1; +SET GLOBAL transaction_read_only = 0; BACKUP STAGE BLOCK_DDL; -SET GLOBAL tx_read_only = 1; -SET GLOBAL tx_read_only = 0; +SET GLOBAL transaction_read_only = 1; +SET GLOBAL transaction_read_only = 0; BACKUP STAGE BLOCK_COMMIT; -SET GLOBAL tx_read_only = 1; -SET GLOBAL tx_read_only = 0; +SET GLOBAL transaction_read_only = 1; +SET GLOBAL transaction_read_only = 0; BACKUP STAGE END; # Show the fate and impact of some SET SESSION sql_log_bin = 0/1 # sliding through the sequence. diff --git a/mysql-test/main/backup_interaction.test b/mysql-test/main/backup_interaction.test index c755bdf8f95..8744ef2c261 100644 --- a/mysql-test/main/backup_interaction.test +++ b/mysql-test/main/backup_interaction.test @@ -300,21 +300,21 @@ HANDLER t1_myisam CLOSE; BACKUP STAGE END; drop table t1_innodb,t1_myisam; ---echo # Show the fate and impact of some SET GLOBAL tx_read_only = 1/0 +--echo # Show the fate and impact of some SET GLOBAL transaction_read_only = 1/0 --echo # sliding through the sequence. BACKUP STAGE START; -SET GLOBAL tx_read_only = 1; -SET GLOBAL tx_read_only = 0; +SET GLOBAL transaction_read_only = 1; +SET GLOBAL transaction_read_only = 0; BACKUP STAGE FLUSH; -SET GLOBAL tx_read_only = 1; -SET GLOBAL tx_read_only = 0; +SET GLOBAL transaction_read_only = 1; +SET GLOBAL transaction_read_only = 0; BACKUP STAGE BLOCK_DDL; -SET GLOBAL tx_read_only = 1; -SET GLOBAL tx_read_only = 0; +SET GLOBAL transaction_read_only = 1; +SET GLOBAL transaction_read_only = 0; BACKUP STAGE BLOCK_COMMIT; -SET GLOBAL tx_read_only = 1; -SET GLOBAL tx_read_only = 0; +SET GLOBAL transaction_read_only = 1; +SET GLOBAL transaction_read_only = 0; BACKUP STAGE END; --echo # Show the fate and impact of some SET SESSION sql_log_bin = 0/1 diff --git a/mysql-test/main/backup_stages.result b/mysql-test/main/backup_stages.result index 823e5d7e462..045497dcd0b 100644 --- a/mysql-test/main/backup_stages.result +++ b/mysql-test/main/backup_stages.result @@ -221,11 +221,11 @@ SELECT * FROM t_permanent_myisam ORDER BY col1; col1 col2 3 NULL SET AUTOCOMMIT = 0; -SET GLOBAL tx_read_only = 1; +SET GLOBAL transaction_read_only = 1; connection con12; BACKUP STAGE START; BACKUP STAGE END; -SET GLOBAL tx_read_only = 0; +SET GLOBAL transaction_read_only = 0; DROP VIEW v_some_view; DROP TABLE t_con1_innodb; DROP TABLE t_con1_myisam; @@ -241,8 +241,8 @@ LOCK TABLES t_permanent_myisam READ; LOCK TABLES t_permanent_innodb WRITE; LOCK TABLES t_permanent_myisam WRITE; UNLOCK TABLES; -SET GLOBAL tx_read_only = 1; -SET GLOBAL tx_read_only = 0; +SET GLOBAL transaction_read_only = 1; +SET GLOBAL transaction_read_only = 0; connection backup; BACKUP STAGE FLUSH; connection con11; @@ -255,8 +255,8 @@ LOCK TABLES t_permanent_innodb WRITE; LOCK TABLES t_permanent_myisam WRITE; ERROR HY000: Lock wait timeout exceeded; try restarting transaction UNLOCK TABLES; -SET GLOBAL tx_read_only = 1; -SET GLOBAL tx_read_only = 0; +SET GLOBAL transaction_read_only = 1; +SET GLOBAL transaction_read_only = 0; connection backup; BACKUP STAGE BLOCK_DDL; connection con11; @@ -270,8 +270,8 @@ ERROR HY000: Lock wait timeout exceeded; try restarting transaction LOCK TABLES t_permanent_myisam WRITE; ERROR HY000: Lock wait timeout exceeded; try restarting transaction UNLOCK TABLES; -SET GLOBAL tx_read_only = 1; -SET GLOBAL tx_read_only = 0; +SET GLOBAL transaction_read_only = 1; +SET GLOBAL transaction_read_only = 0; connection backup; BACKUP STAGE BLOCK_COMMIT; connection con11; @@ -285,8 +285,8 @@ ERROR HY000: Lock wait timeout exceeded; try restarting transaction LOCK TABLES t_permanent_myisam WRITE; ERROR HY000: Lock wait timeout exceeded; try restarting transaction UNLOCK TABLES; -SET GLOBAL tx_read_only = 1; -SET GLOBAL tx_read_only = 0; +SET GLOBAL transaction_read_only = 1; +SET GLOBAL transaction_read_only = 0; connection backup; BACKUP STAGE END; DROP TABLE t_permanent_innodb; diff --git a/mysql-test/main/backup_stages.test b/mysql-test/main/backup_stages.test index 77d410b6caf..7af65284bf4 100644 --- a/mysql-test/main/backup_stages.test +++ b/mysql-test/main/backup_stages.test @@ -254,11 +254,11 @@ COMMIT; SELECT * FROM t_permanent_innodb ORDER BY col1; SELECT * FROM t_permanent_myisam ORDER BY col1; SET AUTOCOMMIT = 0; -SET GLOBAL tx_read_only = 1; +SET GLOBAL transaction_read_only = 1; --connection con12 BACKUP STAGE START; BACKUP STAGE END; -SET GLOBAL tx_read_only = 0; +SET GLOBAL transaction_read_only = 0; DROP VIEW v_some_view; DROP TABLE t_con1_innodb; DROP TABLE t_con1_myisam; @@ -276,8 +276,8 @@ LOCK TABLES t_permanent_myisam READ; LOCK TABLES t_permanent_innodb WRITE; LOCK TABLES t_permanent_myisam WRITE; UNLOCK TABLES; -SET GLOBAL tx_read_only = 1; -SET GLOBAL tx_read_only = 0; +SET GLOBAL transaction_read_only = 1; +SET GLOBAL transaction_read_only = 0; --connection backup BACKUP STAGE FLUSH; --connection con11 @@ -290,8 +290,8 @@ LOCK TABLES t_permanent_innodb WRITE; --error ER_LOCK_WAIT_TIMEOUT LOCK TABLES t_permanent_myisam WRITE; UNLOCK TABLES; -SET GLOBAL tx_read_only = 1; -SET GLOBAL tx_read_only = 0; +SET GLOBAL transaction_read_only = 1; +SET GLOBAL transaction_read_only = 0; --connection backup BACKUP STAGE BLOCK_DDL; --connection con11 @@ -305,8 +305,8 @@ LOCK TABLES t_permanent_innodb WRITE; --error ER_LOCK_WAIT_TIMEOUT LOCK TABLES t_permanent_myisam WRITE; UNLOCK TABLES; -SET GLOBAL tx_read_only = 1; -SET GLOBAL tx_read_only = 0; +SET GLOBAL transaction_read_only = 1; +SET GLOBAL transaction_read_only = 0; --connection backup BACKUP STAGE BLOCK_COMMIT; --connection con11 @@ -320,8 +320,8 @@ LOCK TABLES t_permanent_innodb WRITE; --error ER_LOCK_WAIT_TIMEOUT LOCK TABLES t_permanent_myisam WRITE; UNLOCK TABLES; -SET GLOBAL tx_read_only = 1; -SET GLOBAL tx_read_only = 0; +SET GLOBAL transaction_read_only = 1; +SET GLOBAL transaction_read_only = 0; --connection backup BACKUP STAGE END; diff --git a/mysql-test/main/commit.result b/mysql-test/main/commit.result index 44d46900d38..bf12f3e0ea0 100644 --- a/mysql-test/main/commit.result +++ b/mysql-test/main/commit.result @@ -19,21 +19,21 @@ SET @@autocommit=0; COMMIT; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; START TRANSACTION; -SELECT @@tx_isolation; -@@tx_isolation +SELECT @@transaction_isolation; +@@transaction_isolation REPEATABLE-READ Should be REPEATABLE READ SELECT * FROM t1; s1 1 2 -SELECT @@tx_isolation; -@@tx_isolation +SELECT @@transaction_isolation; +@@transaction_isolation REPEATABLE-READ Should be REPEATABLE READ INSERT INTO t1 VALUES (-1); -SELECT @@tx_isolation; -@@tx_isolation +SELECT @@transaction_isolation; +@@transaction_isolation REPEATABLE-READ Should be REPEATABLE READ COMMIT; @@ -310,20 +310,20 @@ COMMIT; # # Test 2: Check setting of variable. SET SESSION TRANSACTION READ WRITE; -SELECT @@tx_read_only; -@@tx_read_only +SELECT @@transaction_read_only; +@@transaction_read_only 0 SET SESSION TRANSACTION READ ONLY; -SELECT @@tx_read_only; -@@tx_read_only +SELECT @@transaction_read_only; +@@transaction_read_only 1 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ, READ WRITE; -SELECT @@tx_read_only; -@@tx_read_only +SELECT @@transaction_read_only; +@@transaction_read_only 0 SET SESSION TRANSACTION READ ONLY, ISOLATION LEVEL REPEATABLE READ; -SELECT @@tx_read_only; -@@tx_read_only +SELECT @@transaction_read_only; +@@transaction_read_only 1 START TRANSACTION; # Not allowed inside a transaction @@ -528,16 +528,16 @@ DROP TABLE t1; # Test 7: SET TRANSACTION inside stored routines CREATE PROCEDURE p1() SET SESSION TRANSACTION READ ONLY; CALL p1(); -SELECT @@tx_read_only; -@@tx_read_only +SELECT @@transaction_read_only; +@@transaction_read_only 1 SET SESSION TRANSACTION READ WRITE; DROP PROCEDURE p1; CREATE PROCEDURE p1() SET SESSION TRANSACTION READ ONLY, ISOLATION LEVEL SERIALIZABLE; CALL p1(); -SELECT @@tx_read_only; -@@tx_read_only +SELECT @@transaction_read_only; +@@transaction_read_only 1 SET SESSION TRANSACTION READ WRITE, ISOLATION LEVEL REPEATABLE READ; DROP PROCEDURE p1; @@ -549,8 +549,8 @@ END| SELECT f1(); f1() 1 -SELECT @@tx_read_only; -@@tx_read_only +SELECT @@transaction_read_only; +@@transaction_read_only 1 SET SESSION TRANSACTION READ WRITE; DROP FUNCTION f1; @@ -562,8 +562,8 @@ END| SELECT f1(); f1() 1 -SELECT @@tx_read_only; -@@tx_read_only +SELECT @@transaction_read_only; +@@transaction_read_only 1 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ, READ WRITE; DROP FUNCTION f1; diff --git a/mysql-test/main/commit.test b/mysql-test/main/commit.test index a0d4ddee152..4fb86f5e8bc 100644 --- a/mysql-test/main/commit.test +++ b/mysql-test/main/commit.test @@ -34,23 +34,23 @@ COMMIT; # # Verify consistent output from -# SELECT @@tx_isolation (Bug#20837) +# SELECT @@transaction_isolation (Bug#20837) # # The transaction will be in READ UNCOMMITTED mode, -# but SELECT @@tx_isolation should report the session +# but SELECT @@transaction_isolation should report the session # value, which is REPEATABLE READ # SET @@autocommit=0; COMMIT; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; START TRANSACTION; -SELECT @@tx_isolation; +SELECT @@transaction_isolation; --echo Should be REPEATABLE READ SELECT * FROM t1; -SELECT @@tx_isolation; +SELECT @@transaction_isolation; --echo Should be REPEATABLE READ INSERT INTO t1 VALUES (-1); -SELECT @@tx_isolation; +SELECT @@transaction_isolation; --echo Should be REPEATABLE READ COMMIT; @@ -370,16 +370,16 @@ COMMIT; --echo # Test 2: Check setting of variable. SET SESSION TRANSACTION READ WRITE; -SELECT @@tx_read_only; +SELECT @@transaction_read_only; SET SESSION TRANSACTION READ ONLY; -SELECT @@tx_read_only; +SELECT @@transaction_read_only; SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ, READ WRITE; -SELECT @@tx_read_only; +SELECT @@transaction_read_only; SET SESSION TRANSACTION READ ONLY, ISOLATION LEVEL REPEATABLE READ; -SELECT @@tx_read_only; +SELECT @@transaction_read_only; START TRANSACTION; --echo # Not allowed inside a transaction @@ -628,14 +628,14 @@ DROP TABLE t1; CREATE PROCEDURE p1() SET SESSION TRANSACTION READ ONLY; CALL p1(); -SELECT @@tx_read_only; +SELECT @@transaction_read_only; SET SESSION TRANSACTION READ WRITE; DROP PROCEDURE p1; CREATE PROCEDURE p1() SET SESSION TRANSACTION READ ONLY, ISOLATION LEVEL SERIALIZABLE; CALL p1(); -SELECT @@tx_read_only; +SELECT @@transaction_read_only; SET SESSION TRANSACTION READ WRITE, ISOLATION LEVEL REPEATABLE READ; DROP PROCEDURE p1; @@ -648,7 +648,7 @@ END| delimiter ;| SELECT f1(); -SELECT @@tx_read_only; +SELECT @@transaction_read_only; SET SESSION TRANSACTION READ WRITE; DROP FUNCTION f1; @@ -661,7 +661,7 @@ END| delimiter ;| SELECT f1(); -SELECT @@tx_read_only; +SELECT @@transaction_read_only; SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ, READ WRITE; DROP FUNCTION f1; diff --git a/mysql-test/main/concurrent_innodb_safelog.result b/mysql-test/main/concurrent_innodb_safelog.result index 40f89c7c65b..182e99256f8 100644 --- a/mysql-test/main/concurrent_innodb_safelog.result +++ b/mysql-test/main/concurrent_innodb_safelog.result @@ -3,8 +3,8 @@ SET GLOBAL innodb_lock_wait_timeout = 1; SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ; connection default; SET SQL_MODE=""; -SELECT @@global.tx_isolation; -@@global.tx_isolation +SELECT @@global.transaction_isolation; +@@global.transaction_isolation REPEATABLE-READ # keep_locks == 1 GRANT ALL ON test.* TO mysqltest@localhost; diff --git a/mysql-test/main/concurrent_innodb_unsafelog.result b/mysql-test/main/concurrent_innodb_unsafelog.result index 3771438bc66..b87a420b6c6 100644 --- a/mysql-test/main/concurrent_innodb_unsafelog.result +++ b/mysql-test/main/concurrent_innodb_unsafelog.result @@ -1,11 +1,11 @@ SET @save_timeout = @@GLOBAL.innodb_lock_wait_timeout; SET GLOBAL innodb_lock_wait_timeout = 1; -SET @save_isolation = @@GLOBAL.tx_isolation; +SET @save_isolation = @@GLOBAL.transaction_isolation; SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED; connection default; SET SQL_MODE=""; -SELECT @@global.tx_isolation; -@@global.tx_isolation +SELECT @@global.transaction_isolation; +@@global.transaction_isolation READ-COMMITTED # keep_locks == 0 GRANT ALL ON test.* TO mysqltest@localhost; @@ -791,4 +791,4 @@ drop table t1; drop user mysqltest@localhost; SET SQL_MODE=default; SET GLOBAL innodb_lock_wait_timeout = @save_timeout; -SET GLOBAL tx_isolation = @save_isolation; +SET GLOBAL transaction_isolation = @save_isolation; diff --git a/mysql-test/main/concurrent_innodb_unsafelog.test b/mysql-test/main/concurrent_innodb_unsafelog.test index d9e5effaaf6..400d7ef8d87 100644 --- a/mysql-test/main/concurrent_innodb_unsafelog.test +++ b/mysql-test/main/concurrent_innodb_unsafelog.test @@ -18,10 +18,10 @@ let $engine_type= InnoDB; SET @save_timeout = @@GLOBAL.innodb_lock_wait_timeout; SET GLOBAL innodb_lock_wait_timeout = 1; -SET @save_isolation = @@GLOBAL.tx_isolation; +SET @save_isolation = @@GLOBAL.transaction_isolation; SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED; --disable_service_connection --source include/concurrent.inc --enable_service_connection SET GLOBAL innodb_lock_wait_timeout = @save_timeout; -SET GLOBAL tx_isolation = @save_isolation; +SET GLOBAL transaction_isolation = @save_isolation; diff --git a/mysql-test/main/innodb_mysql_lock2.result b/mysql-test/main/innodb_mysql_lock2.result index 9dd5bddc085..a03e1d842b2 100644 --- a/mysql-test/main/innodb_mysql_lock2.result +++ b/mysql-test/main/innodb_mysql_lock2.result @@ -12,8 +12,8 @@ # This test makes sense only in REPEATABLE-READ mode as # in SERIALIZABLE mode all statements that read data take # shared lock on them to enforce its semantics. -select @@session.tx_isolation; -@@session.tx_isolation +select @@session.transaction_isolation; +@@session.transaction_isolation REPEATABLE-READ # Prepare playground by creating tables, views, # routines and triggers used in tests. diff --git a/mysql-test/main/innodb_mysql_lock2.test b/mysql-test/main/innodb_mysql_lock2.test index e5bb4bd9565..b35c23ce659 100644 --- a/mysql-test/main/innodb_mysql_lock2.test +++ b/mysql-test/main/innodb_mysql_lock2.test @@ -31,7 +31,7 @@ CALL mtr.add_suppression("Unsafe statement written to the binary log using state --echo # This test makes sense only in REPEATABLE-READ mode as --echo # in SERIALIZABLE mode all statements that read data take --echo # shared lock on them to enforce its semantics. -select @@session.tx_isolation; +select @@session.transaction_isolation; --echo # Prepare playground by creating tables, views, --echo # routines and triggers used in tests. diff --git a/mysql-test/main/mix2_myisam.result b/mysql-test/main/mix2_myisam.result index 842d5bcfb94..1044704c518 100644 --- a/mysql-test/main/mix2_myisam.result +++ b/mysql-test/main/mix2_myisam.result @@ -905,8 +905,8 @@ drop table t1,t2; 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=MyISAM; 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; diff --git a/mysql-test/main/partition_innodb.result b/mysql-test/main/partition_innodb.result index 95ca989eabf..8238650012f 100644 --- a/mysql-test/main/partition_innodb.result +++ b/mysql-test/main/partition_innodb.result @@ -303,12 +303,12 @@ PARTITION p2 VALUES LESS THAN MAXVALUE ); INSERT INTO t1 VALUES (1,1), (2,2), (3,3), (4,4), (5,5), (6,6), (7,7), (8,8), (9,9), (10,10), (11,11); -SET @old_tx_isolation := @@session.tx_isolation; +SET @old_transaction_isolation := @@session.transaction_isolation; SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; SET autocommit = 0; UPDATE t1 SET DATA = data*2 WHERE id = 3; UPDATE t1 SET data = data*2 WHERE data = 2; -SET @@session.tx_isolation = @old_tx_isolation; +SET @@session.transaction_isolation = @old_transaction_isolation; DROP TABLE t1; # Bug#37721, test of ORDER BY on PK and WHERE on INDEX CREATE TABLE t1 ( diff --git a/mysql-test/main/partition_innodb.test b/mysql-test/main/partition_innodb.test index 563a750b81b..aaca678750b 100644 --- a/mysql-test/main/partition_innodb.test +++ b/mysql-test/main/partition_innodb.test @@ -321,7 +321,7 @@ PARTITION BY RANGE(id) ( INSERT INTO t1 VALUES (1,1), (2,2), (3,3), (4,4), (5,5), (6,6), (7,7), (8,8), (9,9), (10,10), (11,11); -SET @old_tx_isolation := @@session.tx_isolation; +SET @old_transaction_isolation := @@session.transaction_isolation; SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; SET autocommit = 0; @@ -344,7 +344,7 @@ UPDATE t1 SET data = data*2 WHERE data = 2; #--replace_regex /.*---TRANSACTION [0-9]+ [0-9]+, .*, OS thread id [0-9]+// /MariaDB thread id [0-9]+, query id [0-9]+ .*// /.*([0-9]+ lock struct\(s\)), heap size [0-9]+, ([0-9]+ row lock\(s\)).*/\1 \2/ #SHOW ENGINE InnoDB STATUS; -SET @@session.tx_isolation = @old_tx_isolation; +SET @@session.transaction_isolation = @old_transaction_isolation; DROP TABLE t1; diff --git a/mysql-test/main/ps.result b/mysql-test/main/ps.result index d60fa28fc96..cb2e9651c3d 100644 --- a/mysql-test/main/ps.result +++ b/mysql-test/main/ps.result @@ -696,21 +696,21 @@ set @@time_zone:='Japan'; execute stmt; @@time_zone Japan -prepare stmt from "select @@tx_isolation"; +prepare stmt from "select @@transaction_isolation"; execute stmt; -@@tx_isolation +@@transaction_isolation REPEATABLE-READ set transaction isolation level read committed; execute stmt; -@@tx_isolation +@@transaction_isolation REPEATABLE-READ set transaction isolation level serializable; execute stmt; -@@tx_isolation +@@transaction_isolation REPEATABLE-READ -set @@tx_isolation=default; +set @@transaction_isolation=default; execute stmt; -@@tx_isolation +@@transaction_isolation REPEATABLE-READ deallocate prepare stmt; prepare stmt from "create temporary table t1 (letter enum('','a','b','c') diff --git a/mysql-test/main/ps.test b/mysql-test/main/ps.test index bb6ce7d4048..e779eefcb4d 100644 --- a/mysql-test/main/ps.test +++ b/mysql-test/main/ps.test @@ -763,13 +763,13 @@ prepare stmt from "select @@time_zone"; execute stmt; set @@time_zone:='Japan'; execute stmt; -prepare stmt from "select @@tx_isolation"; +prepare stmt from "select @@transaction_isolation"; execute stmt; set transaction isolation level read committed; execute stmt; set transaction isolation level serializable; execute stmt; -set @@tx_isolation=default; +set @@transaction_isolation=default; execute stmt; deallocate prepare stmt; diff --git a/mysql-test/main/set_statement.result b/mysql-test/main/set_statement.result index f0a362e9fe2..a1b1d3659ff 100644 --- a/mysql-test/main/set_statement.result +++ b/mysql-test/main/set_statement.result @@ -1215,8 +1215,6 @@ set @@long_query_time= @save_long_query_time; truncate table mysql.slow_log; set statement autocommit=default for select 1; ERROR 42000: The system variable autocommit cannot be set in SET STATEMENT. -set statement tx_isolation=default for select 1; -ERROR 42000: The system variable tx_isolation cannot be set in SET STATEMENT. set statement skip_replication=default for select 1; ERROR 42000: The system variable skip_replication cannot be set in SET STATEMENT. set statement sql_log_off=default for select 1; diff --git a/mysql-test/main/set_statement.test b/mysql-test/main/set_statement.test index cc191a014a1..029310cdc7c 100644 --- a/mysql-test/main/set_statement.test +++ b/mysql-test/main/set_statement.test @@ -1063,8 +1063,6 @@ truncate table mysql.slow_log; --error ER_SET_STATEMENT_NOT_SUPPORTED set statement autocommit=default for select 1; --error ER_SET_STATEMENT_NOT_SUPPORTED -set statement tx_isolation=default for select 1; ---error ER_SET_STATEMENT_NOT_SUPPORTED set statement skip_replication=default for select 1; --error ER_SET_STATEMENT_NOT_SUPPORTED set statement sql_log_off=default for select 1; diff --git a/mysql-test/main/trans_read_only.result b/mysql-test/main/trans_read_only.result index 5e86b0993cd..acced0a059f 100644 --- a/mysql-test/main/trans_read_only.result +++ b/mysql-test/main/trans_read_only.result @@ -4,22 +4,22 @@ # # Test9: The --transaction-read-only startup option. # Check that the option was set by the .opt file. -SELECT @@tx_read_only; -@@tx_read_only +SELECT @@transaction_read_only; +@@transaction_read_only 1 # Also for new connections. connect con1, localhost, root; -SELECT @@tx_read_only; -@@tx_read_only +SELECT @@transaction_read_only; +@@transaction_read_only 1 SET SESSION TRANSACTION READ WRITE; -SELECT @@tx_read_only; -@@tx_read_only +SELECT @@transaction_read_only; +@@transaction_read_only 0 disconnect con1; connection default; -SELECT @@tx_read_only; -@@tx_read_only +SELECT @@transaction_read_only; +@@transaction_read_only 1 # # Test 10: SET TRANSACTION / START TRANSACTION + implicit commit. diff --git a/mysql-test/main/trans_read_only.test b/mysql-test/main/trans_read_only.test index 38b2a833216..d8c2f20b6a9 100644 --- a/mysql-test/main/trans_read_only.test +++ b/mysql-test/main/trans_read_only.test @@ -8,18 +8,18 @@ --echo # Test9: The --transaction-read-only startup option. --echo # Check that the option was set by the .opt file. -SELECT @@tx_read_only; +SELECT @@transaction_read_only; --echo # Also for new connections. connect (con1, localhost, root); -SELECT @@tx_read_only; +SELECT @@transaction_read_only; SET SESSION TRANSACTION READ WRITE; -SELECT @@tx_read_only; +SELECT @@transaction_read_only; disconnect con1; --source include/wait_until_disconnected.inc connection default; -SELECT @@tx_read_only; +SELECT @@transaction_read_only; --echo # diff --git a/mysql-test/main/unsafe_binlog_innodb.result b/mysql-test/main/unsafe_binlog_innodb.result index ced29926400..19956cfc84d 100644 --- a/mysql-test/main/unsafe_binlog_innodb.result +++ b/mysql-test/main/unsafe_binlog_innodb.result @@ -1,6 +1,6 @@ SET @save_timeout = @@GLOBAL.innodb_lock_wait_timeout; SET GLOBAL innodb_lock_wait_timeout = 1; -SET @save_isolation = @@GLOBAL.tx_isolation; +SET @save_isolation = @@GLOBAL.transaction_isolation; SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9,t10; create table t1 (id int not null, f_id int not null, f int not null, @@ -192,4 +192,4 @@ disconnect i; disconnect j; drop table t1, t2, t3, t5, t6, t8, t9; SET GLOBAL innodb_lock_wait_timeout = @save_timeout; -SET GLOBAL tx_isolation = @save_isolation; +SET GLOBAL transaction_isolation = @save_isolation; diff --git a/mysql-test/main/unsafe_binlog_innodb.test b/mysql-test/main/unsafe_binlog_innodb.test index abd2c58de30..0f57c60cd61 100644 --- a/mysql-test/main/unsafe_binlog_innodb.test +++ b/mysql-test/main/unsafe_binlog_innodb.test @@ -15,10 +15,10 @@ let $engine_type= InnoDB; SET @save_timeout = @@GLOBAL.innodb_lock_wait_timeout; SET GLOBAL innodb_lock_wait_timeout = 1; -SET @save_isolation = @@GLOBAL.tx_isolation; +SET @save_isolation = @@GLOBAL.transaction_isolation; SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED; --source include/unsafe_binlog.inc SET GLOBAL innodb_lock_wait_timeout = @save_timeout; -SET GLOBAL tx_isolation = @save_isolation; +SET GLOBAL transaction_isolation = @save_isolation; diff --git a/mysql-test/main/variables.result b/mysql-test/main/variables.result index 9b54a24be71..4359a8eef01 100644 --- a/mysql-test/main/variables.result +++ b/mysql-test/main/variables.result @@ -548,7 +548,7 @@ set default_storage_engine=myisam; set global thread_cache_size=100; set timestamp=1, timestamp=default; set tmp_table_size=1024; -set tx_isolation="READ-COMMITTED"; +set transaction_isolation="READ-COMMITTED"; set wait_timeout=100; set log_warnings=1; set global log_warnings=1; diff --git a/mysql-test/main/variables.test b/mysql-test/main/variables.test index 89267560ca6..77c6a153304 100644 --- a/mysql-test/main/variables.test +++ b/mysql-test/main/variables.test @@ -336,7 +336,7 @@ set default_storage_engine=myisam; set global thread_cache_size=100; set timestamp=1, timestamp=default; set tmp_table_size=1024; -set tx_isolation="READ-COMMITTED"; +set transaction_isolation="READ-COMMITTED"; set wait_timeout=100; set log_warnings=1; set global log_warnings=1; 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; diff --git a/sql/session_tracker.h b/sql/session_tracker.h index 5715b5837b5..811368fb340 100644 --- a/sql/session_tracker.h +++ b/sql/session_tracker.h @@ -290,9 +290,9 @@ enum enum_tx_state { Transaction access mode */ enum enum_tx_read_flags { - TX_READ_INHERIT = 0, ///< not explicitly set, inherit session.tx_read_only - TX_READ_ONLY = 1, ///< START TRANSACTION READ ONLY, or tx_read_only=1 - TX_READ_WRITE = 2, ///< START TRANSACTION READ WRITE, or tx_read_only=0 + TX_READ_INHERIT = 0, ///< not explicitly set, inherit session.transaction_read_only + TX_READ_ONLY = 1, ///< START TRANSACTION READ ONLY, or transaction_read_only=1 + TX_READ_WRITE = 2, ///< START TRANSACTION READ WRITE, or transaction_read_only=0 }; @@ -300,7 +300,7 @@ enum enum_tx_read_flags { Transaction isolation level */ enum enum_tx_isol_level { - TX_ISOL_INHERIT = 0, ///< not explicitly set, inherit session.tx_isolation + TX_ISOL_INHERIT = 0, ///< not explicitly set, inherit session.transaction_isolation TX_ISOL_UNCOMMITTED = 1, TX_ISOL_COMMITTED = 2, TX_ISOL_REPEATABLE = 3, diff --git a/sql/set_var.cc b/sql/set_var.cc index b49040b8ec3..9fd5aa318c9 100644 --- a/sql/set_var.cc +++ b/sql/set_var.cc @@ -214,8 +214,9 @@ bool sys_var::update(THD *thd, set_var *var) /* Make sure we don't session-track variables that are not actually - part of the session. tx_isolation and and tx_read_only for example - exist as GLOBAL, SESSION, and one-shot ("for next transaction only"). + part of the session. transaction_isolation and transaction_read_only for + example exist as GLOBAL, SESSION, and one-shot ("for next transaction + only"). */ if ((var->type == OPT_SESSION) && (!ret)) { diff --git a/sql/sql_class.h b/sql/sql_class.h index 3021055d4b6..bc94d583398 100644 --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -3505,15 +3505,15 @@ public: /* Current or next transaction isolation level. When a connection is established, the value is taken from - @@session.tx_isolation (default transaction isolation for - the session), which is in turn taken from @@global.tx_isolation - (the global value). + @@session.transaction_isolation (default transaction isolation + for the session), which is in turn taken from + @@global.transaction_isolation (the global value). If there is no transaction started, this variable holds the value of the next transaction's isolation level. When a transaction starts, the value stored in this variable becomes "actual". At transaction commit or rollback, we assign this variable - again from @@session.tx_isolation. + again from @@session.transaction_isolation. The only statement that can otherwise change the value of this variable is SET TRANSACTION ISOLATION LEVEL. Its purpose is to effect the isolation level of the next @@ -3523,13 +3523,13 @@ public: transaction, this assignment (naturally) only affects the upcoming transaction. At the end of the current active transaction the value is - be reset again from @@session.tx_isolation, as described + be reset again from @@session.transaction_isolation, as described above. */ enum_tx_isolation tx_isolation; /* Current or next transaction access mode. - See comment above regarding tx_isolation. + See comment above regarding transaction_isolation. */ bool tx_read_only; enum_check_fields count_cuted_fields; diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index 8a24d2febad..2b175823ad5 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -443,7 +443,7 @@ bool sp_create_assignment_instr(THD *thd, bool no_lookahead, - This query: SET TRANSACTION READ ONLY, ISOLATION LEVEL SERIALIZABLE; in translated to: - SET tx_read_only=1, tx_isolation=ISO_SERIALIZABLE; + SET transaction_read_only=1, transaction_isolation=ISO_SERIALIZABLE; but produces a single sp_create_assignment_instr() call which includes the query fragment covering both options. */ diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index 49655f8f0ce..ccce38a4c60 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -16764,7 +16764,7 @@ transaction_access_mode: MYSQL_YYABORT; set_var *var= (new (thd->mem_root) set_var(thd, lex->option_type, - find_sys_var(thd, "tx_read_only"), + find_sys_var(thd, "transaction_read_only"), &null_clex_str, item)); if (unlikely(var == NULL)) @@ -16783,7 +16783,7 @@ isolation_level: MYSQL_YYABORT; set_var *var= (new (thd->mem_root) set_var(thd, lex->option_type, - find_sys_var(thd, "tx_isolation"), + find_sys_var(thd, "transaction_isolation"), &null_clex_str, item)); if (unlikely(var == NULL) || diff --git a/sql/sys_vars.cc b/sql/sys_vars.cc index 008339b535b..541a3658d99 100644 --- a/sql/sys_vars.cc +++ b/sql/sys_vars.cc @@ -4191,7 +4191,7 @@ Sys_threadpool_dedicated_listener( #endif /* HAVE_POOL_OF_THREADS */ /** - Can't change the 'next' tx_isolation if we are already in a + Can't change the 'next' transaction_isolation if we are already in a transaction. */ @@ -4208,14 +4208,22 @@ static bool check_tx_isolation(sys_var *self, THD *thd, set_var *var) // NO_CMD_LINE - different name of the option static Sys_var_tx_isolation Sys_tx_isolation( - "tx_isolation", "Default transaction isolation level", - NO_SET_STMT SESSION_VAR(tx_isolation), NO_CMD_LINE, + "tx_isolation", "Default transaction isolation level." + "This variable is deprecated and will be removed in a future release.", + SESSION_VAR(tx_isolation), NO_CMD_LINE, + tx_isolation_names, DEFAULT(ISO_REPEATABLE_READ), + NO_MUTEX_GUARD, NOT_IN_BINLOG, ON_CHECK(check_tx_isolation), + ON_UPDATE(0), DEPRECATED("'@@transaction_isolation'")); // since 11.1.0 + +static Sys_var_tx_isolation Sys_transaction_isolation( + "transaction_isolation", "Default transaction isolation level", + SESSION_VAR(tx_isolation), NO_CMD_LINE, tx_isolation_names, DEFAULT(ISO_REPEATABLE_READ), NO_MUTEX_GUARD, NOT_IN_BINLOG, ON_CHECK(check_tx_isolation)); /** - Can't change the tx_read_only state if we are already in a + Can't change the transaction_read_only state if we are already in a transaction. */ @@ -4255,11 +4263,21 @@ bool Sys_var_tx_read_only::session_update(THD *thd, set_var *var) return false; } - +// NO_CMD_LINE - different name of the option static Sys_var_tx_read_only Sys_tx_read_only( "tx_read_only", "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.", + SESSION_VAR(tx_read_only), NO_CMD_LINE, DEFAULT(0), + NO_MUTEX_GUARD, NOT_IN_BINLOG, ON_CHECK(check_tx_read_only), + ON_UPDATE(0), DEPRECATED("'@@transaction_read_only'")); // since 11.1.0 + +static Sys_var_tx_read_only Sys_transaction_read_only( + "transaction_read_only", "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.", SESSION_VAR(tx_read_only), NO_CMD_LINE, DEFAULT(0), NO_MUTEX_GUARD, NOT_IN_BINLOG, ON_CHECK(check_tx_read_only)); diff --git a/sql/sys_vars.inl b/sql/sys_vars.inl index 2c5acdcdc6b..97ba03c84ab 100644 --- a/sql/sys_vars.inl +++ b/sql/sys_vars.inl @@ -2402,9 +2402,12 @@ public: CMD_LINE getopt, const char *values[], uint def_val, PolyLock *lock, enum binlog_status_enum binlog_status_arg, - on_check_function on_check_func) + on_check_function on_check_func, + on_update_function on_update_func=0, + const char *substitute=0) :Sys_var_enum(name_arg, comment, flag_args, off, size, getopt, - values, def_val, lock, binlog_status_arg, on_check_func) + values, def_val, lock, binlog_status_arg, on_check_func, + on_update_func, substitute) {} bool session_update(THD *thd, set_var *var) { @@ -2448,7 +2451,7 @@ public: /** - Class representing the tx_read_only system variable for setting + Class representing the transaction_read_only system variable for setting default transaction access mode. Note that there is a special syntax - SET TRANSACTION READ ONLY @@ -2463,9 +2466,12 @@ public: ptrdiff_t off, size_t size, CMD_LINE getopt, my_bool def_val, PolyLock *lock, enum binlog_status_enum binlog_status_arg, - on_check_function on_check_func) + on_check_function on_check_func, + on_update_function on_update_func=0, + const char *substitute=0) :Sys_var_mybool(name_arg, comment, flag_args, off, size, getopt, - def_val, lock, binlog_status_arg, on_check_func) + def_val, lock, binlog_status_arg, on_check_func, + on_update_func, substitute) {} virtual bool session_update(THD *thd, set_var *var); }; diff --git a/sql/transaction.cc b/sql/transaction.cc index 123f9283d23..94c3ec1d1e1 100644 --- a/sql/transaction.cc +++ b/sql/transaction.cc @@ -181,7 +181,7 @@ bool trans_begin(THD *thd, uint flags) } thd->tx_read_only= false; /* - This flags that tx_read_only was set explicitly, rather than + This flags that transaction_read_only was set explicitly, rather than just from the session's default. */ #ifndef EMBEDDED_LIBRARY diff --git a/storage/rocksdb/mysql-test/rocksdb_stress/t/load_generator.py b/storage/rocksdb/mysql-test/rocksdb_stress/t/load_generator.py index 91dd9e90df8..eef50010d8c 100644 --- a/storage/rocksdb/mysql-test/rocksdb_stress/t/load_generator.py +++ b/storage/rocksdb/mysql-test/rocksdb_stress/t/load_generator.py @@ -230,14 +230,14 @@ class WorkerThread(threading.Thread): return self.con is None def get_isolation_level(self): - execute(self.cur, "SELECT @@SESSION.tx_isolation") + execute(self.cur, "SELECT @@SESSION.transaction_isolation") if self.cur.rowcount != 1: - raise TestError("Unable to retrieve tx_isolation") + raise TestError("Unable to retrieve transaction_isolation") return self.cur.fetchone()[0] def set_isolation_level(self, isolation_level, persist = False): if isolation_level is not None: - execute(self.cur, "SET @@SESSION.tx_isolation = '%s'" % isolation_level) + execute(self.cur, "SET @@SESSION.transaction_isolation = '%s'" % isolation_level) if self.cur.rowcount != 0: raise TestError("Unable to set the isolation level to %s") |