sp.cc 65.2 KB
Newer Older
1 2 3 4
/* Copyright (C) 2002 MySQL AB

   This program is free software; you can redistribute it and/or modify
   it under the terms of the GNU General Public License as published by
5
   the Free Software Foundation; version 2 of the License.
6 7 8 9 10 11 12 13 14 15 16 17 18

   This program is distributed in the hope that it will be useful,
   but WITHOUT ANY WARRANTY; without even the implied warranty of
   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
   GNU General Public License for more details.

   You should have received a copy of the GNU General Public License
   along with this program; if not, write to the Free Software
   Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA */

#include "mysql_priv.h"
#include "sp.h"
#include "sp_head.h"
19
#include "sp_cache.h"
20
#include "sql_trigger.h"
21

22 23
#include <my_user.h>

24 25
static bool
create_string(THD *thd, String *buf,
26
	      int sp_type,
27
	      const char *db, ulong dblen,
28
	      const char *name, ulong namelen,
29 30 31
	      const char *params, ulong paramslen,
	      const char *returns, ulong returnslen,
	      const char *body, ulong bodylen,
32 33
	      st_sp_chistics *chistics,
              const LEX_STRING *definer_user,
Sergey Glukhov's avatar
Sergey Glukhov committed
34 35 36
              const LEX_STRING *definer_host,
              ulong sql_mode);

37 38 39 40
static int
db_load_routine(THD *thd, int type, sp_name *name, sp_head **sphp,
                ulong sql_mode, const char *params, const char *returns,
                const char *body, st_sp_chistics &chistics,
41 42
                const char *definer, longlong created, longlong modified,
                Stored_program_creation_ctx *creation_ctx);
43

44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131
static const
TABLE_FIELD_TYPE proc_table_fields[MYSQL_PROC_FIELD_COUNT] =
{
  {
    { C_STRING_WITH_LEN("db") },
    { C_STRING_WITH_LEN("char(64)") },
    { C_STRING_WITH_LEN("utf8") }
  },
  {
    { C_STRING_WITH_LEN("name") },
    { C_STRING_WITH_LEN("char(64)") },
    { C_STRING_WITH_LEN("utf8") }
  },
  {
    { C_STRING_WITH_LEN("type") },
    { C_STRING_WITH_LEN("enum('FUNCTION','PROCEDURE')") },
    { NULL, 0 }
  },
  {
    { C_STRING_WITH_LEN("specific_name") },
    { C_STRING_WITH_LEN("char(64)") },
    { C_STRING_WITH_LEN("utf8") }
  },
  {
    { C_STRING_WITH_LEN("language") },
    { C_STRING_WITH_LEN("enum('SQL')") },
    { NULL, 0 }
  },
  {
    { C_STRING_WITH_LEN("sql_data_access") },
    { C_STRING_WITH_LEN("enum('CONTAINS_SQL','NO_SQL','READS_SQL_DATA','MODIFIES_SQL_DATA')") },
    { NULL, 0 }
  },
  {
    { C_STRING_WITH_LEN("is_deterministic") },
    { C_STRING_WITH_LEN("enum('YES','NO')") },
    { NULL, 0 }
  },
  {
    { C_STRING_WITH_LEN("security_type") },
    { C_STRING_WITH_LEN("enum('INVOKER','DEFINER')") },
    { NULL, 0 }
  },
  {
    { C_STRING_WITH_LEN("param_list") },
    { C_STRING_WITH_LEN("blob") },
    { NULL, 0 }
  },

  {
    { C_STRING_WITH_LEN("returns") },
    { C_STRING_WITH_LEN("longblob") },
    { NULL, 0 }
  },
  {
    { C_STRING_WITH_LEN("body") },
    { C_STRING_WITH_LEN("longblob") },
    { NULL, 0 }
  },
  {
    { C_STRING_WITH_LEN("definer") },
    { C_STRING_WITH_LEN("char(77)") },
    { C_STRING_WITH_LEN("utf8") }
  },
  {
    { C_STRING_WITH_LEN("created") },
    { C_STRING_WITH_LEN("timestamp") },
    { NULL, 0 }
  },
  {
    { C_STRING_WITH_LEN("modified") },
    { C_STRING_WITH_LEN("timestamp") },
    { NULL, 0 }
  },
  {
    { C_STRING_WITH_LEN("sql_mode") },
    { C_STRING_WITH_LEN("set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES',"
    "'IGNORE_SPACE','NOT_USED','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION',"
    "'NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB',"
    "'NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40',"
    "'ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES',"
    "'STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES',"
    "'ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER',"
    "'HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH')") },
    { NULL, 0 }
  },
  {
    { C_STRING_WITH_LEN("comment") },
132
    { C_STRING_WITH_LEN("text") },
133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159
    { C_STRING_WITH_LEN("utf8") }
  },
  {
    { C_STRING_WITH_LEN("character_set_client") },
    { C_STRING_WITH_LEN("char(32)") },
    { C_STRING_WITH_LEN("utf8") }
  },
  {
    { C_STRING_WITH_LEN("collation_connection") },
    { C_STRING_WITH_LEN("char(32)") },
    { C_STRING_WITH_LEN("utf8") }
  },
  {
    { C_STRING_WITH_LEN("db_collation") },
    { C_STRING_WITH_LEN("char(32)") },
    { C_STRING_WITH_LEN("utf8") }
  },
  {
    { C_STRING_WITH_LEN("body_utf8") },
    { C_STRING_WITH_LEN("longblob") },
    { NULL, 0 }
  }
};

static const TABLE_FIELD_DEF
  proc_table_def= {MYSQL_PROC_FIELD_COUNT, proc_table_fields};

160 161 162 163 164 165 166 167 168 169 170 171
/*************************************************************************/

/**
  Stored_routine_creation_ctx -- creation context of stored routines
  (stored procedures and functions).
*/

class Stored_routine_creation_ctx : public Stored_program_creation_ctx,
                                    public Sql_alloc
{
public:
  static Stored_routine_creation_ctx *
kostja@bodhi.(none)'s avatar
kostja@bodhi.(none) committed
172
  load_from_db(THD *thd, const sp_name *name, TABLE *proc_tbl);
173 174 175 176 177 178 179 180 181 182 183 184

public:
  virtual Stored_program_creation_ctx *clone(MEM_ROOT *mem_root)
  {
    return new (mem_root) Stored_routine_creation_ctx(m_client_cs,
                                                      m_connection_cl,
                                                      m_db_cl);
  }

protected:
  virtual Object_creation_ctx *create_backup_ctx(THD *thd) const
  {
185 186
    DBUG_ENTER("Stored_routine_creation_ctx::create_backup_ctx");
    DBUG_RETURN(new Stored_routine_creation_ctx(thd));
187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335
  }

private:
  Stored_routine_creation_ctx(THD *thd)
    : Stored_program_creation_ctx(thd)
  { }

  Stored_routine_creation_ctx(CHARSET_INFO *client_cs,
                              CHARSET_INFO *connection_cl,
                              CHARSET_INFO *db_cl)
    : Stored_program_creation_ctx(client_cs, connection_cl, db_cl)
  { }
};

/**************************************************************************
  Stored_routine_creation_ctx implementation.
**************************************************************************/

bool load_charset(MEM_ROOT *mem_root,
                  Field *field,
                  CHARSET_INFO *dflt_cs,
                  CHARSET_INFO **cs)
{
  String cs_name;

  if (get_field(mem_root, field, &cs_name))
  {
    *cs= dflt_cs;
    return TRUE;
  }

  *cs= get_charset_by_csname(cs_name.c_ptr(), MY_CS_PRIMARY, MYF(0));

  if (*cs == NULL)
  {
    *cs= dflt_cs;
    return TRUE;
  }

  return FALSE;
}

/*************************************************************************/

bool load_collation(MEM_ROOT *mem_root,
                    Field *field,
                    CHARSET_INFO *dflt_cl,
                    CHARSET_INFO **cl)
{
  String cl_name;

  if (get_field(mem_root, field, &cl_name))
  {
    *cl= dflt_cl;
    return TRUE;
  }

  *cl= get_charset_by_name(cl_name.c_ptr(), MYF(0));

  if (*cl == NULL)
  {
    *cl= dflt_cl;
    return TRUE;
  }

  return FALSE;
}

/*************************************************************************/

Stored_routine_creation_ctx *
Stored_routine_creation_ctx::load_from_db(THD *thd,
                                         const sp_name *name,
                                         TABLE *proc_tbl)
{
  /* Load character set/collation attributes. */

  CHARSET_INFO *client_cs;
  CHARSET_INFO *connection_cl;
  CHARSET_INFO *db_cl;

  const char *db_name= thd->strmake(name->m_db.str, name->m_db.length);
  const char *sr_name= thd->strmake(name->m_name.str, name->m_name.length);

  bool invalid_creation_ctx= FALSE;

  if (load_charset(thd->mem_root,
                   proc_tbl->field[MYSQL_PROC_FIELD_CHARACTER_SET_CLIENT],
                   thd->variables.character_set_client,
                   &client_cs))
  {
    sql_print_warning("Stored routine '%s'.'%s': invalid value "
                      "in column mysql.proc.character_set_client.",
                      (const char *) db_name,
                      (const char *) sr_name);

    invalid_creation_ctx= TRUE;
  }

  if (load_collation(thd->mem_root,
                     proc_tbl->field[MYSQL_PROC_FIELD_COLLATION_CONNECTION],
                     thd->variables.collation_connection,
                     &connection_cl))
  {
    sql_print_warning("Stored routine '%s'.'%s': invalid value "
                      "in column mysql.proc.collation_connection.",
                      (const char *) db_name,
                      (const char *) sr_name);

    invalid_creation_ctx= TRUE;
  }

  if (load_collation(thd->mem_root,
                     proc_tbl->field[MYSQL_PROC_FIELD_DB_COLLATION],
                     NULL,
                     &db_cl))
  {
    sql_print_warning("Stored routine '%s'.'%s': invalid value "
                      "in column mysql.proc.db_collation.",
                      (const char *) db_name,
                      (const char *) sr_name);

    invalid_creation_ctx= TRUE;
  }

  if (invalid_creation_ctx)
  {
    push_warning_printf(thd,
                        MYSQL_ERROR::WARN_LEVEL_WARN,
                        ER_SR_INVALID_CREATION_CTX,
                        ER(ER_SR_INVALID_CREATION_CTX),
                        (const char *) db_name,
                        (const char *) sr_name);
  }

  /*
    If we failed to retrieve the database collation, load the default one
    from the disk.
  */

  if (!db_cl)
    db_cl= get_default_db_collation(thd, name->m_db.str);

  /* Create the context. */

  return new Stored_routine_creation_ctx(client_cs, connection_cl, db_cl);
}

/*************************************************************************/
336

337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380
class Proc_table_intact : public Table_check_intact
{
private:
  bool m_print_once;

public:
  Proc_table_intact() : m_print_once(TRUE) {}

protected:
  void report_error(uint code, const char *fmt, ...);
};


/**
  Report failure to validate the mysql.proc table definition.
  Print a message to the error log only once.
*/

