DB version: 8.0.25 - 阿里RDS
監(jiān)控收到一條告警信息饵溅,于是連上DB查看Processlist
發(fā)現(xiàn)一條慢查詢情龄,USER字段為root賬號(hào)灭忠,HOST字段是%简卧,覺得很詭異:
1.root賬號(hào)密碼只有本人持有屠橄,應(yīng)用連接是另外一個(gè)單獨(dú)賬號(hào)
2.HOST字段來源IP是%族跛,正常情況下應(yīng)該是IP地址才對(duì)
3.此SQL執(zhí)行為何這么久?
針對(duì)以上第3個(gè)問題锐墙,排查到為SQL中調(diào)用了一個(gè)函數(shù)礁哄,如果函數(shù)的入?yún)镹ULL則會(huì)導(dǎo)致執(zhí)行死循環(huán),也正是調(diào)用了函數(shù)溪北,才會(huì)出現(xiàn)USER=‘yd_root’和HOST=‘%’的情況姐仅。
后面在阿里云 RDS 文檔找到一篇關(guān)于%的說明,在最下面常見問題刻盐,鏈接:https://help.aliyun.com/document_detail/94842.html?spm=5176.2020520104.0.0.1155709akPIYwv 文章中提到調(diào)用存儲(chǔ)過程可能會(huì)出現(xiàn)掏膏,經(jīng)測(cè)試,發(fā)現(xiàn)調(diào)用存過敦锌,函數(shù)馒疹,Event的時(shí)候都會(huì)出現(xiàn)類似情況,其他場(chǎng)景暫沒想到乙墙。
測(cè)試前先創(chuàng)建一個(gè)臨時(shí)賬號(hào):
create user test_user@'%' identified by "test_user";
grant all on *.* to test_user@'%' ;
1.測(cè)試調(diào)用存過
-- session1: 登錄root賬號(hào)
# 創(chuàng)建存儲(chǔ)過程颖变,其中 DEFINER=`root`@`%`
USE test;
DELIMITER $$
DROP PROCEDURE IF EXISTS `das` $$
CREATE DEFINER=`root`@`%` PROCEDURE `das`()
BEGIN
SELECT * FROM information_schema.processlist WHERE Id = CONNECTION_ID();
END $$
DELIMITER;
# session2: 登錄test_user賬號(hào)執(zhí)行存儲(chǔ)過程,得到預(yù)期結(jié)果的USER和HOST
USE test;
CALL das();
ID USER HOST DB COMMAND TIME STATE INFO
178616 root %:52958 test Query 0 executing SELECT * FROM information_schema.processlist WHERE Id = CONNECTION_ID()
# session3: 用root賬號(hào)執(zhí)行存過會(huì)得到正確的HOST
結(jié)果略
2.測(cè)試調(diào)用函數(shù)
-- session1: 登錄root賬號(hào)
# 創(chuàng)建函數(shù)听想,其中 DEFINER=`root`@`%`
USE test;
DELIMITER $$
DROP FUNCTION IF EXISTS `das` $$
CREATE DEFINER=`root`@`%` FUNCTION `das`(cid int) RETURNS VARCHAR(100)
BEGIN
declare v_user VARCHAR(50) ;
declare v_host VARCHAR(50) ;
SELECT user,host into v_user,v_host FROM information_schema.processlist WHERE Id = cid;
return concat('USER:',v_user,' ','HOST:',v_host);
END $$
DELIMITER;
# session2: 登錄test_user賬號(hào)執(zhí)行函數(shù)腥刹,得到預(yù)期結(jié)果的USER和HOST
USE test;
select das(CONNECTION_ID()) as res ;
res
USER:root HOST:%:52958
# session3: 用root賬號(hào)執(zhí)行函數(shù)會(huì)得到正確的HOST
結(jié)果略
3.測(cè)試調(diào)用event
event不涉及 存過 或者 函數(shù) 的時(shí)候是不會(huì)出現(xiàn)HOST字段%的情況的
#模擬下event調(diào)用存過出現(xiàn)`%`的場(chǎng)景:
# 登錄root賬號(hào),創(chuàng)建表及存儲(chǔ)過程
use test ;
create table t_event(id int auto_increment PRIMARY key , name VARCHAR(10)) ;
# 創(chuàng)建存儲(chǔ)過程汉买,其中 DEFINER=`root`@`%`
use test;
DELIMITER $$
DROP PROCEDURE IF EXISTS `das` $$
CREATE DEFINER=`root`@`%` PROCEDURE `das`()
BEGIN
insert into t_event (name) select concat(sleep(2),'ccc');
END $$
DELIMITER;
# 創(chuàng)建event
use test;
CREATE DEFINER=`root`@`%` EVENT IF NOT EXISTS `test`.`t_event`
ON SCHEDULE
EVERY '2' SECOND
DO call test.das();
# 查看processlist
SELECT *from information_schema.`PROCESSLIST` where id <>CONNECTION_ID() and info <> '' ;
ID USER HOST DB COMMAND TIME STATE INFO
1487 root % test Connect 1 User sleep insert into t_event (name) select concat(sleep(2),'ccc')
模擬下event調(diào)用函數(shù)出現(xiàn)%的場(chǎng)景:
略