Printing debug information from a stored procedure in MySQL

Question

Is there a way in MySQL to print debug messages in stdout, temptable or logfile? Something like:

• print in SQLServer
• DBMS_OUTPUT.PUT_LINE in Oracle

Answer 1

Option 1: put this in your procedure to print a “comment” to stdout when it starts.

 SELECT 'Comment'; 

Option 2: put this in your procedure to print the variable with it to stdout:

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

Sends myvar is 5 to stdout when the procedure executes.

Option 3: create a table with one text column called tmptable and click on the message:

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

You can put this in a stored procedure, so all you would need to write is the following:

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

Which saves a few keystrokes.

Option 4, Write Messages to a File

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

This team has very severe restrictions. You can only write outfile in areas on the disk that give the group of "others" the ability to create and write permissions. It should work, saving it in the / tmp directory.

Also, once you write outfile, it cannot be overwritten. This is done in order to prevent crackers from rooting your box just because they had SQL that entered your site and could run arbitrary commands in MySQL.

Answer 2

I usually create a log table with a stored procedure to log into it. Call the registration procedure, where necessary, from the developed procedure.

Looking at other posts on the same subject, this seems like normal practice, although there are several alternatives.

Answer 3

One solution is to simply use select without any other suggestions.

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