void Proc_table_intact::report_error(uint code, const char *fmt, ...)
{
  va_list args;
  char buf[512];

  va_start(args, fmt);
  my_vsnprintf(buf, sizeof(buf), fmt, args);
  va_end(args);

  if (code)
    my_message(code, buf, MYF(0));
  else
    my_error(ER_CANNOT_LOAD_FROM_TABLE, MYF(0), "proc");

  if (m_print_once)
  {
    m_print_once= FALSE;
    sql_print_error("%s", buf);
  }
};


/** Single instance used to control printing to the error log. */
static Proc_table_intact proc_table_intact;


381
/**
382 383
  Open the mysql.proc table for read.

384 385 386 387
  @param thd     Thread context
  @param backup  Pointer to Open_tables_state instance where information about
                 currently open tables will be saved, and from which will be
                 restored when we will end work with mysql.proc.
388

389
  @retval
390
    0	Error
391 392
  @retval
    \#	Pointer to TABLE object of mysql.proc
393 394
*/

395
TABLE *open_proc_table_for_read(THD *thd, Open_tables_state *backup)
396
{
397
  DBUG_ENTER("open_proc_table_for_read");
398

399
  TABLE_LIST table;
400
  table.init_one_table("mysql", "proc", TL_READ);
401

402 403
  if (open_system_tables_for_read(thd, &table, backup))
    DBUG_RETURN(NULL);
404

405
  if (!proc_table_intact.check(table.table, &proc_table_def))
406
    DBUG_RETURN(table.table);
407 408 409 410

  close_system_tables(thd, backup);

  DBUG_RETURN(NULL);
411 412 413
}


414
/**
415 416
  Open the mysql.proc table for update.

417
  @param thd  Thread context
418

419
  @note
420 421
    Table opened with this call should closed using close_thread_tables().

422
  @retval
423
    0	Error
424 425
  @retval
    \#	Pointer to TABLE object of mysql.proc
426 427 428 429
*/

static TABLE *open_proc_table_for_update(THD *thd)
{
430
  DBUG_ENTER("open_proc_table_for_update");
431

432 433 434 435 436 437
  TABLE *table;
  TABLE_LIST table_list;
  table_list.init_one_table("mysql", "proc", TL_WRITE);

  if (!(table= open_system_table_for_update(thd, &table_list)))
    DBUG_RETURN(NULL);
438

439 440 441 442
  if (!proc_table_intact.check(table, &proc_table_def))
    DBUG_RETURN(table);

  close_thread_tables(thd);
443

444
  DBUG_RETURN(NULL);
445 446 447
}


448
/**
449 450
  Find row in open mysql.proc table representing stored routine.

451 452 453 454
  @param thd    Thread context
  @param type   Type of routine to find (function or procedure)
  @param name   Name of routine
  @param table  TABLE object for open mysql.proc table.
455

456 457 458 459
  @retval
    SP_OK             Routine found
  @retval
    SP_KEY_NOT_FOUND  No routine with given name
460 461 462 463 464
*/

static int
db_find_routine_aux(THD *thd, int type, sp_name *name, TABLE *table)
{
465
  uchar key[MAX_KEY_LENGTH];	// db, name, optional key length type
466
  DBUG_ENTER("db_find_routine_aux");
467 468
  DBUG_PRINT("enter", ("type: %d  name: %.*s",
		       type, (int) name->m_name.length, name->m_name.str));
469

470 471 472 473 474 475 476
  /*
    Create key to find row. We have to use field->store() to be able to
    handle VARCHAR and CHAR fields.
    Assumption here is that the three first fields in the table are
    'db', 'name' and 'type' and the first key is the primary key over the
    same fields.
  */
477 478
  if (name->m_name.length > table->field[1]->field_length)
    DBUG_RETURN(SP_KEY_NOT_FOUND);
479 480 481
  table->field[0]->store(name->m_db.str, name->m_db.length, &my_charset_bin);
  table->field[1]->store(name->m_name.str, name->m_name.length,
                         &my_charset_bin);
482
  table->field[2]->store((longlong) type, TRUE);
483 484 485
  key_copy(key, table->record[0], table->key_info,
           table->key_info->key_length);

486 487
  if (table->file->index_read_idx_map(table->record[0], 0, key, HA_WHOLE_KEY,
                                      HA_READ_KEY_EXACT))
488
    DBUG_RETURN(SP_KEY_NOT_FOUND);
489

490 491 492
  DBUG_RETURN(SP_OK);
}

monty@mysql.com's avatar
monty@mysql.com committed
493

494
/**
495 496 497
  Find routine definition in mysql.proc table and create corresponding
  sp_head object for it.

498 499 500 501 502
  @param thd   Thread context
  @param type  Type of routine (TYPE_ENUM_PROCEDURE/...)
  @param name  Name of routine
  @param sphp  Out parameter in which pointer to created sp_head
               object is returned (0 in case of error).
503

504
  @note
505 506 507
    This function may damage current LEX during execution, so it is good
    idea to create temporary LEX and make it active before calling it.

508 509 510 511
  @retval
    0       Success
  @retval
    non-0   Error (may be one of special codes like SP_KEY_NOT_FOUND)
512 513
*/

514
static int
515
db_find_routine(THD *thd, int type, sp_name *name, sp_head **sphp)
516 517
{
  TABLE *table;
518
  const char *params, *returns, *body;
519
  int ret;
520
  const char *definer;
521 522
  longlong created;
  longlong modified;
523
  st_sp_chistics chistics;
524 525 526
  char *ptr;
  uint length;
  char buff[65];
527
  String str(buff, sizeof(buff), &my_charset_bin);
528
  bool saved_time_zone_used= thd->time_zone_used;
529
  ulong sql_mode, saved_mode= thd->variables.sql_mode;
530
  Open_tables_state open_tables_state_backup;
531 532
  Stored_program_creation_ctx *creation_ctx;

monty@mysql.com's avatar
monty@mysql.com committed
533
  DBUG_ENTER("db_find_routine");
534
  DBUG_PRINT("enter", ("type: %d name: %.*s",
535
		       type, (int) name->m_name.length, name->m_name.str));
536

537
  *sphp= 0;                                     // In case of errors
538
  if (!(table= open_proc_table_for_read(thd, &open_tables_state_backup)))
539 540
    DBUG_RETURN(SP_OPEN_TABLE_FAILED);

541 542 543
  /* Reset sql_mode during data dictionary operations. */
  thd->variables.sql_mode= 0;

544
  if ((ret= db_find_routine_aux(thd, type, name, table)) != SP_OK)
545
    goto done;
546

547
  if (table->s->fields < MYSQL_PROC_FIELD_COUNT)
548 549 550 551 552
  {
    ret= SP_GET_FIELD_FAILED;
    goto done;
  }

553
  bzero((char *)&chistics, sizeof(chistics));
monty@mysql.com's avatar
monty@mysql.com committed
554
  if ((ptr= get_field(thd->mem_root,
555 556 557 558 559 560 561 562 563 564 565 566 567 568 569 570 571 572 573
		      table->field[MYSQL_PROC_FIELD_ACCESS])) == NULL)
  {
    ret= SP_GET_FIELD_FAILED;
    goto done;
  }
  switch (ptr[0]) {
  case 'N':
    chistics.daccess= SP_NO_SQL;
    break;
  case 'C':
    chistics.daccess= SP_CONTAINS_SQL;
    break;
  case 'R':
    chistics.daccess= SP_READS_SQL_DATA;
    break;
  case 'M':
    chistics.daccess= SP_MODIFIES_SQL_DATA;
    break;
  default:
574
    chistics.daccess= SP_DEFAULT_ACCESS_MAPPING;
575 576
  }

monty@mysql.com's avatar
monty@mysql.com committed
577
  if ((ptr= get_field(thd->mem_root,
578
		      table->field[MYSQL_PROC_FIELD_DETERMINISTIC])) == NULL)
579 580 581 582
  {
    ret= SP_GET_FIELD_FAILED;
    goto done;
  }
583
  chistics.detistic= (ptr[0] == 'N' ? FALSE : TRUE);    
584

monty@mysql.com's avatar
monty@mysql.com committed
585
  if ((ptr= get_field(thd->mem_root,
586
		      table->field[MYSQL_PROC_FIELD_SECURITY_TYPE])) == NULL)
587 588 589 590
  {
    ret= SP_GET_FIELD_FAILED;
    goto done;
  }
591
  chistics.suid= (ptr[0] == 'I' ? SP_IS_NOT_SUID : SP_IS_SUID);
592

monty@mysql.com's avatar
monty@mysql.com committed
593
  if ((params= get_field(thd->mem_root,
594 595 596 597
			 table->field[MYSQL_PROC_FIELD_PARAM_LIST])) == NULL)
  {
    params= "";
  }
598

599 600
  if (type == TYPE_ENUM_PROCEDURE)
    returns= "";
monty@mysql.com's avatar
monty@mysql.com committed
601
  else if ((returns= get_field(thd->mem_root,
602 603 604 605 606 607
			       table->field[MYSQL_PROC_FIELD_RETURNS])) == NULL)
  {
    ret= SP_GET_FIELD_FAILED;
    goto done;
  }

monty@mysql.com's avatar
monty@mysql.com committed
608
  if ((body= get_field(thd->mem_root,
609
		       table->field[MYSQL_PROC_FIELD_BODY])) == NULL)
610 611 612 613
  {
    ret= SP_GET_FIELD_FAILED;
    goto done;
  }
614 615

  // Get additional information
monty@mysql.com's avatar
monty@mysql.com committed
616
  if ((definer= get_field(thd->mem_root,
617 618 619 620 621 622 623 624
			  table->field[MYSQL_PROC_FIELD_DEFINER])) == NULL)
  {
    ret= SP_GET_FIELD_FAILED;
    goto done;
  }

  modified= table->field[MYSQL_PROC_FIELD_MODIFIED]->val_int();
  created= table->field[MYSQL_PROC_FIELD_CREATED]->val_int();
625

monty@mysql.com's avatar
monty@mysql.com committed
626
  sql_mode= (ulong) table->field[MYSQL_PROC_FIELD_SQL_MODE]->val_int();
627

628 629
  table->field[MYSQL_PROC_FIELD_COMMENT]->val_str(&str, &str);

630
  ptr= 0;
631
  if ((length= str.length()))
632
    ptr= thd->strmake(str.ptr(), length);
633 634
  chistics.comment.str= ptr;
  chistics.comment.length= length;
635

636 637
  creation_ctx= Stored_routine_creation_ctx::load_from_db(thd, name, table);

638
  close_system_tables(thd, &open_tables_state_backup);
639
  table= 0;
640

641 642
  ret= db_load_routine(thd, type, name, sphp,
                       sql_mode, params, returns, body, chistics,
643
                       definer, created, modified, creation_ctx);
644
 done:
645 646 647 648 649
  /* 
    Restore the time zone flag as the timezone usage in proc table
    does not affect replication.
  */  
  thd->time_zone_used= saved_time_zone_used;
650
  if (table)
651
    close_system_tables(thd, &open_tables_state_backup);
652
  thd->variables.sql_mode= saved_mode;
653 654
  DBUG_RETURN(ret);
}
monty@mysql.com's avatar
monty@mysql.com committed
655

656

