Looks like the diff is in-complete.. but you should get the updated one from here.. https://code.launchpad.net/~mydb08/maria/maria-5.1/+merge/20003 On Tue, Feb 23, 2010 at 1:17 PM, Venu Anuganti <mydb08@gmail.com> wrote:

Venu Anuganti has proposed merging lp:~mydb08/maria/maria-5.1 into lp:maria.

   Requested reviews:    Maria-captains (maria-captains)

Initial SHOW [GLOBAL] TEMPORARY TABLES [FROM/IN db] implementation along with associated Information Schema tables.

Its been a while (probably 6 yrs+) I pushed any code to MySQL, so review is a must even though am working actively on MySQL code for last few years for developing storage engines.

Give me the feedback in case if I missed anything.

Todo:

- ACL check on global temp tables, need an advice on what priv to use - InnoDB has some issues on stat(tmp_file) as its not generating the full path, will work on a fix.

Questions:

Do we need to expose internal temp tables to this implementation ? I don't see any benefit doing so as it should be really part of slow query implementation.

-- https://code.launchpad.net/~mydb08/maria/maria-5.1/+merge/20003 You are the owner of lp:~mydb08/maria/maria-5.1.

=== modified file 'sql/mysqld.cc' --- sql/mysqld.cc       2010-02-11 19:15:24 +0000 +++ sql/mysqld.cc       2010-02-23 21:17:16 +0000 @@ -3272,6 +3272,7 @@   {"show_storage_engines", (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_STORAGE_ENGINES]), SHOW_LONG_STATUS},   {"show_table_status",    (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_TABLE_STATUS]), SHOW_LONG_STATUS},   {"show_tables",          (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_TABLES]), SHOW_LONG_STATUS}, +  {"show_temporary_tables",(char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_TEMPORARY_TABLES]), SHOW_LONG_STATUS},   {"show_triggers",        (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_TRIGGERS]), SHOW_LONG_STATUS},   {"show_variables",       (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_VARIABLES]), SHOW_LONG_STATUS},   {"show_warnings",        (char*) offsetof(STATUS_VAR, com_stat[(uint) SQLCOM_SHOW_WARNS]), SHOW_LONG_STATUS},

=== modified file 'sql/sql_lex.h' --- sql/sql_lex.h       2009-12-03 11:19:05 +0000 +++ sql/sql_lex.h       2010-02-23 21:17:16 +0000 @@ -67,7 +67,7 @@   SQLCOM_UPDATE, SQLCOM_INSERT, SQLCOM_INSERT_SELECT,   SQLCOM_DELETE, SQLCOM_TRUNCATE, SQLCOM_DROP_TABLE, SQLCOM_DROP_INDEX,

-  SQLCOM_SHOW_DATABASES, SQLCOM_SHOW_TABLES, SQLCOM_SHOW_FIELDS, +  SQLCOM_SHOW_DATABASES, SQLCOM_SHOW_TABLES, SQLCOM_SHOW_TEMPORARY_TABLES, SQLCOM_SHOW_FIELDS,   SQLCOM_SHOW_KEYS, SQLCOM_SHOW_VARIABLES, SQLCOM_SHOW_STATUS,   SQLCOM_SHOW_ENGINE_LOGS, SQLCOM_SHOW_ENGINE_STATUS, SQLCOM_SHOW_ENGINE_MUTEX,   SQLCOM_SHOW_PROCESSLIST, SQLCOM_SHOW_MASTER_STAT, SQLCOM_SHOW_SLAVE_STAT,

=== modified file 'sql/sql_parse.cc' --- sql/sql_parse.cc    2010-01-29 10:42:31 +0000 +++ sql/sql_parse.cc    2010-02-23 21:17:16 +0000 @@ -342,6 +342,9 @@    sql_command_flags[SQLCOM_SHOW_TABLES]=       (CF_STATUS_COMMAND |                                                  CF_SHOW_TABLE_COMMAND |                                                  CF_REEXECUTION_FRAGILE); +   sql_command_flags[SQLCOM_SHOW_TEMPORARY_TABLES]=       (CF_STATUS_COMMAND | +                                                 CF_SHOW_TABLE_COMMAND | +                                                 CF_REEXECUTION_FRAGILE);   sql_command_flags[SQLCOM_SHOW_TABLE_STATUS]= (CF_STATUS_COMMAND |                                                 CF_SHOW_TABLE_COMMAND |                                                 CF_REEXECUTION_FRAGILE); @@ -1774,6 +1777,8 @@

  case SCH_TABLE_NAMES:   case SCH_TABLES: +  case SCH_TEMPORARY_TABLES: +  case SCH_GLOBAL_TEMPORARY_TABLES:   case SCH_VIEWS:   case SCH_TRIGGERS:   case SCH_EVENTS: @@ -2262,6 +2267,7 @@   }   case SQLCOM_SHOW_DATABASES:   case SQLCOM_SHOW_TABLES: +  case SQLCOM_SHOW_TEMPORARY_TABLES:   case SQLCOM_SHOW_TRIGGERS:   case SQLCOM_SHOW_TABLE_STATUS:   case SQLCOM_SHOW_OPEN_TABLES: @@ -5354,6 +5360,8 @@

  case SCH_TABLE_NAMES:   case SCH_TABLES: +  case SCH_TEMPORARY_TABLES: +  case SCH_GLOBAL_TEMPORARY_TABLES:   case SCH_VIEWS:   case SCH_TRIGGERS:   case SCH_EVENTS:

