MySQL Forge

Resources for the MySQL Community

Categories: Contributing | Development | MySQLUniversity

How to Run MySQL With a Debugger

Contents 1 How to Run MySQL With a Debugger 2 Presentation 2.1 Summary 2.2 Shorten testcase 2.3 Tool 1: --debug (the DBUG library) 2.4 Tool 2: mysql-test-run.pl (=mtr) 2.5 Tool 3: look around, read code as you debug 2.6 Tool 4: debuggers (gdb, VisualStudio&cdbg, Eclipse) 2.7 Tool 5: Valgrind 2.8 Tool 6: logs 2.9 Miscellaneous tools 2.10 Combine tools 3 Questions 3.1 Questions asked before the session 3.2 Questions asked during the session 4 Voice recording and other links

← Back to MySQL University main page

[edit] How to Run MySQL With a Debugger

• Date: 2007-11-15
• Presenter: Guilhem Bichot
• Documents contributed by Miguel Solorzano and Reggie Burnett (Windows debugging), Hakan Kuecuekyilmaz (Eclipse), Serg (mysql-test-run.pl tips)
• Scribe: Jon Stephens
• Attendees (please register by filling in your name below, and read the Instructions for Attendees):
  1. Matt Lord
  2. Giuseppe Maxia
  3. Georgi Kodinov
  4. ...

[edit] Presentation

[edit] Summary

• shorten test case
• know tools (debugger etc)
• combine tools

[edit] Shorten testcase

• debugging requires repeating the test case over and over again.
• => make the test case short and easy to run
• short
  • Reduce number of queries/tables/columns/records
  • After fixing the bug in that smaller test case, check again the bigger one
• easy to run
  • mysql-test/mtr (or mysql-test/mysql-test-run.pl) sets up MySQL servers (including replication)
  • and offers a test language http://dev.mysql.com/doc/#mysqltest
  • so try to make the test case into a yourbug.test file (mysql-test/t/)
  • create an empty yourbug.result file (mysql-test/r/) and

cd mysql-test; ./mtr yourbug

• • should show bad results; after bug is fixed: good results!

[edit] Tool 1: --debug (the DBUG library)

• good for getting a helicopter view of the problem
• available only in "debug" builds ./configure --with-debug
• read its manual from dbug/user.* at least from "summary of macros" on
• mysqld's default --debug is --debug=d:t:i:o,/tmp/mysqld.trace
  • t print function's name and indent|deindent, when entering|leaving a function
  • i print thread's number
  • o print to stdout; o,file prints to file instead
  • d print lines with any tag; d,tag1 print only lines with tag tag1; in MySQL code we see tags enter, info, etc: http://forge.mysql.com/wiki/MySQL_Internals_Coding_Guidelines#DBUG_Tags
• traces are big, can be multi-GB, can exhaust memory if ./mtr --debug --mem
  • hack mtr so that even with --mem, debug trace goes to disk
  • use d,...
  • use f,...: f,func1 will print only lines from function func1 (but not other functions, and not those called by func1)
• other useful DBUG flags:
  • F print name of source file
  • L print line of source file
  • n print function call depth
  • S perform sanity memory checks when entering/leaving function
  • T print timestamp
  • O flush trace file after every line: slower but gives more info in case of crash
• DBUG library can also be used to intentionally trigger problems, e.g. crash mysqld in a certain piece of code to test recovery; look into the manual for DBUG_EXECUTE_IF.
• DBUG flags can be changed on the fly

set session debug="+d,sleep_alter_enable_indexes" # see alter_table-big.test

• "This edition of the GDB manual is dedicated to the memory of Fred Fish. Fred was a long-standing contributor to GDB and to Free software in general. We will miss him."

[edit] Tool 2: mysql-test-run.pl (=mtr)

• ./mtr --start-and-exit starts a mysqld, ready for you to throw commands at, often using

../client/mysql -u root -S var/tmp/master.sock test

• caveat: mtr disables InnoDB if the test file does not announce it needs it
  • so if you want InnoDB, call a test which has

source include/have_innodb.in

like ./mtr --start-and-exit innodb

• • same if you want binlog: call a test which has have_log_bin.inc
• add --gdb or --ddd for mtr to start mysqld in a debugger (for Windows: read on) (currently --gdb does not work on Mac OS X)
• add --client-gdb if the bug is in mysqltest
• --mem speeds up the testing cycle, use it unless it exhausts memory (currently does not work on Windows)
• mtr first creates system tables (the mysql database) and then runs tests. System tables are created by starting a separate mysqld --bootstrap and sending it statements like