657 658 659 660 661 662 663
/**
  Silence DEPRECATED SYNTAX warnings when loading a stored procedure
  into the cache.
*/
struct Silence_deprecated_warning : public Internal_error_handler
{
public:
Marc Alff's avatar
Marc Alff committed
664 665 666 667 668 669
  virtual bool handle_condition(THD *thd,
                                uint sql_errno,
                                const char* sqlstate,
                                MYSQL_ERROR::enum_warning_level level,
                                const char* msg,
                                MYSQL_ERROR ** cond_hdl);
670 671 672
};

bool
Marc Alff's avatar
Marc Alff committed
673 674 675 676 677 678 679
Silence_deprecated_warning::handle_condition(
  THD *,
  uint sql_errno,
  const char*,
  MYSQL_ERROR::enum_warning_level level,
  const char*,
  MYSQL_ERROR ** cond_hdl)
680
{
Marc Alff's avatar
Marc Alff committed
681
  *cond_hdl= NULL;
682 683 684 685 686 687 688 689
  if (sql_errno == ER_WARN_DEPRECATED_SYNTAX &&
      level == MYSQL_ERROR::WARN_LEVEL_WARN)
    return TRUE;

  return FALSE;
}


Sergey Glukhov's avatar
Sergey Glukhov committed
690 691 692 693 694 695 696 697 698 699 700 701 702 703 704 705 706 707 708 709 710 711 712 713 714 715 716 717 718 719 720 721 722 723 724 725 726 727 728 729 730 731 732 733 734 735 736 737 738
/**
  @brief    The function parses input strings and returns SP stucture.

  @param[in]      thd               Thread handler
  @param[in]      defstr            CREATE... string
  @param[in]      sql_mode          SQL mode
  @param[in]      creation_ctx      Creation context of stored routines
                                    
  @return     Pointer on sp_head struct
    @retval   #                     Pointer on sp_head struct
    @retval   0                     error
*/

static sp_head *sp_compile(THD *thd, String *defstr, ulong sql_mode,
                           Stored_program_creation_ctx *creation_ctx)
{
  sp_head *sp;
  ulong old_sql_mode= thd->variables.sql_mode;
  ha_rows old_select_limit= thd->variables.select_limit;
  sp_rcontext *old_spcont= thd->spcont;
  Silence_deprecated_warning warning_handler;

  thd->variables.sql_mode= sql_mode;
  thd->variables.select_limit= HA_POS_ERROR;

  Parser_state parser_state(thd, defstr->c_ptr(), defstr->length());
  lex_start(thd);
  thd->push_internal_handler(&warning_handler);
  thd->spcont= 0;

  if (parse_sql(thd, & parser_state, creation_ctx) || thd->lex == NULL)
  {
    sp= thd->lex->sphead;
    delete sp;
    sp= 0;
  }
  else
  {
    sp= thd->lex->sphead;
  }

  thd->pop_internal_handler();
  thd->spcont= old_spcont;
  thd->variables.sql_mode= old_sql_mode;
  thd->variables.select_limit= old_select_limit;
  return sp;
}


739 740 741 742
static int
db_load_routine(THD *thd, int type, sp_name *name, sp_head **sphp,
                ulong sql_mode, const char *params, const char *returns,
                const char *body, st_sp_chistics &chistics,
743 744
                const char *definer, longlong created, longlong modified,
                Stored_program_creation_ctx *creation_ctx)
745
{
746
  LEX *old_lex= thd->lex, newlex;
747
  String defstr;
748 749 750 751
  char saved_cur_db_name_buf[NAME_LEN+1];
  LEX_STRING saved_cur_db_name=
    { saved_cur_db_name_buf, sizeof(saved_cur_db_name_buf) };
  bool cur_db_changed;
Sergey Glukhov's avatar
Sergey Glukhov committed
752
  
gluh@mysql.com/gluh.(none)'s avatar
gluh@mysql.com/gluh.(none) committed
753
  char definer_user_name_holder[USERNAME_LENGTH + 1];
754
  LEX_STRING definer_user_name= { definer_user_name_holder,
gluh@mysql.com/gluh.(none)'s avatar
gluh@mysql.com/gluh.(none) committed
755
                                  USERNAME_LENGTH };
756 757

  char definer_host_name_holder[HOSTNAME_LENGTH + 1];
758 759
  LEX_STRING definer_host_name= { definer_host_name_holder, HOSTNAME_LENGTH };

Sergey Glukhov's avatar
Sergey Glukhov committed
760
  int ret= 0;
761 762 763 764

  thd->lex= &newlex;
  newlex.current_select= NULL;

765 766 767 768
  parse_user(definer, strlen(definer),
             definer_user_name.str, &definer_user_name.length,
             definer_host_name.str, &definer_host_name.length);

769
  defstr.set_charset(creation_ctx->get_client_cs());
770 771 772 773 774 775 776

  /*
    We have to add DEFINER clause and provide proper routine characterstics in
    routine definition statement that we build here to be able to use this
    definition for SHOW CREATE PROCEDURE later.
   */

777
  if (!create_string(thd, &defstr,
778 779 780 781 782 783
                     type,
                     NULL, 0,
                     name->m_name.str, name->m_name.length,
                     params, strlen(params),
                     returns, strlen(returns),
                     body, strlen(body),
Sergey Glukhov's avatar
Sergey Glukhov committed
784 785
                     &chistics, &definer_user_name, &definer_host_name,
                     sql_mode))
786 787
  {
    ret= SP_INTERNAL_ERROR;
788
    goto end;
789
  }
790

791
  /*
792
    Change the current database (if needed).
793

794
    TODO: why do we force switch here?
795 796
  */

797 798 799
  if (mysql_opt_change_db(thd, &name->m_db, &saved_cur_db_name, TRUE,
                          &cur_db_changed))
  {
800
    ret= SP_INTERNAL_ERROR;
801
    goto end;
802
  }
803

804
  {
Sergey Glukhov's avatar
Sergey Glukhov committed
805
    *sphp= sp_compile(thd, &defstr, sql_mode, creation_ctx);
806 807 808 809 810 811 812 813
    /*
      Force switching back to the saved current database (if changed),
      because it may be NULL. In this case, mysql_change_db() would
      generate an error.
    */

    if (cur_db_changed && mysql_change_db(thd, &saved_cur_db_name, TRUE))
    {
814
      ret= SP_INTERNAL_ERROR;
815
      goto end;
816
    }
817

Sergey Glukhov's avatar
Sergey Glukhov committed
818
    if (!*sphp)
819
    {
820 821
      ret= SP_PARSE_ERROR;
      goto end;
822
    }
823 824 825 826 827 828 829 830 831 832 833 834 835 836

    (*sphp)->set_definer(&definer_user_name, &definer_host_name);
    (*sphp)->set_info(created, modified, &chistics, sql_mode);
    (*sphp)->set_creation_ctx(creation_ctx);
    (*sphp)->optimize();
    /*
      Not strictly necessary to invoke this method here, since we know
      that we've parsed CREATE PROCEDURE/FUNCTION and not an
      UPDATE/DELETE/INSERT/REPLACE/LOAD/CREATE TABLE, but we try to
      maintain the invariant that this method is called for each
      distinct statement, in case its logic is extended with other
      types of analyses in future.
    */
    newlex.set_trg_event_type_for_tables();
837
  }
838

bell@sanja.is.com.ua's avatar
bell@sanja.is.com.ua committed
839
end:
840
  lex_end(thd->lex);
841
  thd->lex= old_lex;
842
  return ret;
843 844
}

monty@mysql.com's avatar
monty@mysql.com committed
845

846 847 848
static void
sp_returns_type(THD *thd, String &result, sp_head *sp)
{
849
  TABLE table;
850
  TABLE_SHARE share;
851
  Field *field;
852 853
  bzero((char*) &table, sizeof(table));
  bzero((char*) &share, sizeof(share));
854
  table.in_use= thd;
855
  table.s = &share;
856
  field= sp->create_result_field(0, 0, &table);
857
  field->sql_type(result);
858 859 860 861 862

  if (field->has_charset())
  {
    result.append(STRING_WITH_LEN(" CHARSET "));
    result.append(field->charset()->csname);
Alexander Barkov's avatar
#  
Alexander Barkov committed
863 864 865 866 867
    if (!(field->charset()->state & MY_CS_PRIMARY))
    {
      result.append(STRING_WITH_LEN(" COLLATE "));
      result.append(field->charset()->name);
    }
868 869
  }

870 871 872
  delete field;
}

anozdrin/alik@ibm's avatar
anozdrin/alik@ibm committed
873 874 875 876 877 878 879 880 881 882 883 884

/**
  Write stored-routine object into mysql.proc.

  This operation stores attributes of the stored procedure/function into
  the mysql.proc.

  @param thd  Thread context.
  @param type Stored routine type
              (TYPE_ENUM_PROCEDURE or TYPE_ENUM_FUNCTION).
  @param sp   Stored routine object to store.

885 886 887 888 889 890 891 892 893 894
  @note Opens and closes the thread tables. Therefore assumes
  that there are no locked tables in this thread at the time of
  invocation.
  Unlike some other DDL statements, *does* close the tables
  in the end, since the call to this function is normally
  followed by an implicit grant (sp_grant_privileges())
  and this subsequent call opens and closes mysql.procs_priv.

  @return Error code. SP_OK is returned on success. Other
  SP_ constants are used to indicate about errors.
anozdrin/alik@ibm's avatar
anozdrin/alik@ibm committed
895 896 897 898
*/

