Percona XtraDB Cluster moved to https://jira.percona.com/projects/PXC

Partitioning + load data local infile = deadlocks or crash

Bug #1455098 reported by Aleksey Sokolov
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Percona XtraDB Cluster moved to https://jira.percona.com/projects/PXC
Status tracked in 5.6
5.5
Won't Fix
Undecided
Unassigned
5.6
Fix Released
Critical
Krunal Bauskar
Percona XtraDB Cluster moved to https://jira.percona.com/projects/PXC 5.6.27-25.13

Bug Description

Hello.
I found the problem that occurs while trying to use partitioning and "load data local infile".

Test configuration (2 virtual machines):
OS: CentOS Linux release 7.1.1503 (Core)
PXC Version:

mysql> show variables like 'version%';
+-------------------------+---------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+-------------------------+---------------------------------------------------------------------------------------------------+
| version | 5.6.22-72.0-56-log |
| version_comment | Percona XtraDB Cluster (GPL), Release rel72.0, Revision 978, WSREP version 25.8, wsrep_25.8.r4150 |
| version_compile_machine | x86_64 |
| version_compile_os | Linux |
+-------------------------+---------------------------------------------------------------------------------------------------+
4 rows in set (0,00 sec)

Installed packets (from percona yum repo):

[root@vm-percona-test-1 /]# rpm -qa | grep -i percona
Percona-XtraDB-Cluster-shared-56-5.6.22-25.8.978.el7.x86_64
Percona-XtraDB-Cluster-server-56-5.6.22-25.8.978.el7.x86_64
Percona-XtraDB-Cluster-garbd-3-3.9-1.3494.rhel7.x86_64
Percona-XtraDB-Cluster-test-56-5.6.22-25.8.978.el7.x86_64
percona-release-0.1-3.noarch
Percona-XtraDB-Cluster-galera-3-3.9-1.3494.rhel7.x86_64
Percona-XtraDB-Cluster-client-56-5.6.22-25.8.978.el7.x86_64
percona-xtrabackup-2.2.10-1.el7.x86_64
Percona-XtraDB-Cluster-56-debuginfo-5.6.22-25.8.978.el7.x86_64
Percona-XtraDB-Cluster-galera-3-debuginfo-3.9-1.3494.rhel7.x86_64
Percona-XtraDB-Cluster-full-56-5.6.22-25.8.978.el7.x86_64

Wsrep status after first launch:

mysql> show status like 'wsrep%';
+------------------------------+----------------------------------------------------+
| Variable_name | Value |
+------------------------------+----------------------------------------------------+
.......
| wsrep_local_state_comment | Synced |
.......
| wsrep_incoming_addresses | 194.50.85.96:3306,194.50.85.8:3306 |
.......
| wsrep_cluster_size | 2 |
.......

Test 1 (without partitioning):

mysql> create database `testdb`;
Query OK, 1 row affected (0,00 sec)

