mysql 錯(cuò)誤 ERROR: [SQLCODE] 1055 , [SQLSTATE] 42000 排查思路

研發(fā)創(chuàng)建了存儲過程，需要部署定時(shí)任務(wù)，在上線前進(jìn)行檢查，執(zhí)行定時(shí)腳本時(shí)，出現(xiàn)了如下報(bào)錯(cuò)

目前創(chuàng)新互聯(lián)建站已為千余家的企業(yè)提供了網(wǎng)站建設(shè)、域名、虛擬主機(jī)、網(wǎng)站托管、企業(yè)網(wǎng)站設(shè)計(jì)、祥云網(wǎng)站維護(hù)等服務(wù)，公司將堅(jiān)持客戶導(dǎo)向、應(yīng)用為本的策略，正道將秉承"和諧、參與、激情"的文化，與客戶和合作伙伴齊心協(xié)力一起成長，共同發(fā)展。

ERROR: [SQLCODE] 1055 , [SQLSTATE] 42000 , [MESSAGE] Expression #1 of ORDER BY clause is not in GROUP BY

此時(shí)查看sql_mode，發(fā)現(xiàn)里面沒有only_full_group_by，但是執(zhí)行還會報(bào)錯(cuò)，懷疑是鏈接沒斷開，當(dāng)前session沒生效，斷開重連還是一樣的報(bào)錯(cuò)

遂查看存儲過程 show create procedure dataabase.procname;

發(fā)現(xiàn)創(chuàng)建之前的sql_mode有only_full_group_by，為了能正常運(yùn)行，在當(dāng)前模式下，刪除新建存儲過程，即可重新調(diào)用，執(zhí)行成功。

mysql之慢sql問題排查

0、首先排除機(jī)器問題，如cpu、內(nèi)存情況

1、根據(jù)日志找到sql語句，從兩點(diǎn)，一是索引、二是語句的寫法

2、使用mysql的explain+語句形式，排查是否引用索引，通過key、extra，key表示有沒有用到索引，用到的是哪個(gè)索引，像like、or等是索引失效的，extra using index表示覆蓋索引，usingwhere表示where條件用到了索引，通過explain結(jié)果，修改sql語句，該加索引加索引，該修改語句修改語句

3、語句編寫上，子查詢、關(guān)聯(lián)查詢大表小表

4、部署測試

MySQL 問題排查都有哪些手段？

可以使用 show processlist 命令查看當(dāng)前所有連接信息。

使用 explain 命令查詢 SQL 語句執(zhí)行計(jì)劃。

開啟慢查詢?nèi)罩?，查看慢查詢?SQL。

記一次Mysql故障排查(lock wait timeout exceeded;try restarting transaction)

最近接了一個(gè)鍋,進(jìn)入新公司接手了一個(gè)進(jìn)入交付階段的項(xiàng)目.在code?review的時(shí)候發(fā)現(xiàn)很多問題,然后開始修復(fù)bug.

在測試階段突然發(fā)現(xiàn)幾乎所有涉及到更新的操作都失敗,下面貼出異常信息.

第一次 出現(xiàn)的時(shí)候百度了一下,猜想可能是多服務(wù)部署資源沖突,重啟服務(wù)故障消失.所以沒有特別重視

第二次 出現(xiàn)的時(shí)候只有測試環(huán)境部署,不存在多機(jī)資源沖突的問題,猜想是多線程資源交叉導(dǎo)致的,于是給可能導(dǎo)致資源競爭的地方加上了分布式鎖.

由于無法重現(xiàn)故障,所以并沒有確認(rèn)問題得到解決.

第三次 故障依舊,當(dāng)發(fā)現(xiàn)問題依然存在的時(shí)候,開始認(rèn)真反思,發(fā)現(xiàn)自己解決問題的思路明顯有問題,過于片面,一直都只在應(yīng)用層面尋求解決問題的辦法,而且解決問題的方式也只是在嘗試百度出來的方法.并沒有去思考更深層的問題.

在Mysql5.5中，information_schema 庫中增加了三個(gè)關(guān)于鎖的表（MEMORY引擎）；

INNODB_TRX ## 當(dāng)前運(yùn)行的所有事務(wù)

INNODB _LOCKS ## 當(dāng)前出現(xiàn)的鎖

INNODB_LOCK_WAITS ## 鎖等待的對應(yīng)關(guān)系

通過查詢 INNODB_TRX 發(fā)現(xiàn)

當(dāng)前事務(wù)中又兩個(gè)RUNNING狀態(tài)開始時(shí)間在一個(gè)小時(shí)之前

開始一直以為是鎖表了

查看了 INNODB _LOCKS? 事務(wù)信息之后發(fā)現(xiàn)有4行數(shù)據(jù)被鎖住了一直沒有釋放

從這里開始發(fā)現(xiàn)問題了,應(yīng)用已經(jīng)拋了異常,事務(wù)理所當(dāng)然的應(yīng)該回滾才對,為什么資源依然沒有釋放,導(dǎo)致持續(xù)的阻塞呢?

其實(shí)最開始的異常信息就已經(jīng)給出了答案,回到開始的地方,再看異常信息就很清楚了,應(yīng)用里面的異常類是 MySQLTransactionRollBackException

是一個(gè)回滾異常, 這就說明在事務(wù)回滾的時(shí)候出了問題資源沒有得到釋放

然后開始查詢 MySQLTransactionRollBackException? 相關(guān)的信息

這個(gè)時(shí)候 innodb_rollback_on_timeout =0(默認(rèn)配置)這個(gè)MySQL的配置開始進(jìn)入我的視線,

舉個(gè)栗子

事務(wù)在鎖等待超時(shí)后是回滾事務(wù)內(nèi)所有的statement還是最后一條語句；

0表示rollback最后一條語句，默認(rèn)值； 有點(diǎn)坑爹啊( 細(xì)思極恐 )

1表示回滾事務(wù)內(nèi)所有的statements；(此參數(shù)是只讀參數(shù)，需在my.cnf中配置，并且重啟生效；)

吃過一次虧,這次并沒有盲目的相信百度到的信息

于是開始測試

一、驗(yàn)證innodb_rollback_on_timeout=off的情況

1.session?A

開啟事務(wù),事務(wù)未提交,鎖住id=1的數(shù)據(jù)

2.session B?

開啟事務(wù),執(zhí)行更新id=2的數(shù)據(jù)成功(事務(wù)未提交,鎖住id=2),然后請求id=1等待鎖超時(shí),id=2的數(shù)據(jù)更改為222.

3.session C

請求id=2的數(shù)據(jù)50秒后顯示等待鎖超時(shí)

執(zhí)行 SELECT * FROM information_schema.INNODB_TRX;

可發(fā)現(xiàn)有資源一直未釋放,具體到測試數(shù)據(jù)中就是id=2的資源一直被鎖定,線程一直被掛起.

總結(jié):通過實(shí)驗(yàn)基本可以確定是業(yè)務(wù)資源交叉導(dǎo)致死鎖之后資源沒釋放造成的持續(xù)阻塞，

二.驗(yàn)證innodb_rollback_on_timeout=on

修改配置后將驗(yàn)證innodb_rollback_on_timeout=off的步驟再走一遍

發(fā)現(xiàn)鎖等待只能在業(yè)務(wù)層面盡量避免

on/off的區(qū)別在于session?C進(jìn)入時(shí)不會持續(xù)阻塞,session B異常后全部回滾