mysql 如何调试触发器,调试MySQL触发器

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.

解决方案

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