Uploaded image for project: 'Percona XtraDB Cluster'
  1. Percona XtraDB Cluster
  2. PXC-2039

Node consistency compromized for simple INSERT INTO ... ON DUPLICATE KEY UPDATE workload

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Done
    • Priority: High
    • Resolution: Fixed
    • Affects Version/s: 5.7.x
    • Fix Version/s: 5.7.21-29.26
    • Component/s: None
    • Labels:

      Description

      In a PXC cluster with data absolutely consistent between the nodes (synced with full SST prior to test), after a short workload, executed in parallel against two nodes, cluster breaks with consistency compromized - either SQL error 1062 or 1032.

      Configuration tested is pretty simple and in fact I cannot find anything in settings that could work as a workaround.

      Verified on:

      pxc4 > select @@version,@@version_comment\G
      *************************** 1. row ***************************
       @@version: 5.7.19-17-57
      @@version_comment: Percona XtraDB Cluster (GPL), Release rel17, Revision c10027a, WSREP version 29.22, wsrep_29.22
      1 row in set (0.00 sec)

      To trigger the issue, the attached script has to be launched against two PXC nodes in the same time, one thread per each is enough, before, create the table for tests, like this:

       

      CREATE DATABASE upsert;
      use upsert;
      CREATE TABLE `work` (
       `col1` varchar(1) NOT NULL,
       `col2` varchar(4) NOT NULL,
       `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
       PRIMARY KEY (`col1`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
      

       

      $ php t1.php 172.17.0.1 & php t1.php 172.17.0.2 &

      After some seconds, you should see at least one node aborting due to consistency compromised, but sometimes I saw both of them. So, we will see typical error like:

       

      2018-01-24T13:06:49.531421Z 1 [ERROR] Slave SQL: Could not execute Update_rows event on table upsert.work; Can't find record in 'work', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log FIRST, end_log_pos 178, 
      Error_code: 1032
      2018-01-24T13:06:49.531445Z 1 [Warning] WSREP: RBR event 3 Update_rows apply warning: 120, 147839
      2018-01-24T13:06:49.531790Z 1 [Warning] WSREP: Failed to apply app buffer: seqno: 147839, status: 1
       at galera/src/trx_handle.cpp:apply():351
      
      ...
      
      2018-01-24T13:06:49.534541Z 1 [ERROR] WSREP: Failed to apply trx: source: 31fa8c2a-0107-11e8-8faf-df2c4f84c5b6 version: 3 local: 0 state: APPLYING flags: 1 conn_id: 5 trx_id: 46620 seqnos (l: 89594, g: 147839, s: 147837, d: 147826, ts: 274794538373619)
      2018-01-24T13:06:49.534552Z 1 [ERROR] WSREP: Failed to apply trx 147839 4 times
      2018-01-24T13:06:49.534559Z 1 [ERROR] WSREP: Node consistency compromized, aborting...
      

       

      Example configuration used:

      [MYSQLD]
      user = mysql
      default_storage_engine = InnoDB
      basedir = /usr
      datadir = /var/lib/mysql
      socket = /var/run/mysqld/mysqld.sock
      pid_file = mysqld.pid  
      port = 3306
      performance_schema=0   
      log_error = /var/lib/mysql/pxc1_error.log
      
      server-id=100
      binlog_format = ROW
      
      open_files_limit = 65535
      max_connections = 200  
      thread_cache_size = 100
      tmp_table_size = 64M   
      max_heap_table_size = 64M
      query_cache_type = 0   
      query_cache_size = 0   
      
      innodb_buffer_pool_size = 128M
      innodb_buffer_pool_instances = 4
      innodb_flush_log_at_trx_commit = 0
      innodb_flush_method = O_DIRECT
      innodb_file_per_table = 1
      innodb_log_files_in_group = 2
      innodb_log_file_size = 128M
      innodb_thread_concurrency = 0
      innodb_autoinc_lock_mode = 2
      
      pxc_strict_mode                     = ENFORCING
      wsrep_provider = /usr/lib64/galera3/libgalera_smm.so
      wsrep_cluster_name="galera_cluster"
      wsrep_cluster_address = gcomm://172.17.0.4,172.17.0.5,172.17.0.2,172.17.0.3
      wsrep_node_name = pxc1 
      wsrep_slave_threads = 1
      wsrep_auto_increment_control = 1
      wsrep_drupal_282555_workaround = 0
      wsrep_sst_method = xtrabackup-v2
      wsrep_sst_auth = root:cmon

        Attachments

        1. test.php
          0.7 kB
        2. test2.php
          1 kB

          Issue Links

            Activity

              People

              Assignee:
              krunal.bauskar Krunal Bauskar (Inactive)
              Reporter:
              przemyslaw.malkowski@percona.com Przemyslaw Malkowski
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:

                  Smart Checklist