I am running MySQL 5.0 and WAS receiving an error for the SQL statement that had the 'ON DUPLICATE KEY UPDATE'. After doing a little investigation and looking into the MySQL docs I have fixed the issue.
I dropped the entire WHERE from the statement. According to MySQL docs, there is no WHERE in the statement. If a record key exists, that record is used in the 'ON DUPLICATE KEY UPDATE' part of the statement. Or, maybe I downloaded the wrong version of the plugin. On Saturday, December 31, 2005 at 5:55:31 AM, [EMAIL PROTECTED] confabulated: > I have updated the INSERT code to use the "ON DUPLICATE KEY" feature of > MySQL. This feature insures that if the insert statement is executed > and another insert has already occurred for the changing day, an update > will take place instead. Hopefully, this will alleviate the largest > possibility of a race condition. However, this is a feature of MySQL > that is only available inside of MySQL 4.1 and greater. If you are NOT > running at least MySQL 4.1 you can get the old code from my website > listed below. > Where to get the changes - > http://wiki.apache.org/spamassassin/StatsPlugin > or > http://www.okeating.net/blosxom.cgi/2005/12/31#statsplugin-update > Let me know if you see any other issues or problems. > Thanks for everyone's input, more is always appreciated. > Thanks, > James > Jim C. Nasby wrote: >> On Wed, Dec 28, 2005 at 02:19:51AM -0600, Chris Thielen wrote: >> >>>James Keating wrote: >>> >>> >>>>Well the simplest fix is the one that I did not implement in the first >>>>place, using "ON DUPLICATE KEY". However, I did not implement that >>>>because of its only being in version 4.1 of MySQL and I still use >>>>Debian stable for most production machines, which runs 4.0.x. >>>> >>>>Anyway, I will poke at it some more. >>>> >>> >>>Read on for a portable fix called optimistic locking. >>> >>> >>>Pseudocode follows. I assume you are doing something like this: >> >> >> You assume wrong. :) The code is doing >> >> UPDATE ... SET field = field + 1 ... >> >> which is almost always a much better idea than doing the increment in >> code. >> >> The race condition is in checking to see if the row already exists, and >> then using that information to decide if you should do an insert or an >> update. >> >> The example at the very bottom of http://lnk.nu/postgresql.org/5sl.html >> is how you would do this in a function in PostgreSQL, though there's no >> reason I can think of why you couldn't do that in perl instead (though >> it would perform much slower). -- "This message is made of 100% recycled electrons."
