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