Bug #104981 Raise error that partition' name to be dropped doesn't exist at an earlier stage
Submitted: 18 Sep 7:14 Modified: 20 Sep 6:40
Reporter: Hope Lee (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Partitions Severity:S3 (Non-critical)
Version:8.0.26 OS:Any
Assigned to: CPU Architecture:Any

[18 Sep 7:14] Hope Lee
Description:
We can quickly return if the partition name the DDL is going to alter doesn't exist in the partitioned table at open_table() stage. Don't need to wait until checker in prep_alter_part_table(). It's a waste and loss to performance.

How to repeat:
CREATE TABLE t1 (
	id INT NOT NULL AUTO_INCREMENT,
	name VARCHAR(50),
	purchased DATE,
	KEY (id)
) PARTITION BY RANGE (YEAR(purchased)) (
	PARTITION p0 VALUES LESS THAN (1990),
	PARTITION p1 VALUES LESS THAN (1995),
	PARTITION p2 VALUES LESS THAN (2000),
	PARTITION p3 VALUES LESS THAN (2005)
);

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-8.0.26 > ALTER TABLE t1 DROP PARTITION p4;

Thread 47 "mysqld" hit Breakpoint 1, my_error (nr=1507, MyFlags=0) at /mysql-server/mysys/my_error.cc:218
218   DBUG_TRACE;
(gdb) bt
#0  my_error (nr=1507, MyFlags=0) at /mysql-server/mysys/my_error.cc:218
#1  0x000000000379ed77 in prep_alter_part_table (thd=0x7fff34000da0, table=0x7fff34127b20, alter_info=0x7fffe2acb210, create_info=0x7fffe2acb380, alter_ctx=0x7fffe2ac98a0, partition_changed=0x7fffe2ac97cf, new_part_info=0x7fffe2ac97c0) at /mysql-server/sql/sql_partition.cc:4713
#2  0x0000000003862bf9 in mysql_alter_table (thd=0x7fff34000da0, new_db=0x7fff34094c68 "test", new_name=0x0, create_info=0x7fffe2acb380, table_list=(TABLE_LIST *) 0x7fff34094640 table_name = t1, alter_info=0x7fffe2acb210) at /mysql-server/sql/sql_table.cc:16340
#3  0x0000000003de4c94 in Sql_cmd_alter_table::execute (this=0x7fff34094c70, thd=0x7fff34000da0) at /mysql-server/sql/sql_alter.cc:349
#4  0x000000000378945a in mysql_execute_command (thd=0x7fff34000da0, first_level=true) at /mysql-server/sql/sql_parse.cc:4436
#5  0x000000000378b3f4 in dispatch_sql_command (thd=0x7fff34000da0, parser_state=0x7fffe2accad0) at /mysql-server/sql/sql_parse.cc:5033
#6  0x0000000003781987 in dispatch_command (thd=0x7fff34000da0, com_data=0x7fffe2acdb80, command=COM_QUERY) at /mysql-server/sql/sql_parse.cc:1863
#7  0x000000000377fdae in do_command (thd=0x7fff34000da0) at /mysql-server/sql/sql_parse.cc:1342
#8  0x000000000395c81b in handle_connection (arg=0x8f22050) at /mysql-server/sql/conn_handler/connection_handler_per_thread.cc:301
#9  0x0000000005702176 in pfs_spawn_thread (arg=0xadb1690) at /mysql-server/storage/perfschema/pfs.cc:2898
#10 0x00007ffff7bc06ca in start_thread () from /lib64/libpthread.so.0
#11 0x00007ffff6071edf in clone () from /lib64/libc.so.6

Suggested fix:
diff --git a/sql/parse_tree_nodes.cc b/sql/parse_tree_nodes.cc
index d25cd950e98..d0f639af3e8 100644
--- a/sql/parse_tree_nodes.cc
+++ b/sql/parse_tree_nodes.cc
@@ -2688,7 +2688,7 @@ static bool init_alter_table_stmt(Table_ddl_parse_context *pc,
   LEX *lex = pc->thd->lex;
   if (!lex->query_block->add_table_to_list(
           pc->thd, table_name, nullptr, TL_OPTION_UPDATING, TL_READ_NO_INSERT,
-          MDL_SHARED_UPGRADABLE))
+          MDL_SHARED_UPGRADABLE, NULL, &pc->alter_info->partition_names))
     return true;
   lex->query_block->init_order();
   pc->create_info->db_type = nullptr;

