maria-discuss team mailing list archive

[mariadb@xxxxxxxxxxxxxx]

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.

Thanks,

Dan