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.
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.