Upgrade 1.7->1.8 Test Warnings from update.sql specific to users that use REPLICATION

I am concerned we may cause replication breaks using the update.sql

 

This row is an example:

INSERT INTO lookups (version, linkId, arch_short_name, active, arch_version, code, name, description, default_lookup)   SELECT     0,     UUID(),     'lookup.diagnosisVeNom',     1,     '1.0',     d.code,     d.term,     d.modelling,     0   FROM venom_diagnosis d   WHERE NOT exists(SELECT                      *                    FROM lookups e                    WHERE e.arch_short_name = 'lookup.diagnosisVeNom' AND e.code = d.code

The sql is completed but the following error is produced

2326 row(s) affected, 2 warning(s): 1592 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. 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements writing to a table with an auto-increment column after selecting from another table are unsafe because the order in which rows are retrieved determines what (if any) rows will be written. This order cannot be predicted and may differ on master and the slave. Records: 2326  Duplicates: 0  Warnings: 2

As you can see we are using UUID() which is unsafe it terms of MYSQL's statement based logging.  

I think we need to release a caveat regarding REPLICATION that the update procedure may not be replication safe particularly if the setup is using STATEMENT based replication.  

The safest replication is actually ROW BASED replication. but maybe the best in terms of tradeoffs for safety v log size is actually mixed format

MySQL's (atleast in 5.6) default is actually STATEMENT based logging.

https://dev.mysql.com/doc/refman/5.6/en/replication-formats.html

The problem wont really be an issue until a client has to switch to a slave due to a master failure...then they may find data inconsistencies.

 

 

 

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.

Re: Upgrade 1.7->1.8 Test Warnings from update.sql specific ...

This warning occurs 7 times in the Update SQL for 1.7->1.8

Regards
 
Ben 
OpenVPMS Installer and Helper 
Ph: +61423044823 
Email: info[at]charltonit.com[dot]au

Re: Upgrade 1.7->1.8 Test Warnings from update.sql specific ...

Syndicate content