..bits & bytes teleported

Some links on DTrace + MySQL: http://delicious.com/amitkumarsaha/dtrace%2Bmysql

IDEs like NetBeans has made life easier and more productive. 

But, you may as well use utilities like 'cscope' when all you have is a console login. Here is how you would set up MySQL sources for easy cross-referencing using 'cscope':

Build the cross-reference:

Go the MySQL sources top-level directory and build the cross-reference (-R is used to descend recursively into the directories, and -b builds the cross-references):

/home/as227057/dev-tools/mysql-5.1.25-rc" 24 $ cscope -Rb

Search for symbols, functions, etc:

$cscope` -R brings up a text based interface:

Find this C symbol:
Find this global definition:
Find functions called by this function:
Find functions calling this function:
Find this text string:
Change this text string:
Find this egrep pattern:
Find this file:
Find files #including this file:
Find all function definitions:
Find all symbol assignments:

Using the various options available you can navigate through the sources easily. Here are some links on 'cscope':

• http://cscope.sourceforge.net/
  
• http://cscope.sourceforge.net/large_projects.html

I had posted some notes on the MySQL test framework in my earlier post at http://blogs.sun.com/amitsaha/entry/the_mysql_test_framework

'mysql_client_test.c' which lives in the 'tests/' directory uses the MySQL C API to write various client side tests to be executed on the server. In this post, I will show how you can add your own test(s) to this file.

Why would you want to do that?

Adding your tests to this file enables you take advantage of the existing test framework to run your tests.

How does 'mysql_client_test.c' execute the tests?

In the main( ) function, this code snippet selects the tests to be run:

 for ( ; *argv ; argv++)
  18054       {
  18055         for (fptr= my_tests; fptr->name; fptr++)
  18056         {
  18057           if (!strcmp(fptr->name, *argv))
  18058           {
  18059             (*fptr->function)();
  18060             break;
  18061           }
  18062         }
  18063         if (!fptr->name)
  18064         {
  18065           fprintf(stderr, "\n\nGiven test not found: '%s'\n", *argv);
  18066           fprintf(stderr, "See legal test names with %s -T\n\nAborting!\n",
  18067                   my_progname);
  18068           client_disconnect();
  18069           free_defaults(defaults_argv);
  18070           exit(1);
  18071         }

where 'fptr' is defined as:

struct my_tests_st *fptr
 

Map-like data structure

'my_tests_st' implements a map-like data structure which 'maps' test names to the function pointers which points to functions which contain codes for carrying out the tests. It is defined as:

   struct my_tests_st
      {
        const char *name;
        void       (*function)();
      };

For eg.

 static struct my_tests_st my_tests[]= {
  17681   {"testse_create_table", testse_create_table }, /* Entry for the new test method introduced - Amit.Saha@sun.com*/
  17682   { "disable_general_log", disable_general_log },
.
.

'mysql_client_test.c' can operate in 2 modes: 

1. Run all the tests which are defined in the file (when no arguments is specified)
   
2. Run only the test which is specified in the argument list supplied to it. When the test is specified by its name, the structure above is looked up to find the appropriate function pointer and subsequently the function is called
   

Adding a new test case:

1. Write your test case in a new function and append it to 'mysql_client_test.c'
   
2. Append the method signature to the file somewhere towards the beginning of the file (after the #include statements)
3. Add the appropriate mapping in the static struct my_tests_st my_tests[] either at the end or beginning
4. Do a 'make' in the tests/ sub-directory
5. To run only the new test case, you just added:

   $./mysql_client_test function_name
   

   
   

Demo

Say, I want to create a new test case which will test whether I can create a table in my new custom storage engine- TESTSE.

So I will append the function to 'mysql_client_test.c':

 /* Test to create a table using the TestSE */

 void testse_create_table()

{
  myheader("testse_create_table");

  char QUERY[]="CREATE TABLE demo2(car CHAR(15) NOT NULL, year INTEGER NOT NULL)  ENGINE=TESTSE";



  printf("\n Client connected to MySQL Server %s \n", mysql_get_server_info(mysql));
  printf("\n Connection Description %s\n", mysql_get_host_info(mysql));
  if(mysql_query(mysql, QUERY)!=0){

         printf("\n Query %s failed", QUERY);
         }
  else
  	{
	printf("Query succeeded\n");
	}
 }

Now add the following to static struct my_tests_st my_tests

  {"testse_create_table", testse_create_table },

• Add a function signature for your function- void testse_create_table() somewhere before the place where the above mapping occurs
• Do a 'make'
• Now execute your test by doing:

 $ ./mysql_client_test --no-defaults --socket=/tmp/mysql_9090.sock --user=root testse_create_table

Based on your system settings you may have to supply different options and values. The various options available can be seen by:

$ ./mysql_client_test --help

The test output should be something like:

#####################################
client_connect
#####################################

 Establishing a connection to '' ...OK
Connected to MySQL server version: 5.1.24-rc (50124)

 Creating a test database 'client_test_db' ...OK

#####################################
1 of (1/1): testse_create_table
#####################################

 Client connected to MySQL Server 5.1.24-rc

 Connection Description Localhost via UNIX socket
Query succeeded


#####################################
client_disconnect
#####################################

 dropping the test database 'client_test_db' ...OK
 closing the connection ...OK


All '1' tests were successful (in '1' iterations)
  Total execution time: 0 SECS

Relevant Source code directories and Notes

• mysql-test- test suite for the MySQL daemon. Uses Perl script to run tests using a pre-defined framework and test language
• To run the tests in this test-suite, go to mysql-test and run the 'mysql-test-run.pl' script
• 'make test' also does the above after running the tests in 'unittest'
  
• tests- C & perl scripts to perform various client -like tasks- uses libmysql (C lib) Perl API respectively (mainly tests features which are not possible via above)
• No where in the top-level Makefile is the 'tests' directory mentioned
• what is the 'mysql_client_test.c' doing?
  
• mysql_client_test- shell script wrapper for .libs/mysql_client_test
  
• the other tests- Perl scripts + c binaries can be executed independently; binaries are copied to .libs directory under 'tests'
  
• 'make install' in tests shows the following:
  
• :"/home/as227057/dev-tools/mysql-5.1.25-rc/tests" 104 $ make install
  make[1]: Entering directory `/home/as227057/dev-tools/mysql-5.1.25-rc/tests'
  test -z "/home/as227057/mysql-linux/bin" || mkdir -p -- "/home/as227057/mysql-linux/bin"
    /bin/sh ../libtool --preserve-dup-deps --mode=install /usr/bin/install -c 'mysql_client_test' '/home/as227057/mysql-linux/bin/mysql_client_test'
  /usr/bin/install -c .libs/mysql_client_test /home/as227057/mysql-linux/bin/mysql_client_test
  make[1]: Nothing to be done for `install-data-am'.
  make[1]: Leaving directory `/home/as227057/dev-tools/mysql-5.1.25-rc/tests'
