如何从头到脚彻底解决一个MySQL Bug？华为云数据库高级专家带你看
【摘要】 一个Bug从诞生到终结——处理Bug的全流程
说明：本文中的MySQL，如果不做特殊说明，指的是开源社区版MySQL。
华为云数据库新版本在发布之前，会面临一系列严苛的测试规则，除了要求通过MySQL的所有测试用例之外，还需要通过由华为百万级更丰富、更贴近用户业务场景的测试用例构筑的测试防护网，以此充分验证新版本是否满足用户经典场景的稳定性。
Bug描述
select
subq_0.c2 as c0
from
(select
ref_6.C_STATE asc0,
case whenref_6.C_PHONE is not NULL then ref_5.C_ID else ref_5.C_ID end
asc1,
floor(
ref_3.c_id)as c2
from
sqltester.t0_hash_partition_p1_view as ref_0
right join sqltester.t4 as ref_1
on (EXISTS (
select
ref_1.c_middle as c0
from
sqltester.t1 as ref_2
where ((false)
and ((true)
or (true)))
or (false)
))
innerjoin sqltester.t0_range_key_subpartition_sub_view as ref_3
on(EXISTS (
select
ref_0.c_credit as c0,
ref_1.c_street_1 as c1,
ref_4.c_credit_lim as c2,
ref_3.c_credit as c3
from
sqltester.t0_hash_partition_p1 as ref_4
where true
))
left joinsqltester.t10 as ref_5
innerjoin sqltester.t11 as ref_6
on(true)
on (((pi() isnot NULL))
and (false))
where (((ref_5.C_D_ID isnot NULL)
or(ref_3.c_middle is not NULL))
)) as subq_0
where (EXISTS (
select
subq_0.c0 as c0,
pi() as c1,
ref_11.c_street_1 as c2,
ref_11.c_discount as c3,
pi() as c4
from
sqltester.t0_partition_sub_view_mixed_001 as ref_11))
group by 1
order by 1;
Bug分析
-
使用相对成熟的版本MySQL 5.6进行验证，返回结果与GaussDB(for MySQL)相同，但与MySQL 8.0不同。 -
使用PostgreSQL进行验证，执行结果与MySQL 5.6、GaussDB(for MySQL)相同，但与MySQL 8.0及更高版本不同。
SELECT count(*)
FROM
(SELECT 1
FROM sqltester.t4 AS ref_1
INNER JOIN sqltester.t4 AS ref_3 ON (EXISTS
(SELECT 1
FROMsqltester.t4 AS ref_4
WHERE TRUE ))
LEFT JOIN sqltester.t10 AS ref_5 ON (FALSE)
WHERE (((ref_5.C_D_ID IS NOT NULL)
OR (ref_3.c_middle IS NOT NULL))))AS subq_0
执行计划如下：
-> Aggregate: count(0) (cost=2.75 rows=0)
-> Filter: ((ref_5.C_D_ID is not null) or(ref_3.c_middle is null)) (cost=2.75 rows=0)
-> Inner hash join(no condition) (cost=2.75 rows=0)
-> Index scan on ref_3 using ndx_c_middle (cost=0.13 rows=50)
-> Hash
-> Inner hash join (no condition) (cost=1.50 rows=0)
-> Index scan on ref_1 using ndx_c_id (cost=6.25 rows=50)
-> Hash
-> Left hash join (no condition) (cost=0.25 rows=0)
-> Limit: 1 row(s) (cost=312.50 rows=1)
->Index scan on ref_4 using ndx_c_id (cost=312.50 rows=50)
-> Hash
-> Zero rows (Impossible filter) (cost=0.00..0.00 rows=0)SELECT count(*)
FROM
(SELECT 1
FROM sqltester.t4 AS ref_1
INNER JOIN sqltester.t4 AS ref_3 ON (EXISTS
(SELECT 1
FROM sqltester.t4 AS ref_4
WHERE TRUE ))
LEFT JOIN sqltester.t10 AS ref_5 ON (FALSE)
WHERE (((ref_5.C_D_ID IS NOT NULL) or(ref_3.c_middle IS NOT NULL))))AS subq_0;
+
+
| count(*) |
+
+
| 2500 |
+
+
1 row in set (0.00 sec)SELECT count(*)
FROM
(SELECT 1
FROM sqltester.t4 AS ref_1
INNER JOIN sqltester.t4 AS ref_3 ON (EXISTS
(SELECT 1
FROM sqltester.t4 AS ref_4
WHERE TRUE ))
LEFT JOIN sqltester.t10 AS ref_5 ON (FALSE)
WHERE (((ref_5.C_D_ID IS NOT NULL))))assubq_0;
+
+
| count(*) |
+
+
| 2500 |
+
+
1 row in set (0.01 sec)Breakpoint 1, ZeroRowsIterator::SetNullRowFlag(this=0x7f92a413d510, is_null_row=false)
at /mywork/mysql-sql/sql/basic_row_iterators.h:398
398 assert(m_child_iterator != nullptr);
(gdb) n
399 m_child_iterator->SetNullRowFlag(is_null_row);
(gdb) s
std::unique_ptr<RowIterator,Destroy_only<RowIterator> >::operator-> (this=0x7f92a413d520)
at/opt/simon/taurus/mysql-root/src/tools/gcc-9.3.0/include/c++/9.3.0/bits/unique_ptr.h:355
355 returnget();
(gdb) fin
Run till exit from #0 std::unique_ptr<RowIterator,Destroy_only<RowIterator> >::operator-> (
this=0x7f92a413d520)
at/opt/simon/taurus/mysql-root/src/tools/gcc-9.3.0/include/c++/9.3.0/bits/unique_ptr.h:355
ZeroRowsIterator::SetNullRowFlag (this=0x7f92a413d510,is_null_row=false)
at/home/simon/mywork/mysql-sql/sql/basic_row_iterators.h:399
399 m_child_iterator->SetNullRowFlag(is_null_row);
Value returned is $1 = (RowIterator *) 0x7f92a413d4d0
(gdb) s
TableRowIterator::SetNullRowFlag (this=0x7f92a413d4d0,is_null_row=false)
at/home/simon/mywork/mysql-sql/sql/records.cc:229
229 if(is_null_row) {
(gdb) n
232 m_table->reset_null_row();
(gdb)
234 }如何解决
华为云数据库团队荟聚了业内50%以上的数据库内核专家，以专业技术实时保障客户业务安全，助力企业业务安全上云！
【版权声明】本文为华为云社区用户原创内容，转载时必须标注文章的来源（华为云社区）、文章链接、文章作者等基本信息， 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱：
cloudbbs@huaweicloud.com
- 点赞
- 收藏
- 关注作者
评论（0）