Partially executed transactions

joepmeloen12joepmeloen12 ContributorMember Participant
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

Comments

  • vinicius.grippavinicius.grippa Percona Percona Advisor
    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?
  • joepmeloen12joepmeloen12 Contributor Member Participant
    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
  • vinicius.grippavinicius.grippa Percona Percona Advisor
    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?
  • joepmeloen12joepmeloen12 Contributor Member Participant
    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

    ?>
  • joepmeloen12joepmeloen12 Contributor Member Participant
    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
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.