[MDEV-15611] Due to the failure of foreign key detection, Galera slave node killed himself. - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Critical
Resolution: Fixed
Affects Version/s: 10.2.8, 10.2.10, 10.2.12, 10.3.1
Fix Version/s: 10.1.35, 10.2.15
Component/s: Galera
Labels:
- galera
Environment:

Hide
CentOS Linux release 7.3.1611 (Core) 3.10.0-514.26.2.el7.x86_64
CentOS Linux release 7.4.1708 (Core) 3.10.0-693.5.2.el7.x86_64

MariaDB Galera Cluster (3 nodes)
10.2.12-MariaDB-log / 10.2.10-MariaDB-log
wsrep_25.21 / wsrep_25.20

Show
CentOS Linux release 7.3.1611 (Core) 3.10.0-514.26.2.el7.x86_64 CentOS Linux release 7.4.1708 (Core) 3.10.0-693.5.2.el7.x86_64 MariaDB Galera Cluster (3 nodes) 10.2.12-MariaDB-log / 10.2.10-MariaDB-log wsrep_25.21 / wsrep_25.20

Description

err.log

[ERROR] Slave SQL: Could not execute Delete_rows_v1 event on table aaa.p; Cannot delete or update a parent row: a foreign key constraint fails (`aaa`.`f`, CONSTRAINT `f_ibfk_1` FOREIGN KEY (`f_id`) REFERENCES `p` (`id`)), Error_code: 1451; handler error HA_ERR_ROW_IS_REFERENCED; the event's master log FIRST, end_log_pos 108, Internal MariaDB error code: 1451

2018-03-16 14:33:39 140457990018816 [Warning] WSREP: RBR event 3 Delete_rows_v1 apply warning: 152, 128

2018-03-16 14:33:39 140457990018816 [Warning] WSREP: Failed to apply app buffer: seqno: 128, status: 1

         at galera/src/trx_handle.cpp:apply():351

Reproduce
wsrep_slave_threads>1

