Indirection of SHOW commands between MySQL 4.1 and 5.1

Today, while helping someone find some of the code that implements SHOW VARIABLES I was reminded at how insanely complex it all got with the information_schema implementation and the changes to the SHOW commands that came with it. Specifically, I was trying to find exactly how the SHOW VARIABLES output gets populated — what the source data is.

By way of example, I present the code to implement it in 5.1.51 (the new way), and in 4.1.21 (the old way). I, at least, have a much easier time sorting out the old way. They both start with the same basic entry point, in the parser, in show: within sql/sql_yacc.yy.

SHOW VARIABLES in MySQL 5.1.51

sql/sql_yacc.yy

The show token just does some initialization.

10062 show:
10063           SHOW
10064           {
10065             LEX *lex=Lex;
10066             lex->wild=0;
10067             lex->lock_option= TL_READ;
10068             mysql_init_select(lex);
10069             lex->current_select->parsing_place= SELECT_LIST;
10070             bzero((char*) &lex->create_info,sizeof(lex->create_info));
10071           }
10072           show_param
10073           {}
10074         ;

The command-specific work is done in show_param, but prepare_schema_table just sets up essentially a temporary table.

10076 show_param:
   ...
10273         | opt_var_type  VARIABLES wild_and_where
10274           {
10275             LEX *lex= Lex;
10276             lex->sql_command= SQLCOM_SHOW_VARIABLES;
10277             lex->option_type= $1;
10278             if (prepare_schema_table(YYTHD, lex, 0, SCH_VARIABLES))
10279               MYSQL_YYABORT;
10280           }

sql/sql_parse.cc

Another hoop…