int
sp_create_routine(THD *thd, int type, sp_head *sp)
899
{
900
  int ret;
901
  TABLE *table;
902
  char definer[USER_HOST_BUFF_SIZE];
903
  ulong saved_mode= thd->variables.sql_mode;
anozdrin/alik@ibm's avatar
anozdrin/alik@ibm committed
904

905 906
  CHARSET_INFO *db_cs= get_default_db_collation(thd, sp->m_db.str);

907 908 909 910
  enum_check_fields saved_count_cuted_fields;

  bool store_failed= FALSE;

anozdrin/alik@ibm's avatar
anozdrin/alik@ibm committed
911
  DBUG_ENTER("sp_create_routine");
912
  DBUG_PRINT("enter", ("type: %d  name: %.*s",type, (int) sp->m_name.length,
913
                       sp->m_name.str));
914
  String retstr(64);
915
  retstr.set_charset(system_charset_info);
916

anozdrin/alik@ibm's avatar
anozdrin/alik@ibm committed
917 918 919
  DBUG_ASSERT(type == TYPE_ENUM_PROCEDURE ||
              type == TYPE_ENUM_FUNCTION);

920 921 922
  /* Reset sql_mode during data dictionary operations. */
  thd->variables.sql_mode= 0;

923 924 925 926 927
  /*
    This statement will be replicated as a statement, even when using
    row-based replication.  The flag will be reset at the end of the
    statement.
  */
928
  thd->clear_current_stmt_binlog_format_row();
929

930 931 932
  saved_count_cuted_fields= thd->count_cuted_fields;
  thd->count_cuted_fields= CHECK_FIELD_WARN;

933
  if (!(table= open_proc_table_for_update(thd)))
934 935
    ret= SP_OPEN_TABLE_FAILED;
  else
936
  {
937
    restore_record(table, s->default_values); // Get default values for fields
938 939

    /* NOTE: all needed privilege checks have been already done. */
940
    strxnmov(definer, sizeof(definer)-1, thd->lex->definer->user.str, "@",
941
            thd->lex->definer->host.str, NullS);
942

943
    if (table->s->fields < MYSQL_PROC_FIELD_COUNT)
944 945 946 947
    {
      ret= SP_GET_FIELD_FAILED;
      goto done;
    }
948

949 950 951 952
    if (system_charset_info->cset->numchars(system_charset_info,
                                            sp->m_name.str,
                                            sp->m_name.str+sp->m_name.length) >
        table->field[MYSQL_PROC_FIELD_NAME]->char_length())
953 954 955 956
    {
      ret= SP_BAD_IDENTIFIER;
      goto done;
    }
957 958 959 960 961
    if (sp->m_body.length > table->field[MYSQL_PROC_FIELD_BODY]->field_length)
    {
      ret= SP_BODY_TOO_LONG;
      goto done;
    }
962 963 964 965 966 967 968 969 970 971 972 973 974 975 976 977 978

    store_failed=
      table->field[MYSQL_PROC_FIELD_DB]->
        store(sp->m_db.str, sp->m_db.length, system_charset_info);

    store_failed= store_failed ||
      table->field[MYSQL_PROC_FIELD_NAME]->
        store(sp->m_name.str, sp->m_name.length, system_charset_info);

    store_failed= store_failed ||
      table->field[MYSQL_PROC_MYSQL_TYPE]->
        store((longlong)type, TRUE);

    store_failed= store_failed ||
      table->field[MYSQL_PROC_FIELD_SPECIFIC_NAME]->
        store(sp->m_name.str, sp->m_name.length, system_charset_info);

979
    if (sp->m_chistics->daccess != SP_DEFAULT_ACCESS)
980 981 982 983 984 985 986 987 988 989
    {
      store_failed= store_failed ||
        table->field[MYSQL_PROC_FIELD_ACCESS]->
          store((longlong)sp->m_chistics->daccess, TRUE);
    }

    store_failed= store_failed ||
      table->field[MYSQL_PROC_FIELD_DETERMINISTIC]->
        store((longlong)(sp->m_chistics->detistic ? 1 : 2), TRUE);

990
    if (sp->m_chistics->suid != SP_IS_DEFAULT_SUID)
991 992 993 994 995 996 997 998 999 1000
    {
      store_failed= store_failed ||
        table->field[MYSQL_PROC_FIELD_SECURITY_TYPE]->
          store((longlong)sp->m_chistics->suid, TRUE);
    }

    store_failed= store_failed ||
      table->field[MYSQL_PROC_FIELD_PARAM_LIST]->
        store(sp->m_params.str, sp->m_params.length, system_charset_info);

1001 1002 1003
    if (sp->m_type == TYPE_ENUM_FUNCTION)
    {
      sp_returns_type(thd, retstr, sp);
1004 1005 1006 1007

      store_failed= store_failed ||
        table->field[MYSQL_PROC_FIELD_RETURNS]->
          store(retstr.ptr(), retstr.length(), system_charset_info);
1008
    }
1009 1010 1011 1012 1013 1014 1015 1016 1017

    store_failed= store_failed ||
      table->field[MYSQL_PROC_FIELD_BODY]->
        store(sp->m_body.str, sp->m_body.length, system_charset_info);

    store_failed= store_failed ||
      table->field[MYSQL_PROC_FIELD_DEFINER]->
        store(definer, (uint)strlen(definer), system_charset_info);

1018
    ((Field_timestamp *)table->field[MYSQL_PROC_FIELD_CREATED])->set_time();
1019
    ((Field_timestamp *)table->field[MYSQL_PROC_FIELD_MODIFIED])->set_time();
1020 1021 1022

    store_failed= store_failed ||
      table->field[MYSQL_PROC_FIELD_SQL_MODE]->
1023
        store((longlong)saved_mode, TRUE);
1024

1025
    if (sp->m_chistics->comment.str)
1026 1027 1028 1029 1030 1031
    {
      store_failed= store_failed ||
        table->field[MYSQL_PROC_FIELD_COMMENT]->
          store(sp->m_chistics->comment.str, sp->m_chistics->comment.length,
                system_charset_info);
    }
1032

1033 1034
    if ((sp->m_type == TYPE_ENUM_FUNCTION) &&
        !trust_function_creators && mysql_bin_log.is_open())
1035 1036 1037 1038
    {
      if (!sp->m_chistics->detistic)
      {
	/*
1039
	  Note that this test is not perfect; one could use
1040 1041 1042 1043 1044 1045 1046 1047 1048 1049 1050 1051 1052 1053
	  a non-deterministic read-only function in an update statement.
	*/
	enum enum_sp_data_access access=
	  (sp->m_chistics->daccess == SP_DEFAULT_ACCESS) ?
	  SP_DEFAULT_ACCESS_MAPPING : sp->m_chistics->daccess;
	if (access == SP_CONTAINS_SQL ||
	    access == SP_MODIFIES_SQL_DATA)
	{
	  my_message(ER_BINLOG_UNSAFE_ROUTINE,
		     ER(ER_BINLOG_UNSAFE_ROUTINE), MYF(0));
	  ret= SP_INTERNAL_ERROR;
	  goto done;
	}
      }
1054
      if (!(thd->security_ctx->master_access & SUPER_ACL))
1055 1056 1057 1058 1059 1060 1061 1062
      {
	my_message(ER_BINLOG_CREATE_ROUTINE_NEED_SUPER,
		   ER(ER_BINLOG_CREATE_ROUTINE_NEED_SUPER), MYF(0));
	ret= SP_INTERNAL_ERROR;
	goto done;
      }
    }

1063
    table->field[MYSQL_PROC_FIELD_CHARACTER_SET_CLIENT]->set_notnull();
1064 1065 1066 1067 1068
    store_failed= store_failed ||
      table->field[MYSQL_PROC_FIELD_CHARACTER_SET_CLIENT]->store(
        thd->charset()->csname,
        strlen(thd->charset()->csname),
        system_charset_info);
1069 1070

    table->field[MYSQL_PROC_FIELD_COLLATION_CONNECTION]->set_notnull();
1071 1072 1073 1074 1075
    store_failed= store_failed ||
      table->field[MYSQL_PROC_FIELD_COLLATION_CONNECTION]->store(
        thd->variables.collation_connection->name,
        strlen(thd->variables.collation_connection->name),
        system_charset_info);
1076 1077

    table->field[MYSQL_PROC_FIELD_DB_COLLATION]->set_notnull();
1078 1079 1080
    store_failed= store_failed ||
      table->field[MYSQL_PROC_FIELD_DB_COLLATION]->store(
        db_cs->name, strlen(db_cs->name), system_charset_info);
1081 1082

    table->field[MYSQL_PROC_FIELD_BODY_UTF8]->set_notnull();
1083 1084 1085 1086 1087 1088 1089 1090 1091
    store_failed= store_failed ||
      table->field[MYSQL_PROC_FIELD_BODY_UTF8]->store(
        sp->m_body_utf8.str, sp->m_body_utf8.length, system_charset_info);

    if (store_failed)
    {
      ret= SP_FLD_STORE_FAILED;
      goto done;
    }
1092

monty@mysql.com's avatar
monty@mysql.com committed
1093
    ret= SP_OK;
1094
    if (table->file->ha_write_row(table->record[0]))
1095
      ret= SP_WRITE_ROW_FAILED;
1096 1097 1098
    else if (mysql_bin_log.is_open())
    {
      thd->clear_error();
1099 1100 1101

      String log_query;
      log_query.set_charset(system_charset_info);
1102

1103 1104
      if (!create_string(thd, &log_query,
                         sp->m_type,
1105 1106
                         (sp->m_explicit_name ? sp->m_db.str : NULL), 
                         (sp->m_explicit_name ? sp->m_db.length : 0), 
1107 1108 1109 1110 1111
                         sp->m_name.str, sp->m_name.length,
                         sp->m_params.str, sp->m_params.length,
                         retstr.c_ptr(), retstr.length(),
                         sp->m_body.str, sp->m_body.length,
                         sp->m_chistics, &(thd->lex->definer->user),
Sergey Glukhov's avatar
Sergey Glukhov committed
1112 1113
                         &(thd->lex->definer->host),
                         saved_mode))
1114 1115 1116 1117
      {
        ret= SP_INTERNAL_ERROR;
        goto done;
      }
1118 1119
      /* restore sql_mode when binloging */
      thd->variables.sql_mode= saved_mode;
1120
      /* Such a statement can always go directly to binlog, no trans cache */
1121
      if (thd->binlog_query(THD::STMT_QUERY_TYPE,
1122
                            log_query.c_ptr(), log_query.length(),
1123
                            FALSE, FALSE, FALSE, 0))
1124
        ret= SP_INTERNAL_ERROR;
1125
      thd->variables.sql_mode= 0;
1126 1127
    }

1128 1129
  }

1130
done:
1131
  thd->count_cuted_fields= saved_count_cuted_fields;
1132
  thd->variables.sql_mode= saved_mode;
1133

1134
  close_thread_tables(thd);
1135 1136
  DBUG_RETURN(ret);
}
1137

monty@mysql.com's avatar
monty@mysql.com committed
1138

anozdrin/alik@ibm's avatar
anozdrin/alik@ibm committed
1139 1140 1141 1142 1143 1144 1145 1146 1147 1148 1149 1150 1151 1152 1153 1154 1155
/**
  Delete the record for the stored routine object from mysql.proc.

  The operation deletes the record for the stored routine specified by name
  from the mysql.proc table and invalidates the stored-routine cache.

  @param thd  Thread context.
  @param type Stored routine type
              (TYPE_ENUM_PROCEDURE or TYPE_ENUM_FUNCTION)
  @param name Stored routine name.

  @return Error code. SP_OK is returned on success. Other SP_ constants are
  used to indicate about errors.
*/

int
sp_drop_routine(THD *thd, int type, sp_name *name)
1156 1157 1158
{
  TABLE *table;
  int ret;
anozdrin/alik@ibm's avatar
anozdrin/alik@ibm committed
1159
  DBUG_ENTER("sp_drop_routine");
1160 1161
  DBUG_PRINT("enter", ("type: %d  name: %.*s",
		       type, (int) name->m_name.length, name->m_name.str));
1162

anozdrin/alik@ibm's avatar
anozdrin/alik@ibm committed
1163 1164 1165
  DBUG_ASSERT(type == TYPE_ENUM_PROCEDURE ||
              type == TYPE_ENUM_FUNCTION);

1166 1167 1168 1169 1170
  /*
    This statement will be replicated as a statement, even when using
    row-based replication.  The flag will be reset at the end of the
    statement.
  */
1171
  thd->clear_current_stmt_binlog_format_row();
1172

1173 1174 1175
  if (!(table= open_proc_table_for_update(thd)))
    DBUG_RETURN(SP_OPEN_TABLE_FAILED);
  if ((ret= db_find_routine_aux(thd, type, name, table)) == SP_OK)
1176
  {
1177
    if (table->file->ha_delete_row(table->record[0]))
1178 1179
      ret= SP_DELETE_ROW_FAILED;
  }
1180 1181 1182

  if (ret == SP_OK)
  {
1183 1184
    if (write_bin_log(thd, TRUE, thd->query(), thd->query_length()))
      ret= SP_INTERNAL_ERROR;
anozdrin/alik@ibm's avatar
anozdrin/alik@ibm committed
1185
    sp_cache_invalidate();
1186 1187
  }

1188
  close_thread_tables(thd);
1189 1190 1191
  DBUG_RETURN(ret);
}

