[MDEV-20776] Foreign key constraint failure on a record that's been recently inserted. Created: 2019-10-08 Updated: 2020-01-12 Resolved: 2020-01-12 |
|
| Status: | Closed |
| Project: | MariaDB Server |
| Component/s: | Storage Engine - InnoDB |
| Affects Version/s: | 10.2.21, 10.3.17, 10.4.7 |
| Fix Version/s: | N/A |
| Type: | Bug | Priority: | Major |
| Reporter: | Wojciech Cierpucha | Assignee: | Unassigned |
| Resolution: | Incomplete | Votes: | 2 |
| Labels: | need_feedback | ||
| Environment: |
Ubuntu 14.04 VM, MariaDB is inside a dedicated docker container. |
||
| Description |
|
We sometimes observe that MariaDB returns an Error 1452 about foreign key constraint failure even though the referenced record has been inserted in the same transaction before. The problem occurs mostly when there are multiple such transactions running in parallel - I've been reproducing this with about 80 such transactions, with about 30 running in parallel at any given time. We are using galera, but I've been able to reproduce this issue on single MariaDB node, even with galera disabled, using 10.2.21, 10.3.17 and 10.4.7 releases. MariaDB itself runs in a docker container on a Ubuntu 14.04 VM. Below is the structure of two (parent and child) tables involved with unimportant details anonymized. The `child_resources` table has a `PARENT_ID` column that references the `id` column of `parent_resources`. The `PARENT_ID` column is actually lowercase, I've made it uppercase here to stand out among all those other columns. mysql> show create table parent_resources;
mysql> show create table child_resources;
Below is an excerpt from MariaDB's general log. The transaction is quite long so only the most important parts are presented.
As can be seen, we prepare each statement before executing it. The actual `INSERT INTO child_resources` execution is not visible in the general log, I suspect that's because it doesn't complete successfully. Below is the statement as our application logs it before execution.
The error we receive is as follows:
To sum up, we insert a parent resource with ID "d236f292-435d-4d84-bdaa-6cca80ffaef0", then some time later (in the same transaction) we try to insert a child resource with PARENT_ID "d236f292-435d-4d84-bdaa-6cca80ffaef0" and that fails. The referenced parent resource was clearly added, we do not remove it later and thus it should exist when a child record gets inserted. Is there anything else I can provide for this issue? What would be the next steps for us to help diagnose this? |
| Comments |
| Comment by Andrew Miller [ 2019-10-10 ] | |||||||||||||||||||||||||
|
We've encountered the same issue in our local development VMs running MariaDB 10.2.26 on CentOS 7 installed via RPM. If there's any kind of information I can provide, please let me know. I'm not familiar with profiling MariaDB or anything, so some guidance would be appreciated. | |||||||||||||||||||||||||
| Comment by Elena Stepanova [ 2019-10-12 ] | |||||||||||||||||||||||||
|
A complete test case demonstrating the problem would certainly help. | |||||||||||||||||||||||||
| Comment by Vasily Sokolov [ 2019-10-21 ] | |||||||||||||||||||||||||
|
@Elena Stepanova - from our side it is easy reproduced. We are inserting entries in 2 tables, then insert entry in third table where 2 fields contains values (primary key values) from the firstly created entries. E.g. table1 with "id1" (primary key), table2 with "id2" (primary key) and then insert entry in table3 (where fields "fk_id1" and "fk_id2" are foreign keys to fields "id1" and "id2" from tables "table1" and "table2") - that final insert will fail with the error "Cannot add or update a child row: a foreign key constraint fails" What can we do to at least use some sort of workaround? As it is the system in our production with potentially huge load and we for sure need to maintain here all the foreign keys we have configured already. | |||||||||||||||||||||||||
| Comment by Andrew Miller [ 2019-11-07 ] | |||||||||||||||||||||||||
|
@Elena Stepanova I've updated my Parallels VM to use MariaDB 10.3.18 as well, but the same problem is still persisting. | |||||||||||||||||||||||||
| Comment by Andrew Miller [ 2019-12-13 ] | |||||||||||||||||||||||||
|
I tested by uninstalling and reinstalling MariaDB, patch version by patch version, starting from 10.2.20 to 10.2.27 and this issue appeared for me only once I installed 10.2.27. I suspect one of these "fixes" actually ended up breaking something in regards to foreign key updates. | |||||||||||||||||||||||||
| Comment by Elena Stepanova [ 2019-12-13 ] | |||||||||||||||||||||||||
|
If you can reproduce it this easily, can you present it in the form of actual SQL statements which you run rather than verbal description? It would be both easier for you and more helpful to us. The case below fits your description and yet works perfectly well.
|