Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Incomplete
-
10.2.8
-
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
- relates to
-
-
- Closed
-
Activity
| Field | Original Value | New Value |
|---|---|---|
| Description |
This is the same issue I reported in {code:sql} 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]> {code} 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. * I'm not using a cluster, it's a single machine database. I have attached my.cnf. |
This is the same issue I reported in {code:sql} 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]> {code} 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. |
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).
Could it happen that these rows remained in the table from the time when the instance was still working on the old unfixed version?
| Labels | need_feedback |
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.
| Labels | need_feedback |
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.
| Labels | need_feedback |
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.
Samman, did you encounter the problem again and if you did, were you able to collect information as you were going to?
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.
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.
| Fix Version/s | N/A [ 14700 ] | |
| Resolution | Cannot Reproduce [ 5 ] | |
| Status | Open [ 1 ] | Closed [ 6 ] |
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) |
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...
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.
| Resolution | Cannot Reproduce [ 5 ] | |
| Status | Closed [ 6 ] | Stalled [ 10000 ] |
| Labels | need_feedback |
| Fix Version/s | N/A [ 14700 ] |
| Status | Stalled [ 10000 ] | Open [ 1 ] |
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.
| Link |
This issue relates to |
| Labels | need_feedback |
| Labels | need_feedback |
| Labels | need_feedback |
| Fix Version/s | N/A [ 14700 ] | |
| Resolution | Incomplete [ 4 ] | |
| Status | Open [ 1 ] | Closed [ 6 ] |
| Workflow | MariaDB v3 [ 82758 ] | MariaDB v4 [ 152882 ] |
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.