On Monday, January 2, 2006 at 6:50:15 PM, [EMAIL PROTECTED] confabulated: > You did have the latest version Duane.
> I guess MySQL 4.1 doesn't care if you have the "WHERE" clause after it; > as I have been using the updated version for sometime and have not > received any errors. But MySQL 5.0, apparently, doesn't like that > syntax. I removed the WHERE clause and tested it with MySQL 4.1 and it > still appears to function properly. > I have updated the SpamAssassin Wiki and the code on my site. > Thanks for catching that Duane. Not a problem. Glad I can be of some assistance on this list finally. SA was completely new to me when I joined the list and usually I sit back in the woodwork and let things kind of soak in for a bit before attempting to aid in something. I usually like to make sure I have most, if not all, the ground work done in a solution to something rather than having to go back and forth a few times. That was hard at first on this one because MySQL is still relatively new to me. Anyhow, the plugin is working like a charm at this time. Thanks! > Duane Hill wrote: >> 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."
