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

雨点打透心脏的1/2处 2024-04-18 00:50 69阅读 0赞

测试环境的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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26506993/viewspace-2154288/