MySQL Bugs: #5997: Stored Procedure with 'OUT' param takes over other procedures' statement

Bug #5997	Stored Procedure with 'OUT' param takes over other procedures' statement
Submitted:	9 Oct 2004 0:45	Modified:	28 Oct 2004 1:05
Reporter:	Shuichi Tamagawa	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.2-alpha-debug-log	OS:	Linux (SuSE Linux 9.0)
Assigned to:		CPU Architecture:	Any

Description:
If you have a stored procedure with 'OUT' parameter and create new procedures (with 'OUT parameter), the SQL statement of the first procedure is used for all the new procedures after  that.

How to repeat:
Use the attached test case file.

Suggested fix:
The SQL statement stored in each procedure should be used.

Test case file to reproduce the problem

Attachment: sp.test (application/octet-stream, text), 696 bytes.

Hi Shuichi,

I can't repeat or I've misunderstood the bug report testing on latest BK source:

miguel@hegel:~/dbs/5.0$ bin/mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.2-alpha-debug-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create database test2;
Query OK, 1 row affected (0.03 sec)

mysql> use test2;
Database changed
mysql> DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> DROP TABLE IF EXISTS t2;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> DROP PROCEDURE IF EXISTS sp1;
Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql> DROP PROCEDURE IF EXISTS sp2
    -> ;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE t1(c1 int);
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE t2(c2 int);
Query OK, 0 rows affected (0.01 sec)

mysql> 
mysql> INSERT INTO t1 VALUES(1);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t2 VALUES(2);
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> CREATE PROCEDURE sp1(OUT x1 int)
    ->   SELECT * INTO x1 FROM t1;
Query OK, 0 rows affected (0.01 sec)

mysql> CALL sp1(@var1);
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> SELECT @var1;
+-------+
| @var1 |
+-------+
| 1     |
+-------+
1 row in set (0.00 sec)

mysql> CREATE PROCEDURE sp2(OUT x2 int)
    ->   SELECT * INTO x2 FROM t2;
Query OK, 0 rows affected (0.00 sec)

mysql> CALL sp2(@var2);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @var2;
+-------+
| @var2 |
+-------+
| 2     |
+-------+
1 row in set (0.00 sec)

The result on my current environment, which was installed on Sep 28 from source tree,  is as follows. I'll try the latest version and let you know the result.

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.0.2-alpha-debug-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> source sp.test

<cut>

Query OK, 0 rows affected (0.01 sec)

+-------+
| @var1 |
+-------+
| 1     |
+-------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.02 sec)

+-------+
| @var2 |
+-------+
| 1     |
+-------+
1 row in set (0.00 sec)

<cut>

+-----------+----------+--------------------------------------------------------------------+
| Procedure | sql_mode | Create Procedure                                                   |
+-----------+----------+--------------------------------------------------------------------+
| sp1       |          | CREATE PROCEDURE `test`.`sp1`(OUT x1 int)
SELECT * INTO x1 FROM t1 |
+-----------+----------+--------------------------------------------------------------------+
1 row in set (0.00 sec)

+-----------+----------+--------------------------------------------------------------------+
| Procedure | sql_mode | Create Procedure                                                   |
+-----------+----------+--------------------------------------------------------------------+
| sp2       |          | CREATE PROCEDURE `test`.`sp2`(OUT x1 int)
SELECT * INTO x1 FROM t1 |
+-----------+----------+--------------------------------------------------------------------+
1 row in set (0.00 sec)

Hi Miguel,

I tested on the latest bk version, but got the same result.
So I would like to know the difference between you and me.

I'm using gcc3.3.1-29 for compiler.
And I used the following shell script to run ./configure. 

VERSION="50020"
PREFIX="/usr/local/mysql/$VERSION"
HANDLERS="--with-berkeley-db"
OTHER="--enable-assembler --enable-thread-safe-client --enable-local-infile --disable-shared --with-client-ldflags=-all-static --with-mysqld-ldflags=-all-static --with-debug --with-charset=ujis --with-extra-charsets=all --without-readline --without-libedit --without-docs"