monty@mysql.com's avatar
monty@mysql.com committed
1192

anozdrin/alik@ibm's avatar
anozdrin/alik@ibm committed
1193 1194 1195 1196 1197 1198 1199 1200 1201 1202 1203 1204 1205 1206 1207 1208 1209 1210 1211
/**
  Find and updated the record for the stored routine object in mysql.proc.

  The operation finds the record for the stored routine specified by name
  in the mysql.proc table and updates it with new attributes. After
  successful update, the cache is invalidated.

  @param thd      Thread context.
  @param type     Stored routine type
                  (TYPE_ENUM_PROCEDURE or TYPE_ENUM_FUNCTION)
  @param name     Stored routine name.
  @param chistics New values of stored routine attributes to write.

  @return Error code. SP_OK is returned on success. Other SP_ constants are
  used to indicate about errors.
*/

int
sp_update_routine(THD *thd, int type, sp_name *name, st_sp_chistics *chistics)
1212 1213 1214
{
  TABLE *table;
  int ret;
anozdrin/alik@ibm's avatar
anozdrin/alik@ibm committed
1215
  DBUG_ENTER("sp_update_routine");
1216 1217
  DBUG_PRINT("enter", ("type: %d  name: %.*s",
		       type, (int) name->m_name.length, name->m_name.str));
anozdrin/alik@ibm's avatar
anozdrin/alik@ibm committed
1218 1219 1220

  DBUG_ASSERT(type == TYPE_ENUM_PROCEDURE ||
              type == TYPE_ENUM_FUNCTION);
1221 1222
  /*
    This statement will be replicated as a statement, even when using
1223
    row-based replication. The flag will be reset at the end of the
1224 1225
    statement.
  */
1226
  thd->clear_current_stmt_binlog_format_row();
1227

1228 1229 1230
  if (!(table= open_proc_table_for_update(thd)))
    DBUG_RETURN(SP_OPEN_TABLE_FAILED);
  if ((ret= db_find_routine_aux(thd, type, name, table)) == SP_OK)
1231 1232
  {
    store_record(table,record[1]);
monty@mysql.com's avatar
monty@mysql.com committed
1233
    table->timestamp_field_type= TIMESTAMP_NO_AUTO_SET;
1234
    ((Field_timestamp *)table->field[MYSQL_PROC_FIELD_MODIFIED])->set_time();
1235 1236
    if (chistics->suid != SP_IS_DEFAULT_SUID)
      table->field[MYSQL_PROC_FIELD_SECURITY_TYPE]->
1237
	store((longlong)chistics->suid, TRUE);
1238 1239
    if (chistics->daccess != SP_DEFAULT_ACCESS)
      table->field[MYSQL_PROC_FIELD_ACCESS]->
1240
	store((longlong)chistics->daccess, TRUE);
1241 1242 1243
    if (chistics->comment.str)
      table->field[MYSQL_PROC_FIELD_COMMENT]->store(chistics->comment.str,
						    chistics->comment.length,
1244
						    system_charset_info);
1245 1246
    if ((ret= table->file->ha_update_row(table->record[1],table->record[0])) &&
        ret != HA_ERR_RECORD_IS_THE_SAME)
1247
      ret= SP_WRITE_ROW_FAILED;
1248 1249
    else
      ret= 0;
1250
  }
1251 1252 1253

  if (ret == SP_OK)
  {
1254 1255
    if (write_bin_log(thd, TRUE, thd->query(), thd->query_length()))
      ret= SP_INTERNAL_ERROR;
anozdrin/alik@ibm's avatar
anozdrin/alik@ibm committed
1256
    sp_cache_invalidate();
1257 1258
  }

1259
  close_thread_tables(thd);
1260 1261 1262
  DBUG_RETURN(ret);
}

monty@mysql.com's avatar
monty@mysql.com committed
1263

1264 1265 1266 1267 1268 1269 1270
/**
  Drop all routines in database 'db'

  @note Close the thread tables, the calling code might want to
  delete from other system tables afterwards.
*/

1271 1272 1273 1274 1275
int
sp_drop_db_routines(THD *thd, char *db)
{
  TABLE *table;
  int ret;
1276
  uint key_len;
1277 1278 1279
  DBUG_ENTER("sp_drop_db_routines");
  DBUG_PRINT("enter", ("db: %s", db));

1280 1281 1282
  ret= SP_OPEN_TABLE_FAILED;
  if (!(table= open_proc_table_for_update(thd)))
    goto err;
1283

1284 1285 1286
  table->field[MYSQL_PROC_FIELD_DB]->store(db, strlen(db), system_charset_info);
  key_len= table->key_info->key_part[0].store_length;

1287
  ret= SP_OK;
1288
  table->file->ha_index_init(0, 1);
1289 1290 1291
  if (! table->file->index_read_map(table->record[0],
                                    (uchar *)table->field[MYSQL_PROC_FIELD_DB]->ptr,
                                    (key_part_map)1, HA_READ_KEY_EXACT))
1292 1293 1294 1295
  {
    int nxtres;
    bool deleted= FALSE;

1296 1297
    do
    {
1298
      if (! table->file->ha_delete_row(table->record[0]))
1299 1300 1301 1302
	deleted= TRUE;		/* We deleted something */
      else
      {
	ret= SP_DELETE_ROW_FAILED;
1303
	nxtres= 0;
1304 1305 1306
	break;
      }
    } while (! (nxtres= table->file->index_next_same(table->record[0],
1307
                                (uchar *)table->field[MYSQL_PROC_FIELD_DB]->ptr,
1308
						     key_len)));
1309 1310 1311 1312 1313
    if (nxtres != HA_ERR_END_OF_FILE)
      ret= SP_KEY_NOT_FOUND;
    if (deleted)
      sp_cache_invalidate();
  }
monty@mysql.com's avatar
monty@mysql.com committed
1314
  table->file->ha_index_end();
1315 1316 1317

  close_thread_tables(thd);

1318
err:
1319 1320 1321 1322
  DBUG_RETURN(ret);
}


anozdrin/alik@ibm's avatar
anozdrin/alik@ibm committed
1323 1324 1325 1326 1327 1328 1329 1330 1331 1332 1333 1334 1335 1336 1337 1338 1339 1340 1341 1342 1343
/**
  Implement SHOW CREATE statement for stored routines.

  The operation finds the stored routine object specified by name and then
  calls sp_head::show_create_routine() for the object.

  @param thd  Thread context.
  @param type Stored routine type
              (TYPE_ENUM_PROCEDURE or TYPE_ENUM_FUNCTION)
  @param name Stored routine name.

  @return Error status.
    @retval FALSE on success
    @retval TRUE on error
*/

bool
sp_show_create_routine(THD *thd, int type, sp_name *name)
{
  bool err_status= TRUE;
  sp_head *sp;
anozdrin/alik@ibm's avatar
anozdrin/alik@ibm committed
1344 1345
  sp_cache **cache = type == TYPE_ENUM_PROCEDURE ?
                     &thd->sp_proc_cache : &thd->sp_func_cache;
anozdrin/alik@ibm's avatar
anozdrin/alik@ibm committed
1346 1347

  DBUG_ENTER("sp_show_create_routine");
kostja@bodhi.(none)'s avatar
kostja@bodhi.(none) committed
1348 1349 1350
  DBUG_PRINT("enter", ("name: %.*s",
                       (int) name->m_name.length,
                       name->m_name.str));
anozdrin/alik@ibm's avatar
anozdrin/alik@ibm committed
1351 1352 1353 1354 1355 1356 1357 1358 1359 1360 1361 1362 1363 1364 1365 1366 1367 1368 1369

  DBUG_ASSERT(type == TYPE_ENUM_PROCEDURE ||
              type == TYPE_ENUM_FUNCTION);

  if (type == TYPE_ENUM_PROCEDURE)
  {
    /*
       SHOW CREATE PROCEDURE may require two instances of one sp_head
       object when SHOW CREATE PROCEDURE is called for the procedure that
       is being executed. Basically, there is no actual recursion, so we
       increase the recursion limit for this statement (kind of hack).

       SHOW CREATE FUNCTION does not require this because SHOW CREATE
       statements are prohibitted within stored functions.
     */

    thd->variables.max_sp_recursion_depth++;
  }

anozdrin/alik@ibm's avatar
anozdrin/alik@ibm committed
1370
  if ((sp= sp_find_routine(thd, type, name, cache, FALSE)))
anozdrin/alik@ibm's avatar
anozdrin/alik@ibm committed
1371 1372 1373 1374 1375 1376 1377 1378
    err_status= sp->show_create_routine(thd, type);

  if (type == TYPE_ENUM_PROCEDURE)
    thd->variables.max_sp_recursion_depth--;

  DBUG_RETURN(err_status);
}

1379

1380
/**
1381
  Obtain object representing stored procedure/function by its name from
1382 1383
  stored procedures cache and looking into mysql.proc if needed.

1384 1385 1386 1387 1388 1389 1390 1391 1392 1393 1394
  @param thd          thread context
  @param type         type of object (TYPE_ENUM_FUNCTION or TYPE_ENUM_PROCEDURE)
  @param name         name of procedure
  @param cp           hash to look routine in
  @param cache_only   if true perform cache-only lookup
                      (Don't look in mysql.proc).

  @retval
    NonNULL pointer to sp_head object for the procedure
  @retval
    NULL    in case of error.
1395 1396
*/

1397
sp_head *
1398 1399
sp_find_routine(THD *thd, int type, sp_name *name, sp_cache **cp,
                bool cache_only)
