Re: REPLACE INTO and CASCADEs.
Hi Heikki, everyone, Other than changing all my REPLACE INTO codes to UPDATE, does anyone have a quick solution to this? I have quite alot of code that makes use of what I thought REPLACE INTO was supposed to be doing... so was hoping that someone has managed to come up with some neat trick that can "simulate" the "UPSERT". :) Thanking in advance. Regards, Tan Shao Yi - Original Message - From: Heikki Tuuri Date: February 16 2004 9:12am Subject: Re: REPLACE INTO and CASCADEs. 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 On Sun, 15 Feb 2004, Tan Shao Yi wrote: > 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). > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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]
Re: Backup & Recover Database
On Fri, 6 Feb 2004, Tristan Fiedler wrote: > Dear MySQL'ers, > > I have backed up my db using : > > % mysqldump -p -u tfiedler --opt BigData >./db_backup.sql > > When db_backup.sql is scp'd (secure copy) to another machine running > mysqld, then I ssh to that machine and execute : > > $ mysql -u tfiedler BigData < db_backup.sql > ERROR 1044: Access denied for user: '@localhost' to database 'BigData' Hello, You specified a "-p" to the mysqldump command. Would you like to try specifying the "-p" parameter to the mysql command too? :) Cheers, Tan Shao Yi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: "Bind on Unix Socket: Permission Denied"
On Mon, 22 Dec 2003, Aleksei Wolff wrote: > I just installed the server and I am getting this > message in the mysql.log when I execute: > > ./bin/safe_mysqld --user=mysql & Hi Alex, You might want to check the permissions of your /tmp directory. Is it "drwxrwxrwt"? :) Regards, Tan Shao Yi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with count(*)
On Mon, 11 Aug 2003, gord barq wrote: > I have a table I'm using for logging purposes with a schema like: > > create table results ( > user varchar(255) > > ); > > Where user is not a unique field and I want to find out how many unique > users there are in the table. > > I want to do something like: > > select count(count(*)) from results group by user; > > But that doesn't work.. Hello, How about "SELECT COUNT(DISTINCT user) FROM results"? Hope this helps. Cheers, Tan Shao Yi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: no duplicate entries..
On Sun, 20 Jul 2003, harsh wrote: > > Hi all, > How do i do this ? > i have a table with one of the column named uid, > Now i want to insert a new row,only if no other > row exist with the uid i m trying to insert. > I hope its clear. Hello Harsh, How about making the uid column in your table a primary key since it is to contain only unique values? Cheers, Tan Shao Yi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
