【StoneDB研发日志】union功能bug记录

【StoneDB研发日志】union功能bug记录1、问题现象 create database syw_mtr; use syw_mtr; CREATE TABLE t1 (f1 VARCHAR(255) CHARACTER SET utf8)eng

【StoneDB研发日志】union功能bug记录

1、问题现象

create database syw_mtr;
use syw_mtr;
CREATE TABLE t1 (f1 VARCHAR(255) CHARACTER SET utf8)engine=tianmu;
CREATE TABLE t2 AS SELECT LEFT(f1,171) AS f2 FROM t1 UNION SELECT LEFT(f1,171) AS f2 FROM t1;
ERROR 2013 (HY000): Lost connection to MySQL server during query

问题issue:https://github.com/stoneatom/stonedb/issues/226

2、问题原因

bug代码行

Query_result_create::prepare (sql_insert.cc:2753)

create_table->table不为空

assert(create_table->table == NULL);

stonedb堆栈

    libc.so.6!raise (未知源:0)
    libc.so.6!abort (未知源:0)
    libc.so.6!__assert_fail_base (未知源:0)
    libc.so.6!__assert_fail (未知源:0)
    Query_result_create::prepare(Query_result_create * const this, List<Item> & values, SELECT_LEX_UNIT * u) (optlitaihongstonedbsqlsql_insert.cc:2753)
    st_select_lex::prepare(st_select_lex * const this, THD * thd) (optlitaihongstonedbsqlsql_resolver.cc:361)
    st_select_lex_unit::prepare_fake_select_lex(st_select_lex_unit * const this, THD * thd_arg) (optlitaihongstonedbsqlsql_union.cc:441)
    st_select_lex_unit::optimize_for_stonedb(st_select_lex_unit * const this) (optlitaihongstonedbstoragestonedbcoreengine_execute.cpp:586)
    stonedb::core::Engine::HandleSelect(stonedb::core::Engine * const this, THD * thd, LEX * lex, Query_result *& result, ulong setup_tables_done_option, int & res, int & optimize_after_sdb, int & sdb_free_join, int with_insert) (optlitaihongstonedbstoragestonedbcoreengine_execute.cpp:181)
    stonedb::dbhandler::SDB_HandleSelect(THD * thd, LEX * lex, Query_result *& result, ulong setup_tables_done_option, int & res, int & optimize_after_sdb, int & sdb_free_join, int with_insert) (optlitaihongstonedbstoragestonedbhandlerha_rcengine.cpp:82)
    mysql_execute_command(THD * thd, bool first_level) (optlitaihongstonedbsqlsql_parse.cc:3265)
    mysql_parse(THD * thd, Parser_state * parser_state) (optlitaihongstonedbsqlsql_parse.cc:5621)
    dispatch_command(THD * thd, const COM_DATA * com_data, enum_server_command command) (optlitaihongstonedbsqlsql_parse.cc:1495)
    do_command(THD * thd) (optlitaihongstonedbsqlsql_parse.cc:1034)
    handle_connection(void * arg) (optlitaihongstonedbsqlconn_handlerconnection_handler_per_thread.cc:313)
    pfs_spawn_thread(void * arg) (optlitaihongstonedbstorageperfschemapfs.cc:2197)
    libpthread.so.0!start_thread (未知源:0)
    libc.so.6!clone (未知源:0)

分析过程

assert(create_table->table == NULL);

Query_result_create::prepare (sql_insert.cc:2753)函数prepare阶段和optimize阶段在被调用了两次

1、stonedb::core::Engine::HandleSelect函数首先调用

st_select_lex_unit::prepare->prepare_fake_select_lex ->Query_result_create::prepare

此时create_table->table 是NULL。

2、然后调用

st_select_lex_unit::optimize_for_stonedb -> prepare_fake_select_lex -> Query_result_create::prepare

此时create_table->table不为NULL,assert(create_table->table == NULL); 导致数据库异常退出。

问题出现原因

分析st_select_lex_unit::optimize_for_stonedb函数发现是来源于MySQL的st_select_lex_unit::exec()函数

3、解决办法

mysql的handle_query函数先有prepare然后是optimize函数,但我们stonedb的optimize函数是根据MySQL的st_select_lex_unit::exec()写的,不知道为啥没有正常的optimize流程
file

两种解决方案:

1、修改抛异常的代码行Query_result_create::prepare (sql_insert.cc:2753)函数

assert(create_table->table == NULL);