• As is apparent, 'make install' installs the 'mysql_client_test' wrapper script in the bin/ directory which can be executed as before
  
  
• http://lists.mysql.com/internals/35865
  
• unittest- contains the implementation of the MyTAP library and some example tests using MyTAP- no MySQL relevant tests
• make test-unit
• new tests can be inserted and run by adding relevant info in Makefile.am
  

Reference

• http://dev.mysql.com/doc/mysqltest/en/test-framework-components.html
  
• Top-level Makefile

We are just a week away

Come join us. Details at : http://blogs.sun.com/amitsaha/entry/mysql_camp_in_bangalore_july

Link to visit: http://mysql.meetup.com/297/

PS:

MySQL camp is on http://in.sun.com  (picture from Collin's blog entry) 

Sun Microsystems, India and OSSCube are organizing a MySQL camp on 29th July, 2008.

A highlight of the event is the keynote by Kaj Arno, VP for Community Relations from MySQL AB.

The venue for the camp is:

Hi-Tech Seminar Hall,
   DES Block (Dept of Electrical Science)
   M S Ramaiah Institute of Technology
   Near MS Ramaiah hospital
   MSR Nagar, Bangalore - 560054 

The timings for the camp are 4PM to 7PM, July 29th, 2008.

The Scheduled talks include:

Title	Speaker	Abstract	Duration
MySQL - The community, The Product, The Company	Kaj Arno	An overview of all things MySQL, from a combined technical and business perspective. A short history of MySQL, the company, the product. What the MySQL Community is, and how MySQL works with it. How MySQL is being integrated into Sun.	One Hour
How to Contribute Code to MySQL	Thava Alagu	A discussion on How the community can contribute code to the MySQL DB Project.	About 30 minutes
Performance Optimisation in Enterprise Applications	Sonali Minocha	A discussion on how performance optimisation can be carried out using MySQL for Enterprise Applications.	About 30 minutes

The event will also mark the launch of Bangalore MySQL User Group

So, come join us and have a blast!

Please add your name at http://www.osscube.com/mysql/camp.php and/or http://mysql.meetup.com/297/

When writing scripts for running MySQL in batch mode, it is often desired to be able to change the SQL queries on-the-fly so that we can use the script to solve more generic problems.

