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@biblestuph.com 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@mariadb.org