1
############# mysql-test\t\sql_safe_updates_func.test #####################
3
# Variable Name: sql_safe_updates #
5
# Access Type: Dynamic #
7
# Default Value: 1 TRUE #
8
# Values: 1 TRUE, 0 FALSE #
11
# Creation Date: 2008-02-25 #
12
# Author: Sharique Abdullah #
14
# Description: Test Cases of Dynamic System Variable "sql_safe_updates" #
15
# that checks behavior of this variable in the following ways#
16
# * Functionality based on different values #
18
# Reference: http://dev.mysql.com/doc/refman/5.1/en/set-option.html #
20
###########################################################################
28
SET @default_sql_safe_updates = @@sql_safe_updates;
34
CREATE TEMPORARY TABLE t1(a int PRIMARY KEY, b varchar(20));
36
INSERT INTO t1 VALUES(1, 'val1');
37
INSERT INTO t1 VALUES(2, 'val2');
38
INSERT INTO t1 VALUES(3, 'val3');
39
INSERT INTO t1 VALUES(4, 'val4');
40
INSERT INTO t1 VALUES(5, 'val5');
41
INSERT INTO t1 VALUES(6, 'val6');
42
INSERT INTO t1 VALUES(7, 'val7');
43
INSERT INTO t1 VALUES(8, 'val8');
44
INSERT INTO t1 VALUES(9, 'val9');
48
--echo '#-----------------------------FN_DYNVARS_164_01------------------------------------#'
56
SET SESSION sql_safe_updates = ON;
58
--echo '#-----------------------------FN_DYNVARS_164_02------------------------------------#'
60
# Without WHERE Clause
62
--echo Expected error : Update without key in safe mode
63
--error ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE
67
PREPARE stmt FROM 'DELETE FROM t1;';
68
--echo Expected error : Update without key in safe mode
69
--error ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE
71
DEALLOCATE PREPARE stmt;
73
--echo '#-----------------------------FN_DYNVARS_164_03------------------------------------#'
77
--echo Expected error : Update without key in safe mode
78
--error ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE
79
DELETE FROM t1 LIMIT 2;
82
--echo '#-----------------------------FN_DYNVARS_164_04------------------------------------#'
84
# With a no key WHERE Clause
86
--echo Expected error : Update without key in safe mode
87
--error ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE
88
DELETE FROM t1 WHERE b='val1';
91
--echo '#-----------------------------FN_DYNVARS_164_05------------------------------------#'
93
# With a key WHERE Clause
95
--echo This one should work
96
DELETE FROM t1 WHERE a=1;
106
SET SESSION sql_safe_updates = OFF;
108
--echo '#-----------------------------FN_DYNVARS_164_06------------------------------------#'
110
# With a no key WHERE Clause
112
--echo This one should work
113
DELETE FROM t1 WHERE b='val1';
116
--echo '#-----------------------------FN_DYNVARS_164_07------------------------------------#'
118
# With a key WHERE Clause
120
--echo This one should work
121
DELETE FROM t1 WHERE a=1;
124
--echo '#-----------------------------FN_DYNVARS_164_08------------------------------------#'
126
# With a LIMIT Clause
128
--echo This one should work
129
DELETE FROM t1 LIMIT 2;
132
--echo '#-----------------------------FN_DYNVARS_164_09------------------------------------#'
134
# Without WHERE Clause
136
--echo This one should work
137
--echo DELETE FROM t1;
138
--error ER_MIXING_NOT_ALLOWED
140
--echo Bug#35392 Unexpected error occurs in this statement Can't change size of the file
141
--echo statement is remarked because the error is uncatchable by testing framework
144
PREPARE stmt FROM 'DELETE FROM t1;';
145
--echo Expected error : Update without key in safe mode
146
--error ER_MIXING_NOT_ALLOWED
148
--echo Bug#35392 Unexpected error occurs in this statement Can't change size of the file
149
--echo statement is remarked because the error is uncatchable by testing framework
150
DEALLOCATE PREPARE stmt;
153
--echo '#----------------------------FN_DYNVARS_164_10--------------------------------------#'
155
# Session data integrity check
158
--echo ** Connecting con_int1 using root **
159
connect (con_int1,localhost,root,,);
161
--echo ** Connection con_int1 **
163
SELECT @@SESSION.sql_safe_updates;
164
--echo 0 / FALSE Expected
166
SET SESSION sql_safe_updates = FALSE;
168
--echo ** Connecting con_int2 using root **
169
connect (con_int2,localhost,root,,);
171
--echo ** Connection con_int2 **
173
SELECT @@SESSION.sql_safe_updates;
174
--echo 0 / FALSE Expected
176
SET SESSION sql_safe_updates = TRUE;
178
--echo ** Connection con_int2 **
180
SELECT @@SESSION.sql_safe_updates;
181
--echo 1 / TRUE Expected
183
--echo ** Connection con_int1 **
185
SELECT @@SESSION.sql_safe_updates;
186
--echo 0 / FALSE Expected
188
--echo ** Connection default **
191
--echo Disconnecting Connections con_int1, con_int2
200
SET SESSION sql_safe_updates = @default_sql_safe_updates;