调试MySQL触发器 [英] 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.
基本上，我想查看已运行的更新，删除，插入等查询.我想在终端或日志中的某处看到该查询，确切地说是MySQL执行该查询的方式和时间，可能还有任何相应的输出/错误.有想法/骇客吗?
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 ;
以下是一些其他上下文，可能有助于影响建议或答案.再说一次，我对语义/语法不那么感兴趣，而对看到MySQL运行查询更感兴趣，但是无论如何，我现在对任何事情都持开放态度.
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不起作用/不显示查询.
- 非复制环境，但是如果bin日志显示触发器语句，我肯定会设置它.
- 显示完整的进程列表"是否显示触发器执行和/或在其中执行的语句(运行perl后，我却从没有看到它们显示完整的进程列表，但我可能会错过它)?
- 常规查询日志不会显示这些查询(某些情况下不会显示错误日志).
- 我不再使用别名了.
- 创建触发器时没有语法错误.
- IF语句起作用.
- 当我将NEW值插入"test/temp"表中并手动运行更新查询时，它就可以工作了(我什至可以实际插入整个更新查询)
- 我无法向您显示查询，但正如我刚才提到的，如果有帮助，当我手动运行时它可以工作.
- 我已删除所有错误的字符，制表符，回车符，换行符等.
- 我认为MySQL套接字只会显示本地连接/数据，而不能显示MySQL内部工作.
- MyISAM，因此无法选择INNODB日志
- lsof似乎没有显示其他有用的东西.
- 我在CentOS 5.5上使用MySQL 5.0.77.
- 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.
推荐答案
您可以 dbForge Studio for MySQL 调试触发器.试用试用版.
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.
这篇关于调试MySQL触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
