摘要:
列存储引擎打开tianmu_ini_allowmysqlquerypath参数, 对打开该参数后的异常行为做记录
配置文件文件打开tianmu_ini_allowmysqlquerypath参数
[mysqld]
port = 3306
basedir = YOUR_ABS_PATH/
character-sets-dir = YOUR_ABS_PATH/share/charsets/
lc-messages-dir = YOUR_ABS_PATH/share/
plugin_dir = YOUR_ABS_PATH/lib/plugin/
tmpdir = YOUR_ABS_PATH/tmp/
socket = YOUR_ABS_PATH/tmp/mysql.sock
datadir = YOUR_ABS_PATH/data/
pid-file = YOUR_ABS_PATH/data/mysqld.pid
log-error = YOUR_ABS_PATH/log/mysqld.log
lc-messages-dir = YOUR_ABS_PATH/share/english/
local-infile
# skip-grant-tables
tianmu_ini_allowmysqlquerypath=1
异常记录:
一. SELECT_LEX::prepare中的JOIN断言失败
调用堆栈:
(gdb) bt
#0 0x00007f580b75f387 in __GI_raise (sig=sig@entry=6) at ../nptl/sysdeps/unix/sysv/linux/raise.c:55
#1 0x00007f580b760a78 in __GI_abort () at abort.c:90
#2 0x00007f580b7581a6 in __assert_fail_base (fmt=0x7f580b8b3f60 "%s%s%s:%u: %s%sAssertion `%s' failed.\n%n", assertion=assertion@entry=0x344ce43 "join == __null",
file=file@entry=0x344cdb8 "/data2/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-238/sql/sql_", line=line@entry=110,
function=function@entry=0x344d9e0 <st_select_lex::prepare(THD*)::__PRETTY_FUNCTION__> "bool st_select_lex::prepare(THD*)") at assert.c:92
#3 0x00007f580b758252 in __GI___assert_fail (assertion=0x344ce43 "join == __null", file=0x344cdb8 "/data2/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-238/sql/sql_", line=110,
function=0x344d9e0 <st_select_lex::prepare(THD*)::__PRETTY_FUNCTION__> "bool st_select_lex::prepare(THD*)") at assert.c:101
#4 0x00000000023d9159 in st_select_lex::prepare (this=0x7f53d0007730, thd=0x7f53d0002c10) at /data2/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-238/sql/sql_:110
#5 0x00000000023e5655 in handle_query (thd=0x7f53d0002c10, lex=0x7f53d0004f38, result=0x7f53d0022060, added_options=0, removed_options=0, optimize_after_bh=2, free_join_from_bh=1)
at /data2/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-238/sql/sql_:139
#6 0x000000000239ba04 in execute_sqlcom_select (thd=0x7f53d0002c10, all_tables=0x7f53d001f628) at /data2/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-238/sql/sql_:5184
#7 0x0000000002394d4e in mysql_execute_command (thd=0x7f53d0002c10, first_level=true) at /data2/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-238/sql/sql_:2831
#8 0x000000000239c993 in mysql_parse (thd=0x7f53d0002c10, parser_state=0x7f57be4baeb0) at /data2/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-238/sql/sql_:5621
#9 0x0000000002391c2b in dispatch_command (thd=0x7f53d0002c10, com_data=0x7f57be4bb650, command=COM_QUERY) at /data2/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-238/sql/sql_:1495
#10 0x0000000002390b57 in do_command (thd=0x7f53d0002c10) at /data2/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-238/sql/sql_:1034
#11 0x00000000024c3757 in handle_connection (arg=0x9e32560) at /data2/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-238/sql/conn_handler/connection_handler_per_:313
#12 0x0000000002ba7388 in pfs_spawn_thread (arg=0x9e0e740) at /data2/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-238/storage/perfschema/:2197
#13 0x00007f580e2deea5 in start_thread (arg=0x7f57be4bc700) at pthread_create.c:307
#14 0x00007f580b827b0d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:111
(gdb) f 4
#4 0x00000000023d9159 in st_select_lex::prepare (this=0x7f53d0007730, thd=0x7f53d0002c10) at /data2/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-238/sql/sql_:110
110 assert(join == NULL);
(gdb) p join
$2 = (JOIN *) 0x7f53d0a95e50
(gdb) p *join
$3 = {<Sql_alloc> = {<No data fields>}, select_lex = 0x7f53d0007730, unit = 0x7f53d0007a18, thd = 0x7f53d0002c10, join_list = 0x0, select_options = 0, join_tab = 0x0, qep_tab = 0x0,
best_ref = 0x7f53d0a96720, map2table = 0x7f53d0a96740, sort_by_table = 0x0, tables = 2, primary_tables = 2, const_tables = 0, tmp_tables = 0, send_group_parts = 0, sort_and_group = false,
first_record = false, grouped = false, do_send_rows = true, all_table_map = 3, const_table_map = 0, found_const_table_map = 0, send_records = 0, found_records = 0, examined_rows = 0,
row_limit = 18446744073709551615, m_select_limit = 18446744073709551615, fetch_limit = 18446744073709551615, best_positions = 0x0, positions = 0x0, first_select = 0x234f46b
<sub_select(JOIN*, QEP_TAB*, bool)>, best_read = 252533971.3962957, best_rowcount = 1260401460, sort_cost = 0, fields = 0x7f53d0007868,
group_fields = {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x43f0310 <end_of_list>, last = 0x7f53d0a95f38, elements = 0}, <No data fields>},
group_fields_cache = {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x43f0310 <end_of_list>, last = 0x7f53d0a95f50, elements = 0}, <No data fields>},
sum_funcs = 0x7f53d0a96230, sum_funcs_end = 0x7f53d0a96238, sum_funcs2 = 0x0, sum_funcs_end2 = 0x0, tmp_table_param = {<Sql_alloc> = {<No data fields>},
copy_funcs = {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x43f0310 <end_of_list>, last = 0x7f53d0a95f90, elements = 0}, <No data fields>}, copy_field = 0x0,
copy_field_end = 0x0, group_buff = 0x0, items_to_copy = 0x0, recinfo = 0x0, start_recinfo = 0x0, keyinfo = 0x0, end_write_records = 18446744073709551615, field_count = 6,
func_count = 1, sum_func_count = 0, hidden_field_count = 0, group_parts = 0, group_length = 0, group_null_parts = 0, quick_group = 1, outer_sum_func_count = 0,
using_outer_summary_function = false, table_charset = 0x0, schema_table = false, precomputed_group_by = false, force_copy_fields = false, skip_create_table = false,
bit_fields_as_long = false, can_use_pk_for_unique = true}, lock = 0x7f53d0044bb0, rollup = {state = st_rollup::STATE_NONE, null_items = {m_array = 0x0, m_size = 0},
ref_pointer_arrays = 0x0, fields = 0x0}, implicit_grouping = false, select_distinct = false, group_optimized_away = false, simple_order = false, simple_group = false,
ordered_index_usage = JOIN::ordered_index_void, no_order = false, skip_sort_order = false, need_tmp = false, keyuse_array = {<Mem_root_array_YY<Key_use, true>> = {m_root = 0x7f53d0005950,
m_array = 0x0, m_size = 0, m_capacity = 0}, <No data fields>}, all_fields = @0x7f53d0007890, tmp_all_fields1 = {<base_list> = {<Sql_alloc> = {<No data fields>},
first = 0x43f0310 <end_of_list>, last = 0x7f53d0a96088, elements = 0}, <No data fields>}, tmp_all_fields2 = {<base_list> = {<Sql_alloc> = {<No data fields>},
first = 0x43f0310 <end_of_list>, last = 0x7f53d0a960a0, elements = 0}, <No data fields>}, tmp_all_fields3 = {<base_list> = {<Sql_alloc> = {<No data fields>},
first = 0x43f0310 <end_of_list>, last = 0x7f53d0a960b8, elements = 0}, <No data fields>}, tmp_fields_list1 = {<base_list> = {<Sql_alloc> = {<No data fields>},
first = 0x43f0310 <end_of_list>, last = 0x7f53d0a960d0, elements = 0}, <No data fields>}, tmp_fields_list2 = {<base_list> = {<Sql_alloc> = {<No data fields>},
first = 0x43f0310 <end_of_list>, last = 0x7f53d0a960e8, elements = 0}, <No data fields>}, tmp_fields_list3 = {<base_list> = {<Sql_alloc> = {<No data fields>},
first = 0x43f0310 <end_of_list>, last = 0x7f53d0a96100, elements = 0}, <No data fields>}, fields_list = @0x7f53d0007868, error = 0, order = {order = 0x0, src = ESC_ORDER_BY,
flags = 0}, group_list = {order = 0x0, src = ESC_GROUP_BY, flags = 0}, explain_flags = {sorts = "\000\000\000\000"}, where_cond = 0x7f53d0010f70, having_cond = 0x0,
having_for_explain = 0x1, tables_list = 0x7f53d001f628, cond_equal = 0x7f53d0011068, return_tab = 0 '\000', ref_ptrs = {m_array = 0x7f53d0a94f58, m_size = 55}, items0 = {m_array = 0x0,
m_size = 0}, items1 = {m_array = 0x0, m_size = 0}, items2 = {m_array = 0x0, m_size = 0}, items3 = {m_array = 0x0, m_size = 0}, current_ref_ptrs = {m_array = 0x0, m_size = 0},
zero_result_cause = 0x0, child_subquery_can_materialize = false, allow_outer_refs = true, sj_tmp_tables = {<base_list> = {<Sql_alloc> = {<No data fields>},
first = 0x43f0310 <end_of_list>, last = 0x7f53d0a961f0, elements = 0}, <No data fields>}, sjm_exec_list = {<base_list> = {<Sql_alloc> = {<No data fields>},
first = 0x43f0310 <end_of_list>, last = 0x7f53d0a96208, elements = 0}, <No data fields>}, set_group_rpa = false, group_sent = false, calc_found_rows = false, with_json_agg = false,
optimized = true, executed = false, plan_state = JOIN::NO_PLAN}
(gdb)
函数原文:
bool SELECT_LEX::prepare(THD *thd)
{
DBUG_ENTER("SELECT_LEX::prepare");
// We may do subquery transformation, or Item substitution:
Prepare_error_tracker tracker(thd);
assert(this == thd->lex->current_select());
assert(join == NULL);
问题分析:
- Debug版本出现
- 用Release版本编译, 不再出现
二. ERROR 1100 (HY000): Table 'C' was not locked with LOCK TABLES
问题现象:
mysql> SELECT FIND_TOP_USID(C.USID) AS SUSID,
-> C2.CORPNAME AS SCORPNAME,
-> C2.BNAME,
-> C.USID,
-> C.CORPNAME,
-> C.IBUSINESSID,
-> C.TTLB
-> FROM CUSTOM C
-> LEFT JOIN CUSTOM C2
-> ON C2.USID = C.SUSID
-> WHERE C.LGTP = '02'
-> AND C.USTP = '01'
-> AND C.IBUSINESSID = 2;
ERROR 1100 (HY000): Table 'C' was not locked with LOCK TABLES
问题分析:
TODO:
问题解决:
TODO:
三. ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)
问题分析:
TODO
问题解决:
修改参数 log_bin_trust_function_creators=1
四. 使用Release版本, 打开log_bin_trust_function_creators=1, 执行手动重写后的调用自定义动态函数的语句, 再执行show tables, 出现偶发的Crash
问题描述:
- 使用Relase版本编译
- 打开tianmu_ini_allowmysqlquerypath=1
- 打开 log_bin_trust_function_creators=1
- 执行重写后的语句
- mysqld进程偶发crash
set global log_bin_trust_function_creators=1;
use dingyou;
SELECT FIND_TOP_USID(C.USID) AS SUSID,
C2.CORPNAME AS SCORPNAME,
C2.BNAME,
C.USID,
C.CORPNAME,
C.IBUSINESSID,
C.TTLB
FROM CUSTOM C
LEFT JOIN CUSTOM C2
ON C2.USID = C.SUSID
WHERE C.LGTP = '02'
AND C.USTP = '01'
AND C.IBUSINESSID = 2;
发生crash的堆栈:
(gdb) bt
#0 belongs_to_p_s (tl=0x7f84d401c490, tl=0x7f84d401c490) at /data2/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-238/sql/table.h:3056
#1 close_thread_tables (thd=thd@entry=0x7f84d4002c10) at /data2/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-238/sql/sql_:1667
#2 0x0000000000ebb8ac in mysql_execute_command (thd=thd@entry=0x7f84d4002c10, first_level=first_level@entry=true)
at /data2/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-238/sql/sql_:5032
#3 0x0000000000ec1ee5 in mysql_parse (thd=thd@entry=0x7f84d4002c10, parser_state=parser_state@entry=0x7f88ab7d5fe0)
at /data2/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-238/sql/sql_:5621
#4 0x0000000000ec29e4 in dispatch_command (thd=thd@entry=0x7f84d4002c10, com_data=com_data@entry=0x7f88ab7d6640, command=COM_QUERY)
at /data2/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-238/sql/sql_:1495
#5 0x0000000000ec4430 in do_command (thd=thd@entry=0x7f84d4002c10) at /data2/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-238/sql/sql_:1034
#6 0x0000000000f85250 in handle_connection (arg=arg@entry=0x5605220) at /data2/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-238/sql/conn_handler/connection_handler_per_:313
#7 0x000000000144f204 in pfs_spawn_thread (arg=0x623ff10) at /data2/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-238/storage/perfschema/:2197
#8 0x00007f88f6784ea5 in start_thread (arg=0x7f88ab7d7700) at pthread_create.c:307
#9 0x00007f88f3ccdb0d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:111
/**
Check if the table belongs to the P_S, excluding setup and threads tables.
@note Performance Schema tables must be accessible independently of the
LOCK TABLE mode. This function is needed to handle the special case
of P_S tables being used under LOCK TABLE mode.
*/
inline bool belongs_to_p_s(TABLE_LIST *tl)
{
return (!strcmp("performance_schema", tl->db) &&
strcmp(tl->table_name, "threads") &&
strstr(tl->table_name, "setup_") == NULL);
}
(gdb) p tl->db
$1 = 0x0
(gdb) p *tl
$3 = {next_local = 0x0, next_global = 0x0, prev_global = 0x0, db = 0x0, table_name = 0x0, alias = 0x0, target_tablespace_name = {str = 0x0, length = 0}, schema_table_name = 0x0,
option = 0x0, opt_hints_table = 0x0, opt_hints_qb = 0x0, m_tableno = 0, m_map = 0, m_join_cond = 0x0, m_sj_cond = 0x0, sj_inner_tables = 0, natural_join = 0x0, is_natural_join = false,
join_using_fields = 0x0, join_columns = 0x0, is_join_columns_complete = false, next_name_resolution_table = 0x0, index_hints = 0x0, table = 0x0, table_id = {
static TABLE_ID_MAX = 281474976710655, m_id = 0}, derived_result = 0x0, correspondent_table = 0x0, derived = 0x0, schema_table = 0x0, schema_select_lex = 0x0,
schema_table_reformed = false, schema_table_param = 0x0, select_lex = 0x0, view = 0x0, field_translation = 0x0, field_translation_end = 0x0, merge_underlying_list = 0x0,
view_tables = 0x0, belong_to_view = 0x0, referencing_view = 0x0, parent_l = 0x0, security_ctx = 0x0, view_sctx = 0x0, allowed_show = false, next_leaf = 0x0, derived_where_cond = 0x0,
check_option = 0x0, replace_filter = 0x0, select_stmt = {str = 0x0, length = 0}, md5 = {str = 0x0, length = 0}, source = {str = 0x0, length = 0}, view_db = {str = 0x0, length = 0},
view_name = {str = 0x0, length = 0}, timestamp = {str = 0x0, length = 0}, definer = {user = {str = 0x0, length = 0}, host = {str = 0x0, length = 0}, plugin = {str = 0x0, length = 0},
auth = {str = 0x0, length = 0}, uses_identified_by_clause = false, uses_identified_with_clause = false, uses_authentication_string_clause = false,
uses_identified_by_password_clause = false, alter_status = {update_password_expired_fields = false, update_password_expired_column = false, use_default_password_lifetime = false,
expire_after_days = 0, update_account_locked_column = false, account_locked = false}}, file_version = 0, updatable_view = 0, algorithm = 0, view_suid = 2, with_check = 0,
effective_algorithm = VIEW_ALGORITHM_UNDEFINED, grant = {grant_table = 0x3, version = 16908288, privilege = 0, m_internal = {m_schema_lookup_done = false, m_schema_access = 0x0,
m_table_lookup_done = false, m_table_access = 0x0}}, engine_data = 11, callback_func = 0x7f84d4b1c6a0, lock_type = -738130240, outer_join = 32644, shared = 0, db_length = 32,
table_name_length = 992, m_updatable = 8, m_insertable = false, straight = false, updating = false, force_index = false, ignore_leaves = false, dep_tables = 0, on_expr_dep_tables = 12664,
nested_join = 0x61c2000, embedding = 0xe5d2a0 <sql_alloc_error_handler()>, join_list = 0x7f8800000064, cacheable_table = false, table_in_first_from_clause = false,
open_type = OT_TEMPORARY_OR_BASE, contain_auto_increment = false, check_option_processed = false, replace_filter_processed = false, required_type = FRMTYPE_ERROR,
timestamp_buffer = "\000\000\000\000\000\000\000\000\001\000\000\000\000\000\000\000\000\000\000", prelocking_placeholder = false, open_strategy = TABLE_LIST::OPEN_NORMAL,
internal_tmp_table = false, is_alias = false, is_fqtn = false, view_creation_ctx = 0x0, view_client_cs_name = {str = 0x0, length = 0}, view_connection_cl_name = {str = 0x0, length = 0},
view_body_utf8 = {str = 0x0, length = 0}, derived_key_list = {<base_list> = {<Sql_alloc> = {<No data fields>}, first = 0x0, last = 0x0, elements = 0}, <No data fields>},
trg_event_map = 0 '\000', i_s_requested_object = 0, has_db_lookup_value = false, has_table_lookup_value = false, table_open_method = 0, schema_table_state = NOT_PROCESSED, mdl_request = {
type = MDL_INTENTION_EXCLUSIVE, duration = MDL_STATEMENT, next_in_list = 0x0, prev_in_list = 0x0, ticket = 0x0, key = {m_length = 0, m_db_name_length = 0,
m_ptr = '\000' <repeats 28 times>, "\350`\000Ԅ\177\000\000\340\375<\006\000\000\000\000\020\277\001Ԅ\177\000\000\000\000\000\000\000\000\000\000\001", '\000' <repeats 143 times>...,
static m_namespace_to_wait_state_name = {{m_key = 111, m_name = 0x19235dd "Waiting for global read lock", m_flags = 0}, {m_key = 112,
m_name = 0x19236a0 "Waiting for tablespace metadata lock", m_flags = 0}, {m_key = 113, m_name = 0x19236c8 "Waiting for schema metadata lock", m_flags = 0}, {m_key = 114,
m_name = 0x19236f0 "Waiting for table metadata lock", m_flags = 0}, {m_key = 115, m_name = 0x1923710 "Waiting for stored function metadata lock", m_flags = 0}, {m_key = 116,
m_name = 0x1923740 "Waiting for stored procedure metadata lock", m_flags = 0}, {m_key = 117, m_name = 0x1923770 "Waiting for trigger metadata lock", m_flags = 0}, {m_key = 118,
m_name = 0x1923798 "Waiting for event metadata lock", m_flags = 0}, {m_key = 119, m_name = 0x19235fa "Waiting for commit lock", m_flags = 0}, {m_key = 120,
m_name = 0x1923612 "User lock", m_flags = 0}, {m_key = 121, m_name = 0x19237b8 "Waiting for locking service lock", m_flags = 0}}}, m_src_file = 0x0, m_src_line = 0},
view_no_explain = false, partition_names = 0x0, m_join_cond_optim = 0x0, cond_equal = 0x0, optimized_away = 96, derived_keys_ready = 204, m_table_ref_type = 32644,
m_table_ref_version = 140208469251952}
问题解决:
在belongs_to_p_s函数中,如果TABLE_LISTS中无db和table_name则认为判定失败
- 函数逻辑
/**
Check if the table belongs to the P_S, excluding setup and threads tables.
@note Performance Schema tables must be accessible independently of the
LOCK TABLE mode. This function is needed to handle the special case
of P_S tables being used under LOCK TABLE mode.
*/
inline bool belongs_to_p_s(TABLE_LIST *tl)
{
if (!tl->db) {
return false;
}
if (!tl->table_name) {
return false;
}
return (!strcmp("performance_schema", tl->db) &&
strcmp(tl->table_name, "threads") &&
strstr(tl->table_name, "setup_") == NULL);
}
连续性测试不稳定的情况:
#!/bin/bash
/stonedb57/install/bin/mysql -uroot -pstonedb123 -S /stonedb57/install/tmp/mysql.sock -D dingyou -e " set global log_bin_trust_function_creators=1; "
index=0
while [[ true ]]; do
value="VALUE FOR $index"
echo /stonedb57/install/bin/mysql -uroot -pstonedb123 -S /stonedb57/install/tmp/mysql.sock -D dingyou -e "
SELECT FIND_TOP_USID(C.USID) AS SUSID,
C2.CORPNAME AS SCORPNAME,
C2.BNAME,
C.USID,
C.CORPNAME,
C.IBUSINESSID,
C.TTLB
FROM CUSTOM C
LEFT JOIN CUSTOM C2
ON C2.USID = C.SUSID
WHERE C.LGTP = '02'
AND C.USTP = '01'
AND C.IBUSINESSID = 2;
"
/stonedb57/install/bin/mysql -uroot -pstonedb123 -S /stonedb57/install/tmp/mysql.sock -D dingyou -e "
SELECT FIND_TOP_USID(C.USID) AS SUSID,
C2.CORPNAME AS SCORPNAME,
C2.BNAME,
C.USID,
C.CORPNAME,
C.IBUSINESSID,
C.TTLB
FROM CUSTOM C
LEFT JOIN CUSTOM C2
ON C2.USID = C.SUSID
WHERE C.LGTP = '02'
AND C.USTP = '01'
AND C.IBUSINESSID = 2;
"
echo /stonedb57/install/bin/mysql -uroot -pstonedb123 -S /stonedb57/install/tmp/mysql.sock -D dingyou -e " show warnings; "
/stonedb57/install/bin/mysql -uroot -pstonedb123 -S /stonedb57/install/tmp/mysql.sock -D dingyou -e " show warnings; "
echo /stonedb57/install/bin/mysql -uroot -pstonedb123 -S /stonedb57/install/tmp/mysql.sock -D dingyou -e " show tables; "
/stonedb57/install/bin/mysql -uroot -pstonedb123 -S /stonedb57/install/tmp/mysql.sock -D dingyou -e " show tables; "
echo "index: $index over"
let index++
if [[ 10000 == $index ]]; then
break
fi
#sleep 0.5s
done
