TiDB-6.1.0 No TTL Table Creation

Translated
1

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

Original topic: tidb-6.1.0 无创建ttl表

| username: TiDBer_de7hnw7X

【TiDB Usage Environment】Production\Test Environment\POC
Production Environment
【TiDB Version】
6.1.0
【Encountered Problem】
Unable to create TTL table
【Reproduction Path】What operations were performed to encounter the problem
Deployment command:
tiup cluster deploy test 6.1.0 ./cluster.yaml --user root -p
Configuration file as follows:
[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

Cluster status:
[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

【Problem Phenomenon and Impact】
TTL table creation prompts syntax error

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)

2
| username: db_user | Original post link

This is a hack feature, the official version doesn’t seem to support it, and your implementation seems to have some issues as well.

image
image1344×232 38.4 KB

image
image806×1000 41.8 KB

This is not an official version feature.

3
| username: 啦啦啦啦啦 | Original post link

Where did you see this syntax? I haven’t used this kind of writing before.

image
image1098×277 24.5 KB

4
| username: TiDBer_de7hnw7X | Original post link

Can you give me a complete and correct configuration?

5
| username: 啦啦啦啦啦 | Original post link

You can refer to this post for using TTL:

6
| username: TiDBer_de7hnw7X | Original post link

The server configuration is fine, but the client-side implementation is needed?

7
| username: 啦啦啦啦啦 | Original post link

Yes, but according to the documentation, enabling TTL will reject requests from TiDB nodes. It is generally used when using raw KV. Check if it meets your needs.

8
| username: TiDBer_de7hnw7X | Original post link

Do you have a commercial version that can support it natively? If so, we can also use the commercial version.

9
| username: TiDBer_de7hnw7X | Original post link

Do you have any specific plans for TTL in the future?

10
| username: Billmay表妹 | Original post link

I’ll help you connect it.