Beefy Boxes and Bandwidth Generously Provided by pair Networks
There's more than one way to do things

Re: Of Mysql, DBI and PK best practices

by jimbus (Friar)
on Apr 17, 2007 at 14:32 UTC ( #610534=note: print w/replies, xml ) Need Help??

in reply to Of Mysql, DBI and PK best practices

I parallel posted this to MySQL's forum and it was suggested that I was looking for the MySQL syntax "INSERT ... ON DUPLICATE KEY UPDATE".

I'd never heard of such a thing and a brief google search showed it is probably what I'm looking for (I'm a pretty big believer that with reasonable usage, the db is usually going to be more efficient that anything I write). I spent hours searching Google for options on what I was seeing: "MySQL primary key violation on insert" and got no where.

--Jimbus aka Jim Babcock
Wireless Data Engineer and Geek Wannabe
  • Comment on Re: Of Mysql, DBI and PK best practices

Replies are listed 'Best First'.
Re^2: Of Mysql, DBI and PK best practices
by andye (Curate) on Apr 17, 2007 at 17:59 UTC
    Hi jimbus,

    I've used ON DUPLICATE KEY UPDATE in MySQL and it pretty much does exactly what it says on the tin. Seems to work well.

    A more database-neutral alternative is to select the key from the table, and if it doesn't exist then insert, otherwide update.

    You'll need to wrap this in a transaction to avoid the race condition kyle mentions above (unless you are the only process working on the db). In MySQL this means you either need to get a table lock, or switch your table type to InnoDB and do a row-level transaction as described on this page: InnoDB locking reads.

    By the way, ON DUPLICATE KEY UPDATE will lock the auto-increment table if you have an auto-increment field in your table, so this situation is one that can cause locking problems whichever solution you choose, if you have a lot of concurrent inserts/updates.
    (update: will only lock the auto-increment table if an insert actually takes place, I think - still, a potential problem)

    As I see it, the problem with the original solution (use insert, check for error, use insert if update didn't work) is, what if the error wasn't a duplicate key error? To do this properly, I think you'd need to check the error type.

    That way you could try again with an insert if the error was a duplicate key error, or if it was a different error then report the error and exit as usual. You'd need to ensure that RaiseError was switched off on your DBI handle.


    Best wishes, andye

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://610534]
and !@monks...

How do I use this? | Other CB clients
Other Users?
Others studying the Monastery: (5)
As of 2018-05-25 20:57 GMT
Find Nodes?
    Voting Booth?