mysql> use testdb;
Database changed
mysql> CREATE TABLE `ip_test_table` (
    -> `ip_use` int(10) unsigned NOT NULL,
    -> `from_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    -> `to_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
    -> `bytes` bigint(20) unsigned NOT NULL,
    -> `dayofweek` tinyint(3) unsigned NOT NULL,
    -> PRIMARY KEY (`from_time`,`ip_use`,`dayofweek`),
    -> KEY `ip_use` (`ip_use`),
    -> KEY `from_time` (`from_time`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=cp1251;
Query OK, 0 rows affected (0,01 sec)

mysql> select count(*) from ip_test_table;
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0,00 sec)

mysql> show create table ip_test_table\G
*************************** 1. row ***************************
       Table: ip_test_table
Create Table: CREATE TABLE `ip_test_table` (
  `ip_use` int(10) unsigned NOT NULL,
  `from_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `to_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `bytes` bigint(20) unsigned NOT NULL,
  `dayofweek` tinyint(3) unsigned NOT NULL,
  PRIMARY KEY (`from_time`,`ip_use`,`dayofweek`),
  KEY `ip_use` (`ip_use`),
  KEY `from_time` (`from_time`)
) ENGINE=InnoDB DEFAULT CHARSET=cp1251
1 row in set (0,00 sec)

Trying to load data:
[root@vm-percona-test-1 /]# mysql testdb -e "LOAD DATA LOCAL INFILE '/tmp/dumps/iul.dump' INTO TABLE ip_test_table"

[root@vm-percona-test-1 /]# mysql testdb
.......
mysql> select count(*) from ip_test_table;
+----------+
| count(*) |
+----------+
| 225245 |
+----------+
1 row in set (0,06 sec)

All is ok, the data is loaded into the table.

Test 2 (with partitioning):

mysql> drop table ip_test_table;
Query OK, 0 rows affected (0,02 sec)

mysql> CREATE TABLE `ip_test_table` (
    -> `ip_use` int(10) unsigned NOT NULL,
    -> `from_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    -> `to_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
    -> `bytes` bigint(20) unsigned NOT NULL,
    -> `dayofweek` tinyint(3) unsigned NOT NULL,
    -> PRIMARY KEY (`from_time`,`ip_use`,`dayofweek`),
    -> KEY `ip_use` (`ip_use`),
    -> KEY `from_time` (`from_time`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=cp1251
    -> /*!50100 PARTITION BY LIST (dayofweek)
    -> (PARTITION p_dayofweek_1 VALUES IN (1) ENGINE = InnoDB,
    -> PARTITION p_dayofweek_2 VALUES IN (2) ENGINE = InnoDB,
    -> PARTITION p_dayofweek_3 VALUES IN (3) ENGINE = InnoDB,
    -> PARTITION p_dayofweek_4 VALUES IN (4) ENGINE = InnoDB,
    -> PARTITION p_dayofweek_5 VALUES IN (5) ENGINE = InnoDB,
    -> PARTITION p_dayofweek_6 VALUES IN (6) ENGINE = InnoDB,
    -> PARTITION p_dayofweek_7 VALUES IN (7) ENGINE = InnoDB) */;
Query OK, 0 rows affected (0,06 sec)

mysql> show create table ip_test_table\G
*************************** 1. row ***************************
       Table: ip_test_table
Create Table: CREATE TABLE `ip_test_table` (
  `ip_use` int(10) unsigned NOT NULL,
  `from_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `to_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `bytes` bigint(20) unsigned NOT NULL,
  `dayofweek` tinyint(3) unsigned NOT NULL,
  PRIMARY KEY (`from_time`,`ip_use`,`dayofweek`),
  KEY `ip_use` (`ip_use`),
  KEY `from_time` (`from_time`)
) ENGINE=InnoDB DEFAULT CHARSET=cp1251
/*!50100 PARTITION BY LIST (dayofweek)
(PARTITION p_dayofweek_1 VALUES IN (1) ENGINE = InnoDB,
 PARTITION p_dayofweek_2 VALUES IN (2) ENGINE = InnoDB,
 PARTITION p_dayofweek_3 VALUES IN (3) ENGINE = InnoDB,
 PARTITION p_dayofweek_4 VALUES IN (4) ENGINE = InnoDB,
 PARTITION p_dayofweek_5 VALUES IN (5) ENGINE = InnoDB,
 PARTITION p_dayofweek_6 VALUES IN (6) ENGINE = InnoDB,
 PARTITION p_dayofweek_7 VALUES IN (7) ENGINE = InnoDB) */
1 row in set (0,00 sec)

mysql> select count(*) from ip_test_table;
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0,00 sec)

Trying to load data:

[root@vm-percona-test-1 /]# mysql testdb -e "LOAD DATA LOCAL INFILE '/tmp/dumps/iul.dump' INTO TABLE ip_test_table"
ERROR 1213 (40001) at line 1: Deadlock found when trying to get lock; try restarting transaction
[root@vm-percona-test-1 /]# mysql testdb -e "LOAD DATA LOCAL INFILE '/tmp/dumps/iul.dump' INTO TABLE ip_test_table"
ERROR 1213 (40001) at line 1: Deadlock found when trying to get lock; try restarting transaction
[root@vm-percona-test-1 /]# mysql testdb -e "LOAD DATA LOCAL INFILE '/tmp/dumps/iul.dump_2' INTO TABLE ip_test_table"
ERROR 1213 (40001) at line 1: Deadlock found when trying to get lock; try restarting transaction

Oops! Deadlock found.

mysql> select count(*) from ip_test_table;
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0,00 sec)

Error log:

2015-05-14 14:13:31 20180 [Warning] WSREP: SQL statement was ineffective, THD: 8, buf: 220980
QUERY: LOAD DATA LOCAL INFILE '/tmp/dumps/iul.dump' INTO TABLE ip_test_table
 => Skipping replication
2015-05-14 14:13:31 20180 [Warning] WSREP: SQL statement was ineffective, THD: 8, buf: 220980
QUERY: LOAD DATA LOCAL INFILE '/tmp/dumps/iul.dump' INTO TABLE ip_test_table
 => Skipping replication
2015-05-14 14:13:32 20180 [Warning] WSREP: SQL statement was ineffective, THD: 8, buf: 220980
QUERY: LOAD DATA LOCAL INFILE '/tmp/dumps/iul.dump' INTO TABLE ip_test_table
 => Skipping replication
2015-05-14 14:13:40 20180 [Warning] WSREP: SQL statement was ineffective, THD: 9, buf: 220980
QUERY: LOAD DATA LOCAL INFILE '/tmp/dumps/iul.dump' INTO TABLE ip_test_table
 => Skipping replication
2015-05-14 14:13:40 20180 [Warning] WSREP: SQL statement was ineffective, THD: 9, buf: 220980
QUERY: LOAD DATA LOCAL INFILE '/tmp/dumps/iul.dump' INTO TABLE ip_test_table
 => Skipping replication
2015-05-14 14:13:41 20180 [Warning] WSREP: SQL statement was ineffective, THD: 9, buf: 220980
QUERY: LOAD DATA LOCAL INFILE '/tmp/dumps/iul.dump' INTO TABLE ip_test_table
 => Skipping replication
2015-05-14 14:13:40 20180 [Warning] WSREP: SQL statement was ineffective, THD: 10, buf: 220980
QUERY: LOAD DATA LOCAL INFILE '/tmp/dumps/iul.dump_2' INTO TABLE ip_test_table
 => Skipping replication
2015-05-14 14:13:40 20180 [Warning] WSREP: SQL statement was ineffective, THD: 10, buf: 220980
QUERY: LOAD DATA LOCAL INFILE '/tmp/dumps/iul.dump_2' INTO TABLE ip_test_table
 => Skipping replication
2015-05-14 14:13:41 20180 [Warning] WSREP: SQL statement was ineffective, THD: 10, buf: 220980
QUERY: LOAD DATA LOCAL INFILE '/tmp/dumps/iul.dump_2' INTO TABLE ip_test_table
 => Skipping replication

During the test of our mysql cluster, we got an error - "deadlock", but mysqld process did not crash.
We observed that problem in production for the first time (it works with large databases such as zabbix, cacti, websites, etc.).
When we tried to perform a "load data local infile" in production cluster, MySQL process crashed.
After that we got the following errors:

2015-05-13 15:02:34 24942 [Warning] WSREP: SQL statement was ineffective, THD: 7278815, buf: 220980
QUERY: LOAD DATA LOCAL INFILE '/tmp/caban/iul.dump' INTO TABLE ip_test_table
 => Skipping replication
2015-05-13 15:02:34 24942 [ERROR] WSREP: FSM: no such a transition COMMITTED -> ROLLED_BACK
12:02:34 UTC - mysqld got signal 6 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed,
something is definitely wrong and this may fail.
Please help us make Percona XtraDB Cluster better by reporting any
bugs at https://bugs.launchpad.net/percona-xtradb-cluster

key_buffer_size=134217728
read_buffer_size=2097152
max_used_connections=331
max_threads=2050
thread_count=151
connection_count=83
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 10658271 K bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x7fcbe9a1c000
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 7fcbcdeb9cf8 thread_stack 0x40000
/usr/sbin/mysqld(my_print_stacktrace+0x35)[0x8fa965]
/usr/sbin/mysqld(handle_fatal_signal+0x4b4)[0x665644]
/lib64/libpthread.so.0(+0xf710)[0x7fcea99ce710]
/lib64/libc.so.6(gsignal+0x35)[0x7fcea7e14625]
/lib64/libc.so.6(abort+0x175)[0x7fcea7e15e05]
/usr/lib64/libgalera_smm.so(_ZN6galera3FSMINS_9TrxHandle5StateENS1_10TransitionENS_10EmptyGuardENS_11EmptyActionEE8shift_toES2_+0x188)[0x7fce3095fd68]
/usr/lib64/libgalera_smm.so(_ZN6galera13ReplicatorSMM13post_rollbackEPNS_9TrxHandleE+0x2e)[0x7fce3097a9de]
/usr/lib64/libgalera_smm.so(galera_post_rollback+0x68)[0x7fce309941e8]
/usr/sbin/mysqld[0x7a906e]
/usr/sbin/mysqld(_Z15ha_rollback_lowP3THDb+0x97)[0x5a5057]
/usr/sbin/mysqld(_ZN13MYSQL_BIN_LOG8rollbackEP3THDb+0x10f)[0x8b26ff]
/usr/sbin/mysqld(_Z17ha_rollback_transP3THDb+0x74)[0x5a4e24]
/usr/sbin/mysqld(_Z19trans_rollback_stmtP3THD+0x33)[0x78e3a3]
/usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x554)[0x6ea3b4]
/usr/sbin/mysqld(_Z11mysql_parseP3THDPcjP12Parser_state+0x658)[0x6f0958]
/usr/sbin/mysqld[0x6f0acd]
/usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x19d5)[0x6f2de5]
/usr/sbin/mysqld(_Z10do_commandP3THD+0x22b)[0x6f42cb]
/usr/sbin/mysqld(_Z24do_handle_one_connectionP3THD+0x17f)[0x6bc52f]
/usr/sbin/mysqld(handle_one_connection+0x47)[0x6bc717]
/usr/sbin/mysqld(pfs_spawn_thread+0x12a)[0xaf611a]
/lib64/libpthread.so.0(+0x79d1)[0x7fcea99c69d1]
/lib64/libc.so.6(clone+0x6d)[0x7fcea7eca8fd]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (7fcc28289010): is an invalid pointer
Connection ID (thread ID): 7278815
Status: NOT_KILLED

