Wildly different AUTO_INCREMENT values in the same table
Hello,
We recently installed a MySQL Cluster to use in our brand-new high-availabily infrastructure, and soon after we noticed a very strange behavior regarding the auto increment index of a certain table.
First of all, we know that each API node reserves its own ids for this purpose, our prefetch setting is at 1024 currently. To further clarify: we're not asking why we're getting ids like 3,4,1001,1002,5 etc. That's not the issue.
The issue is as follows:
We're using a BIGINT as the auto-increment id (because reasons), and the issue we've observed is that its value leaps around by whole orders of magnitude, apparently at random. For example, when we first set it up, the auto increment value, which was originally in the order of 2*10^17, changed to the order of 8*10^19.
There have also been leaps of millions, again at random. We may get an id like X and the next one two millions higher, with no apparent reason. We've tested using our software and from the mysql consoles on both API servers, obtaining the same bizarre results.
We've even observed values around 17*10^20, which is terrifyingly close to the maximum value of unsigned bigint fields.
Furthermore, "SELECT auto_increment FROM...", shows a value like 8***, with small differences between both API servers, which we could consider correct, if not for the fact that the value seems to leap upwards by millions on its own own volition. And then we have a few thousand values like 17*** that aren't even related to anything.
Has anyone got an idea what the hell is going on and/or how to prevent it?