List:Internals« Previous MessageNext Message »
From:Antony T Curtis Date:December 14 2000 3:28pm
Subject:Re: Querying open tables
View as plain text  
Michael Widenius wrote:
> 
> Hi!
> 
> >>>>> "Antony" == Antony T Curtis <antony@stripped> 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
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<Item> &fields,List<Item> &values);
  int fill_record(Field **field,List<Item> &values);
+ int list_open_tables(THD *thd,List<char> *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<char> *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<char> 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<Item> field_list;
+   char *end,*table_name;
+   List<char> 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<char> 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		{}

Thread
Re: Querying open tablesAntony T Curtis14 Dec
  • Re: Querying open tablesMichael Widenius15 Dec