CREATE TABLE `p` (

  `id` int(11) NOT NULL,

  `a` varchar(33) COLLATE utf8mb4_unicode_ci DEFAULT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ;

CREATE TABLE `f` (

  `id` int(11) NOT NULL,

  `f_id` int(11) DEFAULT NULL,

  KEY `f_id` (`f_id`),

  CONSTRAINT `f_ibfk_1` FOREIGN KEY (`f_id`) REFERENCES `p` (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ;

insert into p select 1,'aaa';

insert into f select 1,1;

#Repeatedly execute the following SQL until you generate thousands of data

insert into f select a.id + b.a,a.f_id from f a join (select max(id) as a from f b) b on 1=1;

select count(*) from f;

+----------+

| count(*) |

+----------+

|   131072 |

+----------+

#Slave node, no matter wsrep_slave_fk_checks is on or off.

show variables like 'wsrep_slave_fk_checks';

+-----------------------+-------+

| Variable_name         | Value |

+-----------------------+-------+

| wsrep_slave_fk_checks | ON    |

+-----------------------+-------+

#Master (Write) node

delete from f;delete from p;

#Slave node down and got errors

2018-03-16 14:33:39 140457990018816 [ERROR] Slave SQL: Could not execute Delete_rows_v1 event on table aaa.p; Cannot delete or update a parent row: a foreign key constraint fails (`aaa`.`f`, CONSTRAINT `f_ibfk_1` FOREIGN KEY (`f_id`) REFERENCES `p` (`id`)), Error_code: 1451; handler error HA_ERR_ROW_IS_REFERENCED; the event's master log FIRST, end_log_pos 108, Internal MariaDB error code: 1451

2018-03-16 14:33:39 140457990018816 [Warning] WSREP: RBR event 3 Delete_rows_v1 apply warning: 152, 128

2018-03-16 14:33:39 140457990018816 [Warning] WSREP: Failed to apply app buffer: seqno: 128, status: 1

         at galera/src/trx_handle.cpp:apply():351

Retrying 2th time

2018-03-16 14:33:39 140457990018816 [ERROR] Slave SQL: Could not execute Delete_rows_v1 event on table aaa.p; Cannot delete or update a parent row: a foreign key constraint fails (`aaa`.`f`, CONSTRAINT `f_ibfk_1` FOREIGN KEY (`f_id`) REFERENCES `p` (`id`)), Error_code: 1451; handler error HA_ERR_ROW_IS_REFERENCED; the event's master log FIRST, end_log_pos 108, Internal MariaDB error code: 1451

2018-03-16 14:33:39 140457990018816 [Warning] WSREP: RBR event 3 Delete_rows_v1 apply warning: 152, 128

2018-03-16 14:33:39 140457990018816 [Warning] WSREP: Failed to apply app buffer: seqno: 128, status: 1

         at galera/src/trx_handle.cpp:apply():351

Retrying 3th time

2018-03-16 14:33:39 140457990018816 [ERROR] Slave SQL: Could not execute Delete_rows_v1 event on table aaa.p; Cannot delete or update a parent row: a foreign key constraint fails (`aaa`.`f`, CONSTRAINT `f_ibfk_1` FOREIGN KEY (`f_id`) REFERENCES `p` (`id`)), Error_code: 1451; handler error HA_ERR_ROW_IS_REFERENCED; the event's master log FIRST, end_log_pos 108, Internal MariaDB error code: 1451

2018-03-16 14:33:39 140457990018816 [Warning] WSREP: RBR event 3 Delete_rows_v1 apply warning: 152, 128

2018-03-16 14:33:39 140457990018816 [Warning] WSREP: Failed to apply app buffer: seqno: 128, status: 1

         at galera/src/trx_handle.cpp:apply():351

Retrying 4th time

2018-03-16 14:33:39 140457990018816 [ERROR] Slave SQL: Could not execute Delete_rows_v1 event on table aaa.p; Cannot delete or update a parent row: a foreign key constraint fails (`aaa`.`f`, CONSTRAINT `f_ibfk_1` FOREIGN KEY (`f_id`) REFERENCES `p` (`id`)), Error_code: 1451; handler error HA_ERR_ROW_IS_REFERENCED; the event's master log FIRST, end_log_pos 108, Internal MariaDB error code: 1451

2018-03-16 14:33:39 140457990018816 [Warning] WSREP: RBR event 3 Delete_rows_v1 apply warning: 152, 128

2018-03-16 14:33:39 140457990018816 [ERROR] WSREP: Failed to apply trx: source: 849e805c-1dd2-11e8-aa79-eb31f2b81368 version: 3 local: 0 state: APPLYING flags: 1 conn_id: 87 trx_id: 12277 seqnos (l: 33, g: 128, s: 127, d: 126, ts: 1358419270394303)

2018-03-16 14:33:39 140457990018816 [ERROR] WSREP: Failed to apply trx 128 4 times

2018-03-16 14:33:39 140457990018816 [ERROR] WSREP: Node consistency compromised, aborting...

#Slave processlist

#Write set 127 is "delete from f;"

#Write set 128 is "delete from p;"

#Slave concurrently apply the two sql

 !image-2018-03-21-10-45-36-084.png|thumbnail!

This error didn't reproduce on MariaDB 10.2.7 with wsrep_slave_threads>1

Attachments

- Sort By Name
- Sort By Date
- Ascending
- Descending
- Thumbnails
- List
- Download All

galera_mdev_15611.cnf
0.1 kB
2018-04-02 12:06
galera_mdev_15611.test
0.8 kB
2018-04-02 12:06
image-2018-03-21-10-45-36-084.png
36 kB
2018-03-21 02:45

Issue Links

is duplicated by

MDEV-15252 DELETE with FKs crashes Galera nodes if wsrep_slave_threads>1

Closed

relates to

MDEV-13246 Stale rows despite ON DELETE CASCADE constraint

Closed

MDEV-13498 DELETE with CASCADE constraints takes long time and then crashes the instance with Signal 6

Closed

MDEV-13678 DELETE with CASCADE takes a long time when Galera is enabled

Closed

MDEV-14222 Unnecessary 'cascade' memory allocation for every updated row when there is no FOREIGN KEY

Closed

MDEV-18393 Galera node kills after DELETE statement with foreign keys

Closed

MDEV-18032 MDEV-15611 apparently still occuring with MariaDB 10.2.16

Closed

MDEV-18174 Galera node terminated due to foreign key constraint

Closed

(3 relates to)

Activity

Ascending order - Click to sort in descending order

Devin Yu created issue - 2018-03-21 02:57

Devin Yu made changes - 2018-03-21 04:21

Field	Original Value	New Value
Description	err.log {code:java} [ERROR] Slave SQL: Could not execute Delete_rows_v1 event on table aaa.p; Cannot delete or update a parent row: a foreign key constraint fails (`aaa`.`f`, CONSTRAINT `f_ibfk_1` FOREIGN KEY (`f_id`) REFERENCES `p` (`id`)), Error_code: 1451; handler error HA_ERR_ROW_IS_REFERENCED; the event's master log FIRST, end_log_pos 108, Internal MariaDB error code: 1451 2018-03-16 14:33:39 140457990018816 [Warning] WSREP: RBR event 3 Delete_rows_v1 apply warning: 152, 128 2018-03-16 14:33:39 140457990018816 [Warning] WSREP: Failed to apply app buffer: seqno: 128, status: 1 at galera/src/trx_handle.cpp:apply():351 {code} Reproduce {code:java} CREATE TABLE `p` ( `id` int(11) NOT NULL, `a` varchar(33) COLLATE utf8mb4_unicode_ci DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ; CREATE TABLE `f` ( `id` int(11) NOT NULL, `f_id` int(11) DEFAULT NULL, KEY `f_id` (`f_id`), CONSTRAINT `f_ibfk_1` FOREIGN KEY (`f_id`) REFERENCES `p` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ; insert into p select 1,'aaa'; insert into f select 1,1; #Repeatedly execute the following SQL until you generate thousands of data insert into f select a.id + b.a,a.f_id from f a join (select max(id) as a from f b) b on 1=1; select count() from f; +----------+ \| count() \| +----------+ \| 131072 \| +----------+ #Slave node show variables like 'wsrep_slave_fk_checks'; +-----------------------+-------+ \| Variable_name \| Value \| +-----------------------+-------+ \| wsrep_slave_fk_checks \| ON \| +-----------------------+-------+ #Master (Write) node delete from f;delete from p; #Slave node down and got errors 2018-03-16 14:33:39 140457990018816 [ERROR] Slave SQL: Could not execute Delete_rows_v1 event on table aaa.p; Cannot delete or update a parent row: a foreign key constraint fails (`aaa`.`f`, CONSTRAINT `f_ibfk_1` FOREIGN KEY (`f_id`) REFERENCES `p` (`id`)), Error_code: 1451; handler error HA_ERR_ROW_IS_REFERENCED; the event's master log FIRST, end_log_pos 108, Internal MariaDB error code: 1451 2018-03-16 14:33:39 140457990018816 [Warning] WSREP: RBR event 3 Delete_rows_v1 apply warning: 152, 128 2018-03-16 14:33:39 140457990018816 [Warning] WSREP: Failed to apply app buffer: seqno: 128, status: 1 at galera/src/trx_handle.cpp:apply():351 Retrying 2th time 2018-03-16 14:33:39 140457990018816 [ERROR] Slave SQL: Could not execute Delete_rows_v1 event on table aaa.p; Cannot delete or update a parent row: a foreign key constraint fails (`aaa`.`f`, CONSTRAINT `f_ibfk_1` FOREIGN KEY (`f_id`) REFERENCES `p` (`id`)), Error_code: 1451; handler error HA_ERR_ROW_IS_REFERENCED; the event's master log FIRST, end_log_pos 108, Internal MariaDB error code: 1451 2018-03-16 14:33:39 140457990018816 [Warning] WSREP: RBR event 3 Delete_rows_v1 apply warning: 152, 128 2018-03-16 14:33:39 140457990018816 [Warning] WSREP: Failed to apply app buffer: seqno: 128, status: 1 at galera/src/trx_handle.cpp:apply():351 Retrying 3th time 2018-03-16 14:33:39 140457990018816 [ERROR] Slave SQL: Could not execute Delete_rows_v1 event on table aaa.p; Cannot delete or update a parent row: a foreign key constraint fails (`aaa`.`f`, CONSTRAINT `f_ibfk_1` FOREIGN KEY (`f_id`) REFERENCES `p` (`id`)), Error_code: 1451; handler error HA_ERR_ROW_IS_REFERENCED; the event's master log FIRST, end_log_pos 108, Internal MariaDB error code: 1451 2018-03-16 14:33:39 140457990018816 [Warning] WSREP: RBR event 3 Delete_rows_v1 apply warning: 152, 128 2018-03-16 14:33:39 140457990018816 [Warning] WSREP: Failed to apply app buffer: seqno: 128, status: 1 at galera/src/trx_handle.cpp:apply():351 Retrying 4th time 2018-03-16 14:33:39 140457990018816 [ERROR] Slave SQL: Could not execute Delete_rows_v1 event on table aaa.p; Cannot delete or update a parent row: a foreign key constraint fails (`aaa`.`f`, CONSTRAINT `f_ibfk_1` FOREIGN KEY (`f_id`) REFERENCES `p` (`id`)), Error_code: 1451; handler error HA_ERR_ROW_IS_REFERENCED; the event's master log FIRST, end_log_pos 108, Internal MariaDB error code: 1451 2018-03-16 14:33:39 140457990018816 [Warning] WSREP: RBR event 3 Delete_rows_v1 apply warning: 152, 128 2018-03-16 14:33:39 140457990018816 [ERROR] WSREP: Failed to apply trx: source: 849e805c-1dd2-11e8-aa79-eb31f2b81368 version: 3 local: 0 state: APPLYING flags: 1 conn_id: 87 trx_id: 12277 seqnos (l: 33, g: 128, s: 127, d: 126, ts: 1358419270394303) 2018-03-16 14:33:39 140457990018816 [ERROR] WSREP: Failed to apply trx 128 4 times 2018-03-16 14:33:39 140457990018816 [ERROR] WSREP: Node consistency compromised, aborting... #Slave processlist #Write set 127 is "delete from f;" #Write set 128 is "delete from p;" #Slave concurrently apply the two sql !image-2018-03-21-10-45-36-084.png\|thumbnail! {code} This error didn't reproduce on MariaDB 10.2.7.	err.log {code:java} [ERROR] Slave SQL: Could not execute Delete_rows_v1 event on table aaa.p; Cannot delete or update a parent row: a foreign key constraint fails (`aaa`.`f`, CONSTRAINT `f_ibfk_1` FOREIGN KEY (`f_id`) REFERENCES `p` (`id`)), Error_code: 1451; handler error HA_ERR_ROW_IS_REFERENCED; the event's master log FIRST, end_log_pos 108, Internal MariaDB error code: 1451 2018-03-16 14:33:39 140457990018816 [Warning] WSREP: RBR event 3 Delete_rows_v1 apply warning: 152, 128 2018-03-16 14:33:39 140457990018816 [Warning] WSREP: Failed to apply app buffer: seqno: 128, status: 1 at galera/src/trx_handle.cpp:apply():351 {code} Reproduce {code:java} CREATE TABLE `p` ( `id` int(11) NOT NULL, `a` varchar(33) COLLATE utf8mb4_unicode_ci DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ; CREATE TABLE `f` ( `id` int(11) NOT NULL, `f_id` int(11) DEFAULT NULL, KEY `f_id` (`f_id`), CONSTRAINT `f_ibfk_1` FOREIGN KEY (`f_id`) REFERENCES `p` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ; insert into p select 1,'aaa'; insert into f select 1,1; #Repeatedly execute the following SQL until you generate thousands of data insert into f select a.id + b.a,a.f_id from f a join (select max(id) as a from f b) b on 1=1; select count() from f; +----------+ \| count() \| +----------+ \| 131072 \| +----------+ #Slave node, no matter wsrep_slave_fk_checks is on or off. show variables like 'wsrep_slave_fk_checks'; +-----------------------+-------+ \| Variable_name \| Value \| +-----------------------+-------+ \| wsrep_slave_fk_checks \| ON \| +-----------------------+-------+ #Master (Write) node delete from f;delete from p; #Slave node down and got errors 2018-03-16 14:33:39 140457990018816 [ERROR] Slave SQL: Could not execute Delete_rows_v1 event on table aaa.p; Cannot delete or update a parent row: a foreign key constraint fails (`aaa`.`f`, CONSTRAINT `f_ibfk_1` FOREIGN KEY (`f_id`) REFERENCES `p` (`id`)), Error_code: 1451; handler error HA_ERR_ROW_IS_REFERENCED; the event's master log FIRST, end_log_pos 108, Internal MariaDB error code: 1451 2018-03-16 14:33:39 140457990018816 [Warning] WSREP: RBR event 3 Delete_rows_v1 apply warning: 152, 128 2018-03-16 14:33:39 140457990018816 [Warning] WSREP: Failed to apply app buffer: seqno: 128, status: 1 at galera/src/trx_handle.cpp:apply():351 Retrying 2th time 2018-03-16 14:33:39 140457990018816 [ERROR] Slave SQL: Could not execute Delete_rows_v1 event on table aaa.p; Cannot delete or update a parent row: a foreign key constraint fails (`aaa`.`f`, CONSTRAINT `f_ibfk_1` FOREIGN KEY (`f_id`) REFERENCES `p` (`id`)), Error_code: 1451; handler error HA_ERR_ROW_IS_REFERENCED; the event's master log FIRST, end_log_pos 108, Internal MariaDB error code: 1451 2018-03-16 14:33:39 140457990018816 [Warning] WSREP: RBR event 3 Delete_rows_v1 apply warning: 152, 128 2018-03-16 14:33:39 140457990018816 [Warning] WSREP: Failed to apply app buffer: seqno: 128, status: 1 at galera/src/trx_handle.cpp:apply():351 Retrying 3th time 2018-03-16 14:33:39 140457990018816 [ERROR] Slave SQL: Could not execute Delete_rows_v1 event on table aaa.p; Cannot delete or update a parent row: a foreign key constraint fails (`aaa`.`f`, CONSTRAINT `f_ibfk_1` FOREIGN KEY (`f_id`) REFERENCES `p` (`id`)), Error_code: 1451; handler error HA_ERR_ROW_IS_REFERENCED; the event's master log FIRST, end_log_pos 108, Internal MariaDB error code: 1451 2018-03-16 14:33:39 140457990018816 [Warning] WSREP: RBR event 3 Delete_rows_v1 apply warning: 152, 128 2018-03-16 14:33:39 140457990018816 [Warning] WSREP: Failed to apply app buffer: seqno: 128, status: 1 at galera/src/trx_handle.cpp:apply():351 Retrying 4th time 2018-03-16 14:33:39 140457990018816 [ERROR] Slave SQL: Could not execute Delete_rows_v1 event on table aaa.p; Cannot delete or update a parent row: a foreign key constraint fails (`aaa`.`f`, CONSTRAINT `f_ibfk_1` FOREIGN KEY (`f_id`) REFERENCES `p` (`id`)), Error_code: 1451; handler error HA_ERR_ROW_IS_REFERENCED; the event's master log FIRST, end_log_pos 108, Internal MariaDB error code: 1451 2018-03-16 14:33:39 140457990018816 [Warning] WSREP: RBR event 3 Delete_rows_v1 apply warning: 152, 128 2018-03-16 14:33:39 140457990018816 [ERROR] WSREP: Failed to apply trx: source: 849e805c-1dd2-11e8-aa79-eb31f2b81368 version: 3 local: 0 state: APPLYING flags: 1 conn_id: 87 trx_id: 12277 seqnos (l: 33, g: 128, s: 127, d: 126, ts: 1358419270394303) 2018-03-16 14:33:39 140457990018816 [ERROR] WSREP: Failed to apply trx 128 4 times 2018-03-16 14:33:39 140457990018816 [ERROR] WSREP: Node consistency compromised, aborting... #Slave processlist #Write set 127 is "delete from f;" #Write set 128 is "delete from p;" #Slave concurrently apply the two sql !image-2018-03-21-10-45-36-084.png\|thumbnail! {code} This error didn't reproduce on MariaDB 10.2.7.

Devin Yu made changes - 2018-03-21 06:24

Description

*err.log*
{code:java}
[ERROR] Slave SQL: Could not execute Delete_rows_v1 event on table aaa.p; Cannot delete or update a parent row: a foreign key constraint fails (`aaa`.`f`, CONSTRAINT `f_ibfk_1` FOREIGN KEY (`f_id`) REFERENCES `p` (`id`)), Error_code: 1451; handler error HA_ERR_ROW_IS_REFERENCED; the event's master log FIRST, end_log_pos 108, Internal MariaDB error code: 1451
2018-03-16 14:33:39 140457990018816 [Warning] WSREP: RBR event 3 Delete_rows_v1 apply warning: 152, 128
2018-03-16 14:33:39 140457990018816 [Warning] WSREP: Failed to apply app buffer: seqno: 128, status: 1
         at galera/src/trx_handle.cpp:apply():351
{code}

*Reproduce*
{code:java}
CREATE TABLE `p` (
  `id` int(11) NOT NULL,
  `a` varchar(33) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ;

CREATE TABLE `f` (
  `id` int(11) NOT NULL,
  `f_id` int(11) DEFAULT NULL,
  KEY `f_id` (`f_id`),
  CONSTRAINT `f_ibfk_1` FOREIGN KEY (`f_id`) REFERENCES `p` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ;

insert into p select 1,'aaa';

insert into f select 1,1;

#Repeatedly execute the following SQL until you generate thousands of data
insert into f select a.id + b.a,a.f_id from f a join (select max(id) as a from f b) b on 1=1;

select count(*) from f;
+----------+
| count(*) |
+----------+
| 131072 |
+----------+

#Slave node, no matter wsrep_slave_fk_checks is on or off.
show variables like 'wsrep_slave_fk_checks';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| wsrep_slave_fk_checks | ON |
+-----------------------+-------+

#Master (Write) node
delete from f;delete from p;

#Slave node down and got errors
2018-03-16 14:33:39 140457990018816 [ERROR] Slave SQL: Could not execute Delete_rows_v1 event on table aaa.p; Cannot delete or update a parent row: a foreign key constraint fails (`aaa`.`f`, CONSTRAINT `f_ibfk_1` FOREIGN KEY (`f_id`) REFERENCES `p` (`id`)), Error_code: 1451; handler error HA_ERR_ROW_IS_REFERENCED; the event's master log FIRST, end_log_pos 108, Internal MariaDB error code: 1451
2018-03-16 14:33:39 140457990018816 [Warning] WSREP: RBR event 3 Delete_rows_v1 apply warning: 152, 128
2018-03-16 14:33:39 140457990018816 [Warning] WSREP: Failed to apply app buffer: seqno: 128, status: 1
         at galera/src/trx_handle.cpp:apply():351
Retrying 2th time
2018-03-16 14:33:39 140457990018816 [ERROR] Slave SQL: Could not execute Delete_rows_v1 event on table aaa.p; Cannot delete or update a parent row: a foreign key constraint fails (`aaa`.`f`, CONSTRAINT `f_ibfk_1` FOREIGN KEY (`f_id`) REFERENCES `p` (`id`)), Error_code: 1451; handler error HA_ERR_ROW_IS_REFERENCED; the event's master log FIRST, end_log_pos 108, Internal MariaDB error code: 1451
2018-03-16 14:33:39 140457990018816 [Warning] WSREP: RBR event 3 Delete_rows_v1 apply warning: 152, 128
2018-03-16 14:33:39 140457990018816 [Warning] WSREP: Failed to apply app buffer: seqno: 128, status: 1
         at galera/src/trx_handle.cpp:apply():351
Retrying 3th time
2018-03-16 14:33:39 140457990018816 [ERROR] Slave SQL: Could not execute Delete_rows_v1 event on table aaa.p; Cannot delete or update a parent row: a foreign key constraint fails (`aaa`.`f`, CONSTRAINT `f_ibfk_1` FOREIGN KEY (`f_id`) REFERENCES `p` (`id`)), Error_code: 1451; handler error HA_ERR_ROW_IS_REFERENCED; the event's master log FIRST, end_log_pos 108, Internal MariaDB error code: 1451
2018-03-16 14:33:39 140457990018816 [Warning] WSREP: RBR event 3 Delete_rows_v1 apply warning: 152, 128
2018-03-16 14:33:39 140457990018816 [Warning] WSREP: Failed to apply app buffer: seqno: 128, status: 1
         at galera/src/trx_handle.cpp:apply():351
Retrying 4th time
2018-03-16 14:33:39 140457990018816 [ERROR] Slave SQL: Could not execute Delete_rows_v1 event on table aaa.p; Cannot delete or update a parent row: a foreign key constraint fails (`aaa`.`f`, CONSTRAINT `f_ibfk_1` FOREIGN KEY (`f_id`) REFERENCES `p` (`id`)), Error_code: 1451; handler error HA_ERR_ROW_IS_REFERENCED; the event's master log FIRST, end_log_pos 108, Internal MariaDB error code: 1451
2018-03-16 14:33:39 140457990018816 [Warning] WSREP: RBR event 3 Delete_rows_v1 apply warning: 152, 128
2018-03-16 14:33:39 140457990018816 [ERROR] WSREP: Failed to apply trx: source: 849e805c-1dd2-11e8-aa79-eb31f2b81368 version: 3 local: 0 state: APPLYING flags: 1 conn_id: 87 trx_id: 12277 seqnos (l: 33, g: 128, s: 127, d: 126, ts: 1358419270394303)
2018-03-16 14:33:39 140457990018816 [ERROR] WSREP: Failed to apply trx 128 4 times
2018-03-16 14:33:39 140457990018816 [ERROR] WSREP: Node consistency compromised, aborting...

#Slave processlist
#Write set 127 is "delete from f;"
#Write set 128 is "delete from p;"
#Slave concurrently apply the two sql
!image-2018-03-21-10-45-36-084.png|thumbnail!
{code}

*This error didn't reproduce on MariaDB 10.2.7.*

*err.log*
{code:java}
[ERROR] Slave SQL: Could not execute Delete_rows_v1 event on table aaa.p; Cannot delete or update a parent row: a foreign key constraint fails (`aaa`.`f`, CONSTRAINT `f_ibfk_1` FOREIGN KEY (`f_id`) REFERENCES `p` (`id`)), Error_code: 1451; handler error HA_ERR_ROW_IS_REFERENCED; the event's master log FIRST, end_log_pos 108, Internal MariaDB error code: 1451
2018-03-16 14:33:39 140457990018816 [Warning] WSREP: RBR event 3 Delete_rows_v1 apply warning: 152, 128
2018-03-16 14:33:39 140457990018816 [Warning] WSREP: Failed to apply app buffer: seqno: 128, status: 1
         at galera/src/trx_handle.cpp:apply():351
{code}

*Reproduce*
wsrep_slave_threads>1
{code:java}
CREATE TABLE `p` (
  `id` int(11) NOT NULL,
  `a` varchar(33) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ;

CREATE TABLE `f` (
  `id` int(11) NOT NULL,
  `f_id` int(11) DEFAULT NULL,
  KEY `f_id` (`f_id`),
  CONSTRAINT `f_ibfk_1` FOREIGN KEY (`f_id`) REFERENCES `p` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ;

insert into p select 1,'aaa';

insert into f select 1,1;

#Repeatedly execute the following SQL until you generate thousands of data
insert into f select a.id + b.a,a.f_id from f a join (select max(id) as a from f b) b on 1=1;

select count(*) from f;
+----------+
| count(*) |
+----------+
| 131072 |
+----------+

#Slave node, no matter wsrep_slave_fk_checks is on or off.
show variables like 'wsrep_slave_fk_checks';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| wsrep_slave_fk_checks | ON |
+-----------------------+-------+

#Master (Write) node
delete from f;delete from p;

#Slave node down and got errors
2018-03-16 14:33:39 140457990018816 [ERROR] Slave SQL: Could not execute Delete_rows_v1 event on table aaa.p; Cannot delete or update a parent row: a foreign key constraint fails (`aaa`.`f`, CONSTRAINT `f_ibfk_1` FOREIGN KEY (`f_id`) REFERENCES `p` (`id`)), Error_code: 1451; handler error HA_ERR_ROW_IS_REFERENCED; the event's master log FIRST, end_log_pos 108, Internal MariaDB error code: 1451
2018-03-16 14:33:39 140457990018816 [Warning] WSREP: RBR event 3 Delete_rows_v1 apply warning: 152, 128
2018-03-16 14:33:39 140457990018816 [Warning] WSREP: Failed to apply app buffer: seqno: 128, status: 1
         at galera/src/trx_handle.cpp:apply():351
Retrying 2th time
2018-03-16 14:33:39 140457990018816 [ERROR] Slave SQL: Could not execute Delete_rows_v1 event on table aaa.p; Cannot delete or update a parent row: a foreign key constraint fails (`aaa`.`f`, CONSTRAINT `f_ibfk_1` FOREIGN KEY (`f_id`) REFERENCES `p` (`id`)), Error_code: 1451; handler error HA_ERR_ROW_IS_REFERENCED; the event's master log FIRST, end_log_pos 108, Internal MariaDB error code: 1451
2018-03-16 14:33:39 140457990018816 [Warning] WSREP: RBR event 3 Delete_rows_v1 apply warning: 152, 128
2018-03-16 14:33:39 140457990018816 [Warning] WSREP: Failed to apply app buffer: seqno: 128, status: 1
         at galera/src/trx_handle.cpp:apply():351
Retrying 3th time
2018-03-16 14:33:39 140457990018816 [ERROR] Slave SQL: Could not execute Delete_rows_v1 event on table aaa.p; Cannot delete or update a parent row: a foreign key constraint fails (`aaa`.`f`, CONSTRAINT `f_ibfk_1` FOREIGN KEY (`f_id`) REFERENCES `p` (`id`)), Error_code: 1451; handler error HA_ERR_ROW_IS_REFERENCED; the event's master log FIRST, end_log_pos 108, Internal MariaDB error code: 1451
2018-03-16 14:33:39 140457990018816 [Warning] WSREP: RBR event 3 Delete_rows_v1 apply warning: 152, 128
2018-03-16 14:33:39 140457990018816 [Warning] WSREP: Failed to apply app buffer: seqno: 128, status: 1
         at galera/src/trx_handle.cpp:apply():351
Retrying 4th time
2018-03-16 14:33:39 140457990018816 [ERROR] Slave SQL: Could not execute Delete_rows_v1 event on table aaa.p; Cannot delete or update a parent row: a foreign key constraint fails (`aaa`.`f`, CONSTRAINT `f_ibfk_1` FOREIGN KEY (`f_id`) REFERENCES `p` (`id`)), Error_code: 1451; handler error HA_ERR_ROW_IS_REFERENCED; the event's master log FIRST, end_log_pos 108, Internal MariaDB error code: 1451
2018-03-16 14:33:39 140457990018816 [Warning] WSREP: RBR event 3 Delete_rows_v1 apply warning: 152, 128
2018-03-16 14:33:39 140457990018816 [ERROR] WSREP: Failed to apply trx: source: 849e805c-1dd2-11e8-aa79-eb31f2b81368 version: 3 local: 0 state: APPLYING flags: 1 conn_id: 87 trx_id: 12277 seqnos (l: 33, g: 128, s: 127, d: 126, ts: 1358419270394303)
2018-03-16 14:33:39 140457990018816 [ERROR] WSREP: Failed to apply trx 128 4 times
2018-03-16 14:33:39 140457990018816 [ERROR] WSREP: Node consistency compromised, aborting...

#Slave processlist
#Write set 127 is "delete from f;"
#Write set 128 is "delete from p;"
#Slave concurrently apply the two sql
!image-2018-03-21-10-45-36-084.png|thumbnail!
{code}

*This error didn't reproduce on MariaDB 10.2.7 with wsrep_slave_threads>1*

Devin Yu made changes - 2018-03-21 06:25

Description

*err.log*
{code:java}
[ERROR] Slave SQL: Could not execute Delete_rows_v1 event on table aaa.p; Cannot delete or update a parent row: a foreign key constraint fails (`aaa`.`f`, CONSTRAINT `f_ibfk_1` FOREIGN KEY (`f_id`) REFERENCES `p` (`id`)), Error_code: 1451; handler error HA_ERR_ROW_IS_REFERENCED; the event's master log FIRST, end_log_pos 108, Internal MariaDB error code: 1451
2018-03-16 14:33:39 140457990018816 [Warning] WSREP: RBR event 3 Delete_rows_v1 apply warning: 152, 128
2018-03-16 14:33:39 140457990018816 [Warning] WSREP: Failed to apply app buffer: seqno: 128, status: 1
         at galera/src/trx_handle.cpp:apply():351
{code}

*Reproduce*
wsrep_slave_threads>1
{code:java}
CREATE TABLE `p` (
  `id` int(11) NOT NULL,
  `a` varchar(33) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ;

CREATE TABLE `f` (
  `id` int(11) NOT NULL,
  `f_id` int(11) DEFAULT NULL,
  KEY `f_id` (`f_id`),
  CONSTRAINT `f_ibfk_1` FOREIGN KEY (`f_id`) REFERENCES `p` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ;

insert into p select 1,'aaa';

insert into f select 1,1;

#Repeatedly execute the following SQL until you generate thousands of data
insert into f select a.id + b.a,a.f_id from f a join (select max(id) as a from f b) b on 1=1;

select count(*) from f;
+----------+
| count(*) |
+----------+
| 131072 |
+----------+

#Slave node, no matter wsrep_slave_fk_checks is on or off.
show variables like 'wsrep_slave_fk_checks';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| wsrep_slave_fk_checks | ON |
+-----------------------+-------+

#Master (Write) node
delete from f;delete from p;

#Slave node down and got errors
2018-03-16 14:33:39 140457990018816 [ERROR] Slave SQL: Could not execute Delete_rows_v1 event on table aaa.p; Cannot delete or update a parent row: a foreign key constraint fails (`aaa`.`f`, CONSTRAINT `f_ibfk_1` FOREIGN KEY (`f_id`) REFERENCES `p` (`id`)), Error_code: 1451; handler error HA_ERR_ROW_IS_REFERENCED; the event's master log FIRST, end_log_pos 108, Internal MariaDB error code: 1451
2018-03-16 14:33:39 140457990018816 [Warning] WSREP: RBR event 3 Delete_rows_v1 apply warning: 152, 128
2018-03-16 14:33:39 140457990018816 [Warning] WSREP: Failed to apply app buffer: seqno: 128, status: 1
         at galera/src/trx_handle.cpp:apply():351
Retrying 2th time
2018-03-16 14:33:39 140457990018816 [ERROR] Slave SQL: Could not execute Delete_rows_v1 event on table aaa.p; Cannot delete or update a parent row: a foreign key constraint fails (`aaa`.`f`, CONSTRAINT `f_ibfk_1` FOREIGN KEY (`f_id`) REFERENCES `p` (`id`)), Error_code: 1451; handler error HA_ERR_ROW_IS_REFERENCED; the event's master log FIRST, end_log_pos 108, Internal MariaDB error code: 1451
2018-03-16 14:33:39 140457990018816 [Warning] WSREP: RBR event 3 Delete_rows_v1 apply warning: 152, 128
2018-03-16 14:33:39 140457990018816 [Warning] WSREP: Failed to apply app buffer: seqno: 128, status: 1
         at galera/src/trx_handle.cpp:apply():351
Retrying 3th time
2018-03-16 14:33:39 140457990018816 [ERROR] Slave SQL: Could not execute Delete_rows_v1 event on table aaa.p; Cannot delete or update a parent row: a foreign key constraint fails (`aaa`.`f`, CONSTRAINT `f_ibfk_1` FOREIGN KEY (`f_id`) REFERENCES `p` (`id`)), Error_code: 1451; handler error HA_ERR_ROW_IS_REFERENCED; the event's master log FIRST, end_log_pos 108, Internal MariaDB error code: 1451
2018-03-16 14:33:39 140457990018816 [Warning] WSREP: RBR event 3 Delete_rows_v1 apply warning: 152, 128
2018-03-16 14:33:39 140457990018816 [Warning] WSREP: Failed to apply app buffer: seqno: 128, status: 1
         at galera/src/trx_handle.cpp:apply():351
Retrying 4th time
2018-03-16 14:33:39 140457990018816 [ERROR] Slave SQL: Could not execute Delete_rows_v1 event on table aaa.p; Cannot delete or update a parent row: a foreign key constraint fails (`aaa`.`f`, CONSTRAINT `f_ibfk_1` FOREIGN KEY (`f_id`) REFERENCES `p` (`id`)), Error_code: 1451; handler error HA_ERR_ROW_IS_REFERENCED; the event's master log FIRST, end_log_pos 108, Internal MariaDB error code: 1451
2018-03-16 14:33:39 140457990018816 [Warning] WSREP: RBR event 3 Delete_rows_v1 apply warning: 152, 128
2018-03-16 14:33:39 140457990018816 [ERROR] WSREP: Failed to apply trx: source: 849e805c-1dd2-11e8-aa79-eb31f2b81368 version: 3 local: 0 state: APPLYING flags: 1 conn_id: 87 trx_id: 12277 seqnos (l: 33, g: 128, s: 127, d: 126, ts: 1358419270394303)
2018-03-16 14:33:39 140457990018816 [ERROR] WSREP: Failed to apply trx 128 4 times
2018-03-16 14:33:39 140457990018816 [ERROR] WSREP: Node consistency compromised, aborting...

#Slave processlist
#Write set 127 is "delete from f;"
#Write set 128 is "delete from p;"
#Slave concurrently apply the two sql
!image-2018-03-21-10-45-36-084.png|thumbnail!
{code}

*This error didn't reproduce on MariaDB 10.2.7 with wsrep_slave_threads>1*

*err.log*
{code:java}
[ERROR] Slave SQL: Could not execute Delete_rows_v1 event on table aaa.p; Cannot delete or update a parent row: a foreign key constraint fails (`aaa`.`f`, CONSTRAINT `f_ibfk_1` FOREIGN KEY (`f_id`) REFERENCES `p` (`id`)), Error_code: 1451; handler error HA_ERR_ROW_IS_REFERENCED; the event's master log FIRST, end_log_pos 108, Internal MariaDB error code: 1451
2018-03-16 14:33:39 140457990018816 [Warning] WSREP: RBR event 3 Delete_rows_v1 apply warning: 152, 128
2018-03-16 14:33:39 140457990018816 [Warning] WSREP: Failed to apply app buffer: seqno: 128, status: 1
         at galera/src/trx_handle.cpp:apply():351
{code}

*Reproduce*
*wsrep_slave_threads>1*
{code:java}
CREATE TABLE `p` (
  `id` int(11) NOT NULL,
  `a` varchar(33) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ;

CREATE TABLE `f` (
  `id` int(11) NOT NULL,
  `f_id` int(11) DEFAULT NULL,
  KEY `f_id` (`f_id`),
  CONSTRAINT `f_ibfk_1` FOREIGN KEY (`f_id`) REFERENCES `p` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ;

insert into p select 1,'aaa';

insert into f select 1,1;

#Repeatedly execute the following SQL until you generate thousands of data
insert into f select a.id + b.a,a.f_id from f a join (select max(id) as a from f b) b on 1=1;

select count(*) from f;
+----------+
| count(*) |
+----------+
| 131072 |
+----------+

#Slave node, no matter wsrep_slave_fk_checks is on or off.
show variables like 'wsrep_slave_fk_checks';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| wsrep_slave_fk_checks | ON |
+-----------------------+-------+

#Master (Write) node
delete from f;delete from p;

#Slave node down and got errors
2018-03-16 14:33:39 140457990018816 [ERROR] Slave SQL: Could not execute Delete_rows_v1 event on table aaa.p; Cannot delete or update a parent row: a foreign key constraint fails (`aaa`.`f`, CONSTRAINT `f_ibfk_1` FOREIGN KEY (`f_id`) REFERENCES `p` (`id`)), Error_code: 1451; handler error HA_ERR_ROW_IS_REFERENCED; the event's master log FIRST, end_log_pos 108, Internal MariaDB error code: 1451
2018-03-16 14:33:39 140457990018816 [Warning] WSREP: RBR event 3 Delete_rows_v1 apply warning: 152, 128
2018-03-16 14:33:39 140457990018816 [Warning] WSREP: Failed to apply app buffer: seqno: 128, status: 1
         at galera/src/trx_handle.cpp:apply():351
Retrying 2th time
2018-03-16 14:33:39 140457990018816 [ERROR] Slave SQL: Could not execute Delete_rows_v1 event on table aaa.p; Cannot delete or update a parent row: a foreign key constraint fails (`aaa`.`f`, CONSTRAINT `f_ibfk_1` FOREIGN KEY (`f_id`) REFERENCES `p` (`id`)), Error_code: 1451; handler error HA_ERR_ROW_IS_REFERENCED; the event's master log FIRST, end_log_pos 108, Internal MariaDB error code: 1451
2018-03-16 14:33:39 140457990018816 [Warning] WSREP: RBR event 3 Delete_rows_v1 apply warning: 152, 128
2018-03-16 14:33:39 140457990018816 [Warning] WSREP: Failed to apply app buffer: seqno: 128, status: 1
         at galera/src/trx_handle.cpp:apply():351
Retrying 3th time
2018-03-16 14:33:39 140457990018816 [ERROR] Slave SQL: Could not execute Delete_rows_v1 event on table aaa.p; Cannot delete or update a parent row: a foreign key constraint fails (`aaa`.`f`, CONSTRAINT `f_ibfk_1` FOREIGN KEY (`f_id`) REFERENCES `p` (`id`)), Error_code: 1451; handler error HA_ERR_ROW_IS_REFERENCED; the event's master log FIRST, end_log_pos 108, Internal MariaDB error code: 1451
2018-03-16 14:33:39 140457990018816 [Warning] WSREP: RBR event 3 Delete_rows_v1 apply warning: 152, 128
2018-03-16 14:33:39 140457990018816 [Warning] WSREP: Failed to apply app buffer: seqno: 128, status: 1
         at galera/src/trx_handle.cpp:apply():351
Retrying 4th time
2018-03-16 14:33:39 140457990018816 [ERROR] Slave SQL: Could not execute Delete_rows_v1 event on table aaa.p; Cannot delete or update a parent row: a foreign key constraint fails (`aaa`.`f`, CONSTRAINT `f_ibfk_1` FOREIGN KEY (`f_id`) REFERENCES `p` (`id`)), Error_code: 1451; handler error HA_ERR_ROW_IS_REFERENCED; the event's master log FIRST, end_log_pos 108, Internal MariaDB error code: 1451
2018-03-16 14:33:39 140457990018816 [Warning] WSREP: RBR event 3 Delete_rows_v1 apply warning: 152, 128
2018-03-16 14:33:39 140457990018816 [ERROR] WSREP: Failed to apply trx: source: 849e805c-1dd2-11e8-aa79-eb31f2b81368 version: 3 local: 0 state: APPLYING flags: 1 conn_id: 87 trx_id: 12277 seqnos (l: 33, g: 128, s: 127, d: 126, ts: 1358419270394303)
2018-03-16 14:33:39 140457990018816 [ERROR] WSREP: Failed to apply trx 128 4 times
2018-03-16 14:33:39 140457990018816 [ERROR] WSREP: Node consistency compromised, aborting...

#Slave processlist
#Write set 127 is "delete from f;"
#Write set 128 is "delete from p;"
#Slave concurrently apply the two sql
!image-2018-03-21-10-45-36-084.png|thumbnail!
{code}

*This error didn't reproduce on MariaDB 10.2.7 with wsrep_slave_threads>1*

Elena Stepanova made changes - 2018-03-21 08:32

Fix Version/s		10.2 [ 14601 ]
Assignee		Sachin Setiya [ sachin.setiya.007 ]
Priority	Blocker [ 1 ]	Critical [ 2 ]

Sachin Setiya (Inactive) made changes - 2018-04-02 12:06

Attachment		galera_mdev_15611.cnf [ 45420 ]
Attachment		galera_mdev_15611.test [ 45421 ]

Sachin Setiya (Inactive) added a comment - 2018-04-02 12:07

Thanks for the test case I am looking into it galera_mdev_15611.cnf galera_mdev_15611.test

Sachin Setiya (Inactive) added a comment - 2018-04-02 12:07 Thanks for the test case I am looking into it galera_mdev_15611.cnf galera_mdev_15611.test

Sachin Setiya (Inactive) added a comment - 2018-04-02 14:49

Actually I am not sure if this is bug or not, If we try to try to increase the may_apply_attempts

diff --git a/galera/src/replicator_smm.cpp b/galera/src/replicator_smm.cpp

index 48026f5..a45ee16 100644

--- a/galera/src/replicator_smm.cpp

+++ b/galera/src/replicator_smm.cpp

@@ -24,7 +24,7 @@ apply_trx_ws(void*                    recv_ctx,

              const wsrep_trx_meta_t&  meta)

     using galera::TrxHandle;

-    static const size_t max_apply_attempts(4);

+    static const size_t max_apply_attempts(100);

     size_t attempts(1);

do

We can make the test case to pass.
Hi seppo what you think ?

Sachin Setiya (Inactive) added a comment - 2018-04-02 14:49 Actually I am not sure if this is bug or not, If we try to try to increase the may_apply_attempts diff --git a/galera/src/replicator_smm.cpp b/galera/src/replicator_smm.cpp index 48026f5..a45ee16 100644 --- a/galera/src/replicator_smm.cpp +++ b/galera/src/replicator_smm.cpp @@ - 24 , 7 + 24 , 7 @@ apply_trx_ws( void * recv_ctx, const wsrep_trx_meta_t& meta) { using galera::TrxHandle; - static const size_t max_apply_attempts( 4 ); + static const size_t max_apply_attempts( 100 ); size_t attempts( 1 ); do We can make the test case to pass. Hi seppo what you think ?

Sachin Setiya (Inactive) added a comment - 2018-04-03 08:07

Actually we don't need complicated insert
Even normal insert fails

--source include/galera_cluster.inc

--source include/have_innodb.inc

--connection node_1

CREATE TABLE t1 (

  id int primary key

);

CREATE TABLE t2 (

  id int primary key ,

  f_id int DEFAULT NULL, FOREIGN KEY(f_id)  REFERENCES t1 (id)

);

insert into t1 select 1;

--let $count=200

while($count)

  #Repeatedly execute the following SQL until you generate thousands of data

  --eval insert into t2 values ($count, 1);

  --dec $count

select count(*) from t1;

delete from t2;

delete from t1;

If we remove primary key from t2 then it will make slave node (node 2) to crash , with primary key , node 2 is aborted , which is not the correct behaviour

Sachin Setiya (Inactive) added a comment - 2018-04-03 08:07 Actually we don't need complicated insert Even normal insert fails --source include/galera_cluster.inc --source include/have_innodb.inc --connection node_1 CREATE TABLE t1 ( id int primary key ); CREATE TABLE t2 ( id int primary key , f_id int DEFAULT NULL , FOREIGN KEY (f_id) REFERENCES t1 (id) ); insert into t1 select 1; --let $count=200 while($ count ) { #Repeatedly execute the following SQL until you generate thousands of data --eval insert into t2 values ($count, 1); --dec $count } select count (*) from t1; delete from t2; delete from t1; If we remove primary key from t2 then it will make slave node (node 2) to crash , with primary key , node 2 is aborted , which is not the correct behaviour

Sachin Setiya (Inactive) added a comment - 2018-04-03 08:08

Comment from seppo

I did some FK experiments with MariaDB 10.2 (latest development HEAD), and it turns out that, for an child table delete, only child table key is pushed into write set
this means that the reference to the parent table row is not present in the write set at all. And therefore parallel applying allows processing parent table delete in parallel with child table delete

Sachin Setiya (Inactive) added a comment - 2018-04-03 08:08 Comment from seppo I did some FK experiments with MariaDB 10.2 (latest development HEAD), and it turns out that, for an child table delete, only child table key is pushed into write set this means that the reference to the parent table row is not present in the write set at all. And therefore parallel applying allows processing parent table delete in parallel with child table delete

Devin Yu added a comment - 2018-04-03 08:44

Table t2 without PK, the purpose is to let "be removed from t2;" perform slowly, so slave can apply "delete from t2;" and "delete from t1;" concurrently .
There is a PK in the table of my real environment where we encountered this bug.
BTW, the two SQL can't be in the same transaction, because the granularity of concurrency is transaction
Please redo this test in MariaDB 10.2.7, will never reproduce this bug no matter there is a PK in t2 or not.

Thank you.

Devin Yu added a comment - 2018-04-03 08:44 Table t2 without PK, the purpose is to let "be removed from t2;" perform slowly, so slave can apply "delete from t2;" and "delete from t1;" concurrently . There is a PK in the table of my real environment where we encountered this bug. BTW, the two SQL can't be in the same transaction, because the granularity of concurrency is transaction Please redo this test in MariaDB 10.2.7, will never reproduce this bug no matter there is a PK in t2 or not. Thank you.

Sachin Setiya (Inactive) added a comment - 2018-04-03 09:48

Hi 920895156@qq.com ,

Thanks for reminding that it does not fail in 10.2.7 , I have run git bisect and 2f342c450755fe7b6c39ec69930d240047c8242d is first bad commit, I am trying to understand what is does

Sachin Setiya (Inactive) added a comment - 2018-04-03 09:48 Hi 920895156@qq.com , Thanks for reminding that it does not fail in 10.2.7 , I have run git bisect and 2f342c450755fe7b6c39ec69930d240047c8242d is first bad commit, I am trying to understand what is does

Sachin Setiya (Inactive) added a comment - 2018-04-04 06:57

Patch link http://lists.askmonty.org/pipermail/commits/2018-April/012274.html

Sachin Setiya (Inactive) added a comment - 2018-04-04 06:57 Patch link http://lists.askmonty.org/pipermail/commits/2018-April/012274.html

Sachin Setiya (Inactive) made changes - 2018-04-04 06:57

Status

Open [ 1 ]

In Progress [ 3 ]

Sachin Setiya (Inactive) made changes - 2018-04-04 07:00

Assignee	Sachin Setiya [ sachin.setiya.007 ]	Marko Mäkelä [ marko ]
Status	In Progress [ 3 ]	In Review [ 10002 ]

Marko Mäkelä made changes - 2018-04-05 06:03

Link

This issue relates to ~~MDEV-14222~~ [ ~~MDEV-14222~~ ]

Marko Mäkelä made changes - 2018-04-05 06:03

Link

This issue relates to ~~MDEV-13678~~ [ ~~MDEV-13678~~ ]

Marko Mäkelä made changes - 2018-04-05 06:03

Link

This issue relates to ~~MDEV-13498~~ [ ~~MDEV-13498~~ ]

Marko Mäkelä added a comment - 2018-04-05 06:03

The submitted patch would seem to reintroduce ~~MDEV-13498~~, because the Galera adjustments would be executed even when Galera is disabled (!wsrep_on_trx(trx)).

The function wsrep_must_process_fk() was introduced in ~~MDEV-13498~~/~~MDEV-13246~~ and subsequently changed in ~~MDEV-13678~~.

sachin.setiya.007, in which exact code revisions can the failure be repeated? Note that ~~MDEV-14222~~ (MariaDB 10.2.13) changed some FOREIGN KEY processing by reverting a change that was made in MySQL 5.7.2 and reverted in 5.7.21.

Marko Mäkelä added a comment - 2018-04-05 06:03 The submitted patch would seem to reintroduce MDEV-13498 , because the Galera adjustments would be executed even when Galera is disabled ( !wsrep_on_trx(trx) ). The function wsrep_must_process_fk() was introduced in MDEV-13498 / MDEV-13246 and subsequently changed in MDEV-13678 . sachin.setiya.007 , in which exact code revisions can the failure be repeated? Note that MDEV-14222 (MariaDB 10.2.13) changed some FOREIGN KEY processing by reverting a change that was made in MySQL 5.7.2 and reverted in 5.7.21.

Marko Mäkelä made changes - 2018-04-05 06:03

Link

This issue relates to ~~MDEV-13246~~ [ ~~MDEV-13246~~ ]

Marko Mäkelä added a comment - 2018-04-05 07:11

I think that the predicate should be rewritten like this:

inline bool wsrep_must_process_fk(const upd_node_t* node, const trx_t* trx)

	if (!wsrep_on_trx(trx)) {

		return false;

	return que_node_get_type(node->common.parent) != QUE_NODE_UPDATE

		|| static_cast<upd_node_t*>(node->common.parent)->cascade_node

		!= node;

That is, the condition on WSREP_ON is preserved, but the condition on node is negated.

Marko Mäkelä added a comment - 2018-04-05 07:11 I think that the predicate should be rewritten like this: inline bool wsrep_must_process_fk( const upd_node_t* node, const trx_t* trx) { if (!wsrep_on_trx(trx)) { return false ; } return que_node_get_type(node->common.parent) != QUE_NODE_UPDATE || static_cast <upd_node_t*>(node->common.parent)->cascade_node != node; } That is, the condition on WSREP_ON is preserved, but the condition on node is negated.

Marko Mäkelä made changes - 2018-04-05 07:11

Assignee	Marko Mäkelä [ marko ]	Sachin Setiya [ sachin.setiya.007 ]
Status	In Review [ 10002 ]	Stalled [ 10000 ]

Marko Mäkelä made changes - 2018-04-05 07:12

Affects Version/s		10.3.1 [ 22532 ]
Affects Version/s		10.2.8 [ 22544 ]

Marko Mäkelä made changes - 2018-04-05 07:12

Fix Version/s

10.3 [ 22126 ]

Sachin Setiya (Inactive) added a comment - 2018-04-08 16:50

Hi Marko!

Thanks for the review,
The issue was introduced in 2f342c4 to all version upto 10.2

Sachin Setiya (Inactive) added a comment - 2018-04-08 16:50 Hi Marko! Thanks for the review, The issue was introduced in 2f342c4 to all version upto 10.2

Sachin Setiya (Inactive) added a comment - 2018-04-08 17:08

Hi Marko!

I pushed it into bb-mdev-15611 branch.

Sachin Setiya (Inactive) added a comment - 2018-04-08 17:08 Hi Marko! I pushed it into bb-mdev-15611 branch.

Sachin Setiya (Inactive) made changes - 2018-04-19 11:06

Fix Version/s		10.2.15 [ 23006 ]
Fix Version/s	10.2 [ 14601 ]
Fix Version/s	10.3 [ 22126 ]
Resolution		Fixed [ 1 ]
Status	Stalled [ 10000 ]	Closed [ 6 ]

Sachin Setiya (Inactive) made changes - 2018-05-02 09:32

Link

This issue is duplicated by ~~MDEV-15252~~ [ ~~MDEV-15252~~ ]

Guillaume Lefranc added a comment - 2018-05-30 10:03

Is there a fix for MariaDB 10.1?

Guillaume Lefranc added a comment - 2018-05-30 10:03 Is there a fix for MariaDB 10.1?

Jan Lindström (Inactive) made changes - 2018-06-15 10:28

Fix Version/s

10.1.35 [ 23116 ]

Marko Mäkelä made changes - 2018-10-20 12:36

Assignee

Sachin Setiya [ sachin.setiya.007 ]

Jan Lindström [ jplindst ]

Sergei Golubchik made changes - 2018-12-20 15:29

Link

This issue relates to ~~MDEV-18032~~ [ ~~MDEV-18032~~ ]

TAO ZHOU added a comment - 2019-01-09 03:32

Is this already fixed. I am running 10.2.19 and still getting the same error. It didn't kill itself though.

2019-01-09 13:51:46 50734833152 [ERROR] Slave SQL: Could not execute Delete_rows_v1 event on table w2live_college.WebSiteLayout; Cannot delete or update a parent row: a foreign key constraint fails (`w2live_college`.`WebNodeType`, CONSTRAINT `WebNodeType_ibfk_1` FOREIGN KEY (`webSiteLayoutId`) REFERENCES `WebSiteLayout` (`id`)), Error_code: 1451; handler error HA_ERR_ROW_IS_REFERENCED; the event's master log FIRST, end_log_pos 196, Internal MariaDB error code: 1451

2019-01-09 13:51:46 50734833152 [Warning] WSREP: RBR event 3 Delete_rows_v1 apply warning: 152, 1398192281

2019-01-09 13:51:46 50734833152 [Warning] WSREP: Failed to apply app buffer: seqno: 1398192281, status: 1

	 at galera/src/trx_handle.cpp:apply():353

Retrying 2th time

TAO ZHOU added a comment - 2019-01-09 03:32 Is this already fixed. I am running 10.2.19 and still getting the same error. It didn't kill itself though. 2019 - 01 - 09 13 : 51 : 46 50734833152 [ERROR] Slave SQL: Could not execute Delete_rows_v1 event on table w2live_college.WebSiteLayout; Cannot delete or update a parent row: a foreign key constraint fails (`w2live_college`.`WebNodeType`, CONSTRAINT `WebNodeType_ibfk_1` FOREIGN KEY (`webSiteLayoutId`) REFERENCES `WebSiteLayout` (`id`)), Error_code: 1451 ; handler error HA_ERR_ROW_IS_REFERENCED; the event's master log FIRST, end_log_pos 196 , Internal MariaDB error code: 1451 2019 - 01 - 09 13 : 51 : 46 50734833152 [Warning] WSREP: RBR event 3 Delete_rows_v1 apply warning: 152 , 1398192281 2019 - 01 - 09 13 : 51 : 46 50734833152 [Warning] WSREP: Failed to apply app buffer: seqno: 1398192281 , status: 1 at galera/src/trx_handle.cpp:apply(): 353 Retrying 2th time

TAO ZHOU added a comment - 2019-01-09 05:21

It's still crashing.

TAO ZHOU added a comment - 2019-01-09 05:21 It's still crashing.

Marko Mäkelä made changes - 2019-01-15 06:24

Link

This issue relates to ~~MDEV-18174~~ [ ~~MDEV-18174~~ ]

Pim Rupert made changes - 2019-01-28 10:26

Link

This issue is blocked by ~~MDEV-18393~~ [ ~~MDEV-18393~~ ]

Pim Rupert made changes - 2019-01-28 17:37

Link

This issue is blocked by ~~MDEV-18393~~ [ ~~MDEV-18393~~ ]

Pim Rupert made changes - 2019-01-28 17:39

Link

This issue relates to ~~MDEV-18393~~ [ ~~MDEV-18393~~ ]

Sergei Golubchik made changes - 2021-12-06 21:46

Workflow

MariaDB v3 [ 86114 ]

MariaDB v4 [ 153988 ]

Jira Automation (IT) made changes - 2024-07-04 07:54

Zendesk Related Tickets

134580 183212

People

Assignee:: Jan Lindström (Inactive)

Reporter:: Devin Yu

Votes:: 0 Vote for this issue

Watchers:: 13 Start watching this issue

Dates

Created:: 2018-03-21 02:57

Updated:: 2024-07-08 00:22

Resolved:: 2018-04-19 11:06

Git Integration

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

MariaDB Server

Details

Description

Attachments

Attachments

Issue Links

Activity

People

Dates

Git Integration