调试 MySQL 触发器
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9000255/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Debugging MySQL Triggers
提问by Justin
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?
基本上，我想查看运行的更新、删除、插入等查询。我想在我的终端或日志中的某个地方看到该查询，确切地了解 MySQL 执行它的方式和时间，以及可能的任何相应的输出/错误。想法/黑客？
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.
以下是一些可能有助于影响建议或答案的附加上下文。同样，我对语义/语法不太感兴趣，而对看到 MySQL 运行查询更感兴趣，但无论如何，我现在对任何事情都持开放态度。
- 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.
- Strace 不起作用/显示查询。
- 非复制环境但是如果 bin 日志显示触发器语句，我肯定会设置它。
- “显示完整进程列表”是否显示触发器执行和/或在其中执行的语句（在运行 show full processlist 后我再也看不到它们，就像 perl 可以运行它一样快，但我可能只是错过了它）？
- 一般查询日志不显示这些查询（当然不是错误日志）。
- 我不使用别名（不再）。
- 创建触发器时没有语法错误。
- IF 语句有效。
- 当我将新值插入“测试/临时”表并手动运行更新查询时，它可以工作（我什至实际上插入了整个更新查询）
- 我无法向您展示查询，但正如我刚刚提到的，如果有帮助，它可以在我手动运行时起作用。
- 我已经删除了所有错误的字符、制表符、回车符、换行符等。
- 我认为 MySQL 套接字只会显示本地连接/数据，而不会显示 MySQL 内部工作。
- MyISAM 所以 INNODB 日志不是一个选项
- lsof 似乎没有显示任何其他有用的东西。
- 我在 CentOS 5.5 上使用 MySQL 5.0.77。
采纳答案by Devart
You can debug triggersusing dbForge Studio for MySQL. Try trial version.
您可以使用dbForge Studio for MySQL调试触发器。试用试用版。
There is a detailed description of the trigger debugging process in the documentation: Debugging \ Debugging Stored Routines \ How To: Start Trigger Debugging.
文档中有对触发器调试过程的详细描述：调试\调试存储例程\如何：启动触发器调试。
回答by Gunith D
There's an alternate way of testing it by having a temporary debugtable. In the example here, they create it in an own debugdatabase.
有一种替代方法可以通过临时debug表来测试它。在这里的示例中，他们在自己的debug数据库中创建它。
Step 1:Create a table
第 1 步：创建表
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
步骤 2：创建调试 SP 以填充调试表
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
步骤 3：调用触发器中的调试 SP
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 |
+------------------------------------+
回答by bortunac
MYSQL PROCEDURE => incron => tail -f 'mysql_dynamic.log'
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)
适用于 Linux 机器apt-get install incron（debian incron 教程）
Create the folder in which mysql will inject foo_file
创建 mysql 将注入 foo_file 的文件夹
mkdir -m 777 /var/spool/incron/mysql_dynamic_spool
incrontab -e
and add following incron job
并添加以下 incron 作业
/var/spool/incron/mysql_dynamic_spool IN_CREATE /path/foo_file_procesor $@/$#
Create executable script "/path/foo_file_procesor"
创建可执行脚本“/path/foo_file_processor”
#!/bin/sh
# // is the foo_file absolute addres
body="$( cat )" // read file content
rm
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
