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!

At the recent OpenSQLCamp, Brian Aker and Jay Pipes told us about Drizzle: what it is, and what it’s not. Here’s what I picked up from it.

What it is

• A fork from MySQL 6.0
• A redesign of some components of the code
• A pluggable microkernel

What it’s not

• MySQL 7.0
• A replacement for MySQL
• It is _not_ just a pared down version of MySQL

The redesigns

Not everything has been redesigned, nor will everything be redesigned. The general idea of the drizzle OSS project is to take the best of MySQL, and leave behind some of its shortcomings. I’m not an expert in the MySQL code, but one of the more obvious shortcomings is it’s lack of reuse of standard libraries. Instead, in many cases, MySQL rolls its own code, for example with authentication (why are db rights even stored in MySQL?). This breaks one of the fundamental principles of CS: code reuse. I hope I don’t need to argue the merits of code reuse, but from a product perspective (in the OSS/MySQL world), having to constantly reinvent the wheel (because you’re also having to update your code to keep up with the standard libraries) slows progress considerably. Their philosophy is to build a plugable database server “microkernel” that one, reuses existing, well developed libraries (such as libxml), and two, is extendable in multiple places to allow other developers to more easily and more quickly add in useful features they would like to have.

This isn’t so far fetched, in particular today with the growing use of solid state drives. In what scenarios would MySQL perform better if it used a DRAM-based SSD? With extremely low latency, and very fast random IO compared to mechanical devices, IO bound db workloads could change considerably. Clustered indexes would matter less as long as the number of page accesses doesn’t increase, as opposed to trying to access data purely sequentially (which is data that has some relationship to its adjacent node – that’s why it’s stored for sequential access). Joins lose some of their cons, because random IO doesn’t matter as much. It would be interesting to see benchmark data comparing SSDs versus traditional disks on a database’s performance. Has anyone done this type of analysis?