由3.0.4升级4.0.12后 分区表添加失败

定时维护分区程序,在升级前后都没有变化。会定时执行创建和删除分区任务。
ALTER TABLE kfk_tbl_pt ADD PARTITION (PARTITION p20210422 VALUES LESS THAN ( UNIX_TIMESTAMP(‘2021-04-23 00:00:00’) ));
在升级后会报错 Unknown column ‘2021-04-23 00:00:00’ in ‘expression’,但是单独执行select UNIX_TIMESTAMP(‘2021-04-23 00:00:00’) 是不会报错的。
且原来在navicat中管理非常正常,现在在navicat中不会显示表,这是设置导致的问题还是升级时哪里出现了故障?

1.麻烦核实下在升级前后有调整过什么参数;
2. Navicat 中不显示大写库名下的表应该是一个已知问题,可以参考下这个帖子:

是所有的表名都不显示,不是大写的表名,我们的数据库中有大写的表名和小写的表名但是都不显示。升级前后只对rack,host进行了调整,其他并未做过任何调整。在有11.2.7版本的navicat中可以显示出表名,但是表注释这些是没有的,通过information schema是可以查到的。

1.麻烦反馈下具体的表结构信息,另外如果在创建分区时 tidb.log 中有报错的话也请提供下相关日志;
2.在小写库名中 navicat 也无法显示表名吗?

对,在navicat12版本后,大写小写的表名都无法显示,添加分区的时 tidb.log中没有报错,而且如果不使用UNIX_TIMESTAMP函数,而是先select UNIX_TIMESTAM(‘2021-04-23 00:00:00’)得到结果后,再执行ALTER TABLE kfk_tbl_pt ADD PARTITION (PARTITION p20210422 VALUES LESS THAN ( 1619136000 )); 是可以正常执行并有正确结果的,我们担心是不是升级造成了一些不稳定。

1.表名在 navicat 没有正确显示估计和上面链接里是同一类问题,可以等修复后再看下效果;
2.创建分区报错的这个问题麻烦提供一下表结构信息,我们这边尝试复现下,多谢。

