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.

MySQL has a new section on their site about MySQL 6.0, which they are now calling “ready for pre-production testing”. I’m not sure when this section appeared, but I don’t spend much time on the MySQL site outside of the manual and downloads sections. Browsing around this new section I found a real gem: “Top Reasons Falcon is Cool” (or, as alternately titled on the page itself, “Top Reasons to use Falcon for Online Applications”¹ … did someone forget to rename one or the other?). This page gives a top ten list² of reasons why one should consider using Falcon, the new “not an InnoDB replacement, not at all!” but “really, you should try migrating your InnoDB application to it” storage engine.

I do think that Falcon will eventually be quite interesting, and it will hopefully have a bunch of nice tricks and whatnot, but MySQL’s marketing folks are really pushing it way too early and losing a lot of credibility in the process. Do they not realize we (as MySQL users) and a lot of others (as analysts, Oracle, DB2, and yes, even MS-SQL users) are laughing at them? I know it’s hard work to come up with 10 things sometimes, but if you get stuck at 7, make it a “Top 7” instead of a “Top 10”. Don’t add a bunch of crap to fill it out. There are some minor chuckles in the first 7, but here’s the last 3, with my commentary:

8. Simplified Configuration

There is no complexity whatsoever in terms of configuration as only a handful of variables exist to control the behavior of the Falcon engine.

Hmm, s/bug/feature/ and you’re done! It’s so easy to configure!

9. High Availability

Extreme degrees of high availability are easily accomplished for a Falcon-driven system by using either MySQL replication or supported third-party high availability solutions such as DRBD.

This has nothing to do with Falcon, whatsoever.

10. Parallel Execution

Falcon’s design takes advantage of multi-core systems to provide parallel execution of user and service threads. Falcon uses fine-grained multi-threading to increase parallelism with locking on internal structures being done at a low level. In some cases, two threads can change different attributes of the structure at once, because the attributes are separately lockable.

Yay! Awesome! Parallel execution! It’s finally here!!!

Oh, wait, they don’t mean parallel execution of queries they mean parallel execution of internal threads. That’s nothing new, and InnoDB is already doing that. Maybe Falcon has finer-grained locking and can do this better, but that doesn’t make for a big bold title of “Parallel Execution”.

Come on, folks. Try harder.

¹ Who else gets a headache from American rules for capitalization of titles?

² How trite, yet another top ten list.