For example, I need to use my script for different storage engines and table names and I want to be able to do it at runtime instead of manually changing my script every time.

Here is how I do it: I have a template file which is my script but with the variables having generic names. In this case, I have considered plugin_name as the storage engine name , and table_name as the table name.

Consider the following script:

# automation-script-template
# Mixing shell commands and SQL queries in batch mode- demo script

# Replace variables: plugin_name, table_name with appropriate names- done automatically by the calling shell script.

use test;

INSTALL PLUGIN plugin_name SONAME 'ha_plugin_name.so'; #Ignore statement

system mkdir traces/plugin_name

system cp /tmp/mysqld.trace traces/plugin_name/init.trace # Shell commands prefixed with a 'system'

create table table_name(num integer) engine=plugin_name;

system cp /tmp/mysqld.trace traces/plugin_name/after_create.trace # Shell commands prefixed with a 'system'

system diff traces/plugin_name/init.trace traces/plugin_name/after_create.trace # Shell commands prefixed with a 'system'

In the above script, plugin_name and table_name are the two parameters which I want to be able to change. Now, the strategy I follow is that using a shell script (below) by passing the relevant values as command line arguments to it, I 'generate' a new batch-sctipt file everytime using the above template file:

#!/usr/bin/bash
#launch

#sed- replace VARIABLES with arguments.
#plugin_name, table_name with appropriate names

#replace table_name and plugin_name
sed -e 's/table_name/'${1}'/g' -e 's/plugin_name/'${2}'/g' automation-script-template > collect-traces


# Invoked MySQL in batch mode
mysql -u root < collect-traces > log;

As you can see, I use 'sed' to replace all the occurences of the parameters with the given command line arguments and generate a case-specific script on-the-fly using the automation-script-template. A sample invocation would be:

$ ./launch dummy4 example

Which produces the following 'collect-traces' file (case-specific file):

# Mixing shell commands and SQL queries in batch mode- demo script

# Replace variables: example, dummy4 with appropriate names- done automatically by the calling shell script.

use test;

INSTALL PLUGIN example SONAME 'ha_example.so'; #Ignore statement

system mkdir traces/example

system cp /tmp/mysqld.trace traces/example/init.trace # Shell commands prefixed with a 'system'

create table dummy4(num integer) engine=example;

system cp /tmp/mysqld.trace traces/example/after_create.trace # Shell commands prefixed with a 'system'

system diff traces/example/init.trace traces/example/after_create.trace # Shell commands prefixed with a 'system'

As is apparent from the 'launch' script, this script is fed to the MySQL client in batch mode. Thus, you can successfully write more generic SQL queries using the method above.

You may also want to see this post: Mixing SQL and shell commands in MySQL

When using MySQL in batch mode, you may often find it useful to use the UNIX shell commands along with the SQL queries. This is a demo script showing how you can achieve that:

# Mixing shell commands and SQL queries in batch mode- demo script

use test;

#INSTALL PLUGIN example SONAME 'ha_example.so'; #Ignore statement

system cp /tmp/mysqld.trace logs/init.trace # Shell commands prefixed with a 'system'

create table new4(num integer) engine=EXAMPLE;

system cp /tmp/mysqld.trace logs/after_create.trace # Shell commands prefixed with a 'system'

system diff logs/init.trace logs/after_create.trace # Shell commands prefixed with a 'system'
 

This script makes use of the 'system' command:

system  (\!)    Execute a system shell command.

Note that this is available only on UNIX systems.

Kaj's blog: http://blogs.mysql.com/kaj/2008/06/18/netbeans-as-ide-for-developing-mysql-itself/

MySQL folks have switched to Bazaar as their version control of choice.

Here is the official announcement. 

Not sure if the post title actually reflects the contents. 

This post describes how you can obtain the traces that MySQL server leaves behind for you if you ask it to. Once you have the traces, you can use it for:

• Debugging - It can help you to find out which source file is the server crashing, for eg.
• Learn- learn more about which functions/methods are called for a particular query by the client

Okay, so lets start.

Traceable Server

To be able to obtain traces, your server needs to be compiled with "debug" enabled. If the version information shows you something like this:

 Ver 5.1.24-rc-debug for pc-linux-gnu on i686 (Source distribution)

with 'debug' appended to the version name, then you have a traceable server, else you will have to recompile it by enabling debug support:

./configure --with-debug 
make
make install

Start the server in the 'debug' mode

./mysqld --debug 

You should get something like this:

Version: '5.1.24-rc-debug'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution

The server daemon has started and is waiting for connections.

Where is the 'trace'?

The tracefile is usually /tmp/mysqld.trace on Linux (Unix) systems. Open it using 'cat' or in your text editor and you should see screens full of information like this:

