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

[Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. CREATE... IGNORE SELECT is unsafe because the order in which rows are retrieved by the SELECT determines which (if any) rows are ignored. This order cannot be predicted and may differ on master and the slave. Statement: create temporary table IF NOT EXISTS vt_tmp_u1098(id int(11) primary key) ignore (SELECT 1098 as id) UNION (SELECT vtiger_user2role.userid AS userid FROM vtiger_user2role INNER JOIN vtiger_users ON vtiger_users.id=vtiger_user2role.userid INNER JOIN vtiger_role ON vtiger_role.roleid=vtiger_user2role.roleid WHERE vtiger_role.parentrole like 'H1::H2::H31::H32::%') UNION (SELECT groupid FROM vtiger_groups where groupid in (777,778))


23:17:39' /* user:maintenance/update_mysql_from_logs.pl bt:main,486,445,364,164,79 */

2018-11-09T16:42:03.193203-08:00 8747303 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. INSERT... ON DUPLICATE KEY UPDATE  on a table with more than one UNIQUE KEY is unsafe Statement: INSERT INTO demographics (pwsid, active, sex, bday, bday2, lat, lon, lastlogin, handle, race, body_type, sex_orient, marital, state, country, cupid_sex) VALUES ('2155832_48095', '1', '1', '1954-02-01', '1900-00-00', '25.7979', '-80.3071', '2018-11-09 16:41:16', 'e409', '4', '1', '0', '0', 'Florida', 'United States', '2') ON DUPLICATE KEY UPDATE active = '1',lastlogin = '2018-11-09 16:41:16' /* user:maintenance/update_mysql_from_logs.pl bt:main,486,445,364,164,79 */



The error message it gives you is pretty good actually. Basically the statement that it is warning you about could potentially end up with different results when it runs on the slave versus when it ran on the master. This is something that a tool like Percona's pt-table-checksum (see below link) can help with to detect what is called slave drift or data drift, which is when the slave has different data than the master.

What to actually do about this (other than check your tables with the tool I mentioned) depends on the situation. The query it is warning you about may end up working perfectly fine, but you would not know for sure without checking, and even then it may work sometimes and not others.

The best option in my opinion is to switch to row based replication to avoid this, as then MySQL is replicating the data itself and not the statement. Row based replication has some downsides, mainly that more data is transferred, so slave lag may be more of an issue in extreme cases, so you'll want to read up on that first before making any changes.

The other option would be to re-write the query to avoid any functions / formatting that results in unsafe queries for statement best replication


Mixed format does pretty much what it sounds like, but I've never used it myself and have not seen many other people using it. So that choice is up to you, but I would make sure to research / test it first.

MIXED mode is a good compromise between statement and ROW, and will work for the statement you had in warning message. Basically, for any data update that is "unsafe" from replication point of view, MySQL will log all changed rows values instead of the original statement. But it will keep logging other updates the same way as in statement mode.

In "MIXED" mode, replicated transactions are written in either of two formats: row based logging, or statement based login. MySQL decides to use statement or row based log entry if it considers a given statement is "safe" or not. So "safe" updates will be logged just like in the STATEMENT binlog format, and "not-safe" will be logged as row-based events. This way the MIXED mode is more safe then STATEMENT from data integrity point of view. It's just a compromise between pure STATEMENT or pure ROW modes.
Now, whether to use pure ROW format, we can argue, as there are many pros and cons of this solution

NOTE:
The binlog_format variable is dynamic, so you may change it at any point. The cleanest way would be to set it in your my.cnf on the master and restart the server, otherwise you can stop replication on your slave, change the setting dynamically on the master, and then start replication on your slave so that it picks up the change since a change in global variables only applies to new connections.



Secondly to just switch to "ROW" based replication, I can see that all i need to do is specify the command globally for all. 
like this :- mysql> SET GLOBAL binlog_format = 'ROW';