Galera Cluster crashed after [ERROR] Slave SQL: Could not execute Delete_rows event on table edi.playeraccount; Cannot delete or update a parent row

342 views

Skip to first unread message

tomek....@gmail.com

unread,

Dec 11, 2014, 7:31:09 AM12/11/14

to codersh...@googlegroups.com

The cluster consists of 3 nodes (prod1galera1, prod1galera2 and prod1galera3) [managed by Cluster Control]

Currently prod1galera1 (Percona Cluster 5.6.21) replicates from prod1mysql2 (MySQL 5.5.35) [will replace it totally in near future]

We have faced a problem when committing transaction below (deleting rows)

---------------------------------------------------------------------------------------------------------------------------------------------------

Last transaction committed on prod1galera1

SET TIMESTAMP=1418179678/*!*/;

BEGIN

/*!*/;

# at 145967352

#141210 3:47:58 server id 1 end_log_pos 145967360 Query thread_id=1526733 exec_time=4294966807 error_code=0

SET TIMESTAMP=1418179678/*!*/;

/* delete com.edigaming.lgp.PlayerAccount */ delete from PlayerAccount where id=26018 and version=0

/*!*/;

# at 145967519

#141210 3:47:58 server id 1 end_log_pos 145967527 Query thread_id=1526733 exec_time=4294966807 error_code=0

SET TIMESTAMP=1418179678/*!*/;

/* delete com.edigaming.lgp.PlayerAccount */ delete from PlayerAccount where id=26035 and version=0

/*!*/;

# at 145967686

#141210 3:47:58 server id 1 end_log_pos 145967694 Query thread_id=1526733 exec_time=4294966807 error_code=0

SET TIMESTAMP=1418179678/*!*/;

/* delete com.edigaming.lgp.PlayerAccount */ delete from PlayerAccount where id=26194 and version=0

/*!*/;

# at 145967853

#141210 3:47:58 server id 1 end_log_pos 145967861 Query thread_id=1526733 exec_time=4294966807 error_code=0

SET TIMESTAMP=1418179678/*!*/;

/* delete com.edigaming.lgp.PlayerAccount */ delete from PlayerAccount where id=26206 and version=0

/*!*/;

# at 145968020

#141210 3:47:58 server id 1 end_log_pos 145967888 Xid = 50249859

COMMIT/*!*/;

# at 145968047

Slave stopped.

-----------------------------------------------------------------------------------------------------------------------------------------------------------------

This is the position prod1galera1 stopped replicating from prod1mysql2

Relay_Log_File: mysqld-relay-bin.000071

Relay_Log_Pos: 145968047

Slave_IO_Running: Yes

Slave_SQL_Running: No

Installed versions on the Percona Cluster

-----------------------------------------------------------------------------------------------------------------------------------------------------------------

ii percona-toolkit 2.2.12 all Advanced MySQL and system command-line tools

ii percona-xtrabackup 2.2.6-5042-1.trusty amd64 Open source backup tool for InnoDB and XtraDB

ii percona-xtradb-cluster-client-5.6 5.6.21-25.8-938.trusty amd64 Percona XtraDB Cluster database client binaries

ii percona-xtradb-cluster-common-5.6 5.6.21-25.8-938.trusty amd64 Percona XtraDB Cluster database common files (e.g. /etc/mysql/my.cnf)

ii percona-xtradb-cluster-galera-3.x 3.8.3390.trusty amd64 Galera components of Percona XtraDB Cluster

ii percona-xtradb-cluster-server-5.6 5.6.21-25.8-938.trusty amd64 Percona XtraDB Cluster database server binaries

The same error has happened many times

-----------------------------------------------------------------------------------------------------------------------------------------------------------------

4 2014-12-01 13:24:04 16825 [ERROR] Slave SQL: Could not execute Delete_rows event on table edi.playeraccount; Cannot delete or update a parent row

4 2014-12-01 13:24:04 19655 [ERROR] Slave SQL: Could not execute Delete_rows event on table edi.playeraccount; Cannot delete or update a parent row

