Incomplete and inconsistant replication between cluster members when using bulk inserts

[stackfault]

Hi!

I have a multi-master Galera cluster setup. I can insert rows in it and they get replicated to the other nodes without problem on small scale inserts. Databases, tables creation, everything.

However, when I import a backup of a database made with mysqldump and bulk inserts, The rows count does not exactly match on the replicated servers for some tables, only the server I ran the command on have to right amount of rows. All servers reports in sync and no error in the logs.

After several tests, the number of rows missing is random and it's not always on the same table or the same db. All tables are InnoDB and there is no latency between the hosts. That lab environment is even running within the same machine in VMs.

I am using 5.6.25-25.11-1jessie on Debian Jessie amd64.

Here is the relevant configuration:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
binlog_format=ROW
bind-address=0.0.0.0
default_storage_engine=innodb

innodb_locks_unsafe_for_binlog=1
query_cache_size=0
query_cache_type=0
wsrep_slave_threads=1
wsrep_certify_nonPK=1
wsrep_max_ws_rows=131072
wsrep_max_ws_size=1073741824
wsrep_convert_LOCK_to_trx=0
wsrep_retry_autocommit=1
wsrep_auto_increment_control=1
wsrep_causal_reads=0
wsrep_log_conflicts=ON

innodb_autoinc_lock_mode=2
innodb_flush_log_at_trx_commit=0
innodb_buffer_pool_size=256M
wsrep_provider=/usr/lib/libgalera_smm.so
wsrep_provider_options="gcache.size=300M; gcache.page_size=1G"
wsrep_cluster_name="my_cluster_name_here"
wsrep_cluster_address="gcomm://x.x.x.y,a.a.a.b"
wsrep_node_name="my_nodename_here"
wsrep_node_address="x.x.x.x"
wsrep_sst_method=rsync

[mysql_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Any ideas would be appreciated.

Thanks,

[Philip Stoev]

Hello,

What is the size of the mysqldump file you are trying to restore? Can you
provide the mysqldump options that were used to create it?

The tables that are affected -- how many rows should they contain and how
many they actually contain?

Thank you.

Philip Stoev

--
You received this message because you are subscribed to the Google Groups
"codership" group.
To unsubscribe from this group and stop receiving emails from it, send an
email to codership-tea...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[stackfault]

Hi!

Here is the command used for mysqldump:

mysqldump --single_transaction --extended-insert --flush-logs --user='username_goes_here' --password='unsafe_password_on_cmd_line' --databases database_name > backup.sql

I also tried without: --single_transaction --extended-insert --flush-logs with the same (random) results.

The output dump file is 17mb in text.

In my last attempt, I have imported the same sql file on both an unclustered setup and a clustered one for the comparison since the source have changed significantly since.
I have 6 tables out of 55 with unmatched row numbers. All other tables are matching, ranging from 0 rows to 7000 rows, most below 100.

Here is the list:

Legend:
Table  Unclustered  Node1  Node2

table_a    473    474    474
table_b    55694    59559    62397
table_c    3906    4468    3541
table_d    22149    22344    22367
table_e    4272    4248    4290
table_f     8129    8131    8131

Doing this table, I noticed 5 out of the 6 have more rows in the clustered setup than the original database. A loop maybe? I have 6 nodes and a garbd in this setup but I have shutdown 4 nodes to keep it simple until I figured it out. Now the cluster is running 2 nodes and a garbd.

Thanks,

[James Wang]

Had no such discrepancy before, interested in knowing the answer ...