mysql-show_temp_51.patch

   1 # name       : show_temp_51.patch
   2 # introduced : 11 or before
   3 # maintainer : Yasufumi
   4 #
   5 #!!! notice !!!
   6 # Any small change to this file in the main branch
   7 # should be done or reviewed by the maintainer!
   8 --- a/sql/mysqld.cc
   9 +++ b/sql/mysqld.cc
  10 @@ -3259,6 +3259,7 @@
  11    {"show_table_status",    (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_TABLE_STATUS]), SHOW_LONG_STATUS},
  12    {"show_tables",          (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_TABLES]), SHOW_LONG_STATUS},
  13    {"show_thread_statistics",(char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_THREAD_STATS]), SHOW_LONG_STATUS},
  14 +  {"show_temporary_tables",(char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_TEMPORARY_TABLES]), SHOW_LONG_STATUS},
  15    {"show_triggers",        (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_TRIGGERS]), SHOW_LONG_STATUS},
  16    {"show_user_statistics", (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_USER_STATS]), SHOW_LONG_STATUS},
  17    {"show_variables",       (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_VARIABLES]), SHOW_LONG_STATUS},
  18 --- a/sql/sql_lex.h
  19 +++ b/sql/sql_lex.h
  20 @@ -118,7 +118,7 @@
  21    SQLCOM_CREATE_EVENT, SQLCOM_ALTER_EVENT, SQLCOM_DROP_EVENT,
  22    SQLCOM_SHOW_CREATE_EVENT, SQLCOM_SHOW_EVENTS,
  23    SQLCOM_SHOW_CREATE_TRIGGER,
  24 -  SQLCOM_ALTER_DB_UPGRADE,
  25 +  SQLCOM_ALTER_DB_UPGRADE, SQLCOM_SHOW_TEMPORARY_TABLES,
  26    SQLCOM_SHOW_PROFILE, SQLCOM_SHOW_PROFILES,
  27    SQLCOM_SHOW_PATCHES,
  28
  29 --- a/sql/sql_parse.cc
  30 +++ b/sql/sql_parse.cc
  31 @@ -342,6 +342,9 @@
  32     sql_command_flags[SQLCOM_SHOW_TABLES]=       (CF_STATUS_COMMAND |
  33                                                   CF_SHOW_TABLE_COMMAND |
  34                                                   CF_REEXECUTION_FRAGILE);
  35 +   sql_command_flags[SQLCOM_SHOW_TEMPORARY_TABLES]=       (CF_STATUS_COMMAND |
  36 +                                                 CF_SHOW_TABLE_COMMAND |
  37 +                                                 CF_REEXECUTION_FRAGILE);
  38    sql_command_flags[SQLCOM_SHOW_TABLE_STATUS]= (CF_STATUS_COMMAND |
  39                                                  CF_SHOW_TABLE_COMMAND |
  40                                                  CF_REEXECUTION_FRAGILE);
  41 @@ -1877,6 +1880,8 @@
  42
  43    case SCH_TABLE_NAMES:
  44    case SCH_TABLES:
  45 +  case SCH_TEMPORARY_TABLES:
  46 +  case SCH_GLOBAL_TEMPORARY_TABLES:
  47    case SCH_VIEWS:
  48    case SCH_TRIGGERS:
  49    case SCH_EVENTS:
  50 @@ -2376,6 +2381,7 @@
  51    }
  52    case SQLCOM_SHOW_DATABASES:
  53    case SQLCOM_SHOW_TABLES:
  54 +  case SQLCOM_SHOW_TEMPORARY_TABLES:
  55    case SQLCOM_SHOW_TRIGGERS:
  56    case SQLCOM_SHOW_TABLE_STATUS:
  57    case SQLCOM_SHOW_OPEN_TABLES:
  58 @@ -5529,6 +5535,8 @@
  59
  60    case SCH_TABLE_NAMES:
  61    case SCH_TABLES:
  62 +  case SCH_TEMPORARY_TABLES:
  63 +  case SCH_GLOBAL_TEMPORARY_TABLES:
  64    case SCH_VIEWS:
  65    case SCH_TRIGGERS:
  66    case SCH_EVENTS:
  67 --- a/sql/sql_show.cc
  68 +++ b/sql/sql_show.cc
  69 @@ -3031,6 +3031,7 @@
  70      break;
  71    case SQLCOM_SHOW_TABLES:
  72    case SQLCOM_SHOW_TABLE_STATUS:
  73 +  case SQLCOM_SHOW_TEMPORARY_TABLES:
  74    case SQLCOM_SHOW_TRIGGERS:
  75    case SQLCOM_SHOW_EVENTS:
  76      thd->make_lex_string(&lookup_field_values->db_value,
  77 @@ -3607,6 +3608,231 @@
  78    return (uint) OPEN_FULL_TABLE;
  79  }
  80
  81 +/**
  82 +  @brief          Change I_S table item list for SHOW [GLOBAL] TEMPORARY TABLES [FROM/IN db]
  83 +
  84 +  @param[in]      thd                      thread handler
  85 +  @param[in]      schema_table             I_S table
  86 +
  87 +  @return         Operation status
  88 +    @retval       0                        success
  89 +    @retval       1                        error
  90 +*/
  91 +int make_temporary_tables_old_format(THD *thd, ST_SCHEMA_TABLE *schema_table)
  92 +{
  93 +  char tmp[128];
  94 +  String buffer(tmp,sizeof(tmp), thd->charset());
  95 +  LEX *lex= thd->lex;
  96 +  Name_resolution_context *context= &lex->select_lex.context;
  97 +
  98 +  if (thd->lex->option_type == OPT_GLOBAL) {
  99 +    ST_FIELD_INFO *field_info= &schema_table->fields_info[0];
 100 +    Item_field *field= new Item_field(context, NullS, NullS, field_info->field_name);
 101 +    if (add_item_to_list(thd, field))
 102 +      return 1;
 103 +    field->set_name(field_info->old_name, strlen(field_info->old_name), system_charset_info);
 104 +  }
 105 +
 106 +  ST_FIELD_INFO *field_info= &schema_table->fields_info[2];
 107 +  buffer.length(0);
 108 +  buffer.append(field_info->old_name);
 109 +  buffer.append(lex->select_lex.db);
 110 +
 111 +  if (lex->wild && lex->wild->ptr())
 112 +  {
 113 +    buffer.append(STRING_WITH_LEN(" ("));
 114 +    buffer.append(lex->wild->ptr());
 115 +    buffer.append(')');
 116 +  }
 117 +
 118 +  Item_field *field= new Item_field(context, NullS, NullS, field_info->field_name);
 119 +  if (add_item_to_list(thd, field))
 120 +    return 1;
 121 +
 122 +  field->set_name(buffer.ptr(), buffer.length(), system_charset_info);
 123 +  return 0;
 124 +}
 125 +
 126 +/**
 127 +  @brief          Fill records for temporary tables by reading info from table object
 128 +
 129 +  @param[in]      thd                      thread handler
 130 +  @param[in]      table                    I_S table
 131 +  @param[in]      tmp_table                temporary table
 132 +  @param[in]      db                       database name
 133 +
 134 +  @return         Operation status
 135 +    @retval       0                        success
 136 +    @retval       1                        error
 137 +*/
 138 +
 139 +static int store_temporary_table_record(THD *thd, TABLE *table, TABLE *tmp_table, const char *db, bool table_name_only)
 140 +{
 141 +  CHARSET_INFO *cs= system_charset_info;
 142 +  DBUG_ENTER("store_temporary_table_record");
 143 +
 144 +  if (db && my_strcasecmp(cs, db, tmp_table->s->db.str))
 145 +    DBUG_RETURN(0);
 146 +
 147 +  restore_record(table, s->default_values);
 148 +
 149 +  //session_id
 150 +  table->field[0]->store((longlong) thd->thread_id, TRUE);
 151 +
 152 +  //database
 153 +  table->field[1]->store(tmp_table->s->db.str, tmp_table->s->db.length, cs);
 154 +
 155 +  //table
 156 +  table->field[2]->store(tmp_table->s->table_name.str, tmp_table->s->table_name.length, cs);
 157 +
 158 +  if (table_name_only)
 159 +    DBUG_RETURN(schema_table_store_record(thd, table));
 160 +
 161 +  //engine
 162 +  handler *handle= tmp_table->file;
 163 +  char *engineType = (char *)(handle ? handle->table_type() : "UNKNOWN");
 164 +  table->field[3]->store(engineType, strlen(engineType), cs);
 165 +
 166 +  //name
 167 +  if (tmp_table->s->path.str) {
 168 +    char *p=strstr(tmp_table->s->path.str, "#sql");
 169 +    int len=tmp_table->s->path.length-(p-tmp_table->s->path.str);
 170 +    table->field[4]->store(p, min(FN_REFLEN, len), cs);
 171 +  }
 172 +
 173 +  // file stats
 174 +  handler *file= tmp_table->file;
 175 +
 176 +  if (file) {
 177 +
 178 +    MYSQL_TIME time;
 179 +
 180 +    /**
 181 +        TODO: InnoDB stat(file) checks file on short names within data dictionary
 182 +        rather than using full path, because of that, temp files created in
 183 +        TMPDIR will not have access/create time as it will not find the file
 184 +
 185 +        The fix is to patch InnoDB to use full path
 186 +    */
 187 +    file->info(HA_STATUS_VARIABLE | HA_STATUS_TIME | HA_STATUS_NO_LOCK);
 188 +
 189 +    table->field[5]->store((longlong) file->stats.records, TRUE);
 190 +    table->field[5]->set_notnull();
 191 +
 192 +    table->field[6]->store((longlong) file->stats.mean_rec_length, TRUE);
 193 +    table->field[7]->store((longlong) file->stats.data_file_length, TRUE);
 194 +    table->field[8]->store((longlong) file->stats.index_file_length, TRUE);
 195 +    if (file->stats.create_time)
 196 +    {
 197 +      thd->variables.time_zone->gmt_sec_to_TIME(&time,
 198 +                                                (my_time_t) file->stats.create_time);
 199 +      table->field[9]->store_time(&time, MYSQL_TIMESTAMP_DATETIME);
 200 +      table->field[9]->set_notnull();
 201 +    }
 202 +    if (file->stats.update_time)
 203 +    {
 204 +      thd->variables.time_zone->gmt_sec_to_TIME(&time,
 205 +                                                (my_time_t) file->stats.update_time);
 206 +      table->field[10]->store_time(&time, MYSQL_TIMESTAMP_DATETIME);
 207 +      table->field[10]->set_notnull();
 208 +    }
 209 +  }
 210 +
 211 +  DBUG_RETURN(schema_table_store_record(thd, table));
 212 +}
 213 +
 214 +/**
 215 +  @brief          Fill I_S tables with global temporary tables
 216 +
 217 +  @param[in]      thd                      thread handler
 218 +  @param[in]      tables                   I_S table
 219 +  @param[in]      cond                     'WHERE' condition
 220 +
 221 +  @return         Operation status
 222 +    @retval       0                        success
 223 +    @retval       1                        error
 224 +*/
 225 +
 226 +static int fill_global_temporary_tables(THD *thd, TABLE_LIST *tables, COND *cond)
 227 +{
 228 +  DBUG_ENTER("fill_global_temporary_tables");
 229 +
 230 +  pthread_mutex_lock(&LOCK_thread_count);
 231 +
 232 +  bool table_names_only= (thd->lex->sql_command == SQLCOM_SHOW_TEMPORARY_TABLES) ? 1 : 0;
 233 +  I_List_iterator<THD> it(threads);
 234 +  THD *thd_item;
 235 +  TABLE *tmp;
 236 +
 237 +#ifndef NO_EMBEDDED_ACCESS_CHECKS
 238 +  Security_context *sctx= thd->security_ctx;
 239 +  uint db_access;
 240 +#endif
 241 +
 242 +  while ((thd_item=it++)) {
 243 +    pthread_mutex_lock(&thd_item->LOCK_temporary_tables);
 244 +    for (tmp=thd_item->temporary_tables; tmp; tmp=tmp->next) {
 245 +
 246 +#ifndef NO_EMBEDDED_ACCESS_CHECKS
 247 +      if (test_all_bits(sctx->master_access, DB_ACLS))
 248 +        db_access=DB_ACLS;
 249 +      else
 250 +        db_access= (acl_get(sctx->host, sctx->ip, sctx->priv_user, tmp->s->db.str, 0) | sctx->master_access);
 251 +
 252 +      if (!(db_access & DB_ACLS) && check_grant_db(thd,tmp->s->db.str)) {
 253 +        //no access for temp tables within this db for user
 254 +        continue;
 255 +      }
 256 +#endif
 257 +
 258 +      THD *t= tmp->in_use;
 259 +      tmp->in_use= thd;
 260 +
 261 +      if (store_temporary_table_record(thd_item, tables->table, tmp, thd->lex->select_lex.db, table_names_only)) {
 262 +        tmp->in_use= t;
 263 +        pthread_mutex_unlock(&thd_item->LOCK_temporary_tables);
 264 +        pthread_mutex_unlock(&LOCK_thread_count);
 265 +        DBUG_RETURN(1);
 266 +      }
 267 +
 268 +      tmp->in_use= t;
 269 +    }
 270 +    pthread_mutex_unlock(&thd_item->LOCK_temporary_tables);
 271 +  }
 272 +
 273 +  pthread_mutex_unlock(&LOCK_thread_count);
 274 +  DBUG_RETURN(0);
 275 +}
 276 +
 277 +/**
 278 +  @brief          Fill I_S tables with session temporary tables
 279 +
 280 +  @param[in]      thd                      thread handler
 281 +  @param[in]      tables                   I_S table
 282 +  @param[in]      cond                     'WHERE' condition
 283 +
 284 +  @return         Operation status
 285 +    @retval       0                        success
 286 +    @retval       1                        error
 287 +*/
 288 +
 289 +int fill_temporary_tables(THD *thd, TABLE_LIST *tables, COND *cond)
 290 +{
 291 +  DBUG_ENTER("fill_temporary_tables");
 292 +
 293 +  if (thd->lex->option_type == OPT_GLOBAL)
 294 +    DBUG_RETURN(fill_global_temporary_tables(thd, tables, cond));
 295 +
 296 +  bool table_names_only= (thd->lex->sql_command == SQLCOM_SHOW_TEMPORARY_TABLES) ? 1 : 0;
 297 +  TABLE *tmp;
 298 +
 299 +  for (tmp=thd->temporary_tables; tmp; tmp=tmp->next) {
 300 +    if (store_temporary_table_record(thd, tables->table, tmp, thd->lex->select_lex.db, table_names_only)) {
 301 +      DBUG_RETURN(1);
 302 +    }
 303 +  }
 304 +  DBUG_RETURN(0);
 305 +}
 306
 307  /**
 308    @brief          Fill I_S table with data from FRM file only
 309 @@ -6658,6 +6884,25 @@
 310    {0, 0, MYSQL_TYPE_STRING, 0, 0, 0, SKIP_OPEN_TABLE}
 311  };
 312
 313 +ST_FIELD_INFO temporary_table_fields_info[]=
 314 +{
 315 +  {"SESSION_ID", 4, MYSQL_TYPE_LONGLONG, 0, 0, "Session", SKIP_OPEN_TABLE},
 316 +  {"TABLE_SCHEMA", NAME_CHAR_LEN, MYSQL_TYPE_STRING, 0, 0, "Db", SKIP_OPEN_TABLE},
 317 +  {"TABLE_NAME", NAME_CHAR_LEN, MYSQL_TYPE_STRING, 0, 0, "Temp_tables_in_", SKIP_OPEN_TABLE},
 318 +  {"ENGINE", NAME_CHAR_LEN, MYSQL_TYPE_STRING, 0, 0, "Engine", OPEN_FRM_ONLY},
 319 +  {"NAME", FN_REFLEN, MYSQL_TYPE_STRING, 0, 0, "Name", SKIP_OPEN_TABLE},
 320 +  {"TABLE_ROWS", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONGLONG, 0,
 321 +   MY_I_S_UNSIGNED, "Rows", OPEN_FULL_TABLE},
 322 +  {"AVG_ROW_LENGTH", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONGLONG, 0,
 323 +   MY_I_S_UNSIGNED, "Avg Row", OPEN_FULL_TABLE},
 324 +  {"DATA_LENGTH", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONGLONG, 0,
 325 +   MY_I_S_UNSIGNED, "Data Length", OPEN_FULL_TABLE},
 326 +  {"INDEX_LENGTH", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONGLONG, 0,
 327 +   MY_I_S_UNSIGNED, "Index Size", OPEN_FULL_TABLE},
 328 +  {"CREATE_TIME", 0, MYSQL_TYPE_DATETIME, 0, 1, "Create Time", OPEN_FULL_TABLE},
 329 +  {"UPDATE_TIME", 0, MYSQL_TYPE_DATETIME, 0, 1, "Update Time", OPEN_FULL_TABLE},
 330 +  {0, 0, MYSQL_TYPE_STRING, 0, 0, 0, SKIP_OPEN_TABLE}
 331 +};
 332
 333  ST_FIELD_INFO columns_fields_info[]=
 334  {
 335 @@ -7318,6 +7563,9 @@
 336     fill_schema_files, 0, 0, -1, -1, 0, 0},
 337    {"GLOBAL_STATUS", variables_fields_info, create_schema_table,
 338     fill_status, make_old_format, 0, 0, -1, 0, 0},
 339 +  {"GLOBAL_TEMPORARY_TABLES", temporary_table_fields_info, create_schema_table,
 340 +   fill_global_temporary_tables, make_temporary_tables_old_format, 0, 2, 3, 0,
 341 +   OPEN_TABLE_ONLY|OPTIMIZE_I_S_TABLE},
 342    {"GLOBAL_VARIABLES", variables_fields_info, create_schema_table,
 343     fill_variables, make_old_format, 0, 0, -1, 0, 0},
 344    {"KEY_COLUMN_USAGE", key_column_usage_fields_info, create_schema_table,
 345 @@ -7361,6 +7609,9 @@
 346     get_all_tables, make_table_names_old_format, 0, 1, 2, 1, 0},
 347    {"TABLE_PRIVILEGES", table_privileges_fields_info, create_schema_table,
 348     fill_schema_table_privileges, 0, 0, -1, -1, 0, 0},
 349 +  {"TEMPORARY_TABLES", temporary_table_fields_info, create_schema_table,
 350 +   fill_temporary_tables, make_temporary_tables_old_format, 0, 2, 3, 0,
 351 +   OPEN_TABLE_ONLY|OPTIMIZE_I_S_TABLE},
 352    {"TABLE_STATISTICS", table_stats_fields_info, create_schema_table,
 353      fill_schema_table_stats, make_old_format, 0, -1, -1, 0, 0},
 354    {"THREAD_STATISTICS", thread_stats_fields_info, create_schema_table,
 355 --- a/sql/sql_yacc.yy
 356 +++ b/sql/sql_yacc.yy
 357 @@ -10116,6 +10116,15 @@
 358               if (prepare_schema_table(YYTHD, lex, 0, SCH_TABLE_NAMES))
 359                 MYSQL_YYABORT;
 360             }
 361 +         | opt_var_type TEMPORARY TABLES opt_db
 362 +           {
 363 +             LEX *lex= Lex;
 364 +             lex->sql_command= SQLCOM_SHOW_TEMPORARY_TABLES;
 365 +             lex->option_type= $1;
 366 +             lex->select_lex.db= $4;
 367 +             if (prepare_schema_table(YYTHD, lex, 0, SCH_TEMPORARY_TABLES))
 368 +               MYSQL_YYABORT;
 369 +           }
 370           | opt_full TRIGGERS_SYM opt_db wild_and_where
 371             {
 372               LEX *lex= Lex;
 373 --- a/sql/table.h
 374 +++ b/sql/table.h
 375 @@ -954,6 +954,7 @@
 376    SCH_EVENTS,
 377    SCH_FILES,
 378    SCH_GLOBAL_STATUS,
 379 +  SCH_GLOBAL_TEMPORARY_TABLES,
 380    SCH_GLOBAL_VARIABLES,
 381    SCH_KEY_COLUMN_USAGE,
 382    SCH_OPEN_TABLES,
 383 @@ -973,6 +974,7 @@
 384    SCH_TABLE_CONSTRAINTS,
 385    SCH_TABLE_NAMES,
 386    SCH_TABLE_PRIVILEGES,
 387 +  SCH_TEMPORARY_TABLES,
 388    SCH_TABLE_STATS,
 389    SCH_THREAD_STATS,
 390    SCH_TRIGGERS,
 391 --- a/sql/sql_base.cc
 392 +++ b/sql/sql_base.cc
 393 @@ -1459,12 +1459,16 @@
 394        (thd->current_stmt_binlog_row_based && thd->variables.binlog_format == BINLOG_FORMAT_ROW))
 395    {
 396      TABLE *tmp_next;
 397 +
 398 +    pthread_mutex_lock(&thd->LOCK_temporary_tables);
 399      for (table= thd->temporary_tables; table; table= tmp_next)
 400      {
 401        tmp_next= table->next;
 402        close_temporary(table, 1, 1);
 403      }
 404      thd->temporary_tables= 0;
 405 +    pthread_mutex_unlock(&thd->LOCK_temporary_tables);
 406 +
 407      return;
 408    }
 409
 410 @@ -1477,6 +1481,8 @@
 411
 412    memcpy(buf, stub, stub_len);
 413
 414 +  pthread_mutex_lock(&thd->LOCK_temporary_tables);
 415 +
 416    /*
 417      Insertion sort of temp tables by pseudo_thread_id to build ordered list
 418      of sublists of equal pseudo_thread_id
 419 @@ -1581,6 +1587,7 @@
 420    if (!was_quote_show)
 421      thd->options&= ~OPTION_QUOTE_SHOW_CREATE; /* restore option */
 422    thd->temporary_tables=0;
 423 +  pthread_mutex_unlock(&thd->LOCK_temporary_tables);
 424  }
 425
 426  /*
 427 @@ -1883,6 +1890,8 @@
 428    if (table->child_l || table->parent)
 429      detach_merge_children(table, TRUE);
 430
 431 +  pthread_mutex_lock(&thd->LOCK_temporary_tables);
 432 +
 433    if (table->prev)
 434    {
 435      table->prev->next= table->next;
 436 @@ -1909,6 +1918,9 @@
 437      slave_open_temp_tables--;
 438    }
 439    close_temporary(table, free_share, delete_table);
 440 +
 441 +  pthread_mutex_unlock(&thd->LOCK_temporary_tables);
 442 +
 443    DBUG_VOID_RETURN;
 444  }
 445
 446 @@ -5626,6 +5638,7 @@
 447    if (link_in_list)
 448    {
 449      /* growing temp list at the head */
 450 +    pthread_mutex_lock(&thd->LOCK_temporary_tables);
 451      tmp_table->next= thd->temporary_tables;
 452      if (tmp_table->next)
 453        tmp_table->next->prev= tmp_table;
 454 @@ -5633,6 +5646,7 @@
 455      thd->temporary_tables->prev= 0;
 456      if (thd->slave_thread)
 457        slave_open_temp_tables++;
 458 +    pthread_mutex_unlock(&thd->LOCK_temporary_tables);
 459    }
 460    tmp_table->pos_in_table_list= 0;
 461    DBUG_PRINT("tmptable", ("opened table: '%s'.'%s' 0x%lx", tmp_table->s->db.str,
 462 --- a/sql/sql_class.cc
 463 +++ b/sql/sql_class.cc
 464 @@ -733,6 +733,7 @@
 465    active_vio = 0;
 466  #endif
 467    pthread_mutex_init(&LOCK_thd_data, MY_MUTEX_INIT_FAST);
 468 +  pthread_mutex_init(&LOCK_temporary_tables, MY_MUTEX_INIT_FAST);
 469
 470    /* Variables with default values */
 471    proc_info="login";
 472 @@ -1145,6 +1146,7 @@
 473  #endif
 474    mysys_var=0;                                 // Safety (shouldn't be needed)
 475    pthread_mutex_destroy(&LOCK_thd_data);
 476 +  pthread_mutex_destroy(&LOCK_temporary_tables);
 477  #ifndef DBUG_OFF
 478    dbug_sentry= THD_SENTRY_GONE;
 479  #endif
 480 --- a/sql/sql_class.h
 481 +++ b/sql/sql_class.h
 482 @@ -899,6 +899,11 @@
 483    */
 484    TABLE *temporary_tables;
 485    /**
 486 +     Protects temporary_tables.
 487 +  */
 488 +  pthread_mutex_t LOCK_temporary_tables;
 489 +
 490 +  /**
 491      List of tables that were opened with HANDLER OPEN and are
 492      still in use by this thread.
 493    */
 494 --- /dev/null
 495 +++ b/mysql-test/r/percona_show_temp_tables.result
 496 @@ -0,0 +1,58 @@
 497 +drop table if exists t1,t2,t3;
 498 +drop database if exists showtemp;
 499 +create database if not exists showtemp;
 500 +use test;
 501 +create temporary table t1(id int);
 502 +create temporary table t2(id int);
 503 +create temporary table showtemp.t3(id int);
 504 +insert into t1 values(10),(20),(30),(40);
 505 +insert into showtemp.t3 values(999);
 506 +show temporary tables;
 507 +Temp_tables_in_test
 508 +t2
 509 +t1
 510 +show temporary tables from test;
 511 +Temp_tables_in_test
 512 +t2
 513 +t1
 514 +show temporary tables in showtemp;
 515 +Temp_tables_in_showtemp
 516 +t3
 517 +select table_schema, table_name, engine, table_rows from Information_schema.temporary_tables;
 518 +table_schema   table_name      engine  table_rows
 519 +showtemp       t3      MyISAM  1
 520 +test   t2      MyISAM  0
 521 +test   t1      MyISAM  4
 522 +select table_schema, table_name, engine, table_rows from Information_schema.global_temporary_tables;
 523 +table_schema   table_name      engine  table_rows
 524 +showtemp       t3      MyISAM  1
 525 +test   t2      MyISAM  0
 526 +test   t1      MyISAM  4
 527 +select table_schema, table_name, engine, table_rows from Information_schema.global_temporary_tables where table_schema='showtemp';
 528 +table_schema   table_name      engine  table_rows
 529 +showtemp       t3      MyISAM  1
 530 +select table_schema, table_name, engine, table_rows from Information_schema.global_temporary_tables where table_schema='temp';
 531 +table_schema   table_name      engine  table_rows
 532 +drop table if exists showtemp.t2;
 533 +create temporary table t1(id int);
 534 +create temporary table showtemp.t2(id int);
 535 +show temporary tables;
 536 +Temp_tables_in_test
 537 +t1
 538 +select table_schema, table_name, engine, table_rows from Information_schema.global_temporary_tables;
 539 +table_schema   table_name      engine  table_rows
 540 +showtemp       t2      MyISAM  0
 541 +test   t1      MyISAM  0
 542 +showtemp       t3      MyISAM  1
 543 +test   t2      MyISAM  0
 544 +test   t1      MyISAM  4
 545 +drop table showtemp.t2;
 546 +drop table t1;
 547 +select table_schema, table_name, engine, table_rows from Information_schema.global_temporary_tables;
 548 +table_schema   table_name      engine  table_rows
 549 +showtemp       t3      MyISAM  1
 550 +test   t2      MyISAM  0
 551 +test   t1      MyISAM  4
 552 +drop table t1, t2;
 553 +drop table showtemp.t3;
 554 +drop database showtemp;
 555 --- /dev/null
 556 +++ b/mysql-test/t/percona_show_temp_tables.test
 557 @@ -0,0 +1,65 @@
 558 +# Uses GRANT commands that usually disabled in embedded server
 559 +-- source include/not_embedded.inc
 560 +
 561 +# Save the initial number of concurrent sessions
 562 +--source include/count_sessions.inc
 563 +
 564 +#
 565 +# Test of SHOW [GLOBAL] TEMPORARY TABLES [FROM/IN] DB and
 566 +# Information_schema.temporary_tables and global_temporary_tables
 567 +#
 568 +
 569 +connect(stcon1,localhost,root,,test);
 570 +connect(stcon2,localhost,root,,test);
 571 +
 572 +connection stcon1;
 573 +
 574 +--disable_warnings
 575 +drop table if exists t1,t2,t3;
 576 +drop database if exists showtemp;
 577 +create database if not exists showtemp;
 578 +--enable_warnings
 579 +
 580 +use test;
 581 +create temporary table t1(id int);
 582 +create temporary table t2(id int);
 583 +create temporary table showtemp.t3(id int);
 584 +insert into t1 values(10),(20),(30),(40);
 585 +insert into showtemp.t3 values(999);
 586 +
 587 +show temporary tables;
 588 +# "Session" is not same value always. mysql-test cannot test it always.
 589 +#show global temporary tables;
 590 +show temporary tables from test;
 591 +show temporary tables in showtemp;
 592 +select table_schema, table_name, engine, table_rows from Information_schema.temporary_tables;
 593 +select table_schema, table_name, engine, table_rows from Information_schema.global_temporary_tables;
 594 +select table_schema, table_name, engine, table_rows from Information_schema.global_temporary_tables where table_schema='showtemp';
 595 +select table_schema, table_name, engine, table_rows from Information_schema.global_temporary_tables where table_schema='temp';
 596 +
 597 +connection stcon2;
 598 +
 599 +--disable_warnings
 600 +drop table if exists showtemp.t2;
 601 +--enable_warnings
 602 +create temporary table t1(id int);
 603 +create temporary table showtemp.t2(id int);
 604 +show temporary tables;
 605 +select table_schema, table_name, engine, table_rows from Information_schema.global_temporary_tables;
 606 +drop table showtemp.t2;
 607 +drop table t1;
 608 +
 609 +disconnect stcon2;
 610 +
 611 +connection stcon1;
 612 +select table_schema, table_name, engine, table_rows from Information_schema.global_temporary_tables;
 613 +
 614 +drop table t1, t2;
 615 +drop table showtemp.t3;
 616 +drop database showtemp;
 617 +
 618 +connection default;
 619 +disconnect stcon1;
 620 +
 621 +# Wait till all disconnects are completed
 622 +--source include/wait_until_count_sessions.inc