4 2014-12-01 13:26:22 19977 [ERROR] Slave SQL: Could not execute Delete_rows event on table edi.playeraccount; Cannot delete or update a parent row

4 2014-12-04 04:38:09 23574 [ERROR] Slave SQL: Could not execute Delete_rows event on table edi.playeraccount; Cannot delete or update a parent row

4 2014-12-04 04:38:09 26687 [ERROR] Slave SQL: Could not execute Delete_rows event on table edi.playeraccount; Cannot delete or update a parent row

4 2014-12-06 04:00:09 20756 [ERROR] Slave SQL: Could not execute Delete_rows event on table edi.playeraccount; Cannot delete or update a parent row

4 2014-12-06 04:00:09 5503 [ERROR] Slave SQL: Could not execute Delete_rows event on table edi.playeraccount; Cannot delete or update a parent row

4 2014-12-10 03:42:24 17842 [ERROR] Slave SQL: Could not execute Delete_rows event on table edi.playeraccount; Cannot delete or update a parent row

4 2014-12-10 03:42:24 848 [ERROR] Slave SQL: Could not execute Delete_rows event on table edi.playeraccount; Cannot delete or update a parent row

logs: prod1galera1

-----------------------------------------------------------------------------------------------------------------------------------------------------------------

2014-12-10 03:42:24 21645 [Note] WSREP: (d73075cb, 'tcp://0.0.0.0:4567') address 'tcp://10.1.160.6:4567' pointing to uuid d73075cb is blacklisted, skipping

2014-12-10 03:42:24 21645 [Note] WSREP: (d73075cb, 'tcp://0.0.0.0:4567') turning message relay requesting on, nonlive peers: tcp://10.1.160.7:4567

2014-12-10 03:42:24 21645 [Note] WSREP: declaring 6637501f at tcp://10.1.160.8:4567 stable

