WL#8445: Group Replication: Auto-increment configuration/handling

This WL will provide correct setup of auto_increment variables
(auto_increment_increment and auto_increment_offset) value on
each member, so that different members don't generate
a duplicate auto_increment value.


PROBLEM STATEMENT
=================
When all members in a group have same default values for auto_increment
variables, it will generate duplicate auto_increment values resulting in
negative certification and rollback (except one where it will get committed),
when more then one member try to insert at same instant.

FR1: No member will generate duplicate AUTO_INCREMENT column value, when size of 
the group is not greater than GROUP_REPLICATION_AUTO_INCREMENT_INCREMENT, even 
when any member join/leave the group.

FR2: User can always override these automatic generated values for 
auto_increment_increment and auto_increment_offset.

FR3: These feature will only get enable when user has not set any of these 
auto_increment variables.

OVERVIEW
========
MySQL server already provides two auto-increment variables 
(auto_increment_increment and auto_increment_offset) which can be used to set 
generate different auto_increment values on each member.
MySQL circular (5.0.2 onwards) replication already use this approach.

Calculation of next value for AUTO_INCREMENT column is dependent on two 
variables:
auto_increment_increment and auto_increment_offset.

And next auto-increment value is calculated in mysql server as follows:

1. if no records present currently in table
      nr = 0 // nr is next auto_increment value to be calculated
   else
      nr = maximum auto-inc present in table
2. if auto_increment_increment == 1
      nr = nr + 1
   else
      nr = (nr + auto_increment_increment - auto_increment_offset) / 
auto_increment_increment;
      nr = nr * auto_increment_increment + auto_increment_offset;

where,
auto_increment_increment: controls the interval between successive column 
values. The default value is 1.
auto_increment_offset: determines the starting point for the AUTO_INCREMENT 
column value. The default value is 1.

Example:
auto_increment_increment: 3 (size of group)
auto_increment_offset: server_id (M1 = 1, M2 = 2, M3 = 3)

======================================================================
server|
======================================================================
 M1   |   1                          7              10
 ---------------------------------------------------------------------
 M2   |        2            5                             11
 ---------------------------------------------------------------------
 M3   |            3                           9                12
======================================================================
          1    2   3  *4*   5   *6*  7  *8*    9    10    11    12  (Values 
inserted. Gaps surrounded with *)

Note: Before this worklog there was no provision available to automate setting 
of different auto_increment_increment and auto_increment_offset values on each 
     
member of master-master replication.


CHALLENGES
==========
member joins/leave will change auto_increment_increment
=======================================================
If we set auto_increment_increment as size of group, then for any view change 
i.e. member joins or leaves, we have to update auto_increment_increment value as 
well. But the update in auto_increment_increment value can still generate 
duplicate values on different members.

Example:
auto_increment_increment: 3 (size of group)
auto_increment_offset: server_id (M1 = 1, M2 = 2, M3 = 3)

======================================================================
server|
======================================================================
 M1   |   1            4
 ---------------------------------------------------------------------
 M2   |        2
 ---------------------------------------------------------------------
 M3   |            3
 ---------------------------------------------------------------------
 M4   |                4
======================================================================
          1    2   3  *4*          (Values inserted. Duplicate value surrounded 
with *)

Suppose we have three members in group and fourth member joins so view_change 
will trigger update in auto_increment_increment to 4. So before this change 
occurred M1 had new insert which generated next auto_incremented value of 4 and 
then M4 gets in with auto_increment value starting with 4, resulting in 
duplicate value generation.

RESOLUTION
==========
So to avoid such issues of duplicate value generation because of view changes,
we can use some fixed value for auto_increment_increment. We will call this
auto_increment_increment value as GROUP_REPLICATION_AUTO_INCREMENT_INCREMENT 
thereafter.


But few question will arise if we use such an approach as of:

What will happen if group goes above GROUP_REPLICATION_AUTO_INCREMENT_INCREMENT?
- This WL will only resolve duplicate key generation issue if total members in 
group are less than GROUP_REPLICATION_AUTO_INCREMENT_INCREMENT. For situations 
where the number of members is expected to be greater than 
GROUP_REPLICATION_AUTO_INCREMENT_INCREMENT, the user can opt for a manual 
configuration of this option.

What is value of GROUP_REPLICATION_AUTO_INCREMENT_INCREMENT?
- This constant value determines the value to be set for 
auto_increment_increment. The default value for this constant is choosen as 7, 
as a valid compromise taking into account the maximum usable values for each 
possible auto_increment_increment and what is a normal group expected size.

Table below shows worst case scenario of maximum usable value for different 
auto_increment_increment.
--------------------------------
auto_inc |   #values | %usable |
--------------------------------
1        |4294967295 | 100.00% |
3        |1431655765 | 33.33%  |
5        |858993459  | 20.00%  |
7        |613566756  | 14.29%  |
9        |477218588  | 11.11%  |
11       |390451572  | 9.09%   |
13       |330382100  | 7.69%   |
15       |286331153  | 6.67%   |
17       |252645135  | 5.88%   |
19       |226050910  | 5.26%   |
--------------------------------

Summary of changes:
===================
- Whenever joiner joins group and it finds that default values are set for both 
auto_increment_increment and auto_increment_offset variables and group contains  
more than one member, then it sets auto_increment_increment as  
GROUP_REPLICATION_AUTO_INCREMENT_INCREMENT and auto_increment_offset as server_id.

- This feature will only avoid duplicate auto_increment column values when total 
number of members in group is less than equal to 
GROUP_REPLICATION_AUTO_INCREMENT_INCREMENT. So all the nodes added after   
GROUP_REPLICATION_AUTO_INCREMENT_INCREMENT will have duplicate auto-increment 
column values, unless the user manually changes this.

- Whenever number of member increases more then 
GROUP_REPLICATION_AUTO_INCREMENT_INCREMENT, then it will log a error.


Server Core Changes:
====================

1. new function to get server server_id.
2. new function to get/set auto_increment_increment and auto_increment_offset.

Group Replication Changes:
==========================
1. Introduce a new plugin option GROUP_REPLICATION_AUTO_INCREMENT_INCREMENT with 
default value 7.
2. When a view is delivered:
2.1. Get server_id and auto_increment_increment and auto_increment_offset from
     new function defined above in server core changes
2.2  Check on joining node whether auto_increment_increment and 
auto_increment_offset are set to default i.e. 1 and size of group greater than 1
       if above conditions are true
       set
         auto_increment_increment= GROUP_REPLICATION_AUTO_INCREMENT_INCREMENT;
         auto_increment_offset= server_id;
2.3. Log a plugin error on all members of the group, if group size is greater than      
GROUP_REPLICATION_AUTO_INCREMENT_INCREMENT.