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