lost connection with SELECT queries and DELETE in SP
lost connection with SELECT queries and DELETE in SP
am 30.09.2005 16:39:54 von Giuseppe Maxia
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
--
Giuseppe Maxia
CTO http://www.StarData.it
MySQL Certified Professional
--
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
Re: lost connection with SELECT queries and DELETE in SP
am 01.10.2005 08:23:49 von Remo Tex
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/bugs?unsub=gcdmb-bugs@m.gmane.org
Re: lost connection with SELECT queries and DELETE in SP
am 01.10.2005 08:29:34 von Remo Tex
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/bugs?unsub=gcdmb-bugs@m.gmane.org
cancel <20051001062852.26043.qmail@lists.mysql.com>
am 01.10.2005 08:40:15 von Remo Tex
This message was cancelled from within Mozilla.
--
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
Re: lost connection with SELECT queries and DELETE in SP
am 01.10.2005 08:43:29 von Remo Tex
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/bugs?unsub=gcdmb-bugs@m.gmane.org
Re: OT: lost connection with SELECT queries and DELETE in SP
am 03.10.2005 11:17:10 von Remo Tex
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/bugs?unsub=gcdmb-bugs@m.gmane.org