Based on PXC 5.5.31, if I have a query on different transaction that inserts a row, and a query on another transaction within the same thread id that depends on the first row i.e. FK constraint, when multiple slave threads are configured, the second transaction sometimes gets executed first causing FK violation. test case below:
CREATE TABLE `a` (
`id` bigint(20) DEFAULT NULL,
UNIQUE KEY `id_pk` (`id`)
) ENGINE=InnoDB;
CREATE TABLE `b` (
`id` bigint(20) DEFAULT NULL,
`t` int(11) NOT NULL,
`aid` bigint(20) DEFAULT NULL,
UNIQUE KEY `act_email_uk` (`id`,`t`),
KEY `aid_fk` (`aid`),
CONSTRAINT `aid_fk` FOREIGN KEY (`aid`) REFERENCES `a` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB;
Test script:
<snip>
#!/bin/bash
MY="mysql percona"
for i in {1000..2000}; do
t=$(($i-1))
$MY -e "BEGIN; INSERT INTO a SET id = ${i}; COMMIT; BEGIN; INSERT INTO b SET id=${i}, t=${t}, aid=${i}; COMMIT;"
done
</snip>
If I set wsrep_slave_threads = 1 or wrap both statements on the same transaction, everything is fine. below is the error:
130827 22:19:10 [ERROR] Slave SQL: Could not execute Write_rows event on table percona.b; Cannot add or update a child row: a foreign key constraint fails (`percona`.`b`, CONSTRAINT `aid_fk` FOREIGN KEY (`aid`) REFERENCES `a` (`id`) ON DELETE CASCADE), Error_code: 1452; handler error HA_ERR_NO_REFERENCED_ROW; the event's master log FIRST, end_log_pos 95, Error_code: 1452
130827 22:19:10 [Warning] WSREP: RBR event 2 Write_rows apply warning: 151, 44481
130827 22:19:10 [ERROR] WSREP: Failed to apply trx: source: 7550f2c3-0f7d-11e3-b44f-7b192cf0d736 version: 2 local: 0 state: APPLYING flags: 1 conn_id: 1180 trx_id: 497138 seqnos (l: 280, g: 44481, s: 44480, d: 44211, ts: 1377656358652661293)
130827 22:19:10 [ERROR] WSREP: Failed to apply app buffer: seqno: 44481, status: WSREP_FATAL
at galera/src/replicator_smm.cpp:apply_wscoll():52
at galera/src/replicator_smm.cpp:apply_trx_ws():118
130827 22:19:10 [ERROR] WSREP: Node consistency compromized, aborting...
Below is my.cnf used, 3nodes in all
[mysqld]
log-error = /var/log/mysql/error.log
socket = /var/run/mysqld/mysqld.sock
port = 3306
pid-file = /var/lib/mysql/mysqld.pid
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /var/tmp
default_storage_engine = InnoDB
skip-external-locking
max_connections = 300
max_allowed_packet = 1M
table_open_cache = 512
sort_buffer_size = 1M
net_buffer_length = 8K
read_buffer_size = 1M
read_rnd_buffer_size = 1M
join_buffer_size = 1M
transaction_isolation = REPEATABLE-READ
character-set-server = utf8
collation-server = utf8_unicode_ci
thread_cache_size = 8
binlog_format = ROW
sync_binlog = 0
binlog_cache_size = 1M
server_id = 100
#log_slave_updates = 1
log-bin = mysql-bin
innodb_file_format = barracuda
innodb_strict_mode = 1
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /var/lib/mysql
innodb_buffer_pool_size = 512M
innodb_additional_mem_pool_size = 2M
innodb_log_file_size = 512M
innodb_log_buffer_size = 2M
innodb_flush_log_at_trx_commit = 2
innodb_lock_wait_timeout = 50
innodb_fast_shutdown = 1
#innodb_flush_method = O_DIRECT
innodb_autoinc_lock_mode = 2
innodb_locks_unsafe_for_binlog = 1
innodb_doublewrite = 1
wsrep_node_name = uxdbc01
wsrep_cluster_name = uxdbc
wsrep_debug = 1
wsrep_node_address = 192.168.56.53
wsrep_node_incoming_address = 192.168.56.53
wsrep_provider = /usr/lib/libgalera_smm.so
wsrep_provider_options = "ist.recv_addr=192.168.56.53:4568;gmcast.listen_addr=tcp://192.168.56.53:4567;"
wsrep_slave_threads = 3
wsrep_sst_method = xtrabackup
wsrep_sst_receive_address = 192.168.56.53
wsrep_cluster_address = gcomm://
I can reproduce this in my EC2 PXC test environment on PXC 5.5.31
Percona-
Percona-
Percona-
Percona-