auto_increment jumps by 3 and not 1

Question

I have noticed that in my mysql server, the auto_increment setting increments by 3, for example 3 6 9 12 15 etc.

All the tables in the database are having the same problem.

I'm using mariadb 10.5 on debian 10.

Could it be because of having a 3 vps galley cluster?

MariaDB [(none)]> SHOW VARIABLES LIKE 'auto_inc%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 3     |
| auto_increment_offset    | 1     |
+--------------------------+-------+
2 rows in set (0.002 sec)

Please demonstrate [Why should I provide an mcve for what seems to me to be a very simple sql query](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query) — RiggsFolly, Sep 16 '20 at 14:20
Try something like `SELECT Auto_increment FROM information_schema.tables WHERE table_name='the_table_you_want';` and tell us what are you getting? — Alexandre Elshobokshy, Sep 16 '20 at 14:23
Try this query `SHOW VARIABLES LIKE 'auto_inc%'` and tell the result? it will give 2 variable names and their values — Akhilesh Mishra, Sep 16 '20 at 14:45
MariaDB [(none)]> SHOW VARIABLES LIKE 'auto_inc%'; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | auto_increment_increment | 3 | | auto_increment_offset | 1 | +--------------------------+-------+ 2 rows in set (0.002 sec) — diaconu liviu, Sep 16 '20 at 14:53
I have not changed anything in the MySQL settings. I use these servers for an app that I am developing in php. — diaconu liviu, Sep 16 '20 at 15:20
Could this be the reason? https://galeracluster.com/library/kb/auto-increment-multiples.html — diaconu liviu, Sep 16 '20 at 15:20

score 3 · Accepted Answer · answered Sep 28 '20 at 03:27

They designed it to be like that. You could say that it is a limitation of using Galera. They made it like that to address the collision issue when generating sequential numbers as primary key during inserts in a Multi-Master cluster.

https://mariadb.org/auto-increments-in-galera/

score 2 · Answer 2 · answered Sep 17 '20 at 05:30

2

If you are actually using Galera, then auto_increment_increment needs to be 3 (or however many nodes (servers) are in the cluster).

SHOW VARIABLES LIKE 'wsrep%';

answered Sep 17 '20 at 05:30

Rick James

135,179
13
127
222

Yes, I use galera, then it is normal to be 3 in 3. And this does not affect that the id int255 ends 3 times faster than 1 in 1? – diaconu liviu Sep 17 '20 at 10:30
1

@diaconuliviu - What is int255? If you mean `TINYINT UNSIGNED`, then yes, each node can insert about 85 rows before overflowing. Take that into account when specifying datatypes. Meanwhile `auto_increment_offset` should be 3 different values for the 3 nodes. If you insert only into one node, then 85 rows is the limit. If you insert in just the right way spread across 3 nodes, it won't overflow before about 255 rows. – Rick James Sep 17 '20 at 18:05

auto_increment jumps by 3 and not 1

2 Answers2