Deadlocks and duplicate Entries Load Data Infile

[Marcel Schneider]

After Upgrading to 5.7.18-15-57 Percona XtraDB Cluster Load Data File always ends up in Deadlock if there are more than 10k Rows in the file. After Deadlock happens, there are duplicate entries and much more rows in the table than in the file. The file does not have any increment id. The error log shows certification failure. The Cluster has three servers at the moment.

[Marcel Schneider (mschneiderfse)]

This is the log file while deadlock

[Przemek (pmalkowski)]

I can confirm this is the case when log_bin is disabled, but not when it is enabled.
Interestingly, depending on table schema, either a deadlock or duplicate key error is returned.

mysql> select @@version,@@log_bin,@@log_slave_updates;
+--------------+-----------+---------------------+
| @@version | @@log_bin | @@log_slave_updates |
+--------------+-----------+---------------------+
| 5.7.18-15-57 | 0 | 0 |
+--------------+-----------+---------------------+
1 row in set (0.00 sec)

mysql> show create table B_test\G
*************************** 1. row ***************************
       Table: B_test
Create Table: CREATE TABLE `B_test` (
  `INDaa` decimal(1,0) DEFAULT NULL,
  `EUaa` decimal(1,0) DEFAULT NULL,
  `BAaa` decimal(3,0) DEFAULT NULL,
  `REKaa` decimal(10,0) NOT NULL,
  `IBaa` varchar(34) NOT NULL,
  `SALDOaa` decimal(13,2) DEFAULT NULL,
  `SALDO_aa` decimal(11,2) DEFAULT NULL,
  `SALDO_Caa` decimal(11,2) DEFAULT NULL,
  `SALDO_Vaa` decimal(13,2) DEFAULT NULL,
  `REK_SRaa` decimal(3,0) DEFAULT NULL,
  `RAPaa` decimal(2,0) DEFAULT NULL,
  `VERHaa` decimal(1,0) DEFAULT NULL,
  `ZAB` decimal(1,0) DEFAULT NULL,
  `SFIATIG` decimal(1,0) DEFAULT NULL,
  `DFIATIE_UIT` decimal(1,0) DEFAULT NULL,
  `IIV_RFTE` decimal(1,0) DEFAULT NULL,
  `INDAILEN` decimal(1,0) DEFAULT NULL,
  `DAT_TST_TRASA` decimal(8,0) DEFAULT NULL,
  `JKOM_BH_KANTF` decimal(4,0) DEFAULT NULL,
  `RDEDEN` decimal(3,0) DEFAULT NULL,
  `HEGRLD_VAAGJ` decimal(1,0) DEFAULT NULL,
  `RAT_OPENIKL` decimal(8,0) DEFAULT NULL,
  `RED_LIMIT` decimal(11,2) DEFAULT NULL,
  PRIMARY KEY (`REKaa`),
  UNIQUE KEY `BREKIBANIDX` (`IBaa`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> load data local infile '/var/lib/mysql-files/BREK_test1.csv' into table B_test;
ERROR 1213 (40001): WSREP detected deadlock/conflict and aborted the transaction. Try restarting the transaction

Works OK with log_bin enabled:

mysql> select @@version,@@log_bin,@@log_slave_updates;
+------------------+-----------+---------------------+
| @@version | @@log_bin | @@log_slave_updates |
+------------------+-----------+---------------------+
| 5.7.18-15-57-log | 1 | 0 |
+------------------+-----------+---------------------+
1 row in set (0.00 sec)

mysql> truncate B_test;
Query OK, 0 rows affected (0.06 sec)

mysql> load data local infile '/var/lib/mysql-files/BREK_test1.csv' into table B_test;
Query OK, 17542 rows affected (1.62 sec)
Records: 17542 Deleted: 0 Skipped: 0 Warnings: 0

For a sysbench table, result different (log_bin disabled):

mysql> truncate sbtest1;
Query OK, 0 rows affected (0.03 sec)

mysql> load data local infile '/var/lib/mysql-files/sbtest1.csv' into table sbtest1;
Query OK, 5000 rows affected, 10000 warnings (0.91 sec)
Records: 15000 Deleted: 0 Skipped: 10000 Warnings: 10000

mysql> show warnings;
+---------+------+-----------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------+
| Warning | 1062 | Duplicate entry '2' for key 'PRIMARY' |
| Warning | 1062 | Duplicate entry '5' for key 'PRIMARY' |
| Warning...

[Krunal Bauskar (krunal-bauskar)]

commit 27ca835b012dfdaeffd7ae3b9d2ca0b73baad3a9
Merge: f9ade53 349c07f
Author: Krunal Bauskar <email address hidden>
Date: Wed Aug 2 08:30:22 2017 +0530

    Merge pull request #494 from kbauskar/5.7-pxc-842

    - PXC#842: Deadlocks and duplicate Entries Load Data Infile

commit 349c07fdd162382f53eb5b7855c2cca8aaff6fe5
Author: Krunal Bauskar <email address hidden>
Date: Tue Aug 1 14:06:51 2017 +0530

    - PXC#842: Deadlocks and duplicate Entries Load Data Infile

      Problem:
      -------

      * Load Data Infile (LDI) with log-bin=0 fails.
      * LDI with > 10K rows is split into mini transactions, each having
        10K rows (except the last bucket). Each mini transaction is committed
        independently once the bucket is full.
        A new transaction is started before processing next set of bucket.
      * New transaction should be started after the transaction is committed
        in wsrep and innodb world but the exsiting flow tried start new
        transaction before innodb commit was complete.
      * Recent change for performance optimization has tweaked innodb
        code to invoke post-commit that committed newly started transaction.
        In turn followup bucket is processed without active transaction.

      Solution:
      ---------

      * Ensure that new transaction starts only after the existing mini
        transaction is committed in wsrep and innodb world.

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PXC-842