show_temp.patch

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