How galera really syncs innodb tables?

[Umarzuki Mochlis]

Below is output of query for a innodb table from synced two nodes (+1 garbd) galera mariadb 10.0 cluster

On first db server

MariaDB [FedTest]> select * from staff;
+----+------+
| id | name |
+----+------+
|  1 | ali  |
|  3 | abu  |
|  5 | ling |
+----+------+
3 rows in set (0.00 sec)

On second db server

MariaDB [FedTest]> select * from staff;
+----+-------+
| id | name  |
+----+-------+
|  2 | chong |
|  4 | kerel |
|  6 | umar  |
+----+-------+
3 rows in set (0.00 sec)

It seems that data got partitioned or something. Is this the way that galera does its stuff or it is something I did not do?

/etc/mysql/my.cnf

[MYSQLD]
user=mysql
basedir=/usr/
datadir=/var/lib/mysql
socket=/var/run/mysqld/mysqld.sock
pid_file=mysqld.pid
port=3306
log_error=/var/log/mysql.log
log_warnings=2
innodb_buffer_pool_size=72374M
innodb_flush_log_at_trx_commit=2
innodb_file_per_table=1
innodb_data_file_path = ibdata1:100M:autoextend
innodb_read_io_threads=4
innodb_write_io_threads=4
innodb_io_capacity=200
innodb_doublewrite=1
innodb_log_file_size=1024M
innodb_log_buffer_size=96M
innodb_buffer_pool_instances=8
innodb_log_files_in_group=2
innodb_thread_concurrency=64
innodb_flush_method = O_DIRECT
innodb_autoinc_lock_mode=2
innodb_stats_on_metadata=0
default_storage_engine=innodb
binlog_format=ROW
key_buffer_size = 24M
tmp_table_size = 64M
max_heap_table_size = 64M
max_allowed_packet = 1024M
skip_name_resolve
memlock=0
sysdate_is_now=1
max_connections=1000
thread_cache_size=512
query_cache_type = 0
query_cache_size = 0
table_open_cache=10240
lower_case_table_names=0
explicit_defaults_for_timestamp=1
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_node_address=10.1.1.100
wsrep_provider_options="gcache.size=128M; gmcast.segment=0"
wsrep_cluster_name="my_wsrep_cluster"
wsrep_cluster_address=gcomm://10.1.1.100,10.1.1.101
wsrep_node_name=10.1.1.100
wsrep_slave_threads=8
wsrep_certify_nonPK=1
wsrep_max_ws_rows=131072
wsrep_max_ws_size=1073741824
wsrep_debug=0
wsrep_convert_LOCK_to_trx=0
wsrep_retry_autocommit=1
wsrep_auto_increment_control=1
wsrep_replicate_myisam=1
wsrep_drupal_282555_workaround=0
wsrep_causal_reads=0
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth=root:password
[MYSQL]
socket=/var/run/mysqld/mysqld.sock
[client]
socket=/var/run/mysqld/mysqld.sock
[mysqldump]
max_allowed_packet = 512M
socket=/var/run/mysqld/mysqld.sock
[MYSQLD_SAFE]
pid_file=mysqld.pid
log_error=/var/log/mysql.log
basedir=/usr/
datadir=/var/lib/mysql

[Umarzuki Mochlis]

adding more info

table type (innodb?)

MariaDB [FedTest]> SHOW TABLE STATUS WHERE Name = 'staff';
+-------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length |
Data_length | Max_data_length | Index_length | Data_free |
Auto_increment | Create_time | Update_time | Check_time |
Collation | Checksum | Create_options | Comment |
+-------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| staff | InnoDB | 10 | Compact | 2 | 8192 |
16384 | 0 | 0 | 0 | 7 |
2015-04-09 16:40:41 | NULL | NULL | utf8_general_ci |
NULL | | |
+-------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
1 row in set (0.00 sec)

is there a primary key?

MariaDB [FedTest]> SHOW KEYS FROM staff WHERE Key_name = 'PRIMARY'
-> ;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name |
Collation | Cardinality | Sub_part | Packed | Null | Index_type |
Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| staff | 0 | PRIMARY | 1 | id | A
| 2 | NULL | NULL | | BTREE | |
|
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

tables populated using phpmyadmin 4.3.10

the sst method xtrabackup-v2

> --
> 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.

[Umarzuki Mochlis]

I just found out that this is limitation issue because affected table
is auto increment which is a limitation on galera.

https://mariadb.com/kb/en/mariadb/mariadb-galera-cluster-known-limitations/

Any suggestion for workaround?

[alexey.y...@galeracluster.com]

Auto increment limitation is only that autoincrement values are not
sequential. This does not explain different table contents.

The only way I can think of producing such result is to set wsrep_on=OFF
for the session. See what your phpadmin is doing with session settings.