2、重写stonedb的st_select_lex_unit::optimize_for_stonedb函数,改动比较大,涉及功能多。

解决方案更新:

A:我们发现,这个问题是由于在优化器的代码中,Lex unit 两次prepare造成的。

首先我们抛弃了对assert语句修改的考虑,因为那里判断表没有创建是合理的。

于是解决方案有两种,

1:考虑在lex unit 的prepare里加入 is_prepared的判断。但这个方案,影响面很大,因为这个函数所有的语法都会影响到。

2:针对,select union 或者 select join,以及他们出现的复杂的组合做修改。发现了三处异常点需要进行is_prepared判断。1:union处。2:join处。3:fake_lex_prepare处。

B:针对我们的修改添加了相应mtr语句进行覆盖。一并记录在issue226中。

https://github.com/stoneatom/stonedb/issues/226

ADD test cases to cover these ctas queries for the following reason.

1: From code perspective. There are enough evidences that we want to support these ctas.

2: Our fix can cover these related queries.

CREATE TABLE t2 AS SELECT LEFT(f1,171) AS f2 FROM t1 UNION SELECT LEFT(f1,171) AS f2 FROM t1;

CREATE TABLE t3 AS SELECT t1.f1 AS f3 FROM t1 LEFT JOIN t2 ON t1.f1 = t2.f2;

CREATE TABLE t4 AS SELECT t1.f1 AS f4 FROM t1 INNER JOIN t2 ON t1.f1 = t2.f2;

CREATE TABLE t5 AS SELECT t1.f1 AS f5 FROM t1 RIGHT JOIN t2 ON t1.f1 = t2.f2;

CREATE TABLE t6 AS SELECT t1.f1 AS f6 FROM t1 UNION SELECT t2.f2 AS f6 FROM t2 LEFT JOIN t3 ON t2.f2 = t3.f3;

CREATE TABLE t7 AS SELECT t1.f1 AS f7 FROM t1 UNION SELECT t2.f2 AS f7 FROM t2 INNER JOIN t3 ON t2.f2 = t3.f3;

CREATE TABLE t8 AS SELECT t1.f1 AS f8 FROM t1 UNION SELECT t2.f2 AS f8 FROM t2 RIGHT JOIN t3 ON t2.f2 = t3.f3;

CREATE TABLE t9 AS SELECT t1.f1 AS f9 FROM t1 INNER JOIN t3 ON t1.f1 = t3.f3 UNION SELECT t2.f2 AS f9 FROM t2;

4、MySQL正常流程堆栈

Query_result_create::prepare(Query_result_create * const this, List<Item> & values, SELECT_LEX_UNIT * u) (optlitaihongstonedbsqlsql_insert.cc:2750)
st_select_lex::prepare(st_select_lex * const this, THD * thd) (optlitaihongstonedbsqlsql_resolver.cc:361)
st_select_lex_unit::prepare_fake_select_lex(st_select_lex_unit * const this, THD * thd_arg) (optlitaihongstonedbsqlsql_union.cc:441)
st_select_lex_unit::prepare(st_select_lex_unit * const this, THD * thd_arg, Query_result * sel_result, ulonglong added_options, ulonglong removed_options) (optlitaihongstonedbsqlsql_union.cc:670)
handle_query(THD * thd, LEX * lex, Query_result * result, ulonglong added_options, ulonglong removed_options, int optimize_after_bh, int free_join_from_bh) (optlitaihongstonedbsqlsql_select.cc:150)
mysql_execute_command(THD * thd, bool first_level) (optlitaihongstonedbsqlsql_parse.cc:3266)
mysql_parse(THD * thd, Parser_state * parser_state) (optlitaihongstonedbsqlsql_parse.cc:5621)
dispatch_command(THD * thd, const COM_DATA * com_data, enum_server_command command) (optlitaihongstonedbsqlsql_parse.cc:1495)
do_command(THD * thd) (optlitaihongstonedbsqlsql_parse.cc:1034)
handle_connection(void * arg) (optlitaihongstonedbsqlconn_handlerconnection_handler_per_thread.cc:313)
pfs_spawn_thread(void * arg) (optlitaihongstonedbstorageperfschemapfs.cc:2197)
libpthread.so.0!start_thread (未知源:0)
libc.so.6!clone (未知源:0)

原文地址:https://www.cnblogs.com/yangwilly/archive/2022/08/16/16591385.html

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。
转载请注明出处: https://daima100.com/4905.html

(0)
上一篇 2023-05-31 09:30
下一篇 2023-05-31

相关推荐

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注