maria-discuss team mailing list archive

[Sergei Golubchik <serg@xxxxxxxxxxx>]

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