Howto Add a New Command to the MySQL Server

MySQL LogoAdding a new statement or command to the MySQL server is not difficult.

First, decide if you want to modify the server source code, or if a User-Defined Function (UDF) will meet your needs.

Since I just added the SHUTDOWN server command, I thought I would be helpful to outline the steps needed to add a new command.

Prerequisites:

  1. some familiarity with C/C++ syntax and programming (like “The C Programming Language”, by Kernighan and Ritchie.)
  2. some familiarity with lex and yacc. (I read the Dragon Book a long time ago.)
  3. access to a linux account with cmake, gcc, make and bison packages.
# CentOS
yum install cmake gcc make bison

# Ubuntu
apt-get update
apt-get install cmake gcc make bison

# unpack the MySQL source code:

tar zxvf - < mariadb-5.5.30.tar.gz

# most of the files you need to modify are in this directory:

cd mariadb-5.5.30/sql
  • sql_parse.cc
  • sql_yacc.yy
  • sql_prepare.cc
  • mysqld.cc
  • sql_lex.h

# add the token(s) (commands and arguments you think you will need) and verify the syntax:

bison -v sql_yacc.yy

# if you get warnings, fix %expect in sql_yacc.cc

# cut-and-paste a code block from a command with similar syntax in sql_yacc.cc to implement your new command, and build a test version of MySQL

# build your new server in a sandbox:

make.sh:

#!/bin/bash

cd mariadb-5.5.30
cmake . -DCMAKE_INSTALL_PREFIX:PATH=/usr/local/mariadb-5.5.30
make --with-debug
sudo make install

# test your new server with 3 terminal windows:

start.sh:

#!/bin/bash

killall mysqld
/usr/local/mariadb-5.5.30/bin/mysqld_safe --user=mysql --debug &
tail -f  /tmp/mysqld.trace | grep Got &
tail -f /var/log/mysqld.log &
mysql -u root -p
# login, then test your new command while watching the log and trace

# read /var/log/mysqld.log and /tmp/mysqld.trace for errors and panics like this:

Version: '5.5.30-MariaDB-debug'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  Source distribution
mysqld: /home/james/mariadb-5.5.30/sql/sql_parse.cc:4477: int mysql_execute_command(THD*): Assertion `0' failed.
130515 11:25:19 [ERROR] mysqld got signal 6 ;

This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.

The above panic was caused by the SQLCOM_ switch falling through, because the new command was not defined yet.

# When you’re done, make a test

vi mysql-test/t/my_new_command.test

# Create a patch file:

mv mariadb-5.5.30 mariadb-5.5.30-new
tar zxvf - < mariadb-5.5.30.tar.gz

cd mariadb-5.5.30/src
>patch.txt
for i in sql_parse.cc sql_yacc.yy sql_prepare.cc mysqld.cc sql_lex.h; do
   echo $i
   diff -u $i ../../mariadb-5.5.30-new/sql/ >>patch.txt
done
# don't forget mysql-test/t/my_new_command.test

# apply your patch file:

patch -b < patch.txt

# do a build and test your patch before distributing it.

Easy peasy, right! 🙂

Sergei Golubchik wrote on the MariaDB developers list: "Reserved words are keywords (listed in the sql/lex.h) that are
not listed in the 'keyword' rule of sql_yacc.yy (and 'keyword_sp' rule, that 'keyword' rule includes)."

How can I get the output of the DBUG_PRINT
How to find shift/reduce conflict in this yacc file?
MariaDB Contributor Agreement (MCA) Frequently Asked Questions
wikipedia: diff

MySQL Internals Manual
mysqlperformanceblog.com: XtraDB / InnoDB internals in drawing
Overloading Procedures
innodb_diagrams project
Understanding MySQL Internals By Sasha Pachev (O'Reilly)
DTrace can tell you what MySQL is doing
MySQL C Client API programming tutorial
MySQL 5.1 Class Index

  • https://launchpad.net/~maria-developers
  • IRC, #maria channel on Freenode
  • https://kb.askmonty.org/en/community-contributing-to-the-mariadb-project/
  • https://kb.askmonty.org/en/contributing-code/
  • https://kb.askmonty.org/en/google-summer-of-code-2013/ (ideas)
  • http://mariadb.org/jira/ (search for unassigned tasks)

Keywords: MariaDB, MySQL server programming, tutorial, patch.

This entry was posted in API Programming, Linux, MySQL, Open Source, Oracle, Tech, Toys. Bookmark the permalink.

3 Responses to Howto Add a New Command to the MySQL Server

  1. Hello James,

    I saw your shutdown patch, and on writing a new db opening command I’m pretty stuck at how to pass parameters from the YACC grammar

    command:
    newcommand_SYM ??
    lex->select_lex.db= $2.str; // means that this is argument position 2??

    building the functions to process it with arguments (e.g. char* a db name) is easy, but nothing seems to be passed from the parser with the above approach,

    Thanks for any tips,

    — Mike

  2. Hi Mike.

    The easiest way is find another command that has the same number of arguments and copy that as a template.

    Thanks, James.

  3. SivaPrasath says:

    Hello,

    I am doing my final Year IT in a private engineering college.My final year project needs the implementation of Skyline operator in mysql.I need to add SKYLINE OF clause to improve the results of the query.I need a step wise instruction for implementing skyline in mysql.
    Phone :- +918754754775

    Thanks,

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.