3

MySQL version: 5.5.13

If I set binlog format to STATEMENT, I got the following warnings on the Master:

[Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave...

I also read the limitations of replicate stores procedures and functions: http://dev.mysql.com/doc/refman/5.5/en/stored-programs-logging.html

But if I switch to MIXED, mysqld.log on the Slave shows:

[Warning] Slave SQL: Could not execute Update_rows event on table hdcn.sessions; Can't find record in 'sessions', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.003834, end_log_pos 602692401, Error_code: 1032

[Warning] Slave SQL: Could not execute Delete_rows event on table reportingdb.102_rpt_clickview; Can't find record in '102_rpt_clickview', Error_code: 1032; handler error HA_ERR_END_OF_FILE; the event's master log mysql-bin.003834, end_log_pos 725203511, Error_code: 1032

Looks like the MIXED binlog format causes the Master doesn't replicate completely to Slave.

I switched back to STATEMENT format. Can I ignore the unsafe warnings?

quanta
  • 51,413
  • 19
  • 159
  • 217

2 Answers2

3

I assume you did this on one setup without cleaning up the slave and setting it up from scratch.

As the error message for the statement based replication tells you your application used some commands which can't be replicated using statement based replication. An example is a query like

INSERT INTO t (t) VALUES(NOW())

where NOW() will return different values when executed on the master and on the slave.

By doing this you have different data on the master and slave. Which is bad as depending on your slave your clients will read different data and subsequent writes will change other data, so you end up with data which is even more different.

Now you switch over to mixed replication, which might use row-based replication for some statements. With RBR you really need the exact same data as it has a hard time to identify the rows which were changed and update them.

So what should you do? - Configure your master to use mixed logging and then setup the slave using a consistent snapshot.

johannes
  • 583
  • 2
  • 10
  • I have a question: what is the safest way to switch replication format at runtime? How do I know if there is any temporary tables exist? – quanta Sep 29 '11 at 09:11
  • The safest way is to configure the master accordingly, create a consistent dump and create the slaves from that. – johannes Sep 29 '11 at 09:13
1

You must eliminate all chance of Inserts, Updates, open temp tables, etc...

  1. on Slave(s): 
stop slave;
flush tables with read lock;
  1. on Master:
flush tables with read lock;
set global binlog_format='MIXED';
unlock tables;
  1. on Slave(s): 
unlock tables;
start slave;
Dr. Tyrell
  • 111
  • 2