rm -f config.cache

CFLAGS="-O3 -mpentiumpro" CXX=gcc CXXFLAGS="-O3 -mpentiumpro -felide-constructors -fno-exceptions -fno-rtti"
./configure \
        --prefix=$PREFIX \
        --localstatedir=$PREFIX/data \
        --with-unix-socket-path=$PREFIX/mysql.sock \
        --with-tcp-port=$VERSION \
        $HANDLERS $OTHER

What is yours?

Hi Shuichi,

Here you asked me:

miguel@hegel:~/dbs/5.0$ gcc --version
gcc (GCC) 3.3.4
Copyright (C) 2003 Free Software Foundation, Inc.

miguel@hegel:~/dbs/mysql-5.0$ BUILD/compile-pentium-debug-max --prefix=/home/miguel/dbs/5.0/

I tested with a source tree pulled some few hours before the test:
at 2004-10-08.

Today I did a  new pull/build and here the results:

miguel@hegel:~/dbs/5.0$ bin/mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.2-alpha-debug-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create database test5;
Query OK, 1 row affected (0.00 sec)

mysql> use test5;
Database changed
mysql> DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>  DROP TABLE IF EXISTS t2;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> DROP PROCEDURE IF EXISTS sp1;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> DROP PROCEDURE IF EXISTS sp2;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE t1(c1 int);
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE t2(c2 int);
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t1 VALUES(1);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t2 VALUES(2);
Query OK, 1 row affected (0.01 sec)

mysql> CREATE PROCEDURE sp1(OUT x1 int)
    -> SELECT * INTO x1 FROM t1;
Query OK, 0 rows affected (0.02 sec)

mysql>  CALL sp1(@var1);
Query OK, 0 rows affected (0.05 sec)

mysql> SELECT @var1;
+-------+
| @var1 |
+-------+
| 1     |
+-------+
1 row in set (0.00 sec)

mysql> CREATE PROCEDURE sp2(OUT x2 int)
    -> SELECT * INTO x2 FROM t2;
Query OK, 0 rows affected (0.00 sec)

mysql> CALL sp2(@var2);
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT @var2;
+-------+
| @var2 |
+-------+
| 2     |
+-------+
1 row in set (0.00 sec)

mysql>

Hi Miguel,

I figured out that this happens when compiled with '--with-charset' option and multi-byte character set is specified.

How to repeat:

Add '--with-charset=utf8' (or ujis) to the file BUILD/compile-pentium-debug-max.
Run BUILD/compile-pentium-debug-max.

This is repeatable. Please see my previous comment.

Hi Shuichi,

Just now I begin to compile the latest source according you mentioned.
I will back with what I will find.

Thanks

Hi Shuichi,

I compiled the server like you pointed, however I wasn't able
to repeat the behaivior reported.

Hi.

I have the same problem on 5.0.3-alpha-nightly-20050216-debug, SuSE Linux 9.2.It seems that the first procedure takes over the procedure created after that regardless of the parameter type. This happens only when I complied from source using --with-charset=ujis option.

mysql>create procedure sp1()
    -> begin
    -> select * from t1;
    -> end//
Query OK, 0 rows affected (0.00 sec)

mysql>create procedure sp2()
    -> begin
    -> select * from t2;
    -> end//
Query OK, 0 rows affected (0.00 sec)

mysql>show create procedure sp1\G
*************************** 1. row ***************************
       Procedure: sp1
        sql_mode:
Create Procedure: CREATE PROCEDURE `test`.`sp1`()
begin
select * from t1;
end
1 row in set (0.00 sec)

mysql>show create procedure sp2\G
*************************** 1. row ***************************
       Procedure: sp2
        sql_mode:
Create Procedure: CREATE PROCEDURE `test`.`sp2`()
begin
select * from t1;
end
1 row in set (0.00 sec)

The SQL statement in the sp2 should be 'select * from t2'.