Crafted ALTER TABLE statement can cause the connection hang and break the database

Bug Report

A crafted ALTER TABLE statement causes the database connection to hang indefinitely. The query cannot be aborted, and even the KILL statement fails to terminate it.

If restarting the TiDB instance, it will encounter a schema build error and fail to restart.

1. Minimal reproduce step (Required)

CREATE TABLE t (c INT) PARTITION BY RANGE (c) (PARTITION p VALUES LESS THAN (10));
ALTER TABLE t ADD COLUMN c2 INT AS (JSON_EXTRACT(c, '$.number')) VIRTUAL;

2. What did you expect to see? (Required)

Should not cause hang or break the database.

3. What did you see instead (Required)

bash # docker run -d --name some-tidb -p 4000:4000 -p 10080:10080 pingcap/tidb:nightly

bash # mysql -h127.0.0.1 -P4000 -uroot test

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE TABLE t (c INT) PARTITION BY RANGE (c) (PARTITION p VALUES LESS THAN (10));
Query OK, 0 rows affected (0.01 sec)

mysql> ALTER TABLE t ADD COLUMN c2 INT AS (JSON_EXTRACT(c, '$.number')) VIRTUAL;
^C^C -- query aborted
^C^C -- query aborted
^C^C -- query aborted

Attempting to restart the database fails — it's no longer connectable:

# docker container restart some-tidb

# mysql -h127.0.0.1 -P4000 -uroot test
ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 2

Docker logs output:

[2025/07/16 12:46:20.800 +00:00] [INFO] [tidb.go:93] ["new domain"] [store=30b4a02a-9400-40d1-96bc-19a9beedc02b] ["ddl lease"=45s] ["stats lease"=3s]
[2025/07/16 12:46:20.806 +00:00] [WARN] [controller.go:229] ["[resource group controller] server does not save config, load config failed"]
[2025/07/16 12:46:20.806 +00:00] [INFO] [controller.go:213] ["load resource controller config"] [config="{\"degraded-mode-wait-duration\":\"0s\",\"ltb-max-wait-duration\":\"30s\",\"ltb-token-rpc-max-delay\":\"1s\",\"request-unit\":{\"read-base-cost\":0.125,\"read-per-batch-base-cost\":0.5,\"read-cost-per-byte\":0.0000152587890625,\"write-base-cost\":1,\"write-per-batch-base-cost\":1,\"write-cost-per-byte\":0.0009765625,\"read-cpu-ms-cost\":0.3333333333333333},\"enable-controller-trace-log\":\"false\",\"token-rpc-params\":{\"wait-retry-interval\":\"50ms\",\"wait-retry-times\":20}}"] [ru-config="{\"ReadBaseCost\":0.125,\"ReadPerBatchBaseCost\":0.5,\"ReadBytesCost\":0.0000152587890625,\"WriteBaseCost\":1,\"WritePerBatchBaseCost\":1,\"WriteBytesCost\":0.0009765625,\"CPUMsCost\":0.3333333333333333,\"LTBMaxWaitDuration\":30000000000,\"WaitRetryInterval\":50000000,\"WaitRetryTimes\":20,\"DegradedModeWaitDuration\":0}"] [keyspace-id=4294967295]
[2025/07/16 12:46:20.808 +00:00] [INFO] [mock.go:113] ["owner manager is canceled"] [ownerKey=/tidb/ddl/fg/owner] [ID=716264a2-133f-4b89-beb2-b76e6740b19d]
[2025/07/16 12:46:20.808 +00:00] [INFO] [ddl.go:1050] ["DDL closed"] [category=ddl] [ID=716264a2-133f-4b89-beb2-b76e6740b19d] ["take time"=23.239µs]
[2025/07/16 12:46:20.808 +00:00] [INFO] [ddl.go:768] ["stop DDL"] [category=ddl] [ID=716264a2-133f-4b89-beb2-b76e6740b19d]
[2025/07/16 12:46:20.808 +00:00] [INFO] [domain.go:572] ["domain closed"] ["take time"=58.299µs]
[2025/07/16 12:46:20.808 +00:00] [ERROR] [tidb.go:114] ["init domain failed"] [category=ddl] [error="Build table `test`.`t` schema failed: [expression:3146]Invalid data type for JSON data in argument 1 to function json_extract; a JSON string or JSON type is required."]

4. What is your TiDB version? (Required)

