Description:
How to repeat:
In order to repeat issue one needs:
* the latest compiled mysql-5.0 tree
* terminal with 2 consoles
1. Save attached SQL script as t1.sql with simple stored procedure;
2. Perform following steps:
First console:
--------------
#### Load stored procedure t1 with insert statement
./mysql-5.0/client/mysql test < t1.sql
Second console:
---------------
### Run mysql client
./mysql-5.0/client/mysql test
mysql>
mysql> select body from mysql.proc where name like "t1";
+---------------------------------------------------------------+
| body |
+---------------------------------------------------------------+
| BEGIN
DECLARE t INT;
insert into t1 values (1,0,1);
END |
+---------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> call t1();
Query OK, 1 row affected (0.00 sec)
mysql> select * from t1;
+------+------+------+
| s1 | s2 | s3 |
+------+------+------+
| 1 | 0 | 1 |
+------+------+------+
1 row in set (0.00 sec)
### For now all OK. Go to second stage. Don't exit from mysql client. All next
steps should be
### performed in the same session.
First console:
-------------
#### Comment insert statement in t1.sql and load SP t1 without any statement,
#### only DECLARE stmt remains.
./mysql-5.0/client/mysql test < t1.sql
Second console:
---------------
#### Check that we droped and again created table t1.
mysql> select * from t1;
Empty set (0.00 sec)mysql> select body from mysql.proc where name like "t1";
+-----------------------------+
| body |
+-----------------------------+
| BEGIN
DECLARE t INT;
END |
+-----------------------------+
1 row in set (0.00 sec)
### Call SP t1()
mysql> call t1();
Query OK, 1 row affected (0.00 sec)
mysql> select * from t1;
+------+------+------+
| s1 | s2 | s3 |
+------+------+------+
| 1 | 0 | 1 |
+------+------+------+
1 row in set (0.00 sec)
#### Table t1 again filled out with previous values. How it is possible?
#### Check that SP t1() containts only DECLARE statement
#### One can performs 'delete from t1; select * from t1; call t1()' and will see
#### the same picture. But all these strange things will last only for current
session.
#### So if one will exits from mysql client and run it again the situation will
become
#### normal and will work fine.
----------------------------------------------------------------
Stored procedure t1
----------------------------------------------------------------
drop procedure if exists t1;
drop table if exists t1;
create table t1 (s1 int, s2 int, s3 int);
delimiter |;
CREATE PROCEDURE t1()
BEGIN
DECLARE t INT;
insert into t1 values (1,0,1);
END|
delimiter ;|
