# Autoincrement with XtraDB-Cluster

**URL:** <https://forums.percona.com/t/autoincrement-with-xtradb-cluster/2887>\
**Category:** Percona XtraDB Cluster 5.x\
**Created:** [August 18, 2013, 8:44pm UTC](https://forums.percona.com/t/autoincrement-with-xtradb-cluster/2887 "2013-08-18T20:44:09Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![MCProHosting-Matt](https://avatars.discourse-cdn.com/v4/letter/m/ad7895/32.png) [@MCProHosting-Matt](https://forums.percona.com/u/MCProHosting-Matt)\
**Post date:** [August 18, 2013, 8:44pm UTC](https://forums.percona.com/t/autoincrement-with-xtradb-cluster/2887/1 "2013-08-18T20:44:09Z")

</div>

I’m brand new to database clusters (this is my first one), I just fired everything up yesterday and I began testing.

I have 3 database nodes all running Percona-XtraDB-Cluster with an HAProxy load balancer, after doing some research it appears that there may be some issues with using auto\_increment. The problem is we don’t have a choice since we are using pre-packaged applications such as WHMCS and Multicraft.

After doing some testing I have found that auto-increments are skipping 2, which will cause problems with our application.

Example:  
| 1093 | Node 1 write |  
| 1096 | Node 1 write |  
| 1099 | Node 1 write |  
| 1102 | Node 1 write |  
| 1105 | Node 1 write |  
| 1108 | Node 1 write |  
| 1111 | Node 1 write |  
| 1114 | Node 1 write |  
| 1117 | Node 1 write |  
| 1120 | Node 1 write |  
| 1123 | Node 1 write |  
| 1126 | Node 1 write |  
| 1128 | direct write |  
| 1131 | direct write 2 |

Node 1 writes are writes from an automated PHP script running a while loop with a 0.5 second wait in between.  
Direct writes are simply insert statements ran on the console for the master node.

What can we do about this?

---

<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:** [August 20, 2013, 6:30am UTC](https://forums.percona.com/t/autoincrement-with-xtradb-cluster/2887/2 "2013-08-20T06:30:24Z")

</div>

If I understand correctly, gaps in auto increment values are problematic for your applications? This behaviour is by purpose and default in Percona XtraDB Cluster. It’s in order to avoid conflicts when you do inserts on many nodes simultaneously. With different auto increment ranges reserved for each node - there is no such problem.  
You can find more details here:  
[url][Percona XtraDB Cluster](http://www.percona.com/doc/percona-xtradb-cluster/wsrep-system-index.html#wsrep_auto_increment_control%5B/url%5D)

You can change this behaviour and make insert values in auto-increment column sequentially (as default in normal MySQL), but if would be best if you do writes on one node only at the time.

---

<div class="post-metadata">

**Author:** ![fonsecacassio](https://avatars.discourse-cdn.com/v4/letter/f/7cd45c/32.png) [@fonsecacassio](https://forums.percona.com/u/fonsecacassio)\
**Post date:** [January 7, 2014, 11:03am UTC](https://forums.percona.com/t/autoincrement-with-xtradb-cluster/2887/3 "2014-01-07T11:03:25Z")

</div>

Hello,

And as you would to change the behavior of the auto-increment sequentially for?

I’ve tried putting the variables in my.cnf but it did not.

---

<div class="post-metadata">

**Author:** ![mirfan](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/mirfan/32/16890_2.png) [@mirfan](https://forums.percona.com/u/mirfan)\
**Post date:** [January 9, 2014, 7:06am UTC](https://forums.percona.com/t/autoincrement-with-xtradb-cluster/2887/4 "2014-01-09T07:06:46Z")

</div>

Did you set it off on all nodes of PXC ? As it works only when you set wsrep\_auto\_increment\_control to OFF to entire cluster nodes.

---

<div class="post-metadata">

**Author:** ![fonsecacassio](https://avatars.discourse-cdn.com/v4/letter/f/7cd45c/32.png) [@fonsecacassio](https://forums.percona.com/u/fonsecacassio)\
**Post date:** [January 9, 2014, 11:10am UTC](https://forums.percona.com/t/autoincrement-with-xtradb-cluster/2887/5 "2014-01-09T11:10:14Z")

</div>

Thanks,

really lacked only disable variable wsrep\_auto\_increment\_control.