Print debugging info from stored procedure in MySQL

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.

Print debugging info from stored procedure in MySQL

Related

Recent Posts