Mysql replication breaks with RAND() data

[Doug Shelley]

There appears to be an issue insert data using the RAND() function into a table on the master node.

To reproduce:
1. Create a 2 node replication network for Mysql 5.6 (i.e. trove create the master and then trove create --replica_of the slave)
2. create a table on the master:
       > create table test1(col1 float)
3. add data
       > insert into test1 select rand()

The table will have replicated to the slave but not the one row of data. Do this on slave:
SHOW SLAVE STATUS

You will see this:
Error executing row event: 'Cannot execute statement: impossible to write to binary log since statement is in row format and BINLOG_FORMAT = STATEMENT.'

The issue is that we aren't explicitly setting the binlog_format on the slave to "MIXED". It is defaulting to "STATEMENT".

It is likely the same issue is happening for Percona and MariaDB.

[OpenStack Infra (hudson-openstack)]

Fix proposed to branch: master
Review: https://review.openstack.org/300253

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/300253
Committed: https://git.openstack.org/cgit/openstack/trove/commit/?id=9b03fec1e174d95b7a6047ba4a15eec3f801cf98
Submitter: Jenkins
Branch: master

commit 9b03fec1e174d95b7a6047ba4a15eec3f801cf98
Author: Doug Shelley <email address hidden>
Date: Thu Mar 31 23:18:38 2016 +0000

    Mysql replicas need to set binlog_format

    For GTID replication we set the replica source (i.e. master)
    to have a binlog_format of MIXED. We aren't setting this on
    the replicas so they default to STATEMENT. This causes a problem
    with certain "non-deterministic" functions (e.g. RAND()). This
    changes replica config templates for Mysql and Percona to use
    MIXED mode.

    A new scenario test was introduced to validate that the
    replicas have this set.

    MariaDB doesn't appear to have this issue so it wasn't changed.

    Scenario tests for Mysql, Percona and MariaDB were run to
    validate this change.

    Change-Id: I936cd9bc53a812af19653e9b5b472103fab2b6c1
    Closes-bug: 1563541

[Doug Hellmann (doug-hellmann)]

This issue was fixed in the openstack/trove 6.0.0.0b2 development milestone.