# Partially executed transactions **URL:** \ **Category:** Percona XtraDB Cluster 5.x\ **Created:** [September 9, 2019, 6:55am UTC](https://forums.percona.com/t/partially-executed-transactions/7191 "2019-09-09T06:55:43Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![joepmeloen12](https://avatars.discourse-cdn.com/v4/letter/j/bcef8e/32.png) [@joepmeloen12](https://forums.percona.com/u/joepmeloen12)\ **Post date:** [September 9, 2019, 6:55am UTC](https://forums.percona.com/t/partially-executed-transactions/7191/1 "2019-09-09T06:55:43Z")
Hi, First; excuse me if this has been delt with before. It’s still not possible to search a specific forum here (or I’m completely confused). We’ve got a three-node cluster. When we start inserting rows (in transactions; 1.000 transactions total each consisting of about 4 INSERTs) on all three nodes (as fast as we can) we get quite a few commit faults. Thats no problem, we can handle that. As long as we know it went wrong we can catch that in de db library. Every now and then though a commit is reported as successful (through php\_mysqli to PHP in this case) but if we do a SELECT on the inserted record it is NOT in the table. To make matters worse, other queries within the same transaction DO get inserted, resulting in orphaned rows in a child table: the parent was never actually committed. I (think I) know the pros and cons of transactions in Percona XtraDB Cluster. But I really don’t get this behaviour. Does anyone here have a I clue? Regards, Hidde Server version: 5.7.26-29-57-log Percona XtraDB Cluster (GPL), Release rel29, Revision 03540a3, WSREP version 31.37, wsrep\_31.37 Test output: 111 112 113 Commit failed. Try again. 114 115 Commit failed. Try again. Commit failed. Try again. 116 Commit failed. Try again. 117 Commit failed. Try again. Commit failed. Try again. Commit failed. Try again. Commit failed. Try again. Commit failed. Try again. Commit reported as successful but given record 94318 is NOT in database. Try again. Child table referring records to non-existent record 94318: 2 THIS IS WRONG Commit failed. Try again. Commit failed. Try again. 118 Commit failed. Try again. 119 ---
**Author:** ![vinicius.grippa](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/vinicius.grippa/32/1246_2.png) [@vinicius.grippa](https://forums.percona.com/u/vinicius.grippa)\ **Post date:** [September 9, 2019, 7:39am UTC](https://forums.percona.com/t/partially-executed-transactions/7191/2 "2019-09-09T07:39:04Z")
Hi, Are you using which value for pxc\_strict\_mode? What kind of tables? MyISAM? InnoDB? Are you using foreign keys? If possible provide the create table with the steps that you are performing. One question, if you try to read the data again in another session or node, the value shows up? ---
**Author:** ![joepmeloen12](https://avatars.discourse-cdn.com/v4/letter/j/bcef8e/32.png) [@joepmeloen12](https://forums.percona.com/u/joepmeloen12)\ **Post date:** [September 11, 2019, 8:39am UTC](https://forums.percona.com/t/partially-executed-transactions/7191/3 "2019-09-11T08:39:49Z")
Hi, Thanks for your reply! Here are answers: - pxc\_strict\_mode is set to ENFORCING - as it’s a cluster we’re using InnoDB - we don’t use foreign key constraints; the applications handles that - we try to read again directly after committing, in the same session, and the record which should have been inserted is not there, however other records in the same commit are, but not all. The tables are fairly straightforward, nothing special. A complicating factor might be we’re accessing through PHP. Then again, MySQL tells us the commit succeeded, but it actually did not. [edit] The mysql.log file shows no messages about this. > > One question, if you try to read the data again in another session or node, the value shows up? No, it’s simply not there. Not in any session, not on any node, it’s just never inserted. Regards, and thanks again, Hidde ---
**Author:** ![vinicius.grippa](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/vinicius.grippa/32/1246_2.png) [@vinicius.grippa](https://forums.percona.com/u/vinicius.grippa)\ **Post date:** [September 17, 2019, 11:02am UTC](https://forums.percona.com/t/partially-executed-transactions/7191/4 "2019-09-17T11:02:23Z")
It’s a really strange situation… it is not possible for me to assert with this info if it is a bug or not. If you have a reproducible case so I can test it will help. Have you tried to execute the same sets of commands direct on MySQL without PHP? ---
**Author:** ![joepmeloen12](https://avatars.discourse-cdn.com/v4/letter/j/bcef8e/32.png) [@joepmeloen12](https://forums.percona.com/u/joepmeloen12)\ **Post date:** [September 24, 2019, 4:33am UTC](https://forums.percona.com/t/partially-executed-transactions/7191/5 "2019-09-24T04:33:41Z")
Hi, Thanks for your reply. As we’re inserting at high speed on three nodes at the same time I wouldn’t know how to do this without some sort of scripting or at least I don’t think this can be reproduced manually on the MySQL command line. I’ve attached a very simple script (PHP in this case) which demonstrates this issue. The output on our case is: node1 # php percona\_trans\_test.php [nothing] node2 # php percona\_trans\_test.php Commit succeeded but record 13852 is NOT in table a! Commit succeeded but record 13888 is NOT in table a! node3 # php percona\_trans\_test.php Commit succeeded but record 12049 is NOT in table a! Commit succeeded but record 12625 is NOT in table a! Regards, Hidde [[edit: see next post]] ?\> ---
**Author:** ![joepmeloen12](https://avatars.discourse-cdn.com/v4/letter/j/bcef8e/32.png) [@joepmeloen12](https://forums.percona.com/u/joepmeloen12)\ **Post date:** [September 30, 2019, 5:47am UTC](https://forums.percona.com/t/partially-executed-transactions/7191/6 "2019-09-30T05:47:08Z")
Hi, We are one step further. I think we assumed a commit either failed, or succeeded, and that’s it. Further testing revealed that sometimes, halfway the transaction, we got an error 1213 (“WSREP detected deadlock/conflict and aborted the transaction. Try restarting the transaction”). Now, aborting a transaction halfway leads to a new transaction if you don’t stop immediately. So that explains why some records got inserted, and some didn’t because when you eventually reach your COMMIT, the _new_ transaction commits fine. But in my mind this defies the purpose of transactions; I would like to get a failed COMMIT in the end instead of an abort halfway the transaction. To support such an halfway-abort would mean changing code in hundreds if not thousands of places and it would basically mean we’re replicating transaction logic in the application. If WSREP would _not_ close transaction but just let it all pass and fail to COMMIT in the end, all would be fine. But maybe… we just messed up some setting which causes this behaviour, and maybe it can be mitigated. Below is our my.cnf. Is there a way to avoid this? Regards, Hidde =========================================================== [mysql] # CLIENT port = 3306 socket = /var/run/mysqld/mysqld.sock [mysqld] sql\_mode = only\_full\_group\_by,ERROR\_FOR\_DIVISION\_BY\_ZERO,NO\_AUTO\_CREATE\_USER,NO\_ENGINE\_SUBSTITUTION show\_compatibility\_56 = on ssl-ca = [redacted] ssl-cert = [redacted] ssl-key = [redacted] # GENERAL user = mysql default\_storage\_engine = innodb socket = /var/run/mysqld/mysqld.sock pid\_file = /var/run/mysqld/mysqld.pid tmpdir = [redacted] # MyISAM key\_buffer\_size = 32M # SAFETY max\_allowed\_packet = 128M max\_connect\_errors = 1000000 sysdate\_is\_now = 1 innodb = FORCE innodb\_strict\_mode = 1 log\_bin\_trust\_function\_creators = 1 # DATA STORAGE datadir = [redacted] # BINARY LOGGING log\_bin = [redacted] expire\_logs\_days = 1 sync\_binlog = 1 # CACHES AND LIMITS tmp\_table\_size = 32M max\_heap\_table\_size = 32M query\_cache\_type = 0 query\_cache\_size = 0 max\_connections = 200 thread\_cache\_size = 50 open\_files\_limit = 65535 table\_definition\_cache = 4096 table\_open\_cache = 10240 # INNODB innodb\_flush\_method = O\_DIRECT innodb\_log\_files\_in\_group = 2 innodb\_log\_file\_size = 512M innodb\_flush\_log\_at\_trx\_commit = 1 innodb\_file\_per\_table = 1 innodb\_buffer\_pool\_size = 50G innodb\_stats\_sample\_pages = 100 innodb\_stats\_persistent\_sample\_pages=100 innodb\_stats\_transient\_sample\_pages=100 # LOGGING log\_error = [redacted]mysql-error.log log\_queries\_not\_using\_indexes = 0 slow\_query\_log = 0 slow\_query\_log\_file = [redacted]mysql-slow.log server\_id=1 wsrep\_cluster\_address=“gcomm://node2,node3” wsrep\_provider=/usr/lib/libgalera\_smm.so wsrep\_provider\_options = “gmcast.listen\_addr=tcp://node1; gmcast.segment=1; evs.keepalive\_period=PT1S; evs.inactive\_check\_period=PT0.5S; evs.suspect\_timeout=PT5S; evs.inactive\_timeout=PT15S; evs.install\_timeout=PT15S; socket.ssl\_cert=/[redacted]/percona-cert.pem; socket.ssl\_key=/[redacted]/percona-key.pem; socket.ssl\_cipher=AES128-SHA; socket.ssl\_compression=no; evs.send\_window=512; evs.user\_send\_window=512; gmcast.time\_wait=PT1M; gcache.size=256M” wsrep\_slave\_threads=2 wsrep\_cluster\_name=[redacted] wsrep\_sst\_method=xtrabackup-v2 wsrep\_sst\_auth=[redacted]:[redacted] wsrep\_node\_name=node1 wsrep\_node\_incoming\_address=“node1:4567” wsrep\_sst\_receive\_address=“node1:4444” wsrep\_node\_address=“node1” innodb\_locks\_unsafe\_for\_binlog=1 innodb\_autoinc\_lock\_mode=2 binlog\_format=ROW wsrep\_notify\_cmd=[redacted] wsrep\_retry\_autocommit=20 wsrep\_auto\_increment\_control=OFF auto\_increment\_increment=3 auto\_increment\_offset=1 [mysqldump] quick quote-names max\_allowed\_packet = 1024M [sst] inno-backup-opts=“–skip-ssl” tca=/[redacted]/clusternodessl.crt tcert=/[redacted]/clusternodessl.pem encrypt=2