You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@spamassassin.apache.org by James Keating <ja...@okeating.net> on 2005/12/31 06:55:31 UTC
Re: Real-Time Stats Plugin Released - UPDATE RELEASED
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).
Re: Real-Time Stats Plugin Released - UPDATE RELEASED
Posted by Duane Hill <d....@yournetplus.com>.
On Monday, January 2, 2006 at 6:50:15 PM, jamesk@okeating.net 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, jamesk@okeating.net 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."
Re: Real-Time Stats Plugin Released - UPDATE RELEASED
Posted by James Keating <ja...@okeating.net>.
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, jamesk@okeating.net 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."
>
Re: Real-Time Stats Plugin Released - UPDATE RELEASED
Posted by Duane Hill <d....@yournetplus.com>.
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, jamesk@okeating.net 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."