Description:
A recent replication stoppage when forcing back a move to SBR from RBR which was required for various reasons gives this error message:
2015-06-15 09:47:20 13429 [ERROR] Slave SQL: Error executing row event: 'Cannot execute statement: impossible to write to binary log since statement is in row format and BINLOG_FORMAT = STATEMENT.', Error_code: 1666
2015-06-15 09:47:20 13429 [Warning] Slave: Cannot execute statement: impossible to write to binary log since statement is in row format and BINLOG_FORMAT = STATEMENT. Error_code: 1666
The problem with this error message is that I don't know which statement triggered the problem. I know that RBR logging of errors is rather "limited" but it would help to at least provide a clue as to which database or table was being affected and ideally which type of action. (if you can't provide the mysqlbinlog equivalent of --base64-output=AUTO output)
How to repeat:
Try to switch back to SBR, and note that unless you do this very carefully it's tricky.
It can be done under certain circumstances, but the typical error message you get is the one as shown above. This doesn't point you to "which RBR event is causing the problem" as by the time you get here you expect that any upstream master ( this will be running on an intermediate master ) will have already been converted to SBR.
Suggested fix:
RBR error reporting as reported elsewhere is not ideal and often incomplete which can make diagnosis of problems and thus their resolution harder.
In this specific case if you gave enough information about the "offending" statement then it would be easier to track it down.
I still personally think that if you switch to SBR from RBR then any RBR events should just be passed "downstream" (into the binlogs) "as is" (as RBR events) and processed normally. That avoids all these headaches and would be much more flexible. Note: this is not the same as setting a box to MIXED as all "generated" events on the master would be in STATEMENT based mode if I set binlog_format = STATEMENT, yet any "received" RBR events would be passed on untouched.
If you won't do that please add some information as to the event that causes the source of the problem, such as the db name, table name, "operation" (insert/update/delete) etc. so that the actual source of the event is easier to track down. (On a busy server there may be several processes inserting data so identifying the "bad one" is the real issue here.)
