1548 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT

Question

I got the following warning running the simple statement and I was curious as to why I got it:

UPDATE `Table1` 
SET `City`='Miami', 
    `ExpDate`='201227', 
    `User`='JDoe', 
    `UpdDate`='2015-02-17 16:11:25' 
WHERE `id` = 61`

Here is the Table1 structure:

CREATE TABLE `Table1` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `User` varchar(10) COLLATE utf8_bin DEFAULT NULL,
  `City` varchar(25) COLLATE utf8_bin DEFAULT NULL,
  `ExpDate` varchar(10) COLLATE utf8_bin DEFAULT NULL,
  `UpdDate` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `Unique_Index` (`User`,`City`),
  UNIQUE KEY `id` (`id`),
  KEY `ALT1_IDX_Table1` (`User`,`City`)
) ENGINE=InnoDB AUTO_INCREMENT=64 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

Full Error from log:

2015-02-17 16:10:08 1548 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it invokes a trigger or a stored function that inserts into an AUTO_INCREMENT column. Inserted values cannot be logged correctly. Statement: UPDATETable1SETCity='Miami',ExpDate='201227',User='JDoe',UpdDate='2015-02-17 16:11:25' WHEREid= 61

We need the table structure to identify the error. Edit: Especially the indexes, CREATE code would help. — Brain Foo Long, Feb 18 '15 at 14:22
@BrainFooLong - I posted the structure. Is that what you meant? — xaisoft, Feb 18 '15 at 14:29
Nearly, the complete CREATE TABLE code would help more. As of now there seems no error - Maybe you will find it when you search here http://dev.mysql.com/doc/refman/5.6/en/replication-rbr-safe-unsafe.html — Brain Foo Long, Feb 18 '15 at 14:30
At first, remove the key `UNIQUE KEY id`, primary keys are itself unique. Than remove `KEY ALT1_IDX_Table1`, you already have a UNIQUE key on that 2 fields. Than try again - I've got the errors everytime in combination with weird keys. — Brain Foo Long, Feb 18 '15 at 14:41
PS: Maybe the complete error from the log will help us to solve the problem. — Brain Foo Long, Feb 18 '15 at 14:44

score 1 · Accepted Answer · answered Feb 18 '15 at 14:51

1

Finally, the error show exactly what's wrong here. Statement is unsafe because it invokes a trigger or a stored function that inserts into an AUTO_INCREMENT column The error is not in the table itself, the error is in a trigger or procedure that fires when this table get's updated.

answered Feb 18 '15 at 14:51

Brain Foo Long

2,057
23
28

You are right. I looked and there are like 5 triggers. I am not too familiar with mySQL. I guess a DBA should be able to solve the problem. I have noticed a lot of people using Mixed Mode instead of Binary. – xaisoft Feb 18 '15 at 14:55
Maybe your DB was not fairly optimized for replications. Maybe there is some work to re-build, depends on your specific db structure. You should ask someone that made this database. – Brain Foo Long Feb 18 '15 at 14:57

1548 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT

1 Answers1