MariaDB Auto-Increment in Galera: Prevent ID Conflicts (2026)

MariaDB Auto-Increment in Galera: Prevent ID Conflicts

MariaDB’s Galera Cluster, while fantastic for synchronous replication, can trip you up with its auto-increment behavior if you’re not careful. The core issue is that each node in a Galera cluster generates its own sequence of auto-increment IDs independently. When a transaction is applied to multiple nodes, if those nodes generated overlapping ID ranges, you’ll get duplicate key errors and the cluster will split.

The Problem: Divergent Auto-Increment Sequences

When you insert a row into a table with an auto-increment primary key in a Galera cluster, the ID is generated before the transaction is broadcast to other nodes. If multiple nodes are actively inserting data, they can end up generating IDs within the same range, leading to conflicts when those transactions are applied across the cluster.

Common Causes and Fixes

Default auto_increment_increment and auto_increment_offset:
- Diagnosis: Check the current settings on each node:
```
SHOW GLOBAL VARIABLES LIKE 'auto_increment%';
```
  By default, both auto_increment_increment and auto_increment_offset are 1. This means every node tries to generate IDs starting from 1 and incrementing by 1.
- Fix: Configure auto_increment_increment to be equal to the number of nodes in your cluster, and auto_increment_offset to a unique value for each node. For a 3-node cluster:
  - Node 1: auto_increment_increment = 3, auto_increment_offset = 1
  - Node 2: auto_increment_increment = 3, auto_increment_offset = 2
  - Node 3: auto_increment_increment = 3, auto_increment_offset = 3 Apply these in your Galera configuration file (my.cnf or galera.cnf) under the [mysqld] or [galera] section:
```
[mysqld]
auto_increment_increment = 3
auto_increment_offset = 1 # For node 1
```
  Restart the MySQL service on each node after modifying the configuration.
- Why it works: auto_increment_increment tells the server to jump ahead by this many numbers for each new ID. auto_increment_offset sets the starting point within that sequence for this specific node. With these settings, Node 1 will generate IDs 1, 4, 7…, Node 2 will generate 2, 5, 8…, and Node 3 will generate 3, 6, 9… Each node has a unique, non-overlapping sequence.
Insufficient auto_increment_increment for Dynamic Node Count:
- Diagnosis: If you frequently add or remove nodes from the cluster, and auto_increment_increment is hardcoded to a fixed number (e.g., 3 for a 3-node cluster), adding a new node will cause conflicts because the existing nodes’ sequences will start to overlap with the new node’s default behavior, or the new node’s sequence will collide with existing ones.
- Fix: Set auto_increment_increment to a value larger than your expected maximum cluster size. For example, if you anticipate growing to 5 nodes, set it to 5 (or 6 to be safe). Then, carefully assign unique auto_increment_offset values from 1 to N for N nodes. If you add a node, you’ll need to reconfigure all nodes to use the new auto_increment_increment and assign new, unique offsets.
```
[mysqld]
auto_increment_increment = 5 # For a cluster that might grow to 5 nodes
auto_increment_offset = 1 # For node 1
```
- Why it works: By pre-allocating a larger sequence range, you can accommodate new nodes without immediate conflict. However, this requires careful management and re-configuration when the cluster size changes.
Manual ID Assignment or Bulk Inserts:
- Diagnosis: If you’re manually inserting IDs (e.g., INSERT INTO my_table (id, col1) VALUES (100, 'value')) or performing large bulk inserts that bypass the cluster’s auto-increment generation logic in a way that generates overlapping IDs across nodes. This is less about the Galera configuration itself and more about how you’re interacting with it.
- Fix: Avoid manually assigning IDs in Galera clusters. If you must, ensure the IDs are unique across all nodes that might receive the transaction, or use wsrep_auto_increment_control=OFF temporarily (with extreme caution and understanding of the risks) during a controlled bulk load, followed by careful re-synchronization. The best approach is to let Galera handle ID generation via the configured auto_increment_increment and auto_increment_offset.
- Why it works: Relying on the cluster-aware auto-increment mechanism ensures that IDs are generated predictably and without overlap, even for concurrent inserts.
wsrep_auto_increment_control Disabled:
- Diagnosis: Check the Galera configuration:
```
SHOW GLOBAL VARIABLES LIKE 'wsrep_auto_increment_control';
```
  If this is OFF, Galera is not managing auto-increment values at all, and each node behaves like a standalone server, leading to immediate ID conflicts.
- Fix: Enable wsrep_auto_increment_control by setting it to ON in your Galera configuration file:
```
[galera]
wsrep_auto_increment_control = ON
```
  Restart the MySQL service.
- Why it works: This setting tells Galera to actively manage and synchronize auto-increment values across the cluster, preventing nodes from generating conflicting IDs.
server_id Conflicts (Less common for auto-increment, but related):
- Diagnosis: While server_id is primarily for traditional replication, in some complex setups or older MariaDB/Galera versions, it could indirectly influence how certain replication-aware features behave. Check your server_id settings:
```
SHOW GLOBAL VARIABLES LIKE 'server_id';
```
  Ensure each node has a unique server_id.
- Fix: Assign a unique server_id to each node in its my.cnf or galera.cnf:
```
[mysqld]
server_id = 1 # For node 1
server_id = 2 # For node 2
```
  Restart the MySQL service.
- Why it works: Unique server_ids are fundamental for any replication topology, ensuring that Galera can distinguish between nodes and correctly route transactions. While not a direct cause of auto-increment divergence, it’s a foundational setting that must be correct.
Sequence Table Issues (Manual Workaround):
- Diagnosis: Some older or custom setups might use a separate sequences table to manage IDs manually. If this table is not properly synchronized or updated across all nodes, or if transactions targeting it are not handled correctly by Galera, you can get duplicate IDs.
- Fix: If you’re using a manual sequence table, ensure that operations on it are either atomic and replicated correctly by Galera, or that wsrep_auto_increment_control is used and the sequence table is deprecated. If you must use a sequence table, it needs to be explicitly managed in a Galera-aware manner, often involving wsrep_provider_options and careful transaction control. The recommended fix is to migrate away from manual sequence tables to Galera’s built-in auto_increment management.
- Why it works: Galera’s built-in auto_increment management is designed to be cluster-aware. Migrating to it ensures that ID generation is handled by the replication layer itself, guaranteeing consistency.

The Next Hurdle: Replication Lag and Large Transactions

Once your auto-increment IDs are stable, you might start noticing issues related to replication lag, especially if you have very large transactions or high write loads. Galera’s synchronous nature means that a slow node can hold up the entire cluster, and large transactions can take a long time to apply everywhere, potentially leading to application timeouts.