Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.2.2
-
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
Issue Links
- relates to
-
-
- Closed
-
-
-
- Closed
-