Debian Codegraph @

mysql-test/suite/sys_vars/t/sql_safe_updates_func.test

raw
1 ############# mysql-test\t\sql_safe_updates_func.test #####################
2 # #
3 # Variable Name: sql_safe_updates #
4 # Scope: SESSION #
5 # Access Type: Dynamic #
6 # Data Type: BOOLEAN #
7 # Default Value: 1 TRUE #
8 # Values: 1 TRUE, 0 FALSE #
9 # #
10 # #
11 # Creation Date: 2008-02-25 #
12 # Author: Sharique Abdullah #
13 # #
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 #
17 # #
18 # Reference: http://dev.mysql.com/doc/refman/5.1/en/set-option.html #
19 # #
20 ###########################################################################
21
22 --echo ** Setup **
23 --echo
24 #
25 # Setup
26 #
27
28 SET @default_sql_safe_updates = @@sql_safe_updates;
29
30 #
31 # Creating tables
32 #
33
34 CREATE TEMPORARY TABLE t1(a int PRIMARY KEY, b varchar(20));
35
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');
45
46
47
48 --echo '#-----------------------------FN_DYNVARS_164_01------------------------------------#'
49 --echo
50 --echo Value ON
51 --echo
52 #
53 # Value ON
54 #
55
56 SET SESSION sql_safe_updates = ON;
57
58 --echo '#-----------------------------FN_DYNVARS_164_02------------------------------------#'
59 #
60 # Without WHERE Clause
61 #
62 --echo Expected error : Update without key in safe mode
63 --error ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE
64 DELETE FROM t1;
65 SELECT * FROM t1;
66
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
70 EXECUTE stmt;
71 DEALLOCATE PREPARE stmt;
72
73 --echo '#-----------------------------FN_DYNVARS_164_03------------------------------------#'
74 #
75 # With a LIMIT Clause
76 #
77 --echo No error : Update without key in safe mode but with LIMIT
78 DELETE FROM t1 LIMIT 2;
79 SELECT * FROM t1;
80
81 --echo '#-----------------------------FN_DYNVARS_164_04------------------------------------#'
82 #
83 # With a no key WHERE Clause
84 #
85 --echo Expected error : Update without key in safe mode
86 --error ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE
87 DELETE FROM t1 WHERE b='val1';
88 SELECT * FROM t1;
89
90 --echo '#-----------------------------FN_DYNVARS_164_05------------------------------------#'
91 #
92 # With a key WHERE Clause
93 #
94 --echo This one should work
95 DELETE FROM t1 WHERE a=3;
96 SELECT * FROM t1;
97
98 --echo
99 --echo Value OFF
100 --echo
101 #
102 # Value OFF
103 #
104
105 SET SESSION sql_safe_updates = OFF;
106
107 --echo '#-----------------------------FN_DYNVARS_164_06------------------------------------#'
108 #
109 # With a no key WHERE Clause
110 #
111 --echo This one should work
112 DELETE FROM t1 WHERE b='val1';
113 SELECT * FROM t1;
114
115 --echo '#-----------------------------FN_DYNVARS_164_07------------------------------------#'
116 #
117 # With a key WHERE Clause
118 #
119 --echo This one should work
120 DELETE FROM t1 WHERE a=5;
121 SELECT * FROM t1;
122
123 --echo '#-----------------------------FN_DYNVARS_164_08------------------------------------#'
124 #
125 # With a LIMIT Clause
126 #
127 --echo This one should work
128 DELETE FROM t1 LIMIT 2;
129 SELECT * FROM t1;
130
131 --echo '#-----------------------------FN_DYNVARS_164_09------------------------------------#'
132 #
133 # Without WHERE Clause
134 #
135 --echo This one should work
136 --echo DELETE FROM t1;
137 --error ER_MIXING_NOT_ALLOWED
138 #DELETE FROM t1;
139 --echo Bug#35392 Unexpected error occurs in this statement Can't change size of the file
140 --echo statement is remarked because the error is uncatchable by testing framework
141 SELECT * FROM t1;
142
143 PREPARE stmt FROM 'DELETE FROM t1;';
144 --echo Expected error : Update without key in safe mode
145 --error ER_MIXING_NOT_ALLOWED
146 #EXECUTE stmt;
147 --echo Bug#35392 Unexpected error occurs in this statement Can't change size of the file
148 --echo statement is remarked because the error is uncatchable by testing framework
149 DEALLOCATE PREPARE stmt;
150
151
152 --echo '#----------------------------FN_DYNVARS_164_10--------------------------------------#'
153 #
154 # Session data integrity check
155 #
156
157 --echo ** Connecting con_int1 using root **
158 connect (con_int1,localhost,root,,);
159
160 --echo ** Connection con_int1 **
161 connection con_int1;
162 SELECT @@SESSION.sql_safe_updates;
163 --echo 0 / FALSE Expected
164
165 SET SESSION sql_safe_updates = FALSE;
166
167 --echo ** Connecting con_int2 using root **
168 connect (con_int2,localhost,root,,);
169
170 --echo ** Connection con_int2 **
171 connection con_int2;
172 SELECT @@SESSION.sql_safe_updates;
173 --echo 0 / FALSE Expected
174
175 SET SESSION sql_safe_updates = TRUE;
176
177 --echo ** Connection con_int2 **
178 connection con_int2;
179 SELECT @@SESSION.sql_safe_updates;
180 --echo 1 / TRUE Expected
181
182 --echo ** Connection con_int1 **
183 connection con_int1;
184 SELECT @@SESSION.sql_safe_updates;
185 --echo 0 / FALSE Expected
186
187 --echo ** Connection default **
188 connection default;
189
190 --echo Disconnecting Connections con_int1, con_int2
191 disconnect con_int1;
192 disconnect con_int2;
193
194
195 #
196 # Cleanup
197 #
198
199 SET SESSION sql_safe_updates = @default_sql_safe_updates;
200
201 DROP TABLE t1;