MySQL Bugs: #119309: An insert prepared statement fails to write across partitions

Bug #119309	An insert prepared statement fails to write across partitions
Submitted:	5 Nov 2025 13:37	Modified:	20 Jan 10:16
Reporter:	Ivo Matsuo	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S3 (Non-critical)
Version:	8.0.42	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	insert, partition, prepare statement

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>

Typo in the Suggested fix section: 5.0.41 -> 8.0.41

Tags added

Thank you for the bug report.
Verified as described.

Hello!

We are contributing a patch which fixes this bug (analysis of the cause of the bug can be found in the commit message of the patch).

The patch is against 8.0.45 source but should apply without issues to recent 8.4/9.x versions as well.

Straightforward fix for bug#119309. (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: ps-10448-bug119309-contrib.patch (text/x-patch), 11.58 KiB.

Actually, it is probably a good idea to paste the analysis directly here as well.

So what I have found is that this problem occurs due to incorrect partition pruning in case when preparation of prepared statement/first execution of statement in procedure happens at time point for which evaluation of partitioning expression depending on DEFAULT CURRENT_TIMESTAMP value mapped row to one partition, while (re-)execution of such statement happens at time point
where evaluation of the same expression mapped row to another partition (i.e. partition rollover should have happened).

What happens on lower level is that partition pruning at prepare/first-execution-of-statement-in-procedure time is done by Sql_cmd_insert_base::prepare_inner() call, before tables were locked. So it results in partition_info::lock_partitions bitmap being set based on current timestamp value at this point. This bitmap is also applied without re-calculation in case of statement (re-)execution which happens under different current timestamp, resulting in correct target partition for insert being erroneously pruned away.

The contributed patch implements the following fix for this problem:

Solve the problem by avoiding doing partitioning pruning in case when partition expression depends on DEFAULT CURRENT_TIMESTAMP value before tables are locked. And thus avoid touching partition_info::lock_partitions in this case. Instead in such a case pruning is done at Sql_cmd_insert_values::execute_inner() time (similarly to how it is done for case of dependency on prepared statement parameter, for example). It is re-done for each statement (re-)execution and only affects/involves partition_info::read_partitions, which is recalculated
for further re-executions.

The old approach to pruning is still used for regular statements, since it is safe in their case and also allows to avoid unnecessary overhead.