Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Incomplete
-
10.2.21, 10.3.17, 10.4.7
-
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;
CREATE TABLE `parent_resources` ( |
`id` varchar(255) NOT NULL, |
`name` varchar(255) NOT NULL, |
`column_a` text NOT NULL, |
`column_b` text DEFAULT NULL, |
`column_c` text DEFAULT NULL, |
`column_d` varchar(255) DEFAULT NULL, |
`column_e` text DEFAULT NULL, |
`column_f` varchar(255) NOT NULL, |
`column_g` text DEFAULT NULL, |
`column_h` text DEFAULT NULL, |
`column_i` text DEFAULT NULL, |
`column_j` varchar(255) DEFAULT NULL, |
`column_k` varchar(36) NOT NULL, |
`column_l` text DEFAULT NULL, |
`column_m` tinyint(1) NOT NULL, |
`column_n` varchar(255) DEFAULT NULL, |
`column_o` text DEFAULT NULL, |
`column_p` text NOT NULL, |
PRIMARY KEY (`id`), |
KEY `parent_resources_column_d_table_d_id` (`column_d`), |
KEY `parent_resources_column_f_table_f_id` (`column_f`), |
KEY `parent_resources_column_j_table_j_id` (`column_j`), |
KEY `parent_resources_column_n_table_n_id` (`column_n`), |
KEY `parent_resources_name_idx` (`name`), |
KEY `parent_resources_column_k_table_k_id` (`column_k`), |
CONSTRAINT `parent_resources_column_k_table_k_id` FOREIGN KEY (`column_k`) REFERENCES `table_k` (`id`), |
CONSTRAINT `parent_resources_column_d_table_d_id` FOREIGN KEY (`column_d`) REFERENCES `table_d` (`id`), |
CONSTRAINT `parent_resources_column_f_table_f_id` FOREIGN KEY (`column_f`) REFERENCES `table_f` (`id`), |
CONSTRAINT `parent_resources_column_j_table_j_id` FOREIGN KEY (`column_j`) REFERENCES `table_j` (`id`) |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
mysql> show create table child_resources;
CREATE TABLE `child_resources` ( |
`id` varchar(255) NOT NULL, |
`name` varchar(255) NOT NULL, |
`column_01` text NOT NULL, |
`column_02` text NOT NULL, |
`PARENT_ID` varchar(255) NOT NULL, |
`column_03` varchar(255) DEFAULT NULL, |
`column_04` varchar(255) NOT NULL, |
`column_05` varchar(255) DEFAULT NULL, |
`column_06` text DEFAULT NULL, |
`column_07` text DEFAULT NULL, |
`column_08` text DEFAULT NULL, |
`column_09` tinyint(1) NOT NULL, |
`column_10` text NOT NULL, |
`column_11` varchar(255) DEFAULT NULL, |
`column_12` tinyint(1) NOT NULL, |
`column_13` tinyint(1) NOT NULL, |
`column_14` text NOT NULL, |
`column_15` text NOT NULL, |
`column_16` text DEFAULT NULL, |
`column_17` text NOT NULL, |
`column_18` text DEFAULT NULL, |
`column_19` text NOT NULL, |
`column_20` text DEFAULT NULL, |
`column_21` varchar(255) DEFAULT NULL, |
`column_22` text NOT NULL, |
`column_23` varchar(255) DEFAULT NULL, |
`column_24` varchar(36) NOT NULL, |
`column_25` text DEFAULT NULL, |
`column_26` varchar(255) DEFAULT NULL, |
`column_27` text DEFAULT NULL, |
`column_28` decimal(10,0) NOT NULL, |
`column_29` tinyint(1) NOT NULL, |
`column_30` decimal(10,0) NOT NULL, |
`column_31` text NOT NULL, |
PRIMARY KEY (`id`), |
KEY `child_resources_PARENT_ID_parent_resources_id` (`PARENT_ID`), |
KEY `child_resources_column_03_table_03_id` (`column_03`), |
KEY `child_resources_column_11_table_11_id` (`column_11`), |
KEY `child_resources_column_23_table_23_id` (`column_23`), |
KEY `child_resources_column_26_table_26_id` (`column_26`), |
KEY `child_resources_column_21_table_21_id` (`column_21`), |
KEY `child_resources_column_05_column_05_id` (`column_05`), |
KEY `child_resources_column_04_idx` (`column_04`), |
KEY `child_resources_name_idx` (`name`), |
KEY `child_resources_column_24_table_24_id` (`column_24`), |
CONSTRAINT `child_resources_column_11_table_11_id` FOREIGN KEY (`column_11`) REFERENCES `table_11` (`id`), |
CONSTRAINT `child_resources_column_24_table_24_id` FOREIGN KEY (`column_24`) REFERENCES `table_24` (`id`), |
CONSTRAINT `child_resources_column_03_table_03_id` FOREIGN KEY (`column_03`) REFERENCES `table_03` (`id`), |
CONSTRAINT `child_resources_column_26_table_26_id` FOREIGN KEY (`column_26`) REFERENCES `table_26` (`id`), |
CONSTRAINT `child_resources_column_05_table_05_id` FOREIGN KEY (`column_05`) REFERENCES `table_05` (`id`), |
CONSTRAINT `child_resources_column_21_table_21_id` FOREIGN KEY (`column_21`) REFERENCES `table_21` (`id`), |
CONSTRAINT `child_resources_PARENT_ID_parent_resources_id` FOREIGN KEY (`PARENT_ID`) REFERENCES `parent_resources` (`id`) ON DELETE CASCADE, |
CONSTRAINT `child_resources_column_23_table_23_id` FOREIGN KEY (`column_23`) REFERENCES `table_23` (`id`) |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
Below is an excerpt from MariaDB's general log. The transaction is quite long so only the most important parts are presented.
190912 14:17:06 ...
|
...
|
 |
368199 Query COMMIT
|
 |
...
|
190912 14:17:07 ...
|
190912 14:17:08 ...
|
190912 14:17:09 ...
|
190912 14:17:10 ...
|
190912 14:17:11 ...
|
190912 14:17:12 ...
|
...
|
 |
368199 Query SET TRANSACTION ISOLATION LEVEL READ COMMITTED
|
 |
368199 Query START TRANSACTION
|
 |
 |
368199 Prepare INSERT INTO `parent_resources` (`id`,`name`,`column_a`,`column_b`,`column_m`,`column_e`,`column_f`,`column_g`,`column_j`,`column_k`,`column_p`,`column_d`) VALUES (?,?,?,?,?,?,?,?,?,?,?,?)
|
 |
368199 Execute INSERT INTO `parent_resources` (`id`,`name`,`column_a`,`column_b`,`column_m`,`column_e`,`column_f`,`column_g`,`column_j`,`column_k`,`column_p`,`column_d`) VALUES ('d236f292-435d-4d84-bdaa-6cca80ffaef0','NAME','','Single',1,'[\"058aceaa-3d77-41d0-a029-c7fe84f5b843\"]','2dbcd718-67cc-49cc-a5ab-a65dbf780a43','{\"ABC\":-42,\"DEF\":42}','6a466a2b077441bbb0c08db3c244e5f6','default','default','058aceaa-3d77-41d0-a029-c7fe84f5b843')
|
 |
368199 Close stmt
|
 |
...
|
190912 14:17:13 ...
|
190912 14:17:14 ...
|
190912 14:17:15 ...
|
...
|
 |
368199 Prepare INSERT INTO `child_resources` (`id`,`name`,`column_01`,`column_02`,`column_30`,`PARENT_ID`,`column_03`,`column_28`,`column_29`,`column_04`,`column_05`,`column_07`,`column_08`,`column_09`,`column_10`,`column_11`,`column_12`,`column_13`,`column_14`,`column_15`,`column_17`,`column_18`,`column_19`,`column_20`,`column_21`,`column_22`,`column_23`,`column_24`,`column_31`,`column_26`) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
|
 |
---> HERE THE ACTUAL EXECUTION OF INSERT WOULD BE VISIBLE
|
 |
368199 Close stmt
|
 |
368199 Query ROLLBACK
|
 |
...
|
190912 14:17:16 ...
|
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.
INSERT INTO `child_resources` (`id`,`name`,`column_01`,`column_02`,`column_30`,`PARENT_ID`,`column_03`,`column_28`,`column_29`,`column_04`,`column_05`,`column_07`,`column_08`,`column_09`,`column_10`,`column_11`,`column_12`,`column_13`,`column_14`,`column_15`,`column_17`,`column_18`,`column_19`,`column_20`,`column_21`,`column_22`,`column_23`,`column_24`,`column_31`,`column_26`) VALUES ("50eb1264-1861-438c-be21-a51146e2183f","NAME","","primary",0,"d236f292-435d-4d84-bdaa-6cca80ffaef0",NULL,1400,True,"","468ae667-1164-4122-84e8-6386893867ed",NULL,NULL,Failse,"",NULL,false,false,"[]","SOME_STATUS","N/A",NULL,"N/A",NULL,NULL,"SOME_STATUS","6a466a2b077441bbb0c08db3c244e5f6","default","default","e0a822d5-6e76-44ea-bcab-8b70abbc958c")
|
The error we receive is as follows:
Error 1452: Cannot add or update a child row: a foreign key constraint fails (`dbname`.`child_resources`, CONSTRAINT `child_resources_PARENT_ID_parent_resources_id` FOREIGN KEY (`PARENT_ID`) REFERENCES `parent_resources` (`id`) ON DELETE CASCADE)
|
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?