从 MySQL 中的存储过程打印调试信息

在 MySQL 中有没有一种方法可以将调试消息打印到标准输出(stdout)、诱惑输出(temtable)或者日志文件(logfile)中:

  • SQLServer 中的 print
  • Oracle 中的 DBMS_OUTPUT.PUT_LINE
265133 次浏览
小开

I usually create log table with a stored procedure to log to it. The call the logging procedure wherever needed from the procedure under development.

Looking at other posts on this same question, it seems like a common practice, although there are some alternatives.

小开

One workaround is just to use select without any other clauses.

http://lists.mysql.com/mysql/197901

小开

Option 1: Put this in your procedure to print 'comment' to stdout when it runs.

SELECT 'Comment';

Option 2: Put this in your procedure to print a variable with it to stdout:

declare myvar INT default 0;
SET myvar = 5;
SELECT concat('myvar is ', myvar);

This prints myvar is 5 to stdout when the procedure runs.

Option 3, Create a table with one text column called tmptable, and push messages to it:

declare myvar INT default 0;
SET myvar = 5;
insert into tmptable select concat('myvar is ', myvar);

You could put the above in a stored procedure, so all you would have to write is this:

CALL log(concat('the value is', myvar));

Which saves a few keystrokes.

Option 4, Log messages to file

select "penguin" as log into outfile '/tmp/result.txt';

There is very heavy restrictions on this command. You can only write the outfile to areas on disk that give the 'others' group create and write permissions. It should work saving it out to /tmp directory.

Also once you write the outfile, you can't overwrite it. This is to prevent crackers from rooting your box just because they have SQL injected your website and can run arbitrary commands in MySQL.

小开

Quick way to print something is:

select '** Place your mesage here' AS '** DEBUG:';
小开

This is the way how I will debug:

CREATE PROCEDURE procedure_name()
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
SHOW ERRORS;  --this is the only one which you need
ROLLBACK;
END;
START TRANSACTION;
--query 1
--query 2
--query 3
COMMIT;
END

If query 1, 2 or 3 will throw an error, HANDLER will catch the SQLEXCEPTION and SHOW ERRORS will show errors for us. Note: SHOW ERRORS should be the first statement in the HANDLER.


提交答案