T@1    : | | | | my_free
T@1    : | | | | | my: ptr: 0x8566860
T@1    : | | | | my_free
T@1    : | | | | | my: ptr: 0x8541858
T@1    : | | | | my_free
T@1    : | | | | | my: ptr: 0x85520a8
T@1    : | | | | <~THD()
T@1    : | | >hash_free
T@1    : | | | enter: hash: 0x8540eb8
T@1    : | | plugin_unlock_list
T@1    : | | hash_free
T@1    : | | | enter: hash: 0x85406bc
T@1    : | | | >my_free
T@1    : | | | | my: ptr: 0x8541930
T@1    : | | | hash_free
T@1    : | | | enter: hash: 0x8540688
T@1    : | | | >my_free
T@1    : | | | | my: ptr: 0x8546748
T@1    : | | | my_free
T@1    : | | | my: ptr: 0x853fe38
T@1    : | | sql_print_information
T@1    : | | >vprint_msg_to_log
T@1    : | | | >print_buffer_to_file
T@1    : | | | | enter: buffer: ./mysqld: ready for connections.
Version: '5.1.24-rc-debug'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution

Here is a brief key to the information above:

> indicates that it is entering the function adjoint to it

< indicates an exit out of the function adjoint to it

How does it work?

MySQL source code uses DBUG- a C programming debugging package, originally created by Fred Fish (Details).

Usage of the various DBUG macros available enable code instrumentation which enable obtaining helpful traces as we have just seen. For eg. consider the following code snippet from storage/example/ha_example.cc:

int ha_example::open(const char *name, int mode, uint test_if_locked)
{
  DBUG_ENTER("ha_example::open");

  if (!(share = get_share(name, table)))
    DBUG_RETURN(1);
  thr_lock_data_init(&share->lock,&lock,NULL);

  DBUG_RETURN(0);
}

You can see that DBUG_xx macros are used to indicate the function entry and the function return in this case. There are other macros available which you will come across if you browse through the MySQL source code. 

As it is apparent, DBUG can be used as a useful tool to print out useful diagnostic messages during the course of execution of a program. The DBUG package- source code, user guide lives under the dbug/ sub-directory in the MySQL source code.

Debug String

debug string is the set of options that you can pass to the MySQL server to customize the information that you want it to dump in its trace:

./mysqld --debug='debug string'

By default, the trace is dumped in /tmp/mysqld.trace file and a snapshot has been reproduced earlier. The debug string actually directs DBUG as to what information to be dumped.

The default debug string is: d:t:i:o,/tmp/mysqld.trace and is equivalent to: (Thanks to Guilhem)

./mysqld --debug=d:t:i:o,/tmp/mysqld.trace

The various options, flags are described in the DBUG user's guide. For a local copy see towards the bottom of the post.

Here is a sample debug string I used to obtain the filenames as well as the line nos. (in the source code) in the trace file:

./mysqld --debug=d:t:F:L:o,/tmp/mysqld.trace

Resources

• Some queries I asked on the MySQL internals list can be a good read:

• Running MySQL server in debug mode
• Internal instrumentation using 'DBUG'

• DBUG user guide (local copy)

• DBUG usage in MySQL
  

MySQL 5.1.24-rc ships with a EXAMPLE storage engine which is basically a dummy storage engine and serves as a useful source to start writing your own custom storage engine.

However, it is not available for use, by default. You can verify this as follows:

mysql> show engines;
+------------+---------+-----------------------------------------------------------+--------------+----+------------+
| Engine     | Support | Comment                                                   | Transactions | XA | Savepoints |
+------------+---------+-----------------------------------------------------------+--------------+----+------------+
| CSV        | YES     | CSV storage engine                                        | NO           | NO | NO   |
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                     | NO           | NO | NO   |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables | NO           | NO | NO   |
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance    | NO           | NO | NO   |
+------------+---------+-----------------------------------------------------------+--------------+----+------------+
4 rows in set (0.00 sec)

If you want to use the EXAMPLE storage engine, you will have to include it while configuring your build- (well at least that is what the manual says; I could even use the EXAMPLE storage engine using my earlier configuration). The configure statement would then look like:

/configure --with-example-storage-engine

The next steps remain the same as in my earlier post.

Now, start the MySQL server daemon and connect to it using  your 'root' user for the moment (default password is blank ).

now at the mysql> prompt, type in: (for more on installing plugins, please refer the link)

mysql>use test;
mysql> INSTALL PLUGIN example SONAME 'ha_example.so';

It should return you:

Query OK, 0 rows affected (0.01 sec)

