Re: [Sqlgrey-users] duplicate entry

[Lionel B. <lio...@bo...>]

Le 07/03/2016 21:06, Alex a écrit :
> 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:

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.

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

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

>
> 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