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

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   |
+------------------------------------+

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.