Could not execute Write/Delete_rows event,Node consistency compromized, aborting
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
Percona XtraDB Cluster moved to https://jira.percona.com/projects/PXC |
New
|
Undecided
|
Unassigned |
Bug Description
OS: Red Hat Enterprise Linux Server release 7.2 (Maipo)
Software: Percona XtraDB Cluster 5.7.18-29.20
Cluster: we have three nodes==>node85 for write;node86 and node87 for read only.
there are a pressure test at that time,table cart & shopcart had many insert & delete operation.
The node86(read node) aborted at 2017-09-28T10:00:11 and node85(write node) aborted at 2017-09-
we start these nodes by .../bin/mysqld later,the cluster recovery.
error log for node86:
017-09-
rror HA_ERR_
2017-09-
2017-09-
at galera/
Retrying 2th time
2017-09-
rror HA_ERR_
2017-09-
2017-09-
at galera/
Retrying 3th time
2017-09-
rror HA_ERR_
2017-09-
2017-09-
at galera/
Retrying 4th time
2017-09-
rror HA_ERR_
2017-09-
2017-09-
s (l: 165942, g: 163162, s: 163161, d: 163027, ts: 13749441138646984)
2017-09-
2017-09-
error log for node85:
2017-09-
T_FOUND; the event's master log FIRST, end_log_pos 497, Error_code: 1032
2017-09-
2017-09-
at galera/
Retrying 2th time
2017-09-
T_FOUND; the event's master log FIRST, end_log_pos 497, Error_code: 1032
2017-09-
2017-09-
at galera/
Retrying 3th time
2017-09-
T_FOUND; the event's master log FIRST, end_log_pos 497, Error_code: 1032
2017-09-
2017-09-
at galera/
Retrying 4th time
2017-09-
2017-09-
2017-09-
2017-09-
2017-09-
here are the two tables:
system@localhost 11:40: [shopcart]> show create table cart\G
*******
Table: cart
Create Table: CREATE TABLE `cart` (
`sku_id` int(10) unsigned NOT NULL,
`quantity` smallint(5) unsigned NOT NULL,
`user_id` bigint(10) unsigned NOT NULL,
`channel` varchar(100) DEFAULT NULL,
`type` tinyint(3) unsigned NOT NULL,
`checked` varchar(10) NOT NULL DEFAULT '1' COMMENT '勾选:1—选中,0-未选中',
`create_time` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
`last_update` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`user_
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
system@localhost 11:40: [shopcart]> show create table shopcart\G
*******
Table: shopcart
Create Table: CREATE TABLE `shopcart` (
`id` varchar(50) NOT NULL,
`user_id` bigint(20) NOT NULL COMMENT '用户ID',
`type` tinyint(4) NOT NULL COMMENT '订单类型 1--普通订单 2--定金订单 3--预售订单',
`order_id` varchar(50) DEFAULT NULL COMMENT '订单号',
`total_amount` decimal(20,2) NOT NULL DEFAULT '0.00' COMMENT '总价',
`total_
`shipping` decimal(20,2) NOT NULL DEFAULT '0.00' COMMENT '运费',
`shipping_
`pay_method` varchar(50) DEFAULT NULL COMMENT '支付方式 : ONLINE_PAY COD',
`channel` varchar(50) DEFAULT NULL COMMENT '渠道 PC、MOBILE、
`warp_part` varchar(64) DEFAULT NULL COMMENT '礼品包装货号',
`wrap_price` decimal(20,2) DEFAULT NULL COMMENT '礼品包装价格',
`comments` text COMMENT '备注',
`gift_comments` varchar(50) DEFAULT NULL COMMENT '礼品卡',
`address_id` bigint(20) DEFAULT NULL COMMENT '收货地址',
`invoice_type` varchar(50) DEFAULT '' COMMENT '发票类型',
`delivery_info` varchar(500) DEFAULT NULL COMMENT '配送信息',
`PAYMENT_CODE` varchar(32) DEFAULT NULL COMMENT '支付方式',
`BANK_CODE` varchar(19) DEFAULT NULL COMMENT '银行编码',
`create_time` timestamp NOT NULL COMMENT '订单创建时间',
`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '订单更新时间',
`create_user` varchar(50) NOT NULL,
`update_user` varchar(50) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='购物车表'
1 row in set (0.00 sec)
my.cnf:
node85:
root@dpsvstadbs02 11:49:49:~$ cat /etc/my.cnf
#
##mysql configure file my.cnf
#
[client]
port = 3306
socket = /tmp/mysql.sock
default-
[mysql]
prompt="\u@\h \R:\m:\s [\d]> "
no-auto-rehash
default-
[mysqld]
user = mysql
port = 3306
basedir = /usr/local/mysql
datadir = /docker/
socket = /tmp/mysql.sock
pid-file = dpppnewdbs04.
character-
skip_name_resolve = 1
log_bin_
lower_case_
open_files_limit = 65535
back_log = 1024
server-id = 853306
#binlog
log-bin = /docker/
binlog_format = row
sync_binlog = 1
binlog_cache_size = 512M
expire_logs_days = 3
max_binlog_
max_binlog_size = 1G
master_
relay_log_
gtid_mode = on
enforce_
log_slave_updates
#slow & error log
slow_query_log = 1
slow_query_log_file = /docker/
log-error = /docker/
long_query_time = 0.1
#general_log=on
#general_
max_connections = 10000
max_connect_errors = 1000000
table_open_cache = 128
table_definitio
table_open_
thread_stack = 512K
external-locking = FALSE
max_allowed_packet = 64M
sort_buffer_size = 8M
join_buffer_size = 8M
thread_cache_size = 1500
query_cache_size = 0
query_cache_type = 0
interactive_timeout = 600
wait_timeout = 600
tmp_table_size = 64M
max_heap_table_size = 32M
relay_log_recovery = 1
relay-log-purge = 1
key_buffer_size = 32M
read_buffer_size = 8M
read_rnd_
bulk_insert_
myisam_
myisam_
myisam_
lock_wait_timeout = 3600
explicit_
transaction_
#innodb
default_
innodb_
innodb_
innodb_
#innodb_
innodb_
innodb_
innodb_
innodb_
innodb_
innodb_
innodb_
innodb_
innodb_
innodb_
innodb_io_capacity = 4000
innodb_
innodb_
innodb_
innodb_
innodb_
innodb_open_files = 65535
innodb_
innodb_flush_method = O_DIRECT
innodb_
innodb_
#innodb_file_format = Barracuda
#innodb_
innodb_
innodb_
innodb_
innodb_
innodb_
internal_
innodb_
innodb_status_file = 1
# 注意: 开启 innodb_
innodb_
innodb_
#performance_schema
#performance_schema = 1
#performance_
#innodb monitor
#innodb_
#innodb_
#innodb_
#innodb_
#innodb_
#innodb_
#innodb_
#innodb_
#innodb_
#innodb_
#innodb_
#innodb_
#innodb_
#innodb_
#pxc
wsrep_provider = /usr/local/
wsrep_cluster_
wsrep_node_name = node85
wsrep_node_address = 10.157.24.85
wsrep_cluster_name = pxc_stage
wsrep_sst_auth = sst:sst123
wsrep_sst_method = xtrabackup-v2
wsrep_slave_threads = 8
pxc_strict_mode = ENFORCING
innodb_
wsrep_provider_
[mysqldump]
quick
max_allowed_packet = 1024M
node86:
root@dpsvstadbs03 11:50:22:~$ cat /etc/my.cnf
#
##mysql configure file my.cnf
#
[client]
port = 3306
socket = /tmp/mysql.sock
default-
[mysql]
prompt="\u@\h \R:\m:\s [\d]> "
no-auto-rehash
default-
[mysqld]
user = mysql
port = 3306
basedir = /usr/local/mysql
datadir = /docker/
socket = /tmp/mysql.sock
pid-file = dpppnewdbs04.
character-
skip_name_resolve = 1
log_bin_
lower_case_
open_files_limit = 65535
back_log = 1024
server-id = 863306
#binlog
log-bin = /docker/
binlog_format = row
sync_binlog = 1
binlog_cache_size = 512M
expire_logs_days = 7
max_binlog_
max_binlog_size = 1G
master_
relay_log_
gtid_mode = on
enforce_
log_slave_updates
#slow & error log
slow_query_log = 1
slow_query_log_file = /docker/
log-error = /docker/
long_query_time = 0.1
#general_log=on
#general_
max_connections = 10000
max_connect_errors = 1000000
table_open_cache = 128
table_definitio
table_open_
thread_stack = 512K
external-locking = FALSE
max_allowed_packet = 64M
sort_buffer_size = 8M
join_buffer_size = 8M
thread_cache_size = 1500
query_cache_size = 0
query_cache_type = 0
interactive_timeout = 600
wait_timeout = 600
tmp_table_size = 64M
max_heap_table_size = 32M
relay_log_recovery = 1
relay-log-purge = 1
key_buffer_size = 32M
read_buffer_size = 8M
read_rnd_
bulk_insert_
myisam_
myisam_
myisam_
lock_wait_timeout = 3600
explicit_
transaction_
#innodb
default_
innodb_
innodb_
innodb_
#innodb_
innodb_
innodb_
innodb_
innodb_
innodb_
innodb_
innodb_
innodb_
innodb_
innodb_
innodb_io_capacity = 4000
innodb_
innodb_
innodb_
innodb_
innodb_
innodb_open_files = 65535
innodb_
innodb_flush_method = O_DIRECT
innodb_
innodb_
#innodb_file_format = Barracuda
#innodb_
innodb_
innodb_
innodb_
innodb_
innodb_
internal_
innodb_
innodb_status_file = 1
# 注意: 开启 innodb_
innodb_
innodb_
#performance_schema
#performance_schema = 1
#performance_
#innodb monitor
#innodb_
#innodb_
#innodb_
#innodb_
#innodb_
#innodb_
#innodb_
#innodb_
#innodb_
#innodb_
#innodb_
#innodb_
#innodb_
#innodb_
#pxc
wsrep_provider = /usr/local/
wsrep_cluster_
wsrep_node_name = node86
wsrep_node_address = 10.157.24.86
wsrep_cluster_name = pxc_stage
wsrep_sst_auth = sst:sst123
wsrep_sst_method = xtrabackup-v2
wsrep_slave_threads = 8
pxc_strict_mode = ENFORCING
innodb_
wsrep_provider_
[mysqldump]
quick
max_allowed_packet = 1024M
Changed in percona-xtradb-cluster: | |
status: | New → Incomplete |
status: | Incomplete → New |
assignee: | nobody → sampson,li (sampsonli) |
assignee: | sampson,li (sampsonli) → nobody |
* Normally an error like that suggest some inconsitency with cluster node.
This can happen if there is local trx that is not replicated.
* If above is not the case wondering if you can still reproduce it at will and can share the steps for us to investigate it locally.