Beefy Boxes and Bandwidth Generously Provided by pair Networks
Do you know where your variables are?
 
PerlMonks  

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
jim-dot-babcock-at-usa-dot-com


Comment on Re: Of Mysql, DBI and PK best practices
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.

    HTH!

    Best wishes, andye

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://610534]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others romping around the Monastery: (5)
As of 2014-08-30 20:33 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The best computer themed movie is:











    Results (293 votes), past polls