1
############## mysql-test\t\max_allowed_packet_basic.test ###############
3
# Variable Name: max_allowed_packet #
4
# Scope: GLOBAL | SESSION #
5
# Access Type: Dynamic #
7
# Default Value:4194304 #
8
# Range:1024-1073741824 #
11
# Creation Date: 2008-02-07 #
14
# Description: Test Cases of Dynamic System Variable max_allowed_packet #
15
# that checks the behavior of this variable in the following ways#
17
# * Valid & Invalid values #
18
# * Scope & Access method #
21
# Reference: http://dev.mysql.com/doc/refman/5.1/en/ #
22
# server-system-variables.html #
24
###############################################################################
26
--source include/load_sysvars.inc
28
##################################################################
29
# START OF max_allowed_packet TESTS #
30
##################################################################
33
#############################################################
34
# Save initial value #
35
#############################################################
37
SET @start_global_value = @@global.max_allowed_packet;
38
SELECT @start_global_value;
40
# give a known value to @@session.max_allowed_packet by assigning to
41
# @@global and setting up a new connection (for deterministic result
43
SET @@global.max_allowed_packet = DEFAULT;
44
connect (conn1, localhost, root,,);
48
--echo '#--------------------FN_DYNVARS_070_01-------------------------#'
49
##################################################################
50
# Display the DEFAULT value of max_allowed_packet #
51
##################################################################
53
SET @@global.max_allowed_packet = 1000;
54
SET @@global.max_allowed_packet = DEFAULT;
55
SELECT @@global.max_allowed_packet;
57
--Error ER_VARIABLE_IS_READONLY
58
SET @@session.max_allowed_packet = 20000;
59
--Error ER_VARIABLE_IS_READONLY
60
SET @@session.max_allowed_packet = DEFAULT;
61
SELECT @@session.max_allowed_packet;
64
--echo '#--------------------FN_DYNVARS_070_02-------------------------#'
65
##################################################################
66
# Check the DEFAULT value of max_allowed_packet #
67
##################################################################
69
SET @@global.max_allowed_packet = DEFAULT;
70
SELECT @@global.max_allowed_packet = 4194304;
73
--echo '#--------------------FN_DYNVARS_070_03-------------------------#'
74
############################################################################
75
# Change the value of max_allowed_packet to a valid value for GLOBAL Scope #
76
############################################################################
78
SET @@global.max_allowed_packet = 1024;
79
SELECT @@global.max_allowed_packet;
80
SET @@global.max_allowed_packet = 1025;
81
SELECT @@global.max_allowed_packet;
82
SET @@global.max_allowed_packet = 1073741824;
83
SELECT @@global.max_allowed_packet;
84
SET @@global.max_allowed_packet = 1073741823;
85
SELECT @@global.max_allowed_packet;
87
--echo '#--------------------FN_DYNVARS_070_04-------------------------#'
88
#############################################################################
89
# Change the value of max_allowed_packet to a valid value for SESSION Scope #
90
#############################################################################
92
--Error ER_VARIABLE_IS_READONLY
93
SET @@session.max_allowed_packet = 1024;
94
SELECT @@session.max_allowed_packet;
95
--Error ER_VARIABLE_IS_READONLY
96
SET @@session.max_allowed_packet = 1025;
97
SELECT @@session.max_allowed_packet;
98
--Error ER_VARIABLE_IS_READONLY
99
SET @@session.max_allowed_packet = 65535;
100
SELECT @@session.max_allowed_packet;
101
--Error ER_VARIABLE_IS_READONLY
102
SET @@session.max_allowed_packet = 1073741824;
103
SELECT @@session.max_allowed_packet;
104
--Error ER_VARIABLE_IS_READONLY
105
SET @@session.max_allowed_packet = 1073741823;
106
SELECT @@session.max_allowed_packet;
109
--echo '#------------------FN_DYNVARS_070_05-----------------------#'
110
##############################################################
111
# Change the value of max_allowed_packet to an invalid value #
112
##############################################################
114
SET @@global.max_allowed_packet = 0;
115
SELECT @@global.max_allowed_packet;
116
SET @@global.max_allowed_packet = -1024;
117
SELECT @@global.max_allowed_packet;
118
SET @@global.max_allowed_packet = 1023;
119
SELECT @@global.max_allowed_packet;
120
SET @@global.max_allowed_packet = 10737418241;
121
SELECT @@global.max_allowed_packet;
122
--Error ER_WRONG_TYPE_FOR_VAR
123
SET @@global.max_allowed_packet = 65530.34;
124
SELECT @@global.max_allowed_packet;
125
--Error ER_WRONG_TYPE_FOR_VAR
126
SET @@global.max_allowed_packet = test;
127
SELECT @@global.max_allowed_packet;
129
--Error ER_VARIABLE_IS_READONLY
130
SET @@session.max_allowed_packet = 0;
131
SELECT @@session.max_allowed_packet;
132
--Error ER_VARIABLE_IS_READONLY
133
SET @@session.max_allowed_packet = 1023;
134
SELECT @@session.max_allowed_packet;
135
--Error ER_VARIABLE_IS_READONLY
136
SET @@session.max_allowed_packet = -2;
137
SELECT @@session.max_allowed_packet;
138
--Error ER_WRONG_TYPE_FOR_VAR
139
SET @@session.max_allowed_packet = 65530.34;
140
--Error ER_VARIABLE_IS_READONLY
141
SET @@session.max_allowed_packet = 10737418241;
142
SELECT @@session.max_allowed_packet;
144
--Error ER_WRONG_TYPE_FOR_VAR
145
SET @@session.max_allowed_packet = test;
146
SELECT @@session.max_allowed_packet;
149
--echo '#------------------FN_DYNVARS_070_06-----------------------#'
150
####################################################################
151
# Check if the value in GLOBAL Table matches value in variable #
152
####################################################################
155
SELECT @@global.max_allowed_packet = VARIABLE_VALUE
156
FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES
157
WHERE VARIABLE_NAME='max_allowed_packet';
159
--echo '#------------------FN_DYNVARS_070_07-----------------------#'
160
####################################################################
161
# Check if the value in SESSION Table matches value in variable #
162
####################################################################
164
SELECT @@session.max_allowed_packet = VARIABLE_VALUE
165
FROM INFORMATION_SCHEMA.SESSION_VARIABLES
166
WHERE VARIABLE_NAME='max_allowed_packet';
169
--echo '#------------------FN_DYNVARS_070_08-----------------------#'
170
####################################################################
171
# Check if TRUE and FALSE values can be used on variable #
172
####################################################################
174
SET @@global.max_allowed_packet = TRUE;
175
SELECT @@global.max_allowed_packet;
176
SET @@global.max_allowed_packet = FALSE;
177
SELECT @@global.max_allowed_packet;
180
--echo '#---------------------FN_DYNVARS_070_09----------------------#'
181
#################################################################################
182
# Check if accessing variable with and without GLOBAL point to same variable #
183
#################################################################################
185
SET @@global.max_allowed_packet = 2048;
186
SELECT @@max_allowed_packet = @@global.max_allowed_packet;
189
--echo '#---------------------FN_DYNVARS_070_10----------------------#'
190
########################################################################################################
191
# Check if accessing variable with SESSION,LOCAL and without SCOPE points to same session variable #
192
########################################################################################################
194
--Error ER_VARIABLE_IS_READONLY
195
SET @@max_allowed_packet = 100000;
196
SELECT @@max_allowed_packet = @@local.max_allowed_packet;
197
SELECT @@local.max_allowed_packet = @@session.max_allowed_packet;
200
--echo '#---------------------FN_DYNVARS_070_11----------------------#'
201
#############################################################################
202
# Check if max_allowed_packet can be accessed with and without @@ sign #
203
#############################################################################
205
--Error ER_VARIABLE_IS_READONLY
206
SET max_allowed_packet = 1024;
207
SELECT @@max_allowed_packet;
208
--Error ER_UNKNOWN_TABLE
209
SELECT local.max_allowed_packet;
210
--Error ER_UNKNOWN_TABLE
211
SELECT session.max_allowed_packet;
212
--Error ER_BAD_FIELD_ERROR
213
SELECT max_allowed_packet = @@session.max_allowed_packet;
216
####################################
217
# Restore initial value #
218
####################################
221
SET @@global.max_allowed_packet = @start_global_value;
222
SELECT @@global.max_allowed_packet;
223
SELECT @@session.max_allowed_packet;
226
#######################################################
227
# END OF max_allowed_packet TESTS #
228
#######################################################