MySQL日誌警告'[Warning] Unsafe statement written to the binary log'

feelpurple發表於2018-05-10
MySql
測試環境的MySQL錯誤日誌裡面，出現警告資訊
[Warning] Unsafe statement written to the binary log

  1. 180510 11:15:00 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements writing to a table with an auto-increment column after selecting from another table are unsafe because the order in which rows are retrieved determines what (if any) rows will be written. This order cannot be predicted and may differ on master and the slave. Statement: UPDATE ls_klass_subject ksubj
  2.             JOIN (
  3.                 SELECT classSubjId, SUM(count) AS endCount
  4.                 FROM ls_lesson
  5.                 WHERE isDeleted = 0
  6.                 AND isEnd = 0
  7.                 AND endTime < DATE_ADD(NOW(), INTERVAL -30 MINUTE)
  8.                 GROUP BY classSubjId
  9.             ) t
  10.             SET finish = IFNULL(finish,0)+ t.endCount,
  11.                 modifiedBy =888,
  12.                 modifiedOn = NOW()
  13.             WHERE ksubj.classSubjId =t.classSubjId AND ksubj.isDeleted = 0

報錯原因：
資料庫的二進位制日誌格式為語句，在SQL語句中有DATE_ADD(NOW(), INTERVAL -30 MINUTE)時間函式。
這樣會導致從庫上面的語句執行結果可能不一致。

解決方法：
將二進位制日誌的格式設為ROW或MIXED。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26506993/viewspace-2154288/，如需轉載，請註明出處，否則將追究法律責任。

相關文章