Debian Codegraph @

mysql-test/suite/funcs_1/r/memory_storedproc_08.result

raw
1 SET @@session.sql_mode = 'NO_ENGINE_SUBSTITUTION';
2
3 --source suite/funcs_1/storedproc/load_sp_tb.inc
4 --------------------------------------------------------------------------------
5 SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
6
7 --source suite/funcs_1/storedproc/cleanup_sp_tb.inc
8 --------------------------------------------------------------------------------
9 DROP DATABASE IF EXISTS db_storedproc;
10 DROP DATABASE IF EXISTS db_storedproc_1;
11 CREATE DATABASE db_storedproc charset utf8mb4;
12 CREATE DATABASE db_storedproc_1 charset utf8mb4;
13 USE db_storedproc;
14 create table t1(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int)
15 engine = <engine_to_be_tested>;
16 load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t1;
17 create table t2(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int)
18 engine = <engine_to_be_tested>;
19 load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t2;
20 create table t3(f1 char(20),f2 char(20),f3 integer) engine = <engine_to_be_tested>;
21 load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t3.txt' into table t3;
22 create table t4(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int)
23 engine = <engine_to_be_tested>;
24 load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t4;
25 USE db_storedproc_1;
26 create table t6(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int)
27 engine = <engine_to_be_tested>;
28 load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t6;
29 USE db_storedproc;
30 create table t7 (f1 char(20), f2 char(25), f3 date, f4 int)
31 engine = <engine_to_be_tested>;
32 load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' into table t7;
33 Warnings:
34 Warning 1265 Data truncated for column 'f3' at row 1
35 Warning 1265 Data truncated for column 'f3' at row 2
36 Warning 1265 Data truncated for column 'f3' at row 3
37 Warning 1265 Data truncated for column 'f3' at row 4
38 Warning 1265 Data truncated for column 'f3' at row 5
39 Warning 1265 Data truncated for column 'f3' at row 6
40 Warning 1265 Data truncated for column 'f3' at row 7
41 Warning 1265 Data truncated for column 'f3' at row 8
42 Warning 1265 Data truncated for column 'f3' at row 9
43 Warning 1265 Data truncated for column 'f3' at row 10
44 create table t8 (f1 char(20), f2 char(25), f3 date, f4 int)
45 engine = <engine_to_be_tested>;
46 load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t7.txt' into table t8;
47 Warnings:
48 Warning 1265 Data truncated for column 'f3' at row 1
49 Warning 1265 Data truncated for column 'f3' at row 2
50 Warning 1265 Data truncated for column 'f3' at row 3
51 Warning 1265 Data truncated for column 'f3' at row 4
52 Warning 1265 Data truncated for column 'f3' at row 5
53 Warning 1265 Data truncated for column 'f3' at row 6
54 Warning 1265 Data truncated for column 'f3' at row 7
55 Warning 1265 Data truncated for column 'f3' at row 8
56 Warning 1265 Data truncated for column 'f3' at row 9
57 Warning 1265 Data truncated for column 'f3' at row 10
58 create table t9(f1 int, f2 char(25), f3 int) engine = <engine_to_be_tested>;
59 load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t9.txt' into table t9;
60 create table t10(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int)
61 engine = <engine_to_be_tested>;
62 load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t10;
63 create table t11(f1 char(20),f2 char(25),f3 date,f4 int,f5 char(25),f6 int)
64 engine = <engine_to_be_tested>;
65 load data infile '<MYSQLTEST_VARDIR>/std_data/funcs_1/t4.txt' into table t11;
66 SET sql_mode = default;
67 Warnings:
68 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
69
70 Section 3.1.8 - SHOW statement checks:
71 --------------------------------------------------------------------------------
72 Warnings:
73 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
74 Warnings:
75 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
76
77 Testcase 3.1.8.9:
78 -----------------
79 Warnings:
80 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
81 Warnings:
82 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
83
84 Ensure that all stored procedure changes made with ALTER PROCEDURE or ALTER
85 FUNCTION are properly recorded and displayed when a SHOW CREATE PROCEDURE or
86 SHOW CREATE PROCEDURE STATUS statement, or a SHOW CREATE FUNCTION or SHOW CREATE
87 FUNCTION STATUS statement (respectively) is executed.
88 --------------------------------------------------------------------------------
89 Warnings:
90 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
91 DROP FUNCTION IF EXISTS fn_1;
92 DROP FUNCTION IF EXISTS fn_2;
93 DROP PROCEDURE IF EXISTS sp_1;
94 DROP PROCEDURE IF EXISTS sp_2;
95 CREATE PROCEDURE sp_1 (i1 int)
96 BEGIN
97 set @x=i1;
98 END//
99 CREATE PROCEDURE sp_2 (i1 int) SQL SECURITY INVOKER COMMENT 'created with INVOKER'
100 BEGIN
101 set @x=i1;
102 END//
103 CREATE FUNCTION fn_1 (i1 longtext, i2 mediumint , i3 longblob, i4 year, i5 real) returns year
104 BEGIN
105 set @x=i1;
106 set @y=@x;
107 return i4;
108 END//
109 CREATE FUNCTION fn_2 (i1 longtext, i2 mediumint , i3 longblob, i4 year, i5 real)
110 RETURNS YEAR
111 SQL SECURITY INVOKER
112 COMMENT 'created with INVOKER'
113 BEGIN
114 set @x=i1;
115 set @y=@x;
116 return i4;
117 END//
118
119 ... now check what is stored:
120 -----------------------------
121 SELECT * FROM information_schema.routines
122 WHERE routine_schema = 'db_storedproc'
123 ORDER BY routine_type, routine_name;
124 SPECIFIC_NAME fn_1
125 ROUTINE_CATALOG def
126 ROUTINE_SCHEMA db_storedproc
127 ROUTINE_NAME fn_1
128 ROUTINE_TYPE FUNCTION
129 DATA_TYPE year
130 CHARACTER_MAXIMUM_LENGTH NULL
131 CHARACTER_OCTET_LENGTH NULL
132 NUMERIC_PRECISION NULL
133 NUMERIC_SCALE NULL
134 DATETIME_PRECISION NULL
135 CHARACTER_SET_NAME NULL
136 COLLATION_NAME NULL
137 DTD_IDENTIFIER year
138 ROUTINE_BODY SQL
139 ROUTINE_DEFINITION BEGIN
140 set @x=i1;
141 set @y=@x;
142 return i4;
143 END
144 EXTERNAL_NAME NULL
145 EXTERNAL_LANGUAGE SQL
146 PARAMETER_STYLE SQL
147 IS_DETERMINISTIC NO
148 SQL_DATA_ACCESS CONTAINS SQL
149 SQL_PATH NULL
150 SECURITY_TYPE DEFINER
151 CREATED <modified>
152 LAST_ALTERED <created>
153 SQL_MODE ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
154 ROUTINE_COMMENT
155 DEFINER root@localhost
156 CHARACTER_SET_CLIENT utf8mb4
157 COLLATION_CONNECTION utf8mb4_0900_ai_ci
158 DATABASE_COLLATION utf8mb4_0900_ai_ci
159 SPECIFIC_NAME fn_2
160 ROUTINE_CATALOG def
161 ROUTINE_SCHEMA db_storedproc
162 ROUTINE_NAME fn_2
163 ROUTINE_TYPE FUNCTION
164 DATA_TYPE year
165 CHARACTER_MAXIMUM_LENGTH NULL
166 CHARACTER_OCTET_LENGTH NULL
167 NUMERIC_PRECISION NULL
168 NUMERIC_SCALE NULL
169 DATETIME_PRECISION NULL
170 CHARACTER_SET_NAME NULL
171 COLLATION_NAME NULL
172 DTD_IDENTIFIER year
173 ROUTINE_BODY SQL
174 ROUTINE_DEFINITION BEGIN
175 set @x=i1;
176 set @y=@x;
177 return i4;
178 END
179 EXTERNAL_NAME NULL
180 EXTERNAL_LANGUAGE SQL
181 PARAMETER_STYLE SQL
182 IS_DETERMINISTIC NO
183 SQL_DATA_ACCESS CONTAINS SQL
184 SQL_PATH NULL
185 SECURITY_TYPE INVOKER
186 CREATED <modified>
187 LAST_ALTERED <created>
188 SQL_MODE ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
189 ROUTINE_COMMENT created with INVOKER
190 DEFINER root@localhost
191 CHARACTER_SET_CLIENT utf8mb4
192 COLLATION_CONNECTION utf8mb4_0900_ai_ci
193 DATABASE_COLLATION utf8mb4_0900_ai_ci
194 SPECIFIC_NAME sp_1
195 ROUTINE_CATALOG def
196 ROUTINE_SCHEMA db_storedproc
197 ROUTINE_NAME sp_1
198 ROUTINE_TYPE PROCEDURE
199 DATA_TYPE
200 CHARACTER_MAXIMUM_LENGTH NULL
201 CHARACTER_OCTET_LENGTH NULL
202 NUMERIC_PRECISION NULL
203 NUMERIC_SCALE NULL
204 DATETIME_PRECISION NULL
205 CHARACTER_SET_NAME NULL
206 COLLATION_NAME NULL
207 DTD_IDENTIFIER NULL
208 ROUTINE_BODY SQL
209 ROUTINE_DEFINITION BEGIN
210 set @x=i1;
211 END
212 EXTERNAL_NAME NULL
213 EXTERNAL_LANGUAGE SQL
214 PARAMETER_STYLE SQL
215 IS_DETERMINISTIC NO
216 SQL_DATA_ACCESS CONTAINS SQL
217 SQL_PATH NULL
218 SECURITY_TYPE DEFINER
219 CREATED <modified>
220 LAST_ALTERED <created>
221 SQL_MODE ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
222 ROUTINE_COMMENT
223 DEFINER root@localhost
224 CHARACTER_SET_CLIENT utf8mb4
225 COLLATION_CONNECTION utf8mb4_0900_ai_ci
226 DATABASE_COLLATION utf8mb4_0900_ai_ci
227 SPECIFIC_NAME sp_2
228 ROUTINE_CATALOG def
229 ROUTINE_SCHEMA db_storedproc
230 ROUTINE_NAME sp_2
231 ROUTINE_TYPE PROCEDURE
232 DATA_TYPE
233 CHARACTER_MAXIMUM_LENGTH NULL
234 CHARACTER_OCTET_LENGTH NULL
235 NUMERIC_PRECISION NULL
236 NUMERIC_SCALE NULL
237 DATETIME_PRECISION NULL
238 CHARACTER_SET_NAME NULL
239 COLLATION_NAME NULL
240 DTD_IDENTIFIER NULL
241 ROUTINE_BODY SQL
242 ROUTINE_DEFINITION BEGIN
243 set @x=i1;
244 END
245 EXTERNAL_NAME NULL
246 EXTERNAL_LANGUAGE SQL
247 PARAMETER_STYLE SQL
248 IS_DETERMINISTIC NO
249 SQL_DATA_ACCESS CONTAINS SQL
250 SQL_PATH NULL
251 SECURITY_TYPE INVOKER
252 CREATED <modified>
253 LAST_ALTERED <created>
254 SQL_MODE ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
255 ROUTINE_COMMENT created with INVOKER
256 DEFINER root@localhost
257 CHARACTER_SET_CLIENT utf8mb4
258 COLLATION_CONNECTION utf8mb4_0900_ai_ci
259 DATABASE_COLLATION utf8mb4_0900_ai_ci
260 SHOW CREATE FUNCTION fn_1;
261 Function fn_1
262 sql_mode ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
263 Create Function CREATE DEFINER=`root`@`localhost` FUNCTION `fn_1`(i1 longtext, i2 mediumint , i3 longblob, i4 year, i5 real) RETURNS year
264 BEGIN
265 set @x=i1;
266 set @y=@x;
267 return i4;
268 END
269 character_set_client utf8mb4
270 collation_connection utf8mb4_0900_ai_ci
271 Database Collation utf8mb4_0900_ai_ci
272 SHOW CREATE FUNCTION fn_2;
273 Function fn_2
274 sql_mode ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
275 Create Function CREATE DEFINER=`root`@`localhost` FUNCTION `fn_2`(i1 longtext, i2 mediumint , i3 longblob, i4 year, i5 real) RETURNS year
276 SQL SECURITY INVOKER
277 COMMENT 'created with INVOKER'
278 BEGIN
279 set @x=i1;
280 set @y=@x;
281 return i4;
282 END
283 character_set_client utf8mb4
284 collation_connection utf8mb4_0900_ai_ci
285 Database Collation utf8mb4_0900_ai_ci
286 SHOW CREATE PROCEDURE sp_1;
287 Procedure sp_1
288 sql_mode ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
289 Create Procedure CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_1`(i1 int)
290 BEGIN
291 set @x=i1;
292 END
293 character_set_client utf8mb4
294 collation_connection utf8mb4_0900_ai_ci
295 Database Collation utf8mb4_0900_ai_ci
296 SHOW CREATE PROCEDURE sp_2;
297 Procedure sp_2
298 sql_mode ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
299 Create Procedure CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_2`(i1 int)
300 SQL SECURITY INVOKER
301 COMMENT 'created with INVOKER'
302 BEGIN
303 set @x=i1;
304 END
305 character_set_client utf8mb4
306 collation_connection utf8mb4_0900_ai_ci
307 Database Collation utf8mb4_0900_ai_ci
308 SHOW FUNCTION STATUS LIKE 'fn_%';
309 Db db_storedproc
310 Name fn_1
311 Type FUNCTION
312 Definer root@localhost
313 Modified <modified>
314 Created <created>
315 Security_type DEFINER
316 Comment
317 character_set_client utf8mb4
318 collation_connection utf8mb4_0900_ai_ci
319 Database Collation utf8mb4_0900_ai_ci
320 Db db_storedproc
321 Name fn_2
322 Type FUNCTION
323 Definer root@localhost
324 Modified <modified>
325 Created <created>
326 Security_type INVOKER
327 Comment created with INVOKER
328 character_set_client utf8mb4
329 collation_connection utf8mb4_0900_ai_ci
330 Database Collation utf8mb4_0900_ai_ci
331 SHOW PROCEDURE STATUS LIKE 'sp_%';
332 Db db_storedproc
333 Name sp_1
334 Type PROCEDURE
335 Definer root@localhost
336 Modified <modified>
337 Created <created>
338 Security_type DEFINER
339 Comment
340 character_set_client utf8mb4
341 collation_connection utf8mb4_0900_ai_ci
342 Database Collation utf8mb4_0900_ai_ci
343 Db db_storedproc
344 Name sp_2
345 Type PROCEDURE
346 Definer root@localhost
347 Modified <modified>
348 Created <created>
349 Security_type INVOKER
350 Comment created with INVOKER
351 character_set_client utf8mb4
352 collation_connection utf8mb4_0900_ai_ci
353 Database Collation utf8mb4_0900_ai_ci
354 Warnings:
355 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
356
357 ... now change some stuff:
358 --------------------------
359 Warnings:
360 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
361 ALTER PROCEDURE sp_1 SQL SECURITY INVOKER;
362 ALTER PROCEDURE sp_1 COMMENT 'new comment, SP changed to INVOKER';
363 ALTER PROCEDURE sp_2 SQL SECURITY DEFINER;
364 ALTER PROCEDURE sp_2 DROP COMMENT;
365 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DROP COMMENT' at line 1
366 ALTER PROCEDURE sp_2 COMMENT 'SP changed to DEFINER';
367 ALTER PROCEDURE sp_2 READS SQL DATA;
368 ALTER FUNCTION fn_1 SQL SECURITY INVOKER;
369 ALTER FUNCTION fn_1 COMMENT 'new comment, FN changed to INVOKER';
370 ALTER FUNCTION fn_1 NO SQL;
371 ALTER FUNCTION fn_2 SQL SECURITY DEFINER;
372 ALTER FUNCTION fn_2 COMMENT 'FN changed to DEFINER';
373 ALTER FUNCTION fn_2 MODIFIES SQL DATA;
374
375 ... now check what is stored:
376 -----------------------------
377 SELECT * FROM information_schema.routines
378 WHERE routine_schema = 'db_storedproc'
379 ORDER BY routine_type, routine_name;
380 SPECIFIC_NAME fn_1
381 ROUTINE_CATALOG def
382 ROUTINE_SCHEMA db_storedproc
383 ROUTINE_NAME fn_1
384 ROUTINE_TYPE FUNCTION
385 DATA_TYPE year
386 CHARACTER_MAXIMUM_LENGTH NULL
387 CHARACTER_OCTET_LENGTH NULL
388 NUMERIC_PRECISION NULL
389 NUMERIC_SCALE NULL
390 DATETIME_PRECISION NULL
391 CHARACTER_SET_NAME NULL
392 COLLATION_NAME NULL
393 DTD_IDENTIFIER year
394 ROUTINE_BODY SQL
395 ROUTINE_DEFINITION BEGIN
396 set @x=i1;
397 set @y=@x;
398 return i4;
399 END
400 EXTERNAL_NAME NULL
401 EXTERNAL_LANGUAGE SQL
402 PARAMETER_STYLE SQL
403 IS_DETERMINISTIC NO
404 SQL_DATA_ACCESS NO SQL
405 SQL_PATH NULL
406 SECURITY_TYPE INVOKER
407 CREATED <modified>
408 LAST_ALTERED <created>
409 SQL_MODE ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
410 ROUTINE_COMMENT new comment, FN changed to INVOKER
411 DEFINER root@localhost
412 CHARACTER_SET_CLIENT utf8mb4
413 COLLATION_CONNECTION utf8mb4_0900_ai_ci
414 DATABASE_COLLATION utf8mb4_0900_ai_ci
415 SPECIFIC_NAME fn_2
416 ROUTINE_CATALOG def
417 ROUTINE_SCHEMA db_storedproc
418 ROUTINE_NAME fn_2
419 ROUTINE_TYPE FUNCTION
420 DATA_TYPE year
421 CHARACTER_MAXIMUM_LENGTH NULL
422 CHARACTER_OCTET_LENGTH NULL
423 NUMERIC_PRECISION NULL
424 NUMERIC_SCALE NULL
425 DATETIME_PRECISION NULL
426 CHARACTER_SET_NAME NULL
427 COLLATION_NAME NULL
428 DTD_IDENTIFIER year
429 ROUTINE_BODY SQL
430 ROUTINE_DEFINITION BEGIN
431 set @x=i1;
432 set @y=@x;
433 return i4;
434 END
435 EXTERNAL_NAME NULL
436 EXTERNAL_LANGUAGE SQL
437 PARAMETER_STYLE SQL
438 IS_DETERMINISTIC NO
439 SQL_DATA_ACCESS MODIFIES SQL DATA
440 SQL_PATH NULL
441 SECURITY_TYPE DEFINER
442 CREATED <modified>
443 LAST_ALTERED <created>
444 SQL_MODE ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
445 ROUTINE_COMMENT FN changed to DEFINER
446 DEFINER root@localhost
447 CHARACTER_SET_CLIENT utf8mb4
448 COLLATION_CONNECTION utf8mb4_0900_ai_ci
449 DATABASE_COLLATION utf8mb4_0900_ai_ci
450 SPECIFIC_NAME sp_1
451 ROUTINE_CATALOG def
452 ROUTINE_SCHEMA db_storedproc
453 ROUTINE_NAME sp_1
454 ROUTINE_TYPE PROCEDURE
455 DATA_TYPE
456 CHARACTER_MAXIMUM_LENGTH NULL
457 CHARACTER_OCTET_LENGTH NULL
458 NUMERIC_PRECISION NULL
459 NUMERIC_SCALE NULL
460 DATETIME_PRECISION NULL
461 CHARACTER_SET_NAME NULL
462 COLLATION_NAME NULL
463 DTD_IDENTIFIER NULL
464 ROUTINE_BODY SQL
465 ROUTINE_DEFINITION BEGIN
466 set @x=i1;
467 END
468 EXTERNAL_NAME NULL
469 EXTERNAL_LANGUAGE SQL
470 PARAMETER_STYLE SQL
471 IS_DETERMINISTIC NO
472 SQL_DATA_ACCESS CONTAINS SQL
473 SQL_PATH NULL
474 SECURITY_TYPE INVOKER
475 CREATED <modified>
476 LAST_ALTERED <created>
477 SQL_MODE ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
478 ROUTINE_COMMENT new comment, SP changed to INVOKER
479 DEFINER root@localhost
480 CHARACTER_SET_CLIENT utf8mb4
481 COLLATION_CONNECTION utf8mb4_0900_ai_ci
482 DATABASE_COLLATION utf8mb4_0900_ai_ci
483 SPECIFIC_NAME sp_2
484 ROUTINE_CATALOG def
485 ROUTINE_SCHEMA db_storedproc
486 ROUTINE_NAME sp_2
487 ROUTINE_TYPE PROCEDURE
488 DATA_TYPE
489 CHARACTER_MAXIMUM_LENGTH NULL
490 CHARACTER_OCTET_LENGTH NULL
491 NUMERIC_PRECISION NULL
492 NUMERIC_SCALE NULL
493 DATETIME_PRECISION NULL
494 CHARACTER_SET_NAME NULL
495 COLLATION_NAME NULL
496 DTD_IDENTIFIER NULL
497 ROUTINE_BODY SQL
498 ROUTINE_DEFINITION BEGIN
499 set @x=i1;
500 END
501 EXTERNAL_NAME NULL
502 EXTERNAL_LANGUAGE SQL
503 PARAMETER_STYLE SQL
504 IS_DETERMINISTIC NO
505 SQL_DATA_ACCESS READS SQL DATA
506 SQL_PATH NULL
507 SECURITY_TYPE DEFINER
508 CREATED <modified>
509 LAST_ALTERED <created>
510 SQL_MODE ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
511 ROUTINE_COMMENT SP changed to DEFINER
512 DEFINER root@localhost
513 CHARACTER_SET_CLIENT utf8mb4
514 COLLATION_CONNECTION utf8mb4_0900_ai_ci
515 DATABASE_COLLATION utf8mb4_0900_ai_ci
516 SHOW CREATE FUNCTION fn_1;
517 Function fn_1
518 sql_mode ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
519 Create Function CREATE DEFINER=`root`@`localhost` FUNCTION `fn_1`(i1 longtext, i2 mediumint , i3 longblob, i4 year, i5 real) RETURNS year
520 NO SQL
521 SQL SECURITY INVOKER
522 COMMENT 'new comment, FN changed to INVOKER'
523 BEGIN
524 set @x=i1;
525 set @y=@x;
526 return i4;
527 END
528 character_set_client utf8mb4
529 collation_connection utf8mb4_0900_ai_ci
530 Database Collation utf8mb4_0900_ai_ci
531 SHOW CREATE FUNCTION fn_2;
532 Function fn_2
533 sql_mode ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
534 Create Function CREATE DEFINER=`root`@`localhost` FUNCTION `fn_2`(i1 longtext, i2 mediumint , i3 longblob, i4 year, i5 real) RETURNS year
535 MODIFIES SQL DATA
536 COMMENT 'FN changed to DEFINER'
537 BEGIN
538 set @x=i1;
539 set @y=@x;
540 return i4;
541 END
542 character_set_client utf8mb4
543 collation_connection utf8mb4_0900_ai_ci
544 Database Collation utf8mb4_0900_ai_ci
545 SHOW CREATE PROCEDURE sp_1;
546 Procedure sp_1
547 sql_mode ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
548 Create Procedure CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_1`(i1 int)
549 SQL SECURITY INVOKER
550 COMMENT 'new comment, SP changed to INVOKER'
551 BEGIN
552 set @x=i1;
553 END
554 character_set_client utf8mb4
555 collation_connection utf8mb4_0900_ai_ci
556 Database Collation utf8mb4_0900_ai_ci
557 SHOW CREATE PROCEDURE sp_2;
558 Procedure sp_2
559 sql_mode ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
560 Create Procedure CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_2`(i1 int)
561 READS SQL DATA
562 COMMENT 'SP changed to DEFINER'
563 BEGIN
564 set @x=i1;
565 END
566 character_set_client utf8mb4
567 collation_connection utf8mb4_0900_ai_ci
568 Database Collation utf8mb4_0900_ai_ci
569 SHOW FUNCTION STATUS LIKE 'fn_%';
570 Db db_storedproc
571 Name fn_1
572 Type FUNCTION
573 Definer root@localhost
574 Modified <modified>
575 Created <created>
576 Security_type INVOKER
577 Comment new comment, FN changed to INVOKER
578 character_set_client utf8mb4
579 collation_connection utf8mb4_0900_ai_ci
580 Database Collation utf8mb4_0900_ai_ci
581 Db db_storedproc
582 Name fn_2
583 Type FUNCTION
584 Definer root@localhost
585 Modified <modified>
586 Created <created>
587 Security_type DEFINER
588 Comment FN changed to DEFINER
589 character_set_client utf8mb4
590 collation_connection utf8mb4_0900_ai_ci
591 Database Collation utf8mb4_0900_ai_ci
592 SHOW PROCEDURE STATUS LIKE 'sp_%';
593 Db db_storedproc
594 Name sp_1
595 Type PROCEDURE
596 Definer root@localhost
597 Modified <modified>
598 Created <created>
599 Security_type INVOKER
600 Comment new comment, SP changed to INVOKER
601 character_set_client utf8mb4
602 collation_connection utf8mb4_0900_ai_ci
603 Database Collation utf8mb4_0900_ai_ci
604 Db db_storedproc
605 Name sp_2
606 Type PROCEDURE
607 Definer root@localhost
608 Modified <modified>
609 Created <created>
610 Security_type DEFINER
611 Comment SP changed to DEFINER
612 character_set_client utf8mb4
613 collation_connection utf8mb4_0900_ai_ci
614 Database Collation utf8mb4_0900_ai_ci
615 Warnings:
616 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
617
618 ... change back to default and check result:
619 --------------------------------------------
620 Warnings:
621 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
622 ALTER FUNCTION fn_2 CONTAINS SQL;
623
624 ... now check what is stored:
625 -----------------------------
626 SELECT * FROM information_schema.routines
627 WHERE routine_schema = 'db_storedproc'
628 ORDER BY routine_type, routine_name;
629 SPECIFIC_NAME fn_1
630 ROUTINE_CATALOG def
631 ROUTINE_SCHEMA db_storedproc
632 ROUTINE_NAME fn_1
633 ROUTINE_TYPE FUNCTION
634 DATA_TYPE year
635 CHARACTER_MAXIMUM_LENGTH NULL
636 CHARACTER_OCTET_LENGTH NULL
637 NUMERIC_PRECISION NULL
638 NUMERIC_SCALE NULL
639 DATETIME_PRECISION NULL
640 CHARACTER_SET_NAME NULL
641 COLLATION_NAME NULL
642 DTD_IDENTIFIER year
643 ROUTINE_BODY SQL
644 ROUTINE_DEFINITION BEGIN
645 set @x=i1;
646 set @y=@x;
647 return i4;
648 END
649 EXTERNAL_NAME NULL
650 EXTERNAL_LANGUAGE SQL
651 PARAMETER_STYLE SQL
652 IS_DETERMINISTIC NO
653 SQL_DATA_ACCESS NO SQL
654 SQL_PATH NULL
655 SECURITY_TYPE INVOKER
656 CREATED <modified>
657 LAST_ALTERED <created>
658 SQL_MODE ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
659 ROUTINE_COMMENT new comment, FN changed to INVOKER
660 DEFINER root@localhost
661 CHARACTER_SET_CLIENT utf8mb4
662 COLLATION_CONNECTION utf8mb4_0900_ai_ci
663 DATABASE_COLLATION utf8mb4_0900_ai_ci
664 SPECIFIC_NAME fn_2
665 ROUTINE_CATALOG def
666 ROUTINE_SCHEMA db_storedproc
667 ROUTINE_NAME fn_2
668 ROUTINE_TYPE FUNCTION
669 DATA_TYPE year
670 CHARACTER_MAXIMUM_LENGTH NULL
671 CHARACTER_OCTET_LENGTH NULL
672 NUMERIC_PRECISION NULL
673 NUMERIC_SCALE NULL
674 DATETIME_PRECISION NULL
675 CHARACTER_SET_NAME NULL
676 COLLATION_NAME NULL
677 DTD_IDENTIFIER year
678 ROUTINE_BODY SQL
679 ROUTINE_DEFINITION BEGIN
680 set @x=i1;
681 set @y=@x;
682 return i4;
683 END
684 EXTERNAL_NAME NULL
685 EXTERNAL_LANGUAGE SQL
686 PARAMETER_STYLE SQL
687 IS_DETERMINISTIC NO
688 SQL_DATA_ACCESS CONTAINS SQL
689 SQL_PATH NULL
690 SECURITY_TYPE DEFINER
691 CREATED <modified>
692 LAST_ALTERED <created>
693 SQL_MODE ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
694 ROUTINE_COMMENT FN changed to DEFINER
695 DEFINER root@localhost
696 CHARACTER_SET_CLIENT utf8mb4
697 COLLATION_CONNECTION utf8mb4_0900_ai_ci
698 DATABASE_COLLATION utf8mb4_0900_ai_ci
699 SPECIFIC_NAME sp_1
700 ROUTINE_CATALOG def
701 ROUTINE_SCHEMA db_storedproc
702 ROUTINE_NAME sp_1
703 ROUTINE_TYPE PROCEDURE
704 DATA_TYPE
705 CHARACTER_MAXIMUM_LENGTH NULL
706 CHARACTER_OCTET_LENGTH NULL
707 NUMERIC_PRECISION NULL
708 NUMERIC_SCALE NULL
709 DATETIME_PRECISION NULL
710 CHARACTER_SET_NAME NULL
711 COLLATION_NAME NULL
712 DTD_IDENTIFIER NULL
713 ROUTINE_BODY SQL
714 ROUTINE_DEFINITION BEGIN
715 set @x=i1;
716 END
717 EXTERNAL_NAME NULL
718 EXTERNAL_LANGUAGE SQL
719 PARAMETER_STYLE SQL
720 IS_DETERMINISTIC NO
721 SQL_DATA_ACCESS CONTAINS SQL
722 SQL_PATH NULL
723 SECURITY_TYPE INVOKER
724 CREATED <modified>
725 LAST_ALTERED <created>
726 SQL_MODE ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
727 ROUTINE_COMMENT new comment, SP changed to INVOKER
728 DEFINER root@localhost
729 CHARACTER_SET_CLIENT utf8mb4
730 COLLATION_CONNECTION utf8mb4_0900_ai_ci
731 DATABASE_COLLATION utf8mb4_0900_ai_ci
732 SPECIFIC_NAME sp_2
733 ROUTINE_CATALOG def
734 ROUTINE_SCHEMA db_storedproc
735 ROUTINE_NAME sp_2
736 ROUTINE_TYPE PROCEDURE
737 DATA_TYPE
738 CHARACTER_MAXIMUM_LENGTH NULL
739 CHARACTER_OCTET_LENGTH NULL
740 NUMERIC_PRECISION NULL
741 NUMERIC_SCALE NULL
742 DATETIME_PRECISION NULL
743 CHARACTER_SET_NAME NULL
744 COLLATION_NAME NULL
745 DTD_IDENTIFIER NULL
746 ROUTINE_BODY SQL
747 ROUTINE_DEFINITION BEGIN
748 set @x=i1;
749 END
750 EXTERNAL_NAME NULL
751 EXTERNAL_LANGUAGE SQL
752 PARAMETER_STYLE SQL
753 IS_DETERMINISTIC NO
754 SQL_DATA_ACCESS READS SQL DATA
755 SQL_PATH NULL
756 SECURITY_TYPE DEFINER
757 CREATED <modified>
758 LAST_ALTERED <created>
759 SQL_MODE ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
760 ROUTINE_COMMENT SP changed to DEFINER
761 DEFINER root@localhost
762 CHARACTER_SET_CLIENT utf8mb4
763 COLLATION_CONNECTION utf8mb4_0900_ai_ci
764 DATABASE_COLLATION utf8mb4_0900_ai_ci
765 SHOW CREATE FUNCTION fn_1;
766 Function fn_1
767 sql_mode ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
768 Create Function CREATE DEFINER=`root`@`localhost` FUNCTION `fn_1`(i1 longtext, i2 mediumint , i3 longblob, i4 year, i5 real) RETURNS year
769 NO SQL
770 SQL SECURITY INVOKER
771 COMMENT 'new comment, FN changed to INVOKER'
772 BEGIN
773 set @x=i1;
774 set @y=@x;
775 return i4;
776 END
777 character_set_client utf8mb4
778 collation_connection utf8mb4_0900_ai_ci
779 Database Collation utf8mb4_0900_ai_ci
780 SHOW CREATE FUNCTION fn_2;
781 Function fn_2
782 sql_mode ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
783 Create Function CREATE DEFINER=`root`@`localhost` FUNCTION `fn_2`(i1 longtext, i2 mediumint , i3 longblob, i4 year, i5 real) RETURNS year
784 COMMENT 'FN changed to DEFINER'
785 BEGIN
786 set @x=i1;
787 set @y=@x;
788 return i4;
789 END
790 character_set_client utf8mb4
791 collation_connection utf8mb4_0900_ai_ci
792 Database Collation utf8mb4_0900_ai_ci
793 SHOW CREATE PROCEDURE sp_1;
794 Procedure sp_1
795 sql_mode ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
796 Create Procedure CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_1`(i1 int)
797 SQL SECURITY INVOKER
798 COMMENT 'new comment, SP changed to INVOKER'
799 BEGIN
800 set @x=i1;
801 END
802 character_set_client utf8mb4
803 collation_connection utf8mb4_0900_ai_ci
804 Database Collation utf8mb4_0900_ai_ci
805 SHOW CREATE PROCEDURE sp_2;
806 Procedure sp_2
807 sql_mode ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
808 Create Procedure CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_2`(i1 int)
809 READS SQL DATA
810 COMMENT 'SP changed to DEFINER'
811 BEGIN
812 set @x=i1;
813 END
814 character_set_client utf8mb4
815 collation_connection utf8mb4_0900_ai_ci
816 Database Collation utf8mb4_0900_ai_ci
817 SHOW FUNCTION STATUS LIKE 'fn_%';
818 Db db_storedproc
819 Name fn_1
820 Type FUNCTION
821 Definer root@localhost
822 Modified <modified>
823 Created <created>
824 Security_type INVOKER
825 Comment new comment, FN changed to INVOKER
826 character_set_client utf8mb4
827 collation_connection utf8mb4_0900_ai_ci
828 Database Collation utf8mb4_0900_ai_ci
829 Db db_storedproc
830 Name fn_2
831 Type FUNCTION
832 Definer root@localhost
833 Modified <modified>
834 Created <created>
835 Security_type DEFINER
836 Comment FN changed to DEFINER
837 character_set_client utf8mb4
838 collation_connection utf8mb4_0900_ai_ci
839 Database Collation utf8mb4_0900_ai_ci
840 SHOW PROCEDURE STATUS LIKE 'sp_%';
841 Db db_storedproc
842 Name sp_1
843 Type PROCEDURE
844 Definer root@localhost
845 Modified <modified>
846 Created <created>
847 Security_type INVOKER
848 Comment new comment, SP changed to INVOKER
849 character_set_client utf8mb4
850 collation_connection utf8mb4_0900_ai_ci
851 Database Collation utf8mb4_0900_ai_ci
852 Db db_storedproc
853 Name sp_2
854 Type PROCEDURE
855 Definer root@localhost
856 Modified <modified>
857 Created <created>
858 Security_type DEFINER
859 Comment SP changed to DEFINER
860 character_set_client utf8mb4
861 collation_connection utf8mb4_0900_ai_ci
862 Database Collation utf8mb4_0900_ai_ci
863 Warnings:
864 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
865
866 ... cleanup
867 -----------
868 Warnings:
869 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
870 DROP FUNCTION fn_1;
871 DROP FUNCTION fn_2;
872 DROP PROCEDURE sp_1;
873
874 --source suite/funcs_1/storedproc/cleanup_sp_tb.inc
875 --------------------------------------------------------------------------------
876 DROP DATABASE IF EXISTS db_storedproc;
877 DROP DATABASE IF EXISTS db_storedproc_1;
878 Warnings:
879 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
880
881 . +++ END OF SCRIPT +++
882 --------------------------------------------------------------------------------
883 Warnings:
884 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.