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

Orphan rows despite ON DELETE CASCADE constraint

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Critical
    • Resolution: Incomplete
    • Affects Version/s: 10.2.8
    • Fix Version/s: N/A
    • Labels:
    • Environment:
      Server version: 10.2.8-MariaDB-10.2.8+maria~stretch-log mariadb.org binary distribution
      OS: Debian Stretch

      Description

      This is the same issue I reported in MDEV-13246, opening a new issue as the old one was closed as fixed and I'm still experiencing the issue with the latest stable release.

      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]> 
      

      To answer questions which were asked in the previous issue:

      • I have never disabled foreign key checks.
      • The most recent DDL was weeks before.
      • I don't know how to reproduce it.
      • I didn't have binlogs enabled when it happened. (EDIT: I did have binlogs enabled)
      • I'm not using a cluster, it's a single machine database.

      I have attached my.cnf.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              Unassigned Unassigned
              Reporter:
              Samman Mark Samman
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:

                  Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.