Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-15611

Due to the failure of foreign key detection, Galera slave node killed himself.

Details

    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Fixed
    • 10.2.8, 10.2.10, 10.2.12, 10.3.1
    • 10.1.35, 10.2.15
    • Galera

    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

        Issue Links

          Activity

            920895156@qq.com Devin Yu created issue -
            920895156@qq.com Devin Yu made changes -
            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.*
            920895156@qq.com Devin Yu made changes -
            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*
            920895156@qq.com Devin Yu made changes -
            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*
            elenst Elena Stepanova made changes -
            Fix Version/s 10.2 [ 14601 ]
            Assignee Sachin Setiya [ sachin.setiya.007 ]
            Priority Blocker [ 1 ] Critical [ 2 ]
            sachin.setiya.007 Sachin Setiya (Inactive) made changes -
            Attachment galera_mdev_15611.cnf [ 45420 ]
            Attachment galera_mdev_15611.test [ 45421 ]
            sachin.setiya.007 Sachin Setiya (Inactive) made changes -
            Status Open [ 1 ] In Progress [ 3 ]
            sachin.setiya.007 Sachin Setiya (Inactive) made changes -
            Assignee Sachin Setiya [ sachin.setiya.007 ] Marko Mäkelä [ marko ]
            Status In Progress [ 3 ] In Review [ 10002 ]
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            Assignee Marko Mäkelä [ marko ] Sachin Setiya [ sachin.setiya.007 ]
            Status In Review [ 10002 ] Stalled [ 10000 ]
            marko Marko Mäkelä made changes -
            Affects Version/s 10.3.1 [ 22532 ]
            Affects Version/s 10.2.8 [ 22544 ]
            marko Marko Mäkelä made changes -
            Fix Version/s 10.3 [ 22126 ]
            sachin.setiya.007 Sachin Setiya (Inactive) made changes -
            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.007 Sachin Setiya (Inactive) made changes -
            jplindst Jan Lindström (Inactive) made changes -
            Fix Version/s 10.1.35 [ 23116 ]
            marko Marko Mäkelä made changes -
            Assignee Sachin Setiya [ sachin.setiya.007 ] Jan Lindström [ jplindst ]
            serg Sergei Golubchik made changes -
            marko Marko Mäkelä made changes -
            prupert Pim Rupert made changes -
            prupert Pim Rupert made changes -
            prupert Pim Rupert made changes -
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 86114 ] MariaDB v4 [ 153988 ]
            mariadb-jira-automation Jira Automation (IT) made changes -
            Zendesk Related Tickets 134580 183212

            People

              jplindst Jan Lindström (Inactive)
              920895156@qq.com Devin Yu
              Votes:
              0 Vote for this issue
              Watchers:
              13 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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