Where pegs grow legs: hanging ideas on words

At the MySQL Hackfest Camp of the MySQL conference, Mark Callaghan, MySQL code extraordinaire, helped us to hack our first change to the MySQL source code. In an hour, we implemented an additional command “SHOW HELLO” to the MySQL server. In and of itself, the command is not very useful per se, but I wanted to share what Mark showed us to modify MySQL’s SQL parsing code. Getting started with creating your own patch isn’t as hard as you think!

It was nice having a MySQL code expert by your side, telling you what files you need to look into to make your changes work. Here is how he showed us how to add a “SHOW HELLO” to the existing SHOW commands in the MySQL server. Once you’re done, it works like the following:

mysql> SHOW HELLO;
+--------------------+
| Hello Output       |
+--------------------+
| Good day to you!   |
+--------------------+
1 row in set (0.00 sec)

mysql>

Files we’ll be modifying

There are a few files we’ll have to open. Luckily, all of them are in the sql/ directory. This makes sense since we’re modifying the parser. Here’s the full list at a glance.

• sql/sql_yacc.yy – modifications in 4 places – bison then converts this file into .h and .cc files
• sql/lex.h – modifications in 1 place – reserve HELLO as a symbol
• sql/sql_lex.h – modifications in 1 place – let server know about new command
• sql/sql_parse.cc – modifications in 1 place – maps parsing to execution code
• sql/mysql_priv.h – modifications in 1 place – declare function that does execution in sql_show.cc
• sql/sql_show.cc – implement the actual function that does the work

The modifications

sql_yacc.yy

In my_yyoverflow(), around line 321. Add a token HELLO_SYM.

%token  HAVING
%token  HELP_SYM
%token  HELLO_SYM
%token  HEX_NUM
%token  HIGH_PRIORITY

Around line 6913, tell the lexer to look for another command.

| MUTEX_SYM STATUS_SYM
  { Lex->sql_command = SQLCOM_SHOW_MUTEX_STATUS; }
| opt_full PROCESSLIST_SYM
  { Lex->sql_command= SQLCOM_SHOW_PROCESSLIST;}
| HELLO_SYM
  { Lex->sql_command= SQLCOM_SHOW_HELLO;}
| opt_var_type  VARIABLES wild_and_where
  {
    LEX *lex= Lex;
    lex->sql_command= SQLCOM_SELECT;
    lex->orig_sql_command= SQLCOM_SHOW_VARIABLES;
    lex->option_type= $1;
    if (prepare_schema_table(YYTHD, lex, 0, SCH_VARIABLES))
      YYABORT;
  }

Around 8047, add a keyword.

| GLOBAL_SYM            {}
| HASH_SYM              {}
| HELLO_SYM             {}
| HOSTS_SYM             {}
| HOUR_SYM              {}

lex.h

In the symbols[] array, around line 226.

{ "HASH",             SYM(HASH_SYM)},
{ "HAVING",           SYM(HAVING)},
{ "HELLO",            SYM(HELLO_SYM)},
{ "HELP",             SYM(HELP_SYM)},
{ "HIGH_PRIORITY",    SYM(HIGH_PRIORITY)},

sql_lex.h

In enum_sql_command(), add a command.

SQLCOM_SHOW_INNODB_STATUS, SQLCOM_SHOW_NDBCLUSTER_STATUS, SQLCOM_SHOW_MUTEX_STATUS,
SQLCOM_SHOW_PROCESSLIST, SQLCOM_SHOW_MASTER_STAT, SQLCOM_SHOW_SLAVE_STAT, SQLCOM_SHOW_HELLO,
SQLCOM_SHOW_GRANTS, SQLCOM_SHOW_CREATE, SQLCOM_SHOW_CHARSETS,

sql_parse.cc

Somewhere inside mysql_execute_command(), add the following. I’ve added around line 3725.

case SQLCOM_SHOW_PROCESSLIST:
  if (!thd->security_ctx->priv_user[0] &&
    check_global_access(thd,PROCESS_ACL))
  break;
  mysqld_list_processes(thd,
                       (thd->security_ctx->master_access & PROCESS_ACL ?
                        NullS :
                        thd->security_ctx->priv_user),
                       lex->verbose);
  break;
case SQLCOM_SHOW_HELLO:
  mysqld_print_hello(thd);
  break;
case SQLCOM_SHOW_STORAGE_ENGINES:
  res= mysqld_show_storage_engines(thd);
  break;

mysql_priv.h

Around line 909, under sql_show.cc comments.

/* sql_show.cc */
bool mysqld_show_open_tables(THD *thd,const char *wild);
bool mysqld_show_logs(THD *thd);
void mysqld_print_hello(THD *thd);
void append_identifier(THD *thd, String *packet, const char *name,
uint length);
int get_quote_char_for_identifier(THD *thd, const char *name, uint length);

sql_show.cc

Around line 1267, implement the new command that will run when you call SHOW HELLO.

#ifdef HAVE_EXPLICIT_TEMPLATE_INSTANTIATION
template class I_List ;
#endif

void mysqld_print_hello(THD *thd)
{
  sql_print_error("enter_hello");
  Item *field;
  List field_list;
  field_list.push_back(new Item_empty_string("Hello Output",16));
  Protocol *protocol= thd->protocol;
  if (protocol->send_fields(&field_list,
  Protocol::SEND_NUM_ROWS | Protocol::SEND_EOF))
  {
    sql_print_error("can't send_fields - hello");
    return;
  }
  protocol->prepare_for_resend();
  protocol->store("Good day to you!", system_charset_info);
  if (protocol->write())
  {
    sql_print_error("cant write - hello");
    return; /* purecov: inspected */
  }
  send_eof(thd);
  sql_print_error("exit_hello");
  return;
}

void mysqld_list_processes(THD *thd,const char *user, bool verbose)
{
  Item *field;
  List field_list;

Testing

Once you make the changes, compile MySQL using make, and test it. You should be doing this for every change that you intend to give back to MYSQL. To test, you need to create a .test and .result file inside the mysql-test/t and mysql-test/r directories respectively. You can name the files whatever you want, just make sure to call them the same thing.

mysql-test/t/hello_file.test

# Test for show hello
show hello;

mysql-test/r/hello_file.result

show hello;
Hello Output
Good day to you!

Once you’ve added those files, go into the mysql-test directory, and run ./mtr hello_file. This runs the new test you just added. If it passes, it works, and if not, you need to go back and look at something.

Summary

That’s it! That wasn’t too hard, was it? I realize that you probably would have had a much harder time finding all these places on your own. However, once someone shows you what pieces are important, you should be able to make other changes easily. Hopefully, there are others very familiar with the MySQL code base that would be willing to write a post about how to write a UDF, or add a new SQL function. Plus, I’d love to hear how best to send MySQL row change log output to something like Hypertable or Hbase!