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