lost connection with SELECT queries and DELETE in SP (WITH QUERYCACHE)

Following my previous report
( http://lists.mysql.com/bugs/15632 )
I did some more testing, and I found out that the error is replicable
only when the query cache is active.
Here goes the updated test script.

Tested with MySQL 5.0.13 on Linux (Fedora 4, Debian 3.1, Red Hat
Enterprise 3.0), FreeBSD 5.4, Windows XP.

# cut here
How to replicate:

(1) save this script as test.mysql

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

SET GLOBAL query_cache_size=16*1024*1024; ## <---

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

See my previous post for a sample output.

Regards

GM

--
Giuseppe Maxia
CTO
http://www.StarData.it
MySQL Certified Professional
__ __ __
___ / /____ ________/ /__ _/ /____ _
(_- /___/\__/\_,_/_/ \_,_/\_,_/\__/\_,_/
Database is our business

--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org