# Undocumented behavior with auto\_increment columns in a cluster

**URL:** <https://forums.percona.com/t/undocumented-behavior-with-auto-increment-columns-in-a-cluster/5295>\
**Category:** Percona XtraDB Cluster 5.x\
**Created:** [December 14, 2016, 6:46pm UTC](https://forums.percona.com/t/undocumented-behavior-with-auto-increment-columns-in-a-cluster/5295 "2016-12-14T18:46:00Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![cmcgrail](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/cmcgrail/32/15_2.png) [@cmcgrail](https://forums.percona.com/u/cmcgrail)\
**Post date:** [December 14, 2016, 6:46pm UTC](https://forums.percona.com/t/undocumented-behavior-with-auto-increment-columns-in-a-cluster/5295/1 "2016-12-14T18:46:00Z")

</div>

Given:

A three node cluster. wsrep\_25.10.r4144  
wsrep\_auto\_increment\_control = ON (default) on all nodes  
auto\_increment\_increment [3] and auto\_increment\_offset [1, 2, or 3 depending on the node] appear to be managed properly on all three nodes.  
User schema has about 110 tables with auto\_increment primary key columns.  
Clients connect directly to a node according to their function. There is no proxy or load balancer spreading work across nodes.  
A set of tables receive inserts on node 1, and only on node 1.  
A separate set of tables receive inserts on node3, and only on node 3.  
No inserts are performed on node2.

Given the above we would expect that values in auto\_increment columns would increase by 3 in all tables. We only see that in about 50 of the 110 tables.

Undocumented behavior:

In the remaining 60 of the 110 tables, the auto\_increment columns are incrementing by 1. It is reproducible that consecutive inserts direct to one node will cause this.

This inconsistency is concerning and I’d like to determine the cause of it so I can prevent it from causing problems in the future if our usage changes.

Any advice on where to look next would be appreciated.

---

<div class="post-metadata">

**Author:** ![jrivera](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/jrivera/32/13_2.png) [@jrivera](https://forums.percona.com/u/jrivera)\
**Post date:** [December 16, 2016, 7:22am UTC](https://forums.percona.com/t/undocumented-behavior-with-auto-increment-columns-in-a-cluster/5295/2 "2016-12-16T07:22:52Z")

</div>

Can’t seem to reproduce this case, ran sysbench on 100 tables of 10000 records each but haven’t seen any of the tables with increments of 1. Please share my.cnf from affected node and my.cnf from at least other node not affected by this case.

---

<div class="post-metadata">

**Author:** ![cmcgrail](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/cmcgrail/32/15_2.png) [@cmcgrail](https://forums.percona.com/u/cmcgrail)\
**Post date:** [December 16, 2016, 10:44pm UTC](https://forums.percona.com/t/undocumented-behavior-with-auto-increment-columns-in-a-cluster/5295/3 "2016-12-16T22:44:35Z")

</div>

Thanks for the response!

This is from the node that accepts almost all of the inserts in the cluster.

[mysqld]

basedir=/usr/  
binlog\_format=ROW  
datadir=/opt/data  
default\_storage\_engine=innodb  
explicit\_defaults\_for\_timestamp=1  
innodb\_autoinc\_lock\_mode=2  
innodb\_buffer\_pool\_instances=8  
innodb\_buffer\_pool\_size=8264M  
innodb\_data\_file\_path = ibdata1:100M:autoextend  
innodb\_doublewrite=1  
innodb\_file\_per\_table=1  
innodb\_flush\_log\_at\_trx\_commit=2  
innodb\_flush\_method = O\_DIRECT  
innodb\_io\_capacity=800  
innodb\_log\_buffer\_size=96M  
innodb\_log\_files\_in\_group=2  
innodb\_log\_file\_size=1024M  
innodb\_print\_all\_deadlocks = 1  
innodb\_read\_io\_threads=8  
innodb\_stats\_on\_metadata=0  
innodb\_thread\_concurrency=64  
innodb\_write\_io\_threads=8  
join\_buffer\_size = 15M  
key\_buffer\_size = 128M  
log\_error=/var/log/mysql.log  
log-queries-not-using-indexes  
log-slow-verbosity=query\_plan,explain  
log\_warnings=1  
long\_query\_time = 1  
lower\_case\_table\_names=0  
max\_allowed\_packet = 512M  
max\_connections=275  
max\_heap\_table\_size = 64M  
memlock=0  
performance\_schema=ON  
pid\_file=mysqld.pid  
port=3306  
query\_cache\_limit = 4M  
query\_cache\_size = 256M  
query\_cache\_type = 1  
read\_buffer\_size = 5M  
read\_rnd\_buffer\_size = 256M  
slow-query-log = 1  
slow-query-log-file = /var/log/mysql/mysql-slow.log  
socket=/var/run/mysqld/mysqld.sock  
sort\_buffer\_size = 5M  
sysdate\_is\_now=1  
table\_open\_cache=700  
thread\_cache\_size=512  
tmp\_table\_size = 64M  
user=mysql

wsrep\_auto\_increment\_control=1  
wsrep\_causal\_reads=0  
wsrep\_certify\_nonPK=1  
wsrep\_cluster\_address=gcomm://10.0.2.142,10.0.2.141,10.0.2.161  
wsrep\_cluster\_name=“my\_wsrep\_cluster”  
wsrep\_convert\_LOCK\_to\_trx=0  
wsrep\_debug=0  
wsrep\_drupal\_282555\_workaround=0  
wsrep\_max\_ws\_rows=131072  
wsrep\_max\_ws\_size=1073741824  
wsrep\_node\_address=10.0.2.161  
wsrep\_node\_name=10.0.2.161  
wsrep\_provider\_options=“gcache.size=98304M; gmcast.segment=0”  
wsrep\_provider=/usr/lib/galera/libgalera\_smm.so  
wsrep\_replicate\_myisam=1  
wsrep\_retry\_autocommit=1  
wsrep\_slave\_threads=4  
wsrep\_sst\_auth=root:obfuscatepassword  
wsrep\_sst\_method=xtrabackup-v2

[mysql]

socket=/var/run/mysqld/mysqld.sock

[client]

socket=/var/run/mysqld/mysqld.sock

[mysqldump]

max\_allowed\_packet = 512M  
socket=/var/run/mysqld/mysqld.sock

[mysqld\_safe]

basedir=/usr/  
datadir=/opt/data  
log\_error=/var/log/mysql.log  
pid\_file=mysqld.pid

Only one of the other two nodes receives inserts at all, and only to one table. It is not exhibiting the problem. The my.cnf there is the same, except for wsrep\_node\_name. All three nodes used the same template for the my.cnf to start, with only the minimal changes required made locally on each node. I’m not sure we’d be so lucky as to find a smoking gun by finding differences in config.

This database began life on RDS and got migrated to this cluster. I came into the project after that and am trying to make the database more supportable. There were a lot of things present that could certainly provoke bugs. Mixture of MyISAM and InnoDB. Mix of UTF8 and latin1, sometimes in the same table…

Running values from the three nodes match what is expected:

node1\> show variables like ‘%auto\_increment%’;  
±-----------------------------±------+  
| Variable\_name | Value |  
±-----------------------------±------+  
| auto\_increment\_increment | 3 |  
| auto\_increment\_offset | 2 |  
| wsrep\_auto\_increment\_control | ON |  
±-----------------------------±------+

node2\> show variables like ‘%auto\_increment%’;  
±-----------------------------±------+  
| Variable\_name | Value |  
±-----------------------------±------+  
| auto\_increment\_increment | 3 |  
| auto\_increment\_offset | 3 |  
| wsrep\_auto\_increment\_control | ON |  
±-----------------------------±------+

node3\> show variables like ‘%auto\_increment%’;  
±-----------------------------±------+  
| Variable\_name | Value |  
±-----------------------------±------+  
| auto\_increment\_increment | 3 |  
| auto\_increment\_offset | 1 |  
| wsrep\_auto\_increment\_control | ON |  
±-----------------------------±------+

---

<div class="post-metadata">

**Author:** ![przemek](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/przemek/32/3_2.png) [@przemek](https://forums.percona.com/u/przemek)\
**Post date:** [December 19, 2016, 4:31pm UTC](https://forums.percona.com/t/undocumented-behavior-with-auto-increment-columns-in-a-cluster/5295/4 "2016-12-19T16:31:39Z")

</div>

The auto\_increment\_increment setting values seem correctly set on the nodes.  
Can you post an example (show create table…) of table that is always populated correctly (with increment of 3) and also table that is populated with increment of 1?  
Are you absolutely sure that those “wrong” tables are only written from single node? I assume none of the cluster nodes is replicating any data from an async master?

---

<div class="post-metadata">

**Author:** ![cmcgrail](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/cmcgrail/32/15_2.png) [@cmcgrail](https://forums.percona.com/u/cmcgrail)\
**Post date:** [December 20, 2016, 5:48pm UTC](https://forums.percona.com/t/undocumented-behavior-with-auto-increment-columns-in-a-cluster/5295/5 "2016-12-20T17:48:53Z")

</div>

Figured out what is going on. As noted above, this database began life as an RDS instance. Many of the tables were populated there and have not had any new data inserted since. That explains why the existing data in their auto\_increment columns increments by 1.

Increment by 3 is working on all tables that are currently receiving inserts. I got confused because we have two schemas with tables by the same name. As part of our release cycle, we were updating the schemas one by one, and I was looking at the wrong one. The one with recent inserts was behaving correctly. The other one hadn’t gotten an insert since the RDS days. Once it got the insert, it too shows the expected behavior.

Thanks for your help guys. One less mystery.