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.

Gunith D · Accepted Answer · 2017-07-06 09:01:01Z

18

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

answered Jul 6, 2017 at 9:01

Gunith D

1,8731 gold badge31 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Paul Campbell Over a year ago

This works nicely except for the debug_off() procedure which fails on the select statement when used with a trigger, as here, or with a function since neither will allow you to return a result set. Remove that (and the delete statement that follows) and you are good to go.

Devart · Accepted Answer · 2012-01-25 09:49:09Z

5

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.

edited Jan 25, 2012 at 9:49

answered Jan 25, 2012 at 9:41

Devart

120k23 gold badges172 silver badges189 bronze badges

5 Comments

Justin Over a year ago

Yea, step into is grayed out. Presumably because it's a trial?

Devart Over a year ago

The 'Step Into' command has to be active from the stored procedure code. Have you followed the step by step instruction (How To: Start Trigger Debugging)?

Justin Over a year ago

Thank you Devart! Very nice piece of software.

user3494047 Over a year ago

I have not gotten it to work! When I set a break point in my trigger (which is an after insert trigger) I debug my insert statement and I press ctrl + f5 to jump to the break point and I get that the trigger executed with an error. I just want to make sure, this implies the error is happening before my break point? And I am setting/jumping to my break point correctly?

Devart Over a year ago

You should write INSERT statement in your procedure. Then start debugging the procedure, and click Step Into on INSERT statement. Note, that trigger must be compiled before with debug info.

bortunac · Accepted Answer · 2020-08-19 09:12:50Z

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

Collectives™ on Stack Overflow

Debugging MySQL Triggers

3 Answers 3

1 Comment

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related