Not the answer you need?
Register and ask your own question!
Many Forum changes were implemented on Tue 22 Sep. Read about new Ranks, Scoring, and Reactions.
Email [email protected] for any comments or concerns.

Got error 5 during commit, wsrep_max_ws_size limit

aesellarsaesellars ContributorInactive User Role Beginner
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?

Comments

  • mirfanmirfan Database Administrator Inactive User Role Beginner
    Can you please provide my.cnf and error log to identify the root cause of problem.
  • aesellarsaesellars Contributor Inactive User Role Beginner
    mirfan wrote: »
    Can you please provide my.cnf and error log to identify the root cause of problem.


    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
  • mpchadwickmpchadwick Current User Role Beginner
    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
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.