1# 2# WL#9636: Rename tx_{read_only,isolation} variables to transaction_* 3# 4######################################################################### 5# Saving initial value of transaction_isolation in a temporary variable # 6######################################################################### 7SET @global_start_value = @@global.transaction_isolation; 8SELECT @global_start_value; 9@global_start_value 10REPEATABLE-READ 11SET @session_start_value = @@session.transaction_isolation; 12SELECT @session_start_value; 13@session_start_value 14REPEATABLE-READ 15##################################################################### 16# Display the DEFAULT value of transaction_isolation # 17##################################################################### 18SET @@global.transaction_isolation = 'READ-UNCOMMITTED'; 19SET @@global.transaction_isolation = DEFAULT; 20SELECT @@global.transaction_isolation; 21@@global.transaction_isolation 22REPEATABLE-READ 23SET @@session.transaction_isolation = DEFAULT; 24SELECT @@session.transaction_isolation; 25@@session.transaction_isolation 26REPEATABLE-READ 27########################################################## 28# Check if NULL or empty value is accepeted # 29########################################################## 30SET @@global.transaction_isolation = NULL; 31ERROR 42000: Variable 'transaction_isolation' can't be set to the value of 'NULL' 32SET @@global.transaction_isolation = ''; 33ERROR 42000: Variable 'transaction_isolation' can't be set to the value of '' 34SET @@session.transaction_isolation = NULL; 35ERROR 42000: Variable 'transaction_isolation' can't be set to the value of 'NULL' 36SET @@session.transaction_isolation = ''; 37ERROR 42000: Variable 'transaction_isolation' can't be set to the value of '' 38##################################################################### 39# Change the value of transaction_isolation to a valid value # 40##################################################################### 41SET @@global.transaction_isolation = 'READ-UNCOMMITTED'; 42SELECT @@global.transaction_isolation; 43@@global.transaction_isolation 44READ-UNCOMMITTED 45SET @@global.transaction_isolation = 'read-COMMITTED'; 46SELECT @@global.transaction_isolation; 47@@global.transaction_isolation 48READ-COMMITTED 49SET @@global.transaction_isolation = 'REPEATABLE-READ'; 50SELECT @@global.transaction_isolation; 51@@global.transaction_isolation 52REPEATABLE-READ 53SET @@global.transaction_isolation = 'SERIALIZable'; 54SELECT @@global.transaction_isolation; 55@@global.transaction_isolation 56SERIALIZABLE 57SET @@session.transaction_isolation = 'READ-UNCOMMITTED'; 58SELECT @@session.transaction_isolation; 59@@session.transaction_isolation 60READ-UNCOMMITTED 61SET @@session.transaction_isolation = 'READ-COMMITTED'; 62SELECT @@session.transaction_isolation; 63@@session.transaction_isolation 64READ-COMMITTED 65SET @@session.transaction_isolation = 'REPEATABLE-READ'; 66SELECT @@session.transaction_isolation; 67@@session.transaction_isolation 68REPEATABLE-READ 69SET @@session.transaction_isolation = 'serializable'; 70SELECT @@session.transaction_isolation; 71@@session.transaction_isolation 72SERIALIZABLE 73####################################################################### 74# Change the value of transaction_isolation to invalid value # 75####################################################################### 76SET @@global.transaction_isolation = -1; 77ERROR 42000: Variable 'transaction_isolation' can't be set to the value of '-1' 78SET @@global.transaction_isolation = READUNCOMMITTED; 79ERROR 42000: Variable 'transaction_isolation' can't be set to the value of 'READUNCOMMITTED' 80SET @@global.transaction_isolation = 'REPEATABLE'; 81ERROR 42000: Variable 'transaction_isolation' can't be set to the value of 'REPEATABLE' 82SET @@global.transaction_isolation = OFF; 83ERROR 42000: Variable 'transaction_isolation' can't be set to the value of 'OFF' 84SET @@global.transaction_isolation = ON; 85ERROR 42000: Variable 'transaction_isolation' can't be set to the value of 'ON' 86SET @@global.transaction_isolation = 'NON-SERIALIZABLE'; 87ERROR 42000: Variable 'transaction_isolation' can't be set to the value of 'NON-SERIALIZABLE' 88SET @@transaction_isolation = -1; 89ERROR 42000: Variable 'transaction_isolation' can't be set to the value of '-1' 90SET @@transaction_isolation = READUNCOMMITTED; 91ERROR 42000: Variable 'transaction_isolation' can't be set to the value of 'READUNCOMMITTED' 92SET @@transaction_isolation = 'REPEATABLE'; 93ERROR 42000: Variable 'transaction_isolation' can't be set to the value of 'REPEATABLE' 94SET @@transaction_isolation = 'NONE'; 95ERROR 42000: Variable 'transaction_isolation' can't be set to the value of 'NONE' 96SET @@transaction_isolation = 'ALL'; 97ERROR 42000: Variable 'transaction_isolation' can't be set to the value of 'ALL' 98######################################################################### 99# Check if the value in session Table matches value in variable # 100######################################################################### 101SELECT @@session.transaction_isolation = VARIABLE_VALUE 102FROM INFORMATION_SCHEMA.SESSION_VARIABLES 103WHERE VARIABLE_NAME='transaction_isolation'; 104@@session.transaction_isolation = VARIABLE_VALUE 1051 106######################################################################### 107# Check if the value in GLOBAL Table matches value in variable # 108######################################################################### 109SELECT @@global.transaction_isolation = VARIABLE_VALUE 110FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES 111WHERE VARIABLE_NAME='transaction_isolation'; 112@@global.transaction_isolation = VARIABLE_VALUE 1131 114################################################################### 115# Check if numbers can be used on variable # 116################################################################### 117SET @@global.transaction_isolation = 0; 118SELECT @@global.transaction_isolation; 119@@global.transaction_isolation 120READ-UNCOMMITTED 121SET @@global.transaction_isolation = 1; 122SELECT @@global.transaction_isolation; 123@@global.transaction_isolation 124READ-COMMITTED 125SET @@global.transaction_isolation = 2; 126SELECT @@global.transaction_isolation; 127@@global.transaction_isolation 128REPEATABLE-READ 129SET @@global.transaction_isolation = 3; 130SELECT @@global.transaction_isolation; 131@@global.transaction_isolation 132SERIALIZABLE 133SET @@global.transaction_isolation = 0.4; 134ERROR 42000: Incorrect argument type to variable 'transaction_isolation' 135################################################################### 136# Check if TRUE and FALSE values can be used on variable # 137################################################################### 138SET @@global.transaction_isolation = TRUE; 139SELECT @@global.transaction_isolation; 140@@global.transaction_isolation 141READ-COMMITTED 142SET @@global.transaction_isolation = FALSE; 143SELECT @@global.transaction_isolation; 144@@global.transaction_isolation 145READ-UNCOMMITTED 146########################################################################## 147# Check if transaction_isolation can be accessed with and without @@ sign# 148########################################################################## 149SET transaction_isolation = 'REPEATABLE-READ'; 150SET session.transaction_isolation = 'REPEATABLE-READ'; 151ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'session.transaction_isolation = 'REPEATABLE-READ'' at line 1 152SET global.transaction_isolation = 'REPEATABLE-READ'; 153ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'global.transaction_isolation = 'REPEATABLE-READ'' at line 1 154SET session transaction_isolation = 'REPEATABLE-READ'; 155SELECT @@transaction_isolation; 156@@transaction_isolation 157REPEATABLE-READ 158SET global transaction_isolation = 'REPEATABLE-READ'; 159SELECT @@global.transaction_isolation; 160@@global.transaction_isolation 161REPEATABLE-READ 162############################## 163# Restore initial value # 164############################## 165SET @@global.transaction_isolation = @global_start_value; 166SELECT @@global.transaction_isolation; 167@@global.transaction_isolation 168REPEATABLE-READ 169SET @@session.transaction_isolation = @session_start_value; 170SELECT @@session.transaction_isolation; 171@@session.transaction_isolation 172REPEATABLE-READ 173#################################################################### 174# END OF transaction_isolation TESTS # 175#################################################################### 176