Replication: Unsafe statement written to binary log

For complex topics that regular users would not be interested in. For power users and database administrators.
Post Reply
drawab
Posts: 46
Joined: Tue Apr 01, 2008 4:57 am

Replication: Unsafe statement written to binary log

Post by drawab » Mon Dec 21, 2015 11:36 pm

Hi

After cautiously stepping into replication database as my solution to managing one database across two locations - my server error logs continuously show this type of warnings every read/write

Code: Select all

2015-12-22 11:36:34, 2040, Warning, Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement accesses nontransactional table as well as transactional or temporary table, and writes to any of them. Statement: UPDATE tempagingEM0nsDaoOiO6L5 a [truncated, 474 bytes total]
it seems to be a design issue of OD database MySQL wanting ROW based replication and Open Dental insisting on Statement based replication. Im ready to ignore this warning (or have been ignoring it) but thought to bring it to your notice if it required any update


---------
In other logs when starting the salve - this comes up as well

Code: Select all

Timestamp, Thread, Type, Details
2015-12-19 20:22:24, 1888, Warning, Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual f [truncated, 276 bytes total]
---------
While in the logs I have caught this error as well - which was solved once I restarted the slave properly with adequate credentials

Code: Select all

Timestamp, Thread, Type, Details
2015-12-19 20:22:24, 1888, ERROR, Native table 'performance_schema'.'cond_instances' has the wrong structure
Please keep in mind - none are critical and replication is functioning seamlessly - but i bring this up for OD team to possibly see if anything is up

User avatar
jsalmon
Posts: 1551
Joined: Tue Nov 30, 2010 12:33 pm
Contact:

Re: Replication: Unsafe statement written to binary log

Post by jsalmon » Tue Dec 22, 2015 9:13 am

drawab wrote:

Code: Select all

2015-12-22 11:36:34, 2040, Warning, Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement accesses nontransactional table as well as transactional or temporary table, and writes to any of them. Statement: UPDATE tempagingEM0nsDaoOiO6L5 a [truncated, 474 bytes total]
it seems to be a design issue of OD database MySQL wanting ROW based replication and Open Dental insisting on Statement based replication. Im ready to ignore this warning (or have been ignoring it) but thought to bring it to your notice if it required any update
Yes, in a way you are right. MySQL is replicating temporary tables which Open Dental does not want it to do but there isn't a way to turn that off so we tacked on a random alphanumeric string to the end of the temporary table name to avoid collisions across replication as a bandaid fix. Eventually we will write our aging calculations in a way that doesn't utilize MySQL temporary tables but it is one of, if not the most, complex sections of our software that we are terrified to change. Any temp table that fails to replicate is absolutely fine in Open Dental's eyes so continue to ignore these types of warnings.
drawab wrote:

Code: Select all

Timestamp, Thread, Type, Details
2015-12-19 20:22:24, 1888, Warning, Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual f [truncated, 276 bytes total]
I've honestly never heard of this before. You are using MySQL 5.5 correct?
drawab wrote:While in the logs I have caught this error as well - which was solved once I restarted the slave properly with adequate credentials

Code: Select all

Timestamp, Thread, Type, Details
2015-12-19 20:22:24, 1888, ERROR, Native table 'performance_schema'.'cond_instances' has the wrong structure
This is actually a known thing and you can get rid of the 'performace_schema' warnings if they bother you by running the MySQL upgrade tool. See the Performance Schema Errors section on our MySQL upgrade page:
http://www.opendental.com/manual/mysql55.html
The best thing about a boolean is even if you are wrong, you are only off by a bit.

Jason Salmon
Open Dental Software
http://www.opendental.com

Post Reply