create TABLE “TBL_KFK_VEHICLE_TEST” (
“SourceId” varchar(64) DEFAULT NULL,
“DeviceId” varchar(64) DEFAULT NULL,
“PassTime” timestamp NULL DEFAULT NULL,
“channelId” varchar(64) DEFAULT NULL,
“address” varchar(255) DEFAULT NULL,
KEY “DeviceId” (“DeviceId”)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
PARTITION BY RANGE ( unix_timestamp(passtime))(
PARTITION p20210429 VALUES LESS THAN ( UNIX_TIMESTAMP(‘2021-04-30 00:00:00’) ),
PARTITION p20210430 VALUES LESS THAN ( UNIX_TIMESTAMP(‘2021-05-01 00:00:00’) ),
PARTITION p20210501 VALUES LESS THAN ( UNIX_TIMESTAMP(‘2021-05-02 00:00:00’) )
)
基本是这样的建表语句,但是目前执行这个建表语句都会报错
1054 - Unknow column ‘2021-04-30 00:00:00’ in ‘expression’
而且我们的数据库是从3.0.4升级到4.0.12的,您所说的大小显示不了表应该是5.0出现的bug吧。我们现在升级后出现了这种问题,是不是哪里发生了什么不可见的故障,是不是我们的集群面临什么风险。

上面这个建表语句我执行了下并没有问题,麻烦再检查下:

MySQL [lzy]> select version();               
+---------------------+
| version()           |
+---------------------+
| 5.7.25-TiDB-v4.0.12 |
+---------------------+
1 row in set (0.00 sec)

MySQL [lzy]> create TABLE TBL_KFK_VEHICLE_TEST (
    -> SourceId varchar(64) DEFAULT NULL,
    -> DeviceId varchar(64) DEFAULT NULL,
    -> PassTime timestamp NULL DEFAULT NULL,
    -> channelId varchar(64) DEFAULT NULL,
    -> address varchar(255) DEFAULT NULL,
    -> KEY DeviceId (DeviceId)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
    -> PARTITION BY RANGE (unix_timestamp(passtime))(
    -> PARTITION p20210429 VALUES LESS THAN ( UNIX_TIMESTAMP('2021-04-30 00:00:00') ),
    -> PARTITION p20210430 VALUES LESS THAN ( UNIX_TIMESTAMP('2021-05-01 00:00:00') ),
    -> PARTITION p20210501 VALUES LESS THAN ( UNIX_TIMESTAMP('2021-05-02 00:00:00') )
    -> );
Query OK, 0 rows affected (0.10 sec)

正式因为正常执行这个语句应该是没问题的,而我们的tidb集群在升级后出现了这个执行异常,所以很奇怪。select version()的打印结果和你的也是完全一致的。我们有6个TIDB的DB节点,都出现了一致的问题,但是问题又是在完成DB节点升级后出现的(在PD,KV节点升级期间,还一切正常)。怕就怕这种不可复现的问题,我们要如何排查是哪里出了问题呢?

麻烦提供下集群当前的配置文件(tiup cluster edit-config {cluster-name} 可以看到)以及上面 SQL 报错时的 tidb.log

找到报错日志了,之前负载均衡到其他节点上了,没注意
[2021/04/28 17:22:57.327 +08:00] [INFO] [coprocessor.go:1034] ["[TIME_COP_PROCESS] resp_time:467.598371ms txnStartTS:424568887915053071 region_id:29830091 store_addr:10.120.18.10:20171 kv_process_ms:442 scan_total_write:93506 scan_processed_write:93505 scan_total_data:93505 scan_processed_data:93505 scan_total_lock:1 scan_processed_lock:0"] [conn=1265]
[2021/04/28 17:22:57.372 +08:00] [INFO] [session.go:2397] [“CRUCIAL OPERATION”] [conn=1300] [schemaVersion=8585] [cur_db=KFKMount] [sql=“alter table TBL_KFK_VEHICLE_PT add PARTITION ( PARTITION p20210507 VALUES LESS THAN (unix_timestamp( ‘2021-05-08 00:00:00’ )))”] [user=root@10.120.18.232]
[2021/04/28 17:22:57.388 +08:00] [INFO] [tidb.go:219] [“rollbackTxn for ddl/autocommit failed”]
[2021/04/28 17:22:57.389 +08:00] [WARN] [session.go:1383] [“run statement failed”] [conn=1300] [schemaVersion=8585] [error="[expression:1054]Unknown column ‘2021-05-08 00:00:00’ in ‘expression’"] [session="{\ “currDBName”: “KFKMount”,\ “id”: 1300,\ “status”: 2,\ “strictMode”: false,\ “user”: {\ “Username”: “root”,\ “Hostname”: “10.120.18.232”,\ “CurrentUser”: false,\ “AuthUsername”: “root”,\ “AuthHostname”: “%”\ }\ }"]
[2021/04/28 17:22:57.389 +08:00] [INFO] [conn.go:797] [“command dispatched failed”] [conn=1300] [connInfo=“id:1300, addr:10.120.18.232:62636 status:10, collation:utf8_general_ci, user:root”] [command=Query] [status=“inTxn:0, autocommit:1”] [sql=“alter table TBL_KFK_VEHICLE_PT add PARTITION ( PARTITION p20210507 VALUES LESS THAN (unix_timestamp( ‘2021-05-08 00:00:00’ )))”] [txn_mode=OPTIMISTIC] [err="[expression:1054]Unknown column ‘2021-05-08 00:00:00’ in ‘expression’\ngithub.com/pingcap/errors.AddStack\ \t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/pkg/mod/github.com/pingcap/errors@v0.11.5-0.20201126102027-b0a155152ca3/errors.go:174\ github.com/pingcap/errors.(*Error).GenWithStackByArgs\ \t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/pkg/mod/github.com/pingcap/errors@v0.11.5-0.20201126102027-b0a155152ca3/normalize.go:156\ngithub.com/pingcap/tidb/expression.(*simpleRewriter).rewriteColumn\ \t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/expression/simple_rewriter.go:200\ngithub.com/pingcap/tidb/expression.(*simpleRewriter).Leave\ \t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/expression/simple_rewriter.go:210\ngithub.com/pingcap/parser/ast.(*ColumnNameExpr).Accept\ \t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/pkg/mod/github.com/pingcap/parser@v0.0.0-20210303062609-d1d977c9ceed/ast/expressions.go:593\ngithub.com/pingcap/parser/ast.(*FuncCallExpr).Accept\ \t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/pkg/mod/github.com/pingcap/parser@v0.0.0-20210303062609-d1d977c9ceed/ast/functions.go:496\ngithub.com/pingcap/tidb/expression.RewriteSimpleExprWithTableInfo\ \t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/expression/simple_rewriter.go:83\ github.com/pingcap/tidb/expression.ParseSimpleExprWithTableInfo\ \t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/expression/simple_rewriter.go:64\ github.com/pingcap/tidb/ddl.getRangeValue\ \t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/ddl/partition.go:769\ github.com/pingcap/tidb/ddl.checkCreatePartitionValue\ \t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/ddl/partition.go:716\ github.com/pingcap/tidb/ddl.checkPartitionByRange\ \t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/ddl/ddl_api.go:1735\ github.com/pingcap/tidb/ddl.(*ddl).AddTablePartitions\ \t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/ddl/ddl_api.go:2483\ngithub.com/pingcap/tidb/ddl.(*ddl).AlterTable\ \t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/ddl/ddl_api.go:2092\ngithub.com/pingcap/tidb/executor.(*DDLExec).executeAlterTable\ \t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/executor/ddl.go:366\ngithub.com/pingcap/tidb/executor.(*DDLExec).Next\ \t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/executor/ddl.go:86\ngithub.com/pingcap/tidb/executor.Next\ \t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/executor/executor.go:262\ github.com/pingcap/tidb/executor.(*ExecStmt).handleNoDelayExecutor\ \t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/executor/adapter.go:531\ngithub.com/pingcap/tidb/executor.(*ExecStmt).handleNoDelay\ \t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/executor/adapter.go:413\ngithub.com/pingcap/tidb/executor.(*ExecStmt).Exec\ \t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/executor/adapter.go:366\ngithub.com/pingcap/tidb/session.runStmt\ \t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/session/tidb.go:308\ github.com/pingcap/tidb/session.(*session).ExecuteStmt\ \t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/session/session.go:1380\ngithub.com/pingcap/tidb/server.(*TiDBContext).ExecuteStmt\ \t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/server/driver_tidb.go:262\ngithub.com/pingcap/tidb/server.(*clientConn).handleStmt\ \t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/server/conn.go:1430\ngithub.com/pingcap/tidb/server.(*clientConn).handleQuery\ \t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/server/conn.go:1419\ngithub.com/pingcap/tidb/server.(*clientConn).dispatch\ \t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/server/conn.go:1009\ngithub.com/pingcap/tidb/server.(*clientConn).Run\ \t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/server/conn.go:782\ngithub.com/pingcap/tidb/server.(*Server).onConn\ \t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/server/server.go:449\ runtime.goexit\ \t/usr/local/go/src/runtime/asm_amd64.s:1357"]
[2021/04/28 17:22:57.581 +08:00] [INFO] [coprocessor.go:1034] ["[TIME_COP_PROCESS] resp_time:320.504832ms txnStartTS:424568887901945868 region_id:36851581 store_addr:10.120.18.5:20171 kv_process_ms:319 scan_total_write:97327 scan_processed_write:97326 scan_total_data:97326 scan_processed_data:97326 scan_total_lock:1 scan_processed_lock:0"] [conn=1266]

目前执行 alter table TBL_KFK_VEHICLE_PT add PARTITION ( PARTITION p20210507 VALUES LESS THAN (unix_timestamp( ‘2021-05-08 00:00:00’ ))) 还是这个报错吗?

是的,还是报这个错。

好的,我们先查一下,会尽快反馈,多谢。

方便取一下表的 meta 信息不?
http://127.0.0.1:10080/schema/${DB}/TBL_KFK_VEHICLE_TEST

${DB} 换成数据库名,拿到一个 json 文件

现在是我上面建表语句也会报这个错,建表的时候就报错了。
全文太多了,删掉了一些老的分区信息。保留了最后几条
{
“id”:2973,
“name”:{
“O”:“TBL_KFK_VEHICLE_TEST”,
“L”:“tbl_kfk_vehicle_test”
},
“charset”:“utf8mb4”,
“collate”:“utf8mb4_bin”,
“cols”:[
{
“id”:1,
“name”:{
“O”:“SourceID”,
“L”:“sourceid”
},
“offset”:0,
“origin_default”:null,
“origin_default_bit”:null,
“default”:null,
“default_bit”:null,
“default_is_expr”:false,
“generated_expr_string”:"",
“generated_stored”:false,
“dependences”:null,
“type”:{
“Tp”:15,
“Flag”:0,
“Flen”:64,
“Decimal”:0,
“Charset”:“utf8mb4”,
“Collate”:“utf8mb4_bin”,
“Elems”:null
},
“state”:5,
“comment”:“SourceID”,
“hidden”:false,
“version”:2
},
{
“id”:2,
“name”:{
“O”:“TransportID”,
“L”:“transportid”
},
“offset”:1,
“origin_default”:null,
“origin_default_bit”:null,
“default”:null,
“default_bit”:null,
“default_is_expr”:false,
“generated_expr_string”:"",
“generated_stored”:false,
“dependences”:null,
“type”:{
“Tp”:15,
“Flag”:0,
“Flen”:64,
“Decimal”:0,
“Charset”:“utf8mb4”,
“Collate”:“utf8mb4_bin”,
“Elems”:null
},
“state”:5,
“comment”:“TransportID”,
“hidden”:false,
“version”:2
},
{
“id”:3,
“name”:{
“O”:“DeviceID”,
“L”:“deviceid”
},
“offset”:2,
“origin_default”:null,
“origin_default_bit”:null,
“default”:null,
“default_bit”:null,
“default_is_expr”:false,
“generated_expr_string”:"",
“generated_stored”:false,
“dependences”:null,
“type”:{
“Tp”:15,
“Flag”:0,
“Flen”:64,
“Decimal”:0,
“Charset”:“utf8mb4”,
“Collate”:“utf8mb4_bin”,
“Elems”:null
},
“state”:5,
“comment”:“DeviceID”,
“hidden”:false,
“version”:2
},
{
“id”:4,
“name”:{
“O”:“PlateNo”,
“L”:“plateno”
},
“offset”:3,
“origin_default”:null,
“origin_default_bit”:null,
“default”:null,
“default_bit”:null,
“default_is_expr”:false,
“generated_expr_string”:"",
“generated_stored”:false,
“dependences”:null,
“type”:{
“Tp”:15,
“Flag”:8,
“Flen”:64,
“Decimal”:0,
“Charset”:“utf8mb4”,
“Collate”:“utf8mb4_bin”,
“Elems”:null
},
“state”:5,
“comment”:“PlateNo”,
“hidden”:false,
“version”:2
},
{
“id”:5,
“name”:{
“O”:“PassTime”,
“L”:“passtime”
},
“offset”:4,
“origin_default”:null,
“origin_default_bit”:null,
“default”:null,
“default_bit”:null,
“default_is_expr”:false,
“generated_expr_string”:"",
“generated_stored”:false,
“dependences”:null,
“type”:{
“Tp”:7,
“Flag”:0,
“Flen”:19,
“Decimal”:0,
“Charset”:“binary”,
“Collate”:“binary”,
“Elems”:null
},
“state”:5,
“comment”:“PassTime”,
“hidden”:false,
“version”:2
},
{
“id”:6,
“name”:{
“O”:“fdId”,
“L”:“fdid”
},
“offset”:5,
“origin_default”:null,
“origin_default_bit”:null,
“default”:null,
“default_bit”:null,
“default_is_expr”:false,
“generated_expr_string”:"",
“generated_stored”:false,
“dependences”:null,
“type”:{
“Tp”:15,
“Flag”:8,
“Flen”:64,
“Decimal”:0,
“Charset”:“utf8mb4”,
“Collate”:“utf8mb4_bin”,
“Elems”:null
},
“state”:5,
“comment”:“fdId”,
“hidden”:false,
“version”:2
},
{
“id”:7,
“name”:{
“O”:“channelId”,
“L”:“channelid”
},
“offset”:6,
“origin_default”:null,
“origin_default_bit”:null,
“default”:null,
“default_bit”:null,
“default_is_expr”:false,
“generated_expr_string”:"",
“generated_stored”:false,
“dependences”:null,
“type”:{
“Tp”:15,
“Flag”:0,
“Flen”:64,
“Decimal”:0,
“Charset”:“utf8mb4”,
“Collate”:“utf8mb4_bin”,
“Elems”:null
},
“state”:5,
“comment”:“channelId”,
“hidden”:false,
“version”:2
},
{
“id”:8,
“name”:{
“O”:“channelName”,
“L”:“channelname”
},
“offset”:7,
“origin_default”:null,
“origin_default_bit”:null,
“default”:null,
“default_bit”:null,
“default_is_expr”:false,
“generated_expr_string”:"",
“generated_stored”:false,
“dependences”:null,
“type”:{
“Tp”:15,
“Flag”:0,
“Flen”:500,
“Decimal”:0,
“Charset”:“utf8mb4”,
“Collate”:“utf8mb4_bin”,
“Elems”:null
},
“state”:5,
“comment”:“channelName”,
“hidden”:false,
“version”:2
},
{
“id”:9,
“name”:{
“O”:"_dc_act",
“L”:"_dc_act"
},
“offset”:8,
“origin_default”:null,
“origin_default_bit”:null,
“default”:“I”,
“default_bit”:null,
“default_is_expr”:false,
“generated_expr_string”:"",
“generated_stored”:false,
“dependences”:null,
“type”:{
“Tp”:254,
“Flag”:0,
“Flen”:1,
“Decimal”:0,
“Charset”:“utf8mb4”,
“Collate”:“utf8mb4_bin”,
“Elems”:null
},
“state”:5,
“comment”:"?3??? ?¨¨¡¥?",
“hidden”:false,
“version”:2
}
],
“index_info”:[
{
“id”:1,
“idx_name”:{
“O”:“PlateNo”,
“L”:“plateno”
},
“tbl_name”:{
“O”:"",
“L”:""
},
“idx_cols”:[
{
“name”:{
“O”:“PlateNo”,
“L”:“plateno”
},
“offset”:12,
“length”:-1
}
],
“state”:5,
“comment”:"",
“index_type”:1,
“is_unique”:false,
“is_primary”:false,
“is_invisible”:false
},
{
“id”:2,
“idx_name”:{
“O”:“fdId”,
“L”:“fdid”
},
“tbl_name”:{
“O”:"",
“L”:""
},
“idx_cols”:[
{
“name”:{
“O”:“fdId”,
“L”:“fdid”
},
“offset”:30,
“length”:-1
}
],
“state”:5,
“comment”:"",
“index_type”:1,
“is_unique”:false,
“is_primary”:false,
“is_invisible”:false
}
],
“fk_info”:null,
“state”:5,
“pk_is_handle”:false,
“comment”:“Kafka”,
“auto_inc_id”:0,
“auto_id_cache”:0,
“auto_rand_id”:0,
“max_col_id”:9,
“max_idx_id”:2,
“update_timestamp”:424576077096288263,
“ShardRowIDBits”:4,
“max_shard_row_id_bits”:4,
“auto_random_bits”:0,
“pre_split_regions”:2,
“partition”:{
“type”:1,
“expr”:“unix_timestamp(passtime)”,
“columns”:null,
“enable”:true,
“definitions”:[
{
“id”:10046,
“name”:{
“O”:“p20210504”,
“L”:“p20210504”
},
“less_than”:[
“1620144000”
]
},
{
“id”:10050,
“name”:{
“O”:“p20210505”,
“L”:“p20210505”
},
“less_than”:[
“1620230400”
]
},
{
“id”:10054,
“name”:{
“O”:“p20210506”,
“L”:“p20210506”
},
“less_than”:[
“1620316800”
]
},
{
“id”:10118,
“name”:{
“O”:“p20210507”,
“L”:“p20210507”
},
“less_than”:[
“1620403200”
]
}
],
“adding_definitions”:null,
“num”:8
},
“compression”:"",
“view”:null,
“sequence”:null,
“Lock”:null,
“version”:3,
“tiflash_replica”:null
}

检查了一下分区信息看起来没问题

你们是不是把 SQL 语句写错了呀?比如说 unix_timestamp(‘2021-05-02 00:00:00’)
这个日期应该用单引号,却误使用了反引号?

我们反复确认过sql没问题,分区sql也是由程序自动执行的,升级前都好的,你看报错日志里都是单引号

手动执行这个语句试试呢?先用反引号,看报错信息是否一致;再用单引号,看是否能成功?

@wiboson 麻烦帮忙确认下是引号这个问题导致的吗? 多谢