#22 sqlgrey DB insert race condition

open
nobody
database (9)
5
2010-02-18
2010-02-18
Anonymous
No

Hi,

we just started using sqlgrey in a production environment. Its a great tool and has drasticly lowered our incoming spam!

Im noticing a race condition which is causing a couple of errors per day on our different mailservers, like:

Feb 18 12:14:29 mx2 sqlgrey: dbaccess: warning: couldn't do query: INSERT INTO from_awl (sender_name, sender_domain, src, first_seen, last_seen) VALUES('x','y','z','2010-02-18 11:59:28',NOW()): , reconnecting to DB

We currently have a setup with 3 mailservers in every zonefile, same prio. Mail is quite evenly spread over these servers. All the servers use a central sqlgrey database to keep track of the greylisting.

What happens is, sometimes a mail arrives at mx1 and mx2 at exactly the same time, from the same server and sender. Sqlgrey checks DB from both mailservers and decides the the sender can be added to from_awl. The mailserver which succeeds the first in the resulting INSERT wins. The looser gets an sql error because of the duplicate primary key and decides he needs to reconnect to MySQL and issue an error per e-mail.

There could be several workarounds for this, maybe disabling the primary key so a duplicatie entry doesn't result in error (dunno if duplicate entries will create other probs). Another option would be to use MySQL "INSERT.... ON DUPLICATE KEY UPDATE" type query.

Id like to know if you acknowledge this as a bug or if you have any suggestions.

Thanks!
Robin

Discussion

  • Comment has been marked as spam. 
    Undo

    You can see all pending comments posted by this user  here

    Anonymous - 2010-02-18

    the error I pasted above was from mx2:

    mx2:~# sqlgrey --version
    sqlgrey 1.8.0-rc2

     
  • L0RE

    L0RE - 2010-04-28

    Same problem when using master-master replication,without
    db_cluster = on

    00428 16:10:39 [ERROR] Slave SQL: Error 'Duplicate entry '78.140.95-e-fellows.net-michael.krenz' for key 'PRIMARY'' on query. Default database: 'sqlgrey'. Query: 'INSERT INTO from_awl (sender_name, sender_domain, src, first_seen, last_seen) VALUES
    .....
    Error_code: 1062

     

Get latest updates about Open Source Projects, Conferences and News.

Sign up for the SourceForge newsletter:





No, thanks