#22 sqlgrey DB insert race condition

[Anonymous]

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

[Anonymous]

the error I pasted above was from mx2:

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

[L0RE]

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