Why the auto_increment identifier does not increase one by one, how to set it?

Question

I have a Galera MariaDB cluster (3 nodes), I set uid to auto-grow and became the main key of the table as

  `uid | int(11) | NO | PRI | NULL | auto_increment`. MariaDB [hello_cluster]> select uid from table order by uid limit 10; +-----+ | uid | +-----+ | 3 | | 6 | | 9 | | 12 | | 15 | | 18 | | 21 | | 24 | | 27 | | 30 | +-----+ 

I tried the following command and it does not work

  alter table uid AUTO_INCREMENT=1 

Answer 1

This is by design and reported by MariaDB Galera Cluster - Known limitations :

Do not rely on auto-increment values ​​that should be consistent. Galera uses an auto-increment-based mechanism to create unique, never-ending sequences, so the sequence will have spaces on each individual node.

The rationalization is explained in "Managing Automatic Increments Using Multiple Masters" , and also because the observed auto-increment has the same step as the number of clusters.

MySQL has the system variables auto_increment_increment and auto_increment_offset for managing automatic growth sequences in a multi-wizard environment. Using these variables, you can configure replication with several main masters, where automatic increment sequences alternate in each main node, and there should be no conflicts in the cluster. No matter which master (s) receives the INSERTS.

Even without clusters, it is rarely a “good” idea to rely on auto-grow columns to be tight sequences due to transaction rollbacks and deleted records.