mysql_replication_format_error

Event short description: mysql_replication_format_error

This event is notified when Core-Admin log-watcher detects that the local MySQL server is reporting MySQL replication format errors like the following:

Apr 19 06:29:28 machine mysqld: 160419 6:29:28 [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 `report_viewed_product_index` (`visitor_id`,`customer_id`,`product_id`,`store_id`,`added_at`) VALUES ('78903', 
NULL, '1326', '1', '2016-04-19 04:29:28') ON DUPLICATE KEY UPDATE visitor_id = VALUES(`visitor_id`), customer_id = 
VALUES(`customer_id`), product_id = VALUES(`product_id`), store_id = VALUES(`store_id`), added_at = VALUES(`added_at`)

Background

This event is generated by a MySQL error or warning indicating that the replication format used may cause problems to have a truly synchronized database when MySQL replication is enabled.

MySQL replication format is used to export changes to the rest of MySQL servers doing the synchronization using one of these:

  1. STATEMENT: replication format uses SQL as format to report changes.
  2. RAW: binary internal MySQL format to report changes
  3. MIXED: a mixture of the previous formats where MySQL uses STATEMENT or RAW according to the data being replicated.

Resolution

There are several ways to solve this problem:

  1. Review the application that is producing these events to make it generate SQL statements that do not depend on the time and contexts where they were executed.
  2. Alternatively, if this analysis is not possible, configure replication format to use MIXED by running the following as MySQL administrator inside the machine producing the warning:
    SET GLOBAL binlog_format = 'MIXED';
    
  3. For a more permanent solution, you can configure the following into MySQL configuration, usually located at /etc/mysql/my.cnf:
    binlog_format = 'MIXED'
    

    ..and then restart mysql.