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, [email protected] 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-replication/ > > 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 [email protected] _______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : [email protected] Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
