SHARD_ROW_ID_BITS会影响自增id的取值范围,是否是一个BUG

北京大爷 · 2021 年3 月 31 日 11:31

应该和这个已知问题类似

`alter table t auto_increment=xxx` doesn't work properly

已打开 01:47PM - 27 May 20 UTC

type/bug sig/sql-infra severity/minor

## Bug Report Please answer these questions before submitting your issue. Tha…nks! ### 1. Minimal reproduce step (Required) Reproduced on pd/tikv/tidb (3/3/3) cluster ``` > drop table if exists t; > create table t (a int) shard_row_id_bits = 5; > alter table t auto_increment = 72057594037927936; > insert into t set a=1; > alter table t shard_row_id_bits = 10; Error 1467: shard_row_id_bits 10 will cause next global auto ID 72057594039457405 overflow" -- The error information should show 72057594037927936 mysql root@172.16.4.61:test> create table t1 (a int auto_increment primary key); Query OK, 0 rows affected Time: 0.563s mysql root@172.16.4.61:test> insert into t1 values() Query OK, 1 row affected Time: 0.060s mysql root@172.16.4.61:test> alter table t1 auto_increment = 10000 You're about to run a destructive command. Do you want to proceed? (y/n): y Your call! Query OK, 0 rows affected Time: 1.059s mysql root@172.16.4.61:test> insert into t1 values() Query OK, 1 row affected Time: 0.057s mysql root@172.16.4.61:test> select * from t1 +-------+ | a | +-------+ | 1 | | 93497 | +-------+ 2 rows in set Time: 0.063s ``` ### 2. What did you expect to see? (Required) ``` > alter table t shard_row_id_bits = 10; Error 1467: shard_row_id_bits 10 will cause next global auto ID 72057594039457405 overflow" > select * from t1; +-------+ | a | +-------+ | 1 | | 10000 | +-------+ ``` ### 3. What did you see instead (Required) ``` > alter table t shard_row_id_bits = 10; Error 1467: shard_row_id_bits 10 will cause next global auto ID 72057594037927936 overflow" > select * from t1; +-------+ | a | +-------+ | 1 | | 93497 | +-------+ ``` ### 4. Affected version (Required) ``` mysql root@172.16.4.103:(none)> select tidb_version() +-------------------------------------------------------------------+ | tidb_version() | +-------------------------------------------------------------------+ | Release Version: v4.0.0-rc.2-41-g689a6b6 | | Edition: Community | | Git Commit Hash: 689a6b6439ae7835947fcaccf329a3fc303986cb | | Git Branch: release-4.0 | | UTC Build Time: 2020-05-27 11:07:16 | | GoVersion: go1.13.10 | | Race Enabled: false | | TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306 | | Check Table Before Drop: false | +-------------------------------------------------------------------+ ``` ### 5. Root Cause Analysis

如果目前有 block 点可以通过
alter table tablename SHARD_ROW_ID_BITS=0 来绕过
本身如果主键是 int 或者 bigint 类型时候 SHARD_ROW_ID_BITS 是并不生效的