Just to cross-check, let us see the storage engines that are plugged-in to the server:

  
  
mysql> show engines;
+------------+---------+-----------------------------------------------------------+--------------+----+------------+
| Engine     | Support | Comment                                                   | Transactions | XA | Savepoints |
+------------+---------+-----------------------------------------------------------+--------------+----+------------+
| EXAMPLE    | YES     | Example storage engine                                    | NO           | NO | NO   |
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                     | NO           | NO | NO   |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables | NO           | NO | NO   |
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance    | NO           | NO | NO   |
| CSV        | YES     | CSV storage engine                                        | NO           | NO | NO   |
+------------+---------+-----------------------------------------------------------+--------------+----+------------+

Thus, the EXAMPLE storage engine is now available for use. For usage instructions, please visit this link

In this post I shall show you how you can set up a MySQL community server on your Solaris installation.

Some useful information:

• Solaris installation: Solaris 10 (32-bit SPARC)
• MySQL 5.1.24-rc is the MySQL community server I am using for the demonstration (You may also try this with MySQL 5.1.25-rc, since the older tarball is not available officially anymore)
  

You will need the following tools:

• GNU Make (gmake): I am using GNU Make 3.80
• GNU C/C++ compiler:
  
• gcc (GCC) 3.4.3 (csl-sol210-3_4-branch+sol_rpath)
• g++ (GCC) 3.4.3 (csl-sol210-3_4-branch+sol_rpath)
  

• Download the MySQL 5.1.24-rc source tarball from  http://dev.mysql.com/downloads/mysql/5.1.html
• Untar it
• Navigate to the source directory

• ./configure --prefix=/home/amit/mysql-install # you may change it to reflect the directory you want to install the server on, default is /usr/local
  
• gmake
• gmake install
  
• Now, navigate to the directory where you installed the server
• You should have the following directories under '/home/amit/mysql-install'
  

  bin         info        libexec     mysql-test  sql-bench
  
  include     lib         man         share       var

• Navigate to the 'bin' sub-directory. Run the ./mysql_install_db script which set up the initial tables
  

    
  :$ ./mysql_install_db -u mysql
  Installing MySQL system tables...
  OK
  Filling help tables...
  OK
  
  To start mysqld at boot time you have to copy
  support-files/mysql.server to the right place for your system
  
  PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
  To do so, start the server, then issue the following commands:
  
  /home/as227057/dev-tools/mysql-install/bin/mysqladmin -u root password 'new-password'
  /home/as227057/dev-tools/mysql-install/bin/mysqladmin -u root -h sr1-cblr03-11 password 'new-password'
  
  Alternatively you can run:
  /home/as227057/dev-tools/mysql-install/bin/mysql_secure_installation
  
  which will also give you the option of removing the test
  databases and anonymous user created by default.  This is
  strongly recommended for production servers.
  
  See the manual for more instructions.
  
  You can start the MySQL daemon with:
  cd /home/as227057/dev-tools/mysql-install ; /home/as227057/dev-tools/mysql-install/bin/mysqld_safe &
  
  You can test the MySQL daemon with mysql-test-run.pl
  cd /home/as227057/dev-tools/mysql-install/mysql-test ; perl mysql-test-run.pl
  
  Please report any problems with the /home/as227057/dev-tools/mysql-install/bin/mysqlbug script!
  
  The latest information about MySQL is available at http://www.mysql.com/
  Support MySQL by buying support/licenses from http://shop.mysql.com/

  
  

• Start the MySQL server daemon
The script to start the MySQL daemon lives in the libexec/ directory

/home/as227057/dev-tools/mysql-install/libexec$ ./mysqld
080611 13:59:03 [Note] Event Scheduler: Loaded 0 events
080611 13:59:03 [Note] ./mysqld: ready for connections.
Version: '5.1.24-rc'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution

• Connecting to the MySQL server

  :"/home/as227057/dev-tools/mysql-install/bin" 12 $ ./mysql
  Welcome to the MySQL monitor.  Commands end with ; or \g.
  Your MySQL connection id is 3
  Server version: 5.1.24-rc Source distribution
  
  Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
  
  mysql> show databases;
  +--------------------+
  | Database           |
  +--------------------+
  | information_schema |
  | test               |
  +--------------------+
  2 rows in set (0.01 sec)
  
  mysql>
  
  

  As you can see, the client connects to the server instance we just started in your previous step

That completes our basic setup of MySQL 5.1.24-rc community server on Solaris 10.

MySQL source tarballs

• http://dev.mysql.com/downloads/mysql/5.1.html 

References

• http://dev.mysql.com/doc/refman/5.1/en/installing-source.html