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 Expected error : Update without key in safe mode
78 --error ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE
79 DELETE FROM t1 LIMIT 2;
80 SELECT * FROM t1;
81
82 --echo '#-----------------------------FN_DYNVARS_164_04------------------------------------#'
83 #
84 # With a no key WHERE Clause
85 #
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';
89 SELECT * FROM t1;
90
91 --echo '#-----------------------------FN_DYNVARS_164_05------------------------------------#'
92 #
93 # With a key WHERE Clause
94 #
95 --echo This one should work
96 DELETE FROM t1 WHERE a=1;
97 SELECT * FROM t1;
98
99 --echo
100 --echo Value OFF
101 --echo
102 #
103 # Value OFF
104 #
105
106 SET SESSION sql_safe_updates = OFF;
107
108 --echo '#-----------------------------FN_DYNVARS_164_06------------------------------------#'
109 #
110 # With a no key WHERE Clause
111 #
112 --echo This one should work
113 DELETE FROM t1 WHERE b='val1';
114 SELECT * FROM t1;
115
116 --echo '#-----------------------------FN_DYNVARS_164_07------------------------------------#'
117 #
118 # With a key WHERE Clause
119 #
120 --echo This one should work
121 DELETE FROM t1 WHERE a=1;
122 SELECT * FROM t1;
123
124 --echo '#-----------------------------FN_DYNVARS_164_08------------------------------------#'
125 #
126 # With a LIMIT Clause
127 #
128 --echo This one should work
129 DELETE FROM t1 LIMIT 2;
130 SELECT * FROM t1;
131
132 --echo '#-----------------------------FN_DYNVARS_164_09------------------------------------#'
133 #
134 # Without WHERE Clause
135 #
136 --echo This one should work
137 --echo DELETE FROM t1;
138 --error ER_MIXING_NOT_ALLOWED
139 #DELETE FROM t1;
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
142 SELECT * FROM t1;
143
144 PREPARE stmt FROM 'DELETE FROM t1;';
145 --echo Expected error : Update without key in safe mode
146 --error ER_MIXING_NOT_ALLOWED
147 #EXECUTE stmt;
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;
151
152
153 --echo '#----------------------------FN_DYNVARS_164_10--------------------------------------#'
154 #
155 # Session data integrity check
156 #
157
158 connect (con_int1,localhost,root,,);
159
160 connection con_int1;
161 SELECT @@SESSION.sql_safe_updates;
162 --echo 0 / FALSE Expected
163
164 SET SESSION sql_safe_updates = FALSE;
165
166 connect (con_int2,localhost,root,,);
167
168 connection con_int2;
169 SELECT @@SESSION.sql_safe_updates;
170 --echo 0 / FALSE Expected
171
172 SET SESSION sql_safe_updates = TRUE;
173
174 connection con_int2;
175 SELECT @@SESSION.sql_safe_updates;
176 --echo 1 / TRUE Expected
177
178 connection con_int1;
179 SELECT @@SESSION.sql_safe_updates;
180 --echo 0 / FALSE Expected
181
182 connection default;
183
184 disconnect con_int1;
185 disconnect con_int2;
186
187
188 #
189 # Cleanup
190 #
191
192 SET SESSION sql_safe_updates = @default_sql_safe_updates;
193
194 DROP TABLE t1;