Non-Deterministic Execution Of Transactions from Same Threads
Affects | Status | Importance | Assigned to | Milestone | ||
---|---|---|---|---|---|---|
MySQL patches by Codership |
Fix Released
|
High
|
Vladislav Klyachin | |||
Percona XtraDB Cluster moved to https://jira.percona.com/projects/PXC | Status tracked in 5.6 | |||||
5.5 |
Fix Released
|
Undecided
|
Unassigned | |||
5.6 |
Fix Released
|
Undecided
|
Unassigned |
Bug Description
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_
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-
130827 22:19:10 [ERROR] WSREP: Failed to apply app buffer: seqno: 44481, status: WSREP_FATAL
at galera/
at galera/
130827 22:19:10 [ERROR] WSREP: Node consistency compromized, aborting...
Below is my.cnf used, 3nodes in all
[mysqld]
log-error = /var/log/
socket = /var/run/
port = 3306
pid-file = /var/lib/
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /var/tmp
default_
skip-external-
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_
join_buffer_size = 1M
transaction_
character-
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_
innodb_
innodb_
innodb_
innodb_
innodb_
innodb_
innodb_
innodb_
#innodb_
innodb_
innodb_
innodb_doublewrite = 1
wsrep_node_name = uxdbc01
wsrep_cluster_name = uxdbc
wsrep_debug = 1
wsrep_node_address = 192.168.56.53
wsrep_node_
wsrep_provider = /usr/lib/
wsrep_provider_
wsrep_slave_threads = 3
wsrep_sst_method = xtrabackup
wsrep_sst_
wsrep_cluster_
Changed in codership-mysql: | |
assignee: | nobody → Seppo Jaakola (seppo-jaakola) |
Changed in codership-mysql: | |
assignee: | Seppo Jaakola (seppo-jaakola) → Vladislav Klyachin (klyachin) |
importance: | Undecided → High |
milestone: | none → 5.5.32-23.7.6 |
status: | New → Confirmed |
tags: | added: i35006 |
Changed in codership-mysql: | |
importance: | High → Critical |
Changed in codership-mysql: | |
status: | Confirmed → Fix Committed |
Changed in codership-mysql: | |
status: | Fix Committed → Fix Released |
Changed in percona-xtradb-cluster: | |
status: | Fix Committed → Fix Released |
I can reproduce this in my EC2 PXC test environment on PXC 5.5.31
Percona- XtraDB- Cluster- shared- 5.5.31- 23.7.5. 438.rhel6. x86_64 XtraDB- Cluster- client- 5.5.31- 23.7.5. 438.rhel6. x86_64 XtraDB- Cluster- galera- 2.6-1.152. rhel6.x86_ 64 XtraDB- Cluster- server- 5.5.31- 23.7.5. 438.rhel6. x86_64
Percona-
Percona-
Percona-