Can the auto_id_cache of auto_increment be modified to 1 after table creation to use MySQL compatibility mode?

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: auto_increment 的 auto_id_cache 能否在建表后修改为 1 以使用 mysql 兼容模式

| username: Qiuchi

Can the auto_id_cache of auto_increment be modified after the table is created?

| username: WalterWj | Original post link

Why not test it?

| username: tidb菜鸟一只 | Original post link

Supported

ALTER TABLE t_cache AUTO_ID_CACHE 100;

| username: Qiuchi | Original post link

Actually, I want to change it to MySQL compatibility mode, which is

alter table ${tab_name} AUTO_ID_CACHE 1;

But it reports this error. Does this mean that the mode cannot be changed after the table is created?

[HY000][1105] Can’t Alter AUTO_ID_CACHE between 1 and non-1, the underlying implementation is different

| username: tidb菜鸟一只 | Original post link

My version 5.4.3 can be modified to 1.
image
Are you using version 6.5? The prompt suggests it cannot be modified to 1?

| username: Qiuchi | Original post link

It is 6.5, but this prompt is not very certain whether it is a version issue or a problem with other configurations of the table.

I found that if you set auto_id_cache = 1 when creating the table, you cannot change it to a value greater than 1, and you will get the same error.

create table "test_tab"
(
    "id" bigint(20) not null auto_increment,
    primary key ("id") /*T![clustered_index] CLUSTERED */
) engine = InnoDB
  default charset = utf8mb4
  collate = utf8mb4_bin
  auto_id_cache = 1;

alter table test_tab auto_id_cache 1000;
| username: tidb菜鸟一只 | Original post link

I tested it on version 6.5.0, and indeed, when creating a table, auto_id_cache = 1 can no longer be modified to other values. It should be due to this TiDB 6.5.0 Release Notes | PingCAP Documentation Center. After changing it to 1, it is compatible with MySQL, but it is not allowed to change to other values anymore.

| username: Qiuchi | Original post link

Oh, it’s frustrating that this can’t be changed. The table is quite large.

| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.