mysql procedures should only return status to unix scripts

Question

I have a mysql program that has out parameters and some print statements for debugging. I'm calling this procedure from a Unix shell script and the idea is to assign the out parameter to a unix variable which I use to check the condition later. But the variable is getting the print statement to be assigned as well. I want to get rid of the print statement and just assign the output parameters (integers) to variables.

Unix script:

output_var=`mysql -sN test_db -e "call delete_me(@outs);select @outs"`
echo $output_var

Also gives print statements

proc started
proc ended
1043

When I run the code in a SQL client like DBeaver..I only get the output parameters..

call delete_me(@out);select @out

@out 
----
1043

I don't want to disable print/debug statements as they are helpful for my logs. But I don't want them to appear in mysql out variables and ultimately my unix variables as well.

This is mysql program

CREATE PROCEDURE `delete_me`(out success int)
BEGIN
    
    DECLARE var1 INT;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        SHOW ERRORS;  
        ROLLBACK; 
        SET success=1;
    END;
    START TRANSACTION;
  
    select max(run_id) into var1 from job_run;
    
    select 'proc started';
    
    select 'proc ended';
    
    SET success:= var1;
    
END;

Answer 1

Since the "debug" statement outputs to stdout, and select @out; also outputs to stdout, the MySQL client cannot distinguish between them. The process does not have output redirection functionality.

So you have to think creatively.

One idea is to make sure your debug statements have some common pattern so that you can filter them out. For example, use the word "#DEBUG" consistently.

...
select '#DEBUG: proc started';
...

Then filter it when calling it from Bash:

output_var=$(mysql -sN test_db 
  -e "call delete_me(@outs); select @outs" 
  | grep -v '#DEBUG')

Another idea is to change the code so that the debug statements are only output conditionally.

if @debug then
  select 'proc started';
end if;

So if you want debug output, you can call it like this:

output_var=$(mysql -sN test_db 
  -e "set @debug=1; call delete_me(@outs); select @outs")

If you do not require debug output, do not set @debug=1.

The most important thing is that MySQL stored procedures are really inconvenient in many situations. No real debugger, no packages, no compiler, no standard library, no atomic deployment, etc. I've been using MySQL for many years, but I've seen very few good uses for the program.

Answer 2

Assumption:

• Debug statements and select @out are shown on separate lines (as in the OP's original question)
• Need to append everything but the last line to the log file
• The last line will be saved in the variable

We'll start with a copy of the OP's current method of capturing all output to variables:

all_output=$(mysql -sN test_db -e "call load_procedure(@out);select @out")

# for demo purposes I'll use:

all_output='proc started
proc ended
1043'

To append everything but the last line to the log file:

$ printf "%s\n" "${all_output}" | head -n -1 >> logfile
$ cat logfile
proc started
proc ended

# sed alternative:

$ printf "%s\n" "${all_output}" | sed '$d' >> logfile
$ cat logfile
proc started
proc ended

To capture the last line into a new variable:

$ output_var="$(printf "%s\n" "${all_output}" | tail -1)"
$ printf "%s\n" "${output_var}"
1043