[Sqlgrey-users] duplicate entry

[Alex]

Hi,

I've been using sqlgrey for a while with mariadb and noticed there is
what appears to be a duplicate entry:

Last_SQL_Error: Error 'Duplicate entry
'69.25.195-mk1.netatlantic.com-bounce-#' for key 'PRIMARY'' on query.
Default database: 'sqlgrey'. Query: 'INSERT INTO from_awl
(sender_name, sender_domain, src, first_seen, last_seen)
VALUES('bounce-#','mk1.netatlantic.com','69.25.195','2016-03-07
11:51:51',NOW())'
Replicate_Ignore_Server_Ids:

I have the system set up with a master on one system and replicated to
three others. This occurred on one of the slaves.

This isn't the first time it's happened. Is this to be expected?

Is it possible it's a problem with my setup?

If I run "select count(*) from from_awl;" from the master and each of
the slaves, the count differs by a hundred on one system, and multiple
hundreds on another.

If the Relay_Log_Pos on the slave matches that of the master, doesn't
it mean they're in sync?

I'd sure appreciate any ideas you may have.
Thanks,
Alex

[Lionel Bouton]

Post by Alex
Hi,
I've been using sqlgrey for a while with mariadb and noticed there is
Last_SQL_Error: Error 'Duplicate entry
'69.25.195-mk1.netatlantic.com-bounce-#' for key 'PRIMARY'' on query.
Default database: 'sqlgrey'. Query: 'INSERT INTO from_awl
(sender_name, sender_domain, src, first_seen, last_seen)
VALUES('bounce-#','mk1.netatlantic.com','69.25.195','2016-03-07
11:51:51',NOW())'

It might happen in rare cases where the same source sends an email
through two independent mail servers at the very same time that confirm
an earlier attempt. They will both try to insert the same auto whitelist
entry.

Another possibility is that the dbcluster support uses slaves for all
its reads and they are not synchronized with the master properly, which
given the following seems the most likely explanation.

Post by Alex
I have the system set up with a master on one system and replicated to
three others. This occurred on one of the slaves.
This isn't the first time it's happened. Is this to be expected?

Not if the slaves forbid any modifications not going through the
replication process (I think that's not always true with MySQL/MariaDB)
and are kept in sync.

Post by Alex
Is it possible it's a problem with my setup?
If I run "select count(*) from from_awl;" from the master and each of
the slaves, the count differs by a hundred on one system, and multiple
hundreds on another.

Then you have a replication setup problem.

Post by Alex
If the Relay_Log_Pos on the slave matches that of the master, doesn't
it mean they're in sync?

That's more a question for MariaDB.

Note that if you don't need slaves to support your current load and
greylisting is not critical you are better off without them. SQLgrey can
withstand a database failure : it will attempt to reconnect but in the
meantime it will stop filtering incoming mail to avoid any service
disruption.

Best regards,

Lionel

[Alex]

Hi,

Post by Lionel Bouton

Post by Alex
Last_SQL_Error: Error 'Duplicate entry
'69.25.195-mk1.netatlantic.com-bounce-#' for key 'PRIMARY'' on query.
Default database: 'sqlgrey'. Query: 'INSERT INTO from_awl
(sender_name, sender_domain, src, first_seen, last_seen)
VALUES('bounce-#','mk1.netatlantic.com','69.25.195','2016-03-07
11:51:51',NOW())'

It might happen in rare cases where the same source sends an email
through two independent mail servers at the very same time that confirm
an earlier attempt. They will both try to insert the same auto whitelist
entry.
Another possibility is that the dbcluster support uses slaves for all
its reads and they are not synchronized with the master properly, which
given the following seems the most likely explanation.

Yes, I'm pretty sure that's it as well.

Post by Lionel Bouton
Note that if you don't need slaves to support your current load and
greylisting is not critical you are better off without them. SQLgrey can
withstand a database failure : it will attempt to reconnect but in the
meantime it will stop filtering incoming mail to avoid any service
disruption.

I've recreated the slaves making them read-only, and I believe that's
fixed the problem. Perhaps I should have done that at first.

Thanks for all your help.
Alex