[MDEV-16304] Sequences with INCREMENT=0 produce wrong values with some combinations of START WITH and auto_increment_* options Created: 2018-05-27 Updated: 2018-08-30 Resolved: 2018-08-30 |
|
| Status: | Closed |
| Project: | MariaDB Server |
| Component/s: | Sequences |
| Affects Version/s: | 10.3.7 |
| Fix Version/s: | N/A |
| Type: | Bug | Priority: | Major |
| Reporter: | Juan | Assignee: | Michael Widenius |
| Resolution: | Not a Bug | Votes: | 0 |
| Labels: | None | ||
| Environment: |
CentOS Linux release 7.4.1708 (Core) |
||
| Description |
|
Sequences produce duplicate initial values in multiple nodes after restarting if sequence has been used on node 1 while node 2 was off-line. On a 2 node cluster with a sequence created with
as recommended in the documentation resulting (on node 1) in:
Now get one value from the sequence:
So in the case of this 2-node cluster, the server has reserved 999 numbers starting from next_not_cached_value + auto_increment_offset with an interval of auto_increment_increment, resulting in the next available non-cached value being 2100. If we now bring down the cluster (first node 2, then node 1), bootstrap node 1 again, we have a problem: Node 1:
For some reason, on restart in the bootstrap node the offset is not applied to the next_not_cached_value for the first invocation of "next value". now start
QED |
| Comments |
| Comment by Elena Stepanova [ 2018-05-28 ] | ||||||||||||||||||||||||||||||||||||||||
|
Sorry, I don't understand the nature of the problem here. For the result which you consider to be wrong, please indicate what result you would expect instead, and why. | ||||||||||||||||||||||||||||||||||||||||
| Comment by Juan [ 2018-05-29 ] | ||||||||||||||||||||||||||||||||||||||||
|
The confusion is understandable. A SQL sequence is not what is called a sequence in math. A SQL sequence is a series of unique numbers intended to be used as, and enhance, the concept of auto_increment. A 'sequence' in SQL as defined in ANSI SQL:2003 should return a list of unique numbers. Reference implementations such as Oracle and IBM also expect uniqueness. In the case described, the number 4100, which is the 'next value' returned for the sequence in node 1, is equal to the number 4100, which is the 'next value' returned for the sequence in node 2. In other words, multiple calls to 'next value' for the same sequence in multiple Galera nodes return the same, duplicate, value. ie. sequences don't work in Galera for MariaDB 10.3.7, as they produce duplicate numbers in the same cluster. So to be clear, it is wrong for 2 calls to 'next value' for the same sequence to return the same value. In the case of a Galera installation it is wrong for two calls to 'next value' for the same sequence from 2 nodes to produce the same value. | ||||||||||||||||||||||||||||||||||||||||
| Comment by Elena Stepanova [ 2018-05-29 ] | ||||||||||||||||||||||||||||||||||||||||
|
Thanks. Here are some examples where it raises from questionable to obviously wrong.
This is questionable. Should it really be 1, or should it be 2 (start value + offset)? It might seem okay that it's 1 at the first glance, but then
This seems logical, and here the offset is applied? So, it's inconsistent with the previous example. Further, if we have
Same case as with the first example above, again questionable, it might seem okay, but then
Now, this is obviously wrong. In terms of your rebootstrap scenario, it boils down to this.
but then, when you rebootstrap the cluster and start only one node, which means at this point it has auto_increment_increment=1 and auto_increment_offset=1, so this logic works instead
or, in other terms,
– no offset. Then you start the second node, which gets auto_increment_increment=2 and auto_increment_offset=2, so it works like this
All in all, the logic is flawed in the calculation of the initial offset, it's just less obvious when start with value is default. I've briefly looked into the place where it's calculated, there are many rather conflicting conditions and branches, so it's not surprising. | ||||||||||||||||||||||||||||||||||||||||
| Comment by Michael Widenius [ 2018-08-30 ] | ||||||||||||||||||||||||||||||||||||||||
|
First auto_increment_increment= X and auto_increment_offset= Y doesn't affect the initial value for a sequence, except if it's not part of the sequence that these two variables are generating. set global auto_increment_increment= 2, auto_increment_offset= 2; Is perfectly fine as 100 is part of the sequence 0,2,4,...98,100,102 and so one. The problem is probably, as Elena stated above, related to the new nodes doesn't have the variables set properly. | ||||||||||||||||||||||||||||||||||||||||
| Comment by Elena Stepanova [ 2018-08-30 ] | ||||||||||||||||||||||||||||||||||||||||
|
Given the above, I happily close the issue. |