1754 int prepare_schema_table(THD *thd, LEX *lex, Table_ident *table_ident,
1755                          enum enum_schema_tables schema_table_idx)
1756 {
   ...
1849   if (make_schema_select(thd, select_lex, schema_table_idx))
1850   {
1851     DBUG_RETURN(1);
1852   }

sql/sql_show.cc

Through a few hoops, and sel->add_table_to_list(…) adds the new temporary table to the select list.

6076 int make_schema_select(THD *thd, SELECT_LEX *sel,
6077                        enum enum_schema_tables schema_table_idx)
6078 {
6079   ST_SCHEMA_TABLE *schema_table= get_schema_table(schema_table_idx);
6080   LEX_STRING db, table;
6081   DBUG_ENTER("make_schema_select");
6082   DBUG_PRINT("enter", ("mysql_schema_select: %s", schema_table->table_name));
   ...
6087   thd->make_lex_string(&db, INFORMATION_SCHEMA_NAME.str,
6088                        INFORMATION_SCHEMA_NAME.length, 0);
6089   thd->make_lex_string(&table, schema_table->table_name,
6090                        strlen(schema_table->table_name), 0);
6091   if (schema_table->old_format(thd, schema_table) ||
6092       !sel->add_table_to_list(thd, new Table_ident(thd, db, table, 0),
6093                               0, 0, TL_READ))
6094   {
6095     DBUG_RETURN(1);
6096   }
6097   DBUG_RETURN(0);
6098 }

At this point an internal query has basically been built to do:

SELECT * FROM INFORMATION_SCHEMA.VARIABLES

So now that table needs to be populated, but you’ll need to happen to just know where to look to find the rest of the code…

sql/sql_show.cc

The schema_tables array lists all of the information_schema tables and how they are each populated.

6805 ST_SCHEMA_TABLE schema_tables[]=
6806 {
   ...
6879   {"VARIABLES", variables_fields_info, create_schema_table, fill_variables,
6880    make_old_format, 0, 0, -1, 1, 0},
   ...
6884   {0, 0, 0, 0, 0, 0, 0, 0, 0, 0}
6885 };

The schema_tables array says fill_variables can take care of it…

5441 int fill_variables(THD *thd, TABLE_LIST *tables, COND *cond)
5442 {
   ...
5458   res= show_status_array(thd, wild, enumerate_sys_vars(thd, sorted_vars),
5459                          option_type, NULL, "", tables->table, upper_case_names, cond);
   ...
5461   DBUG_RETURN(res);
5462 }

And then show_status_array just knows how to return a SHOW_VAR *, the real work happens in enumerate_sys_vars.

sql/set_var.cc

And finally the SHOW_VAR *result can be populated from the in-memory structures, in this case the system_variable_hash.

3346 SHOW_VAR* enumerate_sys_vars(THD *thd, bool sorted)
3347 {
3348   int count= system_variable_hash.records, i;
3349   int size= sizeof(SHOW_VAR) * (count + 1);
3350   SHOW_VAR *result= (SHOW_VAR*) thd->alloc(size);
3351 
3352   if (result)
3353   {
3354     SHOW_VAR *show= result;
3355 
3356     for (i= 0; i < count; i++)
3357     {
3358       sys_var *var= (sys_var*) hash_element(&system_variable_hash, i);
3359       show->name= var->name;
3360       show->value= (char*) var;
3361       show->type= SHOW_SYS;
3362       show++;
3363     }
3364 
3365     /* sort into order */
3366     if (sorted)
3367       my_qsort(result, count, sizeof(SHOW_VAR),
3368                (qsort_cmp) show_cmp);
3369 
3370     /* make last element empty */
3371     bzero(show, sizeof(SHOW_VAR));
3372   }
3373   return result;
3374 }

SHOW VARIABLES in MySQL 4.1.24

sql/sql_yacc.yy

The same basic entry point.

4422 show:   SHOW
4423         {
4424           LEX *lex=Lex;
4425           lex->wild=0;
4426           bzero((char*) &lex->create_info,sizeof(lex->create_info));
4427         }
4428         show_param
4429         {}
4430         ;

The SQLCOM_SHOW_VARIABLES is saved for later use, denoting what kind of SHOW this query is.

4432 show_param:
   ...
4531         | opt_var_type VARIABLES wild
4532           {
4533             THD *thd= YYTHD;
4534             thd->lex->sql_command= SQLCOM_SHOW_VARIABLES;
4535             thd->lex->option_type= (enum_var_type) $1;
4536           }

sql/sql_parse.cc

In mysql_execute_command a mega switch statement decides what function will handle this query.

1992 void
1993 mysql_execute_command(THD *thd)
1994 {
   ...
3260   case SQLCOM_SHOW_VARIABLES:
3261     res= mysqld_show(thd, (lex->wild ? lex->wild->ptr() : NullS),
3262                      init_vars, lex->option_type,
3263                      &LOCK_global_system_variables);
3264     break;

sql/sql_show.cc

And the magic happens here, in a single function.

1815 int mysqld_show(THD *thd, const char *wild, show_var_st *variables,
1816                 enum enum_var_type value_type,
1817                 pthread_mutex_t *mutex)
1818 {
1819   char buff[1024];
1820   List<item> field_list;
1821   Protocol *protocol= thd->protocol;
1822   LEX_STRING null_lex_str;
1823   DBUG_ENTER("mysqld_show");
1824 
1825   field_list.push_back(new Item_empty_string("Variable_name",30));
1826   field_list.push_back(new Item_empty_string("Value",256));
1827   if (protocol->send_fields(&field_list,1))
1828     DBUG_RETURN(1);
1829   null_lex_str.str= 0;
1830   null_lex_str.length= 0;
1831 
1832   pthread_mutex_lock(mutex);
1833   for (; variables->name; variables++)
1834   {
   ...
2128   }
2129   pthread_mutex_unlock(mutex);
2130   send_eof(thd);
2131   DBUG_RETURN(0);
2132 
2133  err:
2134   pthread_mutex_unlock(mutex);
2135   DBUG_RETURN(1);
2136 }

Much simpler, in my opinion.

2 thoughts on “Indirection of SHOW commands between MySQL 4.1 and 5.1

  1. The biggest problem being the creation of a temporary table to serve just about anything coming from I_S in MySQL.

    The way we’ve done it with table functions in Drizzle means that it’s conceptually a table, but there’s a storage engine serving up the rows, not some other bit of code writing rows into a table to then be sent to the client.

  2. MySQL: query result to file using console « Php Bugs

What do you think?

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s