[MDEV-13902] Orphan rows despite ON DELETE CASCADE constraint Created: 2017-09-24  Updated: 2019-04-20  Resolved: 2019-04-20

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - InnoDB
Affects Version/s: 10.2.8
Fix Version/s: N/A

Type: Bug Priority: Critical
Reporter: Mark Samman Assignee: Unassigned
Resolution: Incomplete Votes: 0
Labels: need_feedback
Environment:

Server version: 10.2.8-MariaDB-10.2.8+maria~stretch-log mariadb.org binary distribution
OS: Debian Stretch

Attachments: File my.cnf    
Issue Links:
Relates
relates to MDEV-15199 Triangular FKs - Cascade delete cause... Closed

 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.

 Comments   
Comment by Mark Samman [ 2017-09-24 ]

Actually it looks like I did have binlogs enabled this time. Although I'm not familiar with the tooling around binlogs, I'll try to figure out how to create a minimal case to reproduce.

Comment by Mark Samman [ 2017-09-24 ]

I've tried replaying the binlogs on top of a backup a day before this happened, up until a position a bit after the matchmaking_match entry was deleted. I was unable to reproduce the issue that way, so it seems like the binlog isn't helpful here. Perhaps it would be if I had logs from when the MariaDB server started (~30 days ago), but my binlogs only go a few days back.

Note that I did this on a different machine, with a different operating system (OS X), but still with MariaDB 10.2.8 (Server version: 10.2.8-MariaDB Homebrew).

Comment by Elena Stepanova [ 2017-09-30 ]

Samman,

Could it happen that these rows remained in the table from the time when the instance was still working on the old unfixed version?

Comment by Mark Samman [ 2017-09-30 ]

That doesn't sound like it since I saw the INSERT query for the rows in the binlog around September 21 when the server was already running 10.2.8. I've upgraded to 10.2.9 since reporting this and will report back if it still happens on 10.2.9.

Comment by Elena Stepanova [ 2017-10-01 ]

Please also check the error log for any suspicious errors and messages, and run CHECK TABLE on the involved tables.

This case, if it's really happening, is even stranger than MDEV-13246. In MDEV-13246 we had at least a bit tricky configuration, where table B referenced A, and table C referenced both B and A, which was error-prone under some conditions. Here everything seems to be basic and straightforward – `matchmaking_match_users` just references a couple of tables, and that's it. I don't expect that `users` table has foreign keys, does it? If it's indeed buggy, we should be getting lots of bug reports. So, it's likely that there is something more to it.

Comment by Mark Samman [ 2017-10-01 ]

The users table does not have any foreign keys. The error log is empty. Running CHECK TABLE shows status OK. The MariaDB server has been restarted since the issue happened though to upgrade to 10.2.9. I'll do another CHECK TABLE and check error logs next time it happens if 10.2.9 is still affected.

Comment by Elena Stepanova [ 2017-11-05 ]

Samman, did you encounter the problem again and if you did, were you able to collect information as you were going to?

Comment by Mark Samman [ 2017-11-05 ]

I have not encountered the problem since reporting this issue. It's possible that my application logic has changed so that it no longer triggers the issue (it's an actively developed project), or that some change in 10.2.9 fixed it. You can close it if you'd like to and I'll open a new one if I spot it again.

Comment by Elena Stepanova [ 2017-11-05 ]

Samman, I'll close it for now, but JIRA issues are re-open-able, so if you encounter it again, you don't need to create a new one – comment and we will re-open this one.

Comment by Mark Samman [ 2018-02-11 ]

I just had this happen again with server version 10.2.12. The most recent DDL was within the past 12 hours, and it was:

ALTER TABLE `user_notifications` CHANGE COLUMN `match_id` `data` JSON DEFAULT NULL;

It's possible that it's triggered by a bug in my application that uses the Go MySQL driver since there was a memory inconsistency bug last time this happened, and today there was another bug with the buffers used when reading rows from SELECT queries.

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=153661 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 |
 
+----------------------+-----------+------+----------+
 
|               146803 | 146613110 |    1 |        1 |
 
|               146803 | 155148690 |    2 |        1 |
 
|               146803 | 180757106 |    1 |        1 |
 
|               146803 | 244313752 |    2 |        0 |
 
|               146803 | 273445176 |    2 |        0 |
 
|               146803 | 279060114 |    2 |        1 |
 
|               146803 | 279463940 |    1 |        1 |
 
|               146803 | 327802118 |    1 |        1 |
 
|               146803 | 405506430 |    2 |        0 |
 
+----------------------+-----------+------+----------+
 
9 rows in set (0.00 sec)
 
 
 
MariaDB [esportal]> delete from matchmaking_match_users where matchmaking_match_id=146803;
 
Query OK, 9 rows affected (0.00 sec)

Comment by Asmir Mustafic [ 2018-10-10 ]

I'm facing the same issue with 10.2.11

As a temporary solution I have created a cronjob that daily sets to null the columns having orphan values.

But now that we have introduced a slave server, we have the same issue also on the slave... and for now we have also periodically to skip replication errors...

Comment by Asmir Mustafic [ 2018-10-10 ]

Here are my create tables:

CREATE TABLE `users` (
 
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 
  `nickname` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 
  `main_picture_id` int(11) DEFAULT NULL,
 
  PRIMARY KEY (`id`),
 
  KEY `users_main_picture_id_index` (`main_picture_id`)
 
) ENGINE=InnoDB AUTO_INCREMENT=5903615 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci";
 
 
 
 
 
CREATE TABLE `users_pictures` (
 
  `id` int(11) NOT NULL AUTO_INCREMENT,
 
  `user_id` int(11) unsigned NOT NULL,
 
  PRIMARY KEY (`id`),
 
  KEY `user_id` (`user_id`),
 
  CONSTRAINT `users_pictures_users_user_id_fk` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
 
) ENGINE=InnoDB AUTO_INCREMENT=7561551 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci";

From time to time (daily), main_picture_id contains IDs that do not exists in the users_pictures table.

Comment by Asmir Mustafic [ 2018-10-10 ]

CHECK TABLE on both tables did not report any error

Comment by Marko Mäkelä [ 2019-02-21 ]

Samman, goetas, sorry, I only became aware of this report now.

Can you please confirm if you are still having the problem with more recent MariaDB? I believe that MariaDB 10.2.13 should have fixed this.

Generated at Thu Feb 08 08:09:18 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.