1400 1401
{
  sp_head *sp;
1402 1403 1404 1405
  ulong depth= (type == TYPE_ENUM_PROCEDURE ?
                thd->variables.max_sp_recursion_depth :
                0);
  DBUG_ENTER("sp_find_routine");
1406 1407 1408
  DBUG_PRINT("enter", ("name:  %.*s.%.*s  type: %d  cache only %d",
                       (int) name->m_db.length, name->m_db.str,
                       (int) name->m_name.length, name->m_name.str,
1409
                       type, cache_only));
1410

1411
  if ((sp= sp_cache_lookup(cp, name)))
1412
  {
1413
    ulong level;
1414 1415
    sp_head *new_sp;
    const char *returns= "";
1416
    char definer[USER_HOST_BUFF_SIZE];
1417 1418 1419 1420 1421

    /*
      String buffer for RETURNS data type must have system charset;
      64 -- size of "returns" column of mysql.proc.
    */
1422
    String retstr(64);
1423
    retstr.set_charset(sp->get_creation_ctx()->get_client_cs());
1424

1425 1426 1427
    DBUG_PRINT("info", ("found: 0x%lx", (ulong)sp));
    if (sp->m_first_free_instance)
    {
1428
      DBUG_PRINT("info", ("first free: 0x%lx  level: %lu  flags %x",
1429 1430 1431 1432 1433 1434
                          (ulong)sp->m_first_free_instance,
                          sp->m_first_free_instance->m_recursion_level,
                          sp->m_first_free_instance->m_flags));
      DBUG_ASSERT(!(sp->m_first_free_instance->m_flags & sp_head::IS_INVOKED));
      if (sp->m_first_free_instance->m_recursion_level > depth)
      {
1435
        sp->recursion_level_error(thd);
1436 1437 1438 1439
        DBUG_RETURN(0);
      }
      DBUG_RETURN(sp->m_first_free_instance);
    }
1440 1441 1442 1443 1444
    /*
      Actually depth could be +1 than the actual value in case a SP calls
      SHOW CREATE PROCEDURE. Hence, the linked list could hold up to one more
      instance.
    */
1445

1446 1447 1448
    level= sp->m_last_cached_sp->m_recursion_level + 1;
    if (level > depth)
    {
1449
      sp->recursion_level_error(thd);
1450 1451
      DBUG_RETURN(0);
    }
1452 1453 1454 1455

    strxmov(definer, sp->m_definer_user.str, "@",
            sp->m_definer_host.str, NullS);
    if (type == TYPE_ENUM_FUNCTION)
1456
    {
1457 1458 1459 1460 1461 1462
      sp_returns_type(thd, retstr, sp);
      returns= retstr.ptr();
    }
    if (db_load_routine(thd, type, name, &new_sp,
                        sp->m_sql_mode, sp->m_params.str, returns,
                        sp->m_body.str, *sp->m_chistics, definer,
1463 1464
                        sp->m_created, sp->m_modified,
                        sp->get_creation_ctx()) == SP_OK)
1465 1466 1467 1468 1469 1470
    {
      sp->m_last_cached_sp->m_next_cached_sp= new_sp;
      new_sp->m_recursion_level= level;
      new_sp->m_first_instance= sp;
      sp->m_last_cached_sp= sp->m_first_free_instance= new_sp;
      DBUG_PRINT("info", ("added level: 0x%lx, level: %lu, flags %x",
1471 1472
                          (ulong)new_sp, new_sp->m_recursion_level,
                          new_sp->m_flags));
1473
      DBUG_RETURN(new_sp);
1474
    }
1475
    DBUG_RETURN(0);
1476 1477
  }
  if (!cache_only)
1478
  {
1479 1480 1481 1482 1483 1484 1485
    if (db_find_routine(thd, type, name, &sp) == SP_OK)
    {
      sp_cache_insert(cp, sp);
      DBUG_PRINT("info", ("added new: 0x%lx, level: %lu, flags %x",
                          (ulong)sp, sp->m_recursion_level,
                          sp->m_flags));
    }
1486
  }
1487 1488 1489
  DBUG_RETURN(sp);
}

monty@mysql.com's avatar
monty@mysql.com committed
1490

1491
/**
1492 1493
  This is used by sql_acl.cc:mysql_routine_grant() and is used to find
  the routines in 'routines'.
1494 1495 1496 1497 1498 1499 1500 1501

  @param thd Thread handler
  @param routines List of needles in the hay stack
  @param any Any of the needles are good enough

  @return
    @retval FALSE Found.
    @retval TRUE  Not found
1502
*/
1503

1504 1505
bool
sp_exist_routines(THD *thd, TABLE_LIST *routines, bool any)
1506
{
1507
  TABLE_LIST *routine;
1508
  bool sp_object_found;
1509
  DBUG_ENTER("sp_exists_routine");
1510
  for (routine= routines; routine; routine= routine->next_global)
1511 1512 1513 1514
  {
    sp_name *name;
    LEX_STRING lex_db;
    LEX_STRING lex_name;
1515 1516 1517 1518
    lex_db.length= strlen(routine->db);
    lex_name.length= strlen(routine->table_name);
    lex_db.str= thd->strmake(routine->db, lex_db.length);
    lex_name.str= thd->strmake(routine->table_name, lex_name.length);
1519
    name= new sp_name(lex_db, lex_name, true);
1520
    name->init_qname(thd);
1521 1522 1523 1524
    sp_object_found= sp_find_routine(thd, TYPE_ENUM_PROCEDURE, name,
                                     &thd->sp_proc_cache, FALSE) != NULL ||
                     sp_find_routine(thd, TYPE_ENUM_FUNCTION, name,
                                     &thd->sp_func_cache, FALSE) != NULL;
Marc Alff's avatar
Marc Alff committed
1525
    thd->warning_info->clear_warning_info(thd->query_id);
1526
    if (sp_object_found)
1527 1528
    {
      if (any)
1529
        break;
1530 1531 1532
    }
    else if (!any)
    {
1533 1534 1535
      my_error(ER_SP_DOES_NOT_EXIST, MYF(0), "FUNCTION or PROCEDURE",
               routine->table_name);
      DBUG_RETURN(TRUE);
1536 1537
    }
  }
1538
  DBUG_RETURN(FALSE);
1539 1540 1541
}


1542
/**
1543
  Check if a routine exists in the mysql.proc table, without actually
1544
  parsing the definition. (Used for dropping).
1545

1546 1547
  @param thd          thread context
  @param name         name of procedure
1548

1549 1550 1551 1552
  @retval
    0       Success
  @retval
    non-0   Error;  SP_OPEN_TABLE_FAILED or SP_KEY_NOT_FOUND
1553 1554 1555 1556 1557 1558 1559 1560 1561 1562 1563 1564 1565
*/

int
sp_routine_exists_in_table(THD *thd, int type, sp_name *name)
{
  TABLE *table;
  int ret;
  Open_tables_state open_tables_state_backup;

  if (!(table= open_proc_table_for_read(thd, &open_tables_state_backup)))
    ret= SP_OPEN_TABLE_FAILED;
  else
  {
1566
    if ((ret= db_find_routine_aux(thd, type, name, table)) != SP_OK)
1567
      ret= SP_KEY_NOT_FOUND;
1568
    close_system_tables(thd, &open_tables_state_backup);
1569 1570 1571 1572 1573
  }
  return ret;
}


1574
/**
1575 1576 1577 1578 1579 1580
  Structure that represents element in the set of stored routines
  used by statement or routine.
*/
struct Sroutine_hash_entry;

struct Sroutine_hash_entry
1581
{
1582 1583 1584
  /**
    Set key consisting of one-byte routine type and quoted routine name.
  */
1585
  LEX_STRING key;
1586
  /**
1587 1588 1589 1590
    Next element in list linking all routines in set. See also comments
    for LEX::sroutine/sroutine_list and sp_head::m_sroutines.
  */
  Sroutine_hash_entry *next;
1591
  /**
1592 1593 1594 1595 1596
    Uppermost view which directly or indirectly uses this routine.
    0 if routine is not used in view. Note that it also can be 0 if
    statement uses routine both via view and directly.
  */
  TABLE_LIST *belong_to_view;
1597 1598 1599
};


1600 1601
extern "C" uchar* sp_sroutine_key(const uchar *ptr, size_t *plen,
                                  my_bool first)
1602 1603 1604
{
  Sroutine_hash_entry *rn= (Sroutine_hash_entry *)ptr;
  *plen= rn->key.length;
1605
  return (uchar *)rn->key.str;
1606 1607
}

monty@mysql.com's avatar
monty@mysql.com committed
1608

1609
/**
1610 1611 1612 1613
  Check if
   - current statement (the one in thd->lex) needs table prelocking
   - first routine in thd->lex->sroutines_list needs to execute its body in
     prelocked mode.
1614

1615 1616 1617 1618 1619 1620 1621 1622 1623 1624
  @param thd                  Current thread, thd->lex is the statement to be
                              checked.
  @param[out] need_prelocking    TRUE  - prelocked mode should be activated
                                 before executing the statement; 
                                 FALSE - Don't activate prelocking
  @param[out] first_no_prelocking  TRUE  - Tables used by first routine in
                                   thd->lex->sroutines_list should be
                                   prelocked. FALSE - Otherwise.

  @note
1625 1626 1627 1628 1629
    This function assumes that for any "CALL proc(...)" statement routines_list 
    will have 'proc' as first element (it may have several, consider e.g.
    "proc(sp_func(...)))". This property is currently guaranted by the parser.
*/

1630 1631
void sp_get_prelocking_info(THD *thd, bool *need_prelocking, 
                            bool *first_no_prelocking)
1632 1633
{
  Sroutine_hash_entry *routine;
1634
  routine= (Sroutine_hash_entry*)thd->lex->sroutines_list.first;
1635

1636 1637
  DBUG_ASSERT(routine);
  bool first_is_procedure= (routine->key.str[0] == TYPE_ENUM_PROCEDURE);
1638

1639 1640
  *first_no_prelocking= first_is_procedure;
  *need_prelocking= !first_is_procedure || test(routine->next);
1641 1642 1643
}


1644
/**
1645 1646 1647
  Auxilary function that adds new element to the set of stored routines
  used by statement.

1648 1649 1650 1651 1652
  In case when statement uses stored routines but does not need
  prelocking (i.e. it does not use any tables) we will access the
  elements of LEX::sroutines set on prepared statement re-execution.
  Because of this we have to allocate memory for both hash element
  and copy of its key in persistent arena.
1653

1654 1655 1656 1657 1658 1659
  @param lex             LEX representing statement
  @param arena           Arena in which memory for new element will be
                         allocated
  @param key             Key for the hash representing set
  @param belong_to_view  Uppermost view which uses this routine
                         (0 if routine is not used by view)
1660

1661 1662
  @note
    Will also add element to end of 'LEX::sroutines_list' list.
1663

1664
  @todo
1665 1666 1667 1668 1669
    When we will got rid of these accesses on re-executions we will be
    able to allocate memory for hash elements in non-persitent arena
    and directly use key values from sp_head::m_sroutines sets instead
    of making their copies.

1670 1671 1672 1673 1674
  @retval
    TRUE   new element was added.
  @retval
    FALSE  element was not added (because it is already present in
    the set).
1675 1676
*/

1677
static bool add_used_routine(LEX *lex, Query_arena *arena,
1678 1679
                             const LEX_STRING *key,
                             TABLE_LIST *belong_to_view)
1680
{
Konstantin Osipov's avatar
Konstantin Osipov committed
1681 1682 1683
  my_hash_init_opt(&lex->sroutines, system_charset_info,
                   Query_tables_list::START_SROUTINES_HASH_SIZE,
                   0, 0, sp_sroutine_key, 0, 0);
1684

Konstantin Osipov's avatar
Konstantin Osipov committed
1685
  if (!my_hash_search(&lex->sroutines, (uchar *)key->str, key->length))
1686
  {
1687 1688
    Sroutine_hash_entry *rn=
      (Sroutine_hash_entry *)arena->alloc(sizeof(Sroutine_hash_entry) +
1689
                                          key->length + 1);
1690 1691 1692 1693
    if (!rn)              // OOM. Error will be reported using fatal_error().
      return FALSE;
    rn->key.length= key->length;
    rn->key.str= (char *)rn + sizeof(Sroutine_hash_entry);
1694
    memcpy(rn->key.str, key->str, key->length + 1);
1695 1696
    if (my_hash_insert(&lex->sroutines, (uchar *)rn))
      return FALSE;
1697
    lex->sroutines_list.link_in_list((uchar *)rn, (uchar **)&rn->next);
1698
    rn->belong_to_view= belong_to_view;
1699
    return TRUE;
1700
  }
1701
  return FALSE;
1702 1703
}

