The index data and table data of the TTL table are inconsistent

[zeminzhou]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

1. startup a TiDB cluster;
2. create table cache;

CREATE TABLE `cache` (
  `cache_key` varchar(512) NOT NULL,
  `cache_value` json NOT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `expires` int(11) DEFAULT '-1' COMMENT 'cache will expire after n seconds',
  `expired_at` datetime GENERATED ALWAYS AS (if(`expires` > 0, date_add(`updated_at`, interval `expires` second), date_add(`updated_at`, interval 99 year))) VIRTUAL,
  PRIMARY KEY (`cache_key`) /*T![clustered_index] CLUSTERED */,
  KEY `idx_c_on_created_at` (`created_at`),
  KEY `idx_c_on_expired_at` (`expired_at`),
  KEY `idx_expired_at` (`expired_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin /*T![ttl] TTL=`expired_at` + INTERVAL 0 DAY */ /*T![ttl] TTL_ENABLE='ON' */ /*T![ttl] TTL_JOB_INTERVAL='15m' */

3. run the following command

func main() {
	// Open a connection to the database.
	// Replace the parameters with your own values for the database.
	db, err := sql.Open("mysql", "root@tcp(localhost:4000)/test")
	if err != nil {
		fmt.Println("Error connecting to the database:", err)
		return
	}
	defer db.Close()

	for i := 0; i < 1000000; i++ {
		dateStr := time.Now().Format("2006-01-02 15:04:05")
		sqlStr := fmt.Sprintf("INSERT INTO `cache` (`cache_key`, `cache_value`, `created_at`, `updated_at`, `expires`) VALUES ('%s', '\"%s\"', '%s', '%s', %d) ON DUPLICATE KEY UPDATE expires = expires + 1", dateStr, dateStr, dateStr, dateStr, 60)
		_, err = db.Exec(sqlStr)
		if err != nil {
			panic(err)
		}
		if i%10 == 0 {
			oldTime := time.Now().Add(-time.Minute).Format("2006-01-02 15:04:05")
			updateStr := fmt.Sprintf("UPDATE `cache` set `updated_at` = '%s' where `cache_key` = '%s'", dateStr, oldTime)
			_, err = db.Exec(updateStr)
			if err != nil {
				panic(err)
			}
		}
		time.Sleep(500 * time.Millisecond)
	}
}

4. admin check table cache;

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

admin check table should be ok.

3. What did you see instead (Required)

4. What is your TiDB version? (Required)

release-7.5

[ystaticy]

The issue was reproduced in tidb branch master
version: v8.4.0-alpha-480-g5ab6738d15

If I change the type of general column from VIRTUAL to STORED,I haven't encountered any issues so far.

I'm not sure if this issue is related to the generated column; I just made a simple comparison. I haven't encountered any problems with the STORED type for now.

[wjhuang2016]

CREATE TABLE `cache` (
  `cache_key` varchar(512) NOT NULL,
  `cache_value` json NOT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `expires` int(11) DEFAULT '-1' COMMENT 'cache will expire after n seconds',
  `expired_at` datetime GENERATED ALWAYS AS (if(`expires` > 0, date_add(`updated_at`, interval `expires` second), date_add(`updated_at`, interval 99 year))) VIRTUAL,
  PRIMARY KEY (`cache_key`) /*T![clustered_index] CLUSTERED */,
  KEY `idx_c_on_created_at` (`created_at`),
  KEY `idx_c_on_expired_at` (`expired_at`),
  KEY `idx_expired_at` (`expired_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin /*T![ttl] TTL=`expired_at` + INTERVAL 0 DAY */ /*T![ttl] TTL_ENABLE='ON' */ /*T![ttl] TTL_JOB_INTERVAL='1m' */;

INSERT INTO `cache` (`cache_key`,`cache_value`,`created_at`,`updated_at`,`expires`) VALUES ('2024-10-30 18:00:22','\"2024-10-30 18:00:22\"','2024-10-30 18:00:22','2024-10-30 18:01:22',61), ('2024-10-30 18:00:27','\"2024-10-30 18:00:27\"','2024-10-30 18:00:27','2024-10-30 18:01:27',61), ('2024-10-30 18:00:32','\"2024-10-30 18:00:32\"','2024-10-30 18:00:32','2024-10-30 18:01:32',61), ('2024-10-30 18:00:37','\"2024-10-30 18:00:37\"','2024-10-30 18:00:37','2024-10-30 18:01:37',61), ('2024-10-30 18:00:42','\"2024-10-30 18:00:42\"','2024-10-30 18:00:42','2024-10-30 18:01:42',61), ('2024-10-30 18:00:47','\"2024-10-30 18:00:47\"','2024-10-30 18:00:47','2024-10-30 18:01:47',61), ('2024-10-30 18:00:52','\"2024-10-30 18:00:52\"','2024-10-30 18:00:52','2024-10-30 18:01:52',61), ('2024-10-30 18:00:57','\"2024-10-30 18:00:57\"','2024-10-30 18:00:57','2024-10-30 18:01:57',61), ('2024-10-30 18:01:02','\"2024-10-30 18:01:02\"','2024-10-30 18:01:02','2024-10-30 18:02:02',61), ('2024-10-30 18:01:07','\"2024-10-30 18:01:07\"','2024-10-30 18:01:07','2024-10-30 18:02:07',61), ('2024-10-30 18:01:12','\"2024-10-30 18:01:12\"','2024-10-30 18:01:12','2024-10-30 18:02:12',61), ('2024-10-30 18:01:18','\"2024-10-30 18:01:18\"','2024-10-30 18:01:18','2024-10-30 18:02:17',60), ('2024-10-30 18:01:20','\"2024-10-30 18:01:20\"','2024-10-30 18:01:20','2024-10-30 18:01:20',61), ('2024-10-30 18:01:21','\"2024-10-30 18:01:21\"','2024-10-30 18:01:21','2024-10-30 18:01:21',61), ('2024-10-30 18:01:22','\"2024-10-30 18:01:22\"','2024-10-30 18:01:22','2024-10-30 18:01:22',61), ('2024-10-30 18:01:23','\"2024-10-30 18:01:23\"','2024-10-30 18:01:23','2024-10-30 18:02:23',61), ('2024-10-30 18:01:24','\"2024-10-30 18:01:24\"','2024-10-30 18:01:24','2024-10-30 18:01:24',61), ('2024-10-30 18:01:25','\"2024-10-30 18:01:25\"','2024-10-30 18:01:25','2024-10-30 18:01:25',61), ('2024-10-30 18:01:26','\"2024-10-30 18:01:26\"','2024-10-30 18:01:26','2024-10-30 18:01:26',61), ('2024-10-30 18:01:27','\"2024-10-30 18:01:27\"','2024-10-30 18:01:27','2024-10-30 18:01:27',61), ('2024-10-30 18:01:28','\"2024-10-30 18:01:28\"','2024-10-30 18:01:28','2024-10-30 18:02:28',61), ('2024-10-30 18:01:29','\"2024-10-30 18:01:29\"','2024-10-30 18:01:29','2024-10-30 18:01:29',61), ('2024-10-30 18:01:30','\"2024-10-30 18:01:30\"','2024-10-30 18:01:30','2024-10-30 18:01:30',61), ('2024-10-30 18:01:31','\"2024-10-30 18:01:31\"','2024-10-30 18:01:31','2024-10-30 18:01:31',61), ('2024-10-30 18:01:32','\"2024-10-30 18:01:32\"','2024-10-30 18:01:32','2024-10-30 18:01:32',61), ('2024-10-30 18:01:33','\"2024-10-30 18:01:33\"','2024-10-30 18:01:33','2024-10-30 18:02:33',61), ('2024-10-30 18:01:34','\"2024-10-30 18:01:34\"','2024-10-30 18:01:34','2024-10-30 18:01:34',61), ('2024-10-30 18:01:35','\"2024-10-30 18:01:35\"','2024-10-30 18:01:35','2024-10-30 18:01:35',61), ('2024-10-30 18:01:36','\"2024-10-30 18:01:36\"','2024-10-30 18:01:36','2024-10-30 18:01:36',61), ('2024-10-30 18:01:37','\"2024-10-30 18:01:37\"','2024-10-30 18:01:37','2024-10-30 18:01:37',61), ('2024-10-30 18:01:38','\"2024-10-30 18:01:38\"','2024-10-30 18:01:38','2024-10-30 18:02:38',61), ('2024-10-30 18:01:39','\"2024-10-30 18:01:39\"','2024-10-30 18:01:39','2024-10-30 18:01:39',61), ('2024-10-30 18:01:40','\"2024-10-30 18:01:40\"','2024-10-30 18:01:40','2024-10-30 18:01:40',61), ('2024-10-30 18:01:41','\"2024-10-30 18:01:41\"','2024-10-30 18:01:41','2024-10-30 18:01:41',61), ('2024-10-30 18:01:42','\"2024-10-30 18:01:42\"','2024-10-30 18:01:42','2024-10-30 18:01:42',61), ('2024-10-30 18:01:43','\"2024-10-30 18:01:43\"','2024-10-30 18:01:43','2024-10-30 18:02:43',61), ('2024-10-30 18:01:44','\"2024-10-30 18:01:44\"','2024-10-30 18:01:44','2024-10-30 18:01:44',61), ('2024-10-30 18:01:45','\"2024-10-30 18:01:45\"','2024-10-30 18:01:45','2024-10-30 18:01:45',61), ('2024-10-30 18:01:46','\"2024-10-30 18:01:46\"','2024-10-30 18:01:46','2024-10-30 18:01:46',61), ('2024-10-30 18:01:47','\"2024-10-30 18:01:47\"','2024-10-30 18:01:47','2024-10-30 18:01:47',61), ('2024-10-30 18:01:48','\"2024-10-30 18:01:48\"','2024-10-30 18:01:48','2024-10-30 18:02:48',60), ('2024-10-30 18:01:49','\"2024-10-30 18:01:49\"','2024-10-30 18:01:49','2024-10-30 18:01:49',61), ('2024-10-30 18:01:50','\"2024-10-30 18:01:50\"','2024-10-30 18:01:50','2024-10-30 18:01:50',61), ('2024-10-30 18:01:51','\"2024-10-30 18:01:51\"','2024-10-30 18:01:51','2024-10-30 18:01:51',61), ('2024-10-30 18:01:52','\"2024-10-30 18:01:52\"','2024-10-30 18:01:52','2024-10-30 18:01:52',61), ('2024-10-30 18:01:53','\"2024-10-30 18:01:53\"','2024-10-30 18:01:53','2024-10-30 18:02:53',61), ('2024-10-30 18:01:54','\"2024-10-30 18:01:54\"','2024-10-30 18:01:54','2024-10-30 18:01:54',61), ('2024-10-30 18:01:55','\"2024-10-30 18:01:55\"','2024-10-30 18:01:55','2024-10-30 18:01:55',61), ('2024-10-30 18:01:56','\"2024-10-30 18:01:56\"','2024-10-30 18:01:56','2024-10-30 18:01:56',61), ('2024-10-30 18:01:57','\"2024-10-30 18:01:57\"','2024-10-30 18:01:57','2024-10-30 18:01:57',61), ('2024-10-30 18:01:58','\"2024-10-30 18:01:58\"','2024-10-30 18:01:58','2024-10-30 18:02:58',61), ('2024-10-30 18:01:59','\"2024-10-30 18:01:59\"','2024-10-30 18:01:59','2024-10-30 18:01:59',61), ('2024-10-30 18:02:00','\"2024-10-30 18:02:00\"','2024-10-30 18:02:00','2024-10-30 18:02:00',61), ('2024-10-30 18:02:01','\"2024-10-30 18:02:01\"','2024-10-30 18:02:01','2024-10-30 18:02:01',61), ('2024-10-30 18:02:02','\"2024-10-30 18:02:02\"','2024-10-30 18:02:02','2024-10-30 18:02:02',61), ('2024-10-30 18:02:03','\"2024-10-30 18:02:03\"','2024-10-30 18:02:03','2024-10-30 18:03:03',61), ('2024-10-30 18:02:04','\"2024-10-30 18:02:04\"','2024-10-30 18:02:04','2024-10-30 18:02:04',61), ('2024-10-30 18:02:05','\"2024-10-30 18:02:05\"','2024-10-30 18:02:05','2024-10-30 18:02:05',61), ('2024-10-30 18:02:06','\"2024-10-30 18:02:06\"','2024-10-30 18:02:06','2024-10-30 18:02:06',61), ('2024-10-30 18:02:07','\"2024-10-30 18:02:07\"','2024-10-30 18:02:07','2024-10-30 18:02:07',61), ('2024-10-30 18:02:08','\"2024-10-30 18:02:08\"','2024-10-30 18:02:08','2024-10-30 18:03:08',61), ('2024-10-30 18:02:09','\"2024-10-30 18:02:09\"','2024-10-30 18:02:09','2024-10-30 18:02:09',61), ('2024-10-30 18:02:10','\"2024-10-30 18:02:10\"','2024-10-30 18:02:10','2024-10-30 18:02:10',61), ('2024-10-30 18:02:11','\"2024-10-30 18:02:11\"','2024-10-30 18:02:11','2024-10-30 18:02:11',61), ('2024-10-30 18:02:12','\"2024-10-30 18:02:12\"','2024-10-30 18:02:12','2024-10-30 18:02:12',61), ('2024-10-30 18:02:13','\"2024-10-30 18:02:13\"','2024-10-30 18:02:13','2024-10-30 18:03:13',61), ('2024-10-30 18:02:14','\"2024-10-30 18:02:14\"','2024-10-30 18:02:14','2024-10-30 18:02:14',61), ('2024-10-30 18:02:15','\"2024-10-30 18:02:15\"','2024-10-30 18:02:15','2024-10-30 18:02:15',61), ('2024-10-30 18:02:16','\"2024-10-30 18:02:16\"','2024-10-30 18:02:16','2024-10-30 18:02:16',61), ('2024-10-30 18:02:17','\"2024-10-30 18:02:17\"','2024-10-30 18:02:17','2024-10-30 18:02:18',61), ('2024-10-30 18:02:18','\"2024-10-30 18:02:18\"','2024-10-30 18:02:18','2024-10-30 18:02:18',60), ('2024-10-30 18:02:19','\"2024-10-30 18:02:19\"','2024-10-30 18:02:19','2024-10-30 18:03:19',61), ('2024-10-30 18:02:20','\"2024-10-30 18:02:20\"','2024-10-30 18:02:20','2024-10-30 18:02:20',61), ('2024-10-30 18:02:21','\"2024-10-30 18:02:21\"','2024-10-30 18:02:21','2024-10-30 18:02:21',61), ('2024-10-30 18:02:22','\"2024-10-30 18:02:22\"','2024-10-30 18:02:22','2024-10-30 18:02:22',61), ('2024-10-30 18:02:23','\"2024-10-30 18:02:23\"','2024-10-30 18:02:23','2024-10-30 18:02:23',61), ('2024-10-30 18:02:24','\"2024-10-30 18:02:24\"','2024-10-30 18:02:24','2024-10-30 18:02:24',61), ('2024-10-30 18:02:25','\"2024-10-30 18:02:25\"','2024-10-30 18:02:25','2024-10-30 18:02:25',61), ('2024-10-30 18:02:26','\"2024-10-30 18:02:26\"','2024-10-30 18:02:26','2024-10-30 18:02:26',61), ('2024-10-30 18:02:27','\"2024-10-30 18:02:27\"','2024-10-30 18:02:27','2024-10-30 18:02:27',61), ('2024-10-30 18:02:28','\"2024-10-30 18:02:28\"','2024-10-30 18:02:28','2024-10-30 18:02:28',61), ('2024-10-30 18:02:29','\"2024-10-30 18:02:29\"','2024-10-30 18:02:29','2024-10-30 18:02:29',61), ('2024-10-30 18:02:30','\"2024-10-30 18:02:30\"','2024-10-30 18:02:30','2024-10-30 18:02:30',61), ('2024-10-30 18:02:31','\"2024-10-30 18:02:31\"','2024-10-30 18:02:31','2024-10-30 18:02:31',61), ('2024-10-30 18:02:32','\"2024-10-30 18:02:32\"','2024-10-30 18:02:32','2024-10-30 18:02:32',61), ('2024-10-30 18:02:33','\"2024-10-30 18:02:33\"','2024-10-30 18:02:33','2024-10-30 18:02:33',61), ('2024-10-30 18:02:34','\"2024-10-30 18:02:34\"','2024-10-30 18:02:34','2024-10-30 18:02:34',61), ('2024-10-30 18:02:35','\"2024-10-30 18:02:35\"','2024-10-30 18:02:35','2024-10-30 18:02:35',61), ('2024-10-30 18:02:36','\"2024-10-30 18:02:36\"','2024-10-30 18:02:36','2024-10-30 18:02:36',61), ('2024-10-30 18:02:37','\"2024-10-30 18:02:37\"','2024-10-30 18:02:37','2024-10-30 18:02:37',61), ('2024-10-30 18:02:38','\"2024-10-30 18:02:38\"','2024-10-30 18:02:38','2024-10-30 18:02:38',61), ('2024-10-30 18:02:39','\"2024-10-30 18:02:39\"','2024-10-30 18:02:39','2024-10-30 18:02:39',61), ('2024-10-30 18:02:40','\"2024-10-30 18:02:40\"','2024-10-30 18:02:40','2024-10-30 18:02:40',61), ('2024-10-30 18:02:41','\"2024-10-30 18:02:41\"','2024-10-30 18:02:41','2024-10-30 18:02:41',61), ('2024-10-30 18:02:42','\"2024-10-30 18:02:42\"','2024-10-30 18:02:42','2024-10-30 18:02:42',61), ('2024-10-30 18:02:43','\"2024-10-30 18:02:43\"','2024-10-30 18:02:43','2024-10-30 18:02:43',61), ('2024-10-30 18:02:44','\"2024-10-30 18:02:44\"','2024-10-30 18:02:44','2024-10-30 18:02:44',61), ('2024-10-30 18:02:45','\"2024-10-30 18:02:45\"','2024-10-30 18:02:45','2024-10-30 18:02:45',61), ('2024-10-30 18:02:46','\"2024-10-30 18:02:46\"','2024-10-30 18:02:46','2024-10-30 18:02:46',61), ('2024-10-30 18:02:47','\"2024-10-30 18:02:47\"','2024-10-30 18:02:47','2024-10-30 18:02:48',61), ('2024-10-30 18:02:48','\"2024-10-30 18:02:48\"','2024-10-30 18:02:48','2024-10-30 18:02:48',60), ('2024-10-30 18:02:49','\"2024-10-30 18:02:49\"','2024-10-30 18:02:49','2024-10-30 18:02:49',61), ('2024-10-30 18:02:50','\"2024-10-30 18:02:50\"','2024-10-30 18:02:50','2024-10-30 18:02:50',61), ('2024-10-30 18:02:51','\"2024-10-30 18:02:51\"','2024-10-30 18:02:51','2024-10-30 18:02:51',61), ('2024-10-30 18:02:52','\"2024-10-30 18:02:52\"','2024-10-30 18:02:52','2024-10-30 18:02:52',61), ('2024-10-30 18:02:53','\"2024-10-30 18:02:53\"','2024-10-30 18:02:53','2024-10-30 18:02:53',61), ('2024-10-30 18:02:54','\"2024-10-30 18:02:54\"','2024-10-30 18:02:54','2024-10-30 18:02:54',61), ('2024-10-30 18:02:55','\"2024-10-30 18:02:55\"','2024-10-30 18:02:55','2024-10-30 18:02:55',61), ('2024-10-30 18:02:56','\"2024-10-30 18:02:56\"','2024-10-30 18:02:56','2024-10-30 18:02:56',61), ('2024-10-30 18:02:57','\"2024-10-30 18:02:57\"','2024-10-30 18:02:57','2024-10-30 18:02:57',61), ('2024-10-30 18:02:58','\"2024-10-30 18:02:58\"','2024-10-30 18:02:58','2024-10-30 18:02:58',61), ('2024-10-30 18:02:59','\"2024-10-30 18:02:59\"','2024-10-30 18:02:59','2024-10-30 18:02:59',61), ('2024-10-30 18:03:00','\"2024-10-30 18:03:00\"','2024-10-30 18:03:00','2024-10-30 18:03:00',61), ('2024-10-30 18:03:01','\"2024-10-30 18:03:01\"','2024-10-30 18:03:01','2024-10-30 18:03:01',61), ('2024-10-30 18:03:02','\"2024-10-30 18:03:02\"','2024-10-30 18:03:02','2024-10-30 18:03:02',61), ('2024-10-30 18:03:03','\"2024-10-30 18:03:03\"','2024-10-30 18:03:03','2024-10-30 18:03:03',61), ('2024-10-30 18:03:04','\"2024-10-30 18:03:04\"','2024-10-30 18:03:04','2024-10-30 18:03:04',61), ('2024-10-30 18:03:05','\"2024-10-30 18:03:05\"','2024-10-30 18:03:05','2024-10-30 18:03:05',61), ('2024-10-30 18:03:06','\"2024-10-30 18:03:06\"','2024-10-30 18:03:06','2024-10-30 18:03:06',61), ('2024-10-30 18:03:07','\"2024-10-30 18:03:07\"','2024-10-30 18:03:07','2024-10-30 18:03:07',61), ('2024-10-30 18:03:08','\"2024-10-30 18:03:08\"','2024-10-30 18:03:08','2024-10-30 18:03:08',61), ('2024-10-30 18:03:09','\"2024-10-30 18:03:09\"','2024-10-30 18:03:09','2024-10-30 18:03:09',61), ('2024-10-30 18:03:10','\"2024-10-30 18:03:10\"','2024-10-30 18:03:10','2024-10-30 18:03:10',61), ('2024-10-30 18:03:11','\"2024-10-30 18:03:11\"','2024-10-30 18:03:11','2024-10-30 18:03:11',61), ('2024-10-30 18:03:12','\"2024-10-30 18:03:12\"','2024-10-30 18:03:12','2024-10-30 18:03:12',61), ('2024-10-30 18:03:13','\"2024-10-30 18:03:13\"','2024-10-30 18:03:13','2024-10-30 18:03:13',61), ('2024-10-30 18:03:14','\"2024-10-30 18:03:14\"','2024-10-30 18:03:14','2024-10-30 18:03:14',60), ('2024-10-30 18:03:15','\"2024-10-30 18:03:15\"','2024-10-30 18:03:15','2024-10-30 18:03:15',61), ('2024-10-30 18:03:16','\"2024-10-30 18:03:16\"','2024-10-30 18:03:16','2024-10-30 18:03:16',61), ('2024-10-30 18:03:17','\"2024-10-30 18:03:17\"','2024-10-30 18:03:17','2024-10-30 18:03:17',61), ('2024-10-30 18:03:18','\"2024-10-30 18:03:18\"','2024-10-30 18:03:18','2024-10-30 18:03:18',61), ('2024-10-30 18:03:19','\"2024-10-30 18:03:19\"','2024-10-30 18:03:19','2024-10-30 18:03:19',61), ('2024-10-30 18:03:20','\"2024-10-30 18:03:20\"','2024-10-30 18:03:20','2024-10-30 18:03:20',61);

DELETE LOW_PRIORITY FROM `cache` WHERE `cache_key` IN ('2024-10-30 18:00:22', '2024-10-30 18:00:27', '2024-10-30 18:00:32', '2024-10-30 18:00:37', '2024-10-30 18:00:42', '2024-10-30 18:00:47', '2024-10-30 18:00:52', '2024-10-30 18:00:57', '2024-10-30 18:01:02', '2024-10-30 18:01:07', '2024-10-30 18:01:12', '2024-10-30 18:01:18', '2024-10-30 18:01:20', '2024-10-30 18:01:21', '2024-10-30 18:01:22', '2024-10-30 18:01:24', '2024-10-30 18:01:25', '2024-10-30 18:01:26', '2024-10-30 18:01:27', '2024-10-30 18:01:29', '2024-10-30 18:01:30', '2024-10-30 18:01:31', '2024-10-30 18:01:32', '2024-10-30 18:01:34', '2024-10-30 18:01:35', '2024-10-30 18:01:36', '2024-10-30 18:01:37', '2024-10-30 18:01:39', '2024-10-30 18:01:40', '2024-10-30 18:01:41', '2024-10-30 18:01:42', '2024-10-30 18:01:44', '2024-10-30 18:01:45', '2024-10-30 18:01:46', '2024-10-30 18:01:47', '2024-10-30 18:01:49', '2024-10-30 18:01:50', '2024-10-30 18:01:51', '2024-10-30 18:01:52', '2024-10-30 18:01:54', '2024-10-30 18:01:55', '2024-10-30 18:01:56', '2024-10-30 18:01:57', '2024-10-30 18:01:59', '2024-10-30 18:02:00', '2024-10-30 18:02:01', '2024-10-30 18:02:02', '2024-10-30 18:02:04', '2024-10-30 18:02:05', '2024-10-30 18:02:06', '2024-10-30 18:02:07', '2024-10-30 18:02:09', '2024-10-30 18:02:10', '2024-10-30 18:02:11', '2024-10-30 18:02:12', '2024-10-30 18:02:14', '2024-10-30 18:02:15', '2024-10-30 18:02:16', '2024-10-30 18:02:17', '2024-10-30 18:02:18') AND `expired_at` < FROM_UNIXTIME(1730282601) LIMIT 60;

[joechenrh]

When investigating into this problem, I found another problem which can be reproduced easier.

CREATE TABLE cache (
  cache_key varchar(512) NOT NULL,
  updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  expired_at datetime GENERATED ALWAYS AS (if(expires > 0, date_add(updated_at, interval expires second), date_add(updated_at, interval 99 year))) VIRTUAL,
  expires int(11),
  PRIMARY KEY (cache_key) /*T![clustered_index] CLUSTERED */,
  KEY idx_c_on_expired_at (expired_at)
);

INSERT INTO cache(cache_key, expires) VALUES ('2001-01-01 11:11:11', 60) ON DUPLICATE KEY UPDATE expires = expires + 1;
select sleep(5);
INSERT INTO cache(cache_key, expires) VALUES ('2001-01-01 11:11:11', 60) ON DUPLICATE KEY UPDATE expires = expires + 1;
select sleep(5);
INSERT INTO cache(cache_key, expires) VALUES ('2001-01-01 11:11:11', 60) ON DUPLICATE KEY UPDATE expires = expires + 1;

The third insert will cause an error like:

ERROR 8141 (HY000): assertion failed: key: 7480000000000000745f6980000000000000020419b4cae95100000001323030312d30312dff30312031313a3131ff3a31310000000000fa, assertion: Exist, start_ts: 453715841994719233, existing start ts: 0, existing commit ts: 0

admin check table cache will return no error but you can see that expired_at in table and index are calculated using different timestamp.

mysql> select /*+ force_index(test.cache, idx_c_on_expired_at) */ cache_key, expired_at from cache order by cache_key;
+---------------------+---------------------+
| cache_key           | expired_at          |
+---------------------+---------------------+
| 2001-01-01 11:11:11 | 2024-11-05 14:37:12 |
+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> select /*+ ignore_index(test.cache, idx_c_on_expired_at) */ cache_key, expired_at from cache order by cache_key;
+---------------------+---------------------+
| cache_key           | expired_at          |
+---------------------+---------------------+
| 2001-01-01 11:11:11 | 2024-11-05 14:37:17 |
+---------------------+---------------------+
1 row in set (0.00 sec)

Not sure if the root causes of these two problems are same.

[joechenrh]

/severity major

[YangKeao]

The index data and table data inconsistency is always considered as a critical issue, so I changed the severity to critical.

[joechenrh]

/remove-label may-affects-5.4

[joechenrh]

After #57123, I ran the test locally and got no errors. So I close it.

Reopen if you still got some error.