How to obtain all executing queries from a core file

When investigating core files from crashes, one can quite easily figure out which query crashed, as we've seen.

Sometimes you want to just list all the currently executing statements, this is useful for diagnosing hangs or corruptions.

At least GDB 7 supports python macros, which can help us a lot here.   I use a core file from 5.5.27, also a non-debug build but not "stripped".   So it's a standard build made with -g allowing us to reference symbols.

I wrote a simplistic macro to iterate through mysqld's global "threads" variable.
This is what my ~./.gdbinit looks like:

set history filename gdb_history.txt
set history size 32000
set history save on
set pagination off
set logging overwrite on
set logging on
set print elements 1024
set print pretty on
set print object on
define print_thds
        set $thd = ($arg0)->first
        while ( $thd != 0 && $thd->next != 0)
             
                set $thread_id=$thd->thread_id
                set $cmd=$thd->command
                set $proc_info=$thd->proc_info ? $thd->proc_info : ""
                 set $query = $thd->query_string->string
printf "thread id: %lu\n",$thread_id print $cmd
print $proc_info
print $query.str
printf "\n\n"      
                set $thd=$thd->next
        end
end
document print_thds
  print_thds : Dumps the query_strings in a list of THD objects
end

Loading a corefile and executing the macro, see now how it works :

[sbester@fc14 mysql-advanced-5.5.27-linux2.6-x86_64]$ gdb ./bin/mysqld ./core.5376

GNU gdb (GDB) Fedora (7.2-52.fc14)

Copyright (C) 2010 Free Software Foundation, Inc.

License GPLv3+: GNU GPL version 3 or later

This is free software: you are free to change and redistribute it.

There is NO WARRANTY, to the extent permitted by law.  Type "show copying"

and "show warranty" for details.

This GDB was configured as "x86_64-redhat-linux-gnu".

For bug reporting instructions, please see:

...

SIGINT is used by the debugger.

Are you sure you want to change it? (y or n) [answered Y; input not from terminal]

Reading symbols from /home/sbester/mysql/5.5/mysql-advanced-5.5.27-linux2.6-x86_64/bin/mysqld...done.

[New Thread 5417]

[New Thread 5376]

Reading symbols from /lib/modules/2.6.35.14-106.fc14.x86_64/vdso/vdso.so...Reading symbols from /usr/lib/debug/lib/modules/2.6.35.14-106.fc14.x86_64/vdso/vdso.so.debug...done.

done.

(gdb) print_thds threads

thread id: 22

$1 = COM_QUERY

$2 = 0x9ae27c "init"

$3 = 0x7fffc801dc80 "/* 50605 */  delete  ignore from `t1` order by  ( `b`<>  null )"

thread id: 21

$4 = COM_QUERY

$5 = 0xa0aeda "update"

$6 = 0x1880850 "/* 50605   explain extended   */  insert  ignore into `t1` set `a`=`a`"

thread id: 20

$7 = COM_SLEEP

$8 = ""

$9 = 0x0

thread id: 19

$10 = COM_SLEEP

$11 = ""

$12 = 0x0

thread id: 18

$13 = COM_QUERY

$14 = 0xa10e30 "updating"

$15 = 0x7fffd800e540 "/* 50605 */  delete  ignore from `t1` where (  ( `b` )    ) >= (  ( `a` is  null  )  )"

thread id: 17

$16 = COM_SLEEP

$17 = ""

$18 = 0x0

thread id: 16

$19 = COM_QUERY

$20 = 0x9ae27c "init"

$21 = 0x7fffd0017930 "/* 50605   explain extended   */ update  ignore`t3` set `a` = 4.7  order by  (  ( `a` is  null  )   sounds like  ( `a` )   ) desc limit 8"

thread id: 15

$22 = COM_QUERY

$23 = 0x9be1e5 "Updating"

$24 = 0x7fffc8005230 "/* 50605  explain partitions  */ update `t3` set `a` = `a`  where  ( `a` )    limit 1"

thread id: 14

$25 = COM_SLEEP

$26 = ""

$27 = 0x0

thread id: 13

$28 = COM_QUERY

$29 = 0x9ae27c "init"

$30 = 0x7fffc0045e10 "/* 50605   explain extended   */ update `t1` set `b` =  'e'   order by   ( `a`  not like  `b` )   desc limit 7"

thread id: 12

$31 = COM_QUERY

$32 = 0x9be1e5 "Updating"

$33 = 0x1850f80 "/* 50605 */ update  ignore`t3` set `b` = `b`  where (   ( `b`  and  `a` )  ) = (  (  ( `a` is  null  )<> ( `a` )   ) ) limit 7"

thread id: 11

$34 = COM_QUERY

$35 = 0x9ae27c "init"

$36 = 0x7ffff0005760 "/* 50605  explain /*!50605 format= json*/  */ update  ignore`t2` set `a` = -1.4  where (   ( `a`<=  `a` )  ) < (     CONCAT(  ( `b` )   ,   ( `a`  xor   null )   ,   ( `a` is  null  )   )     ) order by   (   null )   desc limit 5"

thread id: 9

$37 = COM_QUERY

$38 = 0x9ae27c "init"

$39 = 0x7fffd8005140 "/* 50605  explain   */  delete  from `t2` order by  (  ( `b` )  = ( `b`   sounds like -4.6 )    ) desc"

thread id: 8

$40 = COM_QUERY

$41 = 0x9be1e5 "Updating"

$42 = 0x7fffdc004d40 "/* 50605  explain /*!50605 format= json*/  */ update `t1` set `a` =  'deg'   where   ( `a`<`a` )"

thread id: 7

$43 = COM_QUERY

$44 = 0x9ae27c "init"

$45 = 0x7fffd00048e0 "/* 50605   explain extended   */ update `t2` set `a` = 8  order by   ( `b`>`b` )  asc  limit 4"

thread id: 5

$46 = COM_QUERY

$47 = 0xa10e30 "updating"

$48 = 0x7fffcc034000 "/* 50605  explain /*!50605 format=traditional */  */  delete  ignore from `t3`"

thread id: 4

$49 = COM_QUERY

$50 = 0x9ae27c "init"

$51 = 0x7fffc001b8d0 "/* 50605 */  delete  from `t1` where (  (  ( `a` )  < (  ( `b` is  null  )<=   ( `a`<>  null )    ) ) )  &   (     OCTET_LENGTH(  ( `b` )     )     ) order by  ( `b` )    desc limit 7"

thread id: 3

$52 = COM_SLEEP

$53 = ""

$54 = 0x0

thread id: 2

$55 = COM_QUERY

$56 = 0x9ae27c "init"

$57 = 0x7fffc0004a90 "/* 50605  explain /*!50605 format= json*/  */ update  ignore`t3` set `b` =   null  order by   ( 1.5 )   limit 8"

thread id: 1

$58 = COM_QUERY

$59 = 0xa0aeda "update"

$60 = 0x7fffcc004a90 "/* 50605   explain extended   */  insert  ignore into `t1` set `a`=6"