You may download the Percona XtraDB Cluster operations manual by visiting
http://www.percona.com/software/percona-xtradb-cluster/. You may find information
in the manual which will help you identify the cause of the crash.
150513 15:02:35 mysqld_safe Number of processes running now: 0
150513 15:02:35 mysqld_safe WSREP: not restarting wsrep node automatically
150513 15:02:35 mysqld_safe mysqld from pid file /var/lib/mysql/blackbird.la.net.ua.pid ended

mysql> show variables like 'wsrep%splitting';
+---------------------------+-------+
| Variable_name | Value |
+---------------------------+-------+
| wsrep_load_data_splitting | ON |
+---------------------------+-------+
1 row in set (0,00 sec)

mysql> show variables like 'wsrep%ws%';
+-------------------+------------+
| Variable_name | Value |
+-------------------+------------+
| wsrep_max_ws_rows | 1048576 |
| wsrep_max_ws_size | 2147483648 |
+-------------------+------------+
2 rows in set (0,00 sec)

Aleksey Sokolov (absokolov2010)
tags: added: load-data
Aleksey Sokolov (absokolov2010)
tags: added: crash pxc
Revision history for this message
Alex Yurchenko (ayurchen) wrote :

what is binlog_format setting for the session that does LOAD DATA?

Revision history for this message
Aleksey Sokolov (absokolov2010) wrote :
Download full text (3.5 KiB)

