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