# name : show_temp.patch # introduced : 11 or before # maintainer : Yasufumi # #!!! notice !!! # Any small change to this file in the main branch # should be done or reviewed by the maintainer! diff -ruN a/sql/handler.h b/sql/handler.h --- a/sql/handler.h 2010-12-03 14:09:14.406955791 +0900 +++ b/sql/handler.h 2010-12-03 14:29:16.533356953 +0900 @@ -569,6 +569,7 @@ SCH_EVENTS, SCH_FILES, SCH_GLOBAL_STATUS, + SCH_GLOBAL_TEMPORARY_TABLES, SCH_GLOBAL_VARIABLES, SCH_KEY_COLUMN_USAGE, SCH_OPEN_TABLES, @@ -590,6 +591,7 @@ SCH_TABLE_CONSTRAINTS, SCH_TABLE_NAMES, SCH_TABLE_PRIVILEGES, + SCH_TEMPORARY_TABLES, SCH_TRIGGERS, SCH_USER_PRIVILEGES, SCH_VARIABLES, diff -ruN a/sql/mysqld.cc b/sql/mysqld.cc --- a/sql/mysqld.cc 2010-12-02 21:23:05.495293844 +0900 +++ b/sql/mysqld.cc 2010-12-03 14:25:40.317039327 +0900 @@ -3033,6 +3033,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}, diff -ruN a/sql/sql_lex.h b/sql/sql_lex.h --- a/sql/sql_lex.h 2010-12-02 19:22:40.040023288 +0900 +++ b/sql/sql_lex.h 2010-12-03 14:09:53.465292483 +0900 @@ -186,7 +186,7 @@ SQLCOM_CREATE_EVENT, SQLCOM_ALTER_EVENT, SQLCOM_DROP_EVENT, SQLCOM_SHOW_CREATE_EVENT, SQLCOM_SHOW_EVENTS, SQLCOM_SHOW_CREATE_TRIGGER, - SQLCOM_ALTER_DB_UPGRADE, + SQLCOM_ALTER_DB_UPGRADE, SQLCOM_SHOW_TEMPORARY_TABLES, SQLCOM_SHOW_PROFILE, SQLCOM_SHOW_PROFILES, SQLCOM_SIGNAL, SQLCOM_RESIGNAL, SQLCOM_SHOW_RELAYLOG_EVENTS, diff -ruN a/sql/sql_parse.cc b/sql/sql_parse.cc --- a/sql/sql_parse.cc 2010-12-02 19:22:40.046023936 +0900 +++ b/sql/sql_parse.cc 2010-12-03 14:09:53.471950455 +0900 @@ -349,6 +349,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); @@ -1511,6 +1514,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: @@ -2018,6 +2023,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: @@ -4807,6 +4813,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: diff -ruN a/sql/sql_show.cc b/sql/sql_show.cc --- a/sql/sql_show.cc 2010-12-03 13:38:47.493070606 +0900 +++ b/sql/sql_show.cc 2010-12-03 14:27:04.590939717 +0900 @@ -2685,6 +2685,7 @@ break; case SQLCOM_SHOW_TABLES: case SQLCOM_SHOW_TABLE_STATUS: + case SQLCOM_SHOW_TEMPORARY_TABLES: case SQLCOM_SHOW_TRIGGERS: case SQLCOM_SHOW_EVENTS: thd->make_lex_string(&lookup_field_values->db_value, @@ -3173,6 +3174,228 @@ 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"); + + mysql_mutex_lock(&LOCK_thread_count); + + bool table_names_only= (thd->lex->sql_command == SQLCOM_SHOW_TEMPORARY_TABLES) ? 1 : 0; + I_List_iterator it(threads); + THD *thd_item; + TABLE *tmp; + +#ifndef NO_EMBEDDED_ACCESS_CHECKS + Security_context *sctx= thd->security_ctx; + uint db_access; +#endif + + while ((thd_item=it++)) { + for (tmp=thd_item->temporary_tables; tmp; tmp=tmp->next) { + +#ifndef NO_EMBEDDED_ACCESS_CHECKS + if (test_all_bits(sctx->master_access, DB_ACLS)) + db_access=DB_ACLS; + else + db_access= (acl_get(sctx->host, sctx->ip, sctx->priv_user, tmp->s->db.str, 0) | sctx->master_access); + + if (!(db_access & DB_ACLS) && check_grant_db(thd,tmp->s->db.str)) { + //no access for temp tables within this db for user + continue; + } +#endif + + 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; + mysql_mutex_unlock(&LOCK_thread_count); + DBUG_RETURN(1); + } + + tmp->in_use= t; + } + } + + mysql_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); +} /** Try acquire high priority share metadata lock on a table (with @@ -6849,6 +7072,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[]= { @@ -7463,6 +7705,9 @@ hton_fill_schema_table, 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, @@ -7512,6 +7757,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_TRIGGER_ONLY|OPTIMIZE_I_S_TABLE}, diff -ruN a/sql/sql_yacc.yy b/sql/sql_yacc.yy --- a/sql/sql_yacc.yy 2010-12-02 19:22:40.077024170 +0900 +++ b/sql/sql_yacc.yy 2010-12-03 14:09:53.496023791 +0900 @@ -10869,6 +10869,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;