Emergency

Can't import dump file to Percona Cluster with 3 node (actually 1 node is active)

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

  • Can't import dump file to Percona Cluster with 3 node (actually 1 node is active)

    Hi friends,

    I get just 40M database dump file from My SQL 5.7 and want to import to Percona Cluster. I can't achieve this and can't find any reason from net.

    The error is

    ERROR 1213 (40001) at line 69: WSREP detected deadlock/conflict and aborted the transaction. Try restarting the transaction

    My config file is as following:

    *****************
    [mysqld]

    datadir=/var/lib/mysql
    user=mysql
    socket=/var/lib/mysql/mysql.sock
    port=3306
    sql_mode="STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_Z ERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE _USER,NO_ENGINE_SUBSTITUTION"

    skip-external-locking
    key_buffer_size = 128M
    skip-name-resolve
    tmp_table_size=50M
    max_allowed_packet = 1M
    table_open_cache = 512
    sort_buffer_size = 2M
    read_buffer_size = 2M
    read_rnd_buffer_size = 8M
    myisam_sort_buffer_size = 64M
    thread_cache_size = 8
    query_cache_size = 32M
    slow_query_log=1
    event_scheduler=on

    # Replication Master Server (default)
    # binary logging is required for replication
    log-bin=mysql-bin

    # required unique id between 1 and 2^32 - 1
    # defaults to 1 if master-host is not set but will not function as a master if omitted
    server-id= 1

    # Uncomment the following if you are using InnoDB tables
    innodb_buffer_pool_size = 384M

    log-error=/var/log/mysqld.log
    pid-file=/var/run/mysqld/mysqld.pid

    #pxc-encrypt-cluster-traffic=ON

    wsrep_cluster_name=pxc-cluster
    wsrep_cluster_address=gcomm://XX.XX.XX.XX,YY.YY.YY.YY,ZZ.ZZ.ZZ.ZZ
    wsrep_node_name=call1
    wsrep_node_address=XX.XX.XX.XX

    wsrep_sst_method=xtrabackup-v2
    wsrep_sst_auth=sstuser:kurulum

    pxc_strict_mode=DISABLED #PERMISSIVE #DISABLED #ENFORCING
    wsrep_log_conflicts=ON
    wsrep_debug=ON

    #Binary logging format - mixed recommended
    wsrep_forced_binlog_format=MIXED
    default_storage_engine=InnoDB
    innodb_autoinc_lock_mode=2
    [mysqldump]
    quick
    max_allowed_packet = 16M
    [mysql]
    no-auto-rehash
    [myisamchk]
    key_buffer_size = 256M
    sort_buffer_size = 256M
    read_buffer = 2M
    write_buffer = 2M

    [mysqlhotcopy]
    interactive-timeout

    ***********************

    Output of log is
    ..........................
    2017-12-02T07:10:24.241542Z 461 [Note] WSREP: Cleaning up wsrep-transaction for local query: LOCK TABLES `agents_realtime_statuses` WRITE
    2017-12-02T07:10:24.241634Z 461 [Note] WSREP: set_query_id(), assigned new next trx id: 1418
    2017-12-02T07:10:24.241672Z 461 [Note] WSREP: Cleaning up wsrep-transaction for local query: /*!40000 ALTER TABLE `agents_realtime_statuses` DISABLE KEYS */
    2017-12-02T07:10:24.241738Z 461 [Note] WSREP: Thread holds MDL locks at TOI begin: /*!40000 ALTER TABLE `agents_realtime_statuses` DISABLE KEYS */ 461
    2017-12-02T07:10:24.241759Z 461 [Note] WSREP: Executing Query (/*!40000 ALTER TABLE `agents_realtime_statuses` DISABLE KEYS */) with write-set (-1) and exec_mode: LOCAL_STATE in TO Isolation mode
    2017-12-02T07:10:24.241897Z 461 [Note] WSREP: Query (/*!40000 ALTER TABLE `agents_realtime_statuses` DISABLE KEYS */) with write-set (3156) and exec_mode: TOTAL_ORDER replicated in TO Isolation mode
    2017-12-02T07:10:24.241914Z 461 [Note] WSREP: wsrep: initiating TOI for write set (3156)
    2017-12-02T07:10:24.241979Z 461 [Note] WSREP: wsrep: completed TOI write set (3156)
    2017-12-02T07:10:24.241999Z 461 [Note] WSREP: Setting WSREPXid (InnoDB): f0062cd7-b62d-11e7-a967-c75103ad8859:3156
    2017-12-02T07:10:24.242852Z 461 [Note] WSREP: Completed query (/*!40000 ALTER TABLE `agents_realtime_statuses` DISABLE KEYS */) replication with write-set (3156) and exec_mode: TOTAL_ORDER in TO Isolation mode
    2017-12-02T07:10:24.243000Z 461 [Note] WSREP: set_query_id(), assigned new next trx id: 1419
    2017-12-02T07:10:24.245458Z 461 [Note] WSREP: wsrep: replicating commit (-1)
    2017-12-02T07:10:24.245508Z 461 [Warning] WSREP: SQL statement (INSERT INTO `agents_realtime_statuses` VALUES ('logoff',10,'2017-12-01 19:40:09',NULL,NULL,'cybernet-ministry-yardim',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0, 144,15,2581,9,6,0,0,NULL,'2017-12-01 18:40:02',0,0,0,0,0,NULL),('logoff',8,'2017-12-01 16:56:22',NULL,NULL,'cybernet-ministry-yardim',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,8, 31,6,709,5,1,0,0,NULL,'2017-12-01 16:50:02',0,0,0,370,2,NULL),('logoff',7,'2017-12-01 19:46:04',NULL,NULL,'cybernet-ministry-yardim',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,3, 373,46,1202,19,25,0,0,NULL,'2017-12-01 18:40:00',0,0,0,59,46,NULL),('logoff',5,'2017-12-01 14:56:20',NULL,NULL,'cybernet-ministry-yardim',NULL,NULL,NULL,NULL,NULL,'1512125646.28799 ',NULL,NULL,0,20,3,0,0,2,0,0,NULL,'2017-12-01 14:42:34',0,0,0,0,20,NULL),('logoff',6,'2017-12-01 19:03:36',NULL,NULL,'cybernet-ministry-yardim',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0, 50,5,0,0,5,0,0,NULL,'2017-12-01 19:00:18',0,0,0,0,0,NULL),('logoff',11,'2017-12-01 21:00:48',NULL,NULL,'cybernet-ministry-yardim',
    2017-12-02T07:10:24.245533Z 461 [Note] WSREP: commit action failed for reason: WSREP_TRX_FAIL THD: 461 Query: INSERT INTO `agents_realtime_statuses` VALUES ('logoff',10,'2017-12-01 19:40:09',NULL,NULL,'cybernet-ministry-yardim',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0, 144,15,2581,9,6,0,0,NULL,'2017-12-01 18:40:02',0,0,0,0,0,NULL),('logoff',8,'2017-12-01 16:56:22',NULL,NULL,'cybernet-ministry-yardim',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,8, 31,6,709,5,1,0,0,NULL,'2017-12-01 16:50:02',0,0,0,370,2,NULL),('logoff',7,'2017-12-01 19:46:04',NULL,NULL,'cybernet-ministry-yardim',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,3, 373,46,1202,19,25,0,0,NULL,'2017-12-01 18:40:00',0,0,0,59,46,NULL),('logoff',5,'2017-12-01 14:56:20',NULL,NULL,'cybernet-ministry-yardim',NULL,NULL,NULL,NULL,NULL,'1512125646.28799 ',NULL,NULL,0,20,3,0,0,2,0,0,NULL,'2017-12-01 14:42:34',0,0,0,0,20,NULL),('logoff',6,'2017-12-01 19:03:36',NULL,NULL,'cybernet-ministry-yardim',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0, 50,5,0,0,5,0,0,NULL,'2017-12-01 19:00:18',0,0,0,0,0,NULL),('logoff',11,'2017-12-0
    2017-12-02T07:10:24.245543Z 461 [Note] WSREP: conflict state: NO_CONFLICT
    2017-12-02T07:10:24.245551Z 461 [Note] WSREP: --------- CONFLICT DETECTED --------
    2017-12-02T07:10:24.245558Z 461 [Note] WSREP: cluster conflict due to certification failure for threads:

    2017-12-02T07:10:24.245584Z 461 [Note] WSREP: Victim thread:
    THD: 461, mode: local, state: executing, conflict: cert failure, seqno: -1
    SQL: INSERT INTO `agents_realtime_statuses` VALUES ('logoff',10,'2017-12-01 19:40:09',NULL,NULL,'cybernet-ministry-yardim',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0, 144,15,2581,9,6,0,0,NULL,'2017-12-01 18:40:02',0,0,0,0,0,NULL),('logoff',8,'2017-12-01 16:56:22',NULL,NULL,'cybernet-ministry-yardim',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,8, 31,6,709,5,1,0,0,NULL,'2017-12-01 16:50:02',0,0,0,370,2,NULL),('logoff',7,'2017-12-01 19:46:04',NULL,NULL,'cybernet-ministry-yardim',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,3, 373,46,1202,19,25,0,0,NULL,'2017-12-01 18:40:00',0,0,0,59,46,NULL),('logoff',5,'2017-12-01 14:56:20',NULL,NULL,'cybernet-ministry-yardim',NULL,NULL,NULL,NULL,NULL,'1512125646.28799 ',NULL,NULL,0,20,3,0,0,2,0,0,NULL,'2017-12-01 14:42:34',0,0,0,0,20,NULL),('logoff',6,'2017-12-01 19:03:36',NULL,NULL,'cybernet-ministry-yardim',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0, 50,5,0,0,5,0,0,NULL,'2017-12-01 19:00:18',0
    2017-12-02T07:10:24.245793Z 461 [Note] WSREP: Cleaning up wsrep-transaction for local query: INSERT INTO `agents_realtime_statuses` VALUES ('logoff',10,'2017-12-01 19:40:09',NULL,NULL,'cybernet-ministry-yardim',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0, 144,15,2581,9,6,0,0,NULL,'2017-12-01 18:40:02',0,0,0,0,0,NULL),('logoff',8,'2017-12-01 01 14:56:20',NULL,NULL,'cybernet-ministry-yardim',NULL,NULL,NULL,NULL,NULL,'1512125646.28799 ',NULL,NULL,0,20,3,0,0,2,0,0,NULL,'2017-12-01 14:42:34',0,0,0,0,20,NULL),('logoff',6,'2017-12-01 19:03:36',NULL,NULL,'cybernet-ministry-yardim',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0, 50,5,0,0,5,0,0,NULL,'2017-12-01 19:00:18',0,0,0,0,0,NULL),('logoff',11,'2017-12-01 21:00:48',NULL,
    2017-12-02T07:10:24.245841Z 461 [Note] WSREP: Retrying auto-commit query (on abort): INSERT INTO `agents_realtime_statuses` VALUES ('logoff',10,'2017-12-01 19:40:09',NULL,NULL,'cybernet-ministry-yardim',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0, 144,15,2581,9,6,0,0,NULL,'2017-12-01 18:40:02',0,0,0,0,0,NULL),('logoff',8,'2017-12-01 01 14:56:20',NULL,NULL,'cybernet-ministry-yardim',NULL,NULL,NULL,NULL,NULL,'1512125646.28799 ',NULL,NULL,0,20,3,0,0,2,0,0,NULL,'2017-12-01 14:42:34',0,0,0,0,20,NULL),('logoff',6,'2017-12-01 19:03:36',NULL,NULL,'cybernet-ministry-yardim',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0, 50,5,0,0,5,0,0,NULL,'2017-12-01 19:00:18',0,0,0,0,0,NULL),('logoff',11,'2017-12-01 21:00:48',NULL,NULL,'cy
    2017-12-02T07:10:24.245863Z 461 [Note] WSREP: Assigned new trx id to retry auto-commit query: 1419
    2017-12-02T07:10:24.246301Z 461 [Note] WSREP: wsrep: replicating commit (-1)
    2017-12-02T07:10:24.246336Z 461 [Warning] WSREP: SQL statement (INSERT INTO `agents_realtime_statuses` VALUES ('logoff',10,'2017-12-01 19:40:09',NULL,NULL,'cybernet-ministry-yardim',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0, 144,15,2581,9,6,0,0,NULL,'2017-12-01 18:40:02',0,0,0,0,0,NULL),('logoff',8,'2017-12-01 16:56:22',NULL,NULL,'cybernet-ministry-ministry-yardim',NULL,NULL,NULL,NULL,NULL,'1512125646.28799 ',NULL,NULL,0,20,3,0,0,2,0,0,NULL,'2017-12-01 14:42:34',0,0,0,0,20,NULL),('logoff',6,'2017-12-01 19:03:36',NULL,NULL,'cybernet-ministry-yardim',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0, 50,5,0,0,5,0,0,NULL,'2017-12-01 19:00:18',0,0,0,0,0,NULL),('logoff',11,'2017-12-01 21:00:48',NULL,NULL,'cybernet-ministry-yardim',
    2017-12-02T07:10:24.246353Z 461 [Note] WSREP: commit action failed for reason: WSREP_TRX_FAIL THD: 461 Query: INSERT INTO `agents_realtime_statuses` VALUES ('logoff',10,'2017-

    ....................................

    I can't find the reason.


    PLEASE HELP ME. IT IS URGENT!!!!


    Thanks!!!



  • #2
    1. I see you are importing plain mysqldump fileinto PXC that involes cluster-unsafe statement like LOCK TABLES, etc....
    Cleaning up wsrep-transaction for local query: LOCK TABLES `agents_realtime_statuses` WRITE

    2. Failure suggests certification failure. Are you trying to load the files through multiple nodes at same time ?

    3. I would suggest you can enable wsrep-debug=1 to get more information about failure. (Note: wsrep-debug is only for investigation and shouldn't be used for production load).

    4. If you can reduce the test-case and share it here we can help it investigate further and directly more about it.

    Comment


    • #3
      Hi Krunal, thanks for reply.

      I didn't execute option 2 in my import. As I noticed in question that I have 3 node cluster with 2 of them shutdown. And just test from 1st node.

      For now I forget all about import and just test simple case: INSERTION.
      I login to Node1 with root user and just create simple table in shems database to test if insert can be done successfully.

      mysql> create table test (id integer);
      Query OK, 0 rows affected (0.01 sec)

      mysql> insert into test values (4);
      ERROR 1213 (40001): WSREP detected deadlock/conflict and aborted the transaction. Try restarting the transaction


      Again the log output is:

      2017-12-04T11:18:04.550046Z 48 [Note] WSREP: Cleaning up wsrep-transaction for local query: insert into test values (4)
      2017-12-04T11:18:04.550065Z 48 [Note] WSREP: Retrying auto-commit query (on abort): insert into test values (4)
      2017-12-04T11:18:04.550095Z 48 [Note] WSREP: Assigned new trx id to retry auto-commit query: 108
      2017-12-04T11:18:04.550187Z 48 [Note] WSREP: wsrep: replicating commit (-1)
      2017-12-04T11:18:04.550205Z 48 [Warning] WSREP: SQL statement (insert into test values (4)) was not replicated (thd: 48)
      2017-12-04T11:18:04.550211Z 48 [Note] WSREP: commit action failed for reason: WSREP_TRX_FAIL THD: 48 Query: insert into test values (4)
      2017-12-04T11:18:04.550216Z 48 [Note] WSREP: conflict state: NO_CONFLICT
      2017-12-04T11:18:04.550221Z 48 [Note] WSREP: --------- CONFLICT DETECTED --------
      2017-12-04T11:18:04.550240Z 48 [Note] WSREP: cluster conflict due to certification failure for threads:

      2017-12-04T11:18:04.550246Z 48 [Note] WSREP: Victim thread:
      THD: 48, mode: local, state: executing, conflict: cert failure, seqno: -1
      SQL: insert into test values (4)




      WHAT DOES IT MEANS????

      Thanks.


      Comment


      • #4
        Hi krunalbauskar.

        I find the error reason. The reason is that I have to set

        binlog_format=ROW

        to work any INSERT or Import operation successfully.


        Now I need your help. The following notes are excerpt from Percona-XtraDB-Cluster-5.7.18-29.20 documentation

        .............

        binlog_format
        Galera supports only row-level replication, so set binlog_format=ROW.

        ..............
        ..............
        variable wsrep_forced_binlog_format
        Command Line --wsrep-forced-binlog-format
        Config File Yes
        Scope Global
        Dynamic Yes
        Default Value NONE
        Defines a binary log format that will always be effective, regardless of the client session binlog_format variable value.
        Possible values for this variable are:
        • ROW: Force row-based logging format
        • STATEMENT: Force statement-based logging format
        • MIXED: Force mixed logging format
        • NONE: Do not force the binary log format and use whatever is set by the binlog_format variable (default)
        ................

        As you can see from excerpt we have also variable wsrep_forced_binlog_format that MIXED value can be assigned. Although there is this variable when this variable is used with value MIXED as you can see the INSERT and IMPORT operations are failed with "ERROR 1213 (40001) at line 69: WSREP detected deadlock/conflict and aborted the transaction. Try restarting the transaction" error.

        We have to use variable wsrep_forced_binlog_format with MIXED value in our project. I wonder about that if there is such variable then why I can't use it successfully.

        We want to use Percona XtraDB Cluster in our call center projects.


        Please help me about solution.


        THANKS!!

        Mehman Jafarov









        Comment


        • #5
          1. for the first problem: I am wondering how you could get the server to start with binlog_format != ROW.

          pxc1 --binlog_format=MIXED
          2017-12-08T06:01:43.727203Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
          2017-12-08T06:01:43.727278Z 0 [Warning] Insecure configuration for --secure-file-priv: Current value does not restrict location of generated files. Consider setting it to a valid, non-empty path.
          2017-12-08T06:01:43.727297Z 0 [ERROR] WSREP: Percona-XtraDB-Cluster prohibits setting binlog_format to STATEMENT or MIXED at global level
          2017-12-08T06:01:43.727320Z 0 [ERROR] Aborting

          2017-12-08T06:01:43.727343Z 0 [Note] Binlog end

          2. MIXED format is not safe to use since it keep on switching between STATEMENT AND ROW. PXC needs ROW based logging to ensure the ordering.
          The said piece of documentation needs to be updated but we strongly recommend not to overwrite the mode to avoid the problem like the one you faced before.

          Comment

          Working...
          X