HackFest B: Creating a New SHOW Command by Brian Aker at the MySQL Users Conference

Brian stepped through the steps for those attending to modify and deploy new functionality in the mysql server. Cool. The end result I would consider for an experienced developer as relatively easy (after avoiding the pitfalls).

NOTE: I wasn’t able to complete this successfully during the session, but I’ve posted this, so hopefully the input and review of others can help in overcoming the current issues. See Outstanding Issues throughout my notes.

Our goal: Produce a new command SHOW CONFERENCE;

The following commands were performed on CentOS 4.2. There may be some differences with different Linux Distros.

Prepare a current Source Code Tree

`
su -
useradd mysqldev
su - mysqldev
wget http://downloads.mysql.com/snapshots/mysql-5.1/mysql-5.1.10-beta-nightly-20060426.tar.gz

NOTE: You should check the snapshots page for latest versions http://downloads.mysql.com/snapshots.php

tar xvfz mysql-5.1.10-beta-nightly-20060413.tar.gz
cd mysql-5.1.10-beta-nightly-20060413

compile script will depend on H/W and requirements

./BUILD/compile-pentium-debug –prefix=/home/mysqldev
make install
scripts/mysql_install_db –datadir=/home/mysqldev/data
sql/mysqld –basedir=/home/mysqldev –datadir=/home/mysqldev/data &
`

NOTE: This can take a while, so it’s a good opportunity to leave this and come back at a later time. The –prefix allows you to install into the new users directory ensuring that you can test without affecting any currently installed mysql installations on your machine.

The BUILD directory contains many different compilations for platforms and variants. This version contains 41 specific scripts. For the purpose of our new SHOW command, we are going to run in debug mode for any necessary debugging.

Outstanding Issues

• I was surprised a make install didn’t configure the bin/mysqld and scripts/mysql_install_db as you would get in a binary distribution. There is probably additional commands necessary for this, but for the purpose of development it works.
• I run multiple versions of MySQL on my machine, and I had some conflict with my /etc/my.cnf file, so I just removed it for the purpose of this test. There needs to be a cleaner way to ensure this isn’t used, and also to compartmentalise/isolate different source trees.

Confirm Installation

I ran this in a seperate terminal window.

su - mysqldev
bin/mysql -e "SELECT VERSION()"
+------------------------------------+
| VERSION()                          |
+------------------------------------+
| 5.1.10-beta-nightly-20060413-debug |
+------------------------------------+

This gives us a suitable source baseline.

Implementing a new SHOW Command

The easiest means of developing a new SHOW command is to base this on an existing command. We are going to base this new SHOW command on the SHOW AUTHORS command.

We are going to be looking at the following files.

1. lex.h
2. sql_yacc.yy
3. sql_lex.h
4. sql_parse.cc
5. mysql_priv.h
6. sql_show.cc

All these files are found under the sql directory in the source tree.

lex.h

131:     { "CONFERENCE",       SYM(CONFERENCE_SYM)},

sql_yacc.yy

202:  %token  CONFERENCE_SYM
...
8267:       | CONFERENCE_SYM
8268:         {
8269:           LEX *lex=Lex;
8270:           lex->sql_command= SQLCOM_SHOW_CONFERENCE;
8271:         }
...
9366:       | CONFERENCE_SYM        {}

sql_lex.h

112:   SQLCOM_SHOW_CONFERENCE,

sql_parse.cc

3515:   case SQLCOM_SHOW_CONFERENCE:
3516:     res= mysqld_show_conference(thd);
3517:     break;

mysql_priv.h

915: bool mysqld_show_conference(THD *thd);

sql_show.cc

229: bool mysqld_show_conference(THD *thd)
230: {
231:   List<item> field_list;
232:   Protocol *protocol= thd->protocol;
233:   DBUG_ENTER("mysqld_show_conference");
234:
235:   field_list.push_back(new Item_empty_string("Name",100));
236:
237:   if (protocol->send_fields(&field_list,
238:                             Protocol::SEND_NUM_ROWS | Protocol::SEND_EOF))
239:     DBUG_RETURN(TRUE);
240:
241:   protocol->prepare_for_resend();
242:   protocol->store("Welcome to the MySQL User Conference 2006", system_charset_info);
243:   if (protocol->write())
244:     DBUG_RETURN(TRUE);
245:   send_eof(thd);
246:   DBUG_RETURN(FALSE);
247: }

<br /> cd ..<br /> ./BUILD/compile-pentium-debug --prefix=/home/mysqldev<br /> ...<br /> gmake[3]: *** Waiting for unfinished jobs....<br /> /bin/chmod +x mysql-test-run-t<br /> /bin/mv mysql-test-run-t mysql-test-run<br /> gmake[3]: Leaving directory `/home/mysqldev/mysql-5.1.10-beta-nightly-20060413/mysql-test'<br /> gmake[2]: *** [all-recursive] Error 1<br /> gmake[2]: Leaving directory `/home/mysqldev/mysql-5.1.10-beta-nightly-20060413/mysql-test'<br /> gmake[1]: *** [all-recursive] Error 1<br /> gmake[1]: Leaving directory `/home/mysqldev/mysql-5.1.10-beta-nightly-20060413'<br /> gmake: *** [all] Error 2<br />

Outstanding Issues

• I’m sure there must be a more effecient means of compiling changes, this process effectively cleaned and started again.
• I had a build failure which didn’t seem to make any sense.

At this point I wasn’t able to continue, but here are notes I took of next steps.

make install
gdb mysqld
run --gdb --debug

In another terminal session.

su - mysqldev
bin/mysql
mysql> SHOW CONFERENCE;

I’m keen to see it work in my own environment and documented for others to try.

Brian moved on to creating a INFORMATION_SCHEMA query but we ran out of time to complete this. I’ve got some notes to document as a later date.