From: Antony T Curtis Date: December 14 2000 3:28pm Subject: Re: Querying open tables List-Archive: http://lists.mysql.com/internals/219 Message-Id: <3A38E725.64F10A4B@abacus.co.uk> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="------------8BAD49D3400D7FB9940BF196" --------------8BAD49D3400D7FB9940BF196 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Michael Widenius wrote: > > Hi! > > >>>>> "Antony" == Antony T Curtis writes: > > Antony> Is there any conveinent mechanism in place to query which tables are > Antony> open? > > Not for the moment; It would however be almost trivial to implement > 'show open tables' ; A shall put this on our TODO ! I have implemented this in the attached diff -- ANTONY T CURTIS Tel: +44 (1635) 36222 Abacus Polar Holdings Ltd Fax: +44 (1635) 38670 > When we are planning for posterity, we ought to remember that virtue is > not hereditary. >  -- Thomas Paine --------------8BAD49D3400D7FB9940BF196 Content-Type: text/plain; charset=us-ascii; name="open_tables.diff" Content-Transfer-Encoding: 7bit Content-Disposition: inline; filename="open_tables.diff" diff -rC2 mysql-3.23.28-gamma/sql/lex.h mysql-3.23.28-gamma2/sql/lex.h *** mysql-3.23.28-gamma/sql/lex.h Wed Nov 22 11:58:58 2000 --- mysql-3.23.28-gamma2/sql/lex.h Thu Dec 14 12:43:00 2000 *************** *** 224,227 **** --- 224,228 ---- { "NULL", SYM(NULL_SYM),0,0}, { "ON", SYM(ON),0,0}, + { "OPEN", SYM(OPEN_SYM),0,0}, { "OPTIMIZE", SYM(OPTIMIZE),0,0}, { "OPTION", SYM(OPTION),0,0}, diff -rC2 mysql-3.23.28-gamma/sql/mysql_priv.h mysql-3.23.28-gamma2/sql/mysql_priv.h *** mysql-3.23.28-gamma/sql/mysql_priv.h Wed Nov 22 11:59:00 2000 --- mysql-3.23.28-gamma2/sql/mysql_priv.h Thu Dec 14 14:50:10 2000 *************** *** 345,348 **** --- 345,349 ---- /* sql_list.c */ int mysqld_show_dbs(THD *thd,const char *wild); + int mysqld_show_open_tables(THD *thd,const char *db,const char *wild); int mysqld_show_tables(THD *thd,const char *db,const char *wild); int mysqld_extend_show_tables(THD *thd,const char *db,const char *wild); *************** *** 407,410 **** --- 408,413 ---- int fill_record(List &fields,List &values); int fill_record(Field **field,List &values); + int list_open_tables(THD *thd,List *files, const char *db,const char *wild); + char* query_table_status(THD *thd,const char *db,const char *table_name); /* sql_calc.cc */ diff -rC2 mysql-3.23.28-gamma/sql/sql_base.cc mysql-3.23.28-gamma2/sql/sql_base.cc *** mysql-3.23.28-gamma/sql/sql_base.cc Wed Nov 22 11:58:58 2000 --- mysql-3.23.28-gamma2/sql/sql_base.cc Thu Dec 14 14:51:17 2000 *************** *** 113,116 **** --- 113,184 ---- #endif + int list_open_tables(THD *thd,List *tables, const char *db,const char *wild) + { + int result = 0, idx; + uint col_access=thd->col_access; + TABLE_LIST table_list; + DBUG_ENTER("list_open_tables"); + VOID(pthread_mutex_lock(&LOCK_open)); + bzero((char*) &table_list,sizeof(table_list)); + + for (idx=0 ; result == 0 && idx < open_cache.records; idx++) + { + TABLE *entry=(TABLE*) hash_element(&open_cache,idx); + if ((!entry->real_name) || strcmp(entry->table_cache_key,db)) + continue; + if (wild && wild[0] && wild_compare(entry->real_name,wild)) + continue; + if (db && !(col_access & TABLE_ACLS)) + { + table_list.db= (char*) db; + table_list.real_name= entry->real_name;/*real name*/ + table_list.grant.privilege=col_access; + if (check_grant(thd,TABLE_ACLS,&table_list,1)) + continue; + } + /* need to check if he haven't already listed it */ + + List_iterator it(*tables); + char *table_name; + int check = 0; + while (check == 0 && (table_name=it++)) + { + if (!strcmp(table_name,entry->real_name)) + check++; + } + if (check) + continue; + + if (tables->push_back(thd->strdup(entry->real_name))) + { + result = -1; + } + } + + VOID(pthread_mutex_unlock(&LOCK_open)); + DBUG_RETURN(result); + } + + char* + query_table_status(THD *thd,const char *db,const char *table_name) + { + int cached = 0, in_use = 0; + char info[256]; + + for (idx=0 ; idx < open_cache.records; idx++) + { + TABLE *entry=(TABLE*) hash_element(&open_cache,idx); + if (strcmp(entry->table_cache_key,db) || + strcmp(entry->real_name,table_name)) + continue; + + cached++; + if (entry->in_use) + in_use++; + } + + sprintf(info, "cached=%d, in_use=%d", cached, in_use); + return thd->strdup(info); + } diff -rC2 mysql-3.23.28-gamma/sql/sql_lex.h mysql-3.23.28-gamma2/sql/sql_lex.h *** mysql-3.23.28-gamma/sql/sql_lex.h Wed Nov 22 11:58:58 2000 --- mysql-3.23.28-gamma2/sql/sql_lex.h Thu Dec 14 12:43:55 2000 *************** *** 53,57 **** SQLCOM_BEGIN, SQLCOM_LOAD_MASTER_TABLE, SQLCOM_CHANGE_MASTER, SQLCOM_RENAME_TABLE, SQLCOM_BACKUP_TABLE, SQLCOM_RESTORE_TABLE, ! SQLCOM_RESET, SQLCOM_PURGE, SQLCOM_SHOW_BINLOGS }; --- 53,58 ---- SQLCOM_BEGIN, SQLCOM_LOAD_MASTER_TABLE, SQLCOM_CHANGE_MASTER, SQLCOM_RENAME_TABLE, SQLCOM_BACKUP_TABLE, SQLCOM_RESTORE_TABLE, ! SQLCOM_RESET, SQLCOM_PURGE, SQLCOM_SHOW_BINLOGS, ! SQLCOM_SHOW_OPEN_TABLES }; diff -rC2 mysql-3.23.28-gamma/sql/sql_parse.cc mysql-3.23.28-gamma2/sql/sql_parse.cc *** mysql-3.23.28-gamma/sql/sql_parse.cc Wed Nov 22 11:58:58 2000 --- mysql-3.23.28-gamma2/sql/sql_parse.cc Thu Dec 14 12:47:04 2000 *************** *** 1447,1450 **** --- 1447,1451 ---- init_vars); break; + case SQLCOM_SHOW_OPEN_TABLES: case SQLCOM_SHOW_TABLES: #ifdef DONT_ALLOW_SHOW_COMMANDS *************** *** 1468,1473 **** goto error; /* purecov: inspected */ /* grant is checked in mysqld_show_tables */ if (lex->options & SELECT_DESCRIBE) ! res= mysqld_extend_show_tables(thd,db, (lex->wild ? lex->wild->ptr() : NullS)); else --- 1469,1478 ---- goto error; /* purecov: inspected */ /* grant is checked in mysqld_show_tables */ + if (lex->sql_command == SQLCOM_SHOW_OPEN_TABLES) + res= mysqld_show_open_tables(thd,db, + (lex->wild ? lex->wild->ptr() : NullS)); + else if (lex->options & SELECT_DESCRIBE) ! res= mysqld_extend_show_tables(thd,db, (lex->wild ? lex->wild->ptr() : NullS)); else diff -rC2 mysql-3.23.28-gamma/sql/sql_show.cc mysql-3.23.28-gamma2/sql/sql_show.cc *** mysql-3.23.28-gamma/sql/sql_show.cc Wed Nov 22 11:59:00 2000 --- mysql-3.23.28-gamma2/sql/sql_show.cc Thu Dec 14 13:46:06 2000 *************** *** 83,86 **** --- 83,127 ---- /*************************************************************************** + ** List all open tables in a database + ***************************************************************************/ + + int mysqld_show_open_tables(THD *thd,const char *db,const char *wild) + { + Item_string *field=new Item_string("",0); + List field_list; + char *end,*table_name; + List tables; + DBUG_ENTER("mysqld_show_open_tables"); + + field->name=(char*) thd->alloc(20+(uint) strlen(db)+(wild ? (uint) strlen(wild)+4:0)); + end=strxmov(field->name,"Open_tables_in_",db,NullS); + if (wild && wild[0]) + strxmov(end," (",wild,")",NullS); + field->max_length=NAME_LEN; + field_list.push_back(field); + field_list.push_back(new Item_empty_string("Comment",80)); + + if (send_fields(thd,field_list,1)) + DBUG_RETURN(1); + + if (list_open_tables(thd,&tables,db,wild)) + DBUG_RETURN(-1); + + List_iterator it(tables); + while ((table_name=it++)) + { + thd->packet.length(0); + net_store_data(&thd->packet,table_name); + net_store_data(&thd->packet,query_table_status(thd,db,table_name)); + if (my_net_write(&thd->net,(char*) thd->packet.ptr(),thd->packet.length())) + DBUG_RETURN(-1); + } + + + send_eof(&thd->net); + DBUG_RETURN(0); + } + + /*************************************************************************** ** List all tables in a database (fast version) ** A table is a .frm file in the current databasedir diff -rC2 mysql-3.23.28-gamma/sql/sql_yacc.yy mysql-3.23.28-gamma2/sql/sql_yacc.yy *** mysql-3.23.28-gamma/sql/sql_yacc.yy Wed Nov 22 11:58:58 2000 --- mysql-3.23.28-gamma2/sql/sql_yacc.yy Thu Dec 14 12:42:18 2000 *************** *** 217,220 **** --- 217,221 ---- %token NUM %token ON + %token OPEN_SYM %token OPTION %token OPTIONALLY *************** *** 2152,2155 **** --- 2153,2161 ---- Lex->db= $3; } + | OPEN_SYM TABLES opt_db wild + { Lex->sql_command= SQLCOM_SHOW_OPEN_TABLES; + Lex->db= $3; + Lex->options=0; + } | COLUMNS FROM table_ident opt_db wild { *************** *** 2511,2514 **** --- 2517,2521 ---- | NCHAR_SYM {} | NO_SYM {} + | OPEN_SYM {} | PACK_KEYS_SYM {} | PASSWORD {} --------------8BAD49D3400D7FB9940BF196--