Hi, Alex.

binlog_format = ROW for all.

mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
 +---------------+-------+
| binlog_format | ROW |
 +---------------+-------+
1 row in set (0,00 sec)

/etc/my.cnf:

[mysqld_safe]

open-files-limit = 320000
malloc-lib=/usr/lib64/libjemalloc.so.1

[mysqld]

ssl-ca = /etc/mysql/ssl/mysql/ca-cert.pem
ssl-cert = /etc/mysql/ssl/mysql/server-cert.pem
ssl-key = /etc/mysql/ssl/mysql/server-key.pem

skip-name-resolve
event_scheduler = On

server-id = 10

user = mysql
bind-address = 10.10.91.2
port = 3306
max_connections = 2048
max_allowed_packet = 1G

datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
tmpdir = /tmp/mysql

symbolic-links = 0

table_open_cache = 32768
table_definition_cache = 16784
table_open_cache_instances = 16

thread_cache_size = 256

default_storage_engine = InnoDB
explicit_defaults_for_timestamp = On
ft_min_word_len = 3

large-pages

sort_buffer_size = 3M
join_buffer_size = 3M
read_rnd_buffer_size = 3M
read_buffer_size = 2M

max_heap_table_size = 512M
tmp_table_size = 512M

# innodb

innodb_buffer_pool_size = 64G
innodb_buffer_pool_instances = 16

