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

Question

I have a MariaDB Galera Cluster(3 nodes), I set uid to increase automatically and be the primary 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 is reported in MariaDB Galera Cluster - Known Limitations:

Do not rely on auto-increment values to be sequential. Galera uses a mechanism based on autoincrement increment to produce unique non-conflicting sequences, so on every single node the sequence will have gaps.

The rational is explained in Managing Auto Increments with Multi Masters, and is also why the observed auto-increment has the same step as the number of clusters.

MySQL has system variables auto_increment_increment and auto_increment_offset for managing auto increment 'sequences' in multi master environment. Using these variables, it is possible to set up a multi master replication, where auto increment sequences in each master node interleave, and no conflicts should happen in the cluster. No matter which master(s) get the INSERTs.

Even without clusters, it is rarely a "good" idea to rely on auto-increment columns to be dense sequences due to transaction rollbacks and deleted records.