Got error 5 during commit, wsrep_max_ws_size limit

MySQL & MariaDB Percona XtraDB Cluster 5.x
1

I’m running percona xtradb cluster 5.6 (5.6.22-72.0)

So I’m having problem importing a mysqldump of an innodb database that’s rather large. The error I get from the client is “Got error 5 during commit”

Some google-fu says this nondescript error is usually from an insert going over the wsrep_max_ws_size.

I’ve tried adjusting this value higher, but the limit seems to be at 4GB (4294901759 bytes). My insert for one table is quite a bit larger than this. Is this limit my actual problem, and if so… why can’t I increase beyond this 4GB limit?

2

Can you please provide my.cnf and error log to identify the root cause of problem.

3

Obviously the below files are edited to remove IP addresses and cluster/node names, as well as xtrabackup auth credentials

Local.cnf

[mysqld]
bind-address = 0.0.0.0
skip_name_resolve = 1
max_connections = 200
back_log = 200
max_allowed_packet = 256M
performance_schema = 0
table_open_cache = 40000
thread_cache_size = 24
thread_stack = 512K
query_cache_limit = 512M
query_cache_size = 64M
join_buffer_size = 4M
key_buffer_size = 4M
sort_buffer_size = 8M


# How to handle temporary tables
tmp_table_size = 16M
max_heap_table_size = 16M
tmpdir = /var/mysqltmp


# InnoDB
innodb = FORCE
innodb_file_per_table = 1
innodb_fast_shutdown = 0
innodb_flush_method = O_DIRECT
innodb_buffer_pool_size = 7G
innodb_buffer_pool_instances = 7 # Make this match the number of gig in the pool
innodb_log_file_size = 1024M
innodb_log_buffer_size = 16M
innodb_flush_log_at_trx_commit = 1
innodb_thread_concurrency = 0
#innodb_data_home_dir = /var/log/mysql/innodb
#innodb_log_group_home_dir = /var/log/mysql/innodb

# Set these only when using SSD storage
innodb_io_capacity = 2000
innodb_read_io_threads = 32 # Default: 4. But should be higher for SSD
innodb_write_io_threads = 32 # Default: 4.


# Logging
log-error = /var/log/mysql/error.log
log-output = FILE
slow-query-log = 1
log-queries-not-using-indexes = 0
slow-query-log-file = /var/log/mysql/slow-queries.log


# Binary Log
max_binlog_size = 200M
expire_logs_days = 3

Cluster.cnf


[mysqld]
wsrep_provider = /usr/lib/libgalera_smm.so
wsrep_cluster_address = gcomm://********
wsrep_slave_threads = 8
wsrep_sst_method = xtrabackup-v2
wsrep_sst_auth = **********
binlog_format = ROW
default_storage_engine = InnoDB
innodb_autoinc_lock_mode = 2
wsrep_cluster_name = *******
wsrep_retry_autocommit = 3
wsrep_max_ws_rows = 13107200
wsrep_max_ws_size = 10737418240
net_retry_count = 10
net_write_timeout = 300
net_read_timeout = 180

[sst]
streamfmt = xbstream
transferfmt = socat
encrypt = 0
progress = 1

Node.cnf


[mysqld]
wsrep_sst_receive_address = ******
wsrep_node_name = *******
wsrep_node_address = *******

Error log has no information for when the issue happens. The client side reports the error to the console directly

4

This is an old thread, but just wanted to note I ran into a similar problem. In my case I identified that it was caused by the --no-autocommit flag which was used when the mysqldump was created. I piped the mysqldump through sed to strip the set autocommit=0; and commit; statements and was able to import the mysqldump

zcat dump.sql.gz | sed '/set autocommit=0;/d' | sed '/commit;/d' | mysql mydb

I also wrote a blog post about this which has a bit more detail: https://maxchadwick.xyz/blog/error-1180-mysqldump-import