Consistency problem on 5.5.34-23.7.6

[Francesco]

I have written some simple code (java code attached) that breaks Consistency on my Percona XtraDB cluster.
If you run the code sometimes you don't get some rows (so the line "Did not get it for:" is printed out).

My configuration:
3 nodes

[root@cluster1 ~]# rpm -qa | grep -i percona
Percona-XtraDB-Cluster-shared-5.5.34-23.7.6.565.rhel6.i686
Percona-XtraDB-Cluster-galera-2.8-1.162.rhel6.i686
percona-release-0.0-1.i386
Percona-XtraDB-Cluster-client-5.5.34-23.7.6.565.rhel6.i686
Percona-Server-shared-51-5.1.72-rel14.10.597.rhel6.i686
percona-xtrabackup-2.1.5-680.rhel6.i686
Percona-XtraDB-Cluster-server-5.5.34-23.7.6.565.rhel6.i686

table used:
CREATE TABLE test.`test` (
  `id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

[root@cluster1 ~]# uname -a
Linux cluster1 2.6.32-358.el6.i686 #1 SMP Thu Feb 21 21:50:49 UTC 2013 i686 i686 i386 GNU/Linux

program output:
Info: 1
Did not get it for:36
Did not get it for:37
Info: 101
Did not get it for:102
Did not get it for:109
Did not get it for:121
Info: 201
Did not get it for:231
Did not get it for:246
Did not get it for:293
Info: 301
Did not get it for:306
Did not get it for:308
Did not get it for:388
Did not get it for:395
Info: 401
Info: 501
Did not get it for:509
Did not get it for:574
Info: 601
Did not get it for:602
Did not get it for:659
Did not get it for:670
Did not get it for:674
Info: 701
Did not get it for:792
Did not get it for:800
Info: 801
Did not get it for:817
Did not get it for:821
Did not get it for:823
Did not get it for:844
Did not get it for:846
Did not get it for:848
Did not get it for:892
Did not get it for:895
Did not get it for:900
Info: 901
Did not get it for:936
Did not get it for:942
Did not get it for:969
Did not get it for:997

my.cnf:

[mysql]
# CLIENT #
port = 3306
socket = /data/mysql/mysql.sock
default-character-set=utf8

[client]
socket = /data/mysql/mysql.sock
default-character-set=utf8

[mysqld]
# GENERAL #
user = mysql
default_storage_engine = InnoDB
socket = /data/mysql/mysql.sock
pid_file = /data/mysql/mysql.pid

# MyISAM #
key_buffer_size = 32M
myisam_recover = FORCE,BACKUP

# SAFETY #
max_allowed_packet = 16M
max_connect_errors = 1000000
skip_name_resolve
sql_mode = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY
sysdate_is_now = 1
innodb = FORCE
innodb_strict_mode = 1

# DATA STORAGE #
datadir = /data/mysql/

# BINARY LOGGING #
log_bin = /data/mysql/mysql-bin
expire_logs_days = 14
sync_binlog = 1

# CACHES AND LIMITS #
tmp_table_size = 32M
max_heap_table_size = 32M
query_cache_type = 0
query_cache_size = 0
max_connections = 500
thread_cache_size = 50
open_files_limit = 65535
table_definition_cache = 1024
table_open_cache = 100

# INNODB #
innodb_flush_method = O_DIRECT
innodb_log_files_in_group = 2
innodb_log_file_size = 64M
innodb_flush_log_at_trx_commit = 1
innodb_file_per_table = 1
innodb_buffer_pool_size = 512M

# LOGGING #
log_error = /data/mysql/mysql-error.log
log_queries_not_using_indexes = 1
slow_query_log = 1
slow_query_log_file = /data/mysql/mysql-slow.log

# UTF8 #
collation-server = utf8_unicode_ci
init-connect='SET NAMES utf8'
character-set-server = utf8

#### Cluster config ####
# Path to Galera library
wsrep_provider = /usr/lib/libgalera_smm.so

# Cluster connection URL contains the IPs of all the nodes
wsrep_cluster_address = gcomm://192.168.0.101,192.168.0.102,192.168.0.103

# In order for Galera to work correctly binlog format should be ROW
binlog_format = ROW

# This is a recommended tuning variable for performance
innodb_locks_unsafe_for_binlog = 1

# This changes how InnoDB auto-increment locks are managed and is a requirement for Galera
innodb_autoinc_lock_mode = 2

# IP address of this node
wsrep_node_address = 192.168.0.101

# SST method
wsrep_sst_method = xtrabackup-v2

# Cluster name
wsrep_cluster_name = data_cluster

# Authentication for SST method
wsrep_sst_auth = "sstuser:password"

[Francesco (francesco-l)]

java code to reproduce

[Jay Janssen (jay-janssen)]

Francesco: I believe you are basically testing if an insert immediately appears on another node, which it will not.

Galera replication is synchronous, but the actual apply and commit on the other nodes is not. This is for replication efficiency reasons. If you want to guarantee consistency, you need to pay for it by enabling wsrep_causal_reads on your cluster. This will increase read latency while it waits for the replication queue to flush on a select.

I'd try enabling that on your cluster and re-running your code.

[Francesco (francesco-l)]

Jay, thank you for you reply.

I think I've been a bit confused by the documentation ( http://www.percona.com/doc/percona-xtradb-cluster/index.html )
At the very first page of the docs it says:
---
Welcome to Percona XtraDB Cluster’s documentation!
Percona XtraDB Cluster is High Availability and Scalability solution for MySQL Users.

Percona XtraDB Cluster provides:

- Synchronous replication. Transaction either commited on all nodes or none.
---

it is a bit ambiguous, isn't it?

I'll try to run the code with your configuration suggestion too, thanks!

[Jay Janssen (jay-janssen)]

I did not write the marketing. Galera’s replication is “virtually synchronous” — the replication aspect *is* synchronous, but the apply is not — otherwise this would just be multi-phase commit replication which is extremely network chatty and slow.

On Nov 15, 2013, at 5:02 AM, Francesco <email address hidden> wrote:

>
> it is a bit ambiguous, isn't it?
>
> I'll try to run the code with your configuration suggestion too, thanks!

Jay Janssen, MySQL Consulting Lead, Percona
http://about.me/jay.janssen

[Francesco (francesco-l)]

Thank you for the explanation. If I can, I suggest to change the "marketing", mainly because that sentence is written in the first page of the documentation page ( http://www.percona.com/doc/percona-xtradb-cluster/5.5/index.html ).

[Launchpad Janitor (janitor)]

[Expired for Percona XtraDB Cluster because there has been no activity for 60 days.]

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PXC-1513