=== modified file 'sql/sql_show.cc' --- sql/sql_show.cc     2010-01-15 15:27:55 +0000 +++ sql/sql_show.cc     2010-02-23 21:17:16 +0000 @@ -2716,6 +2716,7 @@     return 0;   case SQLCOM_SHOW_TABLES:   case SQLCOM_SHOW_TABLE_STATUS: +  case SQLCOM_SHOW_TEMPORARY_TABLES:   case SQLCOM_SHOW_TRIGGERS:   case SQLCOM_SHOW_EVENTS:     lookup_field_values->db_value.str= lex->select_lex.db; @@ -3171,6 +3172,212 @@   return (uint) OPEN_FULL_TABLE;  }

+/** +  @brief          Change I_S table item list for SHOW [GLOBAL] TEMPORARY TABLES [FROM/IN db] + +  @param[in]      thd                      thread handler +  @param[in]      schema_table             I_S table + +  @return         Operation status +    @retval       0                        success +    @retval       1                        error +*/ +int make_temporary_tables_old_format(THD *thd, ST_SCHEMA_TABLE *schema_table) +{ +  char tmp[128]; +  String buffer(tmp,sizeof(tmp), thd->charset()); +  LEX *lex= thd->lex; +  Name_resolution_context *context= &lex->select_lex.context; + +  if (thd->lex->option_type == OPT_GLOBAL) { +    ST_FIELD_INFO *field_info= &schema_table->fields_info[0]; +    Item_field *field= new Item_field(context, NullS, NullS, field_info->field_name); +    if (add_item_to_list(thd, field)) +        return 1; +    field->set_name(field_info->old_name, strlen(field_info->old_name), system_charset_info); +  } + +  ST_FIELD_INFO *field_info= &schema_table->fields_info[2]; +  buffer.length(0); +  buffer.append(field_info->old_name); +  buffer.append(lex->select_lex.db); + +  if (lex->wild && lex->wild->ptr()) +  { +    buffer.append(STRING_WITH_LEN(" (")); +    buffer.append(lex->wild->ptr()); +    buffer.append(')'); +  } + +  Item_field *field= new Item_field(context, NullS, NullS, field_info->field_name); +  if (add_item_to_list(thd, field)) +    return 1; + +  field->set_name(buffer.ptr(), buffer.length(), system_charset_info); +  return 0; +} + +/** +  @brief          Fill records for temporary tables by reading info from table object + +  @param[in]      thd                      thread handler +  @param[in]      table                    I_S table +  @param[in]      tmp_table                temporary table +  @param[in]      db                       database name + +  @return         Operation status +    @retval       0                        success +    @retval       1                        error +*/ + +static int store_temporary_table_record(THD *thd, TABLE *table, TABLE *tmp_table, const char *db, bool table_name_only) +{ +  CHARSET_INFO *cs= system_charset_info; +  DBUG_ENTER("store_temporary_table_record"); + +  if (db && my_strcasecmp(cs, db, tmp_table->s->db.str)) +      DBUG_RETURN(0); + +  restore_record(table, s->default_values); + +  //session_id +  table->field[0]->store((longlong) thd->thread_id, TRUE); + +  //database +  table->field[1]->store(tmp_table->s->db.str, tmp_table->s->db.length, cs); + +  //table +  table->field[2]->store(tmp_table->s->table_name.str, tmp_table->s->table_name.length, cs); + +  if (table_name_only) +      DBUG_RETURN(schema_table_store_record(thd, table)); + +  //engine +  handler *handle= tmp_table->file; +  char *engineType = (char *)(handle ? handle->table_type() : "UNKNOWN"); +  table->field[3]->store(engineType, strlen(engineType), cs); + +  //name +  if (tmp_table->s->path.str) { +    char *p=strstr(tmp_table->s->path.str, "#sql"); +    int len=tmp_table->s->path.length-(p-tmp_table->s->path.str); +    table->field[4]->store(p, min(FN_REFLEN, len), cs); +  } + +  // file stats +  handler *file= tmp_table->file; + +  if (file) { + +      MYSQL_TIME time; + +      /** +      TODO: InnoDB stat(file) checks file on short names within data dictionary +            rather than using full path, because of that, temp files created in +            TMPDIR will not have access/create time as it will not find the file + +            The fix is to patch InnoDB to use full path +      */ +      file->info(HA_STATUS_VARIABLE | HA_STATUS_TIME | HA_STATUS_NO_LOCK); + +      table->field[5]->store((longlong) file->stats.records, TRUE); +      table->field[5]->set_notnull(); + +      table->field[6]->store((longlong) file->stats.mean_rec_length, TRUE); +      table->field[7]->store((longlong) file->stats.data_file_length, TRUE); +      table->field[8]->store((longlong) file->stats.index_file_length, TRUE); +      if (file->stats.create_time) +      { +          thd->variables.time_zone->gmt_sec_to_TIME(&time, +              (my_time_t) file->stats.create_time); +          table->field[9]->store_time(&time, MYSQL_TIMESTAMP_DATETIME); +          table->field[9]->set_notnull(); +      } +      if (file->stats.update_time) +      { +          thd->variables.time_zone->gmt_sec_to_TIME(&time, +              (my_time_t) file->stats.update_time); +          table->field[10]->store_time(&time, MYSQL_TIMESTAMP_DATETIME); +          table->field[10]->set_notnull(); +      } +  } + +  DBUG_RETURN(schema_table_store_record(thd, table)); +} + +/** +  @brief          Fill I_S tables with global temporary tables + +  @param[in]      thd                      thread handler +  @param[in]      tables                   I_S table +  @param[in]      cond                     'WHERE' condition + +  @return         Operation status +    @retval       0                        success +    @retval       1                        error +*/ + +static int fill_global_temporary_tables(THD *thd, TABLE_LIST *tables, COND *cond) +{ +  DBUG_ENTER("fill_global_temporary_tables"); + +  pthread_mutex_lock(&LOCK_thread_count); + +  bool table_names_only= (thd->lex->sql_command == SQLCOM_SHOW_TEMPORARY_TABLES) ? 1 : 0; +  I_List_iterator<THD> it(threads); +  THD *thd_item; +  TABLE *tmp; + +  //TODO: ACL check when I_S used directly on global temp tables +  while ((thd_item=it++)) { +    for (tmp=thd_item->temporary_tables; tmp; tmp=tmp->next) { + +        THD *t= tmp->in_use; +        tmp->in_use= thd; + +        if (store_temporary_table_record(thd_item, tables->table, tmp, thd->lex->select_lex.db, table_names_only)) { +            tmp->in_use= t; +            pthread_mutex_unlock(&LOCK_thread_count); +            DBUG_RETURN(1); +        } + +        tmp->in_use= t; +    } +  } + +  pthread_mutex_unlock(&LOCK_thread_count); +  DBUG_RETURN(0); +} + +/** +  @brief          Fill I_S tables with session temporary tables + +  @param[in]      thd                      thread handler +  @param[in]      tables                   I_S table +  @param[in]      cond                     'WHERE' condition + +  @return         Operation status +    @retval       0                        success +    @retval       1                        error +*/ + +int fill_temporary_tables(THD *thd, TABLE_LIST *tables, COND *cond) +{ +  DBUG_ENTER("fill_temporary_tables"); + +  if (thd->lex->option_type == OPT_GLOBAL) +    DBUG_RETURN(fill_global_temporary_tables(thd, tables, cond)); + +  bool table_names_only= (thd->lex->sql_command == SQLCOM_SHOW_TEMPORARY_TABLES) ? 1 : 0; +  TABLE *tmp; + +  for (tmp=thd->temporary_tables; tmp; tmp=tmp->next) { +      if (store_temporary_table_record(thd, tables->table, tmp, thd->lex->select_lex.db, table_names_only)) { +          DBUG_RETURN(1); +      } +  } +  DBUG_RETURN(0); +}

 /**   @brief          Fill I_S table with data from FRM file only @@ -6259,6 +6466,25 @@   {0, 0, MYSQL_TYPE_STRING, 0, 0, 0, SKIP_OPEN_TABLE}  };

+ST_FIELD_INFO temporary_table_fields_info[]= +{ +  {"SESSION_ID", 4, MYSQL_TYPE_LONGLONG, 0, 0, "Session", SKIP_OPEN_TABLE}, +  {"TABLE_SCHEMA", NAME_CHAR_LEN, MYSQL_TYPE_STRING, 0, 0, "Db", SKIP_OPEN_TABLE}, +  {"TABLE_NAME", NAME_CHAR_LEN, MYSQL_TYPE_STRING, 0, 0, "Temp_tables_in_", SKIP_OPEN_TABLE}, +  {"ENGINE", NAME_CHAR_LEN, MYSQL_TYPE_STRING, 0, 0, "Engine", OPEN_FRM_ONLY}, +  {"NAME", FN_REFLEN, MYSQL_TYPE_STRING, 0, 0, "Name", SKIP_OPEN_TABLE}, +  {"TABLE_ROWS", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONGLONG, 0, +   MY_I_S_UNSIGNED, "Rows", OPEN_FULL_TABLE}, +  {"AVG_ROW_LENGTH", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONGLONG, 0, +   MY_I_S_UNSIGNED, "Avg Row", OPEN_FULL_TABLE}, +  {"DATA_LENGTH", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONGLONG, 0, +   MY_I_S_UNSIGNED, "Data Length", OPEN_FULL_TABLE}, +  {"INDEX_LENGTH", MY_INT64_NUM_DECIMAL_DIGITS, MYSQL_TYPE_LONGLONG, 0, +   MY_I_S_UNSIGNED, "Index Size", OPEN_FULL_TABLE}, +  {"CREATE_TIME", 0, MYSQL_TYPE_DATETIME, 0, 1, "Create Time", OPEN_FULL_TABLE}, +  {"UPDATE_TIME", 0, MYSQL_TYPE_DATETIME, 0, 1, "Update Time", OPEN_FULL_TABLE}, +  {0, 0, MYSQL_TYPE_STRING, 0, 0, 0, SKIP_OPEN_TABLE} +};

 ST_FIELD_INFO columns_fields_info[]=  { @@ -6817,6 +7043,9 @@    fill_schema_files, 0, 0, -1, -1, 0, 0},   {"GLOBAL_STATUS", variables_fields_info, create_schema_table,    fill_status, make_old_format, 0, 0, -1, 0, 0}, +  {"GLOBAL_TEMPORARY_TABLES", temporary_table_fields_info, create_schema_table, +   fill_global_temporary_tables, make_temporary_tables_old_format, 0, 2, 3, 0, +   OPEN_TABLE_ONLY|OPTIMIZE_I_S_TABLE},   {"GLOBAL_VARIABLES", variables_fields_info, create_schema_table,    fill_variables, make_old_format, 0, 0, -1, 0, 0},   {"KEY_COLUMN_USAGE", key_column_usage_fields_info, create_schema_table, @@ -6860,6 +7089,9 @@    get_all_tables, make_table_names_old_format, 0, 1, 2, 1, 0},   {"TABLE_PRIVILEGES", table_privileges_fields_info, create_schema_table,    fill_schema_table_privileges, 0, 0, -1, -1, 0, 0}, +  {"TEMPORARY_TABLES", temporary_table_fields_info, create_schema_table, +   fill_temporary_tables, make_temporary_tables_old_format, 0, 2, 3, 0, +   OPEN_TABLE_ONLY|OPTIMIZE_I_S_TABLE},   {"TRIGGERS", triggers_fields_info, create_schema_table,    get_all_tables, make_old_format, get_schema_triggers_record, 5, 6, 0,    OPEN_TABLE_ONLY},

=== modified file 'sql/sql_yacc.yy' --- sql/sql_yacc.yy     2010-01-17 17:22:46 +0000 +++ sql/sql_yacc.yy     2010-02-23 21:17:16 +0000 @@ -10035,6 +10035,15 @@              if (prepare_schema_table(YYTHD, lex, 0, SCH_TABLE_NAMES))                MYSQL_YYABORT;            } +         | opt_var_type TEMPORARY TABLES opt_db +           { +             LEX *lex= Lex; +             lex->sql_command= SQLCOM_SHOW_TEMPORARY_TABLES; +             lex->option_type= $1; +             lex->select_lex.db= $4; +             if (prepare_schema_table(YYTHD, lex, 0, SCH_TEMPORARY_TABLES)) +               MYSQL_YYABORT; +           }          | opt_full TRIGGERS_SYM opt_db wild_and_where            {              LEX *lex= Lex;

=== modified file 'sql/table.h' --- sql/table.h 2010-02-10 19:06:24 +0000 +++ sql/table.h 2010-02-23 21:17:16 +0000 @@ -942,6 +942,7 @@   SCH_FILES,   SCH_GLOBAL_STATUS,   SCH_GLOBAL_VARIABLES, +  SCH_GLOBAL_TEMPORARY_TABLES,   SCH_KEY_COLUMN_USAGE,   SCH_OPEN_TABLES,   SCH_PARTITIONS, @@ -960,6 +961,7 @@   SCH_TABLE_CONSTRAINTS,   SCH_TABLE_NAMES,   SCH_TABLE_PRIVILEGES, +  SCH_TEMPORARY_TABLES,   SCH_TRIGGERS,   SCH_USER_PRIVILEGES,   SCH_VARIABLES,