..bits & bytes teleported

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