lost connection with SELECT queries and DELETE in SP

Description:
Using a mix of SELECT statements and DELETE commands
on InnoDB tables will result in a lost connection, unless explicitly
using autocommit=0.

MySQL server:  5.0.13 binary distribution (standard and max).
OS:            Linux (Fedora 4 and Debian 3.1)

How to replicate:

(1) save this script as test.mysql

# ---- CUT HERE -----
create database if not exists test;
use test;

drop table if exists t2;
drop table if exists t1;

create table t1 (
     id1 int not null primary key,
     c1 char(10)
) ENGINE = INNODB DEFAULT CHARSET = LATIN1;

create table t2 (
     id2 int not null primary key,
     c2 char(10),
     id1 int not null,
     key (id1),
     FOREIGN KEY (id1) REFERENCES t1 (id1)
) ENGINE = INNODB DEFAULT CHARSET = LATIN1;

INSERT INTO t1 (id1, c1) values
(1, 'aaa'), (2, 'bbb'), (3, 'ccc'),
(4, 'ddd'), (5, 'eee');

INSERT INTO t2 (id2,c2, id1) VALUES
(1001, 'nnnn', 1), (1002, 'oooo', 1),
(1003, 'pppp', 2), (1004, 'qqqq', 2), (1005, 'rrrr', 2),
(1006, 'ssss', 3), (1007, 'tttt', 3),
(1008, 'uuuu', 4);

select * from t1;
select * from t2;

SELECT "TABLES READY -- CREATING STORED PROCEDURE" AS DIAGNOSTICS;

delimiter //

drop procedure if exists delete_t1 //

create procedure delete_t1(which_id int)
DETERMINISTIC MODIFIES SQL DATA
BEGIN
     SELECT 't2' AS 'table', COUNT(*) FROM t2;
     DELETE FROM t2 WHERE id1 = which_id;
     SELECT 't2' AS 'table', COUNT(*) FROM t2;
     SELECT 't1' AS 'table', COUNT(*) FROM t1;
     DELETE FROM t1 WHERE id1 = which_id;
     SELECT 't1' AS 'table', COUNT(*) FROM t1;
END //
delimiter ;

SELECT "STORED PROCEDURE READY -- NOW TESTING" AS DIAGNOSTICS;

select "following call (1) should succeed" AS DIAGNOSTICS;
call delete_t1(1);  -- this one will succeed
COMMIT;
select "previous call (1) should have succeeded" AS DIAGNOSTICS;
select "following call (2a) should fail" AS DIAGNOSTICS;
call delete_t1(2);  -- this one will fail
connect ; -- reconnect after failure
select "previous call (2a) should have failed" AS DIAGNOSTICS;
select "following call (2b) should succeed" AS DIAGNOSTICS;
set autocommit = 0;
call delete_t1(2);  -- this one will succeed
COMMIT;
select "previous call (2b) should have succeeded" AS DIAGNOSTICS;
# ---- CUT HERE

(2) run the script as
mysql --force -t < test.mysql

(3) You should see output as follows:
$ mysql -t --force < test.mysql
+-----+------+
| id1 | c1   |
+-----+------+
|   1 | aaa  |
|   2 | bbb  |
|   3 | ccc  |
|   4 | ddd  |
|   5 | eee  |
+-----+------+
+------+------+-----+
| id2  | c2   | id1 |
+------+------+-----+
| 1001 | nnnn |   1 |
| 1002 | oooo |   1 |
| 1003 | pppp |   2 |
| 1004 | qqqq |   2 |
| 1005 | rrrr |   2 |
| 1006 | ssss |   3 |
| 1007 | tttt |   3 |
| 1008 | uuuu |   4 |
+------+------+-----+
+-------------------------------------------+
| DIAGNOSTICS                               |
+-------------------------------------------+
| TABLES READY -- CREATING STORED PROCEDURE |
+-------------------------------------------+
+---------------------------------------+
| DIAGNOSTICS                           |
+---------------------------------------+
| STORED PROCEDURE READY -- NOW TESTING |
+---------------------------------------+
+-----------------------------------+
| DIAGNOSTICS                       |
+-----------------------------------+
| following call (1) should succeed |
+-----------------------------------+
+-------+----------+
| table | COUNT(*) |
+-------+----------+
| t2    |        8 |
+-------+----------+
+-------+----------+
| table | COUNT(*) |
+-------+----------+
| t2    |        6 |
+-------+----------+
+-------+----------+
| table | COUNT(*) |
+-------+----------+
| t1    |        5 |
+-------+----------+
+-------+----------+
| table | COUNT(*) |
+-------+----------+
| t1    |        4 |
+-------+----------+
+-----------------------------------------+
| DIAGNOSTICS                             |
+-----------------------------------------+
| previous call (1) should have succeeded |
+-----------------------------------------+
+---------------------------------+
| DIAGNOSTICS                     |
+---------------------------------+
| following call (2a) should fail |
+---------------------------------+
ERROR 2013 (HY000) at line 58: Lost connection to MySQL server during query
+---------------------------------------+
| DIAGNOSTICS                           |
+---------------------------------------+
| previous call (2a) should have failed |
+---------------------------------------+
+------------------------------------+
| DIAGNOSTICS                        |
+------------------------------------+
| following call (2b) should succeed |
+------------------------------------+
+-------+----------+
| table | COUNT(*) |
+-------+----------+
| t2    |        3 |
+-------+----------+
+-------+----------+
| table | COUNT(*) |
+-------+----------+
| t2    |        3 |
+-------+----------+
+-------+----------+
| table | COUNT(*) |
+-------+----------+
| t1    |        4 |
+-------+----------+
+-------+----------+
| table | COUNT(*) |
+-------+----------+
| t1    |        3 |
+-------+----------+
+------------------------------------------+
| DIAGNOSTICS                              |
+------------------------------------------+
| previous call (2b) should have succeeded |
+------------------------------------------+

Regards

GM

-- 
Giuseppe Maxia
CTO http://www.StarData.it
MySQL Certified Professional

-- 
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe:    http://lists.mysql.com/[email protected]