From 8493044c4ccf18f7f290a83d0ccaa396c470c1ac Mon Sep 17 00:00:00 2001 From: Nicholas Othieno Date: Tue, 8 Nov 2022 20:45:55 +0000 Subject: [PATCH] Disable using spatial keys hints to prevent an optimizer induced crash in explain statements Description =========== When a spatial key is used as a hint in an explain query, the MySQL 5.7 engine crashes in the function "Explain_table_base::explain_key_and_len_index" in the file sql/opt_explain.cc. This crash is due to MAX_INDEX = 64 being used as the key value in "Explain_table_base::explain_key_and_len_index", which causes an incorrect key_len value to be assigned leading to buffer overflow and memory corruption in aforementioned function. (gdb) bt \#0 Explain_table_base::explain_key_and_len_index (this=0x7f3dde5f0260, key=64, key_length=3315985466, key_parts=4) at /MySQL/sql/opt_explain.cc:903 Sometimes too setting the key to MAX_INDEX = 64 causes the mysqld process to attempt to access inaccessible memory, leading to a crash. Although the function "Explain_table_base::explain_key_and_len_index" appears to have an assert to check that MAX_INDEX != 64, the assert does not fire in the release build. The reason key is set to MAX_INDEX = 64 is because the function "TABLE_LIST::process_index_hints" in sql/table.cc sets the spatial key from the index hint as the only usable key. This spatial key is then rejected in the function "find_shortest_key" in sql/sql_optimizer.cc because it is a spatial key. Instead "find_shortest_key" sets MAX_INDEX = 64 as the best key to use, and eventually when "Explain_table_base::explain_key_and_len_index" tries to use this best key, a crash happens. See the excerpt below from "find_shortest_key": if (key_ref.key_length < min_length && !(key_ref.flags & HA_SPATIAL)) { min_length = key_ref.key_length; best = nr; } To solve this problem we disable using spatial indices as hints in joins from within the function "TABLE_LIST::process_index_hints" in sql/table.cc. Testing ======= To test this change we added a new test case that provides a index hint on a join to use a spatial index in an explain query. In the result, the spatial key is ignored and instead a different key is selected to scan the table. License ======= All new code of the whole pull request, including one or several files that are either new files or modified ones, are contributed under the BSD-new license. I am contributing on behalf of my employer Amazon Web Services, Inc. --- ...able_index_hints_on_spatial_indices.result | 25 +++++++++++++++++ ...isable_index_hints_on_spatial_indices.test | 28 +++++++++++++++++++ sql/table.cc | 4 +-- 3 files changed, 55 insertions(+), 2 deletions(-) create mode 100644 mysql-test/r/disable_index_hints_on_spatial_indices.result create mode 100644 mysql-test/t/disable_index_hints_on_spatial_indices.test diff --git a/mysql-test/r/disable_index_hints_on_spatial_indices.result b/mysql-test/r/disable_index_hints_on_spatial_indices.result new file mode 100644 index 000000000000..7b1e156a42ac --- /dev/null +++ b/mysql-test/r/disable_index_hints_on_spatial_indices.result @@ -0,0 +1,25 @@ +CREATE DATABASE db1; +USE db1; +CREATE TABLE `t1` ( +`column1` int(11) NOT NULL AUTO_INCREMENT, +`column2` polygon NOT NULL, +PRIMARY KEY (`column1`), +SPATIAL KEY `spatial_key1` (`column2`) +) ENGINE=InnoDB; +CREATE TABLE `t2` ( +`col_id1` char(36) CHARACTER SET ascii NOT NULL, +`column1` int(11) NOT NULL, +PRIMARY KEY (`col_id1`), +KEY `another_key` (`column1`) +) ENGINE=InnoDB; +EXPLAIN +SELECT csdj.col_id1, ai.column1 +FROM db1.t2 csdj JOIN +db1.t1 ai +USE INDEX (spatial_key1) ON csdj.column1 = ai.column1; +id select_type table partitions type possible_keys key key_len ref rows filtered Extra +1 SIMPLE csdj NULL index another_key another_key 4 NULL 1 100.00 Using index +1 SIMPLE ai NULL eq_ref PRIMARY PRIMARY 4 db1.csdj.column1 1 100.00 Using index +Warnings: +Note 1003 /* select#1 */ select `db1`.`csdj`.`col_id1` AS `col_id1`,`db1`.`ai`.`column1` AS `column1` from `db1`.`t2` `csdj` join `db1`.`t1` `ai` USE INDEX (`spatial_key1`) where (`db1`.`ai`.`column1` = `db1`.`csdj`.`column1`) +DROP DATABASE db1; diff --git a/mysql-test/t/disable_index_hints_on_spatial_indices.test b/mysql-test/t/disable_index_hints_on_spatial_indices.test new file mode 100644 index 000000000000..3a1c9b33c868 --- /dev/null +++ b/mysql-test/t/disable_index_hints_on_spatial_indices.test @@ -0,0 +1,28 @@ +-- If spatial index hints are enabled, this EXPLAIN will lead to +-- an engine crash + +CREATE DATABASE db1; +USE db1; + +CREATE TABLE `t1` ( +`column1` int(11) NOT NULL AUTO_INCREMENT, +`column2` polygon NOT NULL, +PRIMARY KEY (`column1`), +SPATIAL KEY `spatial_key1` (`column2`) +) ENGINE=InnoDB; + +CREATE TABLE `t2` ( +`col_id1` char(36) CHARACTER SET ascii NOT NULL, +`column1` int(11) NOT NULL, +PRIMARY KEY (`col_id1`), +KEY `another_key` (`column1`) +) ENGINE=InnoDB; + + +EXPLAIN +SELECT csdj.col_id1, ai.column1 +FROM db1.t2 csdj JOIN +db1.t1 ai +USE INDEX (spatial_key1) ON csdj.column1 = ai.column1; + +DROP DATABASE db1; diff --git a/sql/table.cc b/sql/table.cc index 0e1029ae7ef5..2d7df881c460 100644 --- a/sql/table.cc +++ b/sql/table.cc @@ -7211,8 +7211,8 @@ bool TABLE_LIST::process_index_hints(TABLE *tbl) pos--; - /* add to the appropriate clause mask */ - if (hint->clause & INDEX_HINT_MASK_JOIN) + /* add to the appropriate clause mask, except for spatial index hints that are ignored */ + if (hint->clause & INDEX_HINT_MASK_JOIN & (tbl->key_info[pos].flags != HA_SPATIAL)) index_join[hint->type].set_bit (pos); if (hint->clause & INDEX_HINT_MASK_ORDER) index_order[hint->type].set_bit (pos);