Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

autoid: support system variables auto_increment_increment and auto_increment_offset #14245

Closed
bb7133 opened this issue Dec 26, 2019 · 0 comments · Fixed by #14301
Closed

autoid: support system variables auto_increment_increment and auto_increment_offset #14245

bb7133 opened this issue Dec 26, 2019 · 0 comments · Fixed by #14301
Assignees
Labels
type/enhancement The issue or PR belongs to an enhancement.

Comments

@bb7133
Copy link
Member

bb7133 commented Dec 26, 2019

Feature Request

Is your feature request related to a problem? Please describe:
The system variable auto_increment_increment and auto_increment_offset are useful in some scenarios. For example, when the users want to do a Master-Master(bidirectional) replication with auto_increment columns, duplicate-key conflict and be avoided by setting different variable values in the clusters, for example:

Cluster 1:

mysql> create table t(a int key auto_increment);
mysql> set @@auto_increment_offset = 1
mysql> set @@auto_increment_increment = 2
mysql> insert into t values (...)
# the sequence of a will be 1, 3, 5, 7...

Cluster 2:

mysql> create table t(a int key auto_increment);
mysql> set @@auto_increment_offset = 2
mysql> set @@auto_increment_increment = 2
mysql> insert into t values (...)
# the sequence of a will be 2, 4, 6, 8...

For now, TiDB defines those 2 variables: users can set them but they don't actually take effect. We should make them work for those users who need them.

Describe alternatives you've considered:
The main implementation in MySQL Server can be found here:

https://github.com/mysql/mysql-server/blob/91a17cedb1ee880fe7915fb14cfd74c04e8d6588/sql/handler.cc#L3431

Basically, it made a rounding and then calculate next ID from the following formula:

nr = nr * auto_increment_increment + auto_increment_offset

The result can be guaranteed to be a multiple of auto_increment_increment offset with auto_increment_offset.

We can port this formula to TiDB's ID allocator:

func (alloc *allocator) Alloc(tableID int64, n uint64) (int64, int64, error) {

Somethings we may need to consider:

  • The formula applies to the cached values only. The allocation of the next step from KV is not affected.
  • The allocation of rowid should not be affected.
  • The rebase behavior from explicit value insertion or ALTER TABLE ... AUTO_INCREMENT should not break the constraint of these 2 variables: looks it should be fine, the next value is calculated instantly by the formula above.

Some manual tests have been done from a raw prototype: bb7133@adb83e3.

Teachability, Documentation, Adoption, Migration Strategy:

This is simply missing compatibility feature, seems we don't need to care them.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
type/enhancement The issue or PR belongs to an enhancement.
Projects
None yet
Development

Successfully merging a pull request may close this issue.

2 participants