0

I have MariaDB configured frot 3 node Galera Cluster and haproxy and auto increment is going 1,4,7,10,13 etc

Why and how do I fix it?

[mysqld]
port = 3307
user                    = mysql
pid-file                = /run/mysqld/mysqld.pid
basedir                 = /usr
datadir                 = /var/lib/mysql
tmpdir                  = /tmp
lc-messages-dir         = /usr/share/mysql
lc-messages             = en_US
skip-external-locking


[galera]
max_allowed_packet     = 5G
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so
# Specify cluster nodes
wsrep_cluster_address="gcomm://mariadb-1,mariadb-2,mariadb-3"
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=0
bind-address=0.0.0.0
wsrep_cluster_name="galera_cluster"
wsrep_node_address="mariadb-1"
wsrep_sst_method=rsync

listen galera_cluster
    bind 0.0.0.0:3306
    mode tcp
    option tcpka
    balance source
    server mariadb-1 mariadb-1:3307  check weight 1
    server mariadb-2 mariadb-2:3307  check weight 1
    server mariadb-3 mariadb-3:3307  check weight 1
1

auto_increment_increment is necessarily set to the number of nodes in the cluster. This, together with auto_increment_offset prevents INSERTs using AUTO_INCREMENT from ever colliding.

AUTO_INCREMENT guarantees uniqueness, nothing else. You must not depend on anything else.

On another topic: max_allowed_packet = 5G is huge. It may even cause you trouble. Please justify such a large vale; we can discuss the consequences and workarounds.

6
  • The max value for max_allowed_packet is 1G. Nov 19 '21 at 20:52
  • I have changed it to add still the same max_allowed_packet = 1G auto_increment_increment = 3 and auto_increment_offse = 1 Nov 19 '21 at 23:14
  • Please do not try to change auto_increment_*; Galera needs to be in control of those two settings. Anyway, the "offset" needs to be different in each node.
    – Rick James
    Nov 20 '21 at 1:22
  • If I stop two servers it work correct. Does HaProxy setup correct? Nov 20 '21 at 3:06
  • If you stop two servers, you have no HA. Also, that one node will think that there might be a "split-brain" and it will refuse to accept writes.
    – Rick James
    Nov 20 '21 at 6:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.