Bug #77930 update table gives error 1592 Unsafe statement written to the binary log
Submitted: 4 Aug 2015 9:58 Modified: 5 Jun 2018 5:15
Reporter: Mordechai Danielov Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: Errors Severity:S2 (Serious)
Version:MySQL Community Server (GPL) 5.6.25 OS:Any
Assigned to: CPU Architecture:Any

[4 Aug 2015 9:58] Mordechai Danielov
Description:
BINLOG_FORMAT = STATEMENT
target table has an autoincrement column, but it isn't the one being updated
another table is being joined, but there is no order by
I also tried creating an temp table and joining on the PK, but that made no difference for this case

How to repeat:
table definitions and a sample query:

CREATE TABLE `tbl_xrays_done_per_visit` (
  `Visit_Code` int(11) DEFAULT NULL,
  `Xray_Type_ID` int(11) DEFAULT NULL,
  `Side_ID` int(11) DEFAULT NULL,
  `Xray_UID` int(11) NOT NULL AUTO_INCREMENT,
  `TimeDone` datetime DEFAULT NULL,
  `Tech_ID` int(11) DEFAULT '0',
  `Xray_Diagnosis` text,
  `signedBy_ID` int(11) DEFAULT NULL,
  `noReportNeeded` tinyint(1) DEFAULT '0',
  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`Xray_UID`),
  KEY `Side_ID` (`Side_ID`),
  KEY `Tech_ID` (`Tech_ID`),
  KEY `TimeDone` (`TimeDone`),
  KEY `Visit_Code` (`Visit_Code`),
  KEY `Xray_Type_ID` (`Xray_Type_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=101236 DEFAULT CHARSET=utf8;

CREATE TABLE `tbl_visits` (
  `Visit_Code` int(11) NOT NULL AUTO_INCREMENT,
  `Visit_Date` datetime DEFAULT NULL,
  `Last_Name` varchar(20) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `First_Name` varchar(20) DEFAULT NULL,
  `last_name_e` varchar(20) DEFAULT NULL,
  `first_name_e` varchar(20) DEFAULT NULL,
  `ID_Num` varchar(10) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `DOB` date DEFAULT NULL,
  `Gender` char(1) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `PhoneNum_Home` varchar(20) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `PhoneNum_Mobile` varchar(20) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `Complaint` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `BranchCode` varchar(10) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `referring_Doctor` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`Visit_Code`),
  KEY `Visit_Date` (`Visit_Date`),
  KEY `BranchCode` (`BranchCode`)
) ENGINE=InnoDB AUTO_INCREMENT=95015 DEFAULT CHARSET=utf8;

CREATE TEMPORARY TABLE IF NOT EXISTS t_visits AS (select x.Xray_UID from  tbl_xrays_done_per_visit x 
inner join tbl_visits v on v.visit_code=x.visit_code 
where 
TimeDone>=adddate(now(),INTERVAL -7 DAY) 
and x.noReportNeeded=1
and v.Complaint in('something') 
 limit 10);
 
 update tbl_xrays_done_per_visit x 
inner join t_visits v on v.Xray_UID=x.Xray_UID 
set x.noReportNeeded=1 ;
[5 May 2018 5:15] MySQL Verification Team
Sorry for to be late. It's possible you provide a script to populate the tables and so repeat the issue reported?. Thanks.
[6 Jun 2018 1:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".