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