1 /*
2 Copyright (c) 2002, 2018, Oracle and/or its affiliates.
3 Copyright (c) 2009, 2020, MariaDB
4
5 This program is free software; you can redistribute it and/or modify
6 it under the terms of the GNU General Public License as published by
7 the Free Software Foundation; version 2 of the License.
8
9 This program is distributed in the hope that it will be useful,
10 but WITHOUT ANY WARRANTY; without even the implied warranty of
11 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
12 GNU General Public License for more details.
13
14 You should have received a copy of the GNU General Public License
15 along with this program; if not, write to the Free Software
16 Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1335 USA */
17
18 #include "mariadb.h"
19 #include "sql_priv.h"
20 #include "unireg.h"
21 #include "sp.h"
22 #include "sql_base.h" // close_thread_tables
23 #include "sql_lex.h" // empty_clex_str
24 #include "sql_parse.h" // parse_sql
25 #include "key.h" // key_copy
26 #include "sql_show.h" // append_definer, append_identifier
27 #include "sql_db.h" // get_default_db_collation, mysql_opt_change_db,
28 // mysql_change_db, check_db_dir_existence,
29 // load_db_opt_by_name
30 #include "sql_table.h" // write_bin_log
31 #include "sp_head.h"
32 #include "sp_cache.h"
33 #include "transaction.h"
34 #include "lock.h" // lock_object_name
35
36 #include <my_user.h>
37 #include "mysql/psi/mysql_sp.h"
38
get_cache(THD * thd) const39 sp_cache **Sp_handler_procedure::get_cache(THD *thd) const
40 {
41 return &thd->sp_proc_cache;
42 }
43
get_cache(THD * thd) const44 sp_cache **Sp_handler_function::get_cache(THD *thd) const
45 {
46 return &thd->sp_func_cache;
47 }
48
get_cache(THD * thd) const49 sp_cache **Sp_handler_package_spec::get_cache(THD *thd) const
50 {
51 return &thd->sp_package_spec_cache;
52 }
53
get_cache(THD * thd) const54 sp_cache **Sp_handler_package_body::get_cache(THD *thd) const
55 {
56 return &thd->sp_package_body_cache;
57 }
58
59
recursion_depth(THD * thd) const60 ulong Sp_handler_procedure::recursion_depth(THD *thd) const
61 {
62 return thd->variables.max_sp_recursion_depth;
63 }
64
65
add_instr_freturn(THD * thd,sp_head * sp,sp_pcontext * spcont,Item * item,LEX * lex) const66 bool Sp_handler::add_instr_freturn(THD *thd, sp_head *sp,
67 sp_pcontext *spcont,
68 Item *item, LEX *lex) const
69 {
70 my_error(ER_SP_BADRETURN, MYF(0));
71 return true;
72 }
73
74
add_instr_preturn(THD * thd,sp_head * sp,sp_pcontext * spcont) const75 bool Sp_handler::add_instr_preturn(THD *thd, sp_head *sp,
76 sp_pcontext *spcont) const
77 {
78 thd->parse_error();
79 return true;
80 }
81
82
add_instr_freturn(THD * thd,sp_head * sp,sp_pcontext * spcont,Item * item,LEX * lex) const83 bool Sp_handler_function::add_instr_freturn(THD *thd, sp_head *sp,
84 sp_pcontext *spcont,
85 Item *item, LEX *lex) const
86 {
87 return sp->add_instr_freturn(thd, spcont, item, lex);
88 }
89
90
add_instr_preturn(THD * thd,sp_head * sp,sp_pcontext * spcont) const91 bool Sp_handler_procedure::add_instr_preturn(THD *thd, sp_head *sp,
92 sp_pcontext *spcont) const
93 {
94 return sp->add_instr_preturn(thd, spcont);
95 }
96
97
98 Sp_handler_procedure sp_handler_procedure;
99 Sp_handler_function sp_handler_function;
100 Sp_handler_package_spec sp_handler_package_spec;
101 Sp_handler_package_body sp_handler_package_body;
102 Sp_handler_trigger sp_handler_trigger;
103 Sp_handler_package_procedure sp_handler_package_procedure;
104 Sp_handler_package_function sp_handler_package_function;
105
106
package_routine_handler() const107 const Sp_handler *Sp_handler_procedure::package_routine_handler() const
108 {
109 return &sp_handler_package_procedure;
110 }
111
112
package_routine_handler() const113 const Sp_handler *Sp_handler_function::package_routine_handler() const
114 {
115 return &sp_handler_package_function;
116 }
117
118
119 static const
120 TABLE_FIELD_TYPE proc_table_fields[MYSQL_PROC_FIELD_COUNT] =
121 {
122 {
123 { STRING_WITH_LEN("db") },
124 { STRING_WITH_LEN("char(64)") },
125 { STRING_WITH_LEN("utf8") }
126 },
127 {
128 { STRING_WITH_LEN("name") },
129 { STRING_WITH_LEN("char(64)") },
130 { STRING_WITH_LEN("utf8") }
131 },
132 {
133 { STRING_WITH_LEN("type") },
134 { STRING_WITH_LEN("enum('FUNCTION','PROCEDURE')") },
135 { NULL, 0 }
136 },
137 {
138 { STRING_WITH_LEN("specific_name") },
139 { STRING_WITH_LEN("char(64)") },
140 { STRING_WITH_LEN("utf8") }
141 },
142 {
143 { STRING_WITH_LEN("language") },
144 { STRING_WITH_LEN("enum('SQL')") },
145 { NULL, 0 }
146 },
147 {
148 { STRING_WITH_LEN("sql_data_access") },
149 { STRING_WITH_LEN("enum('CONTAINS_SQL','NO_SQL','READS_SQL_DATA','MODIFIES_SQL_DATA')") },
150 { NULL, 0 }
151 },
152 {
153 { STRING_WITH_LEN("is_deterministic") },
154 { STRING_WITH_LEN("enum('YES','NO')") },
155 { NULL, 0 }
156 },
157 {
158 { STRING_WITH_LEN("security_type") },
159 { STRING_WITH_LEN("enum('INVOKER','DEFINER')") },
160 { NULL, 0 }
161 },
162 {
163 { STRING_WITH_LEN("param_list") },
164 { STRING_WITH_LEN("blob") },
165 { NULL, 0 }
166 },
167
168 {
169 { STRING_WITH_LEN("returns") },
170 { STRING_WITH_LEN("longblob") },
171 { NULL, 0 }
172 },
173 {
174 { STRING_WITH_LEN("body") },
175 { STRING_WITH_LEN("longblob") },
176 { NULL, 0 }
177 },
178 {
179 { STRING_WITH_LEN("definer") },
180 { STRING_WITH_LEN("char(") },
181 { STRING_WITH_LEN("utf8") }
182 },
183 {
184 { STRING_WITH_LEN("created") },
185 { STRING_WITH_LEN("timestamp") },
186 { NULL, 0 }
187 },
188 {
189 { STRING_WITH_LEN("modified") },
190 { STRING_WITH_LEN("timestamp") },
191 { NULL, 0 }
192 },
193 {
194 { STRING_WITH_LEN("sql_mode") },
195 { STRING_WITH_LEN("set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES',"
196 "'IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY',"
197 "'NO_UNSIGNED_SUBTRACTION',"
198 "'NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB',"
199 "'NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40',"
200 "'ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES',"
201 "'STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES',"
202 "'ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER',"
203 "'HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH',"
204 "'EMPTY_STRING_IS_NULL','SIMULTANEOUS_ASSIGNMENT',"
205 "'TIME_ROUND_FRACTIONAL')") },
206 { NULL, 0 }
207 },
208 {
209 { STRING_WITH_LEN("comment") },
210 { STRING_WITH_LEN("text") },
211 { STRING_WITH_LEN("utf8") }
212 },
213 {
214 { STRING_WITH_LEN("character_set_client") },
215 { STRING_WITH_LEN("char(32)") },
216 { STRING_WITH_LEN("utf8") }
217 },
218 {
219 { STRING_WITH_LEN("collation_connection") },
220 { STRING_WITH_LEN("char(32)") },
221 { STRING_WITH_LEN("utf8") }
222 },
223 {
224 { STRING_WITH_LEN("db_collation") },
225 { STRING_WITH_LEN("char(32)") },
226 { STRING_WITH_LEN("utf8") }
227 },
228 {
229 { STRING_WITH_LEN("body_utf8") },
230 { STRING_WITH_LEN("longblob") },
231 { NULL, 0 }
232 },
233 {
234 { STRING_WITH_LEN("aggregate") },
235 { STRING_WITH_LEN("enum('NONE','GROUP')") },
236 { NULL, 0 }
237 }
238 };
239
240 static const TABLE_FIELD_DEF
241 proc_table_def= {MYSQL_PROC_FIELD_COUNT, proc_table_fields, 0, (uint*) 0 };
242
243 /*************************************************************************/
244
245 /**
246 Stored_routine_creation_ctx -- creation context of stored routines
247 (stored procedures and functions).
248 */
249
250 class Stored_routine_creation_ctx : public Stored_program_creation_ctx,
251 public Sql_alloc
252 {
253 public:
254 static Stored_routine_creation_ctx *
255 load_from_db(THD *thd, const Database_qualified_name *name, TABLE *proc_tbl);
256
257 public:
clone(MEM_ROOT * mem_root)258 virtual Stored_program_creation_ctx *clone(MEM_ROOT *mem_root)
259 {
260 return new (mem_root) Stored_routine_creation_ctx(m_client_cs,
261 m_connection_cl,
262 m_db_cl);
263 }
264
265 protected:
create_backup_ctx(THD * thd) const266 virtual Object_creation_ctx *create_backup_ctx(THD *thd) const
267 {
268 DBUG_ENTER("Stored_routine_creation_ctx::create_backup_ctx");
269 DBUG_RETURN(new Stored_routine_creation_ctx(thd));
270 }
271
272 private:
Stored_routine_creation_ctx(THD * thd)273 Stored_routine_creation_ctx(THD *thd)
274 : Stored_program_creation_ctx(thd)
275 { }
276
Stored_routine_creation_ctx(CHARSET_INFO * client_cs,CHARSET_INFO * connection_cl,CHARSET_INFO * db_cl)277 Stored_routine_creation_ctx(CHARSET_INFO *client_cs,
278 CHARSET_INFO *connection_cl,
279 CHARSET_INFO *db_cl)
280 : Stored_program_creation_ctx(client_cs, connection_cl, db_cl)
281 { }
282 };
283
284 /**************************************************************************
285 Stored_routine_creation_ctx implementation.
286 **************************************************************************/
287
load_charset(MEM_ROOT * mem_root,Field * field,CHARSET_INFO * dflt_cs,CHARSET_INFO ** cs)288 bool load_charset(MEM_ROOT *mem_root,
289 Field *field,
290 CHARSET_INFO *dflt_cs,
291 CHARSET_INFO **cs)
292 {
293 LEX_CSTRING cs_name;
294
295 if (field->val_str_nopad(mem_root, &cs_name))
296 {
297 *cs= dflt_cs;
298 return TRUE;
299 }
300
301 DBUG_ASSERT(cs_name.str[cs_name.length] == 0);
302 *cs= get_charset_by_csname(cs_name.str, MY_CS_PRIMARY, MYF(0));
303
304 if (*cs == NULL)
305 {
306 *cs= dflt_cs;
307 return TRUE;
308 }
309
310 return FALSE;
311 }
312
313 /*************************************************************************/
314
load_collation(MEM_ROOT * mem_root,Field * field,CHARSET_INFO * dflt_cl,CHARSET_INFO ** cl)315 bool load_collation(MEM_ROOT *mem_root,
316 Field *field,
317 CHARSET_INFO *dflt_cl,
318 CHARSET_INFO **cl)
319 {
320 LEX_CSTRING cl_name;
321
322 if (field->val_str_nopad(mem_root, &cl_name))
323 {
324 *cl= dflt_cl;
325 return TRUE;
326 }
327
328 DBUG_ASSERT(cl_name.str[cl_name.length] == 0);
329 *cl= get_charset_by_name(cl_name.str, MYF(0));
330
331 if (*cl == NULL)
332 {
333 *cl= dflt_cl;
334 return TRUE;
335 }
336
337 return FALSE;
338 }
339
340 /*************************************************************************/
341
342 Stored_routine_creation_ctx *
load_from_db(THD * thd,const Database_qualified_name * name,TABLE * proc_tbl)343 Stored_routine_creation_ctx::load_from_db(THD *thd,
344 const Database_qualified_name *name,
345 TABLE *proc_tbl)
346 {
347 /* Load character set/collation attributes. */
348
349 CHARSET_INFO *client_cs;
350 CHARSET_INFO *connection_cl;
351 CHARSET_INFO *db_cl;
352
353 const char *db_name= thd->strmake(name->m_db.str, name->m_db.length);
354 const char *sr_name= thd->strmake(name->m_name.str, name->m_name.length);
355
356 bool invalid_creation_ctx= FALSE;
357
358 if (load_charset(thd->mem_root,
359 proc_tbl->field[MYSQL_PROC_FIELD_CHARACTER_SET_CLIENT],
360 thd->variables.character_set_client,
361 &client_cs))
362 {
363 sql_print_warning("Stored routine '%s'.'%s': invalid value "
364 "in column mysql.proc.character_set_client.",
365 (const char *) db_name,
366 (const char *) sr_name);
367
368 invalid_creation_ctx= TRUE;
369 }
370
371 if (load_collation(thd->mem_root,
372 proc_tbl->field[MYSQL_PROC_FIELD_COLLATION_CONNECTION],
373 thd->variables.collation_connection,
374 &connection_cl))
375 {
376 sql_print_warning("Stored routine '%s'.'%s': invalid value "
377 "in column mysql.proc.collation_connection.",
378 (const char *) db_name,
379 (const char *) sr_name);
380
381 invalid_creation_ctx= TRUE;
382 }
383
384 if (load_collation(thd->mem_root,
385 proc_tbl->field[MYSQL_PROC_FIELD_DB_COLLATION],
386 NULL,
387 &db_cl))
388 {
389 sql_print_warning("Stored routine '%s'.'%s': invalid value "
390 "in column mysql.proc.db_collation.",
391 (const char *) db_name,
392 (const char *) sr_name);
393
394 invalid_creation_ctx= TRUE;
395 }
396
397 if (invalid_creation_ctx)
398 {
399 push_warning_printf(thd,
400 Sql_condition::WARN_LEVEL_WARN,
401 ER_SR_INVALID_CREATION_CTX,
402 ER_THD(thd, ER_SR_INVALID_CREATION_CTX),
403 (const char *) db_name,
404 (const char *) sr_name);
405 }
406
407 /*
408 If we failed to retrieve the database collation, load the default one
409 from the disk.
410 */
411
412 if (!db_cl)
413 db_cl= get_default_db_collation(thd, name->m_db.str);
414
415 /* Create the context. */
416
417 return new Stored_routine_creation_ctx(client_cs, connection_cl, db_cl);
418 }
419
420 /*************************************************************************/
421
422 class Proc_table_intact : public Table_check_intact
423 {
424 private:
425 bool m_print_once;
426
427 public:
Proc_table_intact()428 Proc_table_intact() : m_print_once(TRUE) { has_keys= TRUE; }
429
430 protected:
431 void report_error(uint code, const char *fmt, ...);
432 };
433
434
435 /**
436 Report failure to validate the mysql.proc table definition.
437 Print a message to the error log only once.
438 */
439
report_error(uint code,const char * fmt,...)440 void Proc_table_intact::report_error(uint code, const char *fmt, ...)
441 {
442 va_list args;
443 char buf[512];
444
445 va_start(args, fmt);
446 my_vsnprintf(buf, sizeof(buf), fmt, args);
447 va_end(args);
448
449 if (code)
450 my_message(code, buf, MYF(0));
451 else
452 my_error(ER_CANNOT_LOAD_FROM_TABLE_V2, MYF(0), "mysql", "proc");
453
454 if (m_print_once)
455 {
456 m_print_once= FALSE;
457 sql_print_error("%s", buf);
458 }
459 };
460
461
462 /** Single instance used to control printing to the error log. */
463 static Proc_table_intact proc_table_intact;
464
465
466 /**
467 Open the mysql.proc table for read.
468
469 @param thd Thread context
470 @param backup Pointer to Open_tables_state instance where information about
471 currently open tables will be saved, and from which will be
472 restored when we will end work with mysql.proc.
473
474 NOTES
475 On must have a start_new_trans object active when calling this function
476
477 @retval
478 0 Error
479 @retval
480 \# Pointer to TABLE object of mysql.proc
481 */
482
open_proc_table_for_read(THD * thd)483 TABLE *open_proc_table_for_read(THD *thd)
484 {
485 TABLE_LIST table;
486 DBUG_ENTER("open_proc_table_for_read");
487
488 DBUG_ASSERT(thd->internal_transaction());
489
490 table.init_one_table(&MYSQL_SCHEMA_NAME, &MYSQL_PROC_NAME, NULL, TL_READ);
491
492 if (open_system_tables_for_read(thd, &table))
493 DBUG_RETURN(NULL);
494
495 if (!proc_table_intact.check(table.table, &proc_table_def))
496 DBUG_RETURN(table.table);
497
498 thd->commit_whole_transaction_and_close_tables();
499
500 DBUG_RETURN(NULL);
501 }
502
503
504 /**
505 Open the mysql.proc table for update.
506
507 @param thd Thread context
508
509 @note
510 Table opened with this call should closed using close_thread_tables().
511
512 We don't need to use the start_new_transaction object when calling this
513 as there can't be any active transactions when we create or alter
514 stored procedures
515
516 @retval
517 0 Error
518 @retval
519 \# Pointer to TABLE object of mysql.proc
520 */
521
open_proc_table_for_update(THD * thd)522 static TABLE *open_proc_table_for_update(THD *thd)
523 {
524 TABLE_LIST table_list;
525 TABLE *table;
526 MDL_savepoint mdl_savepoint= thd->mdl_context.mdl_savepoint();
527 DBUG_ENTER("open_proc_table_for_update");
528
529 DBUG_ASSERT(!thd->internal_transaction());
530
531 table_list.init_one_table(&MYSQL_SCHEMA_NAME, &MYSQL_PROC_NAME, NULL,
532 TL_WRITE);
533
534 if (!(table= open_system_table_for_update(thd, &table_list)))
535 DBUG_RETURN(NULL);
536
537 if (!proc_table_intact.check(table, &proc_table_def))
538 DBUG_RETURN(table);
539
540 thd->commit_whole_transaction_and_close_tables();
541 thd->mdl_context.rollback_to_savepoint(mdl_savepoint);
542
543 DBUG_RETURN(NULL);
544 }
545
546
547 /**
548 Find row in open mysql.proc table representing stored routine.
549
550 @param thd Thread context
551 @param name Name of routine
552 @param table TABLE object for open mysql.proc table.
553
554 @retval
555 SP_OK Routine found
556 @retval
557 SP_KEY_NOT_FOUND No routine with given name
558 */
559
560 int
db_find_routine_aux(THD * thd,const Database_qualified_name * name,TABLE * table) const561 Sp_handler::db_find_routine_aux(THD *thd,
562 const Database_qualified_name *name,
563 TABLE *table) const
564 {
565 uchar key[MAX_KEY_LENGTH]; // db, name, optional key length type
566 DBUG_ENTER("db_find_routine_aux");
567 DBUG_PRINT("enter", ("type: %s name: %.*s",
568 type_str(),
569 (int) name->m_name.length, name->m_name.str));
570
571 /*
572 Create key to find row. We have to use field->store() to be able to
573 handle VARCHAR and CHAR fields.
574 Assumption here is that the three first fields in the table are
575 'db', 'name' and 'type' and the first key is the primary key over the
576 same fields.
577 */
578 if (name->m_name.length > table->field[1]->field_length)
579 DBUG_RETURN(SP_KEY_NOT_FOUND);
580 table->field[0]->store(name->m_db, &my_charset_bin);
581 table->field[1]->store(name->m_name, &my_charset_bin);
582 table->field[2]->store((longlong) type(), true);
583 key_copy(key, table->record[0], table->key_info,
584 table->key_info->key_length);
585
586 if (table->file->ha_index_read_idx_map(table->record[0], 0, key,
587 HA_WHOLE_KEY,
588 HA_READ_KEY_EXACT))
589 DBUG_RETURN(SP_KEY_NOT_FOUND);
590
591 DBUG_RETURN(SP_OK);
592 }
593
594
read_from_mysql_proc_row(THD * thd,TABLE * table)595 bool st_sp_chistics::read_from_mysql_proc_row(THD *thd, TABLE *table)
596 {
597 LEX_CSTRING str;
598
599 if (table->field[MYSQL_PROC_FIELD_ACCESS]->val_str_nopad(thd->mem_root,
600 &str))
601 return true;
602
603 switch (str.str[0]) {
604 case 'N':
605 daccess= SP_NO_SQL;
606 break;
607 case 'C':
608 daccess= SP_CONTAINS_SQL;
609 break;
610 case 'R':
611 daccess= SP_READS_SQL_DATA;
612 break;
613 case 'M':
614 daccess= SP_MODIFIES_SQL_DATA;
615 break;
616 default:
617 daccess= SP_DEFAULT_ACCESS_MAPPING;
618 }
619
620 if (table->field[MYSQL_PROC_FIELD_DETERMINISTIC]->val_str_nopad(thd->mem_root,
621 &str))
622 return true;
623 detistic= str.str[0] == 'N' ? false : true;
624
625 if (table->field[MYSQL_PROC_FIELD_SECURITY_TYPE]->val_str_nopad(thd->mem_root,
626 &str))
627 return true;
628 suid= str.str[0] == 'I' ? SP_IS_NOT_SUID : SP_IS_SUID;
629
630 if (table->field[MYSQL_PROC_FIELD_AGGREGATE]->val_str_nopad(thd->mem_root,
631 &str))
632 return true;
633
634 switch (str.str[0]) {
635 case 'N':
636 agg_type= NOT_AGGREGATE;
637 break;
638 case 'G':
639 agg_type= GROUP_AGGREGATE;
640 break;
641 default:
642 agg_type= DEFAULT_AGGREGATE;
643 }
644
645
646 if (table->field[MYSQL_PROC_FIELD_COMMENT]->val_str_nopad(thd->mem_root,
647 &comment))
648 return true;
649
650 return false;
651 }
652
653
read_from_mysql_proc_row(THD * thd,TABLE * table)654 bool AUTHID::read_from_mysql_proc_row(THD *thd, TABLE *table)
655 {
656 LEX_CSTRING str;
657 if (table->field[MYSQL_PROC_FIELD_DEFINER]->val_str_nopad(thd->mem_root,
658 &str))
659 return true;
660 parse(str.str, str.length);
661 if (user.str[user.length])
662 ((char *) user.str)[user.length]= '\0'; // 0-terminate if was truncated
663 return false;
664 }
665
666
667 /**
668 Find routine definition in mysql.proc table and create corresponding
669 sp_head object for it.
670
671 @param thd Thread context
672 @param name Name of routine
673 @param sphp Out parameter in which pointer to created sp_head
674 object is returned (0 in case of error).
675
676 @note
677 This function may damage current LEX during execution, so it is good
678 idea to create temporary LEX and make it active before calling it.
679
680 @retval
681 0 Success
682 @retval
683 non-0 Error (may be one of special codes like SP_KEY_NOT_FOUND)
684 */
685
686 int
db_find_routine(THD * thd,const Database_qualified_name * name,sp_head ** sphp) const687 Sp_handler::db_find_routine(THD *thd,
688 const Database_qualified_name *name,
689 sp_head **sphp) const
690 {
691 TABLE *table;
692 LEX_CSTRING params, returns, body;
693 int ret;
694 longlong created;
695 longlong modified;
696 Sp_chistics chistics;
697 bool saved_time_zone_used= thd->time_zone_used;
698 bool trans_commited= 0;
699 sql_mode_t sql_mode;
700 Stored_program_creation_ctx *creation_ctx;
701 AUTHID definer;
702 DBUG_ENTER("db_find_routine");
703 DBUG_PRINT("enter", ("type: %s name: %.*s",
704 type_str(),
705 (int) name->m_name.length, name->m_name.str));
706
707 *sphp= 0; // In case of errors
708
709 start_new_trans new_trans(thd);
710 Sql_mode_instant_set sms(thd, 0);
711
712 if (!(table= open_proc_table_for_read(thd)))
713 {
714 ret= SP_OPEN_TABLE_FAILED;
715 goto done;
716 }
717
718 if ((ret= db_find_routine_aux(thd, name, table)) != SP_OK)
719 goto done;
720
721 if (table->s->fields < MYSQL_PROC_FIELD_COUNT)
722 {
723 ret= SP_GET_FIELD_FAILED;
724 goto done;
725 }
726
727 if (chistics.read_from_mysql_proc_row(thd, table) ||
728 definer.read_from_mysql_proc_row(thd, table))
729 {
730 ret= SP_GET_FIELD_FAILED;
731 goto done;
732 }
733
734 table->field[MYSQL_PROC_FIELD_PARAM_LIST]->val_str_nopad(thd->mem_root,
735 ¶ms);
736 if (type() != SP_TYPE_FUNCTION)
737 returns= empty_clex_str;
738 else if (table->field[MYSQL_PROC_FIELD_RETURNS]->val_str_nopad(thd->mem_root,
739 &returns))
740 {
741 ret= SP_GET_FIELD_FAILED;
742 goto done;
743 }
744
745 if (table->field[MYSQL_PROC_FIELD_BODY]->val_str_nopad(thd->mem_root,
746 &body))
747 {
748 ret= SP_GET_FIELD_FAILED;
749 goto done;
750 }
751
752 // Get additional information
753 modified= table->field[MYSQL_PROC_FIELD_MODIFIED]->val_int();
754 created= table->field[MYSQL_PROC_FIELD_CREATED]->val_int();
755 sql_mode= (sql_mode_t) table->field[MYSQL_PROC_FIELD_SQL_MODE]->val_int();
756
757 creation_ctx= Stored_routine_creation_ctx::load_from_db(thd, name, table);
758
759 trans_commited= 1;
760 thd->commit_whole_transaction_and_close_tables();
761 new_trans.restore_old_transaction();
762
763 ret= db_load_routine(thd, name, sphp,
764 sql_mode, params, returns, body, chistics, definer,
765 created, modified, NULL, creation_ctx);
766 done:
767 /*
768 Restore the time zone flag as the timezone usage in proc table
769 does not affect replication.
770 */
771 thd->time_zone_used= saved_time_zone_used;
772 if (!trans_commited)
773 {
774 if (table)
775 thd->commit_whole_transaction_and_close_tables();
776 new_trans.restore_old_transaction();
777 }
778 DBUG_RETURN(ret);
779 }
780
781
782 int
db_find_and_cache_routine(THD * thd,const Database_qualified_name * name,sp_head ** sp) const783 Sp_handler::db_find_and_cache_routine(THD *thd,
784 const Database_qualified_name *name,
785 sp_head **sp) const
786 {
787 int rc= db_find_routine(thd, name, sp);
788 if (rc == SP_OK)
789 {
790 sp_cache_insert(get_cache(thd), *sp);
791 DBUG_PRINT("info", ("added new: %p, level: %lu, flags %x",
792 sp[0], sp[0]->m_recursion_level,
793 sp[0]->m_flags));
794 }
795 return rc;
796 }
797
798
799 /**
800 Silence DEPRECATED SYNTAX warnings when loading a stored procedure
801 into the cache.
802 */
803
804 struct Silence_deprecated_warning : public Internal_error_handler
805 {
806 public:
807 virtual bool handle_condition(THD *thd,
808 uint sql_errno,
809 const char* sqlstate,
810 Sql_condition::enum_warning_level *level,
811 const char* msg,
812 Sql_condition ** cond_hdl);
813 };
814
815 bool
handle_condition(THD *,uint sql_errno,const char *,Sql_condition::enum_warning_level * level,const char *,Sql_condition ** cond_hdl)816 Silence_deprecated_warning::handle_condition(
817 THD *,
818 uint sql_errno,
819 const char*,
820 Sql_condition::enum_warning_level *level,
821 const char*,
822 Sql_condition ** cond_hdl)
823 {
824 *cond_hdl= NULL;
825 if (sql_errno == ER_WARN_DEPRECATED_SYNTAX &&
826 *level == Sql_condition::WARN_LEVEL_WARN)
827 return TRUE;
828
829 return FALSE;
830 }
831
832
833 /**
834 @brief The function parses input strings and returns SP stucture.
835
836 @param[in] thd Thread handler
837 @param[in] defstr CREATE... string
838 @param[in] sql_mode SQL mode
839 @param[in] parent The owner package for package routines,
840 or NULL for standalone routines.
841 @param[in] creation_ctx Creation context of stored routines
842
843 @return Pointer on sp_head struct
844 @retval # Pointer on sp_head struct
845 @retval 0 error
846 */
847
sp_compile(THD * thd,String * defstr,sql_mode_t sql_mode,sp_package * parent,Stored_program_creation_ctx * creation_ctx)848 static sp_head *sp_compile(THD *thd, String *defstr, sql_mode_t sql_mode,
849 sp_package *parent,
850 Stored_program_creation_ctx *creation_ctx)
851 {
852 sp_head *sp;
853 sql_mode_t old_sql_mode= thd->variables.sql_mode;
854 ha_rows old_select_limit= thd->variables.select_limit;
855 sp_rcontext *old_spcont= thd->spcont;
856 Silence_deprecated_warning warning_handler;
857 Parser_state parser_state;
858
859 thd->variables.sql_mode= sql_mode;
860 thd->variables.select_limit= HA_POS_ERROR;
861
862 if (parser_state.init(thd, defstr->c_ptr_safe(), defstr->length()))
863 {
864 thd->variables.sql_mode= old_sql_mode;
865 thd->variables.select_limit= old_select_limit;
866 return NULL;
867 }
868
869 lex_start(thd);
870 thd->lex->sphead= parent;
871 thd->push_internal_handler(&warning_handler);
872 thd->spcont= 0;
873
874 if (parse_sql(thd, & parser_state, creation_ctx) || thd->lex == NULL)
875 {
876 sp= thd->lex->sphead;
877 sp_head::destroy(sp);
878 sp= 0;
879 }
880 else
881 {
882 sp= thd->lex->sphead;
883 }
884
885 thd->pop_internal_handler();
886 thd->spcont= old_spcont;
887 thd->variables.sql_mode= old_sql_mode;
888 thd->variables.select_limit= old_select_limit;
889 if (sp != NULL)
890 sp->init_psi_share();
891 return sp;
892 }
893
894
895 class Bad_db_error_handler : public Internal_error_handler
896 {
897 public:
Bad_db_error_handler()898 Bad_db_error_handler()
899 :m_error_caught(false)
900 {}
901
902 virtual bool handle_condition(THD *thd,
903 uint sql_errno,
904 const char* sqlstate,
905 Sql_condition::enum_warning_level *level,
906 const char* message,
907 Sql_condition ** cond_hdl);
908
error_caught() const909 bool error_caught() const { return m_error_caught; }
910
911 private:
912 bool m_error_caught;
913 };
914
915 bool
handle_condition(THD * thd,uint sql_errno,const char * sqlstate,Sql_condition::enum_warning_level * level,const char * message,Sql_condition ** cond_hdl)916 Bad_db_error_handler::handle_condition(THD *thd,
917 uint sql_errno,
918 const char* sqlstate,
919 Sql_condition::enum_warning_level
920 *level,
921 const char* message,
922 Sql_condition ** cond_hdl)
923 {
924 if (sql_errno == ER_BAD_DB_ERROR)
925 {
926 m_error_caught= true;
927 return true;
928 }
929 return false;
930 }
931
932
933 int
db_load_routine(THD * thd,const Database_qualified_name * name,sp_head ** sphp,sql_mode_t sql_mode,const LEX_CSTRING & params,const LEX_CSTRING & returns,const LEX_CSTRING & body,const st_sp_chistics & chistics,const AUTHID & definer,longlong created,longlong modified,sp_package * parent,Stored_program_creation_ctx * creation_ctx) const934 Sp_handler::db_load_routine(THD *thd, const Database_qualified_name *name,
935 sp_head **sphp,
936 sql_mode_t sql_mode,
937 const LEX_CSTRING ¶ms,
938 const LEX_CSTRING &returns,
939 const LEX_CSTRING &body,
940 const st_sp_chistics &chistics,
941 const AUTHID &definer,
942 longlong created, longlong modified,
943 sp_package *parent,
944 Stored_program_creation_ctx *creation_ctx) const
945 {
946 LEX *old_lex= thd->lex, newlex;
947 String defstr;
948 char saved_cur_db_name_buf[SAFE_NAME_LEN+1];
949 LEX_STRING saved_cur_db_name=
950 { saved_cur_db_name_buf, sizeof(saved_cur_db_name_buf) };
951 bool cur_db_changed;
952 Bad_db_error_handler db_not_exists_handler;
953
954 int ret= 0;
955
956 thd->lex= &newlex;
957 newlex.current_select= NULL;
958
959 defstr.set_charset(creation_ctx->get_client_cs());
960
961 /*
962 We have to add DEFINER clause and provide proper routine characterstics in
963 routine definition statement that we build here to be able to use this
964 definition for SHOW CREATE PROCEDURE later.
965 */
966
967 if (show_create_sp(thd, &defstr,
968 null_clex_str, name->m_name,
969 params, returns, body,
970 chistics, definer, DDL_options(), sql_mode))
971 {
972 ret= SP_INTERNAL_ERROR;
973 goto end;
974 }
975
976 thd->push_internal_handler(&db_not_exists_handler);
977 /*
978 Change the current database (if needed).
979
980 TODO: why do we force switch here?
981 */
982
983 if (mysql_opt_change_db(thd, &name->m_db, &saved_cur_db_name, TRUE,
984 &cur_db_changed))
985 {
986 ret= SP_INTERNAL_ERROR;
987 thd->pop_internal_handler();
988 goto end;
989 }
990 thd->pop_internal_handler();
991 if (db_not_exists_handler.error_caught())
992 {
993 ret= SP_INTERNAL_ERROR;
994 my_error(ER_BAD_DB_ERROR, MYF(0), name->m_db.str);
995
996 goto end;
997 }
998
999 {
1000 *sphp= sp_compile(thd, &defstr, sql_mode, parent, creation_ctx);
1001 /*
1002 Force switching back to the saved current database (if changed),
1003 because it may be NULL. In this case, mysql_change_db() would
1004 generate an error.
1005 */
1006
1007 if (cur_db_changed && mysql_change_db(thd,
1008 (LEX_CSTRING*) &saved_cur_db_name,
1009 TRUE))
1010 {
1011 ret= SP_INTERNAL_ERROR;
1012 goto end;
1013 }
1014
1015 if (!*sphp)
1016 {
1017 ret= SP_PARSE_ERROR;
1018 goto end;
1019 }
1020
1021 (*sphp)->set_definer(&definer.user, &definer.host);
1022 (*sphp)->set_info(created, modified, chistics, sql_mode);
1023 (*sphp)->set_creation_ctx(creation_ctx);
1024 (*sphp)->optimize();
1025
1026 if (type() == SP_TYPE_PACKAGE_BODY)
1027 {
1028 sp_package *package= (*sphp)->get_package();
1029 List_iterator<LEX> it(package->m_routine_implementations);
1030 for (LEX *lex; (lex= it++); )
1031 {
1032 DBUG_ASSERT(lex->sphead);
1033 lex->sphead->set_definer(&definer.user, &definer.host);
1034 lex->sphead->set_suid(package->suid());
1035 lex->sphead->m_sql_mode= sql_mode;
1036 lex->sphead->set_creation_ctx(creation_ctx);
1037 lex->sphead->optimize();
1038 }
1039 }
1040
1041 /*
1042 Not strictly necessary to invoke this method here, since we know
1043 that we've parsed CREATE PROCEDURE/FUNCTION and not an
1044 UPDATE/DELETE/INSERT/REPLACE/LOAD/CREATE TABLE, but we try to
1045 maintain the invariant that this method is called for each
1046 distinct statement, in case its logic is extended with other
1047 types of analyses in future.
1048 */
1049 newlex.set_trg_event_type_for_tables();
1050 }
1051
1052 end:
1053 thd->lex->sphead= NULL;
1054 lex_end(thd->lex);
1055 thd->lex= old_lex;
1056 return ret;
1057 }
1058
1059
1060 void
sp_returns_type(THD * thd,String & result,const sp_head * sp)1061 sp_returns_type(THD *thd, String &result, const sp_head *sp)
1062 {
1063 TABLE table;
1064 TABLE_SHARE share;
1065 Field *field;
1066 bzero((char*) &table, sizeof(table));
1067 bzero((char*) &share, sizeof(share));
1068 table.in_use= thd;
1069 table.s = &share;
1070 field= sp->create_result_field(0, 0, &table);
1071 field->sql_type(result);
1072
1073 if (field->has_charset())
1074 {
1075 result.append(STRING_WITH_LEN(" CHARSET "));
1076 result.append(field->charset()->csname);
1077 if (!(field->charset()->state & MY_CS_PRIMARY))
1078 {
1079 result.append(STRING_WITH_LEN(" COLLATE "));
1080 result.append(field->charset()->name);
1081 }
1082 }
1083
1084 delete field;
1085 }
1086
1087
1088 /**
1089 Delete the record for the stored routine object from mysql.proc,
1090 which is already opened, locked, and positioned to the record with the
1091 record to be deleted.
1092
1093 The operation deletes the record for the current record in "table"
1094 and invalidates the stored-routine cache.
1095
1096 @param thd Thread context.
1097 @param name Stored routine name.
1098 @param table A pointer to the opened mysql.proc table
1099
1100 @returns Error code.
1101 @return SP_OK on success, or SP_DELETE_ROW_FAILED on error.
1102 used to indicate about errors.
1103 */
1104
1105 int
sp_drop_routine_internal(THD * thd,const Database_qualified_name * name,TABLE * table) const1106 Sp_handler::sp_drop_routine_internal(THD *thd,
1107 const Database_qualified_name *name,
1108 TABLE *table) const
1109 {
1110 DBUG_ENTER("sp_drop_routine_internal");
1111
1112 if (table->file->ha_delete_row(table->record[0]))
1113 DBUG_RETURN(SP_DELETE_ROW_FAILED);
1114
1115 /* Make change permanent and avoid 'table is marked as crashed' errors */
1116 table->file->extra(HA_EXTRA_FLUSH);
1117
1118 sp_cache_invalidate();
1119 /*
1120 A lame workaround for lack of cache flush:
1121 make sure the routine is at least gone from the
1122 local cache.
1123 */
1124 sp_head *sp;
1125 sp_cache **spc= get_cache(thd);
1126 DBUG_ASSERT(spc);
1127 if ((sp= sp_cache_lookup(spc, name)))
1128 sp_cache_flush_obsolete(spc, &sp);
1129 /* Drop statistics for this stored program from performance schema. */
1130 MYSQL_DROP_SP(type(), name->m_db.str, static_cast<uint>(name->m_db.length),
1131 name->m_name.str, static_cast<uint>(name->m_name.length));
1132 DBUG_RETURN(SP_OK);
1133 }
1134
1135
1136 int
sp_find_and_drop_routine(THD * thd,TABLE * table,const Database_qualified_name * name) const1137 Sp_handler::sp_find_and_drop_routine(THD *thd, TABLE *table,
1138 const Database_qualified_name *name) const
1139 {
1140 int ret;
1141 if ((ret= db_find_routine_aux(thd, name, table)) != SP_OK)
1142 return ret;
1143 return sp_drop_routine_internal(thd, name, table);
1144 }
1145
1146
1147 int
1148 Sp_handler_package_spec::
sp_find_and_drop_routine(THD * thd,TABLE * table,const Database_qualified_name * name) const1149 sp_find_and_drop_routine(THD *thd, TABLE *table,
1150 const Database_qualified_name *name) const
1151 {
1152 int ret;
1153 if ((ret= db_find_routine_aux(thd, name, table)) != SP_OK)
1154 return ret;
1155 /*
1156 When we do "DROP PACKAGE pkg", we should also perform
1157 "DROP PACKAGE BODY pkg" automatically.
1158 */
1159 ret= sp_handler_package_body.sp_find_and_drop_routine(thd, table, name);
1160 if (ret != SP_KEY_NOT_FOUND && ret != SP_OK)
1161 {
1162 /*
1163 - SP_KEY_NOT_FOUND means that "CREATE PACKAGE pkg" did not
1164 have a correspoinding "CREATE PACKAGE BODY pkg" yet.
1165 - SP_OK means that "CREATE PACKAGE pkg" had a correspoinding
1166 "CREATE PACKAGE BODY pkg", which was successfully dropped.
1167 */
1168 return ret; // Other codes mean an unexpecte error
1169 }
1170 return Sp_handler::sp_find_and_drop_routine(thd, table, name);
1171 }
1172
1173
1174 /**
1175 Write stored-routine object into mysql.proc.
1176
1177 This operation stores attributes of the stored procedure/function into
1178 the mysql.proc.
1179
1180 @param thd Thread context.
1181 @param sp Stored routine object to store.
1182
1183 @note Opens and closes the thread tables. Therefore assumes
1184 that there are no locked tables in this thread at the time of
1185 invocation.
1186 Unlike some other DDL statements, *does* close the tables
1187 in the end, since the call to this function is normally
1188 followed by an implicit grant (sp_grant_privileges())
1189 and this subsequent call opens and closes mysql.procs_priv.
1190
1191 @return Error status.
1192 @retval FALSE on success
1193 @retval TRUE on error
1194 */
1195
1196 bool
sp_create_routine(THD * thd,const sp_head * sp) const1197 Sp_handler::sp_create_routine(THD *thd, const sp_head *sp) const
1198 {
1199 LEX *lex= thd->lex;
1200 bool ret= TRUE;
1201 TABLE *table;
1202 char definer_buf[USER_HOST_BUFF_SIZE];
1203 LEX_CSTRING definer;
1204 sql_mode_t saved_mode= thd->variables.sql_mode;
1205
1206 CHARSET_INFO *db_cs= get_default_db_collation(thd, sp->m_db.str);
1207
1208 bool store_failed= FALSE;
1209 DBUG_ENTER("sp_create_routine");
1210 DBUG_PRINT("enter", ("type: %s name: %.*s",
1211 type_str(),
1212 (int) sp->m_name.length,
1213 sp->m_name.str));
1214 MDL_key::enum_mdl_namespace mdl_type= get_mdl_type();
1215 LEX_CSTRING returns= empty_clex_str;
1216 String retstr(64);
1217 retstr.set_charset(system_charset_info);
1218
1219 /* Grab an exclusive MDL lock. */
1220 if (lock_object_name(thd, mdl_type, sp->m_db.str, sp->m_name.str))
1221 {
1222 my_error(ER_BAD_DB_ERROR, MYF(0), sp->m_db.str);
1223 DBUG_RETURN(TRUE);
1224 }
1225
1226 /*
1227 Check that a database directory with this name
1228 exists. Design note: This won't work on virtual databases
1229 like information_schema.
1230 */
1231 if (check_db_dir_existence(sp->m_db.str))
1232 {
1233 my_error(ER_BAD_DB_ERROR, MYF(0), sp->m_db.str);
1234 DBUG_RETURN(TRUE);
1235 }
1236
1237
1238 /* Reset sql_mode during data dictionary operations. */
1239 thd->variables.sql_mode= 0;
1240
1241 Check_level_instant_set check_level_save(thd, CHECK_FIELD_WARN);
1242
1243 if (!(table= open_proc_table_for_update(thd)))
1244 {
1245 my_error(ER_SP_STORE_FAILED, MYF(0), type_str(), sp->m_name.str);
1246 goto done;
1247 }
1248 else
1249 {
1250 /* Checking if the routine already exists */
1251 if (db_find_routine_aux(thd, sp, table) == SP_OK)
1252 {
1253 if (lex->create_info.or_replace())
1254 {
1255 switch (type()) {
1256 case SP_TYPE_PACKAGE:
1257 // Drop together with its PACKAGE BODY mysql.proc record
1258 if (sp_handler_package_spec.sp_find_and_drop_routine(thd, table, sp))
1259 goto done;
1260 break;
1261 case SP_TYPE_PACKAGE_BODY:
1262 case SP_TYPE_FUNCTION:
1263 case SP_TYPE_PROCEDURE:
1264 if (sp_drop_routine_internal(thd, sp, table))
1265 goto done;
1266 break;
1267 case SP_TYPE_TRIGGER:
1268 case SP_TYPE_EVENT:
1269 DBUG_ASSERT(0);
1270 ret= SP_OK;
1271 }
1272 }
1273 else if (lex->create_info.if_not_exists())
1274 {
1275 push_warning_printf(thd, Sql_condition::WARN_LEVEL_NOTE,
1276 ER_SP_ALREADY_EXISTS,
1277 ER_THD(thd, ER_SP_ALREADY_EXISTS),
1278 type_str(), sp->m_name.str);
1279
1280 ret= FALSE;
1281
1282 // Setting retstr as it is used for logging.
1283 if (type() == SP_TYPE_FUNCTION)
1284 {
1285 sp_returns_type(thd, retstr, sp);
1286 retstr.get_value(&returns);
1287 }
1288 goto log;
1289 }
1290 else
1291 {
1292 my_error(ER_SP_ALREADY_EXISTS, MYF(0), type_str(), sp->m_name.str);
1293 goto done;
1294 }
1295 }
1296
1297 restore_record(table, s->default_values); // Get default values for fields
1298
1299 /* NOTE: all needed privilege checks have been already done. */
1300 thd->lex->definer->set_lex_string(&definer, definer_buf);
1301
1302 if (table->s->fields < MYSQL_PROC_FIELD_COUNT)
1303 {
1304 my_error(ER_SP_STORE_FAILED, MYF(0), type_str(), sp->m_name.str);
1305 goto done;
1306 }
1307
1308 if (system_charset_info->numchars(sp->m_name.str,
1309 sp->m_name.str + sp->m_name.length) >
1310 table->field[MYSQL_PROC_FIELD_NAME]->char_length())
1311 {
1312 my_error(ER_TOO_LONG_IDENT, MYF(0), sp->m_name.str);
1313 goto done;
1314 }
1315 if (sp->m_body.length > table->field[MYSQL_PROC_FIELD_BODY]->field_length)
1316 {
1317 my_error(ER_TOO_LONG_BODY, MYF(0), sp->m_name.str);
1318 goto done;
1319 }
1320
1321 store_failed=
1322 table->field[MYSQL_PROC_FIELD_DB]->
1323 store(sp->m_db, system_charset_info);
1324
1325 store_failed= store_failed ||
1326 table->field[MYSQL_PROC_FIELD_NAME]->
1327 store(sp->m_name, system_charset_info);
1328
1329 if (sp->agg_type() != DEFAULT_AGGREGATE)
1330 {
1331 store_failed= store_failed ||
1332 table->field[MYSQL_PROC_FIELD_AGGREGATE]->
1333 store((longlong)sp->agg_type(),TRUE);
1334 }
1335
1336 store_failed= store_failed ||
1337 table->field[MYSQL_PROC_MYSQL_TYPE]->
1338 store((longlong) type(), true);
1339
1340 store_failed= store_failed ||
1341 table->field[MYSQL_PROC_FIELD_SPECIFIC_NAME]->
1342 store(sp->m_name, system_charset_info);
1343
1344 if (sp->daccess() != SP_DEFAULT_ACCESS)
1345 {
1346 store_failed= store_failed ||
1347 table->field[MYSQL_PROC_FIELD_ACCESS]->
1348 store((longlong)sp->daccess(), TRUE);
1349 }
1350
1351 store_failed= store_failed ||
1352 table->field[MYSQL_PROC_FIELD_DETERMINISTIC]->
1353 store((longlong)(sp->detistic() ? 1 : 2), TRUE);
1354
1355 if (sp->suid() != SP_IS_DEFAULT_SUID)
1356 {
1357 store_failed= store_failed ||
1358 table->field[MYSQL_PROC_FIELD_SECURITY_TYPE]->
1359 store((longlong)sp->suid(), TRUE);
1360 }
1361
1362 store_failed= store_failed ||
1363 table->field[MYSQL_PROC_FIELD_PARAM_LIST]->
1364 store(sp->m_params, system_charset_info);
1365
1366 if (type() == SP_TYPE_FUNCTION)
1367 {
1368 sp_returns_type(thd, retstr, sp);
1369 retstr.get_value(&returns);
1370
1371 store_failed= store_failed ||
1372 table->field[MYSQL_PROC_FIELD_RETURNS]->
1373 store(retstr.ptr(), retstr.length(), system_charset_info);
1374 }
1375
1376 store_failed= store_failed ||
1377 table->field[MYSQL_PROC_FIELD_BODY]->
1378 store(sp->m_body, system_charset_info);
1379
1380 store_failed= store_failed ||
1381 table->field[MYSQL_PROC_FIELD_DEFINER]->
1382 store(definer, system_charset_info);
1383
1384 table->field[MYSQL_PROC_FIELD_CREATED]->set_time();
1385 table->field[MYSQL_PROC_FIELD_MODIFIED]->set_time();
1386
1387 store_failed= store_failed ||
1388 table->field[MYSQL_PROC_FIELD_SQL_MODE]->
1389 store((longlong)saved_mode, TRUE);
1390
1391 if (sp->comment().str)
1392 {
1393 store_failed= store_failed ||
1394 table->field[MYSQL_PROC_FIELD_COMMENT]->
1395 store(sp->comment(), system_charset_info);
1396 }
1397
1398 if (type() == SP_TYPE_FUNCTION &&
1399 !trust_function_creators && mysql_bin_log.is_open())
1400 {
1401 if (!sp->detistic())
1402 {
1403 /*
1404 Note that this test is not perfect; one could use
1405 a non-deterministic read-only function in an update statement.
1406 */
1407 enum enum_sp_data_access access=
1408 (sp->daccess() == SP_DEFAULT_ACCESS) ?
1409 SP_DEFAULT_ACCESS_MAPPING : sp->daccess();
1410 if (access == SP_CONTAINS_SQL ||
1411 access == SP_MODIFIES_SQL_DATA)
1412 {
1413 my_error(ER_BINLOG_UNSAFE_ROUTINE, MYF(0));
1414 goto done;
1415 }
1416 }
1417 if (!(thd->security_ctx->master_access & PRIV_LOG_BIN_TRUSTED_SP_CREATOR))
1418 {
1419 my_error(ER_BINLOG_CREATE_ROUTINE_NEED_SUPER,MYF(0));
1420 goto done;
1421 }
1422 }
1423
1424 table->field[MYSQL_PROC_FIELD_CHARACTER_SET_CLIENT]->set_notnull();
1425 store_failed= store_failed ||
1426 table->field[MYSQL_PROC_FIELD_CHARACTER_SET_CLIENT]->store(
1427 thd->charset()->csname,
1428 strlen(thd->charset()->csname),
1429 system_charset_info);
1430
1431 table->field[MYSQL_PROC_FIELD_COLLATION_CONNECTION]->set_notnull();
1432 store_failed= store_failed ||
1433 table->field[MYSQL_PROC_FIELD_COLLATION_CONNECTION]->store(
1434 thd->variables.collation_connection->name,
1435 strlen(thd->variables.collation_connection->name),
1436 system_charset_info);
1437
1438 table->field[MYSQL_PROC_FIELD_DB_COLLATION]->set_notnull();
1439 store_failed= store_failed ||
1440 table->field[MYSQL_PROC_FIELD_DB_COLLATION]->store(
1441 db_cs->name, strlen(db_cs->name), system_charset_info);
1442
1443 table->field[MYSQL_PROC_FIELD_BODY_UTF8]->set_notnull();
1444 store_failed= store_failed ||
1445 table->field[MYSQL_PROC_FIELD_BODY_UTF8]->store(
1446 sp->m_body_utf8, system_charset_info);
1447
1448 if (store_failed)
1449 {
1450 my_error(ER_CANT_CREATE_SROUTINE, MYF(0), sp->m_name.str);
1451 goto done;
1452 }
1453
1454 if (table->file->ha_write_row(table->record[0]))
1455 {
1456 my_error(ER_SP_ALREADY_EXISTS, MYF(0), type_str(), sp->m_name.str);
1457 goto done;
1458 }
1459 /* Make change permanent and avoid 'table is marked as crashed' errors */
1460 table->file->extra(HA_EXTRA_FLUSH);
1461
1462 sp_cache_invalidate();
1463 }
1464
1465 log:
1466 if (mysql_bin_log.is_open())
1467 {
1468 thd->clear_error();
1469
1470 StringBuffer<128> log_query(thd->variables.character_set_client);
1471 DBUG_ASSERT(log_query.charset()->mbminlen == 1);
1472
1473 if (show_create_sp(thd, &log_query,
1474 sp->m_explicit_name ? sp->m_db : null_clex_str,
1475 sp->m_name,
1476 sp->m_params, returns, sp->m_body,
1477 sp->chistics(),
1478 thd->lex->definer[0],
1479 thd->lex->create_info,
1480 saved_mode))
1481 {
1482 my_error(ER_OUT_OF_RESOURCES, MYF(0));
1483 goto done;
1484 }
1485 /* restore sql_mode when binloging */
1486 thd->variables.sql_mode= saved_mode;
1487 /* Such a statement can always go directly to binlog, no trans cache */
1488 if (thd->binlog_query(THD::STMT_QUERY_TYPE,
1489 log_query.ptr(), log_query.length(),
1490 FALSE, FALSE, FALSE, 0) > 0)
1491 {
1492 my_error(ER_ERROR_ON_WRITE, MYF(0), "binary log", -1);
1493 goto done;
1494 }
1495 thd->variables.sql_mode= 0;
1496 }
1497 ret= FALSE;
1498
1499 done:
1500 thd->variables.sql_mode= saved_mode;
1501 DBUG_ASSERT(!thd->is_current_stmt_binlog_format_row());
1502 DBUG_RETURN(ret);
1503 }
1504
1505
1506 static bool
append_suid(String * buf,enum_sp_suid_behaviour suid)1507 append_suid(String *buf, enum_sp_suid_behaviour suid)
1508 {
1509 return suid == SP_IS_NOT_SUID &&
1510 buf->append(STRING_WITH_LEN(" SQL SECURITY INVOKER\n"));
1511 }
1512
1513
1514 static bool
append_comment(String * buf,const LEX_CSTRING & comment)1515 append_comment(String *buf, const LEX_CSTRING &comment)
1516 {
1517 if (!comment.length)
1518 return false;
1519 if (buf->append(STRING_WITH_LEN(" COMMENT ")))
1520 return true;
1521 append_unescaped(buf, comment.str, comment.length);
1522 return buf->append('\n');
1523 }
1524
1525
1526 static bool
append_package_chistics(String * buf,const st_sp_chistics & chistics)1527 append_package_chistics(String *buf, const st_sp_chistics &chistics)
1528 {
1529 return append_suid(buf, chistics.suid) ||
1530 append_comment(buf, chistics.comment);
1531 }
1532
1533
1534 bool
show_create_sp(THD * thd,String * buf,const LEX_CSTRING & db,const LEX_CSTRING & name,const LEX_CSTRING & params,const LEX_CSTRING & returns,const LEX_CSTRING & body,const st_sp_chistics & chistics,const AUTHID & definer,const DDL_options_st ddl_options,sql_mode_t sql_mode) const1535 Sp_handler_package::show_create_sp(THD *thd, String *buf,
1536 const LEX_CSTRING &db,
1537 const LEX_CSTRING &name,
1538 const LEX_CSTRING ¶ms,
1539 const LEX_CSTRING &returns,
1540 const LEX_CSTRING &body,
1541 const st_sp_chistics &chistics,
1542 const AUTHID &definer,
1543 const DDL_options_st ddl_options,
1544 sql_mode_t sql_mode) const
1545 {
1546 Sql_mode_instant_set sms(thd, sql_mode);
1547 bool rc=
1548 buf->append(STRING_WITH_LEN("CREATE ")) ||
1549 (ddl_options.or_replace() &&
1550 buf->append(STRING_WITH_LEN("OR REPLACE "))) ||
1551 append_definer(thd, buf, &definer.user, &definer.host) ||
1552 buf->append(type_lex_cstring()) ||
1553 buf->append(" ", 1) ||
1554 (ddl_options.if_not_exists() &&
1555 buf->append(STRING_WITH_LEN("IF NOT EXISTS "))) ||
1556 (db.length > 0 &&
1557 (append_identifier(thd, buf, db.str, db.length) ||
1558 buf->append('.'))) ||
1559 append_identifier(thd, buf, name.str, name.length) ||
1560 append_package_chistics(buf, chistics) ||
1561 buf->append(" ", 1) ||
1562 buf->append(body.str, body.length);
1563 return rc;
1564 }
1565
1566
1567 /**
1568 Delete the record for the stored routine object from mysql.proc
1569 and do binary logging.
1570
1571 The operation deletes the record for the stored routine specified by name
1572 from the mysql.proc table and invalidates the stored-routine cache.
1573
1574 @param thd Thread context.
1575 @param name Stored routine name.
1576
1577 @return Error code. SP_OK is returned on success. Other SP_ constants are
1578 used to indicate about errors.
1579 */
1580
1581 int
sp_drop_routine(THD * thd,const Database_qualified_name * name) const1582 Sp_handler::sp_drop_routine(THD *thd,
1583 const Database_qualified_name *name) const
1584 {
1585 TABLE *table;
1586 int ret;
1587 DBUG_ENTER("sp_drop_routine");
1588 DBUG_PRINT("enter", ("type: %s name: %.*s",
1589 type_str(),
1590 (int) name->m_name.length, name->m_name.str));
1591 MDL_key::enum_mdl_namespace mdl_type= get_mdl_type();
1592
1593 /* Grab an exclusive MDL lock. */
1594 if (lock_object_name(thd, mdl_type, name->m_db.str, name->m_name.str))
1595 DBUG_RETURN(SP_DELETE_ROW_FAILED);
1596
1597 if (!(table= open_proc_table_for_update(thd)))
1598 DBUG_RETURN(SP_OPEN_TABLE_FAILED);
1599
1600 if ((ret= sp_find_and_drop_routine(thd, table, name)) == SP_OK &&
1601 write_bin_log(thd, TRUE, thd->query(), thd->query_length()))
1602 ret= SP_INTERNAL_ERROR;
1603 /*
1604 This statement will be replicated as a statement, even when using
1605 row-based replication. The flag will be reset at the end of the
1606 statement.
1607 */
1608 DBUG_ASSERT(!thd->is_current_stmt_binlog_format_row());
1609 DBUG_RETURN(ret);
1610 }
1611
1612
1613 /**
1614 Find and updated the record for the stored routine object in mysql.proc.
1615
1616 The operation finds the record for the stored routine specified by name
1617 in the mysql.proc table and updates it with new attributes. After
1618 successful update, the cache is invalidated.
1619
1620 @param thd Thread context.
1621 @param name Stored routine name.
1622 @param chistics New values of stored routine attributes to write.
1623
1624 @return Error code. SP_OK is returned on success. Other SP_ constants are
1625 used to indicate about errors.
1626 */
1627
1628 int
sp_update_routine(THD * thd,const Database_qualified_name * name,const st_sp_chistics * chistics) const1629 Sp_handler::sp_update_routine(THD *thd, const Database_qualified_name *name,
1630 const st_sp_chistics *chistics) const
1631 {
1632 TABLE *table;
1633 int ret;
1634 DBUG_ENTER("sp_update_routine");
1635 DBUG_PRINT("enter", ("type: %s name: %.*s",
1636 type_str(),
1637 (int) name->m_name.length, name->m_name.str));
1638 MDL_key::enum_mdl_namespace mdl_type= get_mdl_type();
1639
1640 /* Grab an exclusive MDL lock. */
1641 if (lock_object_name(thd, mdl_type, name->m_db.str, name->m_name.str))
1642 DBUG_RETURN(SP_OPEN_TABLE_FAILED);
1643
1644 if (!(table= open_proc_table_for_update(thd)))
1645 DBUG_RETURN(SP_OPEN_TABLE_FAILED);
1646
1647 if ((ret= db_find_routine_aux(thd, name, table)) == SP_OK)
1648 {
1649 if (type() == SP_TYPE_FUNCTION && ! trust_function_creators &&
1650 mysql_bin_log.is_open() &&
1651 (chistics->daccess == SP_CONTAINS_SQL ||
1652 chistics->daccess == SP_MODIFIES_SQL_DATA))
1653 {
1654 char *ptr;
1655 bool is_deterministic;
1656 ptr= get_field(thd->mem_root,
1657 table->field[MYSQL_PROC_FIELD_DETERMINISTIC]);
1658 if (ptr == NULL)
1659 {
1660 ret= SP_INTERNAL_ERROR;
1661 goto err;
1662 }
1663 is_deterministic= ptr[0] == 'N' ? FALSE : TRUE;
1664 if (!is_deterministic)
1665 {
1666 my_message(ER_BINLOG_UNSAFE_ROUTINE,
1667 ER_THD(thd, ER_BINLOG_UNSAFE_ROUTINE), MYF(0));
1668 ret= SP_INTERNAL_ERROR;
1669 goto err;
1670 }
1671 }
1672
1673 store_record(table,record[1]);
1674 table->field[MYSQL_PROC_FIELD_MODIFIED]->set_time();
1675 if (chistics->suid != SP_IS_DEFAULT_SUID)
1676 table->field[MYSQL_PROC_FIELD_SECURITY_TYPE]->
1677 store((longlong)chistics->suid, TRUE);
1678 if (chistics->daccess != SP_DEFAULT_ACCESS)
1679 table->field[MYSQL_PROC_FIELD_ACCESS]->
1680 store((longlong)chistics->daccess, TRUE);
1681 if (chistics->comment.str)
1682 table->field[MYSQL_PROC_FIELD_COMMENT]->store(chistics->comment,
1683 system_charset_info);
1684 if (chistics->agg_type != DEFAULT_AGGREGATE)
1685 table->field[MYSQL_PROC_FIELD_AGGREGATE]->
1686 store((longlong)chistics->agg_type, TRUE);
1687 if ((ret= table->file->ha_update_row(table->record[1],table->record[0])) &&
1688 ret != HA_ERR_RECORD_IS_THE_SAME)
1689 ret= SP_WRITE_ROW_FAILED;
1690 else
1691 ret= 0;
1692 /* Make change permanent and avoid 'table is marked as crashed' errors */
1693 table->file->extra(HA_EXTRA_FLUSH);
1694 }
1695
1696 if (ret == SP_OK)
1697 {
1698 if (write_bin_log(thd, TRUE, thd->query(), thd->query_length()))
1699 ret= SP_INTERNAL_ERROR;
1700 sp_cache_invalidate();
1701 }
1702 err:
1703 DBUG_ASSERT(!thd->is_current_stmt_binlog_format_row());
1704 DBUG_RETURN(ret);
1705 }
1706
1707
1708 /**
1709 This internal handler is used to trap errors from opening mysql.proc.
1710 */
1711
1712 class Lock_db_routines_error_handler : public Internal_error_handler
1713 {
1714 public:
handle_condition(THD * thd,uint sql_errno,const char * sqlstate,Sql_condition::enum_warning_level * level,const char * msg,Sql_condition ** cond_hdl)1715 bool handle_condition(THD *thd,
1716 uint sql_errno,
1717 const char* sqlstate,
1718 Sql_condition::enum_warning_level *level,
1719 const char* msg,
1720 Sql_condition ** cond_hdl)
1721 {
1722 if (sql_errno == ER_NO_SUCH_TABLE ||
1723 sql_errno == ER_NO_SUCH_TABLE_IN_ENGINE ||
1724 sql_errno == ER_CANNOT_LOAD_FROM_TABLE_V2 ||
1725 sql_errno == ER_COL_COUNT_DOESNT_MATCH_PLEASE_UPDATE ||
1726 sql_errno == ER_COL_COUNT_DOESNT_MATCH_CORRUPTED_V2)
1727 return true;
1728 return false;
1729 }
1730 };
1731
1732
1733 /**
1734 Acquires exclusive metadata lock on all stored routines in the
1735 given database.
1736
1737 @note Will also return false (=success) if mysql.proc can't be opened
1738 or is outdated. This allows DROP DATABASE to continue in these
1739 cases.
1740 */
1741
lock_db_routines(THD * thd,const char * db)1742 bool lock_db_routines(THD *thd, const char *db)
1743 {
1744 TABLE *table;
1745 uint key_len;
1746 MDL_request_list mdl_requests;
1747 Lock_db_routines_error_handler err_handler;
1748 uchar keybuf[MAX_KEY_LENGTH];
1749 DBUG_ENTER("lock_db_routines");
1750
1751 DBUG_SLOW_ASSERT(ok_for_lower_case_names(db));
1752
1753 start_new_trans new_trans(thd);
1754
1755 /*
1756 mysql.proc will be re-opened during deletion, so we can ignore
1757 errors when opening the table here. The error handler is
1758 used to avoid getting the same warning twice.
1759 */
1760 thd->push_internal_handler(&err_handler);
1761 table= open_proc_table_for_read(thd);
1762 thd->pop_internal_handler();
1763 if (!table)
1764 {
1765 /*
1766 DROP DATABASE should not fail even if mysql.proc does not exist
1767 or is outdated. We therefore only abort mysql_rm_db() if we
1768 have errors not handled by the error handler.
1769 */
1770 new_trans.restore_old_transaction();
1771 DBUG_RETURN(thd->is_error() || thd->killed);
1772 }
1773
1774 table->field[MYSQL_PROC_FIELD_DB]->store(db, strlen(db), system_charset_info);
1775 key_len= table->key_info->key_part[0].store_length;
1776 table->field[MYSQL_PROC_FIELD_DB]->get_key_image(keybuf, key_len, Field::itRAW);
1777 int nxtres= table->file->ha_index_init(0, 1);
1778 if (nxtres)
1779 {
1780 table->file->print_error(nxtres, MYF(0));
1781 goto error;
1782 }
1783
1784 if (!table->file->ha_index_read_map(table->record[0], keybuf, (key_part_map)1,
1785 HA_READ_KEY_EXACT))
1786 {
1787 do
1788 {
1789 char *sp_name= get_field(thd->mem_root,
1790 table->field[MYSQL_PROC_FIELD_NAME]);
1791 if (sp_name == NULL) // skip invalid sp names (hand-edited mysql.proc?)
1792 continue;
1793
1794 longlong sp_type= table->field[MYSQL_PROC_MYSQL_TYPE]->val_int();
1795 MDL_request *mdl_request= new (thd->mem_root) MDL_request;
1796 const Sp_handler *sph= Sp_handler::handler((enum_sp_type)
1797 sp_type);
1798 if (!sph)
1799 sph= &sp_handler_procedure;
1800 MDL_REQUEST_INIT(mdl_request, sph->get_mdl_type(), db, sp_name,
1801 MDL_EXCLUSIVE, MDL_TRANSACTION);
1802 mdl_requests.push_front(mdl_request);
1803 } while (! (nxtres= table->file->ha_index_next_same(table->record[0], keybuf, key_len)));
1804 }
1805 table->file->ha_index_end();
1806 if (nxtres != 0 && nxtres != HA_ERR_END_OF_FILE)
1807 {
1808 table->file->print_error(nxtres, MYF(0));
1809 goto error;
1810 }
1811 thd->commit_whole_transaction_and_close_tables();
1812 new_trans.restore_old_transaction();
1813
1814 /* We should already hold a global IX lock and a schema X lock. */
1815 DBUG_ASSERT(thd->mdl_context.is_lock_owner(MDL_key::BACKUP, "", "",
1816 MDL_BACKUP_DDL) &&
1817 thd->mdl_context.is_lock_owner(MDL_key::SCHEMA, db, "",
1818 MDL_EXCLUSIVE));
1819 DBUG_RETURN(thd->mdl_context.acquire_locks(&mdl_requests,
1820 thd->variables.lock_wait_timeout));
1821 error:
1822 thd->commit_whole_transaction_and_close_tables();
1823 new_trans.restore_old_transaction();
1824 DBUG_RETURN(true);
1825 }
1826
1827
1828 /**
1829 Drop all routines in database 'db'
1830
1831 @note Close the thread tables, the calling code might want to
1832 delete from other system tables afterwards.
1833 */
1834
1835 int
sp_drop_db_routines(THD * thd,const char * db)1836 sp_drop_db_routines(THD *thd, const char *db)
1837 {
1838 TABLE *table;
1839 int ret;
1840 uint key_len;
1841 MDL_savepoint mdl_savepoint= thd->mdl_context.mdl_savepoint();
1842 uchar keybuf[MAX_KEY_LENGTH];
1843 size_t db_length= strlen(db);
1844 Sql_mode_instant_remove smir(thd, MODE_PAD_CHAR_TO_FULL_LENGTH); // see below
1845 DBUG_ENTER("sp_drop_db_routines");
1846 DBUG_PRINT("enter", ("db: %s", db));
1847
1848 ret= SP_OPEN_TABLE_FAILED;
1849 if (!(table= open_proc_table_for_update(thd)))
1850 goto err;
1851
1852 table->field[MYSQL_PROC_FIELD_DB]->store(db, db_length, system_charset_info);
1853 key_len= table->key_info->key_part[0].store_length;
1854 table->field[MYSQL_PROC_FIELD_DB]->get_key_image(keybuf, key_len, Field::itRAW);
1855
1856 ret= SP_OK;
1857 if (table->file->ha_index_init(0, 1))
1858 {
1859 ret= SP_KEY_NOT_FOUND;
1860 goto err_idx_init;
1861 }
1862 if (!table->file->ha_index_read_map(table->record[0], keybuf, (key_part_map)1,
1863 HA_READ_KEY_EXACT))
1864 {
1865 int nxtres;
1866 bool deleted= FALSE;
1867
1868 do
1869 {
1870 if (! table->file->ha_delete_row(table->record[0]))
1871 {
1872 deleted= TRUE; /* We deleted something */
1873 #ifdef HAVE_PSI_SP_INTERFACE
1874 String buf;
1875 // the following assumes MODE_PAD_CHAR_TO_FULL_LENGTH being *unset*
1876 String *name= table->field[MYSQL_PROC_FIELD_NAME]->val_str(&buf);
1877
1878 enum_sp_type sp_type= (enum_sp_type) table->field[MYSQL_PROC_MYSQL_TYPE]->ptr[0];
1879 /* Drop statistics for this stored program from performance schema. */
1880 MYSQL_DROP_SP(sp_type, db, static_cast<uint>(db_length), name->ptr(), name->length());
1881 #endif
1882 }
1883 else
1884 {
1885 ret= SP_DELETE_ROW_FAILED;
1886 nxtres= 0;
1887 break;
1888 }
1889 } while (!(nxtres= table->file->ha_index_next_same(table->record[0],
1890 keybuf, key_len)));
1891 if (nxtres != HA_ERR_END_OF_FILE)
1892 ret= SP_KEY_NOT_FOUND;
1893 if (deleted)
1894 {
1895 sp_cache_invalidate();
1896 /* Make change permanent and avoid 'table is marked as crashed' errors */
1897 table->file->extra(HA_EXTRA_FLUSH);
1898 }
1899 }
1900 table->file->ha_index_end();
1901
1902 err_idx_init:
1903 trans_commit_stmt(thd);
1904 close_thread_tables(thd);
1905 /*
1906 Make sure to only release the MDL lock on mysql.proc, not other
1907 metadata locks DROP DATABASE might have acquired.
1908 */
1909 thd->mdl_context.rollback_to_savepoint(mdl_savepoint);
1910
1911 err:
1912 DBUG_RETURN(ret);
1913 }
1914
1915
1916 /**
1917 Implement SHOW CREATE statement for stored routines.
1918
1919 The operation finds the stored routine object specified by name and then
1920 calls sp_head::show_create_routine() for the object.
1921
1922 @param thd Thread context.
1923 @param name Stored routine name.
1924
1925 @return Error status.
1926 @retval FALSE on success
1927 @retval TRUE on error
1928 */
1929
1930 bool
sp_show_create_routine(THD * thd,const Database_qualified_name * name) const1931 Sp_handler::sp_show_create_routine(THD *thd,
1932 const Database_qualified_name *name) const
1933 {
1934 DBUG_ENTER("sp_show_create_routine");
1935 DBUG_PRINT("enter", ("type: %s name: %.*s",
1936 type_str(),
1937 (int) name->m_name.length,
1938 name->m_name.str));
1939 /*
1940 @todo: Consider using prelocking for this code as well. Currently
1941 SHOW CREATE PROCEDURE/FUNCTION is a dirty read of the data
1942 dictionary, i.e. takes no metadata locks.
1943 It is "safe" to do as long as it doesn't affect the results
1944 of the binary log or the query cache, which currently it does not.
1945 */
1946 sp_head *sp= 0;
1947
1948 DBUG_EXECUTE_IF("cache_sp_in_show_create",
1949 /* Some tests need just need a way to cache SP without other side-effects.*/
1950 sp_cache_routine(thd, name, false, &sp);
1951 sp->show_create_routine(thd, this);
1952 DBUG_RETURN(false);
1953 );
1954
1955 bool free_sp= db_find_routine(thd, name, &sp) == SP_OK;
1956 bool ret= !sp || sp->show_create_routine(thd, this);
1957 if (ret)
1958 {
1959 /*
1960 If we have insufficient privileges, pretend the routine
1961 does not exist.
1962 */
1963 my_error(ER_SP_DOES_NOT_EXIST, MYF(0), type_str(), name->m_name.str);
1964 }
1965 if (free_sp)
1966 sp_head::destroy(sp);
1967 DBUG_RETURN(ret);
1968 }
1969
1970
1971 /*
1972 A helper class to split package name from a dot-qualified name
1973 and return it as a 0-terminated string
1974 'pkg.name' -> 'pkg\0'
1975 */
1976
1977 class Prefix_name_buf: public LEX_CSTRING
1978 {
1979 char m_buf[SAFE_NAME_LEN + 1];
1980 public:
Prefix_name_buf(const THD * thd,const LEX_CSTRING & name)1981 Prefix_name_buf(const THD *thd, const LEX_CSTRING &name)
1982 {
1983 const char *end;
1984 if (!(end= strrchr(name.str, '.')))
1985 {
1986 static_cast<LEX_CSTRING*>(this)[0]= null_clex_str;
1987 }
1988 else
1989 {
1990 str= m_buf;
1991 length= end - name.str;
1992 set_if_smaller(length, sizeof(m_buf) - 1);
1993 memcpy(m_buf, name.str, length);
1994 m_buf[length]= '\0';
1995 }
1996 }
1997 };
1998
1999
2000 /*
2001 In case of recursions, we create multiple copies of the same SP.
2002 This methods checks the current recursion depth.
2003 In case if the recursion limit exceeded, it throws an error
2004 and returns NULL.
2005 Otherwise, depending on the current recursion level, it:
2006 - either returns the original SP,
2007 - or makes and returns a new clone of SP
2008 */
2009
2010 sp_head *
sp_clone_and_link_routine(THD * thd,const Database_qualified_name * name,sp_head * sp) const2011 Sp_handler::sp_clone_and_link_routine(THD *thd,
2012 const Database_qualified_name *name,
2013 sp_head *sp) const
2014 {
2015 DBUG_ENTER("sp_link_routine");
2016 int rc;
2017 ulong level;
2018 sp_head *new_sp;
2019 LEX_CSTRING returns= empty_clex_str;
2020 Database_qualified_name lname(name->m_db, name->m_name);
2021 #ifndef DBUG_OFF
2022 uint parent_subroutine_count=
2023 !sp->m_parent ? 0 :
2024 sp->m_parent->m_routine_declarations.elements +
2025 sp->m_parent->m_routine_implementations.elements;
2026 #endif
2027
2028 /*
2029 String buffer for RETURNS data type must have system charset;
2030 64 -- size of "returns" column of mysql.proc.
2031 */
2032 String retstr(64);
2033 retstr.set_charset(sp->get_creation_ctx()->get_client_cs());
2034
2035 DBUG_PRINT("info", ("found: %p", sp));
2036 if (sp->m_first_free_instance)
2037 {
2038 DBUG_PRINT("info", ("first free: %p level: %lu flags %x",
2039 sp->m_first_free_instance,
2040 sp->m_first_free_instance->m_recursion_level,
2041 sp->m_first_free_instance->m_flags));
2042 DBUG_ASSERT(!(sp->m_first_free_instance->m_flags & sp_head::IS_INVOKED));
2043 if (sp->m_first_free_instance->m_recursion_level > recursion_depth(thd))
2044 {
2045 recursion_level_error(thd, sp);
2046 DBUG_RETURN(0);
2047 }
2048 DBUG_RETURN(sp->m_first_free_instance);
2049 }
2050 /*
2051 Actually depth could be +1 than the actual value in case a SP calls
2052 SHOW CREATE PROCEDURE. Hence, the linked list could hold up to one more
2053 instance.
2054 */
2055
2056 level= sp->m_last_cached_sp->m_recursion_level + 1;
2057 if (level > recursion_depth(thd))
2058 {
2059 recursion_level_error(thd, sp);
2060 DBUG_RETURN(0);
2061 }
2062
2063 if (type() == SP_TYPE_FUNCTION)
2064 {
2065 sp_returns_type(thd, retstr, sp);
2066 retstr.get_value(&returns);
2067 }
2068
2069 if (sp->m_parent)
2070 {
2071 /*
2072 If we're cloning a recursively called package routine,
2073 we need to take some special measures:
2074 1. Cut the package name prefix from the routine name: 'pkg1.p1' -> 'p1',
2075 to have db_load_routine() generate and parse a query like this:
2076 CREATE PROCEDURE p1 ...;
2077 rather than:
2078 CREATE PROCEDURE pkg1.p1 ...;
2079 The latter would be misinterpreted by the parser as a standalone
2080 routine 'p1' in the database 'pkg1', which is not what we need.
2081 2. We pass m_parent to db_load_routine() to have it set
2082 thd->lex->sphead to sp->m_parent before calling parse_sql().
2083 These two measures allow to parse a package subroutine using
2084 the grammar for standalone routines, e.g.:
2085 CREATE PROCEDURE p1 ... END;
2086 instead of going through a more complex query, e.g.:
2087 CREATE PACKAGE BODY pkg1 AS
2088 PROCEDURE p1 ... END;
2089 END;
2090 */
2091 size_t prefix_length= sp->m_parent->m_name.length + 1;
2092 DBUG_ASSERT(prefix_length < lname.m_name.length);
2093 DBUG_ASSERT(lname.m_name.str[sp->m_parent->m_name.length] == '.');
2094 lname.m_name.str+= prefix_length;
2095 lname.m_name.length-= prefix_length;
2096 sp->m_parent->m_is_cloning_routine= true;
2097 }
2098
2099
2100 rc= db_load_routine(thd, &lname, &new_sp,
2101 sp->m_sql_mode, sp->m_params, returns,
2102 sp->m_body, sp->chistics(),
2103 sp->m_definer,
2104 sp->m_created, sp->m_modified,
2105 sp->m_parent,
2106 sp->get_creation_ctx());
2107 if (sp->m_parent)
2108 sp->m_parent->m_is_cloning_routine= false;
2109
2110 if (rc == SP_OK)
2111 {
2112 #ifndef DBUG_OFF
2113 /*
2114 We've just called the parser to clone the routine.
2115 In case of a package routine, make sure that the parser
2116 has not added any new subroutines directly to the parent package.
2117 The cloned subroutine instances get linked below to the first instance,
2118 they must have no direct links from the parent package.
2119 */
2120 DBUG_ASSERT(!sp->m_parent ||
2121 parent_subroutine_count ==
2122 sp->m_parent->m_routine_declarations.elements +
2123 sp->m_parent->m_routine_implementations.elements);
2124 #endif
2125 sp->m_last_cached_sp->m_next_cached_sp= new_sp;
2126 new_sp->m_recursion_level= level;
2127 new_sp->m_first_instance= sp;
2128 sp->m_last_cached_sp= sp->m_first_free_instance= new_sp;
2129 DBUG_PRINT("info", ("added level: %p, level: %lu, flags %x",
2130 new_sp, new_sp->m_recursion_level,
2131 new_sp->m_flags));
2132 DBUG_RETURN(new_sp);
2133 }
2134 DBUG_RETURN(0);
2135 }
2136
2137
2138 /**
2139 Obtain object representing stored procedure/function by its name from
2140 stored procedures cache and looking into mysql.proc if needed.
2141
2142 @param thd thread context
2143 @param name name of procedure
2144 @param cp hash to look routine in
2145 @param cache_only if true perform cache-only lookup
2146 (Don't look in mysql.proc).
2147
2148 @retval
2149 NonNULL pointer to sp_head object for the procedure
2150 @retval
2151 NULL in case of error.
2152 */
2153
2154 sp_head *
sp_find_routine(THD * thd,const Database_qualified_name * name,bool cache_only) const2155 Sp_handler::sp_find_routine(THD *thd, const Database_qualified_name *name,
2156 bool cache_only) const
2157 {
2158 DBUG_ENTER("Sp_handler::sp_find_routine");
2159 DBUG_PRINT("enter", ("name: %.*s.%.*s type: %s cache only %d",
2160 (int) name->m_db.length, name->m_db.str,
2161 (int) name->m_name.length, name->m_name.str,
2162 type_str(), cache_only));
2163 sp_cache **cp= get_cache(thd);
2164 sp_head *sp;
2165
2166 if ((sp= sp_cache_lookup(cp, name)))
2167 DBUG_RETURN(sp_clone_and_link_routine(thd, name, sp));
2168 if (!cache_only)
2169 db_find_and_cache_routine(thd, name, &sp);
2170 DBUG_RETURN(sp);
2171 }
2172
2173
2174 /**
2175 Find a package routine.
2176 See sp_cache_routine() for more information on parameters and return value.
2177
2178 @param thd - current THD
2179 @param pkgname_str - package name
2180 @param name - a mixed qualified name, with:
2181 * name->m_db set to the database, e.g. "dbname"
2182 * name->m_name set to a package-qualified name,
2183 e.g. "pkgname.spname".
2184 @param cache_only - don't load mysql.proc if not cached
2185 @retval non-NULL - a pointer to an sp_head object
2186 @retval NULL - an error happened.
2187 */
2188
2189 sp_head *
sp_find_package_routine(THD * thd,const LEX_CSTRING pkgname_str,const Database_qualified_name * name,bool cache_only) const2190 Sp_handler::sp_find_package_routine(THD *thd,
2191 const LEX_CSTRING pkgname_str,
2192 const Database_qualified_name *name,
2193 bool cache_only) const
2194 {
2195 DBUG_ENTER("sp_find_package_routine");
2196 Database_qualified_name pkgname(&name->m_db, &pkgname_str);
2197 sp_head *ph= sp_cache_lookup(&thd->sp_package_body_cache, &pkgname);
2198 if (!ph && !cache_only)
2199 sp_handler_package_body.db_find_and_cache_routine(thd, &pkgname, &ph);
2200 if (ph)
2201 {
2202 LEX_CSTRING tmp= name->m_name;
2203 const char *dot= strrchr(tmp.str, '.');
2204 size_t prefix_length= dot ? dot - tmp.str + 1 : 0;
2205 sp_package *pkg= ph->get_package();
2206 tmp.str+= prefix_length;
2207 tmp.length-= prefix_length;
2208 LEX *plex= pkg ? pkg->m_routine_implementations.find(tmp, type()) : NULL;
2209 sp_head *sp= plex ? plex->sphead : NULL;
2210 if (sp)
2211 DBUG_RETURN(sp_clone_and_link_routine(thd, name, sp));
2212 }
2213 DBUG_RETURN(NULL);
2214 }
2215
2216
2217 /**
2218 Find a package routine.
2219 See sp_cache_routine() for more information on parameters and return value.
2220
2221 @param thd - current THD
2222 @param name - Qualified name with the following format:
2223 * name->m_db is set to the database name, e.g. "dbname"
2224 * name->m_name is set to a package-qualified name,
2225 e.g. "pkgname.spname", as a single string with a
2226 dot character as a separator.
2227 @param cache_only - don't load mysql.proc if not cached
2228 @retval non-NULL - a pointer to an sp_head object
2229 @retval NULL - an error happened
2230 */
2231
2232 sp_head *
sp_find_package_routine(THD * thd,const Database_qualified_name * name,bool cache_only) const2233 Sp_handler::sp_find_package_routine(THD *thd,
2234 const Database_qualified_name *name,
2235 bool cache_only) const
2236 {
2237 DBUG_ENTER("Sp_handler::sp_find_package_routine");
2238 Prefix_name_buf pkgname(thd, name->m_name);
2239 DBUG_ASSERT(pkgname.length);
2240 DBUG_RETURN(sp_find_package_routine(thd, pkgname, name, cache_only));
2241 }
2242
2243
2244 /**
2245 This is used by sql_acl.cc:mysql_routine_grant() and is used to find
2246 the routines in 'routines'.
2247
2248 @param thd Thread handler
2249 @param routines List of needles in the hay stack
2250
2251 @return
2252 @retval FALSE Found.
2253 @retval TRUE Not found
2254 */
2255
2256 bool
sp_exist_routines(THD * thd,TABLE_LIST * routines) const2257 Sp_handler::sp_exist_routines(THD *thd, TABLE_LIST *routines) const
2258 {
2259 TABLE_LIST *routine;
2260 bool sp_object_found;
2261 DBUG_ENTER("sp_exists_routine");
2262 for (routine= routines; routine; routine= routine->next_global)
2263 {
2264 sp_name *name;
2265 LEX_CSTRING lex_db;
2266 LEX_CSTRING lex_name;
2267 thd->make_lex_string(&lex_db, routine->db.str, routine->db.length);
2268 thd->make_lex_string(&lex_name, routine->table_name.str,
2269 routine->table_name.length);
2270 name= new sp_name(&lex_db, &lex_name, true);
2271 sp_object_found= sp_find_routine(thd, name, false) != NULL;
2272 thd->get_stmt_da()->clear_warning_info(thd->query_id);
2273 if (! sp_object_found)
2274 {
2275 my_error(ER_SP_DOES_NOT_EXIST, MYF(0), "FUNCTION or PROCEDURE",
2276 routine->table_name.str);
2277 DBUG_RETURN(TRUE);
2278 }
2279 }
2280 DBUG_RETURN(FALSE);
2281 }
2282
2283
sp_sroutine_key(const uchar * ptr,size_t * plen,my_bool first)2284 extern "C" uchar* sp_sroutine_key(const uchar *ptr, size_t *plen,
2285 my_bool first)
2286 {
2287 Sroutine_hash_entry *rn= (Sroutine_hash_entry *)ptr;
2288 *plen= rn->mdl_request.key.length();
2289 return (uchar *)rn->mdl_request.key.ptr();
2290 }
2291
2292
2293 /**
2294 Auxilary function that adds new element to the set of stored routines
2295 used by statement.
2296
2297 In case when statement uses stored routines but does not need
2298 prelocking (i.e. it does not use any tables) we will access the
2299 elements of Query_tables_list::sroutines set on prepared statement
2300 re-execution. Because of this we have to allocate memory for both
2301 hash element and copy of its key in persistent arena.
2302
2303 @param prelocking_ctx Prelocking context of the statement
2304 @param arena Arena in which memory for new element will be
2305 allocated
2306 @param key Key for the hash representing set
2307 @param belong_to_view Uppermost view which uses this routine
2308 (0 if routine is not used by view)
2309
2310 @note
2311 Will also add element to end of 'Query_tables_list::sroutines_list' list.
2312
2313 @todo
2314 When we will got rid of these accesses on re-executions we will be
2315 able to allocate memory for hash elements in non-persitent arena
2316 and directly use key values from sp_head::m_sroutines sets instead
2317 of making their copies.
2318
2319 @retval
2320 TRUE new element was added.
2321 @retval
2322 FALSE element was not added (because it is already present in
2323 the set).
2324 */
2325
sp_add_used_routine(Query_tables_list * prelocking_ctx,Query_arena * arena,const MDL_key * key,const Sp_handler * handler,TABLE_LIST * belong_to_view)2326 bool sp_add_used_routine(Query_tables_list *prelocking_ctx, Query_arena *arena,
2327 const MDL_key *key,
2328 const Sp_handler *handler,
2329 TABLE_LIST *belong_to_view)
2330 {
2331 my_hash_init_opt(PSI_INSTRUMENT_ME, &prelocking_ctx->sroutines, system_charset_info,
2332 Query_tables_list::START_SROUTINES_HASH_SIZE,
2333 0, 0, sp_sroutine_key, 0, 0);
2334
2335 if (!my_hash_search(&prelocking_ctx->sroutines, key->ptr(), key->length()))
2336 {
2337 Sroutine_hash_entry *rn=
2338 (Sroutine_hash_entry *)arena->alloc(sizeof(Sroutine_hash_entry));
2339 if (unlikely(!rn)) // OOM. Error will be reported using fatal_error().
2340 return FALSE;
2341 MDL_REQUEST_INIT_BY_KEY(&rn->mdl_request, key, MDL_SHARED, MDL_TRANSACTION);
2342 if (my_hash_insert(&prelocking_ctx->sroutines, (uchar *)rn))
2343 return FALSE;
2344 prelocking_ctx->sroutines_list.link_in_list(rn, &rn->next);
2345 rn->belong_to_view= belong_to_view;
2346 rn->m_handler= handler;
2347 rn->m_sp_cache_version= 0;
2348 return TRUE;
2349 }
2350 return FALSE;
2351 }
2352
2353
2354 /*
2355 Find and cache a routine in a parser-safe reentrant mode.
2356
2357 If sp_head is not in the cache,
2358 its loaded from mysql.proc, parsed using parse_sql(), and cached.
2359 Note, as it is called from inside parse_sql() itself,
2360 we need to preserve and restore the parser state.
2361
2362 It's used during parsing of CREATE PACKAGE BODY,
2363 to load the corresponding CREATE PACKAGE.
2364 */
2365
2366 int
sp_cache_routine_reentrant(THD * thd,const Database_qualified_name * name,sp_head ** sp) const2367 Sp_handler::sp_cache_routine_reentrant(THD *thd,
2368 const Database_qualified_name *name,
2369 sp_head **sp) const
2370 {
2371 int ret;
2372 Parser_state *oldps= thd->m_parser_state;
2373 thd->m_parser_state= NULL;
2374 ret= sp_cache_routine(thd, name, false, sp);
2375 thd->m_parser_state= oldps;
2376 return ret;
2377 }
2378
2379
2380 /**
2381 Check if a routine has a declaration in the CREATE PACKAGE statement,
2382 by looking up in thd->sp_package_spec_cache, and by loading from mysql.proc
2383 if needed.
2384
2385 @param thd current thd
2386 @param db the database name
2387 @param package the package name
2388 @param name the routine name
2389 @param type the routine type
2390 @retval true, if the routine has a declaration
2391 @retval false, if the routine does not have a declaration
2392
2393 This function can be called in arbitrary context:
2394 - inside a package routine
2395 - inside a standalone routine
2396 - inside a anonymous block
2397 - outside of any routines
2398
2399 The state of the package specification (i.e. the CREATE PACKAGE statement)
2400 for "package" before the call of this function is not known:
2401 it can be cached, or not cached.
2402 After the call of this function, the package specification is always cached,
2403 unless a fatal error happens.
2404 */
2405
2406 static bool
is_package_public_routine(THD * thd,const LEX_CSTRING & db,const LEX_CSTRING & package,const LEX_CSTRING & routine,enum_sp_type type)2407 is_package_public_routine(THD *thd,
2408 const LEX_CSTRING &db,
2409 const LEX_CSTRING &package,
2410 const LEX_CSTRING &routine,
2411 enum_sp_type type)
2412 {
2413 sp_head *sp= NULL;
2414 Database_qualified_name tmp(db, package);
2415 bool ret= sp_handler_package_spec.
2416 sp_cache_routine_reentrant(thd, &tmp, &sp);
2417 sp_package *spec= (!ret && sp) ? sp->get_package() : NULL;
2418 return spec && spec->m_routine_declarations.find(routine, type);
2419 }
2420
2421
2422 /**
2423 Check if a routine has a declaration in the CREATE PACKAGE statement
2424 by looking up in sp_package_spec_cache.
2425
2426 @param thd current thd
2427 @param db the database name
2428 @param pkgname the package name
2429 @param name the routine name
2430 @param type the routine type
2431 @retval true, if the routine has a declaration
2432 @retval false, if the routine does not have a declaration
2433
2434 This function is called in the middle of CREATE PACKAGE BODY parsing,
2435 to lookup the current package routines.
2436 The package specification (i.e. the CREATE PACKAGE statement) for
2437 the current package body must already be loaded and cached at this point.
2438 */
2439
2440 static bool
is_package_public_routine_quick(THD * thd,const LEX_CSTRING & db,const LEX_CSTRING & pkgname,const LEX_CSTRING & name,enum_sp_type type)2441 is_package_public_routine_quick(THD *thd,
2442 const LEX_CSTRING &db,
2443 const LEX_CSTRING &pkgname,
2444 const LEX_CSTRING &name,
2445 enum_sp_type type)
2446 {
2447 Database_qualified_name tmp(db, pkgname);
2448 sp_head *sp= sp_cache_lookup(&thd->sp_package_spec_cache, &tmp);
2449 sp_package *pkg= sp ? sp->get_package() : NULL;
2450 DBUG_ASSERT(pkg); // Must already be cached
2451 return pkg && pkg->m_routine_declarations.find(name, type);
2452 }
2453
2454
2455 /**
2456 Check if a qualified name, e.g. "CALL name1.name2",
2457 refers to a known routine in the package body "pkg".
2458 */
2459
2460 static bool
is_package_body_routine(THD * thd,sp_package * pkg,const LEX_CSTRING & name1,const LEX_CSTRING & name2,enum_sp_type type)2461 is_package_body_routine(THD *thd, sp_package *pkg,
2462 const LEX_CSTRING &name1,
2463 const LEX_CSTRING &name2,
2464 enum_sp_type type)
2465 {
2466 return Sp_handler::eq_routine_name(pkg->m_name, name1) &&
2467 (pkg->m_routine_declarations.find(name2, type) ||
2468 pkg->m_routine_implementations.find(name2, type));
2469 }
2470
2471
2472 /**
2473 Resolve a qualified routine reference xxx.yyy(), between:
2474 - A standalone routine: xxx.yyy
2475 - A package routine: current_database.xxx.yyy
2476 */
2477
2478 bool Sp_handler::
sp_resolve_package_routine_explicit(THD * thd,sp_head * caller,sp_name * name,const Sp_handler ** pkg_routine_handler,Database_qualified_name * pkgname) const2479 sp_resolve_package_routine_explicit(THD *thd,
2480 sp_head *caller,
2481 sp_name *name,
2482 const Sp_handler **pkg_routine_handler,
2483 Database_qualified_name *pkgname) const
2484 {
2485 sp_package *pkg;
2486
2487 /*
2488 If a qualified routine name was used, e.g. xxx.yyy(),
2489 we possibly have a call to a package routine.
2490 Rewrite name if name->m_db (xxx) is a known package,
2491 and name->m_name (yyy) is a known routine in this package.
2492 */
2493 LEX_CSTRING tmpdb= thd->db;
2494 if (is_package_public_routine(thd, tmpdb, name->m_db, name->m_name, type()) ||
2495 // Check if a package routine calls a private routine
2496 (caller && caller->m_parent &&
2497 is_package_body_routine(thd, caller->m_parent,
2498 name->m_db, name->m_name, type())) ||
2499 // Check if a package initialization sections calls a private routine
2500 (caller && (pkg= caller->get_package()) &&
2501 is_package_body_routine(thd, pkg, name->m_db, name->m_name, type())))
2502 {
2503 pkgname->m_db= tmpdb;
2504 pkgname->m_name= name->m_db;
2505 *pkg_routine_handler= package_routine_handler();
2506 return name->make_package_routine_name(thd->mem_root, tmpdb,
2507 name->m_db, name->m_name);
2508 }
2509 return false;
2510 }
2511
2512
2513 /**
2514 Resolve a non-qualified routine reference yyy(), between:
2515 - A standalone routine: current_database.yyy
2516 - A package routine: current_database.current_package.yyy
2517 */
2518
2519 bool Sp_handler::
sp_resolve_package_routine_implicit(THD * thd,sp_head * caller,sp_name * name,const Sp_handler ** pkg_routine_handler,Database_qualified_name * pkgname) const2520 sp_resolve_package_routine_implicit(THD *thd,
2521 sp_head *caller,
2522 sp_name *name,
2523 const Sp_handler **pkg_routine_handler,
2524 Database_qualified_name *pkgname) const
2525 {
2526 sp_package *pkg;
2527
2528 if (!caller || !caller->m_name.length)
2529 {
2530 /*
2531 We are either in a an anonymous block,
2532 or not in a routine at all.
2533 */
2534 return false; // A standalone routine is called
2535 }
2536
2537 if (caller->m_parent)
2538 {
2539 // A package routine calls a non-qualified routine
2540 int ret= SP_OK;
2541 Prefix_name_buf pkgstr(thd, caller->m_name);
2542 DBUG_ASSERT(pkgstr.length);
2543 LEX_CSTRING tmpname; // Non-qualified m_name
2544 tmpname.str= caller->m_name.str + pkgstr.length + 1;
2545 tmpname.length= caller->m_name.length - pkgstr.length - 1;
2546
2547 /*
2548 We're here if a package routine calls another non-qualified
2549 function or procedure, e.g. yyy().
2550 We need to distinguish two cases:
2551 - yyy() is another routine from the same package
2552 - yyy() is a standalone routine from the same database
2553 To detect if yyy() is a package (rather than a standalone) routine,
2554 we check if:
2555 - yyy() recursively calls itself
2556 - yyy() is earlier implemented in the current CREATE PACKAGE BODY
2557 - yyy() has a forward declaration
2558 - yyy() is declared in the corresponding CREATE PACKAGE
2559 */
2560 if (eq_routine_name(tmpname, name->m_name) ||
2561 caller->m_parent->m_routine_implementations.find(name->m_name, type()) ||
2562 caller->m_parent->m_routine_declarations.find(name->m_name, type()) ||
2563 is_package_public_routine_quick(thd, caller->m_db,
2564 pkgstr, name->m_name, type()))
2565 {
2566 DBUG_ASSERT(ret == SP_OK);
2567 pkgname->copy(thd->mem_root, caller->m_db, pkgstr);
2568 *pkg_routine_handler= package_routine_handler();
2569 if (name->make_package_routine_name(thd->mem_root, pkgstr, name->m_name))
2570 return true;
2571 }
2572 return ret != SP_OK;
2573 }
2574
2575 if ((pkg= caller->get_package()) &&
2576 pkg->m_routine_implementations.find(name->m_name, type()))
2577 {
2578 pkgname->m_db= caller->m_db;
2579 pkgname->m_name= caller->m_name;
2580 // Package initialization section is calling a non-qualified routine
2581 *pkg_routine_handler= package_routine_handler();
2582 return name->make_package_routine_name(thd->mem_root,
2583 caller->m_name, name->m_name);
2584 }
2585
2586 return false; // A standalone routine is called
2587
2588 }
2589
2590
2591 /**
2592 Detect cases when a package routine (rather than a standalone routine)
2593 is called, and rewrite sp_name accordingly.
2594
2595 @param thd Current thd
2596 @param caller The caller routine (or NULL if outside of a routine)
2597 @param [IN/OUT] name The called routine name
2598 @param [OUT] pkgname If the routine is found to be a package routine,
2599 pkgname is populated with the package name.
2600 Otherwise, it's not touched.
2601 @retval false on success
2602 @retval true on error (e.g. EOM, could not read CREATE PACKAGE)
2603 */
2604
2605 bool
sp_resolve_package_routine(THD * thd,sp_head * caller,sp_name * name,const Sp_handler ** pkg_routine_handler,Database_qualified_name * pkgname) const2606 Sp_handler::sp_resolve_package_routine(THD *thd,
2607 sp_head *caller,
2608 sp_name *name,
2609 const Sp_handler **pkg_routine_handler,
2610 Database_qualified_name *pkgname) const
2611 {
2612 if (!thd->db.length || !(thd->variables.sql_mode & MODE_ORACLE))
2613 return false;
2614
2615 return name->m_explicit_name ?
2616 sp_resolve_package_routine_explicit(thd, caller, name,
2617 pkg_routine_handler, pkgname) :
2618 sp_resolve_package_routine_implicit(thd, caller, name,
2619 pkg_routine_handler, pkgname);
2620 }
2621
2622
2623 /**
2624 Add routine which is explicitly used by statement to the set of stored
2625 routines used by this statement.
2626
2627 To be friendly towards prepared statements one should pass
2628 persistent arena as second argument.
2629
2630 @param prelocking_ctx Prelocking context of the statement
2631 @param arena Arena in which memory for new element of the set
2632 will be allocated
2633 @param rt Routine name
2634
2635 @note
2636 Will also add element to end of 'Query_tables_list::sroutines_list' list
2637 (and will take into account that this is an explicitly used routine).
2638 */
2639
add_used_routine(Query_tables_list * prelocking_ctx,Query_arena * arena,const Database_qualified_name * rt) const2640 void Sp_handler::add_used_routine(Query_tables_list *prelocking_ctx,
2641 Query_arena *arena,
2642 const Database_qualified_name *rt) const
2643 {
2644 MDL_key key(get_mdl_type(), rt->m_db.str, rt->m_name.str);
2645 (void) sp_add_used_routine(prelocking_ctx, arena, &key, this, 0);
2646 prelocking_ctx->sroutines_list_own_last= prelocking_ctx->sroutines_list.next;
2647 prelocking_ctx->sroutines_list_own_elements=
2648 prelocking_ctx->sroutines_list.elements;
2649 }
2650
2651
2652 /**
2653 Remove routines which are only indirectly used by statement from
2654 the set of routines used by this statement.
2655
2656 @param prelocking_ctx Prelocking context of the statement
2657 */
2658
sp_remove_not_own_routines(Query_tables_list * prelocking_ctx)2659 void sp_remove_not_own_routines(Query_tables_list *prelocking_ctx)
2660 {
2661 Sroutine_hash_entry *not_own_rt, *next_rt;
2662 for (not_own_rt= *prelocking_ctx->sroutines_list_own_last;
2663 not_own_rt; not_own_rt= next_rt)
2664 {
2665 /*
2666 It is safe to obtain not_own_rt->next after calling hash_delete() now
2667 but we want to be more future-proof.
2668 */
2669 next_rt= not_own_rt->next;
2670 my_hash_delete(&prelocking_ctx->sroutines, (uchar *)not_own_rt);
2671 }
2672
2673 *prelocking_ctx->sroutines_list_own_last= NULL;
2674 prelocking_ctx->sroutines_list.next= prelocking_ctx->sroutines_list_own_last;
2675 prelocking_ctx->sroutines_list.elements=
2676 prelocking_ctx->sroutines_list_own_elements;
2677 }
2678
2679
2680 /**
2681 Merge contents of two hashes representing sets of routines used
2682 by statements or by other routines.
2683
2684 @param dst hash to which elements should be added
2685 @param src hash from which elements merged
2686
2687 @note
2688 This procedure won't create new Sroutine_hash_entry objects,
2689 instead it will simply add elements from source to destination
2690 hash. Thus time of life of elements in destination hash becomes
2691 dependant on time of life of elements from source hash. It also
2692 won't touch lists linking elements in source and destination
2693 hashes.
2694
2695 @returns
2696 @return TRUE Failure
2697 @return FALSE Success
2698 */
2699
sp_update_sp_used_routines(HASH * dst,HASH * src)2700 bool sp_update_sp_used_routines(HASH *dst, HASH *src)
2701 {
2702 for (uint i=0 ; i < src->records ; i++)
2703 {
2704 Sroutine_hash_entry *rt= (Sroutine_hash_entry *)my_hash_element(src, i);
2705 if (!my_hash_search(dst, (uchar *)rt->mdl_request.key.ptr(),
2706 rt->mdl_request.key.length()))
2707 {
2708 if (my_hash_insert(dst, (uchar *)rt))
2709 return TRUE;
2710 }
2711 }
2712 return FALSE;
2713 }
2714
2715
2716 /**
2717 Add contents of hash representing set of routines to the set of
2718 routines used by statement.
2719
2720 @param thd Thread context
2721 @param prelocking_ctx Prelocking context of the statement
2722 @param src Hash representing set from which routines will
2723 be added
2724 @param belong_to_view Uppermost view which uses these routines, 0 if none
2725
2726 @note It will also add elements to end of
2727 'Query_tables_list::sroutines_list' list.
2728 */
2729
2730 void
sp_update_stmt_used_routines(THD * thd,Query_tables_list * prelocking_ctx,HASH * src,TABLE_LIST * belong_to_view)2731 sp_update_stmt_used_routines(THD *thd, Query_tables_list *prelocking_ctx,
2732 HASH *src, TABLE_LIST *belong_to_view)
2733 {
2734 for (uint i=0 ; i < src->records ; i++)
2735 {
2736 Sroutine_hash_entry *rt= (Sroutine_hash_entry *)my_hash_element(src, i);
2737 (void)sp_add_used_routine(prelocking_ctx, thd->stmt_arena,
2738 &rt->mdl_request.key, rt->m_handler,
2739 belong_to_view);
2740 }
2741 }
2742
2743
2744 /**
2745 Add contents of list representing set of routines to the set of
2746 routines used by statement.
2747
2748 @param thd Thread context
2749 @param prelocking_ctx Prelocking context of the statement
2750 @param src List representing set from which routines will
2751 be added
2752 @param belong_to_view Uppermost view which uses these routines, 0 if none
2753
2754 @note It will also add elements to end of
2755 'Query_tables_list::sroutines_list' list.
2756 */
2757
sp_update_stmt_used_routines(THD * thd,Query_tables_list * prelocking_ctx,SQL_I_List<Sroutine_hash_entry> * src,TABLE_LIST * belong_to_view)2758 void sp_update_stmt_used_routines(THD *thd, Query_tables_list *prelocking_ctx,
2759 SQL_I_List<Sroutine_hash_entry> *src,
2760 TABLE_LIST *belong_to_view)
2761 {
2762 for (Sroutine_hash_entry *rt= src->first; rt; rt= rt->next)
2763 (void)sp_add_used_routine(prelocking_ctx, thd->stmt_arena,
2764 &rt->mdl_request.key, rt->m_handler,
2765 belong_to_view);
2766 }
2767
2768
2769 /**
2770 A helper wrapper around sp_cache_routine() to use from
2771 prelocking until 'sp_name' is eradicated as a class.
2772 */
2773
sp_cache_routine(THD * thd,bool lookup_only,sp_head ** sp) const2774 int Sroutine_hash_entry::sp_cache_routine(THD *thd,
2775 bool lookup_only,
2776 sp_head **sp) const
2777 {
2778 char qname_buff[NAME_LEN*2+1+1];
2779 sp_name name(&mdl_request.key, qname_buff);
2780 /*
2781 Check that we have an MDL lock on this routine, unless it's a top-level
2782 CALL. The assert below should be unambiguous: the first element
2783 in sroutines_list has an MDL lock unless it's a top-level call, or a
2784 trigger, but triggers can't occur here (see the preceding assert).
2785 */
2786 DBUG_ASSERT(mdl_request.ticket || this == thd->lex->sroutines_list.first);
2787
2788 return m_handler->sp_cache_routine(thd, &name, lookup_only, sp);
2789 }
2790
2791
2792 /**
2793 Ensure that routine is present in cache by loading it from the mysql.proc
2794 table if needed. If the routine is present but old, reload it.
2795 Emit an appropriate error if there was a problem during
2796 loading.
2797
2798 @param[in] thd Thread context.
2799 @param[in] name Name of routine.
2800 @param[in] lookup_only Only check that the routine is in the cache.
2801 If it's not, don't try to load. If it is present,
2802 but old, don't try to reload.
2803 @param[out] sp Pointer to sp_head object for routine, NULL if routine was
2804 not found.
2805
2806 @retval 0 Either routine is found and was successfully loaded into cache
2807 or it does not exist.
2808 @retval non-0 Error while loading routine from mysql,proc table.
2809 */
2810
sp_cache_routine(THD * thd,const Database_qualified_name * name,bool lookup_only,sp_head ** sp) const2811 int Sp_handler::sp_cache_routine(THD *thd,
2812 const Database_qualified_name *name,
2813 bool lookup_only,
2814 sp_head **sp) const
2815 {
2816 int ret= 0;
2817 sp_cache **spc= get_cache(thd);
2818
2819 DBUG_ENTER("Sp_handler::sp_cache_routine");
2820
2821 DBUG_ASSERT(spc);
2822
2823 *sp= sp_cache_lookup(spc, name);
2824
2825 if (lookup_only)
2826 DBUG_RETURN(SP_OK);
2827
2828 if (*sp)
2829 {
2830 sp_cache_flush_obsolete(spc, sp);
2831 if (*sp)
2832 DBUG_RETURN(SP_OK);
2833 }
2834
2835 switch ((ret= db_find_and_cache_routine(thd, name, sp)))
2836 {
2837 case SP_OK:
2838 break;
2839 case SP_KEY_NOT_FOUND:
2840 ret= SP_OK;
2841 break;
2842 default:
2843 /* Query might have been killed, don't set error. */
2844 if (thd->killed)
2845 break;
2846 /*
2847 Any error when loading an existing routine is either some problem
2848 with the mysql.proc table, or a parse error because the contents
2849 has been tampered with (in which case we clear that error).
2850 */
2851 if (ret == SP_PARSE_ERROR)
2852 thd->clear_error();
2853 /*
2854 If we cleared the parse error, or when db_find_routine() flagged
2855 an error with it's return value without calling my_error(), we
2856 set the generic "mysql.proc table corrupt" error here.
2857 */
2858 if (!thd->is_error())
2859 {
2860 my_error(ER_SP_PROC_TABLE_CORRUPT, MYF(0),
2861 ErrConvDQName(name).ptr(), ret);
2862 }
2863 break;
2864 }
2865 DBUG_RETURN(ret);
2866 }
2867
2868
2869 /**
2870 Cache a package routine using its package name and a qualified name.
2871 See sp_cache_routine() for more information on parameters and return values.
2872
2873 @param thd - current THD
2874 @param pkgname_str - package name, e.g. "pkgname"
2875 @param name - name with the following format:
2876 * name->m_db is a database name, e.g. "dbname"
2877 * name->m_name is a package-qualified name,
2878 e.g. "pkgname.spname"
2879 @param lookup_only - don't load mysql.proc if not cached
2880 @param [OUT] sp - the result is returned here.
2881 @retval false - loaded or does not exists
2882 @retval true - error while loading mysql.proc
2883 */
2884
2885 int
sp_cache_package_routine(THD * thd,const LEX_CSTRING & pkgname_cstr,const Database_qualified_name * name,bool lookup_only,sp_head ** sp) const2886 Sp_handler::sp_cache_package_routine(THD *thd,
2887 const LEX_CSTRING &pkgname_cstr,
2888 const Database_qualified_name *name,
2889 bool lookup_only, sp_head **sp) const
2890 {
2891 DBUG_ENTER("sp_cache_package_routine");
2892 DBUG_ASSERT(type() == SP_TYPE_FUNCTION || type() == SP_TYPE_PROCEDURE);
2893 sp_name pkgname(&name->m_db, &pkgname_cstr, false);
2894 sp_head *ph= NULL;
2895 int ret= sp_handler_package_body.sp_cache_routine(thd, &pkgname,
2896 lookup_only,
2897 &ph);
2898 if (!ret)
2899 {
2900 sp_package *pkg= ph ? ph->get_package() : NULL;
2901 LEX_CSTRING tmp= name->m_name;
2902 const char *dot= strrchr(tmp.str, '.');
2903 size_t prefix_length= dot ? dot - tmp.str + 1 : 0;
2904 tmp.str+= prefix_length;
2905 tmp.length-= prefix_length;
2906 LEX *rlex= pkg ? pkg->m_routine_implementations.find(tmp, type()) : NULL;
2907 *sp= rlex ? rlex->sphead : NULL;
2908 }
2909
2910 DBUG_RETURN(ret);
2911 }
2912
2913
2914 /**
2915 Cache a package routine by its fully qualified name.
2916 See sp_cache_routine() for more information on parameters and return values.
2917
2918 @param thd - current THD
2919 @param name - name with the following format:
2920 * name->m_db is a database name, e.g. "dbname"
2921 * name->m_name is a package-qualified name,
2922 e.g. "pkgname.spname"
2923 @param lookup_only - don't load mysql.proc if not cached
2924 @param [OUT] sp - the result is returned here
2925 @retval false - loaded or does not exists
2926 @retval true - error while loading mysql.proc
2927 */
2928
sp_cache_package_routine(THD * thd,const Database_qualified_name * name,bool lookup_only,sp_head ** sp) const2929 int Sp_handler::sp_cache_package_routine(THD *thd,
2930 const Database_qualified_name *name,
2931 bool lookup_only, sp_head **sp) const
2932 {
2933 DBUG_ENTER("Sp_handler::sp_cache_package_routine");
2934 Prefix_name_buf pkgname(thd, name->m_name);
2935 DBUG_ASSERT(pkgname.length);
2936 DBUG_RETURN(sp_cache_package_routine(thd, pkgname, name, lookup_only, sp));
2937 }
2938
2939
2940 /**
2941 Generates the CREATE... string from the table information.
2942
2943 @return
2944 Returns false on success, true on (alloc) failure.
2945 */
2946
2947 bool
show_create_sp(THD * thd,String * buf,const LEX_CSTRING & db,const LEX_CSTRING & name,const LEX_CSTRING & params,const LEX_CSTRING & returns,const LEX_CSTRING & body,const st_sp_chistics & chistics,const AUTHID & definer,const DDL_options_st ddl_options,sql_mode_t sql_mode) const2948 Sp_handler::show_create_sp(THD *thd, String *buf,
2949 const LEX_CSTRING &db,
2950 const LEX_CSTRING &name,
2951 const LEX_CSTRING ¶ms,
2952 const LEX_CSTRING &returns,
2953 const LEX_CSTRING &body,
2954 const st_sp_chistics &chistics,
2955 const AUTHID &definer,
2956 const DDL_options_st ddl_options,
2957 sql_mode_t sql_mode) const
2958 {
2959 size_t agglen= (chistics.agg_type == GROUP_AGGREGATE)? 10 : 0;
2960 LEX_CSTRING tmp;
2961
2962 /* Make some room to begin with */
2963 if (buf->alloc(100 + db.length + 1 + name.length +
2964 params.length + returns.length +
2965 chistics.comment.length + 10 /* length of " DEFINER= "*/ +
2966 agglen + USER_HOST_BUFF_SIZE))
2967 return true;
2968
2969 Sql_mode_instant_set sms(thd, sql_mode);
2970 buf->append(STRING_WITH_LEN("CREATE "));
2971 if (ddl_options.or_replace())
2972 buf->append(STRING_WITH_LEN("OR REPLACE "));
2973 append_definer(thd, buf, &definer.user, &definer.host);
2974 if (chistics.agg_type == GROUP_AGGREGATE)
2975 buf->append(STRING_WITH_LEN("AGGREGATE "));
2976 tmp= type_lex_cstring();
2977 buf->append(&tmp);
2978 buf->append(STRING_WITH_LEN(" "));
2979 if (ddl_options.if_not_exists())
2980 buf->append(STRING_WITH_LEN("IF NOT EXISTS "));
2981
2982 if (db.length > 0)
2983 {
2984 append_identifier(thd, buf, &db);
2985 buf->append('.');
2986 }
2987 append_identifier(thd, buf, &name);
2988 buf->append('(');
2989 buf->append(¶ms);
2990 buf->append(')');
2991 if (type() == SP_TYPE_FUNCTION)
2992 {
2993 if (sql_mode & MODE_ORACLE)
2994 buf->append(STRING_WITH_LEN(" RETURN "));
2995 else
2996 buf->append(STRING_WITH_LEN(" RETURNS "));
2997 buf->append(returns.str, returns.length); // Not \0 terminated
2998 }
2999 buf->append('\n');
3000 switch (chistics.daccess) {
3001 case SP_NO_SQL:
3002 buf->append(STRING_WITH_LEN(" NO SQL\n"));
3003 break;
3004 case SP_READS_SQL_DATA:
3005 buf->append(STRING_WITH_LEN(" READS SQL DATA\n"));
3006 break;
3007 case SP_MODIFIES_SQL_DATA:
3008 buf->append(STRING_WITH_LEN(" MODIFIES SQL DATA\n"));
3009 break;
3010 case SP_DEFAULT_ACCESS:
3011 case SP_CONTAINS_SQL:
3012 /* Do nothing */
3013 break;
3014 }
3015 if (chistics.detistic)
3016 buf->append(STRING_WITH_LEN(" DETERMINISTIC\n"));
3017 append_suid(buf, chistics.suid);
3018 append_comment(buf, chistics.comment);
3019 buf->append(body.str, body.length); // Not \0 terminated
3020 return false;
3021 }
3022
3023
3024 /**
3025 @brief The function loads sp_head struct for information schema purposes
3026 (used for I_S ROUTINES & PARAMETERS tables).
3027
3028 @param[in] thd thread handler
3029 @param[in] proc_table mysql.proc table structurte
3030 @param[in] db database name
3031 @param[in] name sp name
3032 @param[in] sql_mode SQL mode
3033 @param[in] type Routine type
3034 @param[in] returns 'returns' string
3035 @param[in] params parameters definition string
3036 @param[out] free_sp_head returns 1 if we need to free sp_head struct
3037 otherwise returns 0
3038
3039 @return Pointer on sp_head struct
3040 @retval # Pointer on sp_head struct
3041 @retval 0 error
3042 */
3043
3044 sp_head *
sp_load_for_information_schema(THD * thd,TABLE * proc_table,const LEX_CSTRING & db,const LEX_CSTRING & name,const LEX_CSTRING & params,const LEX_CSTRING & returns,sql_mode_t sql_mode,bool * free_sp_head) const3045 Sp_handler::sp_load_for_information_schema(THD *thd, TABLE *proc_table,
3046 const LEX_CSTRING &db,
3047 const LEX_CSTRING &name,
3048 const LEX_CSTRING ¶ms,
3049 const LEX_CSTRING &returns,
3050 sql_mode_t sql_mode,
3051 bool *free_sp_head) const
3052 {
3053 String defstr;
3054 const AUTHID definer= {{STRING_WITH_LEN("")}, {STRING_WITH_LEN("")}};
3055 sp_head *sp;
3056 sp_cache **spc= get_cache(thd);
3057 sp_name sp_name_obj(&db, &name, true); // This can change "name"
3058 *free_sp_head= 0;
3059 if ((sp= sp_cache_lookup(spc, &sp_name_obj)))
3060 {
3061 return sp;
3062 }
3063
3064 LEX *old_lex= thd->lex, newlex;
3065 Stored_program_creation_ctx *creation_ctx=
3066 Stored_routine_creation_ctx::load_from_db(thd, &sp_name_obj, proc_table);
3067 defstr.set_charset(creation_ctx->get_client_cs());
3068 if (show_create_sp(thd, &defstr,
3069 sp_name_obj.m_db, sp_name_obj.m_name,
3070 params, returns, empty_body_lex_cstring(sql_mode),
3071 Sp_chistics(), definer, DDL_options(), sql_mode))
3072 return 0;
3073
3074 thd->lex= &newlex;
3075 newlex.current_select= NULL;
3076 sp= sp_compile(thd, &defstr, sql_mode, NULL, creation_ctx);
3077 *free_sp_head= 1;
3078 thd->lex->sphead= NULL;
3079 lex_end(thd->lex);
3080 thd->lex= old_lex;
3081 return sp;
3082 }
3083
3084
empty_body_lex_cstring(sql_mode_t mode) const3085 LEX_CSTRING Sp_handler_procedure::empty_body_lex_cstring(sql_mode_t mode) const
3086 {
3087 static LEX_CSTRING m_empty_body_std= {STRING_WITH_LEN("BEGIN END")};
3088 static LEX_CSTRING m_empty_body_ora= {STRING_WITH_LEN("AS BEGIN NULL; END")};
3089 return mode & MODE_ORACLE ? m_empty_body_ora : m_empty_body_std;
3090 }
3091
3092
empty_body_lex_cstring(sql_mode_t mode) const3093 LEX_CSTRING Sp_handler_function::empty_body_lex_cstring(sql_mode_t mode) const
3094 {
3095 static LEX_CSTRING m_empty_body_std= {STRING_WITH_LEN("RETURN NULL")};
3096 static LEX_CSTRING m_empty_body_ora= {STRING_WITH_LEN("AS BEGIN RETURN NULL; END")};
3097 return mode & MODE_ORACLE ? m_empty_body_ora : m_empty_body_std;
3098 }
3099