Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Are you sure the database BINLOG_FORMAT is ROW? Could you post MySQL version and my.cnf? –  Emyl Oct 4 '13 at 14:28

1 Answer 1

SHOW GLOBAL VARIABLES LIKE 'binlog_format';

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           STATEMENT          STATEMENT
    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:

SET GLOBAL BINLOG_FORMAT = 'ROW';
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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