1 # name : show_temp.patch
2 # introduced : 11 or before
3 # maintainer : Yasufumi
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
15 + SCH_GLOBAL_TEMPORARY_TABLES,
20 SCH_TABLE_CONSTRAINTS,
23 + SCH_TEMPORARY_TABLES,
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
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
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
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);
67 + case SCH_TEMPORARY_TABLES:
68 + case SCH_GLOBAL_TEMPORARY_TABLES:
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:
84 + case SCH_TEMPORARY_TABLES:
85 + case SCH_GLOBAL_TEMPORARY_TABLES:
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
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 @@ -3174,6 +3175,228 @@
101 return (uint) OPEN_FULL_TABLE;
105 + @brief Change I_S table item list for SHOW [GLOBAL] TEMPORARY TABLES [FROM/IN db]
107 + @param[in] thd thread handler
108 + @param[in] schema_table I_S table
110 + @return Operation status
114 +int make_temporary_tables_old_format(THD *thd, ST_SCHEMA_TABLE *schema_table)
117 + String buffer(tmp,sizeof(tmp), thd->charset());
118 + LEX *lex= thd->lex;
119 + Name_resolution_context *context= &lex->select_lex.context;
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))
126 + field->set_name(field_info->old_name, strlen(field_info->old_name), system_charset_info);
129 + ST_FIELD_INFO *field_info= &schema_table->fields_info[2];
131 + buffer.append(field_info->old_name);
132 + buffer.append(lex->select_lex.db);
134 + if (lex->wild && lex->wild->ptr())
136 + buffer.append(STRING_WITH_LEN(" ("));
137 + buffer.append(lex->wild->ptr());
138 + buffer.append(')');
141 + Item_field *field= new Item_field(context, NullS, NullS, field_info->field_name);
142 + if (add_item_to_list(thd, field))
145 + field->set_name(buffer.ptr(), buffer.length(), system_charset_info);
150 + @brief Fill records for temporary tables by reading info from table object
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
157 + @return Operation status
162 +static int store_temporary_table_record(THD *thd, TABLE *table, TABLE *tmp_table, const char *db, bool table_name_only)
164 + CHARSET_INFO *cs= system_charset_info;
165 + DBUG_ENTER("store_temporary_table_record");
167 + if (db && my_strcasecmp(cs, db, tmp_table->s->db.str))
170 + restore_record(table, s->default_values);
173 + table->field[0]->store((longlong) thd->thread_id, TRUE);
176 + table->field[1]->store(tmp_table->s->db.str, tmp_table->s->db.length, cs);
179 + table->field[2]->store(tmp_table->s->table_name.str, tmp_table->s->table_name.length, cs);
181 + if (table_name_only)
182 + DBUG_RETURN(schema_table_store_record(thd, table));
185 + handler *handle= tmp_table->file;
186 + char *engineType = (char *)(handle ? handle->table_type() : "UNKNOWN");
187 + table->field[3]->store(engineType, strlen(engineType), cs);
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);
197 + handler *file= tmp_table->file;
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
208 + The fix is to patch InnoDB to use full path
210 + file->info(HA_STATUS_VARIABLE | HA_STATUS_TIME | HA_STATUS_NO_LOCK);
212 + table->field[5]->store((longlong) file->stats.records, TRUE);
213 + table->field[5]->set_notnull();
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)
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();
225 + if (file->stats.update_time)
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();
234 + DBUG_RETURN(schema_table_store_record(thd, table));
238 + @brief Fill I_S tables with global temporary tables
240 + @param[in] thd thread handler
241 + @param[in] tables I_S table
242 + @param[in] cond 'WHERE' condition
244 + @return Operation status
249 +static int fill_global_temporary_tables(THD *thd, TABLE_LIST *tables, COND *cond)
251 + DBUG_ENTER("fill_global_temporary_tables");
253 + mysql_mutex_lock(&LOCK_thread_count);
255 + bool table_names_only= (thd->lex->sql_command == SQLCOM_SHOW_TEMPORARY_TABLES) ? 1 : 0;
256 + I_List_iterator<THD> it(threads);
260 +#ifndef NO_EMBEDDED_ACCESS_CHECKS
261 + Security_context *sctx= thd->security_ctx;
265 + while ((thd_item=it++)) {
266 + for (tmp=thd_item->temporary_tables; tmp; tmp=tmp->next) {
268 +#ifndef NO_EMBEDDED_ACCESS_CHECKS
269 + if (test_all_bits(sctx->master_access, DB_ACLS))
272 + db_access= (acl_get(sctx->host, sctx->ip, sctx->priv_user, tmp->s->db.str, 0) | sctx->master_access);
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
280 + THD *t= tmp->in_use;
283 + if (store_temporary_table_record(thd_item, tables->table, tmp, thd->lex->select_lex.db, table_names_only)) {
285 + mysql_mutex_unlock(&LOCK_thread_count);
293 + mysql_mutex_unlock(&LOCK_thread_count);
298 + @brief Fill I_S tables with session temporary tables
300 + @param[in] thd thread handler
301 + @param[in] tables I_S table
302 + @param[in] cond 'WHERE' condition
304 + @return Operation status
309 +int fill_temporary_tables(THD *thd, TABLE_LIST *tables, COND *cond)
311 + DBUG_ENTER("fill_temporary_tables");
313 + if (thd->lex->option_type == OPT_GLOBAL)
314 + DBUG_RETURN(fill_global_temporary_tables(thd, tables, cond));
316 + bool table_names_only= (thd->lex->sql_command == SQLCOM_SHOW_TEMPORARY_TABLES) ? 1 : 0;
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)) {
328 Try acquire high priority share metadata lock on a table (with
329 @@ -6850,6 +7073,25 @@
330 {0, 0, MYSQL_TYPE_STRING, 0, 0, 0, SKIP_OPEN_TABLE}
333 +ST_FIELD_INFO temporary_table_fields_info[]=
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}
353 ST_FIELD_INFO columns_fields_info[]=
355 @@ -7464,6 +7706,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 @@ -7513,6 +7758,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))
382 + | opt_var_type TEMPORARY TABLES opt_db
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))
391 | opt_full TRIGGERS_SYM opt_db wild_and_where