Description:
This report is based on message http://lists.mysql.com/bugs/15639 posted by Giuseppe Maxia.
Without the query cache enabled, the following sequence of actions gives the expected results:
mysql> set global query_cache_size=0;
Query OK, 0 rows affected (0,01 sec)
mysql> create table t1 (
-> id1 int not null primary key,
-> c1 char(10)
-> ) ENGINE = INNODB DEFAULT CHARSET = LATIN1;
Query OK, 0 rows affected (0,05 sec)
mysql> 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;
Query OK, 0 rows affected (0,06 sec)
mysql> INSERT INTO t1 (id1, c1) values
-> (1, 'aaa'), (2, 'bbb'), (3, 'ccc'),
-> (4, 'ddd'), (5, 'eee');
Query OK, 5 rows affected (0,01 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> 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);
Query OK, 8 rows affected (0,01 sec)
Records: 8 Duplicates: 0 Warnings: 0
mysql> delimiter //
mysql> 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 //
Query OK, 0 rows affected (0,03 sec)
mysql> delimiter ;
mysql> call delete_t1(1);
+-------+----------+
| table | COUNT(*) |
+-------+----------+
| t2 | 8 |
+-------+----------+
1 row in set (0,00 sec)
+-------+----------+
| table | COUNT(*) |
+-------+----------+
| t2 | 6 |
+-------+----------+
1 row in set (0,04 sec)
+-------+----------+
| table | COUNT(*) |
+-------+----------+
| t1 | 5 |
+-------+----------+
1 row in set (0,04 sec)
+-------+----------+
| table | COUNT(*) |
+-------+----------+
| t1 | 4 |
+-------+----------+
1 row in set (0,09 sec)
Query OK, 0 rows affected (0,09 sec)
mysql> commit;
Query OK, 0 rows affected (0,00 sec)
mysql> call delete_t1(2);
+-------+----------+
| table | COUNT(*) |
+-------+----------+
| t2 | 6 |
+-------+----------+
1 row in set (0,00 sec)
+-------+----------+
| table | COUNT(*) |
+-------+----------+
| t2 | 3 |
+-------+----------+
1 row in set (0,00 sec)
+-------+----------+
| table | COUNT(*) |
+-------+----------+
| t1 | 4 |
+-------+----------+
1 row in set (0,01 sec)
+-------+----------+
| table | COUNT(*) |
+-------+----------+
| t1 | 3 |
+-------+----------+
1 row in set (0,04 sec)
Query OK, 0 rows affected (0,04 sec)
mysql> exit
Bye
But if query cache is enabled:
[openxs@Fedora 5.0]$ bin/mysql -uroot
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.14-rc
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> SET GLOBAL query_cache_size=16*1024*1024;
Query OK, 0 rows affected (0,00 sec)
mysql> drop table if exists t2;
Query OK, 0 rows affected (0,04 sec)
mysql> drop table if exists t1;
Query OK, 0 rows affected (0,02 sec)
mysql> create table t1 (
-> id1 int not null primary key,
-> c1 char(10)
-> ) ENGINE = INNODB DEFAULT CHARSET = LATIN1;
Query OK, 0 rows affected (0,05 sec)
mysql> 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;
Query OK, 0 rows affected (0,06 sec)
mysql> INSERT INTO t1 (id1, c1) values
-> (1, 'aaa'), (2, 'bbb'), (3, 'ccc'),
-> (4, 'ddd'), (5, 'eee');
Query OK, 5 rows affected (0,01 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> 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);
Query OK, 8 rows affected (0,01 sec)
Records: 8 Duplicates: 0 Warnings: 0
mysql> call delete_t1(1);
+-------+----------+
| table | COUNT(*) |
+-------+----------+
| t2 | 8 |
+-------+----------+
1 row in set (0,01 sec)
+-------+----------+
| table | COUNT(*) |
+-------+----------+
| t2 | 6 |
+-------+----------+
1 row in set (0,01 sec)
+-------+----------+
| table | COUNT(*) |
+-------+----------+
| t1 | 5 |
+-------+----------+
1 row in set (0,01 sec)
+-------+----------+
| table | COUNT(*) |
+-------+----------+
| t1 | 4 |
+-------+----------+
1 row in set (0,05 sec)
Query OK, 0 rows affected (0,05 sec)
mysql> commit;
Query OK, 0 rows affected (0,00 sec)
mysql> select * from t2;
+------+------+-----+
| id2 | c2 | id1 |
+------+------+-----+
| 1003 | pppp | 2 |
| 1004 | qqqq | 2 |
| 1005 | rrrr | 2 |
| 1006 | ssss | 3 |
| 1007 | tttt | 3 |
| 1008 | uuuu | 4 |
+------+------+-----+
6 rows in set (0,00 sec)
mysql> call delete_t1(2);
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> connect;
Connection id: 3
Current database: test
mysql> call delete_t1(2);
+-------+----------+
| table | COUNT(*) |
+-------+----------+
| t2 | 3 |
+-------+----------+
1 row in set (0,01 sec)
+-------+----------+
| table | COUNT(*) |
+-------+----------+
| t2 | 3 |
+-------+----------+
1 row in set (0,01 sec)
ERROR 2013 (HY000): Lost connection to MySQL server during query
So, there is still some problem... ("Transactional InnoDB tables that have been changed are invalidated when a COMMIT is performed.")
mysql> connect;
Connection id: 4
Current database: test
mysql> set autocommit=0;
Query OK, 0 rows affected (0,00 sec)
mysql> call delete_t1(2);
+-------+----------+
| table | COUNT(*) |
+-------+----------+
| t2 | 3 |
+-------+----------+
1 row in set (0,01 sec)
+-------+----------+
| table | COUNT(*) |
+-------+----------+
| t2 | 3 |
+-------+----------+
1 row in set (0,01 sec)
+-------+----------+
| table | COUNT(*) |
+-------+----------+
| t1 | 3 |
+-------+----------+
1 row in set (0,01 sec)
+-------+----------+
| table | COUNT(*) |
+-------+----------+
| t1 | 3 |
+-------+----------+
1 row in set (0,01 sec)
Query OK, 0 rows affected (0,01 sec)
The following BK changeset was used:
ChangeSet@1.2030, 2005-09-30 15:56:01+03:00, marko@hundin.mysql.fi
How to repeat:
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
Suggested fix:
Something should be changed in query cache algorithms for SPs to work properly.
