[MDEV-21868] Failed of foreign key detection, Galera node down. - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Incomplete
Affects Version/s: 10.3.16
Fix Version/s: N/A
Component/s: Galera
Labels:
- need_feedback
Environment:
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

Attachments

Activity

Jan Lindström (Inactive) added a comment - 2020-10-06 12:10

Firstly, you should try more recent version of MariaDB 10.3 server and Galera library. Lot of bugs has been fixed after 10.3.16. When you said you deleted all the data from datadir did this include GRA files ? If you can repeat this I would need more detailed steps how to repeat and possibly files from datadir or edited tables content.

Jan Lindström (Inactive) added a comment - 2020-10-06 12:10 Firstly, you should try more recent version of MariaDB 10.3 server and Galera library. Lot of bugs has been fixed after 10.3.16. When you said you deleted all the data from datadir did this include GRA files ? If you can repeat this I would need more detailed steps how to repeat and possibly files from datadir or edited tables content.

People

Assignee:: Jan Lindström (Inactive)

Reporter:: Satria Dwi Putra

Votes:: 0 Vote for this issue

Watchers:: 3 Start watching this issue

Dates

Created:: 2020-03-04 14:19

Updated:: 2020-11-09 11:04

Resolved:: 2020-11-09 11:04

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server