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 diff -ruN a/sql/handler.h b/sql/handler.h
   9 --- a/sql/handler.h     2011-04-09 19:11:53.000000000 +0400
  10 +++ b/sql/handler.h     2011-04-10 12:16:43.000000000 +0400
  11 @@ -569,6 +569,7 @@
  12    SCH_EVENTS,
  13    SCH_FILES,
  14    SCH_GLOBAL_STATUS,
  15 +  SCH_GLOBAL_TEMPORARY_TABLES,
  16    SCH_GLOBAL_VARIABLES,
  17    SCH_KEY_COLUMN_USAGE,
  18    SCH_OPEN_TABLES,
  19 @@ -590,6 +591,7 @@
  20    SCH_TABLE_CONSTRAINTS,
  21    SCH_TABLE_NAMES,
  22    SCH_TABLE_PRIVILEGES,
  23 +  SCH_TEMPORARY_TABLES,
  24    SCH_TRIGGERS,
  25    SCH_USER_PRIVILEGES,
  26    SCH_VARIABLES,
  27 diff -ruN a/sql/mysqld.cc b/sql/mysqld.cc
  28 --- a/sql/mysqld.cc     2011-04-09 19:11:52.000000000 +0400
  29 +++ b/sql/mysqld.cc     2011-04-10 12:16:44.000000000 +0400
  30 @@ -3051,6 +3051,7 @@
  31    {"show_storage_engines", (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_STORAGE_ENGINES]), SHOW_LONG_STATUS},
  32    {"show_table_status",    (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_TABLE_STATUS]), SHOW_LONG_STATUS},
  33    {"show_tables",          (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_TABLES]), SHOW_LONG_STATUS},
  34 +  {"show_temporary_tables",(char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_TEMPORARY_TABLES]), SHOW_LONG_STATUS},
  35    {"show_triggers",        (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_TRIGGERS]), SHOW_LONG_STATUS},
  36    {"show_variables",       (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_VARIABLES]), SHOW_LONG_STATUS},
  37    {"show_warnings",        (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_WARNS]), SHOW_LONG_STATUS},
  38 diff -ruN a/sql/sql_lex.h b/sql/sql_lex.h
  39 --- a/sql/sql_lex.h     2011-03-31 17:36:18.000000000 +0400
  40 +++ b/sql/sql_lex.h     2011-04-10 12:22:19.000000000 +0400
  41 @@ -194,6 +194,7 @@
  42      When a command is added here, be sure it's also added in mysqld.cc
  43      in "struct show_var_st status_vars[]= {" ...
  44    */
  45 +  SQLCOM_SHOW_TEMPORARY_TABLES,
  46    /* This should be the last !!! */
  47    SQLCOM_END
  48  };
  49 diff -ruN a/sql/sql_parse.cc b/sql/sql_parse.cc
  50 --- a/sql/sql_parse.cc  2011-03-31 17:36:18.000000000 +0400
  51 +++ b/sql/sql_parse.cc  2011-04-10 12:16:44.000000000 +0400
  52 @@ -348,6 +348,9 @@
  53     sql_command_flags[SQLCOM_SHOW_TABLES]=       (CF_STATUS_COMMAND |
  54                                                   CF_SHOW_TABLE_COMMAND |
  55                                                   CF_REEXECUTION_FRAGILE);
  56 +   sql_command_flags[SQLCOM_SHOW_TEMPORARY_TABLES]=       (CF_STATUS_COMMAND |
  57 +                                                 CF_SHOW_TABLE_COMMAND |
  58 +                                                 CF_REEXECUTION_FRAGILE);
  59    sql_command_flags[SQLCOM_SHOW_TABLE_STATUS]= (CF_STATUS_COMMAND |
  60                                                  CF_SHOW_TABLE_COMMAND |
  61                                                  CF_REEXECUTION_FRAGILE);
  62 @@ -1510,6 +1513,8 @@
  63
  64    case SCH_TABLE_NAMES:
  65    case SCH_TABLES:
  66 +  case SCH_TEMPORARY_TABLES:
  67 +  case SCH_GLOBAL_TEMPORARY_TABLES:
  68    case SCH_VIEWS:
  69    case SCH_TRIGGERS:
  70    case SCH_EVENTS:
  71 @@ -2017,6 +2022,7 @@
  72    }
  73    case SQLCOM_SHOW_DATABASES:
  74    case SQLCOM_SHOW_TABLES:
  75 +  case SQLCOM_SHOW_TEMPORARY_TABLES:
  76    case SQLCOM_SHOW_TRIGGERS:
  77    case SQLCOM_SHOW_TABLE_STATUS:
  78    case SQLCOM_SHOW_OPEN_TABLES:
  79 @@ -4801,6 +4807,8 @@
  80
  81    case SCH_TABLE_NAMES:
  82    case SCH_TABLES:
  83 +  case SCH_TEMPORARY_TABLES:
  84 +  case SCH_GLOBAL_TEMPORARY_TABLES:
  85    case SCH_VIEWS:
  86    case SCH_TRIGGERS:
  87    case SCH_EVENTS:
  88 diff -ruN a/sql/sql_show.cc b/sql/sql_show.cc
  89 --- a/sql/sql_show.cc   2011-04-09 19:11:52.000000000 +0400
  90 +++ b/sql/sql_show.cc   2011-04-10 12:16:44.000000000 +0400
  91 @@ -2693,6 +2693,7 @@
  92      break;
  93    case SQLCOM_SHOW_TABLES:
  94    case SQLCOM_SHOW_TABLE_STATUS:
  95 +  case SQLCOM_SHOW_TEMPORARY_TABLES:
  96    case SQLCOM_SHOW_TRIGGERS:
  97    case SQLCOM_SHOW_EVENTS:
  98      thd->make_lex_string(&lookup_field_values->db_value,
  99 @@ -3181,6 +3182,228 @@
 100    return (uint) OPEN_FULL_TABLE;
 101  }
 102
 103 +/**
 104 +  @brief          Change I_S table item list for SHOW [GLOBAL] TEMPORARY TABLES [FROM/IN db]
 105 +
 106 +  @param[in]      thd                      thread handler
 107 +  @param[in]      schema_table             I_S table
 108 +
 109 +  @return         Operation status
 110 +    @retval       0                        success
 111 +    @retval       1                        error
 112 +*/
 113 +int make_temporary_tables_old_format(THD *thd, ST_SCHEMA_TABLE *schema_table)
 114 +{
 115 +  char tmp[128];
 116 +  String buffer(tmp,sizeof(tmp), thd->charset());
 117 +  LEX *lex= thd->lex;
 118 +  Name_resolution_context *context= &lex->select_lex.context;
 119 +
 120 +  if (thd->lex->option_type == OPT_GLOBAL) {
 121 +    ST_FIELD_INFO *field_info= &schema_table->fields_info[0];
 122 +    Item_field *field= new Item_field(context, NullS, NullS, field_info->field_name);
 123 +    if (add_item_to_list(thd, field))
 124 +      return 1;
 125 +    field->set_name(field_info->old_name, strlen(field_info->old_name), system_charset_info);
 126 +  }
 127 +
 128 +  ST_FIELD_INFO *field_info= &schema_table->fields_info[2];
 129 +  buffer.length(0);
 130 +  buffer.append(field_info->old_name);
 131 +  buffer.append(lex->select_lex.db);
 132 +
 133 +  if (lex->wild && lex->wild->ptr())
 134 +  {
 135 +    buffer.append(STRING_WITH_LEN(" ("));
 136 +    buffer.append(lex->wild->ptr());
 137 +    buffer.append(')');
 138 +  }
 139 +
 140 +  Item_field *field= new Item_field(context, NullS, NullS, field_info->field_name);
 141 +  if (add_item_to_list(thd, field))
 142 +    return 1;
 143 +
 144 +  field->set_name(buffer.ptr(), buffer.length(), system_charset_info);
 145 +  return 0;
 146 +}
 147 +
 148 +/**
 149 +  @brief          Fill records for temporary tables by reading info from table object
 150 +
 151 +  @param[in]      thd                      thread handler
 152 +  @param[in]      table                    I_S table
 153 +  @param[in]      tmp_table                temporary table
 154 +  @param[in]      db                       database name
 155 +
 156 +  @return         Operation status
 157 +    @retval       0                        success
 158 +    @retval       1                        error
 159 +*/
 160 +
 161 +static int store_temporary_table_record(THD *thd, TABLE *table, TABLE *tmp_table, const char *db, bool table_name_only)
 162 +{
 163 +  CHARSET_INFO *cs= system_charset_info;
 164 +  DBUG_ENTER("store_temporary_table_record");
 165 +
 166 +  if (db && my_strcasecmp(cs, db, tmp_table->s->db.str))
 167 +    DBUG_RETURN(0);
 168 +
 169 +  restore_record(table, s->default_values);
 170 +
 171 +  //session_id
 172 +  table->field[0]->store((longlong) thd->thread_id, TRUE);
 173 +
 174 +  //database
 175 +  table->field[1]->store(tmp_table->s->db.str, tmp_table->s->db.length, cs);
 176 +
 177 +  //table
 178 +  table->field[2]->store(tmp_table->s->table_name.str, tmp_table->s->table_name.length, cs);
 179 +
 180 +  if (table_name_only)
 181 +    DBUG_RETURN(schema_table_store_record(thd, table));
 182 +
 183 +  //engine
 184 +  handler *handle= tmp_table->file;
 185 +  char *engineType = (char *)(handle ? handle->table_type() : "UNKNOWN");
 186 +  table->field[3]->store(engineType, strlen(engineType), cs);
 187 +
 188 +  //name
 189 +  if (tmp_table->s->path.str) {
 190 +    char *p=strstr(tmp_table->s->path.str, "#sql");
 191 +    int len=tmp_table->s->path.length-(p-tmp_table->s->path.str);
 192 +    table->field[4]->store(p, min(FN_REFLEN, len), cs);
 193 +  }
 194 +
 195 +  // file stats
 196 +  handler *file= tmp_table->file;
 197 +
 198 +  if (file) {
 199 +
 200 +    MYSQL_TIME time;
 201 +
 202 +    /**
 203 +        TODO: InnoDB stat(file) checks file on short names within data dictionary
 204 +        rather than using full path, because of that, temp files created in
 205 +        TMPDIR will not have access/create time as it will not find the file
 206 +
 207 +        The fix is to patch InnoDB to use full path
 208 +    */
 209 +    file->info(HA_STATUS_VARIABLE | HA_STATUS_TIME | HA_STATUS_NO_LOCK);
 210 +
 211 +    table->field[5]->store((longlong) file->stats.records, TRUE);
 212 +    table->field[5]->set_notnull();
 213 +
 214 +    table->field[6]->store((longlong) file->stats.mean_rec_length, TRUE);
 215 +    table->field[7]->store((longlong) file->stats.data_file_length, TRUE);
 216 +    table->field[8]->store((longlong) file->stats.index_file_length, TRUE);
 217 +    if (file->stats.create_time)
 218 +    {
 219 +      thd->variables.time_zone->gmt_sec_to_TIME(&time,
 220 +                                                (my_time_t) file->stats.create_time);
 221 +      table->field[9]->store_time(&time, MYSQL_TIMESTAMP_DATETIME);
 222 +      table->field[9]->set_notnull();
 223 +    }
 224 +    if (file->stats.update_time)
 225 +    {
 226 +      thd->variables.time_zone->gmt_sec_to_TIME(&time,
 227 +                                                (my_time_t) file->stats.update_time);
 228 +      table->field[10]->store_time(&time, MYSQL_TIMESTAMP_DATETIME);
 229 +      table->field[10]->set_notnull();
 230 +    }
 231 +  }
 232 +
 233 +  DBUG_RETURN(schema_table_store_record(thd, table));
 234 +}
 235 +
 236 +/**
 237 +  @brief          Fill I_S tables with global temporary tables
 238 +
 239 +  @param[in]      thd                      thread handler
 240 +  @param[in]      tables                   I_S table
 241 +  @param[in]      cond                     'WHERE' condition
 242 +
 243 +  @return         Operation status
 244 +    @retval       0                        success
 245 +    @retval       1                        error
 246 +*/
 247 +
 248 +static int fill_global_temporary_tables(THD *thd, TABLE_LIST *tables, COND *cond)
 249 +{
 250 +  DBUG_ENTER("fill_global_temporary_tables");
 251 +
 252 +  mysql_mutex_lock(&LOCK_thread_count);
 253 +
 254 +  bool table_names_only= (thd->lex->sql_command == SQLCOM_SHOW_TEMPORARY_TABLES) ? 1 : 0;
 255 +  I_List_iterator<THD> it(threads);
 256 +  THD *thd_item;
 257 +  TABLE *tmp;
 258 +
 259 +#ifndef NO_EMBEDDED_ACCESS_CHECKS
 260 +  Security_context *sctx= thd->security_ctx;
 261 +  uint db_access;
 262 +#endif
 263 +
 264 +  while ((thd_item=it++)) {
 265 +    for (tmp=thd_item->temporary_tables; tmp; tmp=tmp->next) {
 266 +
 267 +#ifndef NO_EMBEDDED_ACCESS_CHECKS
 268 +      if (test_all_bits(sctx->master_access, DB_ACLS))
 269 +        db_access=DB_ACLS;
 270 +      else
 271 +        db_access= (acl_get(sctx->host, sctx->ip, sctx->priv_user, tmp->s->db.str, 0) | sctx->master_access);
 272 +
 273 +      if (!(db_access & DB_ACLS) && check_grant_db(thd,tmp->s->db.str)) {
 274 +        //no access for temp tables within this db for user
 275 +        continue;
 276 +      }
 277 +#endif
 278 +
 279 +      THD *t= tmp->in_use;
 280 +      tmp->in_use= thd;
 281 +
 282 +      if (store_temporary_table_record(thd_item, tables->table, tmp, thd->lex->select_lex.db, table_names_only)) {
 283 +        tmp->in_use= t;
 284 +        mysql_mutex_unlock(&LOCK_thread_count);
 285 +        DBUG_RETURN(1);
 286 +      }
 287 +
 288 +      tmp->in_use= t;
 289 +    }
 290 +  }
 291 +
 292 +  mysql_mutex_unlock(&LOCK_thread_count);
 293 +  DBUG_RETURN(0);
 294 +}
 295 +
 296 +/**
 297 +  @brief          Fill I_S tables with session temporary tables
 298 +
 299 +  @param[in]      thd                      thread handler
 300 +  @param[in]      tables                   I_S table
 301 +  @param[in]      cond                     'WHERE' condition
 302 +
 303 +  @return         Operation status
 304 +    @retval       0                        success
 305 +    @retval       1                        error
 306 +*/
 307 +
 308 +int fill_temporary_tables(THD *thd, TABLE_LIST *tables, COND *cond)
 309 +{
 310 +  DBUG_ENTER("fill_temporary_tables");
 311 +
 312 +  if (thd->lex->option_type == OPT_GLOBAL)
 313 +    DBUG_RETURN(fill_global_temporary_tables(thd, tables, cond));
 314 +
 315 +  bool table_names_only= (thd->lex->sql_command == SQLCOM_SHOW_TEMPORARY_TABLES) ? 1 : 0;
 316 +  TABLE *tmp;
 317 +
 318 +  for (tmp=thd->temporary_tables; tmp; tmp=tmp->next) {
 319 +    if (store_temporary_table_record(thd, tables->table, tmp, thd->lex->select_lex.db, table_names_only)) {
 320 +      DBUG_RETURN(1);
 321 +    }
 322 +  }
 323 +  DBUG_RETURN(0);
 324 +}
 325
 326  /**
 327     Try acquire high priority share metadata lock on a table (with
 328 @@ -6991,6 +7214,25 @@
 329    {0, 0, MYSQL_TYPE_STRING, 0, 0, 0, SKIP_OPEN_TABLE}
 330  };
 331
 332 +ST_FIELD_INFO temporary_table_fields_info[]=
 333 +{
 334 +  {"SESSION_ID", 4, MYSQL_TYPE_LONGLONG, 0, 0, "Session", SKIP_OPEN_TABLE},
 335 +  {"TABLE_SCHEMA", NAME_CHAR_LEN, MYSQL_TYPE_STRING, 0, 0, "Db", SKIP_OPEN_TABLE},
 336 +  {"TABLE_NAME", NAME_CHAR_LEN, MYSQL_TYPE_STRING, 0, 0, "Temp_tables_in_", SKIP_OPEN_TABLE},
 337 +  {"ENGINE", NAME_CHAR_LEN, MYSQL_TYPE_STRING, 0, 0, "Engine", OPEN_FRM_ONLY},
 338 +  {"NAME", FN_REFLEN, MYSQL_TYPE_STRING, 0, 0, "Name", SKIP_OPEN_TABLE},
 339 +  {"TABLE_ROWS", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONGLONG, 0,
 340 +   MY_I_S_UNSIGNED, "Rows", OPEN_FULL_TABLE},
 341 +  {"AVG_ROW_LENGTH", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONGLONG, 0,
 342 +   MY_I_S_UNSIGNED, "Avg Row", OPEN_FULL_TABLE},
 343 +  {"DATA_LENGTH", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONGLONG, 0,
 344 +   MY_I_S_UNSIGNED, "Data Length", OPEN_FULL_TABLE},
 345 +  {"INDEX_LENGTH", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONGLONG, 0,
 346 +   MY_I_S_UNSIGNED, "Index Size", OPEN_FULL_TABLE},
 347 +  {"CREATE_TIME", 0, MYSQL_TYPE_DATETIME, 0, 1, "Create Time", OPEN_FULL_TABLE},
 348 +  {"UPDATE_TIME", 0, MYSQL_TYPE_DATETIME, 0, 1, "Update Time", OPEN_FULL_TABLE},
 349 +  {0, 0, MYSQL_TYPE_STRING, 0, 0, 0, SKIP_OPEN_TABLE}
 350 +};
 351
 352  ST_FIELD_INFO columns_fields_info[]=
 353  {
 354 @@ -7605,6 +7847,9 @@
 355     hton_fill_schema_table, 0, 0, -1, -1, 0, 0},
 356    {"GLOBAL_STATUS", variables_fields_info, create_schema_table,
 357     fill_status, make_old_format, 0, 0, -1, 0, 0},
 358 +  {"GLOBAL_TEMPORARY_TABLES", temporary_table_fields_info, create_schema_table,
 359 +   fill_global_temporary_tables, make_temporary_tables_old_format, 0, 2, 3, 0,
 360 +   OPEN_TABLE_ONLY|OPTIMIZE_I_S_TABLE},
 361    {"GLOBAL_VARIABLES", variables_fields_info, create_schema_table,
 362     fill_variables, make_old_format, 0, 0, -1, 0, 0},
 363    {"KEY_COLUMN_USAGE", key_column_usage_fields_info, create_schema_table,
 364 @@ -7654,6 +7899,9 @@
 365     get_all_tables, make_table_names_old_format, 0, 1, 2, 1, 0},
 366    {"TABLE_PRIVILEGES", table_privileges_fields_info, create_schema_table,
 367     fill_schema_table_privileges, 0, 0, -1, -1, 0, 0},
 368 +  {"TEMPORARY_TABLES", temporary_table_fields_info, create_schema_table,
 369 +   fill_temporary_tables, make_temporary_tables_old_format, 0, 2, 3, 0,
 370 +   OPEN_TABLE_ONLY|OPTIMIZE_I_S_TABLE},
 371    {"TRIGGERS", triggers_fields_info, create_schema_table,
 372     get_all_tables, make_old_format, get_schema_triggers_record, 5, 6, 0,
 373     OPEN_TRIGGER_ONLY|OPTIMIZE_I_S_TABLE},
 374 diff -ruN a/sql/sql_yacc.yy b/sql/sql_yacc.yy
 375 --- a/sql/sql_yacc.yy   2011-03-31 17:36:18.000000000 +0400
 376 +++ b/sql/sql_yacc.yy   2011-04-10 12:16:43.000000000 +0400
 377 @@ -10869,6 +10869,15 @@
 378               if (prepare_schema_table(YYTHD, lex, 0, SCH_TABLE_NAMES))
 379                 MYSQL_YYABORT;
 380             }
 381 +         | opt_var_type TEMPORARY TABLES opt_db
 382 +           {
 383 +             LEX *lex= Lex;
 384 +             lex->sql_command= SQLCOM_SHOW_TEMPORARY_TABLES;
 385 +             lex->option_type= $1;
 386 +             lex->select_lex.db= $4;
 387 +             if (prepare_schema_table(YYTHD, lex, 0, SCH_TEMPORARY_TABLES))
 388 +               MYSQL_YYABORT;
 389 +           }
 390           | opt_full TRIGGERS_SYM opt_db wild_and_where
 391             {
 392               LEX *lex= Lex;