摘要:
mysql列存储引擎-支持自定义函数-现有代码分析, 分析现有代码, 以确定当前引擎对于自定义函数的支持程度, 作为下一步指定开发计划的依据。
当前列存储引擎的调用自定义函数:
配置参数设置:
[mysqld]
tianmu_ini_allowmysqlquerypath=0
log_bin_trust_function_creators=1
执行SQL报错现象:
mysql> select
-> a.employee_id,
-> get_value(b.employee_id)
-> from
-> employees a
-> left join employees b on
-> a.employee_id = b.employee_id
-> and b.employee_name = 'David Tian';
ERROR 6 (HY000): The query includes syntax that is not supported by the storage engine. Either restructure the query with supported syntax, or enable the MySQL core::Query Path in config file to execute the query with reduced performance.
ERROR 6 (HY000): The query includes syntax that is not supported by the storage engine. Either restructure the query with supported syntax, or enable the MySQL core::Query Path in config file to execute the query with reduced performance.
错误日志直接对应的代码:
ha_rcengine.cpp:74
int TIANMU_HandleSelect(THD *thd, LEX *lex, Query_result *&result, ulong setup_tables_done_option, int &res,
int &optimize_after_tianmu, int &tianmu_free_join, int with_insert=false) {
int ret = RCBASE_QUERY_ROUTE;
try {
// handle_select_ret is introduced here because in case of some exceptions
// (e.g. thrown from ForbiddenMySQLQueryPath) we want to return
// RCBASE_QUERY_ROUTE
int handle_select_ret = ha_rcengine_->HandleSelect(thd, lex, result, setup_tables_done_option, res, optimize_after_tianmu,
tianmu_free_join, with_insert);
if (handle_select_ret == RETURN_QUERY_TO_MYSQL_ROUTE && AtLeastOneTIANMUTableInvolved(lex) &&
ForbiddenMySQLQueryPath(lex)) {
my_message(static_cast<int>(common::ErrorCode::UNKNOWN_ERROR),
"The query includes syntax that is not supported by the storage engine. \
Either restructure the query with supported syntax, or enable the MySQL core::Query Path in config file to execute the query with reduced performance.",
MYF(0));
handle_select_ret = RCBASE_QUERY_ROUTE;
}
ret = handle_select_ret;
} catch (std::exception &e) {
my_message(static_cast<int>(common::ErrorCode::UNKNOWN_ERROR), e.what(), MYF(0));
TIANMU_LOG(LogCtl_Level::ERROR, "HandleSelect Error: %s", e.what());
} catch (...) {
my_message(static_cast<int>(common::ErrorCode::UNKNOWN_ERROR), "An unknown system exception error caught.", MYF(0));
TIANMU_LOG(LogCtl_Level::ERROR, "An unknown system exception error caught.");
}
return ret;
}
识别出无法解析导致返回SQL层的逻辑的堆栈:
(gdb) bt
#0 Tianmu::core::MysqlExpression::SanityAggregationCheck (item=0x7ff208016fd8, fields=std::set with 0 elements, toplevel=true, has_aggregation=0x0)
at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-128/storage/tianmu/core/mysql_expression.cpp:128
#1 0x0000000002a25d8e in Tianmu::core::Query::WrapMysqlExpression (this=0x7ff3dc00a8e0, item=0x7ff208016fd8, tmp_table=..., expr=@0x7ff3dc00a428: 0x0, in_where=false, aggr_used=false)
at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-128/storage/tianmu/core/query_compile.cpp:651
#2 0x0000000002a24aa0 in Tianmu::core::Query::AddFields (this=0x7ff3dc00a8e0, fields=..., tmp_table=..., group_by_clause=false, num_of_added_fields=@0x7ff3dc00a694: 0, ignore_minmax=false,
aggregation_used=@0x7ff3dc00a5df: false) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-128/storage/tianmu/core/query_compile.cpp:473
#3 0x0000000002a27c4d in Tianmu::core::Query::Compile (this=0x7ff3dc00a8e0, compiled_query=0x7ff3dc00a810, selects_list=0x7ff208015840, last_distinct=0x0, res_tab=0x0, ignore_limit=false,
left_expr_for_subselect=0x0, oper_for_subselect=0x0, ignore_minmax=false, for_subq_in_where=false)
at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-128/storage/tianmu/core/query_compile.cpp:1038
#4 0x00000000029d6654 in Tianmu::core::Engine::Execute (this=0x4c08160, thd=0x7ff208011f20, lex=0x7ff208014098, result_output=0x7ff20801ef38, unit_for_union=0x0)
at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-128/storage/tianmu/core/engine_execute.cpp:380
#5 0x00000000029d5cce in Tianmu::core::Engine::HandleSelect (this=0x4c08160, thd=0x7ff208011f20, lex=0x7ff208014098, result=@0x7ff3dc00adf8: 0x7ff20801ef38, setup_tables_done_option=0,
res=@0x7ff3dc00adf4: 0, optimize_after_tianmu=@0x7ff3dc00adec: 1, tianmu_free_join=@0x7ff3dc00adf0: 1, with_insert=0)
at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-128/storage/tianmu/core/engine_execute.cpp:232
#6 0x0000000002abb4dc in Tianmu::dbhandler::TIANMU_HandleSelect (thd=0x7ff208011f20, lex=0x7ff208014098, result=@0x7ff3dc00adf8: 0x7ff20801ef38, setup_tables_done_option=0, res=@0x7ff3dc00adf4: 0,
optimize_after_tianmu=@0x7ff3dc00adec: 1, tianmu_free_join=@0x7ff3dc00adf0: 1, with_insert=0) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-128/storage/tianmu/handler/ha_rcengine.cpp:82
#7 0x00000000022ff7c7 in execute_sqlcom_select (thd=0x7ff208011f20, all_tables=0x7ff20800fb88) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-128/sql/sql_:5182
#8 0x00000000022f95ef in mysql_execute_command (thd=0x7ff208011f20, first_level=true) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-128/sql/sql_:2831
#9 0x00000000023004ce in mysql_parse (thd=0x7ff208011f20, parser_state=0x7ff3dc00bf00) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-128/sql/sql_:5621
#10 0x00000000022f6918 in dispatch_command (thd=0x7ff208011f20, com_data=0x7ff3dc00c660, command=COM_QUERY) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-128/sql/sql_:1495
#11 0x00000000022f5abb in do_command (thd=0x7ff208011f20) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-128/sql/sql_:1034
#12 0x00000000023f99fb in handle_connection (arg=0x6ef92a0) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-128/sql/conn_handler/connection_handler_per_:313
#13 0x0000000002915631 in pfs_spawn_thread (arg=0x6ee40a0) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-128/storage/perfschema/:2197
#14 0x00007ff426fdfea5 in start_thread () from /lib64/libpthread.so.0
#15 0x00007ff424503b0d in clone () from /lib64/libc.so.6
当前列存储引擎对自定义函数的支持程度:
仅支持无参数的自定义函数的调用
自定义函数的参数个数拦截:
case Item::FUNC_ITEM: {
if (dynamic_cast<Item_func_trig_cond *>(item) != NULL) return false;
// currently stored procedures not supported
if (dynamic_cast<Item_func_sp *>(item) != NULL) {
Item_func_sp *ifunc = dynamic_cast<Item_func_sp *>(item);
if (ifunc->argument_count() != 0) return false;
return true;
}
// Otherwise, it's a regular function/operator (hopefully)
Item_func *ifunc = dynamic_cast<Item_func *>(item);
bool correct = true;
for (uint i = 0; i < ifunc->argument_count(); i++) {
Item **args = ifunc->arguments();
correct = (correct && SanityAggregationCheck(args[i], fields, false, has_aggregation));
if (!correct) break;
}
return correct;
}
创建无参数的自定义函数调用测试:
DELIMITER $$
CREATE FUNCTION get_value_no_args() RETURNS INT
BEGIN
DECLARE FINALVAR INT;
SELECT employee_salary INTO FINALVAR FROM employees WHERE employee_id=1;
RETURN FINALVAR;
END $$
DELIMITER ;
select
a.employee_id as a_id,
a.employee_name as a_name,
b.employee_id as b_id,
b.employee_name as b_name,
get_value_no_args()
from
employees a
left join employees b on
a.employee_id = b.employee_id
and b.employee_name = 'David Tian';
+------+------------+------+------------+---------------------+
| a_id | a_name | b_id | b_name | get_value_no_args() |
+------+------------+------+------------+---------------------+
| 1 | David Tian | 1 | David Tian | 3000 |
| 2 | James | NULL | NULL | 3000 |
+------+------------+------+------------+---------------------+
2 rows in set (0.00 sec)
调用堆栈:
(gdb) bt
#0 mysql_execute_command (thd=0x7fd774010540, first_level=false) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-128/sql/sql_:2460
#1 0x0000000002264b88 in sp_instr_stmt::exec_core (this=0x7fd7740bf910, thd=0x7fd774010540, nextp=0x7fd95dc87c08) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-128/sql/sp_:1027
#2 0x0000000002263a2b in sp_lex_instr::reset_lex_and_exec_core (this=0x7fd7740bf910, thd=0x7fd774010540, nextp=0x7fd95dc87c08, open_tables=false)
at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-128/sql/sp_:452
#3 0x0000000002264412 in sp_lex_instr::validate_lex_and_execute_core (this=0x7fd7740bf910, thd=0x7fd774010540, nextp=0x7fd95dc87c08, open_tables=false)
at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-128/sql/sp_:753
#4 0x00000000022648e0 in sp_instr_stmt::execute (this=0x7fd7740bf910, thd=0x7fd774010540, nextp=0x7fd95dc87c08) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-128/sql/sp_:938
#5 0x000000000225d5a5 in sp_head::execute (this=0x7fd7740b7040, thd=0x7fd774010540, merge_da_on_success=true) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-128/sql/sp_:796
#6 0x000000000225e567 in sp_head::execute_function (this=0x7fd7740b7040, thd=0x7fd774010540, argp=0x7fd774006e38, argcount=0, return_value_fld=0x7fd77401dca0)
at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-128/sql/sp_:1287
#7 0x0000000001d766a9 in Item_func_sp::execute_impl (this=0x7fd774006d90, thd=0x7fd774010540) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-128/sql/item_:8706
#8 0x0000000001d763fc in Item_func_sp::execute (this=0x7fd774006d90) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-128/sql/item_:8637
#9 0x0000000001d5d0f6 in Item_func_sp::val_int (this=0x7fd774006d90) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-128/sql/item_func.h:2741
#10 0x0000000002d1c30e in Tianmu::core::MysqlExpression::ItemInt2ValueOrNull (item=0x7fd774006d90)
at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-128/storage/tianmu/core/mysql_expression.cpp:565
#11 0x0000000002d1bb1f in Tianmu::core::MysqlExpression::Evaluate (this=0x7fd7740b5400) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-128/storage/tianmu/core/mysql_expression.cpp:469
#12 0x0000000002b1cdbf in Tianmu::vcolumn::ExpressionColumn::IsNullImpl (this=0x7fd7740cc4a0, mit=...)
at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-128/storage/tianmu/vc/expr_column.cpp:122
#13 0x0000000002a81b48 in Tianmu::vcolumn::VirtualColumnBase::IsNull (this=0x7fd7740cc4a0, mit=...)
at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-128/storage/tianmu/vc/virtual_column_base.h:104
#14 0x0000000002aa27e2 in Tianmu::core::TempTable::SendResult (this=0x7fd7740c7290, limit=2, offset=0, sender=..., pagewise=false)
at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-128/storage/tianmu/core/temp_table_low.cpp:453
#15 0x0000000002aa19a7 in Tianmu::core::TempTable::FillMaterializedBuffers (this=0x7fd7740c7290, local_limit=2, local_offset=0, sender=0x7fd7740c71c0, pagewise=false)
at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-128/storage/tianmu/core/temp_table_low.cpp:310
#16 0x0000000002a8ce91 in Tianmu::core::TempTable::Materialize (this=0x7fd7740c7290, in_subq=false, sender=0x7fd7740c71c0, lazy=false)
at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-128/storage/tianmu/core/temp_table.cpp:1956
#17 0x00000000029d6bf0 in Tianmu::core::Engine::Execute (this=0x4887160, thd=0x7fd774010540, lex=0x7fd7740126b8, result_output=0x7fd77401dc60, unit_for_union=0x0)
at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-128/storage/tianmu/core/engine_execute.cpp:426
#18 0x00000000029d5cce in Tianmu::core::Engine::HandleSelect (this=0x4887160, thd=0x7fd774010540, lex=0x7fd7740126b8, result=@0x7fd95dc89df8: 0x7fd77401dc60, setup_tables_done_option=0,
res=@0x7fd95dc89df4: 0, optimize_after_tianmu=@0x7fd95dc89dec: 1, tianmu_free_join=@0x7fd95dc89df0: 1, with_insert=0)
at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-128/storage/tianmu/core/engine_execute.cpp:232
#19 0x0000000002abb4dc in Tianmu::dbhandler::TIANMU_HandleSelect (thd=0x7fd774010540, lex=0x7fd7740126b8, result=@0x7fd95dc89df8: 0x7fd77401dc60, setup_tables_done_option=0, res=@0x7fd95dc89df4: 0,
optimize_after_tianmu=@0x7fd95dc89dec: 1, tianmu_free_join=@0x7fd95dc89df0: 1, with_insert=0) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-128/storage/tianmu/handler/ha_rcengine.cpp:82
#20 0x00000000022ff7c7 in execute_sqlcom_select (thd=0x7fd774010540, all_tables=0x7fd774007db0) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-128/sql/sql_:5182
#21 0x00000000022f95ef in mysql_execute_command (thd=0x7fd774010540, first_level=true) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-128/sql/sql_:2831
#22 0x00000000023004ce in mysql_parse (thd=0x7fd774010540, parser_state=0x7fd95dc8af00) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-128/sql/sql_:5621
#23 0x00000000022f6918 in dispatch_command (thd=0x7fd774010540, com_data=0x7fd95dc8b660, command=COM_QUERY) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-128/sql/sql_:1495
#24 0x00000000022f5abb in do_command (thd=0x7fd774010540) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-128/sql/sql_:1034
#25 0x00000000023f99fb in handle_connection (arg=0x6b1be40) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-128/sql/conn_handler/connection_handler_per_:313
#26 0x0000000002915631 in pfs_spawn_thread (arg=0x6b0b180) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-75-128/storage/perfschema/:2197
#27 0x00007fd9a8c5eea5 in start_thread () from /lib64/libpthread.so.0
#28 0x00007fd9a6182b0d in clone () from /lib64/libc.so.6