monty@mysql.com's avatar
monty@mysql.com committed
1704

1705
/**
1706 1707
  Add routine which is explicitly used by statement to the set of stored
  routines used by this statement.
1708

1709 1710 1711 1712 1713 1714 1715 1716
  To be friendly towards prepared statements one should pass
  persistent arena as second argument.

  @param lex       LEX representing statement
  @param arena     arena in which memory for new element of the set
                   will be allocated
  @param rt        routine name
  @param rt_type   routine type (one of TYPE_ENUM_PROCEDURE/...)
1717

1718
  @note
1719 1720
    Will also add element to end of 'LEX::sroutines_list' list (and will
    take into account that this is explicitly used routine).
1721 1722
*/

1723
void sp_add_used_routine(LEX *lex, Query_arena *arena,
1724 1725 1726
                         sp_name *rt, char rt_type)
{
  rt->set_routine_type(rt_type);
1727
  (void)add_used_routine(lex, arena, &rt->m_sroutines_key, 0);
1728 1729 1730 1731 1732
  lex->sroutines_list_own_last= lex->sroutines_list.next;
  lex->sroutines_list_own_elements= lex->sroutines_list.elements;
}


1733
/**
1734 1735 1736
  Remove routines which are only indirectly used by statement from
  the set of routines used by this statement.

1737
  @param lex  LEX representing statement
1738 1739 1740 1741 1742 1743 1744 1745 1746 1747 1748 1749 1750
*/

void sp_remove_not_own_routines(LEX *lex)
{
  Sroutine_hash_entry *not_own_rt, *next_rt;
  for (not_own_rt= *(Sroutine_hash_entry **)lex->sroutines_list_own_last;
       not_own_rt; not_own_rt= next_rt)
  {
    /*
      It is safe to obtain not_own_rt->next after calling hash_delete() now
      but we want to be more future-proof.
    */
    next_rt= not_own_rt->next;
Konstantin Osipov's avatar
Konstantin Osipov committed
1751
    my_hash_delete(&lex->sroutines, (uchar *)not_own_rt);
1752 1753 1754 1755 1756
  }

  *(Sroutine_hash_entry **)lex->sroutines_list_own_last= NULL;
  lex->sroutines_list.next= lex->sroutines_list_own_last;
  lex->sroutines_list.elements= lex->sroutines_list_own_elements;
1757 1758 1759
}


1760
/**
1761 1762 1763
  Merge contents of two hashes representing sets of routines used
  by statements or by other routines.

1764 1765
  @param dst   hash to which elements should be added
  @param src   hash from which elements merged
1766

1767
  @note
1768 1769 1770 1771 1772 1773
    This procedure won't create new Sroutine_hash_entry objects,
    instead it will simply add elements from source to destination
    hash. Thus time of life of elements in destination hash becomes
    dependant on time of life of elements from source hash. It also
    won't touch lists linking elements in source and destination
    hashes.
1774 1775 1776 1777

  @returns
    @return TRUE Failure
    @return FALSE Success
1778 1779
*/

1780
bool sp_update_sp_used_routines(HASH *dst, HASH *src)
1781
{
1782
  for (uint i=0 ; i < src->records ; i++)
1783
  {
Konstantin Osipov's avatar
Konstantin Osipov committed
1784 1785
    Sroutine_hash_entry *rt= (Sroutine_hash_entry *)my_hash_element(src, i);
    if (!my_hash_search(dst, (uchar *)rt->key.str, rt->key.length))
1786 1787 1788 1789
    {
      if (my_hash_insert(dst, (uchar *)rt))
        return TRUE;
    }
1790
  }
1791
  return FALSE;
1792 1793
}

monty@mysql.com's avatar
monty@mysql.com committed
1794

1795
/**
1796 1797
  Add contents of hash representing set of routines to the set of
  routines used by statement.
1798

1799 1800 1801 1802 1803
  @param thd             Thread context
  @param lex             LEX representing statement
  @param src             Hash representing set from which routines will
                         be added
  @param belong_to_view  Uppermost view which uses these routines, 0 if none
1804

1805
  @note
1806 1807 1808
    It will also add elements to end of 'LEX::sroutines_list' list.
*/

1809 1810 1811
static void
sp_update_stmt_used_routines(THD *thd, LEX *lex, HASH *src,
                             TABLE_LIST *belong_to_view)
1812 1813 1814
{
  for (uint i=0 ; i < src->records ; i++)
  {
Konstantin Osipov's avatar
Konstantin Osipov committed
1815
    Sroutine_hash_entry *rt= (Sroutine_hash_entry *)my_hash_element(src, i);
1816
    (void)add_used_routine(lex, thd->stmt_arena, &rt->key, belong_to_view);
1817 1818 1819 1820
  }
}


1821
/**
1822 1823 1824
  Add contents of list representing set of routines to the set of
  routines used by statement.

1825 1826 1827 1828 1829
  @param thd             Thread context
  @param lex             LEX representing statement
  @param src             List representing set from which routines will
                         be added
  @param belong_to_view  Uppermost view which uses these routines, 0 if none
1830

1831
  @note
1832 1833 1834
    It will also add elements to end of 'LEX::sroutines_list' list.
*/

1835 1836
static void sp_update_stmt_used_routines(THD *thd, LEX *lex, SQL_LIST *src,
                                         TABLE_LIST *belong_to_view)
1837 1838 1839
{
  for (Sroutine_hash_entry *rt= (Sroutine_hash_entry *)src->first;
       rt; rt= rt->next)
1840
    (void)add_used_routine(lex, thd->stmt_arena, &rt->key, belong_to_view);
1841 1842 1843
}


1844
/**
1845 1846 1847 1848
  Cache sub-set of routines used by statement, add tables used by these
  routines to statement table list. Do the same for all routines used
  by these routines.

1849 1850 1851 1852 1853 1854 1855 1856 1857 1858
  @param thd               thread context
  @param lex               LEX representing statement
  @param start             first routine from the list of routines to be cached
                           (this list defines mentioned sub-set).
  @param first_no_prelock  If true, don't add tables or cache routines used by
                           the body of the first routine (i.e. *start)
                           will be executed in non-prelocked mode.
  @param tabs_changed      Set to TRUE some tables were added, FALSE otherwise

  @note
1859 1860 1861
    If some function is missing this won't be reported here.
    Instead this fact will be discovered during query execution.

1862 1863 1864 1865
  @retval
    0       success
  @retval
    non-0   failure
1866 1867
*/

1868
static int
1869
sp_cache_routines_and_add_tables_aux(THD *thd, LEX *lex,
1870
                                     Sroutine_hash_entry *start, 
1871
                                     bool first_no_prelock)
1872
{
1873
  int ret= 0;
1874
  bool first= TRUE;
1875
  DBUG_ENTER("sp_cache_routines_and_add_tables_aux");
1876

1877
  for (Sroutine_hash_entry *rt= start; rt; rt= rt->next)
1878
  {
1879
    sp_name name(thd, rt->key.str, rt->key.length);
1880 1881
    int type= rt->key.str[0];
    sp_head *sp;
1882

1883 1884 1885
    if (!(sp= sp_cache_lookup((type == TYPE_ENUM_FUNCTION ?
                              &thd->sp_func_cache : &thd->sp_proc_cache),
                              &name)))
1886
    {
1887
      switch ((ret= db_find_routine(thd, type, &name, &sp)))
1888
      {
1889 1890 1891 1892 1893 1894 1895 1896 1897 1898 1899 1900
      case SP_OK:
        {
          if (type == TYPE_ENUM_FUNCTION)
            sp_cache_insert(&thd->sp_func_cache, sp);
          else
            sp_cache_insert(&thd->sp_proc_cache, sp);
        }
        break;
      case SP_KEY_NOT_FOUND:
        ret= SP_OK;
        break;
      default:
1901 1902 1903
        /* Query might have been killed, don't set error. */
        if (thd->killed)
          break;
1904
        /*
1905 1906 1907 1908 1909 1910
          Any error when loading an existing routine is either some problem
          with the mysql.proc table, or a parse error because the contents
          has been tampered with (in which case we clear that error).
        */
        if (ret == SP_PARSE_ERROR)
          thd->clear_error();
1911 1912 1913 1914 1915
        /*
          If we cleared the parse error, or when db_find_routine() flagged
          an error with it's return value without calling my_error(), we
          set the generic "mysql.proc table corrupt" error here.
         */
1916
        if (! thd->is_error())
1917
        {
1918 1919 1920 1921 1922 1923 1924 1925
          /*
            SP allows full NAME_LEN chars thus he have to allocate enough
            size in bytes. Otherwise there is stack overrun could happen
            if multibyte sequence is `name`. `db` is still safe because the
            rest of the server checks agains NAME_LEN bytes and not chars.
            Hence, the overrun happens only if the name is in length > 32 and
            uses multibyte (cyrillic, greek, etc.)
          */
1926
          char n[NAME_LEN*2+2];
1927 1928 1929 1930 1931 1932

          /* m_qname.str is not always \0 terminated */
          memcpy(n, name.m_qname.str, name.m_qname.length);
          n[name.m_qname.length]= '\0';
          my_error(ER_SP_PROC_TABLE_CORRUPT, MYF(0), n, ret);
        }
1933
        break;
1934 1935 1936 1937
      }
    }
    if (sp)
    {
1938 1939
      if (!(first && first_no_prelock))
      {
1940 1941
        sp_update_stmt_used_routines(thd, lex, &sp->m_sroutines,
                                     rt->belong_to_view);
1942 1943
        (void)sp->add_used_tables_to_table_list(thd, &lex->query_tables_last,
                                                rt->belong_to_view);
1944
      }
1945
      sp->propagate_attributes(lex);
1946
    }
1947
    first= FALSE;
1948
  }
1949
  DBUG_RETURN(ret);
1950 1951 1952
}


1953
/**
1954 1955 1956 1957
  Cache all routines from the set of used by statement, add tables used
  by those routines to statement table list. Do the same for all routines
  used by those routines.

1958 1959 1960 1961
  @param thd               thread context
  @param lex               LEX representing statement
  @param first_no_prelock  If true, don't add tables or cache routines used by
                           the body of the first routine (i.e. *start)
1962

1963 1964 1965 1966
  @retval
    0       success
  @retval
    non-0   failure
1967 1968
*/

1969
int
1970
sp_cache_routines_and_add_tables(THD *thd, LEX *lex, bool first_no_prelock)
1971 1972
{
  return sp_cache_routines_and_add_tables_aux(thd, lex,
1973
           (Sroutine_hash_entry *)lex->sroutines_list.first,
1974
           first_no_prelock);
1975 1976 1977
}


1978 1979 1980 1981
/**
  Add all routines used by view to the set of routines used by
  statement.

1982 1983 1984
  Add tables used by those routines to statement table list. Do the same
  for all routines used by these routines.

1985 1986 1987
  @param thd   Thread context
  @param lex   LEX representing statement
  @param view  Table list element representing view
1988

1989 1990 1991 1992
  @retval
    0       success
  @retval
    non-0   failure
1993 1994
*/

