Bug #104881 partition_info::set_used_partition not raise error when finding no partition
Submitted: 9 Sep 2021 8:25 Modified: 9 Sep 2021 8:51
Reporter: Hope Lee (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Partitions Severity:S3 (Non-critical)
Version:8.0.26, 5.7.35 OS:Any
Assigned to: CPU Architecture:Any

[9 Sep 2021 8:25] Hope Lee
Description:
When inserting values into partitioned table, the server will go through all values and call partition_info::set_used_partition() to set used partitions in Sql_cmd_insert_base::prepare_inner().

But if we have found a value that can't be inserted into the partitioned table(get_partition_id() returns HA_ERR_NO_PARTITION_FOUND in partition_info::set_used_partition()), we don't mark the error in THD::Diagnostics_area and continue executing.

So the server continues inserting some values and raises an error until it has run into the bad value that cannot find a partition to insert. It's a waste and loss to performance.

How to repeat:
CREATE TABLE tp_r(
  a INT,
  b VARCHAR(25),
  c DATE,
  PRIMARY KEY(a)
) PARTITION BY RANGE (a) (
  PARTITION p0 VALUES LESS THAN (10),
  PARTITION p1 VALUES LESS THAN (100),   
  PARTITION p2 VALUES LESS THAN (1000));

Use gdb to attach the server and set breakpoint.
(gdb) b my_error
Breakpoint 1 at 0x4ea633f: file /mysql-server/mysys/my_error.cc, line 218.

mysql > INSERT INTO tp_r VALUES (13, 'daa', '1992-11-10'), (100000, 'dw', '2009-11-04'), (12, 'dwad', '2123-02-11');
ERROR 1526 (HY000): Table has no partition for value 100000

Thread 49 "mysqld" hit Breakpoint 1, my_error (nr=1526, MyFlags=0) at /mysql-server/mysys/my_error.cc:218
(gdb) bt
#0  my_error (nr=1526, MyFlags=0) at /mysql-server/mysys/my_error.cc:218
#1  0x0000000003d89178 in partition_info::print_no_partition_found (this=0x7fff380b1700, thd=0x7fff38000da0, table_arg=0x7fff380a3440) at /mysql-server/sql/partition_info.cc:1653
#2  0x000000000361abca in Partition_helper::print_partition_error (this=0x7fff380b0b68, error=160) at /mysql-server/sql/partitioning/partition_handler.cc:918
#3  0x0000000005040372 in ha_innopart::print_error (this=0x7fff380af4d8, error=160, errflag=0) at /mysql-server/storage/innobase/handler/ha_innopart.cc:1642
#4  0x0000000003e05b76 in write_record (thd=0x7fff38000da0, table=0x7fff380a3440, info=0x7fffe07aa300, update=0x7fffe07aa280) at /mysql-server/sql/sql_insert.cc:2167
#5  0x0000000003e0172d in Sql_cmd_insert_values::execute_inner (this=0x7fff3800d750, thd=0x7fff38000da0) at /mysql-server/sql/sql_insert.cc:635
#6  0x0000000003804f17 in Sql_cmd_dml::execute (this=0x7fff3800d750, thd=0x7fff38000da0) at /mysql-server/sql/sql_select.cc:574
#7  0x0000000003786237 in mysql_execute_command (thd=0x7fff38000da0, first_level=true) at /mysql-server/sql/sql_parse.cc:3450
#8  0x000000000378b3f4 in dispatch_sql_command (thd=0x7fff38000da0, parser_state=0x7fffe07abad0) at /mysql-server/sql/sql_parse.cc:5033
#9  0x0000000003781987 in dispatch_command (thd=0x7fff38000da0, com_data=0x7fffe07acb80, command=COM_QUERY) at /mysql-server/sql/sql_parse.cc:1863
#10 0x000000000377fdae in do_command (thd=0x7fff38000da0) at /mysql-server/sql/sql_parse.cc:1342
#11 0x000000000395c81b in handle_connection (arg=0x8f75950) at /mysql-server/sql/conn_handler/connection_handler_per_thread.cc:301
#12 0x00000000057020f8 in pfs_spawn_thread (arg=0xaff2cf0) at /mysql-server/storage/perfschema/pfs.cc:2898
#13 0x00007ffff7bc06ca in start_thread () from /lib64/libpthread.so.0
#14 0x00007ffff6071edf in clone () from /lib64/libc.so.6

Suggested fix:
diff --git a/sql/partition_info.cc b/sql/partition_info.cc
index 87d0bc70e59..1f3c77ec4b3 100644
--- a/sql/partition_info.cc
+++ b/sql/partition_info.cc
@@ -512,7 +512,14 @@ bool partition_info::set_used_partition(THD *thd,
     my_bitmap_map *old_map = dbug_tmp_use_all_columns(table, table->read_set);
     const int rc = get_partition_id(this, &part_id, &func_value);
     dbug_tmp_restore_column_map(table->read_set, old_map);
-    if (rc) return true;
+    if (rc) {
+      if (rc == HA_ERR_NO_PARTITION_FOUND) {
+        char buf[100];
+        longlong2str(func_value, buf, 10);
+        my_error(ER_NO_PARTITION_FOR_GIVEN_VALUE, MYF(0), buf);
+      }
+      return true;
+    }
   }

   DBUG_PRINT("info", ("Insert into partition %u", part_id));

The effect after the patch:

mysql > INSERT INTO tp_r VALUES (13, 'daa', '1992-11-10'), (100000, 'dw', '2009-11-04'), (12, 'dwad', '2123-02-11');
ERROR 1526 (HY000): Table has no partition for value 100000

Thread 49 "mysqld" hit Breakpoint 1, my_error (nr=1526, MyFlags=0) at /mysql-server/mysys/my_error.cc:218
(gdb) bt
#0  my_error (nr=1526, MyFlags=0) at /mysql-server/mysys/my_error.cc:218
#1  0x0000000003d8628e in partition_info::set_used_partition (this=0x7fff380021a0, thd=0x7fff3813ab40, fields=..., values=..., info=..., copy_default_values=false, used_partitions=0x7fffe07aa0f0) at /mysql-server/sql/partition_info.cc:519
#2  0x0000000003e03eff in Sql_cmd_insert_base::prepare_inner (this=0x7fff380074e0, thd=0x7fff3813ab40) at /mysql-server/sql/sql_insert.cc:1487
#3  0x000000000380430b in Sql_cmd_dml::prepare (this=0x7fff380074e0, thd=0x7fff3813ab40) at /mysql-server/sql/sql_select.cc:383
#4  0x0000000003804b7e in Sql_cmd_dml::execute (this=0x7fff380074e0, thd=0x7fff3813ab40) at /mysql-server/sql/sql_select.cc:521
#5  0x0000000003786237 in mysql_execute_command (thd=0x7fff3813ab40, first_level=true) at /mysql-server/sql/sql_parse.cc:3450
#6  0x000000000378b3f4 in dispatch_sql_command (thd=0x7fff3813ab40, parser_state=0x7fffe07abad0) at /mysql-server/sql/sql_parse.cc:5033
#7  0x0000000003781987 in dispatch_command (thd=0x7fff3813ab40, com_data=0x7fffe07acb80, command=COM_QUERY) at /mysql-server/sql/sql_parse.cc:1863
#8  0x000000000377fdae in do_command (thd=0x7fff3813ab40) at /mysql-server/sql/sql_parse.cc:1342
#9  0x000000000395c81b in handle_connection (arg=0x8e92c50) at /mysql-server/sql/conn_handler/connection_handler_per_thread.cc:301
#10 0x0000000005702176 in pfs_spawn_thread (arg=0xadd5460) at /mysql-server/storage/perfschema/pfs.cc:2898
#11 0x00007ffff7bc06ca in start_thread () from /lib64/libpthread.so.0
#12 0x00007ffff6071edf in clone () from /lib64/libc.so.6

And quickly the server can return this command without entering executor.
[9 Sep 2021 8:51] MySQL Verification Team
Hello Lee,

Thank you for the report and test case.

regards,
Umesh