CREATE TABLE IF NOT EXISTS plugin ( name char(64) ...

on its standard input.

• Serg's tip 1: if bug is very early at start of a test, mtr --gdb is useless as it breaks only at first mysql_parse(); you can modify mtr (replace break mysql_parse there by an earlier breakpoint), or use --manual-gdb, --manual-ddd, or --manual-debug, which all make mtr wait for you to start the debugger yourself (where you can put as early breakpoints as you wish) and only then the test will be run.
• Serg's tip 2: if bug is so early that the initial creation of system tables fails: the test will not be run so the above tip will be useless; but you can tell mtr to use a certain mysqld binary (--master-binary=), which will be used for system tables creation too, and this binary can in fact be a script which starts mysqldinside a debugger:

file=$0-args.$$
input=$0-input.$$
echo file /usr/home/serg/Abk/mysql/sql/mysqld > $file
echo set args "$@" "--gdb < $input" >> $file
cat >> $file <<__EOF__
b my_message
__EOF__
cat >$input
xterm -e "/usr/home/serg/Abk/mysql/libtool --mode=execute gdb -x $file"
rm $file $input

• • if you want to start mysqld standalone, unrelated to any mtr work, and want inspiration for the command-line to use, run mtr --script-debug to see how it starts mysqld

[edit] Tool 3: look around, read code as you debug

• It could save your day

[edit] Tool 4: debuggers (gdb, VisualStudio&cdbg, Eclipse)

• good for getting a microscopic view
• gdb http://www.gnu.org/software/gdb/documentation/
  • GNU debugger; Unix and Mac OS X
  • usual commands

help ("help condition"); run (=r); break (=b); continue (=c); condition; next (=n); step (=s); finish; backtrace (=bt);
up; down; until;
info break; disable break; enable break; delete break;
info threads; thread;
print (=p); whatis; ptype; display; watch; kill (=k);

• • pick a good build: default BUILD/compile-*-debug* use -O1 which makes certain variables (especially those on the stack) invisible, or refuses to break at a line through which it does pass

./mtr --mem --gdb innodb
b begin_trans
c
Breakpoint 2, begin_trans (thd=0x8eb9f80) at sql_parse.cc:136
136       if (unlikely(thd->in_sub_stmt))
(gdb) l
131
132
133     bool begin_trans(THD *thd)
134     {
135       int error=0;
136       if (unlikely(thd->in_sub_stmt))
137       {
138         my_error(ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG, MYF(0));
139         return 1;
140       }
(gdb) p error
$2 = <value optimized out>      # grrr!

• • • tip: BUILD/compile-*-debug --with-debug=full, uses -O0
    • on the other hand, compared to -O1 you lose -Wuninitialized so you don't see some warnings, which others will report to you...
  • put set print object on in your .gdbinit: class Format_description_log_event derives from class Log_event:

Breakpoint 2, Log_event::read_log_event (buf=0x89081a0 "\226)2G\017\002", 
    event_len=102, error=0xb738110c, description_event=0x8908080)
    at log_event.cc:1021
1021      DBUG_PRINT("read_event", ("%s(type_code: %d; event_len: %d)",
(gdb) p ev
$2 = (Log_event *) 0x890c6f0                     # not very helpful
(gdb) set print object on 
(gdb) p ev
$3 = (Format_description_log_event *) 0x890c6f0  # ah, thanks

• • When you start mysqld under gdb, add --gdb to its options, so that mysqld interrupts itself upon receiving certain signals, otherwise it ignores them
    • Example: you started mysqld under gdb, mysqld is already running, how to set a breakpoint? Control-C, set breakpoint, and c
  • Tired of setting the same breakpoints again and again? put them in a file and use gdb's option -x
  • Emacs/Xemacs users: Meta-x gdb <Enter>, type name of executable to debug(mysqld); starts gdb and shows code in execution

• ddd GUI wrapper over gdb, can show assembler (Source -> Display machine code)

• On some Unix platforms (Solaris...), one might find dbx installed and usable
• A dose of Windows
  • Reggie's tips for debugging with Visual Studio, windbg and cdb, combining them with mtr http://forge.mysql.com/wiki/How_to_Build_MySQL_on_Windows/Presentation#Debugging
  • Miguel's tips on windbg and cdb. They are more powerful debuggers than Visual Studio though less convenient to drive. Advantages:
    • Easily debug on remote machine using for example ssh. You need just to unzip the whole install directory without interfering with Windows Registry of the remote machine, it is a standalone install.
    • With the attach command -psn ServiceName you can attach with a server started as service, that is possible with Visual Studio but requires special procedures not so simple. Should be done in noninvasive debugging mode (-pv command line option).
    • You can find threads which are blocked waiting for a lock, with the command: -c "!locks;q" which has an output like below:

CritSec DeadLockDemo!CritSecTwo+0 at 004A0638
LockCount          1
RecursionCount     1
OwningThread       284
EntryCount         1
ContentionCount    1
*** Locked
Scanned 40 critical sections

• • • and then with command -c "~*kb;q" which scans all threads, you can see the functions involved by reading their call stack.
    • The above can be done in noninvasive mode periodically, manually or through a script, and by using the -loga option it can create a log file.
    • If you want to debug CPU utilization, you can use -c "!runaway;q" as showed in the PDF below.
    • You can easily and quickly analyze possible stack overflow, showing in a separate column the memory consumed by every function, with the command -c "~*kf;q" and repeat it periodically.
    • All the above cases can be monitored automatically using a proper script which is able to run shell commands and time control without manual intervention and different windows.
    • In more detail, with screenshots: Image:Windows MySQL Debugging with CDB.pdf
• Hakan's tips for debugging with Eclipse (Unix, Windows; requires Java) Eclipse/CDT on Linux and Mac OS X
• On Mac OS X there is the XCode integrated development environment, Brian says good things of it.

[edit] Tool 5: Valgrind

• would life be different without it? http://www.valgrind.org
• Last week I fixed two bugs by using Valgrind
• Linux (x86, x86-64, PPC32, PPC64)
• Run-time detection of
  • use of uninitialized variables inside tests or system calls => random program behaviour

{ int a;  if (a > 3) { ... } }

• • • Example in 6.0: ha_myisam::keys_with_parts initialized in ha_myisam::open() but used in ha_myisam::index_flags() which in some cases is used before ha_myisam::open().
  • reading/writing in memory locations which the thread shouldn't access (for example on another thread's stack): caused by corrupted or uninitialized pointer.
  • memory leaks (allocated memory which is not freed)

char *ptr; while(1) { ptr= malloc(100); }

• Programs run slower inside Valgrind so you need short test cases.
• Can attach gdb to mysqld at the code line where the problem materialized: --db-attach=yes
• Whenever you have a reasonably short testcase and you don't know what the bug's cause is, start with a Valgrind run. Any error it prints is a likely cause. After your fix, run Valgrind too (in case you introduced a new problem).
• ./mtr --valgrind, short list of Valgrind errors in ./var/log/warnings, full list in ./var/log/*.err
• If the above found an error, you'd like to attach gdb when the problem happens. Because of stream redirections inside mtr this is not completely obvious

[INS 17:39 /m/mysql-5.1-maint/mysql-test $] cat ~/bin/valgrind-db-attach
#! /bin/sh
# we sleep so that the window does not go away too fast
exec xterm -e "valgrind --db-attach=yes $*; sleep 36000"
[INS 17:39 /m/mysql-5.1-maint/mysql-test $] ./mtr --valgrind-path=~/bin/valgrind-db-attach alias

[edit] Tool 6: logs

• MySQL has several useful logs http://dev.mysql.com/doc/refman/5.1/en/log-files.html
  • binary log --log-bin: list of all statements which modified tables, logged when the modification happens
  • query log --log: list of all statements, logged when received (log is either in tables mysql/*log* CSV tables or in *.log plain files (depends on --log-output=)

[edit] Miscellaneous tools

• Sometimes you cannot use --debug (slowing down too much), and cannot use debugger - no good debugger works on that machine, or bug does not show up under debugger (timing dependent, or memory initialization dependent), or only non-debug, optimized release build shows the problem (excessive optimization by the compiler?) Then add

fprintf(stderr,"your debugging message here %d\n",your variable here);

and recompile.

• For excessive compiler optimizations, you may have to look at the generated assembler (gcc -S)
• If you need to look at how preprocessor symbols (macros) are expanded: gcc -E
• Or, instead of the two above, add -save-temps to gcc options (to CFLAGS and CXXFLAGS) it will not delete any intermediate files (preprocessor output, assembler): you will have all files for inspection.
• You're debugging a race condition and want to force a connection to pause for a while at some place in code so that you can issue a certain command from another connection
  • simple: add sleep(30) and recompile
  • advanced: read http://dev.mysql.com/doc/refman/5.1/en/miscellaneous-functions.html#function_get-lock and use sql/item_func.cc:debug_sync_point(): place

debug_sync_point("yourbug")

in the place to pause, and recompile; in connection2 do

SELECT GET_LOCK("yourbug", 1000);

then in connection1, start the code to pause: it will try to get the lock "bug" and so pause. To resume the paused connection, do

SELECT RELEASE_LOCK("yourbug").

[edit] Combine tools

• mysqld becomes non-responsive => what is it doing? Restart with --debug: it looks like an infinite loop. CPU can also give signs of that (top could say "100% CPU", laptop fans turning on). Why does this loop happen => restart mysqld under gdb, place breakpoints...
• Seeing Valgrind error "use of uninitialized value" in open_tables(). Valgrind gives stack trace. Re-try with --db-attach=yes. Examine the tables list in gdb, yes it looks corrupted. But it's too late to see why, need to backtrack. Where was list created? restart mysqld under gdb and put a breakpoint at the lines where the list was created. Step through lines with next and step. Find the cause. Be happy.

[edit] Questions

[edit] Questions asked before the session

[NONE]

[edit] Questions asked during the session

[NONE]

[edit] Voice recording and other links

• Voice Recording: Ogg Audio (1h 45min, 12.5 MB)
• IRC log: [IRC Log (5.5 KB)]