處理MySQL的典型問題

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

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

圖1 查看參數(shù)

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

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

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

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

此時查看參數(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è)置只對單次連接有效，因此當(dāng)連接斷開后，再次進(jìn)行連接時，上次所做更改失效。

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

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

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

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

游標(biāo)的使用

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

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

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

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

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

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

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

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

……

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

……

end if;

一次鎖等待超時問題的解決

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

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

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

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

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

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

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

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

此時，再去檢查后臺響應(yīng)請求的代碼，

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

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

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

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

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

為了解決這一問題，對后臺代碼做如下的修改：

至此，問題完全解決。

結(jié)語

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