踩坑系列：MySql only_full_group_by設定，竟導致所有應用報錯？

1. 踩坑經歷

一個很平常的下午，大家都在埋頭認真寫bug呢，突然企業微信群裡炸鍋了，好多應用都出現大量的Error紀錄檔，而且都報同一個錯誤，就是下面這個：

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Expression #4 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘online_saas.t.receive_amount’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

從異常資訊可以看出，報錯的原因是因為Sql語句SELECT後面的列包含了group by後面沒有的列並且沒有使用聚合函數。

因為近幾天一直未釋出，所以就問運維是不是改了MySql伺服器的設定，開啟了sql_mode裡的only_full_group_by，導致原本執行正常的Sql通不過檢查而執行失敗。

最後運維說有臺MySql伺服器之前曾用Sql語句臨時關閉過 only_full_group_by ， 而剛剛因為負載過高自動重新啟動了，導致sql_mode又使用了原有的預設值，而MySql 5.7以後sql_mode預設是開啟only_full_group_by的，導致了該錯誤。

最後運維修改了這臺MySql伺服器的my.cnf檔案，將sql_mode裡的only_full_group_by關閉了，重新啟動了MySql伺服器和報錯的應用，事情得以最終解決。

為啥要重新啟動應用呢？是因為運維修改設定後，各個應用還是報錯，所以重新啟動了各個報錯的應用。

2. 原因分析

假設你安裝的是MySql 5.7以後的版本，比如5.7.21，預設情況下，sql_mode裡的only_full_group_by是被開啟的：

這個開啟後，對Sql的語法檢查就會很嚴格，就比如上面報錯的Sql語句，就是因為使用GROUP BY不規範造成的。

正常情況下，我們使用GROUP BY語句都是下面這樣的：

SELECT語句後的列，要麼是GROUP BY語句後面出現的列，要麼是使用了聚合函數。

但如果有些地方寫的不規範，就會報錯，比如下面這樣：

因為age列既沒有出現在GROUP BY語句後，也沒有使用聚合函數。

但如果我們將sql_mode裡的only_full_group_by關閉，上面報錯的語句就不報錯了：

SET @@GLOBAL.sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

注意事項：如果仍然報錯，請開啟新對談執行查詢語句。

還要值得注意的是，上面關閉only_full_group_by的方式是臨時的，如果重新啟動了MySql伺服器，only_full_group_by又被開啟了，如下所示：

這也是為什麼MySql伺服器自動重新啟動後，我們的應用開始報錯的原因，因為運維之前確實改過，但是是臨時改的，重新啟動後又被覆蓋了。

3. 推薦解決方案

如果沒有歷史技術債，這個開關開啟也挺好的，可以讓大家按規範寫Sql，如果不規範，就能及時發現。

但如果有歷史技術債，就需要關閉only_full_group_by了，而且要永久性的關閉，避免MySql伺服器重新啟動後設定又被覆蓋的情況。

可以通過在==/etc/my.cnf==檔案新增以下內容，來永久關閉only_full_group_by：

sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

修改完畢後，記得重新啟動MySql。

如果重新啟動後也不會生效，檢查下sql_mode的位置是不是不對（放在最後是不會生效的）：

---

注：如果覺得本篇部落格有任何錯誤或者更好的建議，歡迎留言，我會及時跟進並更正部落格內容！