Description:
An insert prepared statement sticks on a partition once it executed and it fails to insert into other partitions.
The problem happens if the table has timestamp based partitions and the partition key (column) has non-constant default value like CURRENT_TIMESTAMP.
mysql> EXECUTE stmt USING @id;
ERROR 1748 (HY000): Found a row not matching the given partition set
mysql>
I do not see the problem in 8.0.41 or older then I doubt 8.0.42 introduced the bug.
For instance, if you created the following table with timestamp range partition and a prepared statement inserting data into the table,
CREATE TABLE `test_table` (
`id` int NOT NULL,
`created_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`,`created_timestamp`)
) ENGINE=InnoDB
PARTITION BY RANGE (unix_timestamp(`created_timestamp`))
(PARTITION pMIN VALUES LESS THAN (1762341952) ENGINE = InnoDB,
PARTITION pMAX VALUES LESS THAN MAXVALUE ENGINE = InnoDB);
PREPARE stmt FROM 'INSERT INTO test_table (id) values (?)';
SET @id=1;
The first and several executions which insert data into the same partition of the first execution (pMIN), these queries work out.
+---------------------+---------------------+----------+
| CURRENT_TIMESTAMP | Partition Timestamp | Rollover |
+---------------------+---------------------+----------+
| 2025-11-05 11:25:46 | 2025-11-05 11:25:52 | FALSE |
+---------------------+---------------------+----------+
mysql> EXECUTE stmt USING @id;
Query OK, 1 row affected (0.00 sec)
...
mysql> SELECT * FROM test_table;
+----+---------------------+
| id | created_timestamp |
+----+---------------------+
| 1 | 2025-11-05 11:25:46 |
| 1 | 2025-11-05 11:25:48 |
+----+---------------------+
2 rows in set (0.00 sec)
mysql>
However, another execution which is going to insert into the different partition (pMAX) gets failed by mismatch partition set error.
+---------------------+---------------------+----------+
| CURRENT_TIMESTAMP | Partition Timestamp | Rollover |
+---------------------+---------------------+----------+
| 2025-11-05 11:26:00 | 2025-11-05 11:25:52 | TRUE |
+---------------------+---------------------+----------+
mysql> EXECUTE stmt USING @id;
ERROR 1748 (HY000): Found a row not matching the given partition set
mysql> SELECT * FROM test_db.test_table;
+----+---------------------+
| id | created_timestamp |
+----+---------------------+
| 1 | 2025-11-05 11:25:46 |
| 1 | 2025-11-05 11:25:48 |
+----+---------------------+
2 rows in set (0.00 sec)
mysql>
How to repeat:
You can reproduce by the following queries.
-- Create a test table with timestamp range partitions
CREATE DATABASE IF NOT EXISTS test_db;
DROP TABLE IF EXISTS test_db.test_table;
SELECT UNIX_TIMESTAMP(TIMESTAMPADD(SECOND, 10, CURRENT_TIMESTAMP)) INTO @partition_time;
SELECT FROM_UNIXTIME(@partition_time) AS 'Partition Timestamp';
SET @table_creation = CONCAT('
CREATE TABLE IF NOT EXISTS test_db.test_table (
`id` int NOT NULL,
`created_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`,`created_timestamp`)
) ENGINE=InnoDB
PARTITION BY RANGE (unix_timestamp(`created_timestamp`)) (
PARTITION pMIN VALUES LESS THAN (', @partition_time ,') ENGINE = InnoDB,
PARTITION pMAX VALUES LESS THAN MAXVALUE ENGINE = InnoDB
)');
PREPARE stmt FROM @table_creation;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SHOW CREATE TABLE test_db.test_table\G
-- Create a prepared statement and run before the rollover
SELECT CURRENT_TIMESTAMP,FROM_UNIXTIME(@partition_time) AS 'Partition Timestamp', IF(@partition_time < UNIX_TIMESTAMP(CURRENT_TIMESTAMP), 'TRUE', 'FALSE') AS 'Rollover';
PREPARE stmt FROM 'INSERT INTO test_db.test_table (id) values (?)';
SET @id=1;
EXECUTE stmt USING @id;
SELECT SLEEP(2);
EXECUTE stmt USING @id;
SELECT * FROM test_db.test_table;
SELECT SLEEP(10);
-- Rerun the prepared statement after the rollover
SELECT CURRENT_TIMESTAMP,FROM_UNIXTIME(@partition_time) AS 'Partition Timestamp', IF(@partition_time < UNIX_TIMESTAMP(CURRENT_TIMESTAMP), 'TRUE', 'FALSE') AS 'Rollover';
EXECUTE stmt USING @id;
SELECT * FROM test_db.test_table;
DEALLOCATE PREPARE stmt;
Suggested fix:
I expect to have the same output in 5.0.41 like the following.
+---------------------+---------------------+----------+
| CURRENT_TIMESTAMP | Partition Timestamp | Rollover |
+---------------------+---------------------+----------+
| 2025-11-05 13:34:04 | 2025-11-05 13:33:42 | TRUE |
+---------------------+---------------------+----------+
1 row in set (0.00 sec)
mysql> EXECUTE stmt USING @id;
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM test_db.test_table;
+----+---------------------+
| id | created_timestamp |
+----+---------------------+
| 1 | 2025-11-05 13:33:39 |
| 1 | 2025-11-05 13:33:41 |
| 1 | 2025-11-05 13:34:04 |
+----+---------------------+
3 rows in set (0.00 sec)
mysql>
Description: An insert prepared statement sticks on a partition once it executed and it fails to insert into other partitions. The problem happens if the table has timestamp based partitions and the partition key (column) has non-constant default value like CURRENT_TIMESTAMP. mysql> EXECUTE stmt USING @id; ERROR 1748 (HY000): Found a row not matching the given partition set mysql> I do not see the problem in 8.0.41 or older then I doubt 8.0.42 introduced the bug. For instance, if you created the following table with timestamp range partition and a prepared statement inserting data into the table, CREATE TABLE `test_table` ( `id` int NOT NULL, `created_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`,`created_timestamp`) ) ENGINE=InnoDB PARTITION BY RANGE (unix_timestamp(`created_timestamp`)) (PARTITION pMIN VALUES LESS THAN (1762341952) ENGINE = InnoDB, PARTITION pMAX VALUES LESS THAN MAXVALUE ENGINE = InnoDB); PREPARE stmt FROM 'INSERT INTO test_table (id) values (?)'; SET @id=1; The first and several executions which insert data into the same partition of the first execution (pMIN), these queries work out. +---------------------+---------------------+----------+ | CURRENT_TIMESTAMP | Partition Timestamp | Rollover | +---------------------+---------------------+----------+ | 2025-11-05 11:25:46 | 2025-11-05 11:25:52 | FALSE | +---------------------+---------------------+----------+ mysql> EXECUTE stmt USING @id; Query OK, 1 row affected (0.00 sec) ... mysql> SELECT * FROM test_table; +----+---------------------+ | id | created_timestamp | +----+---------------------+ | 1 | 2025-11-05 11:25:46 | | 1 | 2025-11-05 11:25:48 | +----+---------------------+ 2 rows in set (0.00 sec) mysql> However, another execution which is going to insert into the different partition (pMAX) gets failed by mismatch partition set error. +---------------------+---------------------+----------+ | CURRENT_TIMESTAMP | Partition Timestamp | Rollover | +---------------------+---------------------+----------+ | 2025-11-05 11:26:00 | 2025-11-05 11:25:52 | TRUE | +---------------------+---------------------+----------+ mysql> EXECUTE stmt USING @id; ERROR 1748 (HY000): Found a row not matching the given partition set mysql> SELECT * FROM test_db.test_table; +----+---------------------+ | id | created_timestamp | +----+---------------------+ | 1 | 2025-11-05 11:25:46 | | 1 | 2025-11-05 11:25:48 | +----+---------------------+ 2 rows in set (0.00 sec) mysql> How to repeat: You can reproduce by the following queries. -- Create a test table with timestamp range partitions CREATE DATABASE IF NOT EXISTS test_db; DROP TABLE IF EXISTS test_db.test_table; SELECT UNIX_TIMESTAMP(TIMESTAMPADD(SECOND, 10, CURRENT_TIMESTAMP)) INTO @partition_time; SELECT FROM_UNIXTIME(@partition_time) AS 'Partition Timestamp'; SET @table_creation = CONCAT(' CREATE TABLE IF NOT EXISTS test_db.test_table ( `id` int NOT NULL, `created_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`,`created_timestamp`) ) ENGINE=InnoDB PARTITION BY RANGE (unix_timestamp(`created_timestamp`)) ( PARTITION pMIN VALUES LESS THAN (', @partition_time ,') ENGINE = InnoDB, PARTITION pMAX VALUES LESS THAN MAXVALUE ENGINE = InnoDB )'); PREPARE stmt FROM @table_creation; EXECUTE stmt; DEALLOCATE PREPARE stmt; SHOW CREATE TABLE test_db.test_table\G -- Create a prepared statement and run before the rollover SELECT CURRENT_TIMESTAMP,FROM_UNIXTIME(@partition_time) AS 'Partition Timestamp', IF(@partition_time < UNIX_TIMESTAMP(CURRENT_TIMESTAMP), 'TRUE', 'FALSE') AS 'Rollover'; PREPARE stmt FROM 'INSERT INTO test_db.test_table (id) values (?)'; SET @id=1; EXECUTE stmt USING @id; SELECT SLEEP(2); EXECUTE stmt USING @id; SELECT * FROM test_db.test_table; SELECT SLEEP(10); -- Rerun the prepared statement after the rollover SELECT CURRENT_TIMESTAMP,FROM_UNIXTIME(@partition_time) AS 'Partition Timestamp', IF(@partition_time < UNIX_TIMESTAMP(CURRENT_TIMESTAMP), 'TRUE', 'FALSE') AS 'Rollover'; EXECUTE stmt USING @id; SELECT * FROM test_db.test_table; DEALLOCATE PREPARE stmt; Suggested fix: I expect to have the same output in 5.0.41 like the following. +---------------------+---------------------+----------+ | CURRENT_TIMESTAMP | Partition Timestamp | Rollover | +---------------------+---------------------+----------+ | 2025-11-05 13:34:04 | 2025-11-05 13:33:42 | TRUE | +---------------------+---------------------+----------+ 1 row in set (0.00 sec) mysql> EXECUTE stmt USING @id; Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM test_db.test_table; +----+---------------------+ | id | created_timestamp | +----+---------------------+ | 1 | 2025-11-05 13:33:39 | | 1 | 2025-11-05 13:33:41 | | 1 | 2025-11-05 13:34:04 | +----+---------------------+ 3 rows in set (0.00 sec) mysql>