tidb-6.1.0 无创建ttl表

【 TiDB 使用环境】生产\测试环境\ POC
生产环境
【 TiDB 版本】
6.1.0
【遇到的问题】
ttl表无法创建
【复现路径】做过哪些操作出现的问题
部署命令:
tiup cluster deploy test 6.1.0 ./cluster.yaml --user root -p
配置文件如下:
[root@localhost opt]# cat cluster.yaml

# Global variables are applied to all deployments and used as the default value of

# the deployments if a specific deployment value is missing.

global:
user: “tidb”
ssh_port: 22
deploy_dir: “/tidb-deploy”
data_dir: “/tidb-data”

# Monitored variables are applied to all the machines.

monitored:
node_exporter_port: 9100
blackbox_exporter_port: 9115

server_configs:
tidb:
log.slow-threshold: 300
tikv:
readpool.storage.use-unified-pool: false
readpool.coprocessor.use-unified-pool: true
storage.enable-ttl: true
storage.ttl-check-poll-interval: “24h”
storage.api-version: 2
pd:
replication.enable-placement-rules: true
replication.location-labels: [“host”]
tiflash:
logger.level: “info”

pd_servers:

  • host: 192.168.181.128
  • host: 192.168.181.129
  • host: 192.168.181.130

tidb_servers:

  • host: 192.168.181.129

tikv_servers:

  • host: 192.168.181.128
    port: 20160
    status_port: 20180
    config:
    server.labels: { host: “logic-host-1” }

  • host: 192.168.181.129
    port: 20160
    status_port: 20180
    config:
    server.labels: { host: “logic-host-2” }

  • host: 192.168.181.130
    port: 20160
    status_port: 20180
    config:
    server.labels: { host: “logic-host-3” }

tiflash_servers:

  • host: 192.168.181.128

monitoring_servers:

  • host: 192.168.181.129

grafana_servers:

  • host: 192.168.181.130

集群状态:
[root@localhost opt]# tiup cluster display test
tiup is checking updates for component cluster …
Starting component cluster: /root/.tiup/components/cluster/v1.10.2/tiup-cluster display test
Cluster type: tidb
Cluster name: test
Cluster version: v6.1.0
Deploy user: tidb
SSH type: builtin
Dashboard URL: http://192.168.181.129:2379/dashboard
Grafana URL: http://192.168.181.130:3000
ID Role Host Ports OS/Arch Status Data Dir Deploy Dir


192.168.181.130:3000 grafana 192.168.181.130 3000 linux/x86_64 Up - /tidb-deploy/grafana-3000
192.168.181.128:2379 pd 192.168.181.128 2379/2380 linux/x86_64 Up /tidb-data/pd-2379 /tidb-deploy/pd-2379
192.168.181.129:2379 pd 192.168.181.129 2379/2380 linux/x86_64 Up|UI /tidb-data/pd-2379 /tidb-deploy/pd-2379
192.168.181.130:2379 pd 192.168.181.130 2379/2380 linux/x86_64 Up|L /tidb-data/pd-2379 /tidb-deploy/pd-2379
192.168.181.129:9090 prometheus 192.168.181.129 9090/12020 linux/x86_64 Up /tidb-data/prometheus-9090 /tidb-deploy/prometheus-9090
192.168.181.129:4000 tidb 192.168.181.129 4000/10080 linux/x86_64 Up - /tidb-deploy/tidb-4000
192.168.181.128:9000 tiflash 192.168.181.128 9000/8123/3930/20170/20292/8234 linux/x86_64 Up /tidb-data/tiflash-9000 /tidb-deploy/tiflash-9000
192.168.181.128:20160 tikv 192.168.181.128 20160/20180 linux/x86_64 Up /tidb-data/tikv-20160 /tidb-deploy/tikv-20160
192.168.181.129:20160 tikv 192.168.181.129 20160/20180 linux/x86_64 Up /tidb-data/tikv-20160 /tidb-deploy/tikv-20160
192.168.181.130:20160 tikv 192.168.181.130 20160/20180 linux/x86_64 Up /tidb-data/tikv-20160 /tidb-deploy/tikv-20160
Total nodes: 10

【问题现象及影响】
ttl表创建提示语法错误

MySQL [test]> CREATE TABLE ttl (
-> id bigint,
-> nickname varchar(100),
-> balance decimal(15,2)
-> );TTL=‘1h’, TTL_GRANULARITY=‘ROW’;
Query OK, 0 rows affected (0.09 sec)

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your TiDB version for the right syntax to use line 1 column 3 near “TTL=‘1h’, TTL_GRANULARITY=‘ROW’”
MySQL [test]> ls
-> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your TiDB version for the right syntax to use line 1 column 2 near “ls”
MySQL [test]> show create table ttl;
±------±------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
±------±------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ttl | CREATE TABLE ttl (
id bigint(20) DEFAULT NULL,
nickname varchar(100) DEFAULT NULL,
balance decimal(15,2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
±------±------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.04 sec)

这是hack的功能吧,官方貌似不支持,而且你的写法好像也有点问题

不是官方版本功能

1 Like

这个语法是哪里看到的,没用过这种写法。


https://docs.pingcap.com/zh/tidb/stable/release-5.1.4#兼容性更改

能给我一个完整正确的配置吗?

使用TTL可以参考下这个帖子

就是服务器配置没有问题,需要开发客户端实现??

是的,但是看文档开启TTL后会拒绝tidb节点的请求,一般是裸KV的时候用,看看是否符合你的需求。

你们有商业版本能原生支持吗?可以的话,我们也可以使用商业版本

后续对ttl这块有什么具体的计划吗?

我帮你对接一下