Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-13246

Stale rows despite ON DELETE CASCADE constraint

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.2.2
    • Fix Version/s: 10.2.8
    • Labels:
    • 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

          Issue Links

            Activity

              People

              Assignee:
              marko Marko Mäkelä
              Reporter:
              Samman Mark Samman
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: