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