maria-discuss team mailing list archive
Mailing list archive
Re: Row vs Statement Replication in 10.3
> 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
> 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.
> 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"
>>> 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 )
>>> 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 )
>>> 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.
>> 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