Re: OT: lost connection with SELECT queries and DELETE in SP

and by pure coincidence during this testing seems I've stumbled upon 
another bug (seems 5.0.x not ready yet for GA):
This seems to wreak havoc:
DROP TABLE t1_innodb; -- skip-inndodb option ON
ERROR 6 (HY000): Error on delete of '.\test\t1.MYI' (Errcode: 2)
-- yet *.frm file is deleted and later with innodb ON: table is just not 
there and... cant fix without CREATE TABLE statement so BACKUP early 
backup often :) Actually restoring/copying any t1.frm helps to DROP 
table t1; later but just that. Hope no important data there!

I've already posted that in separate thread.

Giuseppe Maxia wrote:
> 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
> 

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