1995
int
1996
sp_cache_routines_and_add_tables_for_view(THD *thd, LEX *lex, TABLE_LIST *view)
1997 1998 1999
{
  Sroutine_hash_entry **last_cached_routine_ptr=
                          (Sroutine_hash_entry **)lex->sroutines_list.next;
2000 2001
  sp_update_stmt_used_routines(thd, lex, &view->view->sroutines_list,
                               view->top_table());
2002 2003
  return sp_cache_routines_and_add_tables_aux(thd, lex,
                                              *last_cached_routine_ptr, FALSE);
2004 2005 2006
}


2007
/**
2008 2009 2010 2011
  Add triggers for table to the set of routines used by statement.
  Add tables used by them to statement table list. Do the same for
  all implicitly used routines.

2012 2013 2014
  @param thd    thread context
  @param lex    LEX respresenting statement
  @param table  Table list element for table with trigger
2015

2016 2017 2018 2019
  @retval
    0       success
  @retval
    non-0   failure
2020 2021
*/

2022
int
2023
sp_cache_routines_and_add_tables_for_triggers(THD *thd, LEX *lex,
2024
                                              TABLE_LIST *table)
2025
{
2026
  int ret= 0;
2027

2028 2029
  DBUG_ENTER("sp_cache_routines_and_add_tables_for_triggers");

2030 2031 2032 2033 2034
  Sroutine_hash_entry **last_cached_routine_ptr=
    (Sroutine_hash_entry **)lex->sroutines_list.next;

  if (static_cast<int>(table->lock_type) >=
      static_cast<int>(TL_WRITE_ALLOW_WRITE))
2035
  {
2036
    for (int i= 0; i < (int)TRG_EVENT_MAX; i++)
2037
    {
2038 2039
      if (table->trg_event_map &
          static_cast<uint8>(1 << static_cast<int>(i)))
2040
      {
2041
        for (int j= 0; j < (int)TRG_ACTION_MAX; j++)
2042
        {
2043 2044 2045 2046 2047 2048 2049 2050
          /* We can have only one trigger per action type currently */
          sp_head *trigger= table->table->triggers->bodies[i][j];
          if (trigger &&
              add_used_routine(lex, thd->stmt_arena, &trigger->m_sroutines_key,
                               table->belong_to_view))
          {
            trigger->add_used_tables_to_table_list(thd, &lex->query_tables_last,
                                                   table->belong_to_view);
2051
            trigger->propagate_attributes(lex);
2052 2053 2054 2055
            sp_update_stmt_used_routines(thd, lex,
                                         &trigger->m_sroutines,
                                         table->belong_to_view);
          }
2056
        }
2057 2058
      }
    }
2059
  }
2060 2061
  ret= sp_cache_routines_and_add_tables_aux(thd, lex,
                                            *last_cached_routine_ptr,
2062
                                            FALSE);
2063
  DBUG_RETURN(ret);
2064
}
2065

2066

2067 2068 2069 2070 2071 2072
/**
  Generates the CREATE... string from the table information.

  @return
    Returns TRUE on success, FALSE on (alloc) failure.
*/
2073
static bool
Sergey Glukhov's avatar
Sergey Glukhov committed
2074

2075
create_string(THD *thd, String *buf,
2076 2077 2078 2079 2080 2081 2082
              int type,
              const char *db, ulong dblen,
              const char *name, ulong namelen,
              const char *params, ulong paramslen,
              const char *returns, ulong returnslen,
              const char *body, ulong bodylen,
              st_sp_chistics *chistics,
2083
              const LEX_STRING *definer_user,
Sergey Glukhov's avatar
Sergey Glukhov committed
2084 2085
              const LEX_STRING *definer_host,
              ulong sql_mode)
2086
{
Sergey Glukhov's avatar
Sergey Glukhov committed
2087
  ulong old_sql_mode= thd->variables.sql_mode;
2088
  /* Make some room to begin with */
2089
  if (buf->alloc(100 + dblen + 1 + namelen + paramslen + returnslen + bodylen +
2090 2091
		 chistics->comment.length + 10 /* length of " DEFINER= "*/ +
                 USER_HOST_BUFF_SIZE))
2092
    return FALSE;
monty@mysql.com's avatar
monty@mysql.com committed
2093

Sergey Glukhov's avatar
Sergey Glukhov committed
2094
  thd->variables.sql_mode= sql_mode;
bell@sanja.is.com.ua's avatar
merge  
bell@sanja.is.com.ua committed
2095
  buf->append(STRING_WITH_LEN("CREATE "));
2096
  append_definer(thd, buf, definer_user, definer_host);
2097
  if (type == TYPE_ENUM_FUNCTION)
bell@sanja.is.com.ua's avatar
merge  
bell@sanja.is.com.ua committed
2098
    buf->append(STRING_WITH_LEN("FUNCTION "));
2099
  else
bell@sanja.is.com.ua's avatar
merge  
bell@sanja.is.com.ua committed
2100
    buf->append(STRING_WITH_LEN("PROCEDURE "));
2101 2102 2103 2104 2105
  if (dblen > 0)
  {
    append_identifier(thd, buf, db, dblen);
    buf->append('.');
  }
2106
  append_identifier(thd, buf, name, namelen);
2107 2108 2109 2110 2111
  buf->append('(');
  buf->append(params, paramslen);
  buf->append(')');
  if (type == TYPE_ENUM_FUNCTION)
  {
bell@sanja.is.com.ua's avatar
merge  
bell@sanja.is.com.ua committed
2112
    buf->append(STRING_WITH_LEN(" RETURNS "));
2113 2114 2115
    buf->append(returns, returnslen);
  }
  buf->append('\n');
2116 2117
  switch (chistics->daccess) {
  case SP_NO_SQL:
bell@sanja.is.com.ua's avatar
merge  
bell@sanja.is.com.ua committed
2118
    buf->append(STRING_WITH_LEN("    NO SQL\n"));
2119 2120
    break;
  case SP_READS_SQL_DATA:
bell@sanja.is.com.ua's avatar
merge  
bell@sanja.is.com.ua committed
2121
    buf->append(STRING_WITH_LEN("    READS SQL DATA\n"));
2122 2123
    break;
  case SP_MODIFIES_SQL_DATA:
bell@sanja.is.com.ua's avatar
merge  
bell@sanja.is.com.ua committed
2124
    buf->append(STRING_WITH_LEN("    MODIFIES SQL DATA\n"));
2125
    break;
monty@mysql.com's avatar
monty@mysql.com committed
2126 2127 2128 2129
  case SP_DEFAULT_ACCESS:
  case SP_CONTAINS_SQL:
    /* Do nothing */
    break;
2130
  }
2131
  if (chistics->detistic)
bell@sanja.is.com.ua's avatar
merge  
bell@sanja.is.com.ua committed
2132
    buf->append(STRING_WITH_LEN("    DETERMINISTIC\n"));
2133
  if (chistics->suid == SP_IS_NOT_SUID)
bell@sanja.is.com.ua's avatar
merge  
bell@sanja.is.com.ua committed
2134
    buf->append(STRING_WITH_LEN("    SQL SECURITY INVOKER\n"));
2135
  if (chistics->comment.length)
monty@mysql.com's avatar
monty@mysql.com committed
2136
  {
bell@sanja.is.com.ua's avatar
merge  
bell@sanja.is.com.ua committed
2137
    buf->append(STRING_WITH_LEN("    COMMENT "));
2138 2139
    append_unescaped(buf, chistics->comment.str, chistics->comment.length);
    buf->append('\n');
monty@mysql.com's avatar
monty@mysql.com committed
2140
  }
2141
  buf->append(body, bodylen);
Sergey Glukhov's avatar
Sergey Glukhov committed
2142
  thd->variables.sql_mode= old_sql_mode;
2143
  return TRUE;
2144
}
Sergey Glukhov's avatar
Sergey Glukhov committed
2145 2146 2147 2148 2149 2150 2151 2152 2153 2154 2155 2156 2157 2158 2159 2160 2161 2162 2163 2164 2165 2166 2167 2168 2169 2170 2171 2172 2173 2174 2175 2176 2177 2178 2179 2180 2181 2182 2183 2184 2185 2186 2187 2188 2189 2190 2191 2192 2193 2194 2195 2196 2197 2198 2199 2200 2201 2202 2203 2204 2205 2206 2207 2208 2209 2210 2211 2212 2213 2214 2215 2216 2217


/**
  @brief    The function loads sp_head struct for information schema purposes
            (used for I_S ROUTINES & PARAMETERS tables).

  @param[in]      thd               thread handler
  @param[in]      proc_table        mysql.proc table structurte
  @param[in]      db                database name
  @param[in]      name              sp name
  @param[in]      sql_mode          SQL mode
  @param[in]      type              Routine type
  @param[in]      returns           'returns' string
  @param[in]      params            parameters definition string
  @param[out]     free_sp_head      returns 1 if we need to free sp_head struct
                                    otherwise returns 0
                                    
  @return     Pointer on sp_head struct
    @retval   #                     Pointer on sp_head struct
    @retval   0                     error
*/

sp_head *
sp_load_for_information_schema(THD *thd, TABLE *proc_table, String *db,
                               String *name, ulong sql_mode, int type,
                               const char *returns, const char *params,
                               bool *free_sp_head)
{
  const char *sp_body;
  String defstr;
  struct st_sp_chistics sp_chistics;
  const LEX_STRING definer_user= {(char*)STRING_WITH_LEN("")};
  const LEX_STRING definer_host= {(char*)STRING_WITH_LEN("")}; 
  LEX_STRING sp_db_str;
  LEX_STRING sp_name_str;
  sp_head *sp;
  sp_cache **spc= ((type == TYPE_ENUM_PROCEDURE) ?
                  &thd->sp_proc_cache : &thd->sp_func_cache);
  sp_db_str.str= db->c_ptr();
  sp_db_str.length= db->length();
  sp_name_str.str= name->c_ptr();
  sp_name_str.length= name->length();
  sp_name sp_name_obj(sp_db_str, sp_name_str, true);
  sp_name_obj.init_qname(thd);
  *free_sp_head= 0;
  if ((sp= sp_cache_lookup(spc, &sp_name_obj)))
  {
    return sp;
  }

  LEX *old_lex= thd->lex, newlex;
  Stored_program_creation_ctx *creation_ctx= 
    Stored_routine_creation_ctx::load_from_db(thd, &sp_name_obj, proc_table);
  sp_body= (type == TYPE_ENUM_FUNCTION ? "RETURN NULL" : "BEGIN END");
  bzero((char*) &sp_chistics, sizeof(sp_chistics));
  defstr.set_charset(creation_ctx->get_client_cs());
  if (!create_string(thd, &defstr, type, 
                     sp_db_str.str, sp_db_str.length, 
                     sp_name_obj.m_name.str, sp_name_obj.m_name.length, 
                     params, strlen(params),
                     returns, strlen(returns), 
                     sp_body, strlen(sp_body),
                     &sp_chistics, &definer_user, &definer_host, sql_mode))
    return 0;

  thd->lex= &newlex;
  newlex.current_select= NULL; 
  sp= sp_compile(thd, &defstr, sql_mode, creation_ctx);
  *free_sp_head= 1;
  lex_end(thd->lex);
  thd->lex= old_lex;
  return sp;
}