Release Version: v9.0.0-beta.2.pre-118-g6ec4810
Edition: Community
Git Commit Hash: 6ec48101cae75529682a0f9fa8c16cbc42f246d3
Git Branch: HEAD
UTC Build Time: 2025-07-16 11:00:02
GoVersion: go1.23.11
Race Enabled: false
Check Table Before Drop: false
Store: unistore
Kernel Type: Classic

fix: close issue pingcap#62457 check first do not let db down

This will also break cloud

MySQL behavior

mysql> select version();
+-----------+
| version() |
+-----------+
| 9.1.0     |
+-----------+

-- succeed
drop table if exists t;
CREATE TABLE t (c INT) PARTITION BY RANGE (c) (PARTITION p VALUES LESS THAN (10));
ALTER TABLE t ADD COLUMN c2 INT AS (JSON_EXTRACT(c, '$.number')) VIRTUAL;

-- succeed
drop table if exists t;
CREATE TABLE t (c INT);
ALTER TABLE t ADD COLUMN c2 INT AS (JSON_EXTRACT(c, '$.number')) VIRTUAL;

-- succeed
drop table if exists t;
CREATE TABLE t (c INT, c2 INT AS (JSON_EXTRACT(c, '$.number')) VIRTUAL);

-- succeed
drop table if exists t;
CREATE TABLE t (c INT, c2 INT AS (JSON_EXTRACT(c, '$.number')) VIRTUAL) PARTITION BY RANGE (c) (PARTITION p VALUES LESS THAN (10));

TiDB

-- hang caused by load schema fail
drop table if exists t;
CREATE TABLE t (c INT) PARTITION BY RANGE (c) (PARTITION p VALUES LESS THAN (10));
ALTER TABLE t ADD COLUMN c2 INT AS (JSON_EXTRACT(c, '$.number')) VIRTUAL;

-- succeed
drop table if exists t;
CREATE TABLE t (c INT);
ALTER TABLE t ADD COLUMN c2 INT AS (JSON_EXTRACT(c, '$.number')) VIRTUAL;

-- succeed
drop table if exists t;
CREATE TABLE t (c INT, c2 INT AS (JSON_EXTRACT(c, '$.number')) VIRTUAL);

-- ERROR 3146 (22032): Invalid data type for JSON data in argument 1 to function json_extract; a JSON string or JSON type is required.
drop table if exists t;
CREATE TABLE t (c INT, c2 INT AS (JSON_EXTRACT(c, '$.number')) VIRTUAL) PARTITION BY RANGE (c) (PARTITION p VALUES LESS THAN (10));

When we build partition expr, we need to build expressions for all the public columns from table.

tidb/pkg/table/tables/partition.go

