maria-discuss team mailing list archive

[Sergei Golubchik <serg@xxxxxxxxxxx>]

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@xxxxxxxxxxxxxx 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 security@xxxxxxxxxxx