2014-12-10 03:42:24 21645 [Note] WSREP: forgetting 91cefdab (tcp://10.1.160.7:4567)

2014-12-10 03:42:24 21645 [Note] WSREP: (d73075cb, 'tcp://0.0.0.0:4567') address 'tcp://10.1.160.6:4567' pointing to uuid d73075cb is blacklisted, skipping

2014-12-10 03:42:24 21645 [Note] WSREP: (d73075cb, 'tcp://0.0.0.0:4567') turning message relay requesting off

2014-12-10 03:42:24 21645 [Note] WSREP: Node 6637501f state prim

2014-12-10 03:42:24 21645 [Note] WSREP: view(view_id(NON_PRIM,6637501f,51) memb {

d73075cb,0

} joined {

} left {

} partitioned {

6637501f,0

91cefdab,0

})

2014-12-10 03:42:24 21645 [Note] WSREP: forgetting 6637501f (tcp://10.1.160.8:4567)

2014-12-10 03:42:24 21645 [Note] WSREP: New COMPONENT: primary = no, bootstrap = no, my_idx = 0, memb_num = 1

2014-12-10 03:42:24 21645 [Note] WSREP: Flow-control interval: [16, 16]

2014-12-10 03:42:24 21645 [Note] WSREP: Received NON-PRIMARY.

2014-12-10 03:42:24 21645 [Note] WSREP: Shifting SYNCED -> OPEN (TO: 988903)

2014-12-10 03:42:24 21645 [Note] WSREP: view(view_id(NON_PRIM,d73075cb,52) memb {

d73075cb,0

} joined {

} left {

} partitioned {

6637501f,0

91cefdab,0

})

2014-12-10 03:42:24 21645 [Note] WSREP: New cluster view: global state: 146797dd-71a9-11e4-98e5-3ebe51e67dbd:988903, view# -1: non-Primary, number of nodes: 1, my index: 0, protocol version 3

2014-12-10 03:42:24 21645 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.

2014-12-10 03:42:24 21645 [Note] WSREP: New COMPONENT: primary = no, bootstrap = no, my_idx = 0, memb_num = 1

2014-12-10 03:42:24 21645 [Note] WSREP: Flow-control interval: [16, 16]

2014-12-10 03:42:24 21645 [Note] WSREP: Received NON-PRIMARY.

prod1galera2

-----------------------------------------------------------------------------------------------------------------------------------------------------------------

2014-12-10 03:42:24 17842 [ERROR] Slave SQL: Could not execute Delete_rows event on table edi.playeraccount; Cannot delete or update a parent row: a foreign key constraint fails (`edi`.`accountactivity`, CONSTRAINT `FK_AA_PlayerAccount` FOREIGN KEY (`account_id`) REFERENCES `playeraccount` (`id`)), Error_code: 1451; handler error HA_ERR_ROW_IS_REFERENCED; the event's master log FIRST, end_log_pos 382, Error_code: 1451

2014-12-10 03:42:24 17842 [Warning] WSREP: RBR event 3 Delete_rows apply warning: 152, 988903

2014-12-10 03:42:24 17842 [Warning] WSREP: Failed to apply app buffer: seqno: 988903, status: 1

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

Retrying 2th time

2014-12-10 03:42:24 17842 [Warning] WSREP: RBR event 3 Delete_rows apply warning: 152, 988903

2014-12-10 03:42:24 17842 [Warning] WSREP: Failed to apply app buffer: seqno: 988903, status: 1

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

Retrying 3th time

2014-12-10 03:42:24 17842 [Warning] WSREP: RBR event 3 Delete_rows apply warning: 152, 988903

2014-12-10 03:42:24 17842 [Warning] WSREP: Failed to apply app buffer: seqno: 988903, status: 1

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

Retrying 4th time

2014-12-10 03:42:24 17842 [Warning] WSREP: RBR event 3 Delete_rows apply warning: 152, 988903

2014-12-10 03:42:24 17842 [ERROR] WSREP: Failed to apply trx: source: d73075cb-7eca-11e4-98fd-deafa8f05c9a version: 3 local: 0 state: APPLYING flags: 1 conn_id: 4 trx_id: 1989293 seqnos (l: 161198, g: 988903, s: 988902, d: 988889, ts: 1354044455635970)

2014-12-10 03:42:24 17842 [ERROR] WSREP: Failed to apply trx 988903 4 times

2014-12-10 03:42:24 17842 [ERROR] WSREP: Node consistency compromized, aborting...

2014-12-10 03:42:24 17842 [Note] WSREP: Closing send monitor...

prod1galera3

-----------------------------------------------------------------------------------------------------------------------------------------------------------------

2014-12-10 03:42:24 848 [ERROR] Slave SQL: Could not execute Delete_rows event on table edi.playeraccount; Cannot delete or update a parent row: a foreign key constraint fails (`edi`.`accountactivity`, CONSTRAINT `FK_AA_PlayerAccount` FOREIGN KEY (`account_id`) REFERENCES `playeraccount` (`id`)), Error_code: 1451; handler error HA_ERR_ROW_IS_REFERENCED; the event's master log FIRST, end_log_pos 382, Error_code: 1451

2014-12-10 03:42:24 848 [Warning] WSREP: RBR event 3 Delete_rows apply warning: 152, 988903

2014-12-10 03:42:24 848 [Warning] WSREP: Failed to apply app buffer: seqno: 988903, status: 1

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

Retrying 2th time

2014-12-10 03:42:24 848 [Warning] WSREP: RBR event 3 Delete_rows apply warning: 152, 988903

2014-12-10 03:42:24 848 [Warning] WSREP: Failed to apply app buffer: seqno: 988903, status: 1

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

Retrying 3th time

2014-12-10 03:42:24 848 [Warning] WSREP: RBR event 3 Delete_rows apply warning: 152, 988903

2014-12-10 03:42:24 848 [Warning] WSREP: Failed to apply app buffer: seqno: 988903, status: 1

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

Retrying 4th time

2014-12-10 03:42:24 848 [Warning] WSREP: RBR event 3 Delete_rows apply warning: 152, 988903

2014-12-10 03:42:24 848 [ERROR] WSREP: Failed to apply trx: source: d73075cb-7eca-11e4-98fd-deafa8f05c9a version: 3 local: 0 state: APPLYING flags: 1 conn_id: 4 trx_id: 1989293 seqnos (l: 161217, g: 988903, s: 988902, d: 988889, ts: 1354044455635970)

2014-12-10 03:42:24 848 [ERROR] WSREP: Failed to apply trx 988903 4 times

2014-12-10 03:42:24 848 [ERROR] WSREP: Node consistency compromized, aborting...

2014-12-10 03:42:24 848 [Note] WSREP: Closing send monitor...

2014-12-10 03:42:24 848 [Note] WSREP: Closed send monitor.

2014-12-10 03:42:24 848 [Note] WSREP: gcomm: terminating thread

2014-12-10 03:42:24 848 [Note] WSREP: gcomm: joining thread

2014-12-10 03:42:24 848 [Note] WSREP: gcomm: closing backend

2014-12-10 03:42:24 848 [Note] WSREP: (6637501f, 'tcp://0.0.0.0:4567') address 'tcp://10.1.160.8:4567' pointing to uuid 6637501f is blacklisted, skipping

2014-12-10 03:42:24 848 [Note] WSREP: declaring d73075cb at tcp://10.1.160.6:4567 stable

2014-12-10 03:42:24 848 [Note] WSREP: forgetting 91cefdab (tcp://10.1.160.7:4567)

2014-12-10 03:42:24 848 [Note] WSREP: (6637501f, 'tcp://0.0.0.0:4567') address 'tcp://10.1.160.8:4567' pointing to uuid 6637501f is blacklisted, skipping

2014-12-10 03:42:24 848 [Note] WSREP: Node 6637501f state prim

2014-12-10 03:42:24 848 [Warning] WSREP: user message in state LEAVING

2014-12-10 03:42:24 848 [Warning] WSREP: 6637501f sending install message failed: Transport endpoint is not connected

2014-12-10 03:42:24 848 [Note] WSREP: view(view_id(NON_PRIM,6637501f,51) memb {

6637501f,0

} joined {

} left {

} partitioned {

91cefdab,0

d73075cb,0

})

2014-12-10 03:42:24 848 [Note] WSREP: view((empty))

2014-12-10 03:42:24 848 [Note] WSREP: New COMPONENT: primary = no, bootstrap = no, my_idx = 0, memb_num = 1

2014-12-10 03:42:24 848 [Note] WSREP: gcomm: closed

2014-12-10 03:42:24 848 [Note] WSREP: Flow-control interval: [16, 16]

2014-12-10 03:42:24 848 [Note] WSREP: Received NON-PRIMARY.

2014-12-10 03:42:24 848 [Note] WSREP: Shifting SYNCED -> OPEN (TO: 988903)

2014-12-10 03:42:24 848 [Note] WSREP: Received self-leave message.

2014-12-10 03:42:24 848 [Note] WSREP: Flow-control interval: [0, 0]

2014-12-10 03:42:24 848 [Note] WSREP: Received SELF-LEAVE. Closing connection.

2014-12-10 03:42:24 848 [Note] WSREP: Shifting OPEN -> CLOSED (TO: 988903)

2014-12-10 03:42:24 848 [Note] WSREP: RECV thread exiting 0: Success

2014-12-10 03:42:24 848 [Note] WSREP: recv_thread() joined.

2014-12-10 03:42:24 848 [Note] WSREP: Closing replication queue.

2014-12-10 03:42:24 848 [Note] WSREP: Closing slave action queue.

Thank you in advance for your help,

Best wishes,

Tomek Rog

Franck Leveneur

unread,

Jun 11, 2015, 2:04:52 PM6/11/15

to codersh...@googlegroups.com

Hi,

I have having the same issue with Percona-XtraDB-Cluster-galera-3-3.8-1.

One node replicating to 1 Stand alone Mysql.

I'm using GTID for replication.

Were you able to solve the issue?

Thank you.

Jozef Čuj

unread,

Apr 18, 2018, 8:07:01 AM4/18/18

to codership

Do yo have solution for this issue ?
I have similar problem with galera cluster.
Thanks

Dne čtvrtek 11. prosince 2014 13:31:09 UTC+1 tomek....@gmail.com napsal(a):

Reply all

Reply to author

Forward

0 new messages