1#################################################################### 2# START OF tx_read_only TESTS # 3#################################################################### 4############################################################# 5# Save initial value # 6############################################################# 7SET @start_global_value = @@global.tx_read_only; 8SELECT @start_global_value; 9@start_global_value 100 11SET @start_session_value = @@session.tx_read_only; 12SELECT @start_session_value; 13@start_session_value 140 15######################################################################## 16# Display the DEFAULT value of tx_read_only # 17######################################################################## 18SET @@global.tx_read_only = ON; 19SET @@global.tx_read_only = DEFAULT; 20SELECT @@global.tx_read_only; 21@@global.tx_read_only 220 23SET @@session.tx_read_only = ON; 24SET @@session.tx_read_only = DEFAULT; 25SELECT @@session.tx_read_only; 26@@session.tx_read_only 270 28############################################################################## 29# Change the value of tx_read_only to a valid value for GLOBAL Scope # 30############################################################################## 31SET @@global.tx_read_only = ON; 32SELECT @@global.tx_read_only; 33@@global.tx_read_only 341 35SET @@global.tx_read_only = OFF; 36SELECT @@global.tx_read_only; 37@@global.tx_read_only 380 39SET @@global.tx_read_only = 0; 40SELECT @@global.tx_read_only; 41@@global.tx_read_only 420 43SET @@global.tx_read_only = 1; 44SELECT @@global.tx_read_only; 45@@global.tx_read_only 461 47SET @@global.tx_read_only = TRUE; 48SELECT @@global.tx_read_only; 49@@global.tx_read_only 501 51SET @@global.tx_read_only = FALSE; 52SELECT @@global.tx_read_only; 53@@global.tx_read_only 540 55############################################################################### 56# Change the value of tx_read_only to a valid value for SESSION Scope # 57############################################################################### 58SET @@session.tx_read_only = ON; 59SELECT @@session.tx_read_only; 60@@session.tx_read_only 611 62SET @@session.tx_read_only = OFF; 63SELECT @@session.tx_read_only; 64@@session.tx_read_only 650 66SET @@session.tx_read_only = 0; 67SELECT @@session.tx_read_only; 68@@session.tx_read_only 690 70SET @@session.tx_read_only = 1; 71SELECT @@session.tx_read_only; 72@@session.tx_read_only 731 74SET @@session.tx_read_only = TRUE; 75SELECT @@session.tx_read_only; 76@@session.tx_read_only 771 78SET @@session.tx_read_only = FALSE; 79SELECT @@session.tx_read_only; 80@@session.tx_read_only 810 82################################################################ 83# Change the value of tx_read_only to an invalid value # 84################################################################ 85SET @@global.tx_read_only = 'ONN'; 86ERROR 42000: Variable 'tx_read_only' can't be set to the value of 'ONN' 87SET @@global.tx_read_only = "OFFF"; 88ERROR 42000: Variable 'tx_read_only' can't be set to the value of 'OFFF' 89SET @@global.tx_read_only = TTRUE; 90ERROR 42000: Variable 'tx_read_only' can't be set to the value of 'TTRUE' 91SET @@global.tx_read_only = FELSE; 92ERROR 42000: Variable 'tx_read_only' can't be set to the value of 'FELSE' 93SET @@global.tx_read_only = -1024; 94ERROR 42000: Variable 'tx_read_only' can't be set to the value of '-1024' 95SET @@global.tx_read_only = 65536; 96ERROR 42000: Variable 'tx_read_only' can't be set to the value of '65536' 97SET @@global.tx_read_only = 65530.34; 98ERROR 42000: Incorrect argument type to variable 'tx_read_only' 99SET @@global.tx_read_only = test; 100ERROR 42000: Variable 'tx_read_only' can't be set to the value of 'test' 101SET @@session.tx_read_only = ONN; 102ERROR 42000: Variable 'tx_read_only' can't be set to the value of 'ONN' 103SET @@session.tx_read_only = ONF; 104ERROR 42000: Variable 'tx_read_only' can't be set to the value of 'ONF' 105SET @@session.tx_read_only = OF; 106ERROR 42000: Variable 'tx_read_only' can't be set to the value of 'OF' 107SET @@session.tx_read_only = 'OFN'; 108ERROR 42000: Variable 'tx_read_only' can't be set to the value of 'OFN' 109SET @@session.tx_read_only = -2; 110ERROR 42000: Variable 'tx_read_only' can't be set to the value of '-2' 111SET @@session.tx_read_only = 65530.34; 112ERROR 42000: Incorrect argument type to variable 'tx_read_only' 113SET @@session.tx_read_only = 65550; 114ERROR 42000: Variable 'tx_read_only' can't be set to the value of '65550' 115SET @@session.tx_read_only = test; 116ERROR 42000: Variable 'tx_read_only' can't be set to the value of 'test' 117SELECT @@session.tx_read_only; 118@@session.tx_read_only 1190 120#################################################################### 121# Check if the value in GLOBAL Table matches value in variable # 122#################################################################### 123SELECT IF(@@global.tx_read_only, "ON", "OFF") = VARIABLE_VALUE 124FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES 125WHERE VARIABLE_NAME='tx_read_only'; 126IF(@@global.tx_read_only, "ON", "OFF") = VARIABLE_VALUE 1271 128#################################################################### 129# Check if the value in SESSION Table matches value in variable # 130#################################################################### 131SELECT IF(@@session.tx_read_only, "ON", "OFF") = VARIABLE_VALUE 132FROM INFORMATION_SCHEMA.SESSION_VARIABLES 133WHERE VARIABLE_NAME='tx_read_only'; 134IF(@@session.tx_read_only, "ON", "OFF") = VARIABLE_VALUE 1351 136############################################################################### 137# Check if accessing variable with and without GLOBAL point to same variable # 138############################################################################### 139SET @@tx_read_only = OFF; 140SET @@global.tx_read_only = ON; 141SELECT @@tx_read_only = @@global.tx_read_only; 142@@tx_read_only = @@global.tx_read_only 1430 144############################################################################## 145# Check if accessing variable with SESSION,LOCAL and without SCOPE points # 146# to same session variable # 147############################################################################## 148SET @@tx_read_only = ON; 149SELECT @@tx_read_only = @@local.tx_read_only; 150@@tx_read_only = @@local.tx_read_only 1511 152SELECT @@local.tx_read_only = @@session.tx_read_only; 153@@local.tx_read_only = @@session.tx_read_only 1541 155############################################################################### 156# Check if tx_read_only can be accessed with and without @@ sign # 157############################################################################### 158# @@session is synonym for SESSION 159SET @@session.tx_read_only= 0; 160# Without modifier, SET changes session variable 161SET tx_read_only = 1; 162SELECT @@tx_read_only; 163@@tx_read_only 1641 165# name1.name2 refers to database_name.table_name 166SELECT session.tx_read_only; 167ERROR 42S02: Unknown table 'session' in field list 168#################################### 169# Restore initial value # 170#################################### 171SET @@global.tx_read_only = @start_global_value; 172SELECT @@global.tx_read_only; 173@@global.tx_read_only 1740 175SET @@session.tx_read_only = @start_session_value; 176SELECT @@session.tx_read_only; 177@@session.tx_read_only 1780 179######################################################### 180# END OF tx_read_only TESTS # 181######################################################### 182