Transaction lost after recovery
Affects | Status | Importance | Assigned to | Milestone | ||
---|---|---|---|---|---|---|
Percona XtraDB Cluster moved to https://jira.percona.com/projects/PXC | Status tracked in 5.6 | |||||
5.6 |
Fix Committed
|
High
|
Unassigned | |||
5.7 |
Fix Committed
|
High
|
Unassigned |
Bug Description
If PXC is crashed (killed with -9, power failure) when running a lot of DML transactions (for example, single-threaded INSERT loop) after recovery last successfully commited transaction is rolled back even with
- innodb_
- innodb_
- sync_binlog = 1
Steps to reproduce:
Generate a lot of INSERTs:
#!/usr/bin/php
<?php
$conn = new PDO("mysql:
$conn->
$conn->
$conn->
$c = 0;
while (1) {
$q = "INSERT INTO demo.lostcheck3 (v) VALUES( 'percona${c}');";
try {
$f= $conn->query($q);
print "$q, Query OK, rc = ".$f->errorCode() .PHP_EOL;
} catch (Exception $e) {
printf("$q FAILED, %s", $e);
exit(1);
}
$c++;
}
After recovery, the last successfully committed transaction is lost and a corresponding binlog is trimmed:
2017-11-
2017-11-
2017-11-
2017-11-
2017-11-
2017-11-
2017-11-
2017-11-
2017-11-
2017-11-
2017-11-
2017-11-
2017-11-
2017-11-
# mysql -e "select * from demo.lostcheck3 where v ='percona1956' or v ='percona1957'";
+-------------+
| v |
+-------------+
| percona1956 |
+-------------+
However, if data is recovered with WSREP disabled (even if WSREP was enabled when committing) the transaction is recovered.
#head -n 3 /etc/mysql/my.cnf
[mysqld]
#wsrep_
#wsrep_
[...]
2017-11-
2017-11-
2017-11-
2017-11-
2017-11-
InnoDB: Progress in percent: 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
2017-11-
2017-11-
[...]
# mysql -e "select * from demo.lostcheck3 where v ='percona1956' or v ='percona1957'";
+-------------+
| v |
+-------------+
| percona1956 |
| percona1957 |
+-------------+
Tested against PXC 5.7.19-19.22-3 and 5.6.37-26.21-3
commit 65510b5c1f59a52 bcf18da5452a64f 0c9ba4282d 5.7-pxc- 895 685262d242f2e5f 9b5a252952
Merge: fb8e85e 5059434
Author: Krunal Bauskar <email address hidden>
Date: Thu Nov 30 19:01:07 2017 +0530
Merge pull request #574 from kbauskar/
PXC#895: Transaction lost after recovery
commit 5059434eb2eeaac
Author: Krunal Bauskar <email address hidden>
Date: Tue Nov 28 12:08:44 2017 +0530
PXC#895: Transaction lost after recovery
If binlog is enabled then SE will persist redo at following stages:
during prepare
during binlog write
during innodb-commit
3rd stage (fsync) can be skipped as transaction can be recovered
from binlog.
During 3rd stage, commit co-ordinates are also recorded in sysheader
under wsrep placeholder. These co-ordinates are then used to stop
binlog scan (in addition to get recovery position in case of node-crash).
Since fsync of 3rd stage is delayed (as transaction can be recovered
using binlog) it is possible that even though transaction is reported
as success to end-user said co-ordinates are not persisted to disk.
On restart, a prepare state transaction could be recovered and committed
but since wsrep co-ordinates are not persisted a successfully committed
transaction recovery is skipped causing data inconsistency from end-user
application perspective.
This behavior is now changed to let recovery proceed independent of
wsrep co-ordinates and wsrep co-ordinates are updated to reflect
the recovery committed transaction.
Fixed with 5.6 and 5.7