Question 20464 in Mysql Question posted in
The official MySQL documentation can be found here.

mysql procedure should return only status to unix script

I have a mysql procedure which has out parameter and some print statements for debugging.
I am calling this procedure from Unix shell script, idea is to assign the out parameter to a unix variable, which i use to check in later conditions. But the variable is getting the print statements also getting assigned. I want to get rid of print statements and just only the out parameter (integer) assigned to the variable.

Unix script:

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

giving print statements as well

proc started
proc ended
1043

When i run the code in sql client like DBeaver.. i get only the out parameter..

call delete_me(@out);select @out

@out 
----
1043

I don’t want to suppress print/debug statements, as they are helpful in my logs. but i don’t want them in mysql out variable and ultimately in my unix variable as well.

here is mysql procedure

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;
Tags:

2

Answers


  1. 0

    Assumptions:

    • debug statements and select @out are displayed on separate lines (as with OP’s original question)
    • need to append all but the last line to a log file
    • last line is to be saved in a variable

    We’ll start with a copy of OP’s current method of capturing all output to a variable:

    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 all but the last line to a log file:

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

# sed alternative:

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

    To capture the last line to a new variable:

    $ output_var="$(printf "%sn" "${all_output}" | tail -1)"
$ printf "%sn" "${output_var}"
1043
    Login or Signup to reply.
  2. 0

    Since the "debug" statements are outputting to stdout, and the select @out; is also outputting to stdout, there’s no way the MySQL client can tell them apart. There’s no output-redirection feature for procedures.

    So you have to think creatively.

    One idea would be to make sure your debugging statements have some common pattern so you can filter it out. For example, use the word "#DEBUG" consistently.

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

    Then filter it when you call 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 your debug statements only output conditionally.

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

    So you can call it this way if you want debug output:

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

    And just not set @debug=1 if you don’t want debug output.

    The bottom line is that MySQL stored procedures are really inconvenient in many cases. No real debugger, no packages, no compiler, no standard library, no atomic deployment, etc. I’ve been using MySQL for many years, but I have hardly ever seen a good use for procedures.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search