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.
- James
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."
