Re: lost connection with SELECT queries and DELETE in SP

Sorry it was my mistake.  I ran server with "skip-innodb" option on... 
strange why mysql defaults to MyISAM without a error or warnig or.. 
whatever?

Remo Tex wrote:
> well... Works for me:
> MySQL server:  5.0.13 binary distribution :
>  SELECT VERSION();
>  5.0.13-rc-nt-log
> OS: Windows 2000
> Here are results:
> 
> C:\mysql-5.0.13-rc-win32\bin>mysql -u admin -p < test.mysql
> Enter password: *******
> 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
> table   COUNT(*)
> t2      6
> table   COUNT(*)
> t2      3
> table   COUNT(*)
> t1      4
> table   COUNT(*)
> t1      3
> DIAGNOSTICS
> previous call (2a) should have failed
> DIAGNOSTICS
> following call (2b) should succeed
> table   COUNT(*)
> t2      3
> table   COUNT(*)
> t2      3
> table   COUNT(*)
> t1      3
> table   COUNT(*)
> t1      3
> DIAGNOSTICS
> previous call (2b) should have succeeded
> 
> C:\mysql-5.0.13-rc-win32\bin>
> 
> 
> 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]