innodb_log_file_size = 8G
innodb_log_buffer_size = 16M
innodb_log_block_size = 4096
innodb_log_group_home_dir = /var/lib/mysql_logs/innodb
innodb_data_file_path = /ibdata1:64M:autoextend
innodb_data_home_dir = /var/lib/mysql_logs/innodb

innodb_open_files = 8192
innodb_file_per_table = 1
innodb_rollback_on_timeout = On
innodb_flush_log_at_trx_commit = 0
innodb_doublewrite = 1
innodb_flush_method = O_DIRECT
innodb_lock_wait_timeout = 300
innodb_flush_neighbors = 0

innodb_sort_buffer_size = 16M

innodb_io_capacity = 60000
innodb_io_capacity_max = 70000

innodb_max_dirty_pages_pct = 90

innodb_write_io_threads = 24
innodb_read_io_threads = 24
innodb_purge_threads = 4
innodb_random_read_ahead = On

innodb_support_xa = 0

innodb_autoinc_lock_mode = 2 # Galera
innodb_locks_unsafe_for_binlog = 1 # Galera

innodb_buffer_pool_load_at_startup = On
innodb_buffer_pool_dump_at_shutdown = On

innodb_file_format = Barracuda

# MyISAM

key_buffer_size = 128M

# binlog \ relaylog

log-bin = /var/lib/mysql_logs/binary/binlog
max_binlog_size = 1024M
binlog_format = ROW
binlog_cache_size = 5M
max_binlog_files = 50
expire_logs_days = 0
sync_binlog = 0

master_info_repository = 'table'
relay_log_info_repository = 'table'

relay_log = /var/lib/mysql_logs/relay/relaylog
slave_load_tmpdir = /tmp/mysql

master_verify_checksum = On
slave_sql_verify_checksum = ON

relay_log_recovery = On

# logs

log_error = "/var/log/mysql/error.log"
slow_query_log_file = "/var/log/mysql/slow.log"

# galera

wsrep_provider=/usr/lib64/libgalera_smm.so
wsrep_provider_options="gcache.size = 1G; gcache.page_size = 1G; gcache.name = /var/lib/mysql_logs/galera/galera.cache; gcs.fc_limit = 20000; socket.ssl_cert = /etc/mysql/ssl/galera/cert.pem; socket.ssl_key = /etc/mysql/ssl/galera/key.pem"

