← Back to team overview

maria-discuss team mailing list archive

Re: Row vs Statement Replication in 10.3

 

Hello.

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

So you're dealing with the fixes of

  https://bugs.mysql.com/bug.php?id=50440


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

not the table but the master way selecting from it is not trusted.
The bug description puts it very explicitly.

Cheers,

Andrei


>
> 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@xxxxxxxxxxxxxx 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@xxxxxxxxxxx
>>
>
> _______________________________________________
> Mailing list: https://launchpad.net/~maria-discuss
> Post to     : maria-discuss@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~maria-discuss
> More help   : https://help.launchpad.net/ListHelp


References