Lines 305 to 308 in b615180

    
           func newPartitionExpr(tblInfo *model.TableInfo, tp ast.PartitionType, expr string, partCols []ast.CIStr, defs []model.PartitionDefinition) (*PartitionExpr, error) { 
        
           	ctx := NewPartitionExprBuildCtx() 
        
           	dbName := ast.NewCIStr(ctx.GetEvalCtx().CurrentDB()) 
        
           	columns, names, err := expression.ColumnInfos2ColumnsAndNames(ctx, dbName, tblInfo.Name, tblInfo.Cols(), tblInfo)

Since c2 is not public when do the check during adding column, this operation can succeed. But we can't build table after it becomes public.

MySQL behavior

mysql> select version();
+-----------+
| version() |
+-----------+
| 9.1.0 |
+-----------+

-- succeed
drop table if exists t;
CREATE TABLE t (c INT) PARTITION BY RANGE (c) (PARTITION p VALUES LESS THAN (10));
ALTER TABLE t ADD COLUMN c2 INT AS (JSON_EXTRACT(c, '$.number')) VIRTUAL;

-- succeed
drop table if exists t;
CREATE TABLE t (c INT);
ALTER TABLE t ADD COLUMN c2 INT AS (JSON_EXTRACT(c, '$.number')) VIRTUAL;

-- succeed
drop table if exists t;
CREATE TABLE t (c INT, c2 INT AS (JSON_EXTRACT(c, '$.number')) VIRTUAL);

-- succeed
drop table if exists t;
CREATE TABLE t (c INT, c2 INT AS (JSON_EXTRACT(c, '$.number')) VIRTUAL) PARTITION BY RANGE (c) (PARTITION p VALUES LESS THAN (10));
TiDB

-- hang
drop table if exists t;
CREATE TABLE t (c INT) PARTITION BY RANGE (c) (PARTITION p VALUES LESS THAN (10));
ALTER TABLE t ADD COLUMN c2 INT AS (JSON_EXTRACT(c, '$.number')) VIRTUAL;

-- succeed
drop table if exists t;
CREATE TABLE t (c INT);
ALTER TABLE t ADD COLUMN c2 INT AS (JSON_EXTRACT(c, '$.number')) VIRTUAL;

-- succeed
drop table if exists t;
CREATE TABLE t (c INT, c2 INT AS (JSON_EXTRACT(c, '$.number')) VIRTUAL);

-- ERROR 3146 (22032): Invalid data type for JSON data in argument 1 to function json_extract; a JSON string or JSON type is required.
drop table if exists t;
CREATE TABLE t (c INT, c2 INT AS (JSON_EXTRACT(c, '$.number')) VIRTUAL) PARTITION BY RANGE (c) (PARTITION p VALUES LESS THAN (10));
When we build partition expr, we need to build expressions for all the public columns from table.

tidb/pkg/table/tables/partition.go

Lines 305 to 308 in b615180

func newPartitionExpr(tblInfo *model.TableInfo, tp ast.PartitionType, expr string, partCols []ast.CIStr, defs []model.PartitionDefinition) (*PartitionExpr, error) {
ctx := NewPartitionExprBuildCtx()
dbName := ast.NewCIStr(ctx.GetEvalCtx().CurrentDB())
columns, names, err := expression.ColumnInfos2ColumnsAndNames(ctx, dbName, tblInfo.Name, tblInfo.Cols(), tblInfo)
Since c2 is not public when do the check during adding column, this operation can succeed. But we can't build table after it becomes public.

learned it thank you

/severity major

@joechenrh

@joechenrh please help check affected versions

/label affects-8.5
/label affects-8.1
/label affects-7.5
/label affects-7.1
/label affects-6.5

6.1.7 is ok to restart TiDB after ALTER COLUMN

[2025/07/17 09:33:23.730 +00:00] [INFO] [main.go:343] ["disable Prometheus push client"]
[2025/07/17 09:33:23.730 +00:00] [INFO] [store.go:75] ["new store"] [path=unistore:///tmp/tidb]
[2025/07/17 09:33:23.730 +00:00] [INFO] [systime_mon.go:26] ["start system time monitor"]
[2025/07/17 09:33:23.745 +00:00] [INFO] [store.go:81] ["new store with retry success"]
[2025/07/17 09:33:23.746 +00:00] [INFO] [tidb.go:75] ["new domain"] [store=b07bbe0e-2e07-4c7b-9e39-8c403c81f3bd] ["ddl lease"=45s] ["stats lease"=3s] ["index usage sync lease"=0s]
[2025/07/17 09:33:23.751 +00:00] [INFO] [domain.go:176] ["full load InfoSchema success"] [currentSchemaVersion=0] [neededSchemaVersion=39] ["start time"=4.238833ms]
[2025/07/17 09:33:23.752 +00:00] [INFO] [domain.go:437] ["full load and reset schema validator"]
[2025/07/17 09:33:23.752 +00:00] [INFO] [ddl.go:382] ["[ddl] start DDL"] [ID=85aec550-fc8c-41a2-869f-bea93dc4307e] [runWorker=true]
[2025/07/17 09:33:23.752 +00:00] [INFO] [ddl.go:371] ["[ddl] start delRangeManager OK"] ["is a emulator"=true]
[2025/07/17 09:33:23.753 +00:00] [INFO] [ddl_worker.go:165] ["[ddl] start DDL worker"] [worker="worker 1, tp general"]
[2025/07/17 09:33:23.753 +00:00] [INFO] [delete_range.go:140] ["[ddl] start delRange emulator"]
[2025/07/17 09:33:23.753 +00:00] [INFO] [ddl_worker.go:165] ["[ddl] start DDL worker"] [worker="worker 2, tp add index"]
[2025/07/17 09:33:23.754 +00:00] [WARN] [sysvar_cache.go:52] ["sysvar cache is empty, triggering rebuild"]
[2025/07/17 09:33:23.756 +00:00] [WARN] [misc.go:448] ["Automatic TLS Certificate creation is disabled"] []
[2025/07/17 09:33:23.756 +00:00] [INFO] [server.go:253] ["server is running MySQL protocol"] [addr=0.0.0.0:4000]
[2025/07/17 09:33:23.757 +00:00] [INFO] [server.go:267] ["server is running MySQL protocol"] [socket=/tmp/tidb-4000.sock]
[2025/07/17 09:33:23.757 +00:00] [INFO] [http_status.go:87] ["for status and metrics report"] ["listening on addr"=0.0.0.0:10080]
[2025/07/17 09:33:23.757 +00:00] [INFO] [cpu.go:84] ["sql cpu collector started"]
[2025/07/17 09:33:23.757 +00:00] [INFO] [domain.go:1389] ["init stats info time"] ["take time"=1.02975ms]
[2025/07/17 09:33:25.753 +00:00] [INFO] [info.go:983] [SetTiFlashGroupConfig]

mysql> select version();
+--------------------+
| version()          |
+--------------------+
| 5.7.25-TiDB-v6.1.7 |
+--------------------+
1 row in set (0.00 sec)

mysql> show create table test.t;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t     | CREATE TABLE `t` (
  `c` int(11) DEFAULT NULL,
  `c2` int(11) GENERATED ALWAYS AS (json_extract(`c`, _utf8mb4'$.number')) VIRTUAL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
PARTITION BY RANGE (`c`)
(PARTITION `p` VALUES LESS THAN (10)) |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

@lance6716

@lance6716 @joechenrh do we need to figure a way if user happen to run this sql without patch, then start db again.

I checked if they happen to run it, and the db can not start again even if the patch

the patch only keeps users not run it, but if they already run it, it not fix it..

ddl: check when `updateVersionAndTableInfo` (#62470)

@lance6716 @joechenrh do we need to figure a way if user happen to run this sql without patch, then start db again.

I checked if they happen to run it, and the db can not start again even if the patch

the patch only keeps users not run it, but if they already run it, it not fix it..

Maybe it's hard to deal with such case since the table info stored is corrupted... If we want to make the db start again, we need to add patch to skip some check during parsing table info.

got it, thank you

checked quite some sql like this, there's no hang error after the patch

checked quite some sql like this, there's no hang error after the patch

Could you add those SQLs that will cause hang problem and can be fixed by the PR in the PR description? (or just paste it here, both is OK)

use test
drop table t;
create table t;
CREATE TABLE t (c INT) PARTITION BY RANGE (c) (PARTITION p VALUES LESS THAN (10));
ALTER TABLE t ADD COLUMN c2 INT AS (JSON_EXTRACT(c, '$.number')) VIRTUAL;
select version();
use test
CREATE TABLE t (c INT) PARTITION BY RANGE (c) (PARTITION p VALUES LESS THAN (10));
drop table t;
CREATE TABLE t (c INT) PARTITION BY RANGE (c) (PARTITION p VALUES LESS THAN (10));
ALTER TABLE t ADD COLUMN c2 INT AS (JSON_EXTRACT(c, '$.number')) VIRTUAL;
select version();
select * from t;
use test
select * from t;
drop table t;
select * from t;
drop table t;
ALTER TABLE t ADD COLUMN c2 INT AS (JSON_EXTRACT(c, '$.number')) VIRTUAL;
ALTER TABLE t ADD COLUMN c2 INT AS (JSON_EXTRACT(c, '$.number')) VIRTUAL;dad
drop table t;
dad drop table t;
drop database test;
create database test;
use test
CREATE TABLE t (c INT) PARTITION BY RANGE (c) (PARTITION p VALUES LESS THAN (10));
ALTER TABLE t ADD COLUMN c2 INT AS (JSON_EXTRACT(c, '$.number')) VIRTUAL;dad
ALTER TABLE t ADD COLUMN c2 INT AS (JSON_EXTRACT(c, '$.number')) VIRTUAL;
dad ALTER TABLE t ADD COLUMN c2 INT AS (JSON_EXTRACT(c, '$.number')) VIRTUAL;
ALTER TABLE t ADD COLUMN c2 INT AS (JSON_EXTRACT(c, '$.number')) VIRTUAL;
select * from t;
ALTER TABLE t ADD COLUMN c2 INT AS (JSON_EXTRACT(c, '$.number')) VIRTUAL;
use test
-- JSON_UNQUOTE要求JSON类型参数ALTER TABLE t ADD COLUMN c2 VARCHAR(100) AS (JSON_UNQUOTE(c)) VIRTUAL;
ALTER TABLE t ADD COLUMN c2 INT AS (JSON_EXTRACT(c, '$.id')) VIRTUAL;
ALTER TABLE t ADD COLUMN c2 DATE AS (CAST(c AS DATE)) VIRTUAL;
ALTER TABLE t ADD COLUMN c2 INT AS (YEAR(c)) VIRTUAL;ALTER TABLE t ADD COLUMN c2 INT AS (MONTH(c)) VIRTUAL;
ALTER TABLE t ADD COLUMN c2 VARCHAR(50) AS (JSON_UNQUOTE(JSON_EXTRACT(c, '$.name'))) VIRTUAL;
ALTER TABLE t ADD COLUMN c2 INT AS (non_exist_col + 1) VIRTUAL;
drop table t
;
CREATE TABLE t (    c INT) PARTITION BY RANGE (c) (    PARTITION p VALUES LESS THAN (10));
drop table t;
CREATE TABLE t (    c INT,    c2 INT AS (JSON_EXTRACT(c, '$.number')) VIRTUAL) PARTITION BY RANGE (c) (    PARTITION p VALUES LESS THAN (10));
drop table t;
CREATE TABLE t (c INT, c2 INT AS (c + 1) VIRTUAL) PARTITION BY RANGE (c) (PARTITION p VALUES LESS THAN (10));
ALTER TABLE t MODIFY COLUMN c2 INT AS (JSON_EXTRACT(c, '$.number')) VIRTUAL;
drop table t;
CREATE TABLE t (c VARCHAR(100));
ALTER TABLE t ADD COLUMN c2 INT AS (JSON_EXTRACT(CAST(c AS JSON), '$.number')) VIRTUAL;
ALTER TABLE t PARTITION BY RANGE (c2) (PARTITION p VALUES LESS THAN (10));
show warnings;
drop table t;
CREATE TABLE t (c INT);
ALTER TABLE t ADD COLUMN c2 INT AS (YEAR(c)) VIRTUAL;
ALTER TABLE t PARTITION BY RANGE (c2) (PARTITION p VALUES LESS THAN (10));
drop table t;
CREATE TABLE t (c INT, c2 INT AS (c + 1) VIRTUAL) PARTITION BY RANGE (c) (PARTITION p VALUES LESS THAN (10));
ALTER TABLE t MODIFY COLUMN c2 INT AS (JSON_EXTRACT(c, '$.number')) VIRTUAL;
CREATE TABLE t (c INT, c2 INT AS (c + 1) VIRTUAL) PARTITION BY RANGE (c) (PARTITION p VALUES LESS THAN (10));
drop table t;
CREATE TABLE t (c INT, c2 INT AS (c + 1) VIRTUAL) PARTITION BY RANGE (c) (PARTITION p VALUES LESS THAN (10));
ALTER TABLE t MODIFY COLUMN c2 INT AS (JSON_EXTRACT(c, '$.number')) VIRTUAL;

~/.mysql_history
some of my history
sorry some are wrong

ddl: check when `updateVersionAndTableInfo` (#62470) (#64763)

Navigation Menu

Repository navigation

Crafted `ALTER TABLE` statement can cause the connection hang and break the database #62457

Bug Report

1. Minimal reproduce step (Required)

2. What did you expect to see? (Required)

3. What did you see instead (Required)

4. What is your TiDB version? (Required)

5 remaining items

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

	func newPartitionExpr(tblInfo model.TableInfo, tp ast.PartitionType, expr string, partCols []ast.CIStr, defs []model.PartitionDefinition) (PartitionExpr, error) {
	ctx := NewPartitionExprBuildCtx()
	dbName := ast.NewCIStr(ctx.GetEvalCtx().CurrentDB())
	columns, names, err := expression.ColumnInfos2ColumnsAndNames(ctx, dbName, tblInfo.Name, tblInfo.Cols(), tblInfo)

Crafted ALTER TABLE statement can cause the connection hang and break the database #62457

Description

Bug Report

1. Minimal reproduce step (Required)

2. What did you expect to see? (Required)

3. What did you see instead (Required)

4. What is your TiDB version? (Required)

Activity

yihong0618 commented on Jul 17, 2025

joechenrh commented on Jul 17, 2025

yihong0618 commented on Jul 17, 2025

joechenrh commented on Jul 17, 2025

lance6716 commented on Jul 17, 2025

joechenrh commented on Jul 17, 2025

5 remaining items

joechenrh commented on Jul 17, 2025

yihong0618 commented on Jul 17, 2025

joechenrh commented on Jul 17, 2025

yihong0618 commented on Jul 17, 2025

yihong0618 commented on Jul 18, 2025

joechenrh commented on Jul 18, 2025

yihong0618 commented on Jul 18, 2025

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions

Crafted `ALTER TABLE` statement can cause the connection hang and break the database #62457