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