Debugging MySQL Triggers

Question

I love triggers for one reason - they just work. I hate triggers for one reason - when they don't work, forget about trying to debug. O the sweet frustration.

Basically, I want to see THE update, delete, insert, etc query that was ran. I want to see that query ... somewhere, in my terminal or a log, exactly how and when MySQL executes it, and possibly any corresponding output/errors. Thoughts/hacks?

I'm trying to debug an update query with a few joins and what not. My queries are much more complex but for brevity here's an example.

DELIMITER |
CREATE TRIGGER ireallyhateyourightnow AFTER UPDATE ON watch_this_table
FOR EACH ROW BEGIN
 IF (OLD.my_value != NEW.my_value) THEN
  update 
   my_table
  set 
   my_column = NEW.my_value;
 END IF;
END|
DELIMITER ;

Here is some additional context that may help influence a suggestion or answer. Again, I'm less interested in semantics/syntax and more interested in seeing MySQL run the query but by all means, I'm open to anything at this point.

• Strace does not work/show query.
• Non-replicated environment BUT if the bin logs show trigger statements I will certainly set this up.
• Does "show full processlist" show trigger execution and/or statements executed within (I never see them after running show full processlist as fast as perl can run it but I might just be missing it)?
• General query log does not show these queries (certainly not the error log).
• I'm not using aliases (anymore).
• No syntax errors when creating the trigger.
• The IF statement works.
• When I insert the NEW values into a "test/temp" table and manually run the update query it works (I've even went so far as to actually inserting the whole update query)
• I can't show you the query but as I just mentioned, it works when I run manually if that helps.
• I've removed all erroneous characters, tabs, carriage returns, newlines, etc.
• The MySQL socket would only show local connection/data but not MySQL internal workings, I think.
• MyISAM so INNODB logs aren't an option
• lsof didn't seem to show anything else to be of use.
• I'm using MySQL 5.0.77 on CentOS 5.5.

Answer 1

MYSQL PROCEDURE => incron => tail -f 'mysql_dynamic.log'

A Stored Procedure can be invoked inside a trigger but must return nothing

CREATE PROCEDURE `DYN_LOG` (IN s VARCHAR(500))
BEGIN
SELECT s into outfile '/var/spool/incron/mysql_dynamic_spool/foo_file';
DO SLEEP(.1);   // create a gap beetween multiple shuts
END

Now anywhere in a trigger you can invoke

CREATE TRIGGER `trig_name` BEFORE UPDATE ON `tb_name` 
FOR EACH ROW
BEGIN
CALL DYN_LOG(concat_ws('\t',NEW.col1,NEW.col2));
... 
// rest of the code
END

for Linux machines apt-get install incron (debian incron tutorial)

Create the folder in which mysql will inject foo_file

mkdir -m 777 /var/spool/incron/mysql_dynamic_spool
incrontab -e

and add following incron job

/var/spool/incron/mysql_dynamic_spool IN_CREATE /path/foo_file_procesor $@/$#

Create executable script "/path/foo_file_procesor"

#!/bin/sh
# // $1 is the foo_file absolute addres
body="$( cat $1 )" // read file content
rm  $1
log=/var/log/mysql_dynamic.log    // message collector
echo "`date "+%y%m%d %H:%M:%S"`\t== dyn_log ==\t$body">>$log
exit 0

Now watch the collector file

tail -f /var/log/mysql_dynamic.log 

Answer 2

You can debug triggers using dbForge Studio for MySQL. Try trial version.

There is a detailed description of the trigger debugging process in the documentation: Debugging \ Debugging Stored Routines \ How To: Start Trigger Debugging.

Answer 3

There's an alternate way of testing it by having a temporary debug table. In the example here, they create it in an own debug database.

Step 1: Create a table

DROP TABLE IF EXISTS debug;
CREATE TABLE debug (
  proc_id varchar(100) default NULL,
  debug_output text,
  line_id int(11) NOT NULL auto_increment,
  PRIMARY KEY  (line_id)
)

Step 2: Create debug SPs to fill the debug table

DELIMITER $$

DROP PROCEDURE IF EXISTS `debug_insert` $$
CREATE PROCEDURE `debug_insert`(in p_proc_id varchar(100),in p_debug_info text)
begin
  insert into debug (proc_id,debug_output)
  values (p_proc_id,p_debug_info);
end $$

DROP PROCEDURE IF EXISTS `debug_on` $$
CREATE PROCEDURE `debug_on`(in p_proc_id varchar(100))
begin
  call debug_insert(p_proc_id,concat('Debug Started :',now()));
end $$

DROP PROCEDURE IF EXISTS `debug_off` $$
CREATE PROCEDURE `debug_off`(in p_proc_id varchar(100))
begin
  call debug_insert(p_proc_id,concat('Debug Ended :',now()));
  select debug_output from debug where proc_id = p_proc_id order by line_id;
  delete from debug where proc_id = p_proc_id;
end $$

Step 3: Invoke the debug SPs in your trigger

Like this,

CREATE PROCEDURE test_debug()
begin
declare l_proc_id varchar(100) default 'test_debug';
  call debug_on(l_proc_id);
  call debug_insert(l_proc_id,'Testing Debug');
  call debug_off(l_proc_id);
end $$

As a result the debug table would be filled as follows,

+------------------------------------+
| debug_output                       |
+------------------------------------+
| Debug Started :2006-03-24 16:10:33 |
| Testing Debug                      |
| Debug Ended :2006-03-24 16:10:33   |
+------------------------------------+