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 2011-04-09 19:11:53.000000000 +0400
10 +++ b/sql/handler.h 2011-04-10 12:16:43.000000000 +0400
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 2011-04-09 19:11:52.000000000 +0400
29 +++ b/sql/mysqld.cc 2011-04-10 12:16:44.000000000 +0400
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
42 When a command is added here, be sure it's also added in mysqld.cc
43 in "struct show_var_st status_vars[]= {" ...
45 + SQLCOM_SHOW_TEMPORARY_TABLES,
46 /* This should be the last !!! */
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
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);
66 + case SCH_TEMPORARY_TABLES:
67 + case SCH_GLOBAL_TEMPORARY_TABLES:
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:
83 + case SCH_TEMPORARY_TABLES:
84 + case SCH_GLOBAL_TEMPORARY_TABLES:
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
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 @@ -3174,6 +3175,228 @@
100 return (uint) OPEN_FULL_TABLE;
104 + @brief Change I_S table item list for SHOW [GLOBAL] TEMPORARY TABLES [FROM/IN db]
106 + @param[in] thd thread handler
107 + @param[in] schema_table I_S table
109 + @return Operation status
113 +int make_temporary_tables_old_format(THD *thd, ST_SCHEMA_TABLE *schema_table)
116 + String buffer(tmp,sizeof(tmp), thd->charset());
117 + LEX *lex= thd->lex;
118 + Name_resolution_context *context= &lex->select_lex.context;
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))
125 + field->set_name(field_info->old_name, strlen(field_info->old_name), system_charset_info);
128 + ST_FIELD_INFO *field_info= &schema_table->fields_info[2];
130 + buffer.append(field_info->old_name);
131 + buffer.append(lex->select_lex.db);
133 + if (lex->wild && lex->wild->ptr())
135 + buffer.append(STRING_WITH_LEN(" ("));
136 + buffer.append(lex->wild->ptr());
137 + buffer.append(')');
140 + Item_field *field= new Item_field(context, NullS, NullS, field_info->field_name);
141 + if (add_item_to_list(thd, field))
144 + field->set_name(buffer.ptr(), buffer.length(), system_charset_info);
149 + @brief Fill records for temporary tables by reading info from table object
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
156 + @return Operation status
161 +static int store_temporary_table_record(THD *thd, TABLE *table, TABLE *tmp_table, const char *db, bool table_name_only)
163 + CHARSET_INFO *cs= system_charset_info;
164 + DBUG_ENTER("store_temporary_table_record");
166 + if (db && my_strcasecmp(cs, db, tmp_table->s->db.str))
169 + restore_record(table, s->default_values);
172 + table->field[0]->store((longlong) thd->thread_id, TRUE);
175 + table->field[1]->store(tmp_table->s->db.str, tmp_table->s->db.length, cs);
178 + table->field[2]->store(tmp_table->s->table_name.str, tmp_table->s->table_name.length, cs);
180 + if (table_name_only)
181 + DBUG_RETURN(schema_table_store_record(thd, table));
184 + handler *handle= tmp_table->file;
185 + char *engineType = (char *)(handle ? handle->table_type() : "UNKNOWN");
186 + table->field[3]->store(engineType, strlen(engineType), cs);
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);
196 + handler *file= tmp_table->file;
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
207 + The fix is to patch InnoDB to use full path
209 + file->info(HA_STATUS_VARIABLE | HA_STATUS_TIME | HA_STATUS_NO_LOCK);
211 + table->field[5]->store((longlong) file->stats.records, TRUE);
212 + table->field[5]->set_notnull();
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)
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();
224 + if (file->stats.update_time)
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();
233 + DBUG_RETURN(schema_table_store_record(thd, table));
237 + @brief Fill I_S tables with global temporary tables
239 + @param[in] thd thread handler
240 + @param[in] tables I_S table
241 + @param[in] cond 'WHERE' condition
243 + @return Operation status
248 +static int fill_global_temporary_tables(THD *thd, TABLE_LIST *tables, COND *cond)
250 + DBUG_ENTER("fill_global_temporary_tables");
252 + mysql_mutex_lock(&LOCK_thread_count);
254 + bool table_names_only= (thd->lex->sql_command == SQLCOM_SHOW_TEMPORARY_TABLES) ? 1 : 0;
255 + I_List_iterator<THD> it(threads);
259 +#ifndef NO_EMBEDDED_ACCESS_CHECKS
260 + Security_context *sctx= thd->security_ctx;
264 + while ((thd_item=it++)) {
265 + for (tmp=thd_item->temporary_tables; tmp; tmp=tmp->next) {
267 +#ifndef NO_EMBEDDED_ACCESS_CHECKS
268 + if (test_all_bits(sctx->master_access, DB_ACLS))
271 + db_access= (acl_get(sctx->host, sctx->ip, sctx->priv_user, tmp->s->db.str, 0) | sctx->master_access);
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
279 + THD *t= tmp->in_use;
282 + if (store_temporary_table_record(thd_item, tables->table, tmp, thd->lex->select_lex.db, table_names_only)) {
284 + mysql_mutex_unlock(&LOCK_thread_count);
292 + mysql_mutex_unlock(&LOCK_thread_count);
297 + @brief Fill I_S tables with session temporary tables
299 + @param[in] thd thread handler
300 + @param[in] tables I_S table
301 + @param[in] cond 'WHERE' condition
303 + @return Operation status
308 +int fill_temporary_tables(THD *thd, TABLE_LIST *tables, COND *cond)
310 + DBUG_ENTER("fill_temporary_tables");
312 + if (thd->lex->option_type == OPT_GLOBAL)
313 + DBUG_RETURN(fill_global_temporary_tables(thd, tables, cond));
315 + bool table_names_only= (thd->lex->sql_command == SQLCOM_SHOW_TEMPORARY_TABLES) ? 1 : 0;
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)) {
327 Try acquire high priority share metadata lock on a table (with
328 @@ -6850,6 +7073,25 @@
329 {0, 0, MYSQL_TYPE_STRING, 0, 0, 0, SKIP_OPEN_TABLE}
332 +ST_FIELD_INFO temporary_table_fields_info[]=
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}
352 ST_FIELD_INFO columns_fields_info[]=
354 @@ -7464,6 +7706,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 @@ -7513,6 +7758,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))
381 + | opt_var_type TEMPORARY TABLES opt_db
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))
390 | opt_full TRIGGERS_SYM opt_db wild_and_where