I've tried to restore a MySQL database using incremental binlogs and mysqlbinlog tool but I received the following message:

ERROR 1666 (HY000) at line 30: Cannot execute statement: impossible to write to binary log since statement is in row format and BINLOG_FORMAT = STATEMENT.

BINLOG_FORMAT parameter in my database is set to ROW, but is there a way to restore binglog with this parameter in ROW?

  • Are you sure the database BINLOG_FORMAT is ROW? Could you post MySQL version and my.cnf?
    – Emyl
    Commented Oct 4, 2013 at 14:28

1 Answer 1


I believe you will find the server will tell you that it is configured for STATEMENT since there's not another good reason why you'd get this error.

The error message is referring to the server where you're trying to restore the files as currently configured, not the format the server may have been in when the log was created.

MySQL has two logging formats for replication, ROW, which logs the actual changes made to individual rows, and STATEMENT, which logs the queries that made the changes. The MIXED format isn't a different format, but instead is a configuration setting that allows the server to select the format for each query based on internal rules.

When a server has binary logging enabled and executing queries from an incoming binlog, it has to write the queries to its own binlog, but it doesn't just copy the incoming log entry into the outgoing log... if the server is configured for ROW, it will always translate incoming STATEMENT events into ROW events. If it is configured for MIXED, then it may log incoming STATEMENT events as outgoing STATEMENT events or it may log them as ROW... but if it is configured for STATEMENT then it is unable to log incoming ROW events, and the error you're seeing would be what is returned.

 -- server receives  -- server global   -- server writes          
 -- incoming event   -- binlog_format   -- outgoing event

    STATEMENT           MIXED              STATEMENT or ROW
    STATEMENT           ROW                ROW
    ROW                 STATEMENT          XXX NOT SUPPORTED XXX
    ROW                 MIXED              ROW
    ROW                 ROW                ROW

If SHOW GLOBAL VARIABLES LIKE 'binlog_format'; really does say ROW I'll be surprised, but if not, then your fix should be this:

  • 1
    +1 This answer should be in mysql official doc. I spend hours to find a definitive answer for binlog_format.
    – Reed
    Commented Dec 3, 2017 at 1:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.