Hi! The REPLACE + FOREIGN KEY behavior indeed changed between 4.0.17 -> 4.0.18, because the old behavior was wrong, according to the MySQL manual:
" Fixed a bug: MySQL should not let REPLACE to perform internally an UPDATE if the table is referenced by a FOREIGN KEY. The MySQL manual states that REPLACE must resolve a duplicate key error semantically with DELETE(s) + INSERT, and not by an UPDATE. In versions < 4.0.18 and < 4.1.2, MySQL could resolve a duplicate key conflict in REPLACE by doing an UPDATE on the existing row, and FOREIGN KEY checks could behave in a semantically wrong way. (Bug #2418) " I am sorry for the inconvenience. The old behavior was maybe more intuitive, if you think of REPLACE as an 'UPSERT' command. But in MySQL it is a 'DELSERT' command. Best regards, Heikki ----- Original Message ----- From: "Tan Shao Yi" <[EMAIL PROTECTED]> Newsgroups: mailing.database.myodbc Sent: Saturday, February 14, 2004 10:29 PM Subject: REPLACE INTO and CASCADEs. > Hello, > > I just upgraded from 4.0.17 to 4.0.18 on a Linux box and realised the > behaviour of REPLACE INTO appears to have changed. > > I have two tables: table2 references table1 with ON DELETE CASCADE and ON > UPDATE CASCADE on a primary key. > > Previously in 4.0.17 when I issued a REPLACE INTO on table1, only the > row in table1 gets "replaced". No rows in table2 get deleted. > > Now, in 4.0.18, when I issue a REPLACE INTO on table1, the row in table1 > gets "replaced", but this replacement gets cascaded into table2 and all > rows in table2 with the primary key get deleted. > > Has anyone encountered something similar? I have only one box available so > I am not able to re-test my observations again (I have reverted to > 4.0.17). > > Thanking in advance. > > > Regards, > Tan Shao Yi > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
