Partially executed transactions

MySQL & MariaDB Percona XtraDB Cluster 5.x
1

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

2

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?

3

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

4

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?

5

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]]

?>

6

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