Issue of Automatic Data Deletion in TiDB Tables

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

Original topic: tidb表自动删除数据问题

| username: wenyi

Created a table to retain data for one month, but after one hour, all the data was gone, including the data that hadn’t expired yet!!!


image
After one hour, it should have only deleted one expired record, but now both records are gone.
image

| username: wenyi | Original post link

I inserted 2 pieces of data into the table, one within a month and one from more than a month ago. I will check the automatic deletion of data after 1 hour.

| username: Ming | Original post link

You can first check how the information in the mysql.tidb_ttl_job_history table is recorded.

| username: wenyi | Original post link

The image is not visible. Please provide the text you need translated.

| username: 我是咖啡哥 | Original post link

Could you please send the complete SQL you tested? I want to test it in my environment.

| username: wenyi | Original post link

This is a test statement
CREATE TABLE t3 (
id int(11) NOT NULL,
created_at datetime DEFAULT NULL,
PRIMARY KEY (id)
) TTL=created_at + INTERVAL 1 MONTH TTL_ENABLE=‘ON’

insert into t3 values(3,‘2023-07-06 11:11:11’);
insert into t3 values(4,‘2023-05-06 11:11:11’);

| username: wenyi | Original post link

An hour later, when checking the data, all the data was automatically deleted.

| username: zhanggame1 | Original post link

It seems like a bug. I’ll test it.

| username: 有猫万事足 | Original post link

Could it be an issue with the data type?

In the documentation, created_at is a TIMESTAMP, but in your table creation statement, it is a DATETIME.
Try changing it to TIMESTAMP and see if it works.

| username: chao | Original post link

Can this be reproduced repeatedly? Looking at the history table, all executed tasks have expired_rows as 0, which means no data was deleted during task execution.

| username: wenyi | Original post link

It can be reproduced at any time. Not sure if there is a similar situation in version 6.5.3.

| username: wenyi | Original post link

Datetime is a very commonly used type, and we use this type for our business time types.

| username: 有猫万事足 | Original post link

Indeed, and there’s also the 2038 problem with timestamps.
Using this type is correct.
However, it’s also true that it doesn’t match the documentation.
It might really be a bug.
Datetime type support might not be implemented.
You can raise an issue on GitHub.

| username: YangKeao | Original post link

The Datetime type should be supported, it’s just that there are no such examples in the documentation :thinking:

| username: chao | Original post link

Which version is having the issue? Also, can it be reproduced by setting the ttl_job_interval shorter? I tested it locally and only one record was deleted.

| username: chao | Original post link

If it can be reproduced, you can first increase tidb_gc_run_interval, for example, to 24h. Then, after reproducing the issue, execute curl http://{tidbhost}:10080/mvcc/key/test/t3/3 to check the mvcc information of the deleted record and see if it matches the execution time of the ttl job.

| username: 有猫万事足 | Original post link

CREATE TABLE `t3` (
  `id` int(11) NOT NULL,
  `created_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin /*T![ttl] TTL=`created_at` + INTERVAL 1 MONTH */ /*T![ttl] TTL_ENABLE='ON' */ /*T![ttl] TTL_JOB_INTERVAL='1h' */

Cannot reproduce.

My TiDB version:
Release Version: v7.1.0
Edition: Community
Git Commit Hash: 635a4362235e8a3c0043542e629532e3c7bb2756
Git Branch: heads/refs/tags/v7.1.0
UTC Build Time: 2023-05-30 10:58:57
GoVersion: go1.20.3
Race Enabled: false
TiKV Min Version: 6.2.0-alpha
Check Table Before Drop: false
Store: tikv

| username: cassblanca | Original post link

Unable to reproduce in local testing.

| username: zhanggame1 | Original post link

I didn’t measure it either.

| username: coderv | Original post link

Check the deleted logs.