maria-discuss team mailing list archive

[mariadb@xxxxxxxxxxxxxx]

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.

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?

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