[MDEV-20553] Large File Import fails with WSREP: transaction size limit exceeded Created: 2019-09-10  Updated: 2019-12-12  Resolved: 2019-12-12

Status: Closed
Project: MariaDB Server
Component/s: Galera, wsrep
Affects Version/s: 10.4.7
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Justin M Assignee: Jan Lindström (Inactive)
Resolution: Not a Bug Votes: 0
Labels: None
Environment:

Ubuntu 18.04 running Mariadb 10.4.7 Galera Cluster


Issue Links:
Problem/Incident
is caused by MDEV-18564 Change wsrep_load_data_splitting off ... Closed

 Description   

Loading a delimited text file fails in mysql LOAD DATA INFILE query or using the mysqlimport program. Test file is 17 million rows and 4.7GiB. Error thrown is

[Warning] WSREP: transaction size limit (2147483647) exceeded: 2147483648
[ERROR] WSREP: rbr write fail, data_len: 2147483648

I can fix it by setting the wsrep_load_data_splitting='ON' variable, but the documentation says this was deprecated in 10.4.3 because of a new continuous replication change. I'm on 10.4.7 and having this set to off does not work. Not sure if I have a setting in my config breaking this feature, or if it is not yet running fully.

My mariadb.cnf:
[mysqld]
#mysql settings
datadir = /var/lib/mysql
innodb_buffer_pool_size = 15GB
innodb_buffer_pool_instances = 35
innodb_log_file_size = 2G
innodb_log_buffer_size = 16M
innodb_read_io_threads = 64
innodb_write_io_threads = 64
innodb_stats_on_metadata = 0
sql_mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"
default_storage_engine=innodb
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
character-set-client-handshake = false
ft_min_word_len = 3
long_query_time = 5
lower_case_table_names = 1

[galera]

  1. Mandatory settings
    wsrep_on=ON
    wsrep_provider=/usr/lib/galera/libgalera_smm.so
    wsrep_cluster_address=gcomm://10.10.10.11,10.10.10.12
    wsrep_node_address=10.10.10.11
    wsrep_sst_receive_address=10.10.10.11
    wsrep_node_name=Galera-Node1
    binlog_format=row
    wsrep_load_data_splitting= ON
    default_storage_engine=InnoDB
    innodb_autoinc_lock_mode=1
    wsrep_retry_autocommit=10
    wsrep_slave_threads=32
    wsrep_provider_options=gcache.size=2G
  2. Custom settings
    wsrep_cluster_name="Galera-Cluster1"
    wsrep_sst_method = mariabackup
    bind-address=10.10.10.11
    skip-name-resolve

performance_schema = ON
innodb_flush_log_at_trx_commit = 0
wsrep_notify_cmd = notify



 Comments   
Comment by Justin M [ 2019-09-11 ]

Not sure if related directly, but I also get "Got error 5 input/output error during COMMIT" on a query of this same table once imported. There are no errors thrown in /var/log/mysql/error.log

SET @cnt = 0;
update table
set column = (@cnt := @cnt + 1)
ORDER BY column2 ASC;

Comment by Jan Lindström (Inactive) [ 2019-12-12 ]

If your transaction size limit is reached you should use streaming replication with correct configuration.

Generated at Thu Feb 08 09:00:21 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.