diff options
Diffstat (limited to 'mysql-test/suite/sys_vars')
14 files changed, 784 insertions, 535 deletions
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; |