暂无图片
疑似mysql8.0.19查询bug，导致crash，不确定是不是
我来答
返回问答列表
分享
辉
2021-06-15
疑似mysql8.0.19查询bug，导致crash，不确定是不是
mysql

10:17:10 UTC - mysqld got signal 11 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
Thread pointer: 0x7f99fc0a45c0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong…
stack_bottom = 7f99819c6c30 thread_stack 0x46000
/usr/sbin/mysqld(my_print_stacktrace(unsigned char const*, unsigned long)+0x3d) [0x1fb737d]
/usr/sbin/mysqld(handle_fatal_signal+0x333) [0xfe0e23]
/lib64/libpthread.so.0(+0xf6d0) [0x7f9bc793d6d0]
/usr/sbin/mysqld(actual_key_parts(KEY const*)+0xa) [0xefeeaa]
/usr/sbin/mysqld(calculate_key_len(TABLE*, unsigned int, unsigned long)+0x28) [0x10f3a48]
/usr/sbin/mysqld(handler::ha_index_read_map(unsigned char*, unsigned char const*, unsigned long, ha_rkey_function)+0x289) [0x10f42c9]
/usr/sbin/mysqld(check_unique_constraint(TABLE*)+0xb0) [0xe67f10]
/usr/sbin/mysqld(do_sj_dups_weedout(THD*, SJ_TMP_TABLE*)+0x131) [0xe68191]
/usr/sbin/mysqld(WeedoutIterator::Read()+0xa9) [0x109b979]
/usr/sbin/mysqld(MaterializeIterator::MaterializeQueryBlock(MaterializeIterator::QueryBlock const&, unsigned long long*)+0x18b) [0x10a091b]
/usr/sbin/mysqld(MaterializeIterator::Init()+0x1f1) [0x10a10c1]
/usr/sbin/mysqld(filesort(THD*, Filesort*, RowIterator*, Filesort_info*, Sort_result*, unsigned long long*)+0x38e) [0x10da11e]
/usr/sbin/mysqld(SortingIterator::DoSort(QEP_TAB*)+0xb4) [0xe150b4]
/usr/sbin/mysqld(SortingIterator::Init()+0x22) [0xe151d2]
/usr/sbin/mysqld(LimitOffsetIterator::Init()+0x16) [0x109ba66]
/usr/sbin/mysqld(SELECT_LEX_UNIT::ExecuteIteratorQuery(THD*)+0x261) [0xf695b1]
/usr/sbin/mysqld(SELECT_LEX_UNIT::execute(THD*)+0x10a) [0xf6b85a]
/usr/sbin/mysqld(Sql_cmd_dml::execute_inner(THD*)+0x22b) [0xef76cb]
/usr/sbin/mysqld(Sql_cmd_dml::execute(THD*)+0x418) [0xf00dd8]
/usr/sbin/mysqld(mysql_execute_command(THD*, bool)+0x19ed) [0xeb0d7d]
/usr/sbin/mysqld(mysql_parse(THD*, Parser_state*)+0x350) [0xeb4ac0]
/usr/sbin/mysqld(dispatch_command(THD*, COM_DATA const*, enum_server_command)+0x159b) [0xeb658b]
/usr/sbin/mysqld(do_command(THD*)+0x19c) [0xeb7b4c]
/usr/sbin/mysqld() [0xfd2240]
/usr/sbin/mysqld() [0x24be55c]
/lib64/libpthread.so.0(+0x7e25) [0x7f9bc7935e25]
/lib64/libc.so.6(clone+0x6d) [0x7f9bc5abdbad]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (7f99fc2bb518): select * from(SELECT wd.busi_property, wd.do_id, wde.mergetag,wde.ismerge, wd.allot_ref_orderid, wd.so_id_list, wd.cust_order_id_list, wd.cust_id_list AS cust_id, wd.cust_name_list AS cust_name, wd.cust_trade_id_list, wd.cust_id_list, wd.cust_name_list,wd.modify_time, wd.ordertype_id, wd.ordertype_name, wd.house_id, wd.house_name, wd.partner_id, wd.partner_name, wd.comp_id, wd.comp_sname, wd.invoice_flag, wd.platform_id, wd.order_status, wd.status, wd.handover_status, wd.post_status, wd.cod_flag, wd.seller_remark, wd.tio_flag, wd.volume, wd.vunit, wd.weight, wd.wunit, wd.order_source, wd.is_dispatch, wd.total_amount, wd.discount_amount, wd.actual_amount, wd.paid_amount, wd.pay_status, wd.pay_time, wd.cod_pay_mode, wd.cod_amount, wd.remark, wd.is_pre_sell, wd.delFlag, wd.busi_form, wd.is_has_grp_mater, wd.occupy_storage_flag, wd.pco_flag, wd.pco_status, wd.pco_id, wt.vendee_id, wt.vendee_zipcode, wt.vendee_name, wt.vendee_address, wt.vendee_linkman, wt.vendee_mobile, wt.vendee_region_id, wt.vendee_province, wt
Connection ID (thread ID): 1297
Status: NOT_KILLED

The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.10:17:10 UTC - mysqld got signal 11 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
Thread pointer: 0x7f99fc0a45c0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong…
stack_bottom = 7f99819c6c30 thread_stack 0x46000
/usr/sbin/mysqld(my_print_stacktrace(unsigned char const*, unsigned long)+0x3d) [0x1fb737d]
/usr/sbin/mysqld(handle_fatal_signal+0x333) [0xfe0e23]
/lib64/libpthread.so.0(+0xf6d0) [0x7f9bc793d6d0]
/usr/sbin/mysqld(actual_key_parts(KEY const*)+0xa) [0xefeeaa]
/usr/sbin/mysqld(calculate_key_len(TABLE*, unsigned int, unsigned long)+0x28) [0x10f3a48]
/usr/sbin/mysqld(handler::ha_index_read_map(unsigned char*, unsigned char const*, unsigned long, ha_rkey_function)+0x289) [0x10f42c9]
/usr/sbin/mysqld(check_unique_constraint(TABLE*)+0xb0) [0xe67f10]
/usr/sbin/mysqld(do_sj_dups_weedout(THD*, SJ_TMP_TABLE*)+0x131) [0xe68191]
/usr/sbin/mysqld(WeedoutIterator::Read()+0xa9) [0x109b979]
/usr/sbin/mysqld(MaterializeIterator::MaterializeQueryBlock(MaterializeIterator::QueryBlock const&, unsigned long long*)+0x18b) [0x10a091b]
/usr/sbin/mysqld(MaterializeIterator::Init()+0x1f1) [0x10a10c1]
/usr/sbin/mysqld(filesort(THD*, Filesort*, RowIterator*, Filesort_info*, Sort_result*, unsigned long long*)+0x38e) [0x10da11e]
/usr/sbin/mysqld(SortingIterator::DoSort(QEP_TAB*)+0xb4) [0xe150b4]
/usr/sbin/mysqld(SortingIterator::Init()+0x22) [0xe151d2]
/usr/sbin/mysqld(LimitOffsetIterator::Init()+0x16) [0x109ba66]
/usr/sbin/mysqld(SELECT_LEX_UNIT::ExecuteIteratorQuery(THD*)+0x261) [0xf695b1]
/usr/sbin/mysqld(SELECT_LEX_UNIT::execute(THD*)+0x10a) [0xf6b85a]
/usr/sbin/mysqld(Sql_cmd_dml::execute_inner(THD*)+0x22b) [0xef76cb]
/usr/sbin/mysqld(Sql_cmd_dml::execute(THD*)+0x418) [0xf00dd8]
/usr/sbin/mysqld(mysql_execute_command(THD*, bool)+0x19ed) [0xeb0d7d]
/usr/sbin/mysqld(mysql_parse(THD*, Parser_state*)+0x350) [0xeb4ac0]
/usr/sbin/mysqld(dispatch_command(THD*, COM_DATA const*, enum_server_command)+0x159b) [0xeb658b]
/usr/sbin/mysqld(do_command(THD*)+0x19c) [0xeb7b4c]
/usr/sbin/mysqld() [0xfd2240]
/usr/sbin/mysqld() [0x24be55c]
/lib64/libpthread.so.0(+0x7e25) [0x7f9bc7935e25]
/lib64/libc.so.6(clone+0x6d) [0x7f9bc5abdbad]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (7f99fc2bb518): select * from(SELECT wd.busi_property, wd.do_id, wde.mergetag,wde.ismerge, wd.allot_ref_orderid, wd.so_id_list, wd.cust_order_id_list, wd.cust_id_list AS cust_id, wd.cust_name_list AS cust_name, wd.cust_trade_id_list, wd.cust_id_list, wd.cust_name_list,wd.modify_time, wd.ordertype_id, wd.ordertype_name, wd.house_id, wd.house_name, wd.partner_id, wd.partner_name, wd.comp_id, wd.comp_sname, wd.invoice_flag, wd.platform_id, wd.order_status, wd.status, wd.handover_status, wd.post_status, wd.cod_flag, wd.seller_remark, wd.tio_flag, wd.volume, wd.vunit, wd.weight, wd.wunit, wd.order_source, wd.is_dispatch, wd.total_amount, wd.discount_amount, wd.actual_amount, wd.paid_amount, wd.pay_status, wd.pay_time, wd.cod_pay_mode, wd.cod_amount, wd.remark, wd.is_pre_sell, wd.delFlag, wd.busi_form, wd.is_has_grp_mater, wd.occupy_storage_flag, wd.pco_flag, wd.pco_status, wd.pco_id, wt.vendee_id, wt.vendee_zipcode, wt.vendee_name, wt.vendee_address, wt.vendee_linkman, wt.vendee_mobile, wt.vendee_region_id, wt.vendee_province, wt
Connection ID (thread ID): 1297
Status: NOT_KILLED

The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
2021-06-11T10:18:18.974454Z 0 [Warning] [MY-010139] [Server] Changed limits: max_open_files: 10000 (requested 10010)
2021-06-11T10:18:18.974470Z 0 [Warning] [MY-010142] [Server] Changed limits: table_open_cache: 3995 (requested 4000)
2021-06-11T10:18:19.316406Z 0 [Warning] [MY-011068] [Server] The syntax ‘expire-logs-days’ is deprecated and will be removed in a future release. Please use binlog_expire_logs_seconds instead.
2021-06-11T10:18:19.316441Z 0 [Warning] [MY-010915] [Server] ‘NO_ZERO_DATE’, ‘NO_ZERO_IN_DATE’ and ‘ERROR_FOR_DIVISION_BY_ZERO’ sql modes should be used with strict mode. They will be merged with strict mode in a future release.
2021-06-11T10:18:19.318958Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.19) starting as process 127737
2021-06-11T10:18:33.184237Z 0 [System] [MY-010229] [Server] Starting XA crash recovery…
2021-06-11T10:18:33.195600Z 0 [System] [MY-010232] [Server] XA crash recovery finished.
2021-06-11T10:18:35.108359Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2021-06-11T10:18:35.143504Z 0 [Warning] [MY-010604] [Repl] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use ‘–relay-log=localhost-relay-bin’ to avoid this problem.

我来答
添加附件
收藏
分享
问题补充
6条回答
默认
最新
Cui Hulong

简单来说，就是一个内存临时表由于占用了太大的空间，需要转为磁盘临时表，磁盘临时表的索引没有被正确的初始化，最终导致 crash。
解决方式
1.业务表增加合适的索引，避免产生临时表，同时也能加快 SQL 执行效率
2.升级版本
https://bugs.mysql.com/bug.php?id=99234

暂无图片 评论
暂无图片 有用 0
打赏 0
暂无图片
cqiwen

最近更改过主机名？

暂无图片 评论
暂无图片 有用 0
打赏 0
辉

没改过主机名

暂无图片 评论
暂无图片 有用 0
打赏 0
辉

感谢大佬，我加索引试试

暂无图片 评论
暂无图片 有用 0
打赏 0
louteq

你这个问题可能是DuplicateWeedout参数导致的，可以尝试把这个参数禁用optimizer_switch='index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=off,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=off,skip_scan=on,hash_join=on'

暂无图片 评论
暂无图片 有用 1
打赏 0
louteq

mysql重启的时候，堆栈信息下面就打印触发的sql这个是由哪个参数控制的么，我也遇到这个问题了（8.0.19，查询导致crash），重启的时候，就只有堆栈信息，没有打印触发bug的sql。

暂无图片 评论
暂无图片 有用 0
打赏 0
回答交流
Markdown


请输入正文
提交
问题信息
请登录之后查看
邀请回答
暂无人订阅该标签，敬请期待~~
暂无图片墨值悬赏
55M
ORACLE 11.2.0 触发器 获取 空间属性的wkt格式，使用 sde.st_astext 函数，数据错乱
25M
求Cylance杀毒软件安装包测试数据库安全
25M
PostgreSQL ERROR: found xmin 2146512304 from before relfrozenxid 2146512317
10M
clickhouse 查询视图需要给基表的权限，有什么办法能场查视图不能查表呢？
10M
oracle apex是否可以使用nfc功能，以及录像保存功能
10M
视图v$sql_cs_selectivity 同一个RANGE_ID之下出现了同一个微词的重复记录。
10M
一个update 语句行锁升级 触发bug导致死锁myql宕机？请大佬帮忙分析下更具体原因
10M
时区升级
10M
Goldengate源端PDB，被克隆替代后，重新配置麻烦，有无其它方便，高效方法，恢复Goldengate？
10M
OGG使用COLMAP部分字段不同步问题