Good tip, thank you.

At first, it didn't seem helpful, since many of the statements I was looking at did not flag a warning at all (I provided a couple examples earlier, but there were many more that were problematic). After digging a little further I found the explanation on this page:

https://mariadb.com/kb/en/library/binary-log-formats/

specifically:

"If row-based logging is used for a statement, and the session executing the statement has any temporary tables, row-based logging is used for the remaining statements until the temporary table is dropped."

Turns out many of the problem statements I was analyzing were in fact after an RBR involving a temporary table, which I didn't bother dropping until the session died. Dropping the temporary table explicitly helped clear up much of the confusion (note that such statements evidently do not generate a "statement unsafe for statement-based replication" warning; they just get silently converted to RBR).

Having said all that, though, the other example I provided in my earlier message does generate a warning (and in fact it was the culprit statement in the scenario above, since it does use temporary tables):

"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"

(The statement for reference, so you don't need to dig down into the other E-mail):

UPDATE mydb.t1 AS i LEFT JOIN mydb.t2 AS j1 ON ( i.f1 = j1.f1 ) LEFT JOIN mydb.t3 AS j2 ON ( i.f1 = j2.f1 AND i.f2 > 0 AND i.f2 = j2.f2 ) SET i.f3 = IF ( i.f2 < 0, IF ( j1.f3 IS NULL, i.f3, j1.f3 ), IF ( j2.f3 IS NULL, i.f3, j2.f3 ) ) , i.f4 = IF ( i.f2 < 0, IF ( j1.f3 IS NULL, i.f4, j1.f3 ), IF ( j2.f3 IS NULL, i.f4, j2.f3 ) ) WHERE i.f5 != 0 AND i.f2 != 0 AND i.f6 = 1 AND i.f7 = 0

Sorry to be dense, but the explanation is just not clicking for me. Is there somewhere that provides more explanation and perhaps some examples?

Both t2 and t3 are temporary tables, built and loaded just prior to this statement (those statements are all written as STATEMENT). t1 is a regular table with a single PK and no other UNIQUE keys. It does have a trigger that executes AFTER INSERT to insert a record into a second table.

Thanks,

Dan

On 3/29/2019 9:12 AM, Sergei Golubchik wrote:

Hi!

Try to set the binlog format to STATEMENT. Then you should get a warning that "statement is unsafe... because ..." That is, there will be some kind of an explanation.

On Mar 28, mariadb@biblestuph.com wrote:

...

Using binlog-format=MIXED I'm trying to get my head around why certain statements are being written as RBR instead of as a statement. On my 10.3 test server running a fraction of my full load binary logs are getting written to the tune of about 30M/minute (the 5.5 production server with a currently much heavier load writes about 8.5M/minute).

All of the tables are currently MyISAM.

Here's a couple examples:

This always gets written as a statement in 10.3:

UPDATE mydb.t1 SET rundate = '2019-03-28 23:04:00' , runtime = UTC_TIMESTAMP() , runstatus = 1 WHERE hostname = 'my.host.com'

While this always gets written as a row:

UPDATE mydb.t1 SET rundate = '2019-03-28 23:04:00' , runtime = UTC_TIMESTAMP() , runstatus = 10 WHERE hostname = 'my.host.com'

If it matters, runstatus (the only difference in the two) is a TINYINT(3) UNSIGNED NOT NULL DEFAULT 0. Both statements are written as statements in 5.5.

And this one always gets written as RBR on 10.3, but I don't see why. I reviewed the information at https://mariadb.com/kb/en/library/unsafe-statements-for-statement-based-repl... but can't seem to see what is triggering it.

UPDATE mydb.t1 AS i LEFT JOIN mydb.t2 AS j1 ON ( i.f1 = j1.f1 ) LEFT JOIN mydb.t3 AS j2 ON ( i.f1 = j2.f1 AND i.f2 > 0 AND i.f2 = j2.f2 ) SET i.f3 = IF ( i.f2 < 0, IF ( j1.f3 IS NULL, i.f3, j1.f3 ), IF ( j2.f3 IS NULL, i.f3, j2.f3 ) ) , i.f4 = IF ( i.f2 < 0, IF ( j1.f3 IS NULL, i.f4, j1.f3 ), IF ( j2.f3 IS NULL, i.f4, j2.f3 ) ) WHERE i.f5 != 0 AND i.f2 != 0 AND i.f6 = 1 AND i.f7 = 0

The 10.3 logs don't offer anything by way of explanation, presumably because I have it set to MIXED. Looking at it I see I can use a COALESCE instead of the inner IFs, but I'm still unclear why it must be row-based.

Regards, Sergei Chief Architect MariaDB and security@mariadb.org

Nope, all three were MyISAM.

After I sorted out the issue with all of the session's statements becoming RBL until I dropped the temp tables, I was left with two statements that were still giving me trouble. One was the one below, the other another statement against t1.

The only thing I could see they had in common (other than the table being updated) was that they were both multi-table updates; I.E., of the format:

UPDATE t1 JOIN t2 ON ( t1.f1 = t2.f1 ) SET t1.f2 = 'someThing' WHERE t2.f2 = 'someThingElse';

While my original query below had temp tables, the other statement did not; both tables in the query were permanent tables.

I ended up redoing those queries to instead select out the keys/values from t1 I needed to change in a separate query and then following it with a single table update statement directly against t1. Not atomic but for what I was doing accomplished the same thing.

Still unclear as to exactly why the original statements were RBL though. Perhaps there's not enough trust that t2 will look on the slave exactly like it does on the master?

On 3/30/2019 3:22 PM, Sergei Golubchik wrote:

Hi, Dan!

Is t1 by any chance an InnoDB table that has a foreign key relationship to another table that has an auto-increment column? If yes - it might be a bug I've fixed just this week :)

On Mar 29, mariadb@biblestuph.com wrote:

...

"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"

UPDATE mydb.t1 AS i LEFT JOIN mydb.t2 AS j1 ON ( i.f1 = j1.f1 ) LEFT JOIN mydb.t3 AS j2 ON ( i.f1 = j2.f1 AND i.f2 > 0 AND i.f2 = j2.f2 ) SET i.f3 = IF ( i.f2 < 0, IF ( j1.f3 IS NULL, i.f3, j1.f3 ), IF ( j2.f3 IS NULL, i.f3, j2.f3 ) ) , i.f4 = IF ( i.f2 < 0, IF ( j1.f3 IS NULL, i.f4, j1.f3 ), IF ( j2.f3 IS NULL, i.f4, j2.f3 ) ) WHERE i.f5 != 0 AND i.f2 != 0 AND i.f6 = 1 AND i.f7 = 0

Sorry to be dense, but the explanation is just not clicking for me. Is there somewhere that provides more explanation and perhaps some examples?

Both t2 and t3 are temporary tables, built and loaded just prior to this statement (those statements are all written as STATEMENT). t1 is a regular table with a single PK and no other UNIQUE keys. It does have a trigger that executes AFTER INSERT to insert a record into a second table.

Thanks, Dan

Regards, Sergei Chief Architect MariaDB and security@mariadb.org