wsrep_cluster_address=gcomm://10.10.91.2,10.10.91.3,10.10.91.4

ws...

Read more...

Revision history for this message
Aleksey Sokolov (absokolov2010) wrote :

The session variable "binlog_format" does not change:

mysql> show session variables like 'binlog_format'\G
*************************** 1. row ***************************
Variable_name: binlog_format
        Value: ROW
1 row in set (0,00 sec)

[root@vm-percona-test-1 /]# cat /tmp/dumps/iul.dump | head -20
42979152 2015-05-13 17:30:00 2015-05-13 17:35:00 16158 3
42979156 2015-05-13 17:30:00 2015-05-13 17:35:00 4792 3
44675040 2015-05-13 17:30:00 2015-05-13 17:35:00 280 3
42979160 2015-05-13 17:30:00 2015-05-13 17:35:00 3704 3
44917248 2015-05-13 17:30:00 2015-05-13 17:35:00 734 3
44921412 2015-05-13 17:30:00 2015-05-13 17:35:00 32 3
44514352 2015-05-13 17:30:00 2015-05-13 17:35:00 7642838 3
44520544 2015-05-13 17:30:00 2015-05-13 17:35:00 433124 3
42979172 2015-05-13 17:30:00 2015-05-13 17:35:00 366 3
44307896 2015-05-13 17:30:00 2015-05-13 17:35:00 314 3
42979180 2015-05-13 17:30:00 2015-05-13 17:35:00 198 3
44559560 2015-05-13 17:30:00 2015-05-13 17:35:00 300 3
44637696 2015-05-13 17:30:00 2015-05-13 17:35:00 9375 3
44637700 2015-05-13 17:30:00 2015-05-13 17:35:00 9330 3
42979208 2015-05-13 17:30:00 2015-05-13 17:35:00 6624 3
42979212 2015-05-13 17:30:00 2015-05-13 17:35:00 182515 3
42979216 2015-05-13 17:30:00 2015-05-13 17:35:00 81404 3
42979220 2015-05-13 17:30:00 2015-05-13 17:35:00 20851 3
42979224 2015-05-13 17:30:00 2015-05-13 17:35:00 9506 3
42979228 2015-05-13 17:30:00 2015-05-13 17:35:00 934 3

Revision history for this message
Aleksey Sokolov (absokolov2010) wrote :

As a temporary solution, we use temporary tables:

1) CREATE TEMPORARY TABLE __ip_test_table_tmp (
              `ip_use` int(10) unsigned NOT NULL,
              `from_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
              `to_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
              `bytes` bigint(20) unsigned NOT NULL,
              `dayofweek` tinyint(3) unsigned NOT NULL,
              PRIMARY KEY (`from_time`,`ip_use`,`dayofweek`)
        ) ENGINE=Memory;

2) LOAD DATA LOCAL INFILE '/tmp/dumps/iul.dump' INTO TABLE __ip_test_table_tmp;
3) REPLACE INTO ip_test_table SELECT * FROM __ip_test_table_tmp;
4) DROP TABLE IF EXISTS __ip_test_table_tmp;

Revision history for this message
Aleksey Sokolov (absokolov2010) wrote :

How to reproduce:

1) Create database and two tables (first table without partition, second table with partition):

mysql> create database testdb;
Query OK, 1 row affected (0,00 sec)

