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

MySQL & MariaDB Percona XtraDB Cluster 5.x
1

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:

[I][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_ZERO_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[/I]

Output of log is
…
[I]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]
…

I can’t find the reason.

PLEASE HELP ME. IT IS URGENT!!!

Thanks!!!

1 Like
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.

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:

[I]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)[/I]

WHAT DOES IT MEANS???

Thanks.

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

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

  1. 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.