| Bug #110110 | MGR SECONDARY UNREACHABLE | ||
|---|---|---|---|
| Submitted: | 17 Feb 2023 12:25 | Modified: | 21 Feb 2023 3:01 |
| Reporter: | Kai Tian | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server: InnoDB Plugin storage engine | Severity: | S3 (Non-critical) |
| Version: | 8.0.31 | OS: | Red Hat (8.7) |
| Assigned to: | CPU Architecture: | x86 (Intel(R) Xeon(R) Gold 5218 CPU @ 2.30GHz) | |
[17 Feb 2023 12:25]
Kai Tian
[17 Feb 2023 13:44]
MySQL Verification Team
Hi Mr. Tian, Thank you for your interesting bug report. However, we can not repeat it. Hence, what we need from you is a fully repeatable test case, in the form of a set of SQL commands that always lead to the crash like the one you reported. There is nothing that we can do without such a test case. Can't repeat.
[21 Feb 2023 3:01]
Kai Tian
We tried to avoid this problem by turning off innodb_adaptive_hash_index, but the problem persists.
Then we migrated the query function from MGR to slave DB, and then it reported the same error.
We crawled the SQL that caused the problem,here is part of the sql:
with x as (select *, row_number() over(partition by task_id order by gmt_modified desc) ran from spr_t_task_history)
select mcl.name 一,
-- 日
ifnull(cl.chuli, 0) * 1.5 二,
ifnull(cl.chuli, 0) 三,
ifnull(tg.tongguo, 0) 四,
ifnull(jj.jujue, 0) 五,
case when cl.chuli is not null then concat((ifnull(tg.tongguo, 0) / ifnull(cl.chuli, 0)) *100, '%')
else '0%' end 六,
ifnull(fk.fangkuan, 0) 七,
case when tg.tongguo is not null then concat((ifnull(fk.fangkuan, 0) / ifnull(tg.tongguo, 0)) *100, '%')
else '0%' end 八,
ifnull(jfcl.jfchuli, 0) 九,
ifnull(jftg.jftongguo, 0) 十,
case when jfcl.jfchuli is not null then concat((ifnull(jftg.jftongguo, 0) / ifnull(jfcl.jfchuli, 0)) *100, '%')
else '0%' end 十一,
ifnull(fjfcl.fjfchuli, 0) 十二,
ifnull(fjftg.fjftongguo, 0) 十三,
case when fjfcl.fjfchuli is not null then concat((ifnull(fjftg.fjftongguo, 0) / ifnull(fjfcl.fjfchuli, 0)) *100, '%')
else '0%' end 十四,
-- 月
ifnull(mcl.chuli, 0) * 1.5 十五,
ifnull(mcl.chuli, 0) 十六,
ifnull(mtg.tongguo, 0) 十七,
ifnull(mjj.jujue, 0) 十八,
case when mcl.chuli is not null then concat((ifnull(mtg.tongguo, 0) / ifnull(mcl.chuli, 0)) *100, '%')
else '0%' end 十九,
ifnull(mfk.fangkuan, 0) 二十,
case when mtg.tongguo is not null then concat((ifnull(mfk.fangkuan, 0) / ifnull(mtg.tongguo, 0)) *100, '%')
else '0%' end 二十一,
ifnull(mjfcl.jfchuli, 0) 二十二,
ifnull(mjftg.jftongguo, 0) 二十三,
case when mjfcl.jfchuli is not null then concat((ifnull(mjftg.jftongguo, 0) / ifnull(mjfcl.jfchuli, 0)) *100, '%')
else '0%' end 二十四,
ifnull(mfjfcl.fjfchuli, 0) 二十五,
ifnull(mfjftg.fjftongguo, 0) 二十六,
case when mfjfcl.fjfchuli is not null then concat((ifnull(mfjftg.fjftongguo, 0) / ifnull(mfjfcl.fjfchuli, 0)) *100, '%')
else '0%' end 二十七,
ifnull(fkczth.fkcztuihui, 0) 二十八,
case when mcl.chuli is not null then concat((ifnull(fkczth.fkcztuihui, 0) / ifnull(mcl.chuli, 0)) *100, '%')
else '0%' end 二十九
from (
-- 月
select zts.id clid, zts.name, count(*) chuli, count(case when rto.business_type = 2 or rto.apply_type <> 1 then rto.id end) xcfgrchuli
from rob_t_order rto
left join spr_t_process stp on stp.transaction_id = rto.id and stp.is_delete = 0
left join spr_t_task stt on stt.process_id = stp.id and stt.is_delete = 0
left join (select * from x) stth on stth.task_id = stt.id and stth.is_delete = 0 and stth.ran = 1
left join zeu_t_staff zts on stt.executor_id = zts.id and zts.is_delete = 0
where rto.is_delete = 0
and stt.definition_id = 'financingInfoSecondCheck'
and stth.gmt_modified >= date_add(curdate(),interval -day(curdate())+1 day)
and stth.gmt_modified < date_add(current_date, interval 0 day)
and stth.state in (3, 8)
and rto.business_type = 2
group by zts.id, zts.name) mcl
left join (
select zts.id tgid, zts.name, count(*) tongguo
from rob_t_order rto
left join spr_t_process stp on stp.transaction_id = rto.id and stp.is_delete = 0
left join spr_t_task stt on stt.process_id = stp.id and stt.is_delete = 0
left join (select * from x) stth on stth.task_id = stt.id and stth.is_delete = 0 and stth.ran = 1
left join zeu_t_staff zts on stt.executor_id = zts.id and zts.is_delete = 0
where rto.is_delete = 0
and stt.definition_id = 'financingInfoSecondCheck'
and stth.gmt_modified >= date_add(curdate(),interval -day(curdate())+1 day)
and stth.gmt_modified < date_add(current_date, interval 0 day)
and stth.state = 3
and rto.business_type = 2
group by zts.id, zts.name) mtg
on mcl.clid = mtg.tgid
left join (
select zts.id jjid, zts.name, count(*) jujue
from rob_t_order rto
left join spr_t_process stp on stp.transaction_id = rto.id and stp.is_delete = 0
left join spr_t_task stt on stt.process_id = stp.id and stt.is_delete = 0
left join (select * from x) stth on stth.task_id = stt.id and stth.is_delete = 0 and stth.ran = 1
left join zeu_t_staff zts on stt.executor_id = zts.id and zts.is_delete = 0
where rto.is_delete = 0
and stt.definition_id = 'financingInfoSecondCheck'
and stth.gmt_modified >= date_add(curdate(),interval -day(curdate())+1 day)
and stth.gmt_modified < date_add(current_date, interval 0 day)
and stth.state = 8
and rto.business_type = 2
group by zts.id, zts.name) mjj
on mcl.clid = mjj.jjid
left join (
select zts.id fkid, zts.name, count(*) fangkuan
from rob_t_order rto
left join spr_t_process stp on stp.transaction_id = rto.id and stp.is_delete = 0
left join spr_t_task stt on stt.process_id = stp.id and stt.is_delete = 0
left join (select * from x) stth on stth.task_id = stt.id and stth.is_delete = 0 and stth.ran = 1
left join zeu_t_staff zts on stt.executor_id = zts.id and zts.is_delete = 0
where rto.is_delete = 0
and stt.definition_id = 'financingInfoSecondCheck'
and rto.payment_time >= date_add(curdate(),interval -day(curdate())+1 day)
and rto.business_type = 2
group by zts.id, zts.name) mfk
on mcl.clid = mfk.fkid
After executed this SQL twice,the slave DB report error MY-001782 & MY-001756.
[21 Feb 2023 13:07]
MySQL Verification Team
Hi Mr. Tian, We already wrote that we require a fully and fully repeatable test case. It presumes that we have mysqldump of all tables involved in that query. We would also prefer that the query is written in the single line. You do not have to dump the entire tables , but only those parts that are relevant to the query in question. Also, we shall test your query on a single machine, since this is not a Cluster problem, but it a problem in the optimiser or InnoDB. To upload the test case use the table with a title of "Files". Let us know when you upload the test case.
