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!

I recently bought a pocket sized GPS tracker that literally is meant to fit on your keychain.

I’ve been looking for something this simple for a while now – all I wanted to be able to do was keep track of where I’ve _been_ so that I can refer to it later with a full computer; for that, this device exceeds expectations. I have a motorcycle and it’s really nice to be able to show off routes and back roads I recommend taking, not to mention help remind myself where “that picturesque sunset view” was when I was serendipitously passing through a not-so-well-known part of town. Here’s a quick lowdown of what the Taiwanese designed Qstarz can do.

Features

The BT-Q1300S is primarily geared towards runners and the fitness minded since it includes a nice sweatproof arm band for physical activities. Don’t let that fool you though, because it packs a lot of functionality into a small device. It tracks latitude/longitude, plus altitude and speed over time. Like I was saying, it’s all of about the size of a regular keychain, and it only has one button, a couple of leds for status purposes, and a mini-usb port to connect to your computer in order to make the nice pretty graphs I’ve got below.

The device’s interface to start and stop logging, set a waypoint, and turn on/off takes a little time to learn, but is fairly simple and straightforward once you know what those are. For example, to turn on the device, hold the button for 4 seconds. Once it’s locked onto satellites (one of the leds starts blinking to let you know), press and hold the button again for another 2 seconds to start logging. From this point, you can save a waypoint by pressing the button each time you want to save a location, or just let it save waypoints automatically. To stop logging, press and hold for another 2 seconds. Now you’re back in power-save mode. 4 more seconds to turn it off completely. Voila.

Smart waypoint saving

If you’ve ever used a GPS device before, you’ll understand how frustrating it is to later view your trip and see “clusters of waypoints” saved on the same location, or worse, no waypoints between important roads at all because you were traveling too fast. This is the result of using only one dimension (time) for determining when to save waypoints, and it’s all too common in modern devices. One of my favorite things about the Qstarz is that you can configure it to save it across three separate criteria: time, distance, and speed. These can also be combined in any combination in a logical AND fashion, which adds to its flexibility. For example, you want to save waypoints every 1 second AND when you’ve gone greater than 50 feet AND you’re traveling faster than 5 mph. This is awesome when it comes to not logging unnecessary waypoints when you’ve come to a complete stop, or when you’re traveling really, really slow and don’t need an update every x seconds.

Let’s get to the demo!

Ok, the cool stuff now. Here is an example of a trip that I took recently. The software has the ability to export as an html file (hugging to Google Maps for the map), which I’ve included as an iframe here.

It also creates some interesting line graphs to view your datapoints. Here is an example of speed over time.

An example of one of the line graphs Qstarz creates.

What’s missing

I really wish I could give this device 5 stars, but there are a couple of important things that they forgot that unfortunately keep me from giving it ultimate honors. Most importantly, the software has no way to be able to edit waypoints. This is important if you want to build a view of part of your trip taken from the device, or if you find that some of the waypoints are inaccurate and you just want to delete them (this has happened). Since it creates the full kml and other formats for you on export, and since the software utilizes the Google Maps API within itself to view your waypoints as they are imported, this would be a trivial feature to add.

Additionally, but less important, the device’s software won’t create an export for plug-and-play into a blog like this one using an iframe. You see this on a lot of sites nowadays (including Google Maps), so it’d be nice to see this as well. I’m sure that the vast majority of bloggers wouldn’t have figured out how to take the html export and make it work in an iframe (I also had to add my site’s Google Maps API key to the html source, so if I weren’t a web software engineer, I think it would have been hard to get it to work).

I thought I’d share one little and less commonly used, but very useful capability with ssh. It is technically called local application-level port forwarding, and I use it quite often. There are two use cases I find it useful for.

1. Secure browsing at conferences and the like
2. Providing an easy way to access local network resources such as an internal wiki (on my home network).

The command: ssh -l chris -D 12345 local.musialek.org

Setup and Assumptions

I assume that you have a broadband connection to the internet with some sort of router behind it (very common these days). Also, in my example, local.musialek.org has been set the IP address of my router. Last, in my router’s configuration, I have set up port forwarding, to forward packets on port 22 (standard ssh traffic) to the machine on the local network that actually has sshd running on it. There are other ways of doing this, but I’m not going to go through it, as this is not the point of the post.

Browser proxy configuration

Assuming these things are set up, open up a command line window, type in the command, and log in to your ssh server. Last, we need to configure a SOCKS proxy on the browser. With connecting to our ssh server on our local network, with the -D option, we’ve also opened a listening port on port 12345 locally to forward packets along our ssh “tunnel”. SOCKS is an interesting protocol whose job is simply to facilitate communication between other protocols (but only the higher layers of the OSI model). It is what allows us to talk multiple protocols over our tunnel. To be precise, any protocol at a higher layer than layer 5, the Session layer, can be proxied. This includes FTP, HTTP, HTTPS, LDAP, DNS, DHCP, etc. It’s most commonly used with HTTP however, and this is what I want to show today.

Since it’s more easily configurable, I use FoxyProxy to get the SOCKS proxy setup on Firefox. You’ll see options to configure SOCKS.

foxyproxy settings

Make sure to set the port to 12345, which is what we configured our ssh client to listen on (with the -D option). Hit ok and now you’re browsing the internet over your ssh tunnel! That’s it!

Use #1 – Browsing securely

So use case number one, browsing securely. With the above setup, you’re at a conference, people are potentially looking at your traffic, and you don’t want that. So just start up an ssh session back home, configure your browser’s proxy and voila!, your entire session is now encrypted, and coming out of your wireless router back home.

Use #2 – Browse internal resources

I’ve got a wiki on my local network that you can’t get to on the internet, and I use it for keeping track of more personal things like my grocery list, and my list of recipes I’ve found over the years. Obviously, when I’m home and on my local network, it is accessible, but when I’m not home, I can’t get to it. But with the beauty of ssh, this is possible. Best of all, since DNS is also proxied (via the browser), I don’t have to add special rules so that any internal DNS I configure doesn’t have to be setup on my laptop!