1 /*
2 Copyright (c) 2002, 2021, Oracle and/or its affiliates.
3
4 This program is free software; you can redistribute it and/or modify
5 it under the terms of the GNU General Public License, version 2.0,
6 as published by the Free Software Foundation.
7
8 This program is also distributed with certain software (including
9 but not limited to OpenSSL) that is licensed under separate terms,
10 as designated in a particular file or component or in included license
11 documentation. The authors of MySQL hereby grant you an additional
12 permission to link the program and your derivative works with the
13 separately licensed software that they have included with MySQL.
14
15 This program is distributed in the hope that it will be useful,
16 but WITHOUT ANY WARRANTY; without even the implied warranty of
17 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
18 GNU General Public License, version 2.0, for more details.
19
20 You should have received a copy of the GNU General Public License
21 along with this program; if not, write to the Free Software
22 Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA
23 */
24
25 #include "sp.h"
26
27 #include "my_user.h" // parse_user
28 #include "mysql/psi/mysql_sp.h"
29 #include "binlog.h" // mysql_bin_log
30 #include "item_timefunc.h"// Item_func_now_local
31 #include "key.h" // key_copy
32 #include "lock.h" // lock_object_name
33 #include "log.h" // sql_print_warning
34 #include "log_event.h" // append_query_string
35 #include "sp_cache.h" // sp_cache_invalidate
36 #include "sp_head.h" // Stored_program_creation_ctx
37 #include "sql_base.h" // close_thread_tables
38 #include "sql_db.h" // get_default_db_collation
39 #include "sql_parse.h" // parse_sql
40 #include "sql_show.h" // append_identifier
41 #include "sql_table.h" // write_bin_log
42
43 /* Used in error handling only */
44 #define SP_TYPE_STRING(LP) \
45 ((LP)->sphead->m_type == SP_TYPE_FUNCTION ? "FUNCTION" : "PROCEDURE")
46 static bool
47 create_string(THD *thd, String *buf,
48 enum_sp_type sp_type,
49 const char *db, size_t dblen,
50 const char *name, size_t namelen,
51 const char *params, size_t paramslen,
52 const char *returns, size_t returnslen,
53 const char *body, size_t bodylen,
54 st_sp_chistics *chistics,
55 const LEX_CSTRING &definer_user,
56 const LEX_CSTRING &definer_host,
57 sql_mode_t sql_mode);
58
59 static int
60 db_load_routine(THD *thd, enum_sp_type type, sp_name *name, sp_head **sphp,
61 sql_mode_t sql_mode, const char *params, const char *returns,
62 const char *body, st_sp_chistics &chistics,
63 const char *definer, longlong created, longlong modified,
64 Stored_program_creation_ctx *creation_ctx);
65
66 static const
67 TABLE_FIELD_TYPE proc_table_fields[MYSQL_PROC_FIELD_COUNT] =
68 {
69 {
70 { C_STRING_WITH_LEN("db") },
71 { C_STRING_WITH_LEN("char(64)") },
72 { C_STRING_WITH_LEN("utf8") }
73 },
74 {
75 { C_STRING_WITH_LEN("name") },
76 { C_STRING_WITH_LEN("char(64)") },
77 { C_STRING_WITH_LEN("utf8") }
78 },
79 {
80 { C_STRING_WITH_LEN("type") },
81 { C_STRING_WITH_LEN("enum('FUNCTION','PROCEDURE')") },
82 { NULL, 0 }
83 },
84 {
85 { C_STRING_WITH_LEN("specific_name") },
86 { C_STRING_WITH_LEN("char(64)") },
87 { C_STRING_WITH_LEN("utf8") }
88 },
89 {
90 { C_STRING_WITH_LEN("language") },
91 { C_STRING_WITH_LEN("enum('SQL')") },
92 { NULL, 0 }
93 },
94 {
95 { C_STRING_WITH_LEN("sql_data_access") },
96 { C_STRING_WITH_LEN("enum('CONTAINS_SQL','NO_SQL','READS_SQL_DATA','MODIFIES_SQL_DATA')") },
97 { NULL, 0 }
98 },
99 {
100 { C_STRING_WITH_LEN("is_deterministic") },
101 { C_STRING_WITH_LEN("enum('YES','NO')") },
102 { NULL, 0 }
103 },
104 {
105 { C_STRING_WITH_LEN("security_type") },
106 { C_STRING_WITH_LEN("enum('INVOKER','DEFINER')") },
107 { NULL, 0 }
108 },
109 {
110 { C_STRING_WITH_LEN("param_list") },
111 { C_STRING_WITH_LEN("blob") },
112 { NULL, 0 }
113 },
114
115 {
116 { C_STRING_WITH_LEN("returns") },
117 { C_STRING_WITH_LEN("longblob") },
118 { NULL, 0 }
119 },
120 {
121 { C_STRING_WITH_LEN("body") },
122 { C_STRING_WITH_LEN("longblob") },
123 { NULL, 0 }
124 },
125 {
126 { C_STRING_WITH_LEN("definer") },
127 { C_STRING_WITH_LEN("char(93)") },
128 { C_STRING_WITH_LEN("utf8") }
129 },
130 {
131 { C_STRING_WITH_LEN("created") },
132 { C_STRING_WITH_LEN("timestamp") },
133 { NULL, 0 }
134 },
135 {
136 { C_STRING_WITH_LEN("modified") },
137 { C_STRING_WITH_LEN("timestamp") },
138 { NULL, 0 }
139 },
140 {
141 { C_STRING_WITH_LEN("sql_mode") },
142 { C_STRING_WITH_LEN("set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES',"
143 "'IGNORE_SPACE','NOT_USED','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION',"
144 "'NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB',"
145 "'NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40',"
146 "'ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES',"
147 "'STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES',"
148 "'ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER',"
149 "'HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH')") },
150 { NULL, 0 }
151 },
152 {
153 { C_STRING_WITH_LEN("comment") },
154 { C_STRING_WITH_LEN("text") },
155 { C_STRING_WITH_LEN("utf8") }
156 },
157 {
158 { C_STRING_WITH_LEN("character_set_client") },
159 { C_STRING_WITH_LEN("char(32)") },
160 { C_STRING_WITH_LEN("utf8") }
161 },
162 {
163 { C_STRING_WITH_LEN("collation_connection") },
164 { C_STRING_WITH_LEN("char(32)") },
165 { C_STRING_WITH_LEN("utf8") }
166 },
167 {
168 { C_STRING_WITH_LEN("db_collation") },
169 { C_STRING_WITH_LEN("char(32)") },
170 { C_STRING_WITH_LEN("utf8") }
171 },
172 {
173 { C_STRING_WITH_LEN("body_utf8") },
174 { C_STRING_WITH_LEN("longblob") },
175 { NULL, 0 }
176 }
177 };
178
179 static const TABLE_FIELD_DEF
180 proc_table_def= {MYSQL_PROC_FIELD_COUNT, proc_table_fields};
181
182 /*************************************************************************/
183
184 /**
185 Stored_routine_creation_ctx -- creation context of stored routines
186 (stored procedures and functions).
187 */
188
189 class Stored_routine_creation_ctx : public Stored_program_creation_ctx,
190 public Sql_alloc
191 {
192 public:
193 static Stored_routine_creation_ctx *
194 load_from_db(THD *thd, const sp_name *name, TABLE *proc_tbl);
195
196 public:
clone(MEM_ROOT * mem_root)197 virtual Stored_program_creation_ctx *clone(MEM_ROOT *mem_root)
198 {
199 return new (mem_root) Stored_routine_creation_ctx(m_client_cs,
200 m_connection_cl,
201 m_db_cl);
202 }
203
204 protected:
create_backup_ctx(THD * thd) const205 virtual Object_creation_ctx *create_backup_ctx(THD *thd) const
206 {
207 DBUG_ENTER("Stored_routine_creation_ctx::create_backup_ctx");
208 DBUG_RETURN(new Stored_routine_creation_ctx(thd));
209 }
210
211 private:
Stored_routine_creation_ctx(THD * thd)212 Stored_routine_creation_ctx(THD *thd)
213 : Stored_program_creation_ctx(thd)
214 { }
215
Stored_routine_creation_ctx(const CHARSET_INFO * client_cs,const CHARSET_INFO * connection_cl,const CHARSET_INFO * db_cl)216 Stored_routine_creation_ctx(const CHARSET_INFO *client_cs,
217 const CHARSET_INFO *connection_cl,
218 const CHARSET_INFO *db_cl)
219 : Stored_program_creation_ctx(client_cs, connection_cl, db_cl)
220 { }
221 };
222
223 /**************************************************************************
224 Stored_routine_creation_ctx implementation.
225 **************************************************************************/
226
load_charset(MEM_ROOT * mem_root,Field * field,const CHARSET_INFO * dflt_cs,const CHARSET_INFO ** cs)227 bool load_charset(MEM_ROOT *mem_root,
228 Field *field,
229 const CHARSET_INFO *dflt_cs,
230 const CHARSET_INFO **cs)
231 {
232 String cs_name;
233
234 if (get_field(mem_root, field, &cs_name))
235 {
236 *cs= dflt_cs;
237 return TRUE;
238 }
239
240 *cs= get_charset_by_csname(cs_name.c_ptr(), MY_CS_PRIMARY, MYF(0));
241
242 if (*cs == NULL)
243 {
244 *cs= dflt_cs;
245 return TRUE;
246 }
247
248 return FALSE;
249 }
250
251 /*************************************************************************/
252
load_collation(MEM_ROOT * mem_root,Field * field,const CHARSET_INFO * dflt_cl,const CHARSET_INFO ** cl)253 bool load_collation(MEM_ROOT *mem_root,
254 Field *field,
255 const CHARSET_INFO *dflt_cl,
256 const CHARSET_INFO **cl)
257 {
258 String cl_name;
259
260 if (get_field(mem_root, field, &cl_name))
261 {
262 *cl= dflt_cl;
263 return TRUE;
264 }
265
266 *cl= get_charset_by_name(cl_name.c_ptr(), MYF(0));
267
268 if (*cl == NULL)
269 {
270 *cl= dflt_cl;
271 return TRUE;
272 }
273
274 return FALSE;
275 }
276
277 /*************************************************************************/
278
279 Stored_routine_creation_ctx *
load_from_db(THD * thd,const sp_name * name,TABLE * proc_tbl)280 Stored_routine_creation_ctx::load_from_db(THD *thd,
281 const sp_name *name,
282 TABLE *proc_tbl)
283 {
284 /* Load character set/collation attributes. */
285
286 const CHARSET_INFO *client_cs;
287 const CHARSET_INFO *connection_cl;
288 const CHARSET_INFO *db_cl;
289
290 const char *db_name= thd->strmake(name->m_db.str, name->m_db.length);
291 const char *sr_name= thd->strmake(name->m_name.str, name->m_name.length);
292
293 bool invalid_creation_ctx= FALSE;
294
295 if (load_charset(thd->mem_root,
296 proc_tbl->field[MYSQL_PROC_FIELD_CHARACTER_SET_CLIENT],
297 thd->variables.character_set_client,
298 &client_cs))
299 {
300 sql_print_warning("Stored routine '%s'.'%s': invalid value "
301 "in column mysql.proc.character_set_client.",
302 db_name,
303 sr_name);
304
305 invalid_creation_ctx= TRUE;
306 }
307
308 if (load_collation(thd->mem_root,
309 proc_tbl->field[MYSQL_PROC_FIELD_COLLATION_CONNECTION],
310 thd->variables.collation_connection,
311 &connection_cl))
312 {
313 sql_print_warning("Stored routine '%s'.'%s': invalid value "
314 "in column mysql.proc.collation_connection.",
315 db_name,
316 sr_name);
317
318 invalid_creation_ctx= TRUE;
319 }
320
321 if (load_collation(thd->mem_root,
322 proc_tbl->field[MYSQL_PROC_FIELD_DB_COLLATION],
323 NULL,
324 &db_cl))
325 {
326 sql_print_warning("Stored routine '%s'.'%s': invalid value "
327 "in column mysql.proc.db_collation.",
328 db_name,
329 sr_name);
330
331 invalid_creation_ctx= TRUE;
332 }
333
334 if (invalid_creation_ctx)
335 {
336 push_warning_printf(thd,
337 Sql_condition::SL_WARNING,
338 ER_SR_INVALID_CREATION_CTX,
339 ER(ER_SR_INVALID_CREATION_CTX),
340 db_name,
341 sr_name);
342 }
343
344 /*
345 If we failed to retrieve the database collation, load the default one
346 from the disk.
347 */
348
349 if (!db_cl)
350 db_cl= get_default_db_collation(thd, name->m_db.str);
351
352 /* Create the context. */
353
354 return new Stored_routine_creation_ctx(client_cs, connection_cl, db_cl);
355 }
356
357 /*************************************************************************/
358
359 class Proc_table_intact : public Table_check_intact
360 {
361 private:
362 bool m_print_once;
363 bool silence_error;
364
365 public:
Proc_table_intact()366 Proc_table_intact() : m_print_once(TRUE), silence_error(FALSE)
367 { has_keys= TRUE; }
368
369 my_bool check_proc_table(TABLE *table);
370
371 protected:
372 void report_error(uint code, const char *fmt, ...);
373 };
374
375
376 /**
377 Report failure to validate the mysql.proc table definition.
378 Print a message to the error log only once.
379 */
380
report_error(uint code,const char * fmt,...)381 void Proc_table_intact::report_error(uint code, const char *fmt, ...)
382 {
383 va_list args;
384 char buf[512];
385
386 if(silence_error == TRUE)
387 return;
388
389 va_start(args, fmt);
390 my_vsnprintf(buf, sizeof(buf), fmt, args);
391 va_end(args);
392
393 if (code == ER_COL_COUNT_DOESNT_MATCH_CORRUPTED_V2)
394 my_message(code, buf, MYF(0));
395 else
396 my_error(ER_CANNOT_LOAD_FROM_TABLE_V2, MYF(0), "mysql", "proc");
397
398 if (m_print_once)
399 {
400 m_print_once= FALSE;
401 sql_print_error("%s", buf);
402 }
403 };
404
check_proc_table(TABLE * table)405 my_bool Proc_table_intact::check_proc_table(TABLE *table)
406 {
407 silence_error= TRUE;
408 my_bool error= check(table, &proc_table_def);
409 silence_error= FALSE;
410 if (!error)
411 return FALSE;
412
413 //This could have failed because of definer column being 77 characters long
414 uint32 original_definer_length= table->field[MYSQL_PROC_FIELD_DEFINER]->field_length;
415 table->field[MYSQL_PROC_FIELD_DEFINER]->field_length=
416 (USERNAME_CHAR_LENGTH + HOSTNAME_LENGTH + 1) *
417 table->field[MYSQL_PROC_FIELD_DEFINER]->charset()->mbmaxlen;
418
419 error= check(table, &proc_table_def);
420
421 table->field[MYSQL_PROC_FIELD_DEFINER]->field_length= original_definer_length;
422
423 return error;
424 }
425
426 /** Single instance used to control printing to the error log. */
427 static Proc_table_intact proc_table_intact;
428
429
430 /**
431 Open the mysql.proc table for read.
432
433 @param thd Thread context
434 @param backup Pointer to Open_tables_state instance where information about
435 currently open tables will be saved, and from which will be
436 restored when we will end work with mysql.proc.
437
438 @retval
439 0 Error
440 @retval
441 \# Pointer to TABLE object of mysql.proc
442 */
443
open_proc_table_for_read(THD * thd,Open_tables_backup * backup)444 TABLE *open_proc_table_for_read(THD *thd, Open_tables_backup *backup)
445 {
446 TABLE_LIST table;
447
448 DBUG_ENTER("open_proc_table_for_read");
449
450 table.init_one_table("mysql", 5, "proc", 4, "proc", TL_READ);
451
452 if (open_nontrans_system_tables_for_read(thd, &table, backup))
453 DBUG_RETURN(NULL);
454
455 if(!proc_table_intact.check_proc_table(table.table))
456 DBUG_RETURN(table.table);
457
458 close_nontrans_system_tables(thd, backup);
459 DBUG_RETURN(NULL);
460 }
461
462
463 /**
464 Open the mysql.proc table for update.
465
466 @param thd Thread context
467
468 @note
469 Table opened with this call should closed using close_thread_tables().
470
471 @retval
472 0 Error
473 @retval
474 \# Pointer to TABLE object of mysql.proc
475 */
476
open_proc_table_for_update(THD * thd)477 static TABLE *open_proc_table_for_update(THD *thd)
478 {
479 TABLE_LIST table_list;
480 TABLE *table;
481 MDL_savepoint mdl_savepoint= thd->mdl_context.mdl_savepoint();
482 DBUG_ENTER("open_proc_table_for_update");
483
484 table_list.init_one_table("mysql", 5, "proc", 4, "proc", TL_WRITE);
485
486 if (!(table= open_system_table_for_update(thd, &table_list)))
487 DBUG_RETURN(NULL);
488
489 if(!proc_table_intact.check_proc_table(table))
490 DBUG_RETURN(table);
491
492 close_thread_tables(thd);
493 thd->mdl_context.rollback_to_savepoint(mdl_savepoint);
494
495 DBUG_RETURN(NULL);
496 }
497
498
499 /**
500 Return appropriate error about recursion limit reaching
501
502 @param thd Thread handle
503
504 @remark For functions and triggers we return error about
505 prohibited recursion. For stored procedures we
506 return about reaching recursion limit.
507 */
508
recursion_level_error(THD * thd,sp_head * sp)509 static void recursion_level_error(THD *thd, sp_head *sp)
510 {
511 if (sp->m_type == SP_TYPE_PROCEDURE)
512 {
513 my_error(ER_SP_RECURSION_LIMIT, MYF(0),
514 static_cast<int>(thd->variables.max_sp_recursion_depth),
515 sp->m_name.str);
516 }
517 else
518 my_error(ER_SP_NO_RECURSION, MYF(0));
519 }
520
521
522 /**
523 Find row in open mysql.proc table representing stored routine.
524
525 @param thd Thread context
526 @param type Type of routine to find (function or procedure)
527 @param name Name of routine
528 @param table TABLE object for open mysql.proc table.
529
530 @retval
531 SP_OK Routine found
532 @retval
533 SP_KEY_NOT_FOUND No routine with given name
534 */
535
536 static int
db_find_routine_aux(THD * thd,enum_sp_type type,sp_name * name,TABLE * table)537 db_find_routine_aux(THD *thd, enum_sp_type type, sp_name *name, TABLE *table)
538 {
539 uchar key[MAX_KEY_LENGTH]; // db, name, optional key length type
540 DBUG_ENTER("db_find_routine_aux");
541 DBUG_PRINT("enter", ("type: %d name: %.*s",
542 type, (int) name->m_name.length, name->m_name.str));
543
544 /*
545 Create key to find row. We have to use field->store() to be able to
546 handle VARCHAR and CHAR fields.
547 Assumption here is that the three first fields in the table are
548 'db', 'name' and 'type' and the first key is the primary key over the
549 same fields.
550 */
551 if (name->m_name.length > table->field[1]->field_length)
552 DBUG_RETURN(SP_KEY_NOT_FOUND);
553 table->field[0]->store(name->m_db.str, name->m_db.length, &my_charset_bin);
554 table->field[1]->store(name->m_name.str, name->m_name.length,
555 &my_charset_bin);
556 table->field[2]->store((longlong) type, TRUE);
557 key_copy(key, table->record[0], table->key_info,
558 table->key_info->key_length);
559
560 if (table->file->ha_index_read_idx_map(table->record[0], 0, key, HA_WHOLE_KEY,
561 HA_READ_KEY_EXACT))
562 DBUG_RETURN(SP_KEY_NOT_FOUND);
563
564 DBUG_RETURN(SP_OK);
565 }
566
567
568 /**
569 Find routine definition in mysql.proc table and create corresponding
570 sp_head object for it.
571
572 @param thd Thread context
573 @param type Type of routine (SP_TYPE_PROCEDURE/...)
574 @param name Name of routine
575 @param sphp Out parameter in which pointer to created sp_head
576 object is returned (0 in case of error).
577
578 @note
579 This function may damage current LEX during execution, so it is good
580 idea to create temporary LEX and make it active before calling it.
581
582 @retval
583 0 Success
584 @retval
585 non-0 Error (may be one of special codes like SP_KEY_NOT_FOUND)
586 */
587
588 static int
db_find_routine(THD * thd,enum_sp_type type,sp_name * name,sp_head ** sphp)589 db_find_routine(THD *thd, enum_sp_type type, sp_name *name, sp_head **sphp)
590 {
591 TABLE *table;
592 const char *params, *returns, *body;
593 int ret;
594 const char *definer;
595 longlong created;
596 longlong modified;
597 st_sp_chistics chistics;
598 char *ptr;
599 size_t length;
600 char buff[65];
601 String str(buff, sizeof(buff), &my_charset_bin);
602 bool saved_time_zone_used= thd->time_zone_used;
603 sql_mode_t sql_mode, saved_mode= thd->variables.sql_mode;
604 Open_tables_backup open_tables_state_backup;
605 Stored_program_creation_ctx *creation_ctx;
606
607 DBUG_ENTER("db_find_routine");
608 DBUG_PRINT("enter", ("type: %d name: %.*s",
609 type, (int) name->m_name.length, name->m_name.str));
610
611 *sphp= 0; // In case of errors
612 if (!(table= open_proc_table_for_read(thd, &open_tables_state_backup)))
613 DBUG_RETURN(SP_OPEN_TABLE_FAILED);
614
615 /* Reset sql_mode during data dictionary operations. */
616 thd->variables.sql_mode= 0;
617
618 if ((ret= db_find_routine_aux(thd, type, name, table)) != SP_OK)
619 goto done;
620
621 if (table->s->fields < MYSQL_PROC_FIELD_COUNT)
622 {
623 ret= SP_GET_FIELD_FAILED;
624 goto done;
625 }
626
627 memset(&chistics, 0, sizeof(chistics));
628 if ((ptr= get_field(thd->mem_root,
629 table->field[MYSQL_PROC_FIELD_ACCESS])) == NULL)
630 {
631 ret= SP_GET_FIELD_FAILED;
632 goto done;
633 }
634 switch (ptr[0]) {
635 case 'N':
636 chistics.daccess= SP_NO_SQL;
637 break;
638 case 'C':
639 chistics.daccess= SP_CONTAINS_SQL;
640 break;
641 case 'R':
642 chistics.daccess= SP_READS_SQL_DATA;
643 break;
644 case 'M':
645 chistics.daccess= SP_MODIFIES_SQL_DATA;
646 break;
647 default:
648 chistics.daccess= SP_DEFAULT_ACCESS_MAPPING;
649 }
650
651 if ((ptr= get_field(thd->mem_root,
652 table->field[MYSQL_PROC_FIELD_DETERMINISTIC])) == NULL)
653 {
654 ret= SP_GET_FIELD_FAILED;
655 goto done;
656 }
657 chistics.detistic= (ptr[0] == 'N' ? FALSE : TRUE);
658
659 if ((ptr= get_field(thd->mem_root,
660 table->field[MYSQL_PROC_FIELD_SECURITY_TYPE])) == NULL)
661 {
662 ret= SP_GET_FIELD_FAILED;
663 goto done;
664 }
665 chistics.suid= (ptr[0] == 'I' ? SP_IS_NOT_SUID : SP_IS_SUID);
666
667 if ((params= get_field(thd->mem_root,
668 table->field[MYSQL_PROC_FIELD_PARAM_LIST])) == NULL)
669 {
670 params= "";
671 }
672
673 if (type == SP_TYPE_PROCEDURE)
674 returns= "";
675 else if ((returns= get_field(thd->mem_root,
676 table->field[MYSQL_PROC_FIELD_RETURNS])) == NULL)
677 {
678 ret= SP_GET_FIELD_FAILED;
679 goto done;
680 }
681
682 if ((body= get_field(thd->mem_root,
683 table->field[MYSQL_PROC_FIELD_BODY])) == NULL)
684 {
685 ret= SP_GET_FIELD_FAILED;
686 goto done;
687 }
688
689 // Get additional information
690 if ((definer= get_field(thd->mem_root,
691 table->field[MYSQL_PROC_FIELD_DEFINER])) == NULL)
692 {
693 ret= SP_GET_FIELD_FAILED;
694 goto done;
695 }
696
697 modified= table->field[MYSQL_PROC_FIELD_MODIFIED]->val_int();
698 created= table->field[MYSQL_PROC_FIELD_CREATED]->val_int();
699
700 sql_mode= (sql_mode_t) table->field[MYSQL_PROC_FIELD_SQL_MODE]->val_int();
701
702 table->field[MYSQL_PROC_FIELD_COMMENT]->val_str(&str, &str);
703
704 ptr= 0;
705 if ((length= str.length()))
706 ptr= thd->strmake(str.ptr(), length);
707 chistics.comment.str= ptr;
708 chistics.comment.length= length;
709
710 creation_ctx= Stored_routine_creation_ctx::load_from_db(thd, name, table);
711
712 close_nontrans_system_tables(thd, &open_tables_state_backup);
713 table= 0;
714
715 ret= db_load_routine(thd, type, name, sphp,
716 sql_mode, params, returns, body, chistics,
717 definer, created, modified, creation_ctx);
718 done:
719 /*
720 Restore the time zone flag as the timezone usage in proc table
721 does not affect replication.
722 */
723 thd->time_zone_used= saved_time_zone_used;
724 if (table)
725 close_nontrans_system_tables(thd, &open_tables_state_backup);
726 thd->variables.sql_mode= saved_mode;
727 DBUG_RETURN(ret);
728 }
729
730
731 /**
732 Silence DEPRECATED SYNTAX warnings when loading a stored procedure
733 into the cache.
734 */
735 class Silence_deprecated_warning : public Internal_error_handler
736 {
737 public:
handle_condition(THD * thd,uint sql_errno,const char * sqlstate,Sql_condition::enum_severity_level * level,const char * msg)738 virtual bool handle_condition(THD *thd,
739 uint sql_errno,
740 const char* sqlstate,
741 Sql_condition::enum_severity_level *level,
742 const char* msg)
743 {
744 if (sql_errno == ER_WARN_DEPRECATED_SYNTAX &&
745 (*level) == Sql_condition::SL_WARNING)
746 return true;
747
748 return false;
749 }
750 };
751
752
753 /**
754 @brief The function parses input strings and returns SP stucture.
755
756 @param[in] thd Thread handler
757 @param[in] defstr CREATE... string
758 @param[in] sql_mode SQL mode
759 @param[in] creation_ctx Creation context of stored routines
760
761 @return Pointer on sp_head struct
762 @retval # Pointer on sp_head struct
763 @retval 0 error
764 */
765
sp_compile(THD * thd,String * defstr,sql_mode_t sql_mode,Stored_program_creation_ctx * creation_ctx)766 static sp_head *sp_compile(THD *thd, String *defstr, sql_mode_t sql_mode,
767 Stored_program_creation_ctx *creation_ctx)
768 {
769 sp_head *sp;
770 sql_mode_t old_sql_mode= thd->variables.sql_mode;
771 ha_rows old_select_limit= thd->variables.select_limit;
772 sp_rcontext *sp_runtime_ctx_saved= thd->sp_runtime_ctx;
773 Silence_deprecated_warning warning_handler;
774 Parser_state parser_state;
775 sql_digest_state *parent_digest= thd->m_digest;
776 PSI_statement_locker *parent_locker= thd->m_statement_psi;
777
778 thd->variables.sql_mode= sql_mode;
779 thd->variables.select_limit= HA_POS_ERROR;
780
781 if (parser_state.init(thd, defstr->c_ptr(), defstr->length()))
782 {
783 thd->variables.sql_mode= old_sql_mode;
784 thd->variables.select_limit= old_select_limit;
785 return NULL;
786 }
787
788 lex_start(thd);
789 thd->push_internal_handler(&warning_handler);
790 thd->sp_runtime_ctx= NULL;
791
792 thd->m_digest= NULL;
793 thd->m_statement_psi= NULL;
794 if (parse_sql(thd, & parser_state, creation_ctx) || thd->lex == NULL)
795 {
796 sp= thd->lex->sphead;
797 delete sp;
798 sp= 0;
799 }
800 else
801 {
802 sp= thd->lex->sphead;
803 }
804 thd->m_digest= parent_digest;
805 thd->m_statement_psi= parent_locker;
806
807 thd->pop_internal_handler();
808 thd->sp_runtime_ctx= sp_runtime_ctx_saved;
809 thd->variables.sql_mode= old_sql_mode;
810 thd->variables.select_limit= old_select_limit;
811 #ifdef HAVE_PSI_SP_INTERFACE
812 if (sp != NULL)
813 sp->m_sp_share= MYSQL_GET_SP_SHARE(sp->m_type,
814 sp->m_db.str, sp->m_db.length,
815 sp->m_name.str, sp->m_name.length);
816 #endif
817 return sp;
818 }
819
820
821 class Bad_db_error_handler : public Internal_error_handler
822 {
823 public:
Bad_db_error_handler()824 Bad_db_error_handler()
825 :m_error_caught(false)
826 {}
827
handle_condition(THD * thd,uint sql_errno,const char * sqlstate,Sql_condition::enum_severity_level * level,const char * message)828 virtual bool handle_condition(THD *thd,
829 uint sql_errno,
830 const char* sqlstate,
831 Sql_condition::enum_severity_level *level,
832 const char* message)
833 {
834 if (sql_errno == ER_BAD_DB_ERROR)
835 {
836 m_error_caught= true;
837 return true;
838 }
839 return false;
840 }
841
error_caught() const842 bool error_caught() const { return m_error_caught; }
843
844 private:
845 bool m_error_caught;
846 };
847
848
849 static int
db_load_routine(THD * thd,enum_sp_type type,sp_name * name,sp_head ** sphp,sql_mode_t sql_mode,const char * params,const char * returns,const char * body,st_sp_chistics & chistics,const char * definer,longlong created,longlong modified,Stored_program_creation_ctx * creation_ctx)850 db_load_routine(THD *thd, enum_sp_type type, sp_name *name, sp_head **sphp,
851 sql_mode_t sql_mode, const char *params, const char *returns,
852 const char *body, st_sp_chistics &chistics,
853 const char *definer, longlong created, longlong modified,
854 Stored_program_creation_ctx *creation_ctx)
855 {
856 LEX *old_lex= thd->lex, newlex;
857 String defstr;
858 char saved_cur_db_name_buf[NAME_LEN+1];
859 LEX_STRING saved_cur_db_name=
860 { saved_cur_db_name_buf, sizeof(saved_cur_db_name_buf) };
861 bool cur_db_changed;
862 Bad_db_error_handler db_not_exists_handler;
863
864 char definer_user_name_holder[USERNAME_LENGTH + 1];
865 LEX_CSTRING definer_user_name= { definer_user_name_holder, USERNAME_LENGTH};
866
867 char definer_host_name_holder[HOSTNAME_LENGTH + 1];
868 LEX_CSTRING definer_host_name= { definer_host_name_holder, HOSTNAME_LENGTH };
869
870 int ret= 0;
871
872 thd->lex= &newlex;
873 newlex.thd= thd;
874 newlex.set_current_select(NULL);
875
876 parse_user(definer, strlen(definer),
877 definer_user_name_holder,
878 &definer_user_name.length,
879 definer_host_name_holder,
880 &definer_host_name.length);
881
882 defstr.set_charset(creation_ctx->get_client_cs());
883
884 /*
885 We have to add DEFINER clause and provide proper routine characterstics in
886 routine definition statement that we build here to be able to use this
887 definition for SHOW CREATE PROCEDURE later.
888 */
889
890 if (!create_string(thd, &defstr,
891 type,
892 NULL, 0,
893 name->m_name.str, name->m_name.length,
894 params, strlen(params),
895 returns, strlen(returns),
896 body, strlen(body),
897 &chistics, definer_user_name, definer_host_name,
898 sql_mode))
899 {
900 ret= SP_INTERNAL_ERROR;
901 goto end;
902 }
903
904 thd->push_internal_handler(&db_not_exists_handler);
905 /*
906 Change the current database (if needed).
907
908 TODO: why do we force switch here?
909 */
910 if (mysql_opt_change_db(thd, name->m_db, &saved_cur_db_name, TRUE,
911 &cur_db_changed))
912 {
913 ret= SP_INTERNAL_ERROR;
914 thd->pop_internal_handler();
915 goto end;
916 }
917 thd->pop_internal_handler();
918 if (db_not_exists_handler.error_caught())
919 {
920 ret= SP_INTERNAL_ERROR;
921 my_error(ER_BAD_DB_ERROR, MYF(0), name->m_db.str);
922
923 goto end;
924 }
925
926 {
927 *sphp= sp_compile(thd, &defstr, sql_mode, creation_ctx);
928 /*
929 Force switching back to the saved current database (if changed),
930 because it may be NULL. In this case, mysql_change_db() would
931 generate an error.
932 */
933
934 if (cur_db_changed &&
935 mysql_change_db(thd, to_lex_cstring(saved_cur_db_name), true))
936 {
937 ret= SP_INTERNAL_ERROR;
938 goto end;
939 }
940
941 if (!*sphp)
942 {
943 ret= SP_PARSE_ERROR;
944 goto end;
945 }
946
947 (*sphp)->set_definer(definer_user_name, definer_host_name);
948 (*sphp)->set_info(created, modified, &chistics, sql_mode);
949 (*sphp)->set_creation_ctx(creation_ctx);
950 (*sphp)->optimize();
951 /*
952 Not strictly necessary to invoke this method here, since we know
953 that we've parsed CREATE PROCEDURE/FUNCTION and not an
954 UPDATE/DELETE/INSERT/REPLACE/LOAD/CREATE TABLE, but we try to
955 maintain the invariant that this method is called for each
956 distinct statement, in case its logic is extended with other
957 types of analyses in future.
958 */
959 newlex.set_trg_event_type_for_tables();
960 }
961
962 end:
963 thd->lex->sphead= NULL;
964 lex_end(thd->lex);
965 thd->lex= old_lex;
966 return ret;
967 }
968
969
970 static void
sp_returns_type(THD * thd,String & result,sp_head * sp)971 sp_returns_type(THD *thd, String &result, sp_head *sp)
972 {
973 TABLE table;
974 TABLE_SHARE share;
975 Field *field;
976
977 table.in_use= thd;
978 table.s = &share;
979 field= sp->create_result_field(0, 0, &table);
980 field->sql_type(result);
981
982 if (field->has_charset())
983 {
984 result.append(STRING_WITH_LEN(" CHARSET "));
985 result.append(field->charset()->csname);
986 if (!(field->charset()->state & MY_CS_PRIMARY))
987 {
988 result.append(STRING_WITH_LEN(" COLLATE "));
989 result.append(field->charset()->name);
990 }
991 }
992
993 delete field;
994 }
995
996
997 /**
998 Write stored-routine object into mysql.proc.
999
1000 This operation stores attributes of the stored procedure/function into
1001 the mysql.proc.
1002
1003 @param thd Thread context.
1004 @param sp Stored routine object to store.
1005
1006 @note Opens and closes the thread tables. Therefore assumes
1007 that there are no locked tables in this thread at the time of
1008 invocation.
1009 Unlike some other DDL statements, *does* close the tables
1010 in the end, since the call to this function is normally
1011 followed by an implicit grant (sp_grant_privileges())
1012 and this subsequent call opens and closes mysql.procs_priv.
1013
1014 @retval false success
1015 @retval true error
1016 */
1017
sp_create_routine(THD * thd,sp_head * sp)1018 bool sp_create_routine(THD *thd, sp_head *sp)
1019 {
1020 bool error= true;
1021 TABLE *table;
1022 char definer[USER_HOST_BUFF_SIZE];
1023 sql_mode_t saved_mode= thd->variables.sql_mode;
1024 MDL_key::enum_mdl_namespace mdl_type= (sp->m_type == SP_TYPE_FUNCTION) ?
1025 MDL_key::FUNCTION : MDL_key::PROCEDURE;
1026
1027 const CHARSET_INFO *db_cs= get_default_db_collation(thd, sp->m_db.str);
1028
1029 enum_check_fields saved_count_cuted_fields;
1030
1031 bool store_failed= FALSE;
1032
1033 bool save_binlog_row_based;
1034
1035 DBUG_ENTER("sp_create_routine");
1036 DBUG_PRINT("enter", ("type: %d name: %.*s",sp->m_type,
1037 (int) sp->m_name.length, sp->m_name.str));
1038 String retstr(64);
1039 retstr.set_charset(system_charset_info);
1040
1041 assert(sp->m_type == SP_TYPE_PROCEDURE ||
1042 sp->m_type == SP_TYPE_FUNCTION);
1043
1044 /* Grab an exclusive MDL lock. */
1045 if (lock_object_name(thd, mdl_type, sp->m_db.str, sp->m_name.str))
1046 {
1047 my_error(ER_SP_STORE_FAILED, MYF(0),
1048 SP_TYPE_STRING(thd->lex),sp->m_name.str);
1049 DBUG_RETURN(true);
1050 }
1051
1052 /*
1053 Check that a database directory with this name
1054 exists. Design note: This won't work on virtual databases
1055 like information_schema.
1056 */
1057 if (check_db_dir_existence(sp->m_db.str))
1058 {
1059 my_error(ER_BAD_DB_ERROR, MYF(0), sp->m_db.str);
1060 DBUG_RETURN(true);
1061 }
1062
1063 /* Reset sql_mode during data dictionary operations. */
1064 thd->variables.sql_mode= 0;
1065
1066 /*
1067 This statement will be replicated as a statement, even when using
1068 row-based replication. The flag will be reset at the end of the
1069 statement.
1070 */
1071 if ((save_binlog_row_based= thd->is_current_stmt_binlog_format_row()))
1072 thd->clear_current_stmt_binlog_format_row();
1073
1074 saved_count_cuted_fields= thd->count_cuted_fields;
1075 thd->count_cuted_fields= CHECK_FIELD_WARN;
1076
1077 if (!(table= open_proc_table_for_update(thd)))
1078 {
1079 my_error(ER_SP_STORE_FAILED, MYF(0),
1080 SP_TYPE_STRING(thd->lex),sp->m_name.str);
1081 }
1082 else
1083 {
1084 restore_record(table, s->default_values); // Get default values for fields
1085
1086 /* NOTE: all needed privilege checks have been already done. */
1087 strxnmov(definer, sizeof(definer)-1, thd->lex->definer->user.str, "@",
1088 thd->lex->definer->host.str, NullS);
1089
1090 if (table->s->fields < MYSQL_PROC_FIELD_COUNT)
1091 {
1092 my_error(ER_SP_STORE_FAILED, MYF(0),
1093 SP_TYPE_STRING(thd->lex),sp->m_name.str);
1094 goto done;
1095 }
1096
1097 if (system_charset_info->cset->numchars(system_charset_info,
1098 sp->m_name.str,
1099 sp->m_name.str+sp->m_name.length) >
1100 table->field[MYSQL_PROC_FIELD_NAME]->char_length())
1101 {
1102 my_error(ER_TOO_LONG_IDENT, MYF(0), sp->m_name.str);
1103 goto done;
1104 }
1105 if (sp->m_body.length > table->field[MYSQL_PROC_FIELD_BODY]->field_length)
1106 {
1107 my_error(ER_TOO_LONG_BODY, MYF(0), sp->m_name.str);
1108 goto done;
1109 }
1110
1111 store_failed=
1112 table->field[MYSQL_PROC_FIELD_DB]->
1113 store(sp->m_db.str, sp->m_db.length, system_charset_info);
1114
1115 store_failed= store_failed ||
1116 table->field[MYSQL_PROC_FIELD_NAME]->
1117 store(sp->m_name.str, sp->m_name.length, system_charset_info);
1118
1119 store_failed= store_failed ||
1120 table->field[MYSQL_PROC_MYSQL_TYPE]->
1121 store((longlong)sp->m_type, TRUE);
1122
1123 store_failed= store_failed ||
1124 table->field[MYSQL_PROC_FIELD_SPECIFIC_NAME]->
1125 store(sp->m_name.str, sp->m_name.length, system_charset_info);
1126
1127 if (sp->m_chistics->daccess != SP_DEFAULT_ACCESS)
1128 {
1129 store_failed= store_failed ||
1130 table->field[MYSQL_PROC_FIELD_ACCESS]->
1131 store((longlong)sp->m_chistics->daccess, TRUE);
1132 }
1133
1134 store_failed= store_failed ||
1135 table->field[MYSQL_PROC_FIELD_DETERMINISTIC]->
1136 store((longlong)(sp->m_chistics->detistic ? 1 : 2), TRUE);
1137
1138 if (sp->m_chistics->suid != SP_IS_DEFAULT_SUID)
1139 {
1140 store_failed= store_failed ||
1141 table->field[MYSQL_PROC_FIELD_SECURITY_TYPE]->
1142 store((longlong)sp->m_chistics->suid, TRUE);
1143 }
1144
1145 store_failed= store_failed ||
1146 table->field[MYSQL_PROC_FIELD_PARAM_LIST]->
1147 store(sp->m_params.str, sp->m_params.length, system_charset_info);
1148
1149 if (sp->m_type == SP_TYPE_FUNCTION)
1150 {
1151 sp_returns_type(thd, retstr, sp);
1152
1153 store_failed= store_failed ||
1154 table->field[MYSQL_PROC_FIELD_RETURNS]->
1155 store(retstr.ptr(), retstr.length(), system_charset_info);
1156 }
1157
1158 store_failed= store_failed ||
1159 table->field[MYSQL_PROC_FIELD_BODY]->
1160 store(sp->m_body.str, sp->m_body.length, system_charset_info);
1161
1162 bool store_definer_failed= table->field[MYSQL_PROC_FIELD_DEFINER]->
1163 store(definer, strlen(definer), system_charset_info);
1164
1165 store_failed= store_failed || store_definer_failed;
1166
1167 Item_func_now_local::store_in(table->field[MYSQL_PROC_FIELD_CREATED]);
1168 Item_func_now_local::store_in(table->field[MYSQL_PROC_FIELD_MODIFIED]);
1169
1170 store_failed= store_failed ||
1171 table->field[MYSQL_PROC_FIELD_SQL_MODE]->
1172 store((longlong)saved_mode, TRUE);
1173
1174 if (sp->m_chistics->comment.str)
1175 {
1176 store_failed= store_failed ||
1177 table->field[MYSQL_PROC_FIELD_COMMENT]->
1178 store(sp->m_chistics->comment.str, sp->m_chistics->comment.length,
1179 system_charset_info);
1180 }
1181
1182 if ((sp->m_type == SP_TYPE_FUNCTION) &&
1183 !trust_function_creators && mysql_bin_log.is_open())
1184 {
1185 if (!sp->m_chistics->detistic)
1186 {
1187 /*
1188 Note that this test is not perfect; one could use
1189 a non-deterministic read-only function in an update statement.
1190 */
1191 enum enum_sp_data_access access=
1192 (sp->m_chistics->daccess == SP_DEFAULT_ACCESS) ?
1193 SP_DEFAULT_ACCESS_MAPPING : sp->m_chistics->daccess;
1194 if (access == SP_CONTAINS_SQL ||
1195 access == SP_MODIFIES_SQL_DATA)
1196 {
1197 my_error(ER_BINLOG_UNSAFE_ROUTINE, MYF(0));
1198 goto done;
1199 }
1200 }
1201 if (!(thd->security_context()->check_access(SUPER_ACL)))
1202 {
1203 my_error(ER_BINLOG_CREATE_ROUTINE_NEED_SUPER,MYF(0));
1204 goto done;
1205 }
1206 }
1207
1208 table->field[MYSQL_PROC_FIELD_CHARACTER_SET_CLIENT]->set_notnull();
1209 store_failed= store_failed ||
1210 table->field[MYSQL_PROC_FIELD_CHARACTER_SET_CLIENT]->store(
1211 thd->charset()->csname,
1212 strlen(thd->charset()->csname),
1213 system_charset_info);
1214
1215 table->field[MYSQL_PROC_FIELD_COLLATION_CONNECTION]->set_notnull();
1216 store_failed= store_failed ||
1217 table->field[MYSQL_PROC_FIELD_COLLATION_CONNECTION]->store(
1218 thd->variables.collation_connection->name,
1219 strlen(thd->variables.collation_connection->name),
1220 system_charset_info);
1221
1222 table->field[MYSQL_PROC_FIELD_DB_COLLATION]->set_notnull();
1223 store_failed= store_failed ||
1224 table->field[MYSQL_PROC_FIELD_DB_COLLATION]->store(
1225 db_cs->name, strlen(db_cs->name), system_charset_info);
1226
1227 table->field[MYSQL_PROC_FIELD_BODY_UTF8]->set_notnull();
1228 store_failed= store_failed ||
1229 table->field[MYSQL_PROC_FIELD_BODY_UTF8]->store(
1230 sp->m_body_utf8.str, sp->m_body_utf8.length, system_charset_info);
1231
1232 if (store_definer_failed &&
1233 table->field[MYSQL_PROC_FIELD_DEFINER]->field_length <
1234 (USERNAME_CHAR_LENGTH + HOSTNAME_LENGTH + 1) *
1235 table->field[MYSQL_PROC_FIELD_DEFINER]->charset()->mbmaxlen)
1236 {
1237 my_error(ER_USER_COLUMN_OLD_LENGTH, MYF(0),
1238 table->field[MYSQL_PROC_FIELD_DEFINER]->field_name);
1239 goto done;
1240 }
1241 else if (store_failed)
1242 {
1243 my_error(ER_CANT_CREATE_SROUTINE, MYF(0), sp->m_name.str);
1244 goto done;
1245 }
1246
1247 if (table->file->ha_write_row(table->record[0]))
1248 {
1249 my_error(ER_SP_ALREADY_EXISTS, MYF(0),
1250 SP_TYPE_STRING(thd->lex), sp->m_name.str);
1251 goto done;
1252 }
1253
1254 sp_cache_invalidate();
1255
1256 error= false;
1257 if (mysql_bin_log.is_open())
1258 {
1259 thd->clear_error();
1260
1261 String log_query;
1262 log_query.set_charset(system_charset_info);
1263
1264 if (!create_string(thd, &log_query,
1265 sp->m_type,
1266 (sp->m_explicit_name ? sp->m_db.str : NULL),
1267 (sp->m_explicit_name ? sp->m_db.length : 0),
1268 sp->m_name.str, sp->m_name.length,
1269 sp->m_params.str, sp->m_params.length,
1270 retstr.c_ptr(), retstr.length(),
1271 sp->m_body.str, sp->m_body.length,
1272 sp->m_chistics, thd->lex->definer->user,
1273 thd->lex->definer->host,
1274 saved_mode))
1275 {
1276 my_error(ER_SP_STORE_FAILED, MYF(0),
1277 SP_TYPE_STRING(thd->lex), sp->m_name.str);
1278 error= true;
1279 goto done;
1280 }
1281 /* restore sql_mode when binloging */
1282 thd->variables.sql_mode= saved_mode;
1283 thd->add_to_binlog_accessed_dbs(sp->m_db.str);
1284 /* Such a statement can always go directly to binlog, no trans cache */
1285 if (thd->binlog_query(THD::STMT_QUERY_TYPE,
1286 log_query.c_ptr(), log_query.length(),
1287 FALSE, FALSE, FALSE, 0))
1288 {
1289 my_error(ER_SP_STORE_FAILED, MYF(0),
1290 SP_TYPE_STRING(thd->lex), sp->m_name.str);
1291 error= true;
1292 };
1293 thd->variables.sql_mode= 0;
1294 }
1295 }
1296
1297 done:
1298 thd->count_cuted_fields= saved_count_cuted_fields;
1299 thd->variables.sql_mode= saved_mode;
1300 /* Restore the state of binlog format */
1301 assert(!thd->is_current_stmt_binlog_format_row());
1302 if (save_binlog_row_based)
1303 thd->set_current_stmt_binlog_format_row();
1304 DBUG_RETURN(error);
1305 }
1306
1307
1308 /**
1309 Delete the record for the stored routine object from mysql.proc.
1310
1311 The operation deletes the record for the stored routine specified by name
1312 from the mysql.proc table and invalidates the stored-routine cache.
1313
1314 @param thd Thread context.
1315 @param type Stored routine type
1316 (SP_TYPE_PROCEDURE or SP_TYPE_FUNCTION)
1317 @param name Stored routine name.
1318
1319 @return Error code. SP_OK is returned on success. Other SP_ constants are
1320 used to indicate about errors.
1321 */
1322
sp_drop_routine(THD * thd,enum_sp_type type,sp_name * name)1323 int sp_drop_routine(THD *thd, enum_sp_type type, sp_name *name)
1324 {
1325 TABLE *table;
1326 int ret;
1327 bool save_binlog_row_based;
1328 MDL_key::enum_mdl_namespace mdl_type= (type == SP_TYPE_FUNCTION) ?
1329 MDL_key::FUNCTION : MDL_key::PROCEDURE;
1330 DBUG_ENTER("sp_drop_routine");
1331 DBUG_PRINT("enter", ("type: %d name: %.*s",
1332 type, (int) name->m_name.length, name->m_name.str));
1333
1334 assert(type == SP_TYPE_PROCEDURE || type == SP_TYPE_FUNCTION);
1335
1336 /* Grab an exclusive MDL lock. */
1337 if (lock_object_name(thd, mdl_type, name->m_db.str, name->m_name.str))
1338 DBUG_RETURN(SP_DELETE_ROW_FAILED);
1339
1340 if (!(table= open_proc_table_for_update(thd)))
1341 DBUG_RETURN(SP_OPEN_TABLE_FAILED);
1342
1343 /*
1344 This statement will be replicated as a statement, even when using
1345 row-based replication. The flag will be reset at the end of the
1346 statement.
1347 */
1348 if ((save_binlog_row_based= thd->is_current_stmt_binlog_format_row()))
1349 thd->clear_current_stmt_binlog_format_row();
1350
1351 if ((ret= db_find_routine_aux(thd, type, name, table)) == SP_OK)
1352 {
1353 if (table->file->ha_delete_row(table->record[0]))
1354 ret= SP_DELETE_ROW_FAILED;
1355 }
1356
1357 if (ret == SP_OK)
1358 {
1359 thd->add_to_binlog_accessed_dbs(name->m_db.str);
1360 if (write_bin_log(thd, TRUE, thd->query().str, thd->query().length))
1361 ret= SP_INTERNAL_ERROR;
1362 sp_cache_invalidate();
1363
1364 /*
1365 A lame workaround for lack of cache flush:
1366 make sure the routine is at least gone from the
1367 local cache.
1368 */
1369 {
1370 sp_head *sp;
1371 sp_cache **spc= (type == SP_TYPE_FUNCTION ?
1372 &thd->sp_func_cache : &thd->sp_proc_cache);
1373 sp= sp_cache_lookup(spc, name);
1374 if (sp)
1375 sp_cache_flush_obsolete(spc, &sp);
1376 }
1377 #ifdef HAVE_PSI_SP_INTERFACE
1378 /* Drop statistics for this stored program from performance schema. */
1379 MYSQL_DROP_SP(type,
1380 name->m_db.str, name->m_db.length,
1381 name->m_name.str, name->m_name.length);
1382 #endif
1383 }
1384 /* Restore the state of binlog format */
1385 assert(!thd->is_current_stmt_binlog_format_row());
1386 if (save_binlog_row_based)
1387 thd->set_current_stmt_binlog_format_row();
1388 DBUG_RETURN(ret);
1389 }
1390
1391
1392 /**
1393 Find and updated the record for the stored routine object in mysql.proc.
1394
1395 The operation finds the record for the stored routine specified by name
1396 in the mysql.proc table and updates it with new attributes. After
1397 successful update, the cache is invalidated.
1398
1399 @param thd Thread context.
1400 @param type Stored routine type
1401 (SP_TYPE_PROCEDURE or SP_TYPE_FUNCTION)
1402 @param name Stored routine name.
1403 @param chistics New values of stored routine attributes to write.
1404
1405 @return Error code. SP_OK is returned on success. Other SP_ constants are
1406 used to indicate about errors.
1407 */
1408
sp_update_routine(THD * thd,enum_sp_type type,sp_name * name,st_sp_chistics * chistics)1409 int sp_update_routine(THD *thd, enum_sp_type type, sp_name *name,
1410 st_sp_chistics *chistics)
1411 {
1412 TABLE *table;
1413 int ret;
1414 bool save_binlog_row_based;
1415 MDL_key::enum_mdl_namespace mdl_type= (type == SP_TYPE_FUNCTION) ?
1416 MDL_key::FUNCTION : MDL_key::PROCEDURE;
1417 DBUG_ENTER("sp_update_routine");
1418 DBUG_PRINT("enter", ("type: %d name: %.*s",
1419 type, (int) name->m_name.length, name->m_name.str));
1420
1421 assert(type == SP_TYPE_PROCEDURE || type == SP_TYPE_FUNCTION);
1422
1423 /* Grab an exclusive MDL lock. */
1424 if (lock_object_name(thd, mdl_type, name->m_db.str, name->m_name.str))
1425 DBUG_RETURN(SP_OPEN_TABLE_FAILED);
1426
1427 if (!(table= open_proc_table_for_update(thd)))
1428 DBUG_RETURN(SP_OPEN_TABLE_FAILED);
1429
1430 /*
1431 This statement will be replicated as a statement, even when using
1432 row-based replication. The flag will be reset at the end of the
1433 statement.
1434 */
1435 if ((save_binlog_row_based= thd->is_current_stmt_binlog_format_row()))
1436 thd->clear_current_stmt_binlog_format_row();
1437
1438 if ((ret= db_find_routine_aux(thd, type, name, table)) == SP_OK)
1439 {
1440 if (type == SP_TYPE_FUNCTION && ! trust_function_creators &&
1441 mysql_bin_log.is_open() &&
1442 (chistics->daccess == SP_CONTAINS_SQL ||
1443 chistics->daccess == SP_MODIFIES_SQL_DATA))
1444 {
1445 char *ptr;
1446 bool is_deterministic;
1447 ptr= get_field(thd->mem_root,
1448 table->field[MYSQL_PROC_FIELD_DETERMINISTIC]);
1449 if (ptr == NULL)
1450 {
1451 ret= SP_INTERNAL_ERROR;
1452 goto err;
1453 }
1454 is_deterministic= ptr[0] == 'N' ? FALSE : TRUE;
1455 if (!is_deterministic)
1456 {
1457 my_message(ER_BINLOG_UNSAFE_ROUTINE,
1458 ER(ER_BINLOG_UNSAFE_ROUTINE), MYF(0));
1459 ret= SP_INTERNAL_ERROR;
1460 goto err;
1461 }
1462 }
1463
1464 store_record(table,record[1]);
1465 Item_func_now_local::store_in(table->field[MYSQL_PROC_FIELD_MODIFIED]);
1466 if (chistics->suid != SP_IS_DEFAULT_SUID)
1467 table->field[MYSQL_PROC_FIELD_SECURITY_TYPE]->
1468 store((longlong)chistics->suid, TRUE);
1469 if (chistics->daccess != SP_DEFAULT_ACCESS)
1470 table->field[MYSQL_PROC_FIELD_ACCESS]->
1471 store((longlong)chistics->daccess, TRUE);
1472 if (chistics->comment.str)
1473 table->field[MYSQL_PROC_FIELD_COMMENT]->store(chistics->comment.str,
1474 chistics->comment.length,
1475 system_charset_info);
1476 if ((ret= table->file->ha_update_row(table->record[1],table->record[0])) &&
1477 ret != HA_ERR_RECORD_IS_THE_SAME)
1478 ret= SP_WRITE_ROW_FAILED;
1479 else
1480 ret= 0;
1481 }
1482
1483 if (ret == SP_OK)
1484 {
1485 if (write_bin_log(thd, TRUE, thd->query().str, thd->query().length))
1486 ret= SP_INTERNAL_ERROR;
1487 sp_cache_invalidate();
1488 }
1489 err:
1490 /* Restore the state of binlog format */
1491 assert(!thd->is_current_stmt_binlog_format_row());
1492 if (save_binlog_row_based)
1493 thd->set_current_stmt_binlog_format_row();
1494 DBUG_RETURN(ret);
1495 }
1496
1497
1498 /**
1499 This internal handler is used to trap errors from opening mysql.proc.
1500 */
1501
1502 class Lock_db_routines_error_handler : public Internal_error_handler
1503 {
1504 public:
handle_condition(THD * thd,uint sql_errno,const char * sqlstate,Sql_condition::enum_severity_level * level,const char * msg)1505 virtual bool handle_condition(THD *thd,
1506 uint sql_errno,
1507 const char* sqlstate,
1508 Sql_condition::enum_severity_level *level,
1509 const char* msg)
1510 {
1511 if (sql_errno == ER_NO_SUCH_TABLE ||
1512 sql_errno == ER_CANNOT_LOAD_FROM_TABLE_V2 ||
1513 sql_errno == ER_COL_COUNT_DOESNT_MATCH_PLEASE_UPDATE_V2 ||
1514 sql_errno == ER_COL_COUNT_DOESNT_MATCH_CORRUPTED_V2)
1515 return true;
1516 return false;
1517 }
1518 };
1519
1520
1521 /**
1522 Acquires exclusive metadata lock on all stored routines in the
1523 given database.
1524
1525 @note Will also return false (=success) if mysql.proc can't be opened
1526 or is outdated. This allows DROP DATABASE to continue in these
1527 cases.
1528 */
1529
lock_db_routines(THD * thd,const char * db)1530 bool lock_db_routines(THD *thd, const char *db)
1531 {
1532 TABLE *table;
1533 uint key_len;
1534 Open_tables_backup open_tables_state_backup;
1535 MDL_request_list mdl_requests;
1536 Lock_db_routines_error_handler err_handler;
1537 DBUG_ENTER("lock_db_routines");
1538
1539 /*
1540 mysql.proc will be re-opened during deletion, so we can ignore
1541 errors when opening the table here. The error handler is
1542 used to avoid getting the same warning twice.
1543 */
1544 thd->push_internal_handler(&err_handler);
1545 table= open_proc_table_for_read(thd, &open_tables_state_backup);
1546 thd->pop_internal_handler();
1547 if (!table)
1548 {
1549 /*
1550 DROP DATABASE should not fail even if mysql.proc does not exist
1551 or is outdated. We therefore only abort mysql_rm_db() if we
1552 have errors not handled by the error handler.
1553 */
1554 DBUG_RETURN(thd->is_error() || thd->killed);
1555 }
1556
1557 table->field[MYSQL_PROC_FIELD_DB]->store(db, strlen(db), system_charset_info);
1558 key_len= table->key_info->key_part[0].store_length;
1559 int nxtres= table->file->ha_index_init(0, 1);
1560 if (nxtres)
1561 {
1562 table->file->print_error(nxtres, MYF(0));
1563 close_nontrans_system_tables(thd, &open_tables_state_backup);
1564 DBUG_RETURN(true);
1565 }
1566
1567 if (! table->file->ha_index_read_map(table->record[0],
1568 table->field[MYSQL_PROC_FIELD_DB]->ptr,
1569 (key_part_map)1, HA_READ_KEY_EXACT))
1570 {
1571 do
1572 {
1573 char *sp_name= get_field(thd->mem_root,
1574 table->field[MYSQL_PROC_FIELD_NAME]);
1575 if (sp_name == NULL)
1576 {
1577 table->file->ha_index_end();
1578 my_error(ER_SP_WRONG_NAME, MYF(0), "");
1579 close_nontrans_system_tables(thd, &open_tables_state_backup);
1580 DBUG_RETURN(true);
1581 }
1582
1583 longlong sp_type= table->field[MYSQL_PROC_MYSQL_TYPE]->val_int();
1584 MDL_request *mdl_request= new (thd->mem_root) MDL_request;
1585 MDL_REQUEST_INIT(mdl_request,
1586 sp_type == SP_TYPE_FUNCTION ?
1587 MDL_key::FUNCTION : MDL_key::PROCEDURE,
1588 db, sp_name, MDL_EXCLUSIVE, MDL_TRANSACTION);
1589 mdl_requests.push_front(mdl_request);
1590 } while (! (nxtres= table->file->ha_index_next_same(table->record[0],
1591 table->field[MYSQL_PROC_FIELD_DB]->ptr,
1592 key_len)));
1593 }
1594 table->file->ha_index_end();
1595 if (nxtres != 0 && nxtres != HA_ERR_END_OF_FILE)
1596 {
1597 table->file->print_error(nxtres, MYF(0));
1598 close_nontrans_system_tables(thd, &open_tables_state_backup);
1599 DBUG_RETURN(true);
1600 }
1601 close_nontrans_system_tables(thd, &open_tables_state_backup);
1602
1603 /* We should already hold a global IX lock and a schema X lock. */
1604 assert(thd->mdl_context.owns_equal_or_stronger_lock(MDL_key::GLOBAL,
1605 "", "", MDL_INTENTION_EXCLUSIVE) &&
1606 thd->mdl_context.owns_equal_or_stronger_lock(MDL_key::SCHEMA,
1607 db, "", MDL_EXCLUSIVE));
1608 DBUG_RETURN(thd->mdl_context.acquire_locks(&mdl_requests,
1609 thd->variables.lock_wait_timeout));
1610 }
1611
1612
1613 /**
1614 Drop all routines in database 'db'
1615
1616 @note Close the thread tables, the calling code might want to
1617 delete from other system tables afterwards.
1618
1619 @todo We need to change this function to call a my_error()/print_error()
1620 once we stop ignoring return value of sp_drop_db_routines().
1621 */
1622
1623 int
sp_drop_db_routines(THD * thd,const char * db)1624 sp_drop_db_routines(THD *thd, const char *db)
1625 {
1626 TABLE *table;
1627 int ret;
1628 uint key_len;
1629 MDL_savepoint mdl_savepoint= thd->mdl_context.mdl_savepoint();
1630 DBUG_ENTER("sp_drop_db_routines");
1631 DBUG_PRINT("enter", ("db: %s", db));
1632
1633 ret= SP_OPEN_TABLE_FAILED;
1634 if (!(table= open_proc_table_for_update(thd)))
1635 goto err;
1636
1637 table->field[MYSQL_PROC_FIELD_DB]->store(db, strlen(db), system_charset_info);
1638 key_len= table->key_info->key_part[0].store_length;
1639
1640 ret= SP_OK;
1641 if (table->file->ha_index_init(0, 1))
1642 {
1643 ret= SP_KEY_NOT_FOUND;
1644 goto err_idx_init;
1645 }
1646
1647 if (! table->file->ha_index_read_map(table->record[0],
1648 table->field[MYSQL_PROC_FIELD_DB]->ptr,
1649 (key_part_map)1, HA_READ_KEY_EXACT))
1650 {
1651 int nxtres;
1652 bool deleted= FALSE;
1653
1654 do
1655 {
1656 if (! table->file->ha_delete_row(table->record[0]))
1657 {
1658 deleted= TRUE; /* We deleted something */
1659 #ifdef HAVE_PSI_SP_INTERFACE
1660 char* sp_name= (char*)table->field[MYSQL_PROC_FIELD_NAME]->ptr;
1661 char* sp_name_end= strstr(sp_name," ");
1662 size_t sp_name_length= sp_name_end - sp_name;
1663 size_t db_name_length= strlen(db);
1664
1665 enum_sp_type sp_type= (enum_sp_type) table->field[MYSQL_PROC_MYSQL_TYPE]->ptr[0];
1666 /* Drop statistics for this stored program from performance schema. */
1667 MYSQL_DROP_SP(sp_type,
1668 db, db_name_length,
1669 sp_name, sp_name_length);
1670 #endif
1671 }
1672 else
1673 {
1674 ret= SP_DELETE_ROW_FAILED;
1675 nxtres= 0;
1676 break;
1677 }
1678 } while (! (nxtres= table->file->ha_index_next_same(table->record[0],
1679 table->field[MYSQL_PROC_FIELD_DB]->ptr,
1680 key_len)));
1681 if (nxtres != HA_ERR_END_OF_FILE)
1682 ret= SP_KEY_NOT_FOUND;
1683 if (deleted)
1684 sp_cache_invalidate();
1685 }
1686 table->file->ha_index_end();
1687
1688 err_idx_init:
1689 close_thread_tables(thd);
1690 /*
1691 Make sure to only release the MDL lock on mysql.proc, not other
1692 metadata locks DROP DATABASE might have acquired.
1693 */
1694 thd->mdl_context.rollback_to_savepoint(mdl_savepoint);
1695 err:
1696 DBUG_RETURN(ret);
1697 }
1698
1699
1700 /**
1701 Implement SHOW CREATE statement for stored routines.
1702
1703 The operation finds the stored routine object specified by name and then
1704 calls sp_head::show_create_routine() for the object.
1705
1706 @param thd Thread context.
1707 @param type Stored routine type
1708 (SP_TYPE_PROCEDURE or SP_TYPE_FUNCTION)
1709 @param name Stored routine name.
1710
1711 @return Error status.
1712 @retval FALSE on success
1713 @retval TRUE on error
1714 */
1715
sp_show_create_routine(THD * thd,enum_sp_type type,sp_name * name)1716 bool sp_show_create_routine(THD *thd, enum_sp_type type, sp_name *name)
1717 {
1718 sp_head *sp;
1719
1720 DBUG_ENTER("sp_show_create_routine");
1721 DBUG_PRINT("enter", ("name: %.*s",
1722 (int) name->m_name.length,
1723 name->m_name.str));
1724
1725 assert(type == SP_TYPE_PROCEDURE || type == SP_TYPE_FUNCTION);
1726
1727 /*
1728 @todo: Consider using prelocking for this code as well. Currently
1729 SHOW CREATE PROCEDURE/FUNCTION is a dirty read of the data
1730 dictionary, i.e. takes no metadata locks.
1731 It is "safe" to do as long as it doesn't affect the results
1732 of the binary log or the query cache, which currently it does not.
1733 */
1734 if (sp_cache_routine(thd, type, name, FALSE, &sp))
1735 DBUG_RETURN(TRUE);
1736
1737 if (sp == NULL || sp->show_create_routine(thd, type))
1738 {
1739 /*
1740 If we have insufficient privileges, pretend the routine
1741 does not exist.
1742 */
1743 my_error(ER_SP_DOES_NOT_EXIST, MYF(0),
1744 type == SP_TYPE_FUNCTION ? "FUNCTION" : "PROCEDURE",
1745 name->m_name.str);
1746 DBUG_RETURN(TRUE);
1747 }
1748
1749 DBUG_RETURN(FALSE);
1750 }
1751
1752
1753 /**
1754 Obtain object representing stored procedure/function by its name from
1755 stored procedures cache and looking into mysql.proc if needed.
1756
1757 @param thd thread context
1758 @param type type of object (SP_TYPE_FUNCTION or SP_TYPE_PROCEDURE)
1759 @param name name of procedure
1760 @param cp hash to look routine in
1761 @param cache_only if true perform cache-only lookup
1762 (Don't look in mysql.proc).
1763
1764 @retval
1765 NonNULL pointer to sp_head object for the procedure
1766 @retval
1767 NULL in case of error.
1768 */
1769
sp_find_routine(THD * thd,enum_sp_type type,sp_name * name,sp_cache ** cp,bool cache_only)1770 sp_head *sp_find_routine(THD *thd, enum_sp_type type, sp_name *name,
1771 sp_cache **cp, bool cache_only)
1772 {
1773 sp_head *sp;
1774 ulong depth= (type == SP_TYPE_PROCEDURE ?
1775 thd->variables.max_sp_recursion_depth :
1776 0);
1777 DBUG_ENTER("sp_find_routine");
1778 DBUG_PRINT("enter", ("name: %.*s.%.*s type: %d cache only %d",
1779 (int) name->m_db.length, name->m_db.str,
1780 (int) name->m_name.length, name->m_name.str,
1781 type, cache_only));
1782
1783 if ((sp= sp_cache_lookup(cp, name)))
1784 {
1785 ulong level;
1786 sp_head *new_sp;
1787 const char *returns= "";
1788 char definer[USER_HOST_BUFF_SIZE];
1789
1790 /*
1791 String buffer for RETURNS data type must have system charset;
1792 64 -- size of "returns" column of mysql.proc.
1793 */
1794 String retstr(64);
1795 retstr.set_charset(sp->get_creation_ctx()->get_client_cs());
1796
1797 DBUG_PRINT("info", ("found: 0x%lx", (ulong)sp));
1798 if (sp->m_first_free_instance)
1799 {
1800 DBUG_PRINT("info", ("first free: 0x%lx level: %lu flags %x",
1801 (ulong)sp->m_first_free_instance,
1802 sp->m_first_free_instance->m_recursion_level,
1803 sp->m_first_free_instance->m_flags));
1804 assert(!(sp->m_first_free_instance->m_flags & sp_head::IS_INVOKED));
1805 if (sp->m_first_free_instance->m_recursion_level > depth)
1806 {
1807 recursion_level_error(thd, sp);
1808 DBUG_RETURN(0);
1809 }
1810 DBUG_RETURN(sp->m_first_free_instance);
1811 }
1812 /*
1813 Actually depth could be +1 than the actual value in case a SP calls
1814 SHOW CREATE PROCEDURE. Hence, the linked list could hold up to one more
1815 instance.
1816 */
1817
1818 level= sp->m_last_cached_sp->m_recursion_level + 1;
1819 if (level > depth)
1820 {
1821 recursion_level_error(thd, sp);
1822 DBUG_RETURN(0);
1823 }
1824
1825 strxmov(definer, sp->m_definer_user.str, "@",
1826 sp->m_definer_host.str, NullS);
1827 if (type == SP_TYPE_FUNCTION)
1828 {
1829 sp_returns_type(thd, retstr, sp);
1830 returns= retstr.ptr();
1831 }
1832 if (db_load_routine(thd, type, name, &new_sp,
1833 sp->m_sql_mode, sp->m_params.str, returns,
1834 sp->m_body.str, *sp->m_chistics, definer,
1835 sp->m_created, sp->m_modified,
1836 sp->get_creation_ctx()) == SP_OK)
1837 {
1838 sp->m_last_cached_sp->m_next_cached_sp= new_sp;
1839 new_sp->m_recursion_level= level;
1840 new_sp->m_first_instance= sp;
1841 sp->m_last_cached_sp= sp->m_first_free_instance= new_sp;
1842 DBUG_PRINT("info", ("added level: 0x%lx, level: %lu, flags %x",
1843 (ulong)new_sp, new_sp->m_recursion_level,
1844 new_sp->m_flags));
1845 DBUG_RETURN(new_sp);
1846 }
1847 DBUG_RETURN(0);
1848 }
1849 if (!cache_only)
1850 {
1851 if (db_find_routine(thd, type, name, &sp) == SP_OK)
1852 {
1853 sp_cache_insert(cp, sp);
1854 DBUG_PRINT("info", ("added new: 0x%lx, level: %lu, flags %x",
1855 (ulong)sp, sp->m_recursion_level,
1856 sp->m_flags));
1857 }
1858 }
1859 DBUG_RETURN(sp);
1860 }
1861
1862
1863 /**
1864 This is used by sql_acl.cc:mysql_routine_grant() and is used to find
1865 the routines in 'routines'.
1866
1867 @param thd Thread handler
1868 @param routines List of needles in the hay stack
1869 @param is_proc Indicates whether routines in the list are procedures
1870 or functions.
1871
1872 @return
1873 @retval FALSE Found.
1874 @retval TRUE Not found
1875 */
1876
1877 bool
sp_exist_routines(THD * thd,TABLE_LIST * routines,bool is_proc)1878 sp_exist_routines(THD *thd, TABLE_LIST *routines, bool is_proc)
1879 {
1880 TABLE_LIST *routine;
1881 bool sp_object_found;
1882 DBUG_ENTER("sp_exists_routine");
1883 for (routine= routines; routine; routine= routine->next_global)
1884 {
1885 sp_name *name;
1886 LEX_CSTRING lex_db;
1887 LEX_STRING lex_name;
1888 lex_db.length= strlen(routine->db);
1889 lex_name.length= strlen(routine->table_name);
1890 lex_db.str= thd->strmake(routine->db, lex_db.length);
1891 lex_name.str= thd->strmake(routine->table_name, lex_name.length);
1892 name= new sp_name(lex_db, lex_name, true);
1893 name->init_qname(thd);
1894 sp_object_found= is_proc ? sp_find_routine(thd, SP_TYPE_PROCEDURE,
1895 name, &thd->sp_proc_cache,
1896 FALSE) != NULL :
1897 sp_find_routine(thd, SP_TYPE_FUNCTION,
1898 name, &thd->sp_func_cache,
1899 FALSE) != NULL;
1900 thd->get_stmt_da()->reset_condition_info(thd);
1901 if (! sp_object_found)
1902 {
1903 my_error(ER_SP_DOES_NOT_EXIST, MYF(0), is_proc ? "PROCEDURE" : "FUNCTION",
1904 routine->table_name);
1905 DBUG_RETURN(TRUE);
1906 }
1907 }
1908 DBUG_RETURN(FALSE);
1909 }
1910
1911
sp_sroutine_key(const uchar * ptr,size_t * plen,my_bool first)1912 extern "C" uchar* sp_sroutine_key(const uchar *ptr, size_t *plen,
1913 my_bool first)
1914 {
1915 Sroutine_hash_entry *rn= (Sroutine_hash_entry *)ptr;
1916 *plen= rn->mdl_request.key.length();
1917 return (uchar *)rn->mdl_request.key.ptr();
1918 }
1919
1920
1921 /**
1922 Auxilary function that adds new element to the set of stored routines
1923 used by statement.
1924
1925 In case when statement uses stored routines but does not need
1926 prelocking (i.e. it does not use any tables) we will access the
1927 elements of Query_tables_list::sroutines set on prepared statement
1928 re-execution. Because of this we have to allocate memory for both
1929 hash element and copy of its key in persistent arena.
1930
1931 @param prelocking_ctx Prelocking context of the statement
1932 @param arena Arena in which memory for new element will be
1933 allocated
1934 @param key Key for the hash representing set
1935 @param belong_to_view Uppermost view which uses this routine
1936 (0 if routine is not used by view)
1937
1938 @note
1939 Will also add element to end of 'Query_tables_list::sroutines_list' list.
1940
1941 @todo
1942 When we will got rid of these accesses on re-executions we will be
1943 able to allocate memory for hash elements in non-persitent arena
1944 and directly use key values from sp_head::m_sroutines sets instead
1945 of making their copies.
1946
1947 @retval
1948 TRUE new element was added.
1949 @retval
1950 FALSE element was not added (because it is already present in
1951 the set).
1952 */
1953
sp_add_used_routine(Query_tables_list * prelocking_ctx,Query_arena * arena,const MDL_key * key,TABLE_LIST * belong_to_view)1954 bool sp_add_used_routine(Query_tables_list *prelocking_ctx, Query_arena *arena,
1955 const MDL_key *key, TABLE_LIST *belong_to_view)
1956 {
1957 my_hash_init_opt(&prelocking_ctx->sroutines, system_charset_info,
1958 Query_tables_list::START_SROUTINES_HASH_SIZE,
1959 0, 0, sp_sroutine_key, 0, 0,
1960 PSI_INSTRUMENT_ME);
1961
1962 if (!my_hash_search(&prelocking_ctx->sroutines, key->ptr(), key->length()))
1963 {
1964 Sroutine_hash_entry *rn=
1965 (Sroutine_hash_entry *)arena->alloc(sizeof(Sroutine_hash_entry));
1966 if (!rn) // OOM. Error will be reported using fatal_error().
1967 return FALSE;
1968 MDL_REQUEST_INIT_BY_KEY(&rn->mdl_request,
1969 key, MDL_SHARED, MDL_TRANSACTION);
1970 if (my_hash_insert(&prelocking_ctx->sroutines, (uchar *)rn))
1971 return FALSE;
1972 prelocking_ctx->sroutines_list.link_in_list(rn, &rn->next);
1973 rn->belong_to_view= belong_to_view;
1974 rn->m_sp_cache_version= 0;
1975 return TRUE;
1976 }
1977 return FALSE;
1978 }
1979
1980
1981 /**
1982 Add routine which is explicitly used by statement to the set of stored
1983 routines used by this statement.
1984
1985 To be friendly towards prepared statements one should pass
1986 persistent arena as second argument.
1987
1988 @param prelocking_ctx Prelocking context of the statement
1989 @param arena Arena in which memory for new element of the set
1990 will be allocated
1991 @param rt Routine name
1992 @param rt_type Routine type (one of SP_TYPE_PROCEDURE/...)
1993
1994 @note
1995 Will also add element to end of 'Query_tables_list::sroutines_list' list
1996 (and will take into account that this is an explicitly used routine).
1997 */
1998
sp_add_used_routine(Query_tables_list * prelocking_ctx,Query_arena * arena,sp_name * rt,enum_sp_type rt_type)1999 void sp_add_used_routine(Query_tables_list *prelocking_ctx, Query_arena *arena,
2000 sp_name *rt, enum_sp_type rt_type)
2001 {
2002 MDL_key key((rt_type == SP_TYPE_FUNCTION) ? MDL_key::FUNCTION :
2003 MDL_key::PROCEDURE,
2004 rt->m_db.str, rt->m_name.str);
2005 (void)sp_add_used_routine(prelocking_ctx, arena, &key, 0);
2006 prelocking_ctx->sroutines_list_own_last= prelocking_ctx->sroutines_list.next;
2007 prelocking_ctx->sroutines_list_own_elements=
2008 prelocking_ctx->sroutines_list.elements;
2009 }
2010
2011
2012 /**
2013 Remove routines which are only indirectly used by statement from
2014 the set of routines used by this statement.
2015
2016 @param prelocking_ctx Prelocking context of the statement
2017 */
2018
sp_remove_not_own_routines(Query_tables_list * prelocking_ctx)2019 void sp_remove_not_own_routines(Query_tables_list *prelocking_ctx)
2020 {
2021 Sroutine_hash_entry *not_own_rt, *next_rt;
2022 for (not_own_rt= *prelocking_ctx->sroutines_list_own_last;
2023 not_own_rt; not_own_rt= next_rt)
2024 {
2025 /*
2026 It is safe to obtain not_own_rt->next after calling hash_delete() now
2027 but we want to be more future-proof.
2028 */
2029 next_rt= not_own_rt->next;
2030 my_hash_delete(&prelocking_ctx->sroutines, (uchar *)not_own_rt);
2031 }
2032
2033 *prelocking_ctx->sroutines_list_own_last= NULL;
2034 prelocking_ctx->sroutines_list.next= prelocking_ctx->sroutines_list_own_last;
2035 prelocking_ctx->sroutines_list.elements=
2036 prelocking_ctx->sroutines_list_own_elements;
2037 }
2038
2039
2040 /**
2041 Add contents of hash representing set of routines to the set of
2042 routines used by statement.
2043
2044 @param thd Thread context
2045 @param prelocking_ctx Prelocking context of the statement
2046 @param src Hash representing set from which routines will
2047 be added
2048 @param belong_to_view Uppermost view which uses these routines, 0 if none
2049
2050 @note It will also add elements to end of
2051 'Query_tables_list::sroutines_list' list.
2052 */
2053
2054 void
sp_update_stmt_used_routines(THD * thd,Query_tables_list * prelocking_ctx,HASH * src,TABLE_LIST * belong_to_view)2055 sp_update_stmt_used_routines(THD *thd, Query_tables_list *prelocking_ctx,
2056 HASH *src, TABLE_LIST *belong_to_view)
2057 {
2058 for (uint i=0 ; i < src->records ; i++)
2059 {
2060 Sroutine_hash_entry *rt= (Sroutine_hash_entry *)my_hash_element(src, i);
2061 (void)sp_add_used_routine(prelocking_ctx, thd->stmt_arena,
2062 &rt->mdl_request.key, belong_to_view);
2063 }
2064 }
2065
2066
2067 /**
2068 Add contents of list representing set of routines to the set of
2069 routines used by statement.
2070
2071 @param thd Thread context
2072 @param prelocking_ctx Prelocking context of the statement
2073 @param src List representing set from which routines will
2074 be added
2075 @param belong_to_view Uppermost view which uses these routines, 0 if none
2076
2077 @note It will also add elements to end of
2078 'Query_tables_list::sroutines_list' list.
2079 */
2080
sp_update_stmt_used_routines(THD * thd,Query_tables_list * prelocking_ctx,SQL_I_List<Sroutine_hash_entry> * src,TABLE_LIST * belong_to_view)2081 void sp_update_stmt_used_routines(THD *thd, Query_tables_list *prelocking_ctx,
2082 SQL_I_List<Sroutine_hash_entry> *src,
2083 TABLE_LIST *belong_to_view)
2084 {
2085 for (Sroutine_hash_entry *rt= src->first; rt; rt= rt->next)
2086 (void)sp_add_used_routine(prelocking_ctx, thd->stmt_arena,
2087 &rt->mdl_request.key, belong_to_view);
2088 }
2089
2090
2091 /**
2092 A helper wrapper around sp_cache_routine() to use from
2093 prelocking until 'sp_name' is eradicated as a class.
2094 */
2095
sp_cache_routine(THD * thd,Sroutine_hash_entry * rt,bool lookup_only,sp_head ** sp)2096 int sp_cache_routine(THD *thd, Sroutine_hash_entry *rt,
2097 bool lookup_only, sp_head **sp)
2098 {
2099 char qname_buff[NAME_LEN*2+1+1];
2100 sp_name name(&rt->mdl_request.key, qname_buff);
2101 MDL_key::enum_mdl_namespace mdl_type= rt->mdl_request.key.mdl_namespace();
2102 enum_sp_type type= (mdl_type == MDL_key::FUNCTION) ?
2103 SP_TYPE_FUNCTION : SP_TYPE_PROCEDURE;
2104
2105 /*
2106 Check that we have an MDL lock on this routine, unless it's a top-level
2107 CALL. The assert below should be unambiguous: the first element
2108 in sroutines_list has an MDL lock unless it's a top-level call, or a
2109 trigger, but triggers can't occur here (see the preceding assert).
2110 */
2111 assert(rt->mdl_request.ticket || rt == thd->lex->sroutines_list.first);
2112
2113 return sp_cache_routine(thd, type, &name, lookup_only, sp);
2114 }
2115
2116
2117 /**
2118 Ensure that routine is present in cache by loading it from the mysql.proc
2119 table if needed. If the routine is present but old, reload it.
2120 Emit an appropriate error if there was a problem during
2121 loading.
2122
2123 @param[in] thd Thread context.
2124 @param[in] type Type of object (SP_TYPE_FUNCTION or SP_TYPE_PROCEDURE).
2125 @param[in] name Name of routine.
2126 @param[in] lookup_only Only check that the routine is in the cache.
2127 If it's not, don't try to load. If it is present,
2128 but old, don't try to reload.
2129 @param[out] sp Pointer to sp_head object for routine, NULL if routine was
2130 not found.
2131
2132 @retval 0 Either routine is found and was succesfully loaded into cache
2133 or it does not exist.
2134 @retval non-0 Error while loading routine from mysql,proc table.
2135 */
2136
sp_cache_routine(THD * thd,enum_sp_type type,sp_name * name,bool lookup_only,sp_head ** sp)2137 int sp_cache_routine(THD *thd, enum_sp_type type, sp_name *name,
2138 bool lookup_only, sp_head **sp)
2139 {
2140 int ret= 0;
2141 sp_cache **spc= (type == SP_TYPE_FUNCTION) ?
2142 &thd->sp_func_cache : &thd->sp_proc_cache;
2143
2144 DBUG_ENTER("sp_cache_routine");
2145
2146 assert(type == SP_TYPE_FUNCTION || type == SP_TYPE_PROCEDURE);
2147
2148
2149 *sp= sp_cache_lookup(spc, name);
2150
2151 if (lookup_only)
2152 DBUG_RETURN(SP_OK);
2153
2154 if (*sp)
2155 {
2156 sp_cache_flush_obsolete(spc, sp);
2157 if (*sp)
2158 DBUG_RETURN(SP_OK);
2159 }
2160
2161 switch ((ret= db_find_routine(thd, type, name, sp)))
2162 {
2163 case SP_OK:
2164 sp_cache_insert(spc, *sp);
2165 break;
2166 case SP_KEY_NOT_FOUND:
2167 ret= SP_OK;
2168 break;
2169 default:
2170 /* Query might have been killed, don't set error. */
2171 if (thd->killed)
2172 break;
2173 /*
2174 Any error when loading an existing routine is either some problem
2175 with the mysql.proc table, or a parse error because the contents
2176 has been tampered with (in which case we clear that error).
2177 */
2178 if (ret == SP_PARSE_ERROR)
2179 thd->clear_error();
2180 /*
2181 If we cleared the parse error, or when db_find_routine() flagged
2182 an error with it's return value without calling my_error(), we
2183 set the generic "mysql.proc table corrupt" error here.
2184 */
2185 if (! thd->is_error())
2186 {
2187 /*
2188 SP allows full NAME_LEN chars thus he have to allocate enough
2189 size in bytes. Otherwise there is stack overrun could happen
2190 if multibyte sequence is `name`. `db` is still safe because the
2191 rest of the server checks agains NAME_LEN bytes and not chars.
2192 Hence, the overrun happens only if the name is in length > 32 and
2193 uses multibyte (cyrillic, greek, etc.)
2194 */
2195 char n[NAME_LEN*2+2];
2196
2197 /* m_qname.str is not always \0 terminated */
2198 memcpy(n, name->m_qname.str, name->m_qname.length);
2199 n[name->m_qname.length]= '\0';
2200 my_error(ER_SP_PROC_TABLE_CORRUPT, MYF(0), n, ret);
2201 }
2202 break;
2203 }
2204 DBUG_RETURN(ret);
2205 }
2206
2207
2208 /**
2209 Generates the CREATE... string from the table information.
2210
2211 @return
2212 Returns TRUE on success, FALSE on (alloc) failure.
2213 */
create_string(THD * thd,String * buf,enum_sp_type type,const char * db,size_t dblen,const char * name,size_t namelen,const char * params,size_t paramslen,const char * returns,size_t returnslen,const char * body,size_t bodylen,st_sp_chistics * chistics,const LEX_CSTRING & definer_user,const LEX_CSTRING & definer_host,sql_mode_t sql_mode)2214 static bool create_string(THD *thd, String *buf,
2215 enum_sp_type type,
2216 const char *db, size_t dblen,
2217 const char *name, size_t namelen,
2218 const char *params, size_t paramslen,
2219 const char *returns, size_t returnslen,
2220 const char *body, size_t bodylen,
2221 st_sp_chistics *chistics,
2222 const LEX_CSTRING &definer_user,
2223 const LEX_CSTRING &definer_host,
2224 sql_mode_t sql_mode)
2225 {
2226 sql_mode_t old_sql_mode= thd->variables.sql_mode;
2227 /* Make some room to begin with */
2228 if (buf->alloc(100 + dblen + 1 + namelen + paramslen + returnslen + bodylen +
2229 chistics->comment.length + 10 /* length of " DEFINER= "*/ +
2230 USER_HOST_BUFF_SIZE))
2231 return FALSE;
2232
2233 thd->variables.sql_mode= sql_mode;
2234 buf->append(STRING_WITH_LEN("CREATE "));
2235 append_definer(thd, buf, definer_user, definer_host);
2236 if (type == SP_TYPE_FUNCTION)
2237 buf->append(STRING_WITH_LEN("FUNCTION "));
2238 else
2239 buf->append(STRING_WITH_LEN("PROCEDURE "));
2240 if (dblen > 0)
2241 {
2242 append_identifier(thd, buf, db, dblen);
2243 buf->append('.');
2244 }
2245 append_identifier(thd, buf, name, namelen);
2246 buf->append('(');
2247 buf->append(params, paramslen);
2248 buf->append(')');
2249 if (type == SP_TYPE_FUNCTION)
2250 {
2251 buf->append(STRING_WITH_LEN(" RETURNS "));
2252 buf->append(returns, returnslen);
2253 }
2254 buf->append('\n');
2255 switch (chistics->daccess) {
2256 case SP_NO_SQL:
2257 buf->append(STRING_WITH_LEN(" NO SQL\n"));
2258 break;
2259 case SP_READS_SQL_DATA:
2260 buf->append(STRING_WITH_LEN(" READS SQL DATA\n"));
2261 break;
2262 case SP_MODIFIES_SQL_DATA:
2263 buf->append(STRING_WITH_LEN(" MODIFIES SQL DATA\n"));
2264 break;
2265 case SP_DEFAULT_ACCESS:
2266 case SP_CONTAINS_SQL:
2267 /* Do nothing */
2268 break;
2269 }
2270 if (chistics->detistic)
2271 buf->append(STRING_WITH_LEN(" DETERMINISTIC\n"));
2272 if (chistics->suid == SP_IS_NOT_SUID)
2273 buf->append(STRING_WITH_LEN(" SQL SECURITY INVOKER\n"));
2274 if (chistics->comment.length)
2275 {
2276 buf->append(STRING_WITH_LEN(" COMMENT "));
2277 append_unescaped(buf, chistics->comment.str, chistics->comment.length);
2278 buf->append('\n');
2279 }
2280 buf->append(body, bodylen);
2281 thd->variables.sql_mode= old_sql_mode;
2282 return TRUE;
2283 }
2284
2285
2286 /**
2287 @brief The function loads sp_head struct for information schema purposes
2288 (used for I_S ROUTINES & PARAMETERS tables).
2289
2290 @param[in] thd thread handler
2291 @param[in] proc_table mysql.proc table structurte
2292 @param[in] db database name
2293 @param[in] name sp name
2294 @param[in] sql_mode SQL mode
2295 @param[in] type Routine type
2296 @param[in] returns 'returns' string
2297 @param[in] params parameters definition string
2298 @param[out] free_sp_head returns 1 if we need to free sp_head struct
2299 otherwise returns 0
2300
2301 @return Pointer on sp_head struct
2302 @retval # Pointer on sp_head struct
2303 @retval 0 error
2304 */
2305
2306 sp_head *
sp_load_for_information_schema(THD * thd,TABLE * proc_table,String * db,String * name,sql_mode_t sql_mode,enum_sp_type type,const char * returns,const char * params,bool * free_sp_head)2307 sp_load_for_information_schema(THD *thd, TABLE *proc_table, String *db,
2308 String *name, sql_mode_t sql_mode,
2309 enum_sp_type type,
2310 const char *returns, const char *params,
2311 bool *free_sp_head)
2312 {
2313 const char *sp_body;
2314 String defstr;
2315 struct st_sp_chistics sp_chistics;
2316 const LEX_CSTRING definer_user= EMPTY_CSTR;
2317 const LEX_CSTRING definer_host= EMPTY_CSTR;
2318 LEX_CSTRING sp_db_str;
2319 LEX_STRING sp_name_str;
2320 sp_head *sp;
2321 sp_cache **spc= (type == SP_TYPE_FUNCTION) ?
2322 &thd->sp_func_cache : &thd->sp_proc_cache;
2323 sp_db_str.str= db->c_ptr();
2324 sp_db_str.length= db->length();
2325 sp_name_str.str= name->c_ptr();
2326 sp_name_str.length= name->length();
2327 sp_name sp_name_obj(sp_db_str, sp_name_str, true);
2328 sp_name_obj.init_qname(thd);
2329 *free_sp_head= 0;
2330 if ((sp= sp_cache_lookup(spc, &sp_name_obj)))
2331 {
2332 return sp;
2333 }
2334
2335 LEX *old_lex= thd->lex, newlex;
2336 Stored_program_creation_ctx *creation_ctx=
2337 Stored_routine_creation_ctx::load_from_db(thd, &sp_name_obj, proc_table);
2338 sp_body= (type == SP_TYPE_FUNCTION) ? "RETURN NULL" : "BEGIN END";
2339 memset(&sp_chistics, 0, sizeof(sp_chistics));
2340 defstr.set_charset(creation_ctx->get_client_cs());
2341 if (!create_string(thd, &defstr, type,
2342 sp_db_str.str, sp_db_str.length,
2343 sp_name_obj.m_name.str, sp_name_obj.m_name.length,
2344 params, strlen(params),
2345 returns, strlen(returns),
2346 sp_body, strlen(sp_body),
2347 &sp_chistics, definer_user, definer_host, sql_mode))
2348 return 0;
2349
2350 thd->lex= &newlex;
2351 newlex.thd= thd;
2352 newlex.set_current_select(NULL);
2353 sp= sp_compile(thd, &defstr, sql_mode, creation_ctx);
2354 *free_sp_head= 1;
2355 thd->lex->sphead= NULL;
2356 lex_end(thd->lex);
2357 thd->lex= old_lex;
2358 return sp;
2359 }
2360
2361
2362 /**
2363 Start parsing of a stored program.
2364
2365 This function encapsulates all the steps necessary to initialize sp_head to
2366 start parsing SP.
2367
2368 Every successful call of sp_start_parsing() must finish with
2369 sp_finish_parsing().
2370
2371 @param thd Thread context.
2372 @param sp_type The stored program type
2373 @param sp_name The stored progam name
2374
2375 @return properly initialized sp_head-instance in case of success, or NULL is
2376 case of out-of-memory error.
2377 */
sp_start_parsing(THD * thd,enum_sp_type sp_type,sp_name * sp_name)2378 sp_head *sp_start_parsing(THD *thd,
2379 enum_sp_type sp_type,
2380 sp_name *sp_name)
2381 {
2382 // The order is important:
2383 // 1. new sp_head()
2384
2385 sp_head *sp= new sp_head(sp_type);
2386
2387 if (!sp)
2388 return NULL;
2389
2390 // 2. start_parsing_sp_body()
2391
2392 sp->m_parser_data.start_parsing_sp_body(thd, sp);
2393
2394 // 3. finish initialization.
2395
2396 sp->m_root_parsing_ctx= new (thd->mem_root) sp_pcontext(thd);
2397
2398 if (!sp->m_root_parsing_ctx)
2399 return NULL;
2400
2401 thd->lex->set_sp_current_parsing_ctx(sp->m_root_parsing_ctx);
2402
2403 // 4. set name.
2404
2405 sp->init_sp_name(thd, sp_name);
2406
2407 return sp;
2408 }
2409
2410
2411 /**
2412 Finish parsing of a stored program.
2413
2414 This is a counterpart of sp_start_parsing().
2415
2416 @param thd Thread context.
2417 */
sp_finish_parsing(THD * thd)2418 void sp_finish_parsing(THD *thd)
2419 {
2420 sp_head *sp= thd->lex->sphead;
2421
2422 assert(sp);
2423
2424 sp->set_body_end(thd);
2425
2426 sp->m_parser_data.finish_parsing_sp_body(thd);
2427 }
2428
2429
2430 /// @return Item_result code corresponding to the RETURN-field type code.
sp_map_result_type(enum enum_field_types type)2431 Item_result sp_map_result_type(enum enum_field_types type)
2432 {
2433 switch (type) {
2434 case MYSQL_TYPE_BIT:
2435 case MYSQL_TYPE_TINY:
2436 case MYSQL_TYPE_SHORT:
2437 case MYSQL_TYPE_LONG:
2438 case MYSQL_TYPE_LONGLONG:
2439 case MYSQL_TYPE_INT24:
2440 return INT_RESULT;
2441 case MYSQL_TYPE_DECIMAL:
2442 case MYSQL_TYPE_NEWDECIMAL:
2443 return DECIMAL_RESULT;
2444 case MYSQL_TYPE_FLOAT:
2445 case MYSQL_TYPE_DOUBLE:
2446 return REAL_RESULT;
2447 default:
2448 return STRING_RESULT;
2449 }
2450 }
2451
2452
2453 /// @return Item::Type code corresponding to the RETURN-field type code.
sp_map_item_type(enum enum_field_types type)2454 Item::Type sp_map_item_type(enum enum_field_types type)
2455 {
2456 switch (type) {
2457 case MYSQL_TYPE_BIT:
2458 case MYSQL_TYPE_TINY:
2459 case MYSQL_TYPE_SHORT:
2460 case MYSQL_TYPE_LONG:
2461 case MYSQL_TYPE_LONGLONG:
2462 case MYSQL_TYPE_INT24:
2463 return Item::INT_ITEM;
2464 case MYSQL_TYPE_DECIMAL:
2465 case MYSQL_TYPE_NEWDECIMAL:
2466 return Item::DECIMAL_ITEM;
2467 case MYSQL_TYPE_FLOAT:
2468 case MYSQL_TYPE_DOUBLE:
2469 return Item::REAL_ITEM;
2470 default:
2471 return Item::STRING_ITEM;
2472 }
2473 }
2474
2475
2476 /**
2477 @param lex LEX-object, representing an SQL-statement inside SP.
2478
2479 @return a combination of:
2480 - sp_head::MULTI_RESULTS: added if the 'cmd' is a command that might
2481 result in multiple result sets being sent back.
2482 - sp_head::CONTAINS_DYNAMIC_SQL: added if 'cmd' is one of PREPARE,
2483 EXECUTE, DEALLOCATE.
2484 */
sp_get_flags_for_command(LEX * lex)2485 uint sp_get_flags_for_command(LEX *lex)
2486 {
2487 uint flags;
2488
2489 switch (lex->sql_command) {
2490 case SQLCOM_SELECT:
2491 if (lex->result)
2492 {
2493 flags= 0; /* This is a SELECT with INTO clause */
2494 break;
2495 }
2496 /* fallthrough */
2497 case SQLCOM_ANALYZE:
2498 case SQLCOM_OPTIMIZE:
2499 case SQLCOM_PRELOAD_KEYS:
2500 case SQLCOM_ASSIGN_TO_KEYCACHE:
2501 case SQLCOM_CHECKSUM:
2502 case SQLCOM_CHECK:
2503 case SQLCOM_HA_READ:
2504 case SQLCOM_SHOW_BINLOGS:
2505 case SQLCOM_SHOW_BINLOG_EVENTS:
2506 case SQLCOM_SHOW_RELAYLOG_EVENTS:
2507 case SQLCOM_SHOW_CHARSETS:
2508 case SQLCOM_SHOW_COLLATIONS:
2509 case SQLCOM_SHOW_CREATE:
2510 case SQLCOM_SHOW_CREATE_DB:
2511 case SQLCOM_SHOW_CREATE_FUNC:
2512 case SQLCOM_SHOW_CREATE_PROC:
2513 case SQLCOM_SHOW_CREATE_EVENT:
2514 case SQLCOM_SHOW_CREATE_TRIGGER:
2515 case SQLCOM_SHOW_DATABASES:
2516 case SQLCOM_SHOW_ERRORS:
2517 case SQLCOM_SHOW_FIELDS:
2518 case SQLCOM_SHOW_FUNC_CODE:
2519 case SQLCOM_SHOW_GRANTS:
2520 case SQLCOM_SHOW_ENGINE_STATUS:
2521 case SQLCOM_SHOW_ENGINE_LOGS:
2522 case SQLCOM_SHOW_ENGINE_MUTEX:
2523 case SQLCOM_SHOW_EVENTS:
2524 case SQLCOM_SHOW_KEYS:
2525 case SQLCOM_SHOW_MASTER_STAT:
2526 case SQLCOM_SHOW_OPEN_TABLES:
2527 case SQLCOM_SHOW_PRIVILEGES:
2528 case SQLCOM_SHOW_PROCESSLIST:
2529 case SQLCOM_SHOW_PROC_CODE:
2530 case SQLCOM_SHOW_SLAVE_HOSTS:
2531 case SQLCOM_SHOW_SLAVE_STAT:
2532 case SQLCOM_SHOW_STATUS:
2533 case SQLCOM_SHOW_STATUS_FUNC:
2534 case SQLCOM_SHOW_STATUS_PROC:
2535 case SQLCOM_SHOW_STORAGE_ENGINES:
2536 case SQLCOM_SHOW_TABLES:
2537 case SQLCOM_SHOW_TABLE_STATUS:
2538 case SQLCOM_SHOW_VARIABLES:
2539 case SQLCOM_SHOW_WARNS:
2540 case SQLCOM_REPAIR:
2541 flags= sp_head::MULTI_RESULTS;
2542 break;
2543 /*
2544 EXECUTE statement may return a result set, but doesn't have to.
2545 We can't, however, know it in advance, and therefore must add
2546 this statement here. This is ok, as is equivalent to a result-set
2547 statement within an IF condition.
2548 */
2549 case SQLCOM_EXECUTE:
2550 flags= sp_head::MULTI_RESULTS | sp_head::CONTAINS_DYNAMIC_SQL;
2551 break;
2552 case SQLCOM_PREPARE:
2553 case SQLCOM_DEALLOCATE_PREPARE:
2554 flags= sp_head::CONTAINS_DYNAMIC_SQL;
2555 break;
2556 case SQLCOM_CREATE_TABLE:
2557 if (lex->create_info.options & HA_LEX_CREATE_TMP_TABLE)
2558 flags= 0;
2559 else
2560 flags= sp_head::HAS_COMMIT_OR_ROLLBACK;
2561 break;
2562 case SQLCOM_DROP_TABLE:
2563 if (lex->drop_temporary)
2564 flags= 0;
2565 else
2566 flags= sp_head::HAS_COMMIT_OR_ROLLBACK;
2567 break;
2568 case SQLCOM_FLUSH:
2569 flags= sp_head::HAS_SQLCOM_FLUSH;
2570 break;
2571 case SQLCOM_RESET:
2572 flags= sp_head::HAS_SQLCOM_RESET;
2573 break;
2574 case SQLCOM_CREATE_INDEX:
2575 case SQLCOM_CREATE_COMPRESSION_DICTIONARY:
2576 case SQLCOM_CREATE_DB:
2577 case SQLCOM_CREATE_VIEW:
2578 case SQLCOM_CREATE_TRIGGER:
2579 case SQLCOM_CREATE_USER:
2580 case SQLCOM_ALTER_TABLE:
2581 case SQLCOM_GRANT:
2582 case SQLCOM_REVOKE:
2583 case SQLCOM_BEGIN:
2584 case SQLCOM_RENAME_TABLE:
2585 case SQLCOM_RENAME_USER:
2586 case SQLCOM_DROP_INDEX:
2587 case SQLCOM_DROP_COMPRESSION_DICTIONARY:
2588 case SQLCOM_DROP_DB:
2589 case SQLCOM_REVOKE_ALL:
2590 case SQLCOM_DROP_USER:
2591 case SQLCOM_DROP_VIEW:
2592 case SQLCOM_DROP_TRIGGER:
2593 case SQLCOM_TRUNCATE:
2594 case SQLCOM_COMMIT:
2595 case SQLCOM_ROLLBACK:
2596 case SQLCOM_LOAD:
2597 case SQLCOM_LOCK_TABLES:
2598 case SQLCOM_CREATE_PROCEDURE:
2599 case SQLCOM_CREATE_SPFUNCTION:
2600 case SQLCOM_ALTER_PROCEDURE:
2601 case SQLCOM_ALTER_FUNCTION:
2602 case SQLCOM_DROP_PROCEDURE:
2603 case SQLCOM_DROP_FUNCTION:
2604 case SQLCOM_CREATE_EVENT:
2605 case SQLCOM_ALTER_EVENT:
2606 case SQLCOM_DROP_EVENT:
2607 case SQLCOM_INSTALL_PLUGIN:
2608 case SQLCOM_UNINSTALL_PLUGIN:
2609 case SQLCOM_ALTER_DB_UPGRADE:
2610 case SQLCOM_ALTER_DB:
2611 case SQLCOM_ALTER_USER:
2612 case SQLCOM_CREATE_SERVER:
2613 case SQLCOM_ALTER_SERVER:
2614 case SQLCOM_DROP_SERVER:
2615 case SQLCOM_CHANGE_MASTER:
2616 case SQLCOM_CHANGE_REPLICATION_FILTER:
2617 case SQLCOM_SLAVE_START:
2618 case SQLCOM_SLAVE_STOP:
2619 case SQLCOM_ALTER_INSTANCE:
2620 flags= sp_head::HAS_COMMIT_OR_ROLLBACK;
2621 break;
2622 default:
2623 flags= lex->describe ? sp_head::MULTI_RESULTS : 0;
2624 break;
2625 }
2626 return flags;
2627 }
2628
2629
2630 /**
2631 Check that the name 'ident' is ok. It's assumed to be an 'ident'
2632 from the parser, so we only have to check length and trailing spaces.
2633 The former is a standard requirement (and 'show status' assumes a
2634 non-empty name), the latter is a mysql:ism as trailing spaces are
2635 removed by get_field().
2636
2637 @retval true bad name
2638 @retval false name is ok
2639 */
2640
sp_check_name(LEX_STRING * ident)2641 bool sp_check_name(LEX_STRING *ident)
2642 {
2643 assert(ident != NULL && ident->str != NULL);
2644
2645 if (!ident->str[0] || ident->str[ident->length-1] == ' ')
2646 {
2647 my_error(ER_SP_WRONG_NAME, MYF(0), ident->str);
2648 return true;
2649 }
2650
2651 LEX_CSTRING ident_cstr= {ident->str, ident->length};
2652 if (check_string_char_length(ident_cstr, "", NAME_CHAR_LEN,
2653 system_charset_info, 1))
2654 {
2655 my_error(ER_TOO_LONG_IDENT, MYF(0), ident->str);
2656 return true;
2657 }
2658
2659 return false;
2660 }
2661
2662
2663 /**
2664 Simple function for adding an explicitly named (systems) table to
2665 the global table list, e.g. "mysql", "proc".
2666 */
sp_add_to_query_tables(THD * thd,LEX * lex,const char * db,const char * name)2667 TABLE_LIST *sp_add_to_query_tables(THD *thd, LEX *lex,
2668 const char *db, const char *name)
2669 {
2670 TABLE_LIST *table= static_cast<TABLE_LIST*>(thd->alloc(sizeof(TABLE_LIST)));
2671
2672 if (!table)
2673 return NULL;
2674
2675 size_t db_length= strlen(db);
2676 size_t table_name_length= strlen(name);
2677
2678 table->init_one_table(thd->strmake(db, db_length), db_length,
2679 thd->strmake(name, table_name_length),
2680 table_name_length,
2681 thd->mem_strdup(name),
2682 TL_IGNORE, MDL_SHARED_NO_WRITE);
2683
2684 table->select_lex= lex->current_select();
2685 table->cacheable_table= 1;
2686
2687 lex->add_to_query_tables(table);
2688
2689 return table;
2690 }
2691
2692
2693 /**
2694 Prepare an Item for evaluation (call of fix_fields).
2695
2696 @param thd thread handler
2697 @param it_addr pointer on item reference
2698
2699 @retval
2700 NULL error
2701 @retval
2702 non-NULL prepared item
2703 */
sp_prepare_func_item(THD * thd,Item ** it_addr)2704 Item *sp_prepare_func_item(THD* thd, Item **it_addr)
2705 {
2706 it_addr= (*it_addr)->this_item_addr(thd, it_addr);
2707
2708 if (!(*it_addr)->fixed &&
2709 ((*it_addr)->fix_fields(thd, it_addr) ||
2710 (*it_addr)->check_cols(1)))
2711 {
2712 DBUG_PRINT("info", ("fix_fields() failed"));
2713 return NULL;
2714 }
2715
2716 return *it_addr;
2717 }
2718
2719
2720 /**
2721 Evaluate an expression and store the result in the field.
2722
2723 @param thd current thread object
2724 @param result_field the field to store the result
2725 @param expr_item_ptr the root item of the expression
2726
2727 @retval
2728 FALSE on success
2729 @retval
2730 TRUE on error
2731 */
sp_eval_expr(THD * thd,Field * result_field,Item ** expr_item_ptr)2732 bool sp_eval_expr(THD *thd, Field *result_field, Item **expr_item_ptr)
2733 {
2734 Item *expr_item;
2735 Strict_error_handler strict_handler(Strict_error_handler::
2736 ENABLE_SET_SELECT_STRICT_ERROR_HANDLER);
2737 enum_check_fields save_count_cuted_fields= thd->count_cuted_fields;
2738 unsigned int stmt_unsafe_rollback_flags=
2739 thd->get_transaction()->get_unsafe_rollback_flags(Transaction_ctx::STMT);
2740
2741 if (!*expr_item_ptr)
2742 goto error;
2743
2744 if (!(expr_item= sp_prepare_func_item(thd, expr_item_ptr)))
2745 goto error;
2746
2747 /*
2748 Set THD flags to emit warnings/errors in case of overflow/type errors
2749 during saving the item into the field.
2750
2751 Save original values and restore them after save.
2752 */
2753
2754 thd->count_cuted_fields= CHECK_FIELD_ERROR_FOR_NULL;
2755 thd->get_transaction()->reset_unsafe_rollback_flags(Transaction_ctx::STMT);
2756
2757 /*
2758 Variables declared within SP/SF with DECLARE keyword like
2759 DECLARE var INTEGER;
2760 will follow the rules of assignment corresponding to the data type column
2761 in a table. So, STRICT mode gives error if an invalid value is assigned
2762 to the variable here.
2763 */
2764 if (thd->is_strict_mode() && !thd->lex->is_ignore())
2765 thd->push_internal_handler(&strict_handler);
2766 // Save the value in the field. Convert the value if needed.
2767 expr_item->save_in_field(result_field, false);
2768
2769 if (thd->is_strict_mode() && !thd->lex->is_ignore())
2770 thd->pop_internal_handler();
2771 thd->count_cuted_fields= save_count_cuted_fields;
2772 thd->get_transaction()->set_unsafe_rollback_flags(Transaction_ctx::STMT,
2773 stmt_unsafe_rollback_flags);
2774
2775 if (!thd->is_error())
2776 return false;
2777
2778 error:
2779 /*
2780 In case of error during evaluation, leave the result field set to NULL.
2781 Sic: we can't do it in the beginning of the function because the
2782 result field might be needed for its own re-evaluation, e.g. case of
2783 set x = x + 1;
2784 */
2785 result_field->set_null();
2786 return true;
2787 }
2788
2789
2790 /**
2791 Return a string representation of the Item value.
2792
2793 @param thd Thread context.
2794 @param str String buffer for representation of the value.
2795
2796 @note
2797 If the item has a string result type, the string is escaped
2798 according to its character set.
2799
2800 @retval NULL on error
2801 @retval non-NULL a pointer to valid a valid string on success
2802 */
sp_get_item_value(THD * thd,Item * item,String * str)2803 String *sp_get_item_value(THD *thd, Item *item, String *str)
2804 {
2805 switch (item->result_type()) {
2806 case REAL_RESULT:
2807 case INT_RESULT:
2808 case DECIMAL_RESULT:
2809 if (item->field_type() != MYSQL_TYPE_BIT)
2810 return item->val_str(str);
2811 else {/* Bit type is handled as binary string */}
2812 // Fall through
2813 case STRING_RESULT:
2814 {
2815 String *result= item->val_str(str);
2816
2817 if (!result)
2818 return NULL;
2819
2820 {
2821 char buf_holder[STRING_BUFFER_USUAL_SIZE];
2822 String buf(buf_holder, sizeof(buf_holder), result->charset());
2823 const CHARSET_INFO *cs= thd->variables.character_set_client;
2824
2825 /* We must reset length of the buffer, because of String specificity. */
2826 buf.length(0);
2827
2828 buf.append('_');
2829 buf.append(result->charset()->csname);
2830 if (cs->escape_with_backslash_is_dangerous)
2831 buf.append(' ');
2832 append_query_string(thd, cs, result, &buf);
2833 buf.append(" COLLATE '");
2834 buf.append(item->collation.collation->name);
2835 buf.append('\'');
2836 str->copy(buf);
2837
2838 return str;
2839 }
2840 }
2841
2842 case ROW_RESULT:
2843 default:
2844 return NULL;
2845 }
2846 }
2847