Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Incomplete
-
10.3.16
-
OS : Ubuntu 18.04
Cloud : GCP
Instance type : VM
Machine type : n1-standard-8
MariaDB Galera Cluster (4 nodes)
Version : 10.3.16
Description
Hello,
Unfortunately I found this issue in our production environment. 1 node down and always do SST again , again and again.
When I check mysqlerror log . I found this error :
2020-03-04 10:57:54 13 [ERROR] Slave SQL: Could not execute Write_rows_v1 event on table v2_accounts.identity_details; Cannot add or update a child row: a foreign key constraint fails (`v2_accounts`.`identity_details`, CONSTRAINT `identity_details_ibfk_1` FOREIGN KEY (`uid`) REFERENCES `identities` (`uid`)), Error_code: 1452; handler error HA_ERR_NO_REFERENCED_ROW; the event's master log FIRST, end_log_pos 512, Internal MariaDB error code: 1452 |
2020-03-04 10:57:54 13 [Warning] WSREP: RBR event 3 Write_rows_v1 apply warning: 151, 14207906 |
2020-03-04 10:57:54 13 [Warning] WSREP: Failed to apply app buffer: seqno: 14207906, status: 1 |
at galera/src/trx_handle.cpp:apply():353 |
Retrying 2th time
|
|
And then check the table `identity_details` make sure have primary key also structure table looks neat.
CREATE TABLE `identity_details` (
|
`id` int(11) NOT NULL AUTO_INCREMENT, |
`uid` varchar(255) NOT NULL, |
`device_type_id` smallint(4) NOT NULL, |
`device_id` varchar(255) DEFAULT NULL, |
`status` tinyint(2) NOT NULL DEFAULT 1 COMMENT '0 => disactive, 1 => active, 9 => deleted', |
`created_at` datetime NOT NULL,
|
`updated_at` datetime DEFAULT NULL,
|
`deleted_at` datetime DEFAULT NULL,
|
PRIMARY KEY (`id`),
|
UNIQUE KEY `uid` (`uid`) USING BTREE,
|
UNIQUE KEY `device_id` (`device_id`) USING BTREE,
|
KEY `identity_details_ibfk_2` (`device_type_id`),
|
CONSTRAINT `identity_details_ibfk_1` FOREIGN KEY (`uid`) REFERENCES `identities` (`uid`),
|
CONSTRAINT `identity_details_ibfk_2` FOREIGN KEY (`device_type_id`) REFERENCES `device_types` (`id`)
|
) ENGINE=InnoDB AUTO_INCREMENT=13727 DEFAULT CHARSET=utf8mb4; |
Try 1 :
I tried to remove all data in datadir at the fail node but doesn't have impact, still fail and doing SST again , again and again because error log above. this log GRA__.log
?bin?_^?M?10.3.16-MariaDB-1:10.3.16+maria~bionic-log?_^8 |
|
|
|
|
b?fr?_^??MUUINSERT INTO
|
identity_details(
|
uid,
|
device_type_id,
|
device_id,
|
status,
|
created_at,
|
updated_at,
|
deleted_at
|
) VALUES (
|
'79229af87a864036b7411e47ce2f0889', |
3, |
'111101120826', |
0, |
'2020-03-04 13:20:03.180985', |
'2020-03-04 13:20:03.180985', |
NULL
|
)?_^?ML?
|
v2_accountsidentity_detail????_^?M???3 79229af87a864036b7411e47ce2f0889 |
111101120826???????? |
|
Try 2 :
I have suspect that foreign key uid to table identities have a problem (bugs mariadb maybe), bcs uid in identities not primary key but only unique key (I think it's fine but make cluster galera down) . this structure table identities
CREATE TABLE `identities` (
|
`id` int(11) NOT NULL AUTO_INCREMENT, |
`uid` varchar(255) NOT NULL, |
`email` varchar(255) DEFAULT NULL, |
`password_hash` longtext DEFAULT NULL,
|
`password_hash_legacy` longtext DEFAULT NULL,
|
`password_salt_legacy` longtext DEFAULT NULL,
|
`phone` varchar(255) DEFAULT NULL, |
`first_name` varchar(255) DEFAULT NULL, |
`last_name` varchar(255) DEFAULT NULL, |
`birthdate` date DEFAULT NULL,
|
`gender` enum('m','f') DEFAULT NULL, |
`location` varchar(255) DEFAULT NULL, |
`status` int(11) NOT NULL, |
`created_at` datetime NOT NULL,
|
`updated_at` datetime NOT NULL,
|
`deleted_at` datetime DEFAULT NULL,
|
`app_id` int(11) DEFAULT NULL, |
`device_id` varchar(50) DEFAULT NULL, |
`image_url` varchar(255) DEFAULT NULL, |
PRIMARY KEY (`id`) USING BTREE,
|
UNIQUE KEY `idx_uid` (`uid`) USING BTREE,
|
UNIQUE KEY `uq_email_appid` (`email`,`app_id`),
|
UNIQUE KEY `uq_phone_appid` (`phone`,`app_id`),
|
UNIQUE KEY `uq_device_id` (`device_id`) USING BTREE,
|
KEY `created_at` (`created_at`) USING BTREE,
|
FULLTEXT KEY `first_name` (`first_name`,`last_name`)
|
) ENGINE=InnoDB AUTO_INCREMENT=27062787 DEFAULT CHARSET=utf8mb4 |
So I tried to drop foreign key uid in table identity_detail, hopefully error foreign key doesn't appear again. for right now my cluster is fine.
Can you help check for this issue ? because if that error part of galera cluster limitations please tell to us and put in documentation.
Thanks
Satria Dwi Putra