CODEWITHSUNDEEP
mysqltriggers
Justin
Justin

Reputation: 295

Debugging MySQL Triggers

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.

Upvotes: 23

Views: 35493

Answers (3)

bortunac
bortunac

Reputation: 4828

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

Upvotes: 0

Gunith D
Gunith D

Reputation: 1873

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

Upvotes: 18

Devart
Devart

Reputation: 122002

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.

Upvotes: 5

Related Questions