Re: lost connection with SELECT queries and DELETE in SP

Sorry it was my mistake. First time I ran server with "skip-innodb" 
option on... strange why mysql defaults to MyISAM without a error or 
warnig or.. whatever?
Now results without "skip-innodb" :-) ...still works :(
MySQL server:  5.0.13 binary distribution :
  SELECT VERSION();
  5.0.13-rc-nt-log
OS: Windows 2000

# --- And resulting output within innodb:

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>

# -- and tables were InnoDB :

show create table t1;
CREATE TABLE `t1` (
   `id1` int(11) NOT NULL,
   `c1` char(10) default NULL,
   PRIMARY KEY  (`id1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

show create table t2;
CREATE TABLE `t2` (
   `id2` int(11) NOT NULL,
   `c2` char(10) default NULL,
   `id1` int(11) NOT NULL,
   PRIMARY KEY  (`id2`),
   KEY `id1` (`id1`),
   CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`id1`) REFERENCES `t1` (`id1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1


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]