Debian Codegraph @

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

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