The effect after the patch:

mysql-8.0.26 > ALTER TABLE t1 DROP PARTITION p4;

Thread 47 "mysqld" hit Breakpoint 1, my_error (nr=1735, MyFlags=0) at /mysql-server/mysys/my_error.cc:218
218   DBUG_TRACE;
(gdb) bt
#0  my_error (nr=1735, MyFlags=0) at /mysql-server/mysys/my_error.cc:218
#1  0x0000000003d85592 in partition_info::add_named_partition (this=0x7fff340d5100, part_name=0x7fff3400b6a0 "p4", length=2) at /mysql-server/sql/partition_info.cc:179
#2  0x0000000003d85893 in partition_info::set_read_partitions (this=0x7fff340d5100, partition_names=0x7fff3400b848) at /mysql-server/sql/partition_info.cc:241
#3  0x0000000003d85a30 in partition_info::set_partition_bitmaps (this=0x7fff340d5100, table_list=(TABLE_LIST *) 0x7fff3400bee8 table_name = t1) at /mysql-server/sql/partition_info.cc:280
#4  0x0000000003685f7b in open_table (thd=0x7fff34000da0, table_list=(TABLE_LIST *) 0x7fff3400bee8 table_name = t1, ot_ctx=0x7fffe2ac8840) at /mysql-server/sql/sql_base.cc:3456
#5  0x0000000003689579 in open_and_process_table (thd=0x7fff34000da0, lex=0x7fff34003ce0, tables=0x7fff3400bee8, counter=0x7fffe2aca6d4, prelocking_strategy=0x7fffe2aca6d8, has_prelocking_list=false, ot_ctx=0x7fffe2ac8840) at /mysql-server/sql/sql_base.cc:5027
#6  0x000000000368ae4a in open_tables (thd=0x7fff34000da0, start=0x7fffe2ac8918, counter=0x7fffe2aca6d4, flags=0, prelocking_strategy=0x7fffe2aca6d8) at /mysql-server/sql/sql_base.cc:5819
#7  0x00000000038614de in mysql_alter_table (thd=0x7fff34000da0, new_db=0x7fff3400c510 "test", new_name=0x0, create_info=0x7fffe2acb380, table_list=(TABLE_LIST *) 0x7fff3400bee8 table_name = t1, alter_info=0x7fffe2acb210) at /mysql-server/sql/sql_table.cc:15885
#8  0x0000000003de4ca2 in Sql_cmd_alter_table::execute (this=0x7fff3400c518, thd=0x7fff34000da0) at /mysql-server/sql/sql_alter.cc:349
#9  0x000000000378945a in mysql_execute_command (thd=0x7fff34000da0, first_level=true) at /mysql-server/sql/sql_parse.cc:4436
#10 0x000000000378b3f4 in dispatch_sql_command (thd=0x7fff34000da0, parser_state=0x7fffe2accad0) at /mysql-server/sql/sql_parse.cc:5033
#11 0x0000000003781987 in dispatch_command (thd=0x7fff34000da0, com_data=0x7fffe2acdb80, command=COM_QUERY) at /mysql-server/sql/sql_parse.cc:1863
#12 0x000000000377fdae in do_command (thd=0x7fff34000da0) at /mysql-server/sql/sql_parse.cc:1342
#13 0x000000000395c81b in handle_connection (arg=0x8f81850) at /mysql-server/sql/conn_handler/connection_handler_per_thread.cc:301
#14 0x0000000005702184 in pfs_spawn_thread (arg=0xadbcdd0) at /mysql-server/storage/perfschema/pfs.cc:2898
#15 0x00007ffff7bc06ca in start_thread () from /lib64/libpthread.so.0
#16 0x00007ffff6071edf in clone () from /lib64/libc.so.6

In this case, we can raise the error at an earlier stage and then return quickly.
[20 Sep 6:40] MySQL Verification Team
Hello Lee,

Thank you for the report and test case.

regards,
Umesh