[MDEV-13246] Stale rows despite ON DELETE CASCADE constraint - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.2.2
Fix Version/s: 10.2.8
Component/s: Storage Engine - InnoDB
Labels:
- regression
Environment:
Server version: 10.2.6-MariaDB-10.2.6+maria~stretch-log mariadb.org binary distribution

OS: Debian Stretch

Description

While investigating database errors logged in my application, I noticed that some records that reference a different table with an ON DELETE CASCADE constraint had not been deleted. This constraint normally works, this is the first time I've spotted stale rows.

The queries below show stale rows in matchmaking_group_users and matchmaking_group_maps. They both have a matchmaking_group_id that references the column id in the table matchmaking_groups.

MariaDB [esportal]> select * from matchmaking_group_users where matchmaking_group_id not in (select id from matchmaking_groups);

+----------------------+-----------+------------+

| matchmaking_group_id | user_id   | microphone |

+----------------------+-----------+------------+

|               278066 | 182715729 |          1 |

+----------------------+-----------+------------+

1 row in set (0.00 sec)

MariaDB [esportal]> select * from matchmaking_group_maps where matchmaking_group_id not in (select id from matchmaking_groups);

+----------------------+--------+----------+

| matchmaking_group_id | map_id | priority |

+----------------------+--------+----------+

|               278066 |      1 |        4 |

|               278066 |      2 |        2 |

|               278066 |      5 |        1 |

|               278066 |      6 |        3 |

|               278066 |      7 |        6 |

|               278066 |      8 |        5 |

+----------------------+--------+----------+

6 rows in set (0.00 sec)

MariaDB [esportal]> show create table matchmaking_group_users;

+-------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Table                   | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |

