Buy Percona ServicesBuy Now!

Partially executed transactions

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Partially executed transactions

    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?

    Comment


    • #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
      Last edited by joepmeloen12; 09-11-2019, 01:23 PM.

      Comment


      • #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?

        Comment


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

          ?>
          Last edited by joepmeloen12; 09-30-2019, 06:01 AM.

          Comment


          • #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_A UTO_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

            Comment

            Working...
            X