mysql> use testdb;
Database changed
mysql> CREATE TABLE `test_table` (
    -> `ip_use` int(10) unsigned NOT NULL,
    -> `from_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    -> `to_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
    -> `bytes` bigint(20) unsigned NOT NULL,
    -> `dayofweek` tinyint(3) unsigned NOT NULL,
    -> PRIMARY KEY (`from_time`,`ip_use`,`dayofweek`),
    -> KEY `ip_use` (`ip_use`),
    -> KEY `from_time` (`from_time`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=cp1251;
Query OK, 0 rows affected (0,01 sec)

mysql>
mysql> CREATE TABLE `test_table_partition` ( -> `ip_use` int(10) unsigned NOT NULL,
    -> `from_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    -> `to_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
    -> `bytes` bigint(20) unsigned NOT NULL,
    -> `dayofweek` tinyint(3) unsigned NOT NULL,
    -> PRIMARY KEY (`from_time`,`ip_use`,`dayofweek`),
    -> KEY `ip_use` (`ip_use`),
    -> KEY `from_time` (`from_time`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=cp1251
    -> /*!50100 PARTITION BY LIST (dayofweek)
    -> (PARTITION p_dayofweek_1 VALUES IN (1) ENGINE = InnoDB,
    -> PARTITION p_dayofweek_2 VALUES IN (2) ENGINE = InnoDB,
    -> PARTITION p_dayofweek_3 VALUES IN (3) ENGINE = InnoDB,
    -> PARTITION p_dayofweek_4 VALUES IN (4) ENGINE = InnoDB,
    -> PARTITION p_dayofweek_5 VALUES IN (5) ENGINE = InnoDB,
    -> PARTITION p_dayofweek_6 VALUES IN (6) ENGINE = InnoDB,
    -> PARTITION p_dayofweek_7 VALUES IN (7) ENGINE = InnoDB) */;
Query OK, 0 rows affected (0,04 sec)

2) Trying to load data into a table "test_table" (without partition):

[root@vm-percona-test-1 /]# mysql testdb -e "LOAD DATA LOCAL INFILE '/tmp/dumps/iul.dump' INTO TABLE test_table"[root@vm-percona-test-1 /]#

... and check:

mysql> select count(*) from test_table;
+----------+
| count(*) |
 +----------+
| 225245 |
 +----------+
1 row in set (0,04 sec)

3) Trying to load data into a table "test_table_partition" (with partition):

[root@vm-percona-test-1 /]# mysql testdb -e "LOAD DATA LOCAL INFILE '/tmp/dumps/iul.dump' INTO TABLE test_table_partition"
ERROR 1213 (40001) at line 1: Deadlock found when trying to get lock; try restarting transaction
[root@vm-percona-test-1 /]#

... and check:

mysql> select count(*) from test_table_partition;
+----------+
| count(*) |
 +----------+
| 0 |
 +----------+
1 row in set (0,00 sec)

Revision history for this message
Nilnandan Joshi (nilnandan-joshi) wrote :

Tried to reproduce as per your suggetions but got that error while dropping normal table (without partition)
For producing dummy data, I've just added auto_increment to ip_use and add unique index (`ip_use`, `dayofweek`) for partition table.

CREATE TABLE `ip_test_table` (
  `ip_use` int(10) unsigned NOT NULL auto_increment,
  `from_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `to_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `bytes` bigint(20) unsigned NOT NULL,
  `dayofweek` tinyint(3) unsigned NOT NULL,
  PRIMARY KEY (`from_time`,`ip_use`,`dayofweek`),
  UNIQUE KEY `ip_use` (`ip_use`),
  KEY `from_time` (`from_time`)
) ENGINE=InnoDB DEFAULT CHARSET=cp1251

CREATE TABLE `ip_test_table_part` (
  `ip_use` int(10) unsigned NOT NULL auto_increment,
  `from_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `to_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `bytes` bigint(20) unsigned NOT NULL,
  `dayofweek` tinyint(3) unsigned NOT NULL,
  PRIMARY KEY (`from_time`,`ip_use`,`dayofweek`),
  UNIQUE KEY `ip_use` (`ip_use`, `dayofweek`),
  KEY `from_time` (`from_time`)
) ENGINE=InnoDB DEFAULT CHARSET=cp1251
/*!50100 PARTITION BY LIST (dayofweek)
(PARTITION p_dayofweek_1 VALUES IN (1) ENGINE = InnoDB,
 PARTITION p_dayofweek_2 VALUES IN (2) ENGINE = InnoDB,
 PARTITION p_dayofweek_3 VALUES IN (3) ENGINE = InnoDB,
 PARTITION p_dayofweek_4 VALUES IN (4) ENGINE = InnoDB,
 PARTITION p_dayofweek_5 VALUES IN (5) ENGINE = InnoDB,
 PARTITION p_dayofweek_6 VALUES IN (6) ENGINE = InnoDB,
 PARTITION p_dayofweek_7 VALUES IN (7) ENGINE = InnoDB) */;

mysql> LOAD DATA LOCAL INFILE '/var/lib/mysql/ip_test_table.txt' INTO TABLE ip_test_table;
Query OK, 524288 rows affected (5.89 sec)
Records: 524288 Deleted: 0 Skipped: 0 Warnings: 0

mysql> LOAD DATA LOCAL INFILE '/var/lib/mysql/ip_test_table.txt' INTO TABLE ip_test_table_part;
Query OK, 524288 rows affected (4.39 sec)
Records: 524288 Deleted: 0 Skipped: 0 Warnings: 0

mysql> DROP TABLE ip_test_table;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
mysql>

Valerii Kravchuk (valerii-kravchuk)
tags: added: i61579
Revision history for this message
Krunal Bauskar (krunal-bauskar) wrote :

Seems related to : https://bugs.launchpad.net/percona-xtradb-cluster/+bug/1292842

Revision history for this message
Valerii Kravchuk (valerii-kravchuk) wrote :

I set this to Confirmed formally. See https://jira.percona.com/browse/PXC-467

Revision history for this message
Krunal Bauskar (krunal-bauskar) wrote :

Issue: ha-partition handler disabled bin-log when invoking ha_innodb for respective partition.
This in-turn skips append-keys and so the failure with LDI.

Revision history for this message
Krunal Bauskar (krunal-bauskar) wrote :

commit 7caf48a7fcdd365aec979eabbf0699b336068668
Author: Krunal Bauskar <email address hidden>
Date: Thu Nov 5 13:51:31 2015 +0530

    - Disabling intermittently failing TCs. File JIRA issue to track them

commit 555f152b7a1793c76e2d784ee751dbbbf084f3f9
Author: Krunal Bauskar <email address hidden>
Date: Thu Nov 5 10:22:00 2015 +0530

    - PXC#467: LDI on partitioned table fails

      Issue:
      -----
      LDI on partitioned table fails

      Reason:
      ------
      Partition table are handled using ha_partition handler which in turn creates
      "n" different ha_innodb handlers one for each partition.
      When data is inserted in partitioned table to avoid repeat bin-logging
      partition handler disables bin-logging while invoking ha_innodb interface and
      re-enables it on completion. bin-logging is then done as part of ha_partition
      handler interface.

      ha_innodb skips key-append if bin-logging is disabled (and especially with LDI
      involved). [This is looks like a design choice than bug]
      Without append_key, statement can't be be forward to galera plugin for
      certification which actually causes LDI to fail.

      Solution:
      --------
      Relax the append-key condition to consider involvement of LDI with partitioned
      table.

Revision history for this message
Krunal Bauskar (krunal-bauskar) wrote :

There are 2 related issues here:

1. LDI fails because bin-logging is turned-off by partitioning handler to avoid duplicate bin-log by innodb handler and then by partitioning handler. This is fixed.

2. If multiple client exercising such LDI stmt fails it could possible lead to a race condition in Galera which eventually would lead to a crash.
Possible crash or something similar about state xfer error: https://bugs.launchpad.net/percona-xtradb-cluster/+bug/1292842

Second issue has also been fixed so while refreshing please refresh both PXC and Galera (libgalera_smm.so) and second issue is fixed in Galera.

Valerii Kravchuk (valerii-kravchuk)
tags: added: i64275
Revision history for this message
Shahriyar Rzayev (rzayev-sehriyar) wrote :

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

To post a comment you must log in.
This report contains Public information  
Everyone can see this information.
Subscribing...

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.