+-------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| matchmaking_group_users | CREATE TABLE `matchmaking_group_users` (

  `matchmaking_group_id` bigint(20) unsigned NOT NULL,

  `user_id` int(10) unsigned NOT NULL,

  `microphone` tinyint(1) unsigned NOT NULL,

  PRIMARY KEY (`matchmaking_group_id`,`user_id`),

  UNIQUE KEY `user_id` (`user_id`),

  CONSTRAINT `matchmaking_group_users_ibfk_1` FOREIGN KEY (`matchmaking_group_id`) REFERENCES `matchmaking_groups` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,

  CONSTRAINT `matchmaking_group_users_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

+-------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

MariaDB [esportal]> show create table matchmaking_group_maps;

+------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Table                  | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                          |

+------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| matchmaking_group_maps | CREATE TABLE `matchmaking_group_maps` (

  `matchmaking_group_id` bigint(20) unsigned NOT NULL,

  `map_id` tinyint(2) unsigned NOT NULL,

  `priority` tinyint(2) unsigned NOT NULL,

  PRIMARY KEY (`matchmaking_group_id`,`map_id`),

  CONSTRAINT `matchmaking_group_maps_ibfk_1` FOREIGN KEY (`matchmaking_group_id`) REFERENCES `matchmaking_groups` (`id`) ON DELETE CASCADE ON UPDATE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

+------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

I'm running the latest stable version on Debian Stretch:

I did spot these errors in /var/log/daemon.log, but I'm not sure if they're relevant.

Jul  3 22:04:58 esportal mysqld[1772]: 2017-07-03 22:04:58 140365854455552 [ERROR] InnoDB: WSREP: referenced FK check fail: 15 index user_id table esportal/match_rematch_votes

Jul  4 17:40:59 esportal mysqld[1772]: 2017-07-04 17:40:59 140365548082944 [ERROR] InnoDB: WSREP: referenced FK check fail: 15 index host_user_id table esportal/matchmaking_groups

Attachments

- Sort By Name
- Sort By Date
- Ascending
- Descending
- Thumbnails
- List
- Download All

my.cnf
5 kB
2017-07-15 16:47

Issue Links

relates to

MDEV-13498 DELETE with CASCADE constraints takes long time and then crashes the instance with Signal 6

Closed

MDEV-15611 Due to the failure of foreign key detection, Galera slave node killed himself.

Closed

Activity

Ascending order - Click to sort in descending order

View 8 older comments

Elena Stepanova added a comment - 2017-08-14 14:30

marko, I will amend the test, it's clear why it fails; but first let's make sure that the code actually works as expected now, I'm not sure about that.

The relevant part of the test does the following:
1. Connection 1 starts transaction and updates a row X in table 1;
2. Connection 2 deletes a row from table 2, which references table 1, and the operation should cascade-delete row X from the table 1.

Without the fix (apart from the assertion failure, e.g. on a release build, or on 10.1), the DELETE query in connection 2 starts waiting, the process list says

10	root	localhost	test	Query	1	updating	DELETE FROM `matchmaking_groups` WHERE id = 10

and information_schema.innodb_trx says

1299	LOCK WAIT	2017-08-14 17:22:05	1299:4:3:2	2017-08-14 17:22:05	15	10	DELETE FROM `matchmaking_groups` WHERE id = 10	updating or deleting	36	14	1136	8	1	0	REPEATABLE READ	1	1	NULL	0	0	0

which is what the test waits for.

After the fix, the DELETE query succeeds immediately – no waiting, no transaction waiting for a lock.

It seems strange. Is it expected?

Elena Stepanova added a comment - 2017-08-14 14:30 marko , I will amend the test, it's clear why it fails; but first let's make sure that the code actually works as expected now, I'm not sure about that. The relevant part of the test does the following: 1. Connection 1 starts transaction and updates a row X in table 1; 2. Connection 2 deletes a row from table 2, which references table 1, and the operation should cascade-delete row X from the table 1. Without the fix (apart from the assertion failure, e.g. on a release build, or on 10.1), the DELETE query in connection 2 starts waiting, the process list says 10 root localhost test Query 1 updating DELETE FROM `matchmaking_groups` WHERE id = 10 and information_schema.innodb_trx says 1299 LOCK WAIT 2017-08-14 17:22:05 1299:4:3:2 2017-08-14 17:22:05 15 10 DELETE FROM `matchmaking_groups` WHERE id = 10 updating or deleting 36 14 1136 8 1 0 REPEATABLE READ 1 1 NULL 0 0 0 which is what the test waits for. After the fix, the DELETE query succeeds immediately – no waiting, no transaction waiting for a lock. It seems strange. Is it expected?

Marko Mäkelä added a comment - 2017-08-14 19:52

elenst, I changed the SELECT queries at the end of the test, getting the following result:

SELECT * FROM matchmaking_group_users;

matchmaking_group_id	user_id

11	2

SELECT * FROM matchmaking_group_maps;

matchmaking_group_id	map_id

11	66

SELECT * FROM users;

id	name

1	qux

2	bar

DROP TABLE `matchmaking_group_maps`, `matchmaking_group_users`, `matchmaking_groups`, `users`;

Now, what do we actually expect from the test?

At the time of the DELETE operation, there is one active transaction in the system, holding a lock on the users record (1,'foo') which it updated to ('1','qux'). Because this UPDATE statement is only changing the non-indexed column users.name, it can perform this operation in-place, while holding an exclusive lock on this record. There is no need to acquire any locks on the FOREIGN KEY child tables either.

What should the DELETE do? It must delete the matchmaking_groups record (10,1), prompting an ON DELETE CASCADE to the matchmaking_group_maps record (10,55) and the matchmaking_group_users record (10,1). According to the SELECT statements, all these records were deleted. The DELETE should not cascade to the users table, because that table does not contain any CONSTRAINT FOREIGN KEY…ON DELETE CASCADE that would instruct so.

The result looks perfectly valid to me.

Maybe earlier MariaDB versions did some unnecessary locking in the FOREIGN KEY processing. For example, the UPDATE could lock non-matching rows. (Not locking non-matching rows was the ‘semi-consistent read’ feature that I implemented in MySQL 5.0 as MySQL Bug #3300.) Perhaps this feature is not enabled by default before MariaDB 10.2? I seem to remember that MySQL 5.7 introduced some change to the innodb_locks_unsafe_for_binlog parameter, or to the related logic. Perhaps the relaxed locking is now enabled in the default TRANSACTION ISOLATION LEVEL REPEATABLE READ?

Can you determine what locking conflict would emerge on 10.0 or 10.1, and if it is possible to get the same result by setting innodb_locks_unsafe_for_binlog=1?

Marko Mäkelä added a comment - 2017-08-14 19:52 elenst , I changed the SELECT queries at the end of the test, getting the following result: SELECT * FROM matchmaking_group_users; matchmaking_group_id user_id 11 2 SELECT * FROM matchmaking_group_maps; matchmaking_group_id map_id 11 66 SELECT * FROM users; id name 1 qux 2 bar DROP TABLE `matchmaking_group_maps`, `matchmaking_group_users`, `matchmaking_groups`, `users`; Now, what do we actually expect from the test? At the time of the DELETE operation, there is one active transaction in the system, holding a lock on the users record (1,'foo') which it updated to ('1','qux'). Because this UPDATE statement is only changing the non-indexed column users.name, it can perform this operation in-place, while holding an exclusive lock on this record. There is no need to acquire any locks on the FOREIGN KEY child tables either. What should the DELETE do? It must delete the matchmaking_groups record (10,1), prompting an ON DELETE CASCADE to the matchmaking_group_maps record (10,55) and the matchmaking_group_users record (10,1). According to the SELECT statements, all these records were deleted. The DELETE should not cascade to the users table, because that table does not contain any CONSTRAINT FOREIGN KEY…ON DELETE CASCADE that would instruct so. The result looks perfectly valid to me. Maybe earlier MariaDB versions did some unnecessary locking in the FOREIGN KEY processing. For example, the UPDATE could lock non-matching rows. (Not locking non-matching rows was the ‘semi-consistent read’ feature that I implemented in MySQL 5.0 as MySQL Bug #3300 .) Perhaps this feature is not enabled by default before MariaDB 10.2? I seem to remember that MySQL 5.7 introduced some change to the innodb_locks_unsafe_for_binlog parameter, or to the related logic. Perhaps the relaxed locking is now enabled in the default TRANSACTION ISOLATION LEVEL REPEATABLE READ? Can you determine what locking conflict would emerge on 10.0 or 10.1, and if it is possible to get the same result by setting innodb_locks_unsafe_for_binlog=1?

Elena Stepanova added a comment - 2017-08-14 20:49

marko,

The DELETE should not cascade to the users table, because that table does not contain any CONSTRAINT FOREIGN KEY…ON DELETE CASCADE that would instruct so.
The result looks perfectly valid to me.

Sorry, you're right of course. I mixed up the semantics of referenced/referencing tables while writing the previous comment; and the test case was written based on the existing behavior rather than on expectations.

Can you determine what locking conflict would emerge on 10.0 or 10.1, and if it is possible to get the same result by setting innodb_locks_unsafe_for_binlog=1?

If you mean the DELETE command waiting on a lock after UPDATE, this locking behavior happens on 10.1, with and without innodb_locks_unsafe_for_binlog=1.
10.0 is not affected.

Elena Stepanova added a comment - 2017-08-14 20:49 marko , The DELETE should not cascade to the users table, because that table does not contain any CONSTRAINT FOREIGN KEY…ON DELETE CASCADE that would instruct so. The result looks perfectly valid to me. Sorry, you're right of course. I mixed up the semantics of referenced/referencing tables while writing the previous comment; and the test case was written based on the existing behavior rather than on expectations. Can you determine what locking conflict would emerge on 10.0 or 10.1, and if it is possible to get the same result by setting innodb_locks_unsafe_for_binlog=1? If you mean the DELETE command waiting on a lock after UPDATE, this locking behavior happens on 10.1, with and without innodb_locks_unsafe_for_binlog=1 . 10.0 is not affected.

Elena Stepanova added a comment - 2017-08-14 22:47

I suggest to amend the test this way:

Test
--source include/have_innodb.inc
--enable_connect_log

CREATE TABLE `users` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`),
`name` varchar(32) NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `matchmaking_groups` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`host_user_id` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `host_user_id` (`host_user_id`),
CONSTRAINT `matchmaking_groups_ibfk_1` FOREIGN KEY (`host_user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `matchmaking_group_users` (
`matchmaking_group_id` bigint(20) unsigned NOT NULL,
`user_id` int(10) unsigned NOT NULL,
PRIMARY KEY (`matchmaking_group_id`,`user_id`),
UNIQUE KEY `user_id` (`user_id`),
CONSTRAINT `matchmaking_group_users_ibfk_1` FOREIGN KEY (`matchmaking_group_id`) REFERENCES `matchmaking_groups` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `matchmaking_group_users_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `matchmaking_group_maps` (
`matchmaking_group_id` bigint(20) unsigned NOT NULL,
`map_id` tinyint(2) unsigned NOT NULL,
PRIMARY KEY (`matchmaking_group_id`,`map_id`),
CONSTRAINT `matchmaking_group_maps_ibfk_1` FOREIGN KEY (`matchmaking_group_id`) REFERENCES `matchmaking_groups` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `users` VALUES (NULL,'foo'),(NULL,'bar');
INSERT INTO `matchmaking_groups` VALUES (10,1),(11,2);
INSERT INTO `matchmaking_group_users` VALUES (10,1),(11,2);
INSERT INTO `matchmaking_group_maps` VALUES (10,55),(11,66);

BEGIN;
UPDATE users SET name = 'qux' WHERE id = 1;

--connect (con1,localhost,root,,)
SET innodb_lock_wait_timeout= 1;
DELETE FROM `matchmaking_groups` WHERE id = 10;

--connection default
COMMIT;

--disconnect con1

--connection default
--sorted_result
SELECT * FROM matchmaking_group_users WHERE matchmaking_group_id NOT IN (SELECT id FROM matchmaking_groups);
--sorted_result
SELECT * FROM matchmaking_group_maps WHERE matchmaking_group_id NOT IN (SELECT id FROM matchmaking_groups);
--sorted_result
SELECT * FROM users;

DROP TABLE `matchmaking_group_maps`, `matchmaking_group_users`, `matchmaking_groups`, `users`;

Result
CREATE TABLE `users` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`),
`name` varchar(32) NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `matchmaking_groups` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`host_user_id` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `host_user_id` (`host_user_id`),
CONSTRAINT `matchmaking_groups_ibfk_1` FOREIGN KEY (`host_user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `matchmaking_group_users` (
`matchmaking_group_id` bigint(20) unsigned NOT NULL,
`user_id` int(10) unsigned NOT NULL,
PRIMARY KEY (`matchmaking_group_id`,`user_id`),
UNIQUE KEY `user_id` (`user_id`),
CONSTRAINT `matchmaking_group_users_ibfk_1` FOREIGN KEY (`matchmaking_group_id`) REFERENCES `matchmaking_groups` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `matchmaking_group_users_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `matchmaking_group_maps` (
`matchmaking_group_id` bigint(20) unsigned NOT NULL,
`map_id` tinyint(2) unsigned NOT NULL,
PRIMARY KEY (`matchmaking_group_id`,`map_id`),
CONSTRAINT `matchmaking_group_maps_ibfk_1` FOREIGN KEY (`matchmaking_group_id`) REFERENCES `matchmaking_groups` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `users` VALUES (NULL,'foo'),(NULL,'bar');
INSERT INTO `matchmaking_groups` VALUES (10,1),(11,2);
INSERT INTO `matchmaking_group_users` VALUES (10,1),(11,2);
INSERT INTO `matchmaking_group_maps` VALUES (10,55),(11,66);
BEGIN;
UPDATE users SET name = 'qux' WHERE id = 1;
connect con1,localhost,root,,;
SET innodb_lock_wait_timeout= 1;
DELETE FROM `matchmaking_groups` WHERE id = 10;
connection default;
COMMIT;
disconnect con1;
connection default;
SELECT * FROM matchmaking_group_users WHERE matchmaking_group_id NOT IN (SELECT id FROM matchmaking_groups);
matchmaking_group_id user_id
SELECT * FROM matchmaking_group_maps WHERE matchmaking_group_id NOT IN (SELECT id FROM matchmaking_groups);
matchmaking_group_id map_id
SELECT * FROM users;
id name
1 qux
2 bar
DROP TABLE `matchmaking_group_maps`, `matchmaking_group_users`, `matchmaking_groups`, `users`;

Currently the test works this way:

10.0 - passes
10.1 - fails with the timeout at line 44 (because of the locking problem)
10.2 debug - fails with the assertion failure from this bug report
10.2 non-debug - fails with wrong result from this bug report (it does not fail with timeout, possibly related to ~~MDEV-13331~~)
bb-10.2-marko - passes

Elena Stepanova added a comment - 2017-08-14 22:47 I suggest to amend the test this way: Test --source include/have_innodb.inc --enable_connect_log CREATE TABLE `users` ( `id` int (10) unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`), ` name ` varchar (32) NOT NULL DEFAULT '' ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `matchmaking_groups` ( `id` bigint (20) unsigned NOT NULL AUTO_INCREMENT, `host_user_id` int (10) unsigned NOT NULL , PRIMARY KEY (`id`), UNIQUE KEY `host_user_id` (`host_user_id`), CONSTRAINT `matchmaking_groups_ibfk_1` FOREIGN KEY (`host_user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `matchmaking_group_users` ( `matchmaking_group_id` bigint (20) unsigned NOT NULL , `user_id` int (10) unsigned NOT NULL , PRIMARY KEY (`matchmaking_group_id`,`user_id`), UNIQUE KEY `user_id` (`user_id`), CONSTRAINT `matchmaking_group_users_ibfk_1` FOREIGN KEY (`matchmaking_group_id`) REFERENCES `matchmaking_groups` (`id`) ON DELETE CASCADE ON UPDATE CASCADE , CONSTRAINT `matchmaking_group_users_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `matchmaking_group_maps` ( `matchmaking_group_id` bigint (20) unsigned NOT NULL , `map_id` tinyint(2) unsigned NOT NULL , PRIMARY KEY (`matchmaking_group_id`,`map_id`), CONSTRAINT `matchmaking_group_maps_ibfk_1` FOREIGN KEY (`matchmaking_group_id`) REFERENCES `matchmaking_groups` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `users` VALUES ( NULL , 'foo' ),( NULL , 'bar' ); INSERT INTO `matchmaking_groups` VALUES (10,1),(11,2); INSERT INTO `matchmaking_group_users` VALUES (10,1),(11,2); INSERT INTO `matchmaking_group_maps` VALUES (10,55),(11,66); BEGIN ; UPDATE users SET name = 'qux' WHERE id = 1; --connect (con1,localhost,root,,) SET innodb_lock_wait_timeout= 1; DELETE FROM `matchmaking_groups` WHERE id = 10; --connection default COMMIT ; --disconnect con1 --connection default --sorted_result SELECT * FROM matchmaking_group_users WHERE matchmaking_group_id NOT IN ( SELECT id FROM matchmaking_groups); --sorted_result SELECT * FROM matchmaking_group_maps WHERE matchmaking_group_id NOT IN ( SELECT id FROM matchmaking_groups); --sorted_result SELECT * FROM users; DROP TABLE `matchmaking_group_maps`, `matchmaking_group_users`, `matchmaking_groups`, `users`; Result CREATE TABLE `users` ( `id` int (10) unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`), ` name ` varchar (32) NOT NULL DEFAULT '' ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `matchmaking_groups` ( `id` bigint (20) unsigned NOT NULL AUTO_INCREMENT, `host_user_id` int (10) unsigned NOT NULL , PRIMARY KEY (`id`), UNIQUE KEY `host_user_id` (`host_user_id`), CONSTRAINT `matchmaking_groups_ibfk_1` FOREIGN KEY (`host_user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `matchmaking_group_users` ( `matchmaking_group_id` bigint (20) unsigned NOT NULL , `user_id` int (10) unsigned NOT NULL , PRIMARY KEY (`matchmaking_group_id`,`user_id`), UNIQUE KEY `user_id` (`user_id`), CONSTRAINT `matchmaking_group_users_ibfk_1` FOREIGN KEY (`matchmaking_group_id`) REFERENCES `matchmaking_groups` (`id`) ON DELETE CASCADE ON UPDATE CASCADE , CONSTRAINT `matchmaking_group_users_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `matchmaking_group_maps` ( `matchmaking_group_id` bigint (20) unsigned NOT NULL , `map_id` tinyint(2) unsigned NOT NULL , PRIMARY KEY (`matchmaking_group_id`,`map_id`), CONSTRAINT `matchmaking_group_maps_ibfk_1` FOREIGN KEY (`matchmaking_group_id`) REFERENCES `matchmaking_groups` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `users` VALUES ( NULL , 'foo' ),( NULL , 'bar' ); INSERT INTO `matchmaking_groups` VALUES (10,1),(11,2); INSERT INTO `matchmaking_group_users` VALUES (10,1),(11,2); INSERT INTO `matchmaking_group_maps` VALUES (10,55),(11,66); BEGIN ; UPDATE users SET name = 'qux' WHERE id = 1; connect con1,localhost,root,,; SET innodb_lock_wait_timeout= 1; DELETE FROM `matchmaking_groups` WHERE id = 10; connection default ; COMMIT ; disconnect con1; connection default ; SELECT * FROM matchmaking_group_users WHERE matchmaking_group_id NOT IN ( SELECT id FROM matchmaking_groups); matchmaking_group_id user_id SELECT * FROM matchmaking_group_maps WHERE matchmaking_group_id NOT IN ( SELECT id FROM matchmaking_groups); matchmaking_group_id map_id SELECT * FROM users; id name 1 qux 2 bar DROP TABLE `matchmaking_group_maps`, `matchmaking_group_users`, `matchmaking_groups`, `users`; Currently the test works this way: 10.0 - passes 10.1 - fails with the timeout at line 44 (because of the locking problem) 10.2 debug - fails with the assertion failure from this bug report 10.2 non-debug - fails with wrong result from this bug report (it does not fail with timeout, possibly related to MDEV-13331 ) bb-10.2-marko - passes

Mark Samman added a comment - 2017-09-21 13:49

I'm still seeing this issue with
Server version: 10.2.8-MariaDB-10.2.8+maria~stretch-log mariadb.org binary distribution

MariaDB [esportal]> show create table matchmaking_match;

+-------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Table             | Create Table                                                                                                                                                                                                                                              |

+-------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| matchmaking_match | CREATE TABLE `matchmaking_match` (

  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,

  `region_id` tinyint(1) unsigned NOT NULL,

  `inserted` int(10) unsigned NOT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=47381 DEFAULT CHARSET=utf8 |

+-------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

MariaDB [esportal]> show create table matchmaking_match_users;

+-------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Table                   | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |

+-------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| matchmaking_match_users | CREATE TABLE `matchmaking_match_users` (

  `matchmaking_match_id` bigint(20) unsigned NOT NULL,

  `user_id` int(10) unsigned NOT NULL,

  `team` tinyint(1) unsigned NOT NULL,

  `accepted` tinyint(1) unsigned NOT NULL DEFAULT 0,

  PRIMARY KEY (`matchmaking_match_id`,`user_id`),

  UNIQUE KEY `mmu_user_id` (`user_id`),

  CONSTRAINT `matchmaking_match_users_ibfk_1` FOREIGN KEY (`matchmaking_match_id`) REFERENCES `matchmaking_match` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,

  CONSTRAINT `matchmaking_match_users_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

+-------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

MariaDB [esportal]> select * from matchmaking_match_users where matchmaking_match_id not in (select id from matchmaking_match);

+----------------------+-----------+------+----------+

| matchmaking_match_id | user_id   | team | accepted |

+----------------------+-----------+------+----------+

|                45220 |  11179195 |    2 |        1 |

|                45220 |  16004445 |    1 |        1 |

|                45220 |  17375835 |    2 |        1 |

|                45220 |  19416596 |    2 |        1 |

|                45220 |  36791488 |    1 |        0 |

|                45220 |  42131544 |    1 |        1 |

|                45220 | 299652215 |    1 |        0 |

+----------------------+-----------+------+----------+

7 rows in set (0.00 sec)

MariaDB [esportal]> delete from matchmaking_match_users where matchmaking_match_id not in (select id from matchmaking_match);

Query OK, 7 rows affected (0.00 sec)

MariaDB [esportal]>

Mark Samman added a comment - 2017-09-21 13:49 I'm still seeing this issue with Server version: 10.2.8-MariaDB-10.2.8+maria~stretch-log mariadb.org binary distribution MariaDB [esportal]> show create table matchmaking_match; + -------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | + -------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | matchmaking_match | CREATE TABLE `matchmaking_match` ( `id` bigint (20) unsigned NOT NULL AUTO_INCREMENT, `region_id` tinyint(1) unsigned NOT NULL , `inserted` int (10) unsigned NOT NULL , PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=47381 DEFAULT CHARSET=utf8 | + -------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) MariaDB [esportal]> show create table matchmaking_match_users; + -------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | + -------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | matchmaking_match_users | CREATE TABLE `matchmaking_match_users` ( `matchmaking_match_id` bigint (20) unsigned NOT NULL , `user_id` int (10) unsigned NOT NULL , `team` tinyint(1) unsigned NOT NULL , `accepted` tinyint(1) unsigned NOT NULL DEFAULT 0, PRIMARY KEY (`matchmaking_match_id`,`user_id`), UNIQUE KEY `mmu_user_id` (`user_id`), CONSTRAINT `matchmaking_match_users_ibfk_1` FOREIGN KEY (`matchmaking_match_id`) REFERENCES `matchmaking_match` (`id`) ON DELETE CASCADE ON UPDATE CASCADE , CONSTRAINT `matchmaking_match_users_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | + -------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) MariaDB [esportal]> select * from matchmaking_match_users where matchmaking_match_id not in ( select id from matchmaking_match); + ----------------------+-----------+------+----------+ | matchmaking_match_id | user_id | team | accepted | + ----------------------+-----------+------+----------+ | 45220 | 11179195 | 2 | 1 | | 45220 | 16004445 | 1 | 1 | | 45220 | 17375835 | 2 | 1 | | 45220 | 19416596 | 2 | 1 | | 45220 | 36791488 | 1 | 0 | | 45220 | 42131544 | 1 | 1 | | 45220 | 299652215 | 1 | 0 | + ----------------------+-----------+------+----------+ 7 rows in set (0.00 sec) MariaDB [esportal]> delete from matchmaking_match_users where matchmaking_match_id not in ( select id from matchmaking_match); Query OK, 7 rows affected (0.00 sec) MariaDB [esportal]>

MariaDB Server

Stale rows despite ON DELETE CASCADE constraint

Details

Description

Attachments

Attachments

Issue Links

Activity

People

Dates

Git Integration