commit 478ea006657693b56ffab9828e37eed7f169dc77 Author: Dmitry Lenev Date: Thu Mar 26 21:55:12 2026 +0100 Bug#119309 An insert prepared statement fails to write across partitions. Problem: -------- Inserts using prepared statements or stored procedures into partitioned tables which tried to add row with value of partitioning expression depending on columns DEFAULT CURRENT_TIMESTAMP value sometimes failed with "ERROR 1748 (HY000): Found a row not matching the given partition set" error. Cause: ------ This problem occurred due to incorrect partition pruning in case when preparation of prepared statement/first execution of statement in procedure happened 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 happened at time point where evaluation of the same expression mappend row to another partition (i.e. partition rollover should have happened). What has happened on lower level is that partition pruning at prepare/ first-execution-of-statement-in-procedure time was done by Sql_cmd_insert_base::prepare_inner() call, before tables were locked. So it resulted in partition_info::lock_partitions bitmap being set based on current timestamp value at this point. This bitmap was also applied without re-calculation in case of statement (re-)execution which happened under different current timestamp, resulting in correct target partition for insert being erroneously pruned away. Fix: ---- We 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 depency 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. diff --git a/mysql-test/r/bug119309.result b/mysql-test/r/bug119309.result new file mode 100644 index 00000000000..058a50477c6 --- /dev/null +++ b/mysql-test/r/bug119309.result @@ -0,0 +1,110 @@ +# +# Bug#119309 : An insert prepared statement fails to write across partitions. +# +# +# 1) Test case for the problem that was originally reported. +CREATE TABLE t1 ( +id INT, ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, +PRIMARY KEY(id, ts) +) PARTITION BY RANGE (UNIX_TIMESTAMP(ts)) ( +PARTITION p0 VALUES LESS THAN (UNIX_TIMESTAMP('2026-04-25 00:00:00')), +PARTITION p1 VALUES LESS THAN (UNIX_TIMESTAMP('2026-04-25 12:00:00')), +PARTITION pmax VALUES LESS THAN MAXVALUE); +SET TIMESTAMP = UNIX_TIMESTAMP('2026-04-24 23:00:00'); +PREPARE stmt FROM 'INSERT INTO t1 (id) VALUES (?)'; +SET @id=1; +EXECUTE stmt USING @id; +SET TIMESTAMP = UNIX_TIMESTAMP('2026-04-25 01:00:00'); +SET @id=2; +# Before our fix the below and the next execute statement failed +# with "Found a row not matching the given partition set" error +# due to bad partition pruning. +EXECUTE stmt USING @id; +SET TIMESTAMP = UNIX_TIMESTAMP('2026-04-25 13:00:00'); +SET @id=3; +EXECUTE stmt USING @id; +SELECT * FROM t1; +id ts +1 2026-04-24 23:00:00 +2 2026-04-25 01:00:00 +3 2026-04-25 13:00:00 +DELETE FROM t1; +DEALLOCATE PREPARE stmt; +# +# 2) Similar test case involving stored procedure. +CREATE PROCEDURE sp1 (i INT) INSERT INTO t1 (id) VALUES (i); +SET TIMESTAMP = UNIX_TIMESTAMP('2026-04-24 23:00:00'); +CALL sp1(4); +SET TIMESTAMP = UNIX_TIMESTAMP('2026-04-25 01:00:00'); +# Again before the fix the below statement failed for the same reason. +CALL sp1(5); +SELECT * FROM t1; +id ts +4 2026-04-24 23:00:00 +5 2026-04-25 01:00:00 +DELETE FROM t1; +DROP PROCEDURE sp1; +# +# 3) Check that partition pruning still works for case when partitioning +# expression depends on DEFAULT CURRENT_TIMESTAMP value. +# +# 3.1) First, let us check how it works for regular statement. +SET TIMESTAMP = UNIX_TIMESTAMP('2026-04-24 23:00:00'); +EXPLAIN INSERT INTO t1 (id) VALUES (6); +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 INSERT t1 p0 ALL NULL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 insert into `test`.`t1` (`test`.`t1`.`id`) values (6) +SET TIMESTAMP = UNIX_TIMESTAMP('2026-04-25 01:00:00'); +EXPLAIN INSERT INTO t1 (id) VALUES (7); +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 INSERT t1 p1 ALL NULL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 insert into `test`.`t1` (`test`.`t1`.`id`) values (7) +# +# 3.2) Now check the prepared statement case. Note that we are not +# actually testing the prepared statement from original bug report. +# But partition pruning for it should work in the same way and it +# is as close as we can get without too much hassle. +SET TIMESTAMP = UNIX_TIMESTAMP('2026-04-24 23:00:00'); +PREPARE stmt FROM 'EXPLAIN INSERT INTO t1 (id) VALUES (?)'; +SET @id=8; +EXECUTE stmt USING @id; +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 INSERT t1 p0 ALL NULL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 insert into `test`.`t1` (`test`.`t1`.`id`) values (?) +SET TIMESTAMP = UNIX_TIMESTAMP('2026-04-25 01:00:00'); +EXECUTE stmt USING @id; +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 INSERT t1 p1 ALL NULL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 insert into `test`.`t1` (`test`.`t1`.`id`) values (?) +SET TIMESTAMP = UNIX_TIMESTAMP('2026-04-25 13:00:00'); +EXECUTE stmt USING @id; +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 INSERT t1 pmax ALL NULL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 insert into `test`.`t1` (`test`.`t1`.`id`) values (?) +DEALLOCATE PREPARE stmt; +# +# 3.3) And now similar check for pruning in stored routines. +# Same comment applies. +CREATE PROCEDURE sp1 (i INT) EXPLAIN INSERT INTO t1 (id) VALUES (i); +SET TIMESTAMP = UNIX_TIMESTAMP('2026-04-24 23:00:00'); +CALL sp1(9); +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 INSERT t1 p0 ALL NULL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 insert into `test`.`t1` (`test`.`t1`.`id`) values (i@0) +SET TIMESTAMP = UNIX_TIMESTAMP('2026-04-25 01:00:00'); +CALL sp1(9); +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 INSERT t1 p1 ALL NULL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 insert into `test`.`t1` (`test`.`t1`.`id`) values (i@0) +DROP PROCEDURE sp1; +# +# Clean-up. +SET TIMESTAMP = DEFAULT; +DROP TABLE t1; diff --git a/mysql-test/t/bug119309.test b/mysql-test/t/bug119309.test new file mode 100644 index 00000000000..0a7ac667298 --- /dev/null +++ b/mysql-test/t/bug119309.test @@ -0,0 +1,95 @@ +--echo # +--echo # Bug#119309 : An insert prepared statement fails to write across partitions. +--echo # + +--echo # +--echo # 1) Test case for the problem that was originally reported. +CREATE TABLE t1 ( + id INT, ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, + PRIMARY KEY(id, ts) +) PARTITION BY RANGE (UNIX_TIMESTAMP(ts)) ( + PARTITION p0 VALUES LESS THAN (UNIX_TIMESTAMP('2026-04-25 00:00:00')), + PARTITION p1 VALUES LESS THAN (UNIX_TIMESTAMP('2026-04-25 12:00:00')), + PARTITION pmax VALUES LESS THAN MAXVALUE); + +SET TIMESTAMP = UNIX_TIMESTAMP('2026-04-24 23:00:00'); +PREPARE stmt FROM 'INSERT INTO t1 (id) VALUES (?)'; +SET @id=1; +EXECUTE stmt USING @id; + +SET TIMESTAMP = UNIX_TIMESTAMP('2026-04-25 01:00:00'); +SET @id=2; +--echo # Before our fix the below and the next execute statement failed +--echo # with "Found a row not matching the given partition set" error +--echo # due to bad partition pruning. +EXECUTE stmt USING @id; + +SET TIMESTAMP = UNIX_TIMESTAMP('2026-04-25 13:00:00'); +SET @id=3; +EXECUTE stmt USING @id; + +SELECT * FROM t1; +DELETE FROM t1; +DEALLOCATE PREPARE stmt; + +--echo # +--echo # 2) Similar test case involving stored procedure. +CREATE PROCEDURE sp1 (i INT) INSERT INTO t1 (id) VALUES (i); + +SET TIMESTAMP = UNIX_TIMESTAMP('2026-04-24 23:00:00'); +CALL sp1(4); + +SET TIMESTAMP = UNIX_TIMESTAMP('2026-04-25 01:00:00'); +--echo # Again before the fix the below statement failed for the same reason. +CALL sp1(5); + +SELECT * FROM t1; +DELETE FROM t1; +DROP PROCEDURE sp1; + +--echo # +--echo # 3) Check that partition pruning still works for case when partitioning +--echo # expression depends on DEFAULT CURRENT_TIMESTAMP value. +--echo # +--echo # 3.1) First, let us check how it works for regular statement. +SET TIMESTAMP = UNIX_TIMESTAMP('2026-04-24 23:00:00'); +EXPLAIN INSERT INTO t1 (id) VALUES (6); + +SET TIMESTAMP = UNIX_TIMESTAMP('2026-04-25 01:00:00'); +EXPLAIN INSERT INTO t1 (id) VALUES (7); + +--echo # +--echo # 3.2) Now check the prepared statement case. Note that we are not +--echo # actually testing the prepared statement from original bug report. +--echo # But partition pruning for it should work in the same way and it +--echo # is as close as we can get without too much hassle. +SET TIMESTAMP = UNIX_TIMESTAMP('2026-04-24 23:00:00'); +PREPARE stmt FROM 'EXPLAIN INSERT INTO t1 (id) VALUES (?)'; +SET @id=8; +EXECUTE stmt USING @id; + +SET TIMESTAMP = UNIX_TIMESTAMP('2026-04-25 01:00:00'); +EXECUTE stmt USING @id; + +SET TIMESTAMP = UNIX_TIMESTAMP('2026-04-25 13:00:00'); +EXECUTE stmt USING @id; + +DEALLOCATE PREPARE stmt; + +--echo # +--echo # 3.3) And now similar check for pruning in stored routines. +--echo # Same comment applies. +CREATE PROCEDURE sp1 (i INT) EXPLAIN INSERT INTO t1 (id) VALUES (i); + +SET TIMESTAMP = UNIX_TIMESTAMP('2026-04-24 23:00:00'); +CALL sp1(9); + +SET TIMESTAMP = UNIX_TIMESTAMP('2026-04-25 01:00:00'); +CALL sp1(9); + +DROP PROCEDURE sp1; + +--echo # +--echo # Clean-up. +SET TIMESTAMP = DEFAULT; +DROP TABLE t1; diff --git a/sql/partition_info.cc b/sql/partition_info.cc index 8fc560ef195..ba8e6f7354b 100644 --- a/sql/partition_info.cc +++ b/sql/partition_info.cc @@ -522,8 +522,26 @@ bool partition_info::set_used_partition( TODO: avoid setting non partitioning fields default value, to avoid overhead. Not yet done, since mostly only one DEFAULT function per table, or at least very few such columns. + + In case of prepared statements and stored routines we can't do pruning + for partition expression dependent on DEFAULT CURRENT_TIMESTAMP values + before tables are locked. + + Doing so would result in partition_info::lock_partitions bitmap being + set at prepare/first-execution-of-procedure time based on current + timestamp value at this point. This bitmap would be also applied in + case of statement (re-)execution which might happen under different + current timestamp, resulting in correct target partition for insert + being erroneously pruned away. + + So, instead, in such a case, we simply rely on pruning which is done + after tables are locked. It is re-done for each statement (re-)execution + and only affects partition_info::read_partitions, which is recalculated + for further re-executions. */ if (info.function_defaults_apply_on_columns(&full_part_field_set)) { + if (!thd->stmt_arena->is_regular() && !tables_locked) return true; + if (info.set_function_defaults(table)) return true; } {