處理MySQL的典型問題

MySQL系統(tǒng)參數(shù)設(shè)置問題

有如下遞歸函數(shù)，用來查詢某型裝備下的所有子裝備，并將結(jié)果以字符串形式返回，具體函數(shù)如下：

圖1 查看參數(shù)

經(jīng)過反復(fù)測(cè)試，該函數(shù)始終無法得到所有子裝備，懷疑是該函數(shù)內(nèi)部某參數(shù)長(zhǎng)度或系統(tǒng)函數(shù)返回結(jié)果的長(zhǎng)度無法滿足要求。

經(jīng)反復(fù)檢查，初步懷疑是group_concat函數(shù)返回值長(zhǎng)度受限。

如圖1所示，查看系統(tǒng)有關(guān)參數(shù)后發(fā)現(xiàn)group_concat函數(shù)的最大長(zhǎng)度為1024，無法滿足需求。故使用“set group_concat_max_len =10000”將該參數(shù)設(shè)置為10000。再次運(yùn)行遞歸函數(shù)，結(jié)果正常。

本以為問題已經(jīng)得到解決，但是在客戶端斷開與MySQL服務(wù)器連接后，再次連接MySQL服務(wù)器時(shí)，問題仍然出現(xiàn)。

此時(shí)查看參數(shù)” group_concat_max_len”，發(fā)現(xiàn)之前做的更改失效了。

后經(jīng)研究發(fā)現(xiàn)”set group_concat_max_len”相當(dāng)于”set session group_concat_max_len”，該設(shè)置只對(duì)單次連接有效，因此當(dāng)連接斷開后，再次進(jìn)行連接時(shí)，上次所做更改失效。

使 用”set global group_concat_max_len”修改參數(shù)后，斷開連接后再次連接時(shí)，發(fā)現(xiàn)所做更改仍然有效。

但是當(dāng)重啟MySQL服務(wù)后，問題再次出現(xiàn)，再看參數(shù)，發(fā)現(xiàn)之前所做更改再次失效。

此時(shí)，修改配置文 件 my.ini，加 入 一行” group_concat_max_len=10000”，重 啟 MySQL服務(wù)，再次查看對(duì)應(yīng)參數(shù)，發(fā)現(xiàn)所做更改有效。

至此，問題完全解決。得出結(jié)論：my.ini文件中的配置永久有效；使用”set global xxx”設(shè)置參數(shù)時(shí)，在MySQL服務(wù)不重啟的情況下，一直有效；使用“set xxx”設(shè)置參數(shù)時(shí)，僅對(duì)當(dāng)前連接有效。

游標(biāo)的使用

由于需要批量處理數(shù)據(jù)，因此必須用到游標(biāo)。在使用游標(biāo)的過程中，也出現(xiàn)了一些問題。

1.游標(biāo)的聲明。游標(biāo)聲明必須在變量或條件聲明后。

2.多一次循環(huán)的問題。有一個(gè)存儲(chǔ)過程，其中使用到了游標(biāo)。代碼如下：

圖2 查看數(shù)據(jù)庫情況

圖3 查看相關(guān)參數(shù)

當(dāng)調(diào)用該存儲(chǔ)過程時(shí)，發(fā)現(xiàn)實(shí)際的循環(huán)次數(shù)總是比所希望的循環(huán)次數(shù)多一次。

經(jīng)研究發(fā)現(xiàn)，當(dāng)游標(biāo)指到最后一條數(shù)據(jù)時(shí)，done的值仍為0，滿足循環(huán)條件，因此又進(jìn)入下一次循環(huán)，fetch后面的代碼繼續(xù)執(zhí)行，游標(biāo)繼續(xù)向后移動(dòng)，此時(shí)無數(shù)據(jù)，將done置為1，不滿足循環(huán)條件，因此實(shí)際循環(huán)次數(shù)比理論循環(huán)次數(shù)多了一次。

在將代碼進(jìn)行如下修改后，程序正常執(zhí)行。

……

fetch getEqu into equID,equNum,equPrice;if(not done) then

……

end if;

一次鎖等待超時(shí)問題的解決

筆者在一次向后臺(tái)服務(wù)器提交數(shù)據(jù)庫處理請(qǐng)求時(shí)，遭遇了嚴(yán)重的超時(shí)問題。具體情況如下：

系統(tǒng)采用的是B/S架構(gòu),在前臺(tái)向服務(wù)器提交請(qǐng)求后，前臺(tái)頁面一直處于等待狀態(tài)。

使 用”show processlist”反復(fù)查看數(shù)據(jù)庫運(yùn)行狀態(tài)，結(jié)果如圖2所示。

結(jié)果數(shù)據(jù)庫一直在執(zhí)行語 句”delete……”,直 到50秒后，進(jìn)入sleep狀態(tài)，此時(shí)前臺(tái)的等待狀態(tài)結(jié)束。懷疑該問題與數(shù)據(jù)庫鎖有關(guān)。此時(shí)使用”show variables like ‘%innodb_lock%’” 查看相關(guān)參數(shù)，結(jié)果如圖3所示。

我們可以看到innodb_lock_wait_timeout的值與之前等待的時(shí)間相同，再查看后臺(tái)軟件，有提示表示鎖等待超時(shí)。

為了查明具體的原因，再次向服務(wù)器提交相同的請(qǐng)求。

此 時(shí)，查 看information_schema數(shù)據(jù)庫，如圖 4、圖 5。

通過以上結(jié)果可以初步判斷，此次請(qǐng)求造成了數(shù)據(jù)庫的鎖等待，并且能夠精準(zhǔn)定位具體產(chǎn)生鎖等待的表為ck.sysmsg。

此時(shí)，再去檢查后臺(tái)響應(yīng)請(qǐng)求的代碼，

圖4 information_schema數(shù)據(jù)庫

圖5 information_schema數(shù)據(jù)庫

此處，首先建立了一個(gè)數(shù)據(jù)庫連接，并且執(zhí)行了相應(yīng)的數(shù)據(jù)庫操作，該操作會(huì)向ck.sysmsg表中新增一條數(shù)據(jù),但是由于將autocommit設(shè)置為0，該新增操作并不會(huì)立即提交。

然后，sqlExecute函數(shù)又新建了一個(gè)數(shù)據(jù)庫連接，用來更新application表，同時(shí)會(huì)觸發(fā)對(duì)ck.sysmsg表的delete操作。

此時(shí)，之前向ck.sysmsg新增數(shù)據(jù)的操作正在等待提交。因而就會(huì)產(chǎn)生鎖等待的現(xiàn)象，delete的操作一直在等待ck.sysmsg表中鎖的釋放。

為了解決這一問題，對(duì)后臺(tái)代碼做如下的修改：

至此，問題完全解決。

結(jié)語

在利用MySQL進(jìn)行開發(fā)的時(shí)候會(huì)遇到各種各樣的問題，在遇到問題時(shí)，一定要思路清晰，多利用MySQL自帶的命令語句查找問題。希望本文能為大家提供一個(gè)思路。