sqlgrey-users — Users of SQLgrey

Re: [Sqlgrey-users] Add primary keys to tables?

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

Hi,

Le 09/02/2016 14:38, incredibleh0lg a écrit :
> [...]
> Another point made in this discussion was to give a use case: Third party applications using the data in the database. In my case, I’d like to provide a similar functionality as SQLgrey Webinterface provides. So present the user with relevant entries - e.g. in the connect table - and let him decide what to do with it.

I don't think it's a good example because you definitely don't want to
modify the connect table content in the back of SQLgrey : this is the
table it uses to implement the basic greylisting algorithm. So if you
want to modify the connect table you actually want to modify the
greylisting algorithm. It seems to me that the two sane approaches if
that's what you really want would be to :
- implement your changes in SQLgrey itself, not in a separate application,
- replace SQLgrey completely or fork it to implement your own algorithm
if what you want to do is very different or solves a specific problem
which isn't commonly encountered by SQLgrey uers.

The same limitations apply to the config/from_awl/domain_awl tables (awl
means *automatic* white list), if you try to modify them behind
SQLgrey's back you are actually designing a new greylisting program.

The only tables I created for external configuration are the
opt[in|out]_[email|domain] tables.

>  In the current format this means, that the request has to carry the WHOLE entry as parameter to get it moved or deleted.

In fact the connect table is a special case and doesn't even guarantee
that each entry is unique so you can't event select a single entry
deterministically.
The tables that are supposed to be managed externally only have a single
column : so the "WHOLE" entry is actually a single value which is
obviously known by any application which would be designed to insert or
delete entries (update doesn't make sense for these tables)... See below
if "obviously" is not a given for you.

>  This is pretty wasteful and it is the reason why I asked if somebody tested a slightly adjusted schema where generated primary keys are added by the database.In theory, this shouldn’t break the application.

It wouldn't break SQLgrey but it would make developing the application
managing SQLgrey's configuration tables more complex and bug-prone (see
a detailed explanation below).

I think you are probably approaching the SQLgrey database and an
external application's database as a single entity (see why I think so
below too), which I don't think is a good way to approach the external
configuration of SQLgrey.

In a single database for any moderately complex application surrogate
keys are nearly always the best choice for various reasons. For a
database dedicated to a trivial service like SQLgrey this is clearly not
the case.

Let's use a practical example to demonstrate.
Take the optout_email table (the other configuration tables are similar:
all have a single column) and create an application managing its content
(creating entries when users want to optout and deleting them when they
want to activate greylisting again).
If you use surrogate keys in the optout_email table the first question
you have to ask yourself is : does my application separate database's
user objects store :
- user emails,
- references to the surrogate keys in optout_email,
- both
and what is used among them to reference the optout_email entries.

You made clear that don't want to use the user emails to reference
optout_email entries so the only 2 choices for you remaining are the
last ones.
But actually if you store the references to surrogate keys you will most
probably want to keep a copy of the email address too. Not only because
it would be convenient in your application which would probably access
the Postfix user database (or a database used to create the Postfix one
which obviously must have the email addresses itself) but also because
optout_email won't have the email if the user didn't choose to opt out.
If you want to avoid storing the address at all costs you would make the
application and Postfix depend on a SQLgrey change to store all your
users' email addresses to solve this. This is not SQLgrey's role: it's a
greylisting service, not an email user database. If you want to create
your user database you obviously won't try to do so by installing a
greylisting service.

In conclusion there's no real choice once you have chosen to use
surrogate keys : you will have to store both the email and the reference
to the surrogate key in the external database in one way or another.

So now if you really want to use the surrogate keys instead of the email
itself you have to maintain consistency between your reference and the
email addresses stored in two separate databases with 2 different
codebases managed by 2 different sets of people. This is clearly (I hope
so anyway) not something you want to do *at all*. By avoiding surrogate
keys SQLgrey actually forces a potential separate database to be
inherently consistent with its own database.

In case this isn't crystal clear, as an example of the consistency
problems consider these common scenarios :
- admins create entries manually until enough users ask for direct
access and install the configuration application,
- admins use batches to create initial entries or maintain a subset of
the optout_email table in parallel to the configuration application,
these batches probably aren't aware of the existence of the
configuration application and don't know how to update its database,
- after a crash, on restoring the 2 separate databases (SQLgrey and
configuration application) you can get a more recent SQLgrey database
with entries not (yet) referenced by the other restored database version,
- ...
All these scenario imply that there will be entries in optout_email not
yet referenced by the configuration application.
When creating new users with addresses already in optout_email, how will
you create the references to these entries ? The only way is to check
that for each user you create in your application there isn't already an
entry in the optout_email table with the same email address. So for new
users your reference to the optout_email table is the email address and
for existing users the reference is the surrogate key : it's bad design
and a loud and clear invitation to bugs.

Now let's see how you would design the application without surrogate keys.
The application has its user objects with their own email attribute.
- when you want to activate optout, you use your application's email
attribute to find or create an entry in the optout_email table if needed,
- when you want to deactivate it, you find and delete the entry with the
same email,
- when you change the email address of a user, you remove the old entry
(if it exists) and create a new one if applicable.
This is *trivial* logic. An environment where this is difficult to do
would have to prevent you to make the most basic SQL queries imaginable.

Why should we even consider a change only useful to people starting to
develop external tools by shooting themselves in the foot when choosing
their development environment ?

Best regards,

Lionel

Re: [Sqlgrey-users] Add primary keys to tables?

[incredibleh0lg <lis...@li...>]

Hi.


> Le 09/02/2016 00:14, Lionel Bouton a écrit :
>> Hi,
>> 
>> I didn't answer the original message at first because :
>> - I believed I reacted on a similar subject several years ago,
>> - the message title doesn't actually make sense to me.
>> 
>> I suspect some people are coming here with a basic ORM background and
>> don't fully understand the difference between primary keys and object
>> ids (PKs/OIDs in the following). So here is a complete answer..
> 
> If people want to dig more OIDs are in fact surrogate keys (by
> opposition to natural keys which are used in SQLgrey). There's a ton of
> documentation/discussion on the subject.

I understand that this can bring up discussions. But I would disagree with the term OIDs (and thanks for the kind words about my “basic" ORM background...). Basically one can choose between Natural Keys and Generated / Artificial Keys. In my experience natural keys are always not the best choice. In every real world commercial project you will end up in changing your keys, because the natural candidates that are so unchangeable, do change. So generic keys are - at least for me - always the safest way to go.
One can still use generic primary keys and define constraints that the combination of local part and domain have to be unique.

Regarding the speed part: It is a completely valid point to decide to not use real primary keys to improve the performance. But in a normal database design you can achieve this by having views or pre-aggregated tables - or use one of the NoSQL databases.

Another point made in this discussion was to give a use case: Third party applications using the data in the database. In my case, I’d like to provide a similar functionality as SQLgrey Webinterface provides. So present the user with relevant entries - e.g. in the connect table - and let him decide what to do with it. In the current format this means, that the request has to carry the WHOLE entry as parameter to get it moved or deleted. This is pretty wasteful and it is the reason why I asked if somebody tested a slightly adjusted schema where generated primary keys are added by the database.In theory, this shouldn’t break the application.

Last but not least, the argument with “wasted space” is insignificant today. The only concern I have about this is that this can be an issue for servers with huge traffic, as the keys can get pretty big quite quickly. So having this as an optional feature would be the best.

As there is no real further development, it is maybe a good exercise to reimplement it with support for NoSQL databases (which, btw, use generic keys too). 
But thanks for the feedback anyway - sqlgrey is a really reliable tool which is doing great work for years now. So please do not see this is critic, it was more a feature request / question about experiences :)

Cheers,
Holger

Re: [Sqlgrey-users] Add primary keys to tables?

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

Le 09/02/2016 00:14, Lionel Bouton a écrit :
> Hi,
>
> I didn't answer the original message at first because :
> - I believed I reacted on a similar subject several years ago,
> - the message title doesn't actually make sense to me.
>
> I suspect some people are coming here with a basic ORM background and
> don't fully understand the difference between primary keys and object
> ids (PKs/OIDs in the following). So here is a complete answer..

If people want to dig more OIDs are in fact surrogate keys (by
opposition to natural keys which are used in SQLgrey). There's a ton of
documentation/discussion on the subject.

Best regards,

Lionel

Re: [Sqlgrey-users] Add primary keys to tables?

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

Hi,

I didn't answer the original message at first because :
- I believed I reacted on a similar subject several years ago,
- the message title doesn't actually make sense to me.

I suspect some people are coming here with a basic ORM background and
don't fully understand the difference between primary keys and object
ids (PKs/OIDs in the following). So here is a complete answer..

Le 08/02/2016 23:09, Karl O. Pinc a écrit :
> On Mon, 08 Feb 2016 21:47:17 +0100
> Jean-Michel Pouré - GOOZE <jm...@go...> wrote:
>
>> Le mardi 26 janvier 2016 à 17:31 +0000, Holger Steinhauer a écrit :
>>> I am working on a small private project which also involves sqlgrey
>>> integration. Lately, I was playing around with the data and just
>>> came to the point I was before: The tables do not have real primary
>>> keys.

Sure they have at least for tables where it makes sense. The notable
exception is "connect" because enforcing unicity with a PK would both
slow down access to this very time-sensitive table and force SQLgrey to
handle collisions which it really doesn't need to.
I suspect that what you call "real primary keys" are in fact OIDs (not
in the "internal database value" sense, but in the "ORM object id"
sense). From the top of my head, OIDs are only useful in the following
cases :
- identifying objects that don't have any obvious primary key (for
example an user on a web application could want to change his login and
not break all relations with the data linked to him/her),
- simplifying ORMs code and especially how they manage foreign keys
(some ORMs can handle composite PKs though),
- making indexes on foreign keys accesses faster.
We don't use any ORM or foreign keys in SQLgrey and all the objects we
manage are uniquely defined with invariable properties so we are free to
avoid OIDs as they would serve no purpose at all and just slow us down.

>>>  Did anyone try to run sqlgrey with an altered database
>>> schema, where the ID is generic and generated for each entry?

I'm not sure why you would do such a thing.

>>>  
>>> Also, is sqlgrey still in development? Just asking if it is “worth”
>>> raising an Feature Request :)

There hasn't been any development for a while but I'm not aware of any
bug surfacing for a long time either. AFAIK it just works.
I'm not likely to put much effort in it. 99% of my developments are in
Ruby now and while Perl isn't bad it's not as comfortable for me. So the
latest changes have been implemented by other developers.
I thought about rewriting it in Ruby some years ago. This would have
been an opportunity to make the code more readable (I think I could do a
better job on this front) but as I said, it just works (I still use it
myself on several personal and corporate servers).

> You'd have to put forward a use-case. Otherwise it's wasted storage.

Not only would it be wasted storage but it would slow down the database
too (dead weight slows you down, for example not being able to keep the
whole database in RAM because of the additional weight would have a
significant impact on large sites).
Jean-Michel Pouré seemed to imply that it could have performance
benefits bat I can't see how. There's not a single access to the
database that could even use an OID, every object must be accessed based
on conditions on its attributes (parts of email addresses, which are the
only information relevant to SQLgrey's database a mail server can
transmit to it).

Best regards,

Lionel

Re: [Sqlgrey-users] Add primary keys to tables?

[Karl O. P. <ko...@me...>]

On Mon, 08 Feb 2016 21:47:17 +0100
Jean-Michel Pouré - GOOZE <jm...@go...> wrote:

> Le mardi 26 janvier 2016 à 17:31 +0000, Holger Steinhauer a écrit :
> > I am working on a small private project which also involves sqlgrey
> > integration. Lately, I was playing around with the data and just
> > came to the point I was before: The tables do not have real primary
> > keys. Did anyone try to run sqlgrey with an altered database
> > schema, where the ID is generic and generated for each entry? 
> > Also, is sqlgrey still in development? Just asking if it is “worth”
> > raising an Feature Request :)

You'd have to put forward a use-case.  Otherwise it's wasted
storage.



Karl <ko...@me...>
Free Software:  "You don't pay back, you pay forward."
                 -- Robert A. Heinlein

Re: [Sqlgrey-users] Add primary keys to tables?

[Jean-Michel P. - G. <jm...@go...>]

Le mardi 26 janvier 2016 à 17:31 +0000, Holger Steinhauer a écrit :
> I am working on a small private project which also involves sqlgrey
> integration. Lately, I was playing around with the data and just came
> to the point I was before: The tables do not have real primary keys.
> Did anyone try to run sqlgrey with an altered database schema, where
> the ID is generic and generated for each entry? 
> Also, is sqlgrey still in development? Just asking if it is “worth”
> raising an Feature Request :)
> 
> Any help / hints are highly appreciated.

I posted the same message on Tue, 11 Aug 2015 22:26:05 +0200
Just my 2 cents, I guess this is to allow huge loads, maybe hundred thousands of queries every second.

Kind regards,
Jean-Michel

[Sqlgrey-users] Add primary keys to tables?

[Holger S. <lis...@li...>]

Hi all.

I am working on a small private project which also involves sqlgrey integration. Lately, I was playing around with the data and just came to the point I was before: The tables do not have real primary keys. Did anyone try to run sqlgrey with an altered database schema, where the ID is generic and generated for each entry? 
Also, is sqlgrey still in development? Just asking if it is “worth” raising an Feature Request :)

Any help / hints are highly appreciated.

Cheers,
Holger

[Sqlgrey-users] clients_ip_whitelist.local and ipv6

[Grooz, M. (r. iT) <Mar...@re...>]

Hi,

I try to add a ipv6 Subnet to the clients_ip_whitelist.local file. Sqlgrey ignores it. What notation is required?

Regards Marc

Re: [Sqlgrey-users] Problems SQLgrey and gmail

[Olivier C. <o.c...@gm...>]

Thanks :)

Le vendredi 11 décembre 2015, Christian Kivalo <ml+...@va...> a
écrit :

>
>
> On 2015-12-11 06:39, Karl O. Pinc wrote:
> > On Fri, 11 Dec 2015 06:19:32 +0100
> > Olivier CALVANO <o.c...@gm... <javascript:;>> wrote:
> >
> >>
> >> SQLgrey don't see that Gmail don't use the same IP SRC in reconnect.
> >>
> >> they have a solution ?
> >
> > Google's IPs should be in your whitelist.  I can't say why they
> > are not, other than it might have to do with them being IPv6.
> >
> > It might be an interesting exercise to generate the the whitelist
> > from the SPF records....
>
> These was a script posted to the postfix mailinglist to generate a
> postscreen whitelist from spf records
>
> https://github.com/stevejenkins/postwhite
>
>
> > Regards,
> >
> > Karl <ko...@me... <javascript:;>>
> > Free Software:  "You don't pay back, you pay forward."
> >                  -- Robert A. Heinlein
>
> --
>   Christian Kivalo
>
>
>
> ------------------------------------------------------------------------------
> _______________________________________________
> Sqlgrey-users mailing list
> Sql...@li... <javascript:;>
> https://lists.sourceforge.net/lists/listinfo/sqlgrey-users
>

Re: [Sqlgrey-users] Problems SQLgrey and gmail

[Christian K. <ml+...@va...>]

On 2015-12-11 06:39, Karl O. Pinc wrote:
> On Fri, 11 Dec 2015 06:19:32 +0100
> Olivier CALVANO <o.c...@gm...> wrote:
> 
>> 
>> SQLgrey don't see that Gmail don't use the same IP SRC in reconnect.
>> 
>> they have a solution ?
> 
> Google's IPs should be in your whitelist.  I can't say why they
> are not, other than it might have to do with them being IPv6.
> 
> It might be an interesting exercise to generate the the whitelist
> from the SPF records....

These was a script posted to the postfix mailinglist to generate a 
postscreen whitelist from spf records

https://github.com/stevejenkins/postwhite


> Regards,
> 
> Karl <ko...@me...>
> Free Software:  "You don't pay back, you pay forward."
>                  -- Robert A. Heinlein

--
  Christian Kivalo

Re: [Sqlgrey-users] Problems SQLgrey and gmail

[Karl O. P. <ko...@me...>]

On Fri, 11 Dec 2015 06:19:32 +0100
Olivier CALVANO <o.c...@gm...> wrote:

> 
> SQLgrey don't see that Gmail don't use the same IP SRC in reconnect.
> 
> they have a solution ?

Google's IPs should be in your whitelist.  I can't say why they
are not, other than it might have to do with them being IPv6.

It might be an interesting exercise to generate the the whitelist
from the SPF records....

Regards,

Karl <ko...@me...>
Free Software:  "You don't pay back, you pay forward."
                 -- Robert A. Heinlein

[Sqlgrey-users] Problems SQLgrey and gmail

[Olivier C. <o.c...@gm...>]

Hi

we have a small problems with SQLGrey/gmail :



Dec 11 05:49:06 mx-srv1 sqlgrey: grey: new:
2607:f8b0:4001:c05::236(2607:f8b0:4001:c05::236), o.c...@gm... ->
ol...@ca...

Dec 11 05:49:06 mx-srv1 postfix/smtpd[16795]: NOQUEUE: reject: RCPT from
mail-ig0-x236.google.com[2607:f8b0:4001:c05::236]: 450 4.7.1 <
ol...@ca...>: Recipient address rejected: Greylisted for 5 minutes;
from=<o.c...@gm...> to=<ol...@ca...> proto=ESMTP helo=<
mail-ig0-x236.google.com>

Dec 11 05:54:45 mx-srv1 sqlgrey: grey: new:
2607:f8b0:4001:c05::232(2607:f8b0:4001:c05::232), o.c...@gm... ->
ol...@ca...

Dec 11 05:54:45 mx-srv1 postfix/smtpd[16978]: NOQUEUE: reject: RCPT from
mail-ig0-x232.google.com[2607:f8b0:4001:c05::232]: 450 4.7.1 <
ol...@ca...>: Recipient address rejected: Greylisted for 5 minutes;
from=<o.c...@gm...> to=<ol...@ca...> proto=ESMTP helo=<
mail-ig0-x232.google.com>

Dec 11 06:02:25 mx-srv1 sqlgrey: grey: new:
2607:f8b0:4001:c05::241(2607:f8b0:4001:c05::241), o.c...@gm... ->
ol...@ca...

Dec 11 06:02:25 mx-srv1 postfix/smtpd[17206]: NOQUEUE: reject: RCPT from
mail-ig0-x241.google.com[2607:f8b0:4001:c05::241]: 450 4.7.1 <
ol...@ca...>: Recipient address rejected: Greylisted for 5 minutes;
from=<o.c...@gm...> to=<ol...@ca...> proto=ESMTP helo=<
mail-ig0-x241.google.com>


SQLgrey don't see that Gmail don't use the same IP SRC in reconnect.

they have a solution ?

regards

Olivier

[Sqlgrey-users] Configuration check and db_dsn

[Alex <mys...@gm...>]

Hi guys,

I've just rebuilt my sqlgrey setup on three fedora systems, and hoped
someone could help me verify I've done it correctly. I've set it up to
use mariadb replication with one master server (also a slave) and two
separate slaves. The database is successfully being syncd with the two
slave systems.

I'd like to have it configured such that each slave writes only to the master.

I tried specifying db_host to be the name of the master server, but it
never seemed to connect to it. It was only after configuring db_dsn
explicitly did it appear to connect properly, according to the mariadb
general query log.

Is there any way to have sqlgrey explicitly record the name of the
host to which it's writing for every update?

Below is the config from one of the slaves. Can I ask you to check to
make sure it's configured the way it should be, given my description
above?

loglevel = 3
log_override = whitelist:1,grey:3,spam:2
reconnect_delay = 5
awl_age = 31
group_domain_level = 10
db_type = mysql
db_name = sqlgrey
db_host = ns1.example.com;mysql_connect_timeout=1
db_port = default
db_user = sqlgrey
db_pass = mypass
db_cleanup_hostname=ns1.example.com
db_dsn = DBI:mysql:database=sqlgrey;host=ns1.example.com;port=3306
db_cleandelay = 1800
clean_method = sync
db_cluster = off
read_hosts=ns1.example.com
prepend = 1

Thanks,
Alex

Re: [Sqlgrey-users] Creating initial database tables

[Alex <mys...@gm...>]

Hi,

> Look in your database logs to see why the tables didn't
> create.  If there's nothing, then reconfigure the db to log
> errors and try again.

So I migrated my sqlgrey.conf from the working system, and it didn't
have the db_dsn line uncommented:

# db_dsn = DBI:mysql:database=sqlgrey;host=localhost;port=3306

That value is null by default, so perhaps there should be a separate
check for that?

Apparently my logging wasn't set up the way I expected, because it
wasn't logging these failures to connect to the database.

Thanks,
Alex

Re: [Sqlgrey-users] Creating initial database tables

[Karl O. P. <ko...@me...>]

On Sat, 26 Sep 2015 12:22:38 -0400
Alex <mys...@gm...> wrote:

> Hi, I posted the message below a few days ago, and haven't received
> any replies, and I'm really stuck.
> 
> I followed the directions in the HOWTO for configuring sqlgrey to be
> used with mysql/mariadb, and just don't understand why it's not
> creating the tables.
> 
> When I connect this new database to the master, slave status says
> this:
> 
> Last_SQL_Error: Error 'Table 'sqlgrey.domain_awl' doesn't exist' on
> query. Default database: 'sqlgrey'. Query: 'UPDATE domain_awl SET
> last_seen = NOW(), first_seen = first_seen WHERE sender_domain =
> 'bounce.twitter.com' AND src = '199.59.150''
> 
> I'd sure appreciate any ideas.

Look in your database logs to see why the tables didn't
create.  If there's nothing, then reconfigure the db to log
errors and try again.



Karl <ko...@me...>
Free Software:  "You don't pay back, you pay forward."
                 -- Robert A. Heinlein

Re: [Sqlgrey-users] Creating initial database tables

[Alex <mys...@gm...>]

Hi, I posted the message below a few days ago, and haven't received
any replies, and I'm really stuck.

I followed the directions in the HOWTO for configuring sqlgrey to be
used with mysql/mariadb, and just don't understand why it's not
creating the tables.

When I connect this new database to the master, slave status says this:

Last_SQL_Error: Error 'Table 'sqlgrey.domain_awl' doesn't exist' on
query. Default database: 'sqlgrey'. Query: 'UPDATE domain_awl SET
last_seen = NOW(), first_seen = first_seen WHERE sender_domain =
'bounce.twitter.com' AND src = '199.59.150''

I'd sure appreciate any ideas.
Thanks,
Alex


On Sun, Sep 20, 2015 at 8:01 PM, Alex <mys...@gm...> wrote:
> Hi guys,
>
> I'm trying to build a new system to replace the one I reported some
> time ago as having connect errors, and having some trouble building
> the initial tables using mariadb.
>
> I can login to the database as the sqlgrey user and create the tables
> manually. However, when starting sqlgrey, the tables aren't
> automatically created.
>
> When trying to execute the SELECT that is run from within
> table_exists, I get the following:
>
> MariaDB [(none)]> use sqlgrey;
> Database changed
> MariaDB [sqlgrey]> SELECT 1 from connect LIMIT 0;
> ERROR 1146 (42S02): Table 'sqlgrey.connect' doesn't exist
>
> There is something in the table_exists() function that says SQL error
> reporting needs to be turned off at connect time. Is that an sqlgrey
> setting or a mariadb setting?
>
> Any ideas greatly appreciated.
> Thanks,
> Alex

[Sqlgrey-users] Creating initial database tables

[Alex <mys...@gm...>]

Hi guys,

I'm trying to build a new system to replace the one I reported some
time ago as having connect errors, and having some trouble building
the initial tables using mariadb.

I can login to the database as the sqlgrey user and create the tables
manually. However, when starting sqlgrey, the tables aren't
automatically created.

When trying to execute the SELECT that is run from within
table_exists, I get the following:

MariaDB [(none)]> use sqlgrey;
Database changed
MariaDB [sqlgrey]> SELECT 1 from connect LIMIT 0;
ERROR 1146 (42S02): Table 'sqlgrey.connect' doesn't exist

There is something in the table_exists() function that says SQL error
reporting needs to be turned off at connect time. Is that an sqlgrey
setting or a mariadb setting?

Any ideas greatly appreciated.
Thanks,
Alex

Re: [Sqlgrey-users] Frequent disconnect/reconnect issues

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

Le 20/08/2015 03:09, Alex a écrit :
> Hi,
>
>>>> I don't have answers to all your questions but could you explain why you
>>>> are using db_cluster and how/why you set the replication to 5 MySQL
>>>> databases? This seems overkill and needlessly complex.
>>> It's actually three databases - two of them have multiple CNAMEs that
>>> appear to be chosen on occasion.
>> You shouldn't use 5 entries in read_hosts but only 2 or 3 depending on
>> what kind of load distribution between your databases you want. But it's
>> probably not relevant in your case.
> It's not five. It's really only two, unless you count itself. The
> other two are localhost and a CNAME entry.

Then you shouldn't have 5 entries in read_hosts as I already said.
Seriously if you don't understand why 5 entries for 3 servers can cause
problems you shouldn't even think about touching db_cluster: you are
clearly using it wrong.

>
>>> I'm using db_cluster because I have three mail servers for the same
>>> domain with the same weight,
>> Do you have system load problems which mandates you having 3 mail
>> servers? It's usually not a good idea to have several active mail
>> servers unless you really need them.
> Yes, for load and redundancy reasons. The three servers receive about
> 400k messages per day, at peak.

This is only <5 messages per second. Hardly something a single server
can't handle (unless you use costly antispam measures that you can't
delegate to backend servers).

This kind of redundancy is mostly superfluous with SMTP: if you have a
spare server ready to replace your mail server (ie: affect it the public
IP of your single MX to it or redirect it if you have some kind of
reverse proxy), you only have to start it to get the messages queued at
the origin delivered to it.

>> It's a general recommendation but
>> in the case of SQLgrey some spammers try to send mails to all servers at
>> the same time and it can confuse SQLgrey which tries to insert 3 times
>> the same entry because each instance can't known that the 2 others are
>> trying too. In this case SQLgrey will handle this as a database error
>> (because it can't guess if it's not one) and try to reconnect. With a
>> single server this is not possible.
> Okay, that's interesting. And you believe it's possible that's what
> happening here?

It's a known problem so yes it's possible.

>
>>>  resulting in a reconnect potentially
>>> reaching a different mail server, otherwise resulting in another 5
>>> minute greylisting delay.
>>>
>>> I thought that was the correct way to address this situation?
>> No: db_cluster is only meant to solve rare performance issues, has some
>> drawbacks (replication delay of most replication solutions means slaves
>> are out of sync with the master which can confuse SQLgrey in some cases)
>> and requires a bit of DBA work to setup properly. Unless your master
>> database is so loaded that SQLgrey starts to timeout you shouldn't use
>> dbcluster.
> Except if the master becomes inaccessible, no?

Even with db_cluster on if the master becomes inaccessible SQLgrey stops
greylisting and let everything pass. If it can't write to the database
there's no way it can do its job.

>
>>>> Can you reproduce your problem with a single database and db_cluster off?
>>> For the reason described above, I don't think it would be a good idea
>>> to disconnect them from each other.
>> I don't understand what you mean by "disconnect them from each other".
>> Disconnecting databases from each other? Disconnecting mail servers from
>> database servers?
>>
>> Your database should already be kept in sync by MySQL replication
>> (although I learned not to trust it myself) so disabling db_cluster
>> shouldn't have any impact on the database, you will simply write and
>> read from your single master (ns1.example.com) instead of writing to it
>> and reading from read_hosts. The slaves won't be used but you can keep
>> them if you really need to re-enable db_cluster later.
> I think I've been confusing MySQL replication and dbcluster as being
> something that must be used together.

I don't know why you think this after our exchanges. They don't have to
work *together* but if the replication doesn't work properly SQLgrey
with db_cluster will surely fail to work properly.

>> Disconnecting mail servers from the database servers (other than
>> ns1.example.com) should have zero impact: they are supposed to have the
>> same data anyway (in fact that's not true there's a replication delay
>> which can cause problems so disabling db_cluster can actually solve
>> problems there).
> Okay, great. I'll disable dbcluster and follow up. I was following the README:
>
>     If [mail1] gets a new request, it'll greylist, respond "450" and
> stick client into the "connect" table.
>     Now heres the problem. Client backs off, and comes back later to
> try again, but there is no
>     garantee it'll get [mail1] again. Infact, if it doesnt, the
> greylisting will happen all over. The solution
>     is to use DBClustering. Each mailserver STILL has its own
> SQL-server, but we add a master-sql
>     and let the local sql-servers be replication slaves:
>
> I don't have the load balancer, as shown in the drawing in the README.
> I guess I don't fully understand the purpose of db_cluster.

db_cluster support wasn't written by me and I never used it so I just
read its README again to check.
I think it doesn't really explain when you should use it properly, just
ignore any advice in it not linked to database load problems.

Best regards,

Lionel

Re: [Sqlgrey-users] Frequent disconnect/reconnect issues

[Urban, F. <Fra...@co...>]

Hi Alex,

here my configuring working since 10 years without any problem. I just got new servers and transferred the config and database from Mysql to Mariadb also without any problem.
In the last 365 days Sqlgerey blocked 150.000.000 emails here. It was more in the past up to 1.000.000.000 per year.
We still have only 20% SPAM after Greylisting.

We are using 7 servers. mail1 to mail7
The Mariadb is installed as master on mail6 and uses mail7 as a replication server.
mail6 is used as AWL and Bayes database for SpamAssassin 
mail6 is used as master for Sqlgrey and mail7 as reading host

That's the config used on all servers

/etc/sqlgrey/sqlgrey.conf 

db_host = mail6
db_cluster = on
read_hosts = mail7,mail6
db_cleanup_hostname = mail6


Greetings

Frank

-----Ursprüngliche Nachricht-----
Von: Alex [mailto:mys...@gm...] 
Gesendet: Donnerstag, 20. August 2015 03:10
An: SQLgrey users mailing-list
Betreff: Re: [Sqlgrey-users] Frequent disconnect/reconnect issues

Hi,

>>> I don't have answers to all your questions but could you explain why 
>>> you are using db_cluster and how/why you set the replication to 5 
>>> MySQL databases? This seems overkill and needlessly complex.
>> It's actually three databases - two of them have multiple CNAMEs that 
>> appear to be chosen on occasion.
>
> You shouldn't use 5 entries in read_hosts but only 2 or 3 depending on 
> what kind of load distribution between your databases you want. But 
> it's probably not relevant in your case.

It's not five. It's really only two, unless you count itself. The other two are localhost and a CNAME entry.

>> I'm using db_cluster because I have three mail servers for the same 
>> domain with the same weight,
>
> Do you have system load problems which mandates you having 3 mail 
> servers? It's usually not a good idea to have several active mail 
> servers unless you really need them.

Yes, for load and redundancy reasons. The three servers receive about 400k messages per day, at peak.

> It's a general recommendation but
> in the case of SQLgrey some spammers try to send mails to all servers 
> at the same time and it can confuse SQLgrey which tries to insert 3 
> times the same entry because each instance can't known that the 2 
> others are trying too. In this case SQLgrey will handle this as a 
> database error (because it can't guess if it's not one) and try to 
> reconnect. With a single server this is not possible.

Okay, that's interesting. And you believe it's possible that's what happening here?

>>  resulting in a reconnect potentially reaching a different mail 
>> server, otherwise resulting in another 5 minute greylisting delay.
>>
>> I thought that was the correct way to address this situation?
>
> No: db_cluster is only meant to solve rare performance issues, has 
> some drawbacks (replication delay of most replication solutions means 
> slaves are out of sync with the master which can confuse SQLgrey in 
> some cases) and requires a bit of DBA work to setup properly. Unless 
> your master database is so loaded that SQLgrey starts to timeout you 
> shouldn't use dbcluster.

Except if the master becomes inaccessible, no?

>>> Can you reproduce your problem with a single database and db_cluster off?
>> For the reason described above, I don't think it would be a good idea 
>> to disconnect them from each other.
>
> I don't understand what you mean by "disconnect them from each other".
> Disconnecting databases from each other? Disconnecting mail servers 
> from database servers?
>
> Your database should already be kept in sync by MySQL replication 
> (although I learned not to trust it myself) so disabling db_cluster 
> shouldn't have any impact on the database, you will simply write and 
> read from your single master (ns1.example.com) instead of writing to 
> it and reading from read_hosts. The slaves won't be used but you can 
> keep them if you really need to re-enable db_cluster later.

I think I've been confusing MySQL replication and dbcluster as being something that must be used together.

> Disconnecting mail servers from the database servers (other than
> ns1.example.com) should have zero impact: they are supposed to have 
> the same data anyway (in fact that's not true there's a replication 
> delay which can cause problems so disabling db_cluster can actually 
> solve problems there).

Okay, great. I'll disable dbcluster and follow up. I was following the README:

    If [mail1] gets a new request, it'll greylist, respond "450" and stick client into the "connect" table.
    Now heres the problem. Client backs off, and comes back later to try again, but there is no
    garantee it'll get [mail1] again. Infact, if it doesnt, the greylisting will happen all over. The solution
    is to use DBClustering. Each mailserver STILL has its own SQL-server, but we add a master-sql
    and let the local sql-servers be replication slaves:

I don't have the load balancer, as shown in the drawing in the README.
I guess I don't fully understand the purpose of db_cluster.

Thanks so much,
Alex

------------------------------------------------------------------------------
_______________________________________________
Sqlgrey-users mailing list
Sql...@li...
https://lists.sourceforge.net/lists/listinfo/sqlgrey-users

Re: [Sqlgrey-users] Frequent disconnect/reconnect issues

[Alex <mys...@gm...>]

Hi,

>>> I don't have answers to all your questions but could you explain why you
>>> are using db_cluster and how/why you set the replication to 5 MySQL
>>> databases? This seems overkill and needlessly complex.
>> It's actually three databases - two of them have multiple CNAMEs that
>> appear to be chosen on occasion.
>
> You shouldn't use 5 entries in read_hosts but only 2 or 3 depending on
> what kind of load distribution between your databases you want. But it's
> probably not relevant in your case.

It's not five. It's really only two, unless you count itself. The
other two are localhost and a CNAME entry.

>> I'm using db_cluster because I have three mail servers for the same
>> domain with the same weight,
>
> Do you have system load problems which mandates you having 3 mail
> servers? It's usually not a good idea to have several active mail
> servers unless you really need them.

Yes, for load and redundancy reasons. The three servers receive about
400k messages per day, at peak.

> It's a general recommendation but
> in the case of SQLgrey some spammers try to send mails to all servers at
> the same time and it can confuse SQLgrey which tries to insert 3 times
> the same entry because each instance can't known that the 2 others are
> trying too. In this case SQLgrey will handle this as a database error
> (because it can't guess if it's not one) and try to reconnect. With a
> single server this is not possible.

Okay, that's interesting. And you believe it's possible that's what
happening here?

>>  resulting in a reconnect potentially
>> reaching a different mail server, otherwise resulting in another 5
>> minute greylisting delay.
>>
>> I thought that was the correct way to address this situation?
>
> No: db_cluster is only meant to solve rare performance issues, has some
> drawbacks (replication delay of most replication solutions means slaves
> are out of sync with the master which can confuse SQLgrey in some cases)
> and requires a bit of DBA work to setup properly. Unless your master
> database is so loaded that SQLgrey starts to timeout you shouldn't use
> dbcluster.

Except if the master becomes inaccessible, no?

>>> Can you reproduce your problem with a single database and db_cluster off?
>> For the reason described above, I don't think it would be a good idea
>> to disconnect them from each other.
>
> I don't understand what you mean by "disconnect them from each other".
> Disconnecting databases from each other? Disconnecting mail servers from
> database servers?
>
> Your database should already be kept in sync by MySQL replication
> (although I learned not to trust it myself) so disabling db_cluster
> shouldn't have any impact on the database, you will simply write and
> read from your single master (ns1.example.com) instead of writing to it
> and reading from read_hosts. The slaves won't be used but you can keep
> them if you really need to re-enable db_cluster later.

I think I've been confusing MySQL replication and dbcluster as being
something that must be used together.

> Disconnecting mail servers from the database servers (other than
> ns1.example.com) should have zero impact: they are supposed to have the
> same data anyway (in fact that's not true there's a replication delay
> which can cause problems so disabling db_cluster can actually solve
> problems there).

Okay, great. I'll disable dbcluster and follow up. I was following the README:

    If [mail1] gets a new request, it'll greylist, respond "450" and
stick client into the "connect" table.
    Now heres the problem. Client backs off, and comes back later to
try again, but there is no
    garantee it'll get [mail1] again. Infact, if it doesnt, the
greylisting will happen all over. The solution
    is to use DBClustering. Each mailserver STILL has its own
SQL-server, but we add a master-sql
    and let the local sql-servers be replication slaves:

I don't have the load balancer, as shown in the drawing in the README.
I guess I don't fully understand the purpose of db_cluster.

Thanks so much,
Alex

Re: [Sqlgrey-users] Frequent disconnect/reconnect issues

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

Le 19/08/2015 20:12, Alex a écrit :
> Hi,
>
>> I don't have answers to all your questions but could you explain why you
>> are using db_cluster and how/why you set the replication to 5 MySQL
>> databases? This seems overkill and needlessly complex.
> It's actually three databases - two of them have multiple CNAMEs that
> appear to be chosen on occasion.

You shouldn't use 5 entries in read_hosts but only 2 or 3 depending on
what kind of load distribution between your databases you want. But it's
probably not relevant in your case.

>
> I'm using db_cluster because I have three mail servers for the same
> domain with the same weight,

Do you have system load problems which mandates you having 3 mail
servers? It's usually not a good idea to have several active mail
servers unless you really need them. It's a general recommendation but
in the case of SQLgrey some spammers try to send mails to all servers at
the same time and it can confuse SQLgrey which tries to insert 3 times
the same entry because each instance can't known that the 2 others are
trying too. In this case SQLgrey will handle this as a database error
(because it can't guess if it's not one) and try to reconnect. With a
single server this is not possible.

>  resulting in a reconnect potentially
> reaching a different mail server, otherwise resulting in another 5
> minute greylisting delay.
>
> I thought that was the correct way to address this situation?

No: db_cluster is only meant to solve rare performance issues, has some
drawbacks (replication delay of most replication solutions means slaves
are out of sync with the master which can confuse SQLgrey in some cases)
and requires a bit of DBA work to setup properly. Unless your master
database is so loaded that SQLgrey starts to timeout you shouldn't use
dbcluster.

>> Can you reproduce your problem with a single database and db_cluster off?
> For the reason described above, I don't think it would be a good idea
> to disconnect them from each other.

I don't understand what you mean by "disconnect them from each other".
Disconnecting databases from each other? Disconnecting mail servers from
database servers?

Your database should already be kept in sync by MySQL replication
(although I learned not to trust it myself) so disabling db_cluster
shouldn't have any impact on the database, you will simply write and
read from your single master (ns1.example.com) instead of writing to it
and reading from read_hosts. The slaves won't be used but you can keep
them if you really need to re-enable db_cluster later.
Disconnecting mail servers from the database servers (other than
ns1.example.com) should have zero impact: they are supposed to have the
same data anyway (in fact that's not true there's a replication delay
which can cause problems so disabling db_cluster can actually solve
problems there).

>
> This configuration seemed to run just fine for months.

Sometimes things work by chance even if they aren't set up properly
until the right set of conditions making them fail happen.

Best regards,

Lionel

Re: [Sqlgrey-users] Frequent disconnect/reconnect issues

[Alex <mys...@gm...>]

Hi,

> I don't have answers to all your questions but could you explain why you
> are using db_cluster and how/why you set the replication to 5 MySQL
> databases? This seems overkill and needlessly complex.

It's actually three databases - two of them have multiple CNAMEs that
appear to be chosen on occasion.

I'm using db_cluster because I have three mail servers for the same
domain with the same weight, resulting in a reconnect potentially
reaching a different mail server, otherwise resulting in another 5
minute greylisting delay.

I thought that was the correct way to address this situation?

> Can you reproduce your problem with a single database and db_cluster off?

For the reason described above, I don't think it would be a good idea
to disconnect them from each other.

This configuration seemed to run just fine for months.

There are entries in the mysql error logs when I deliberately
disconnected the master from the slave, but nothing around the time
the dbaccess error was triggered.

Should I look at recording every INSERT in the logs somehow?

Thanks,
Alex

Re: [Sqlgrey-users] Frequent disconnect/reconnect issues

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

Hi,

I don't have answers to all your questions but could you explain why you
are using db_cluster and how/why you set the replication to 5 MySQL
databases? This seems overkill and needlessly complex.

Can you reproduce your problem with a single database and db_cluster off?

Best regards,

Lionel

Re: [Sqlgrey-users] Frequent disconnect/reconnect issues

[Karl O. P. <ko...@me...>]

On Wed, 19 Aug 2015 13:33:49 -0400
Alex <mys...@gm...> wrote:

> Hi guys,
> 
> I'm still having database problems. Can someone tell me under what
> circumstances the database would expect to see a value that doesn't
> exist?
> 
> Aug 19 00:07:39 mail02 sqlgrey: dbaccess: warning: couldn't do
> query:#012INSERT INTO from_awl (sender_name, sender_domain, src,
> first_seen, last_seen)
> VALUES('shimvebvl','uwerim.ru','91.121.21','2015-08-19
> 00:00:57',NOW()):#012, reconnecting to DB
> 
> And why this would cause a disconnect/reconnect to occur?

I believe you have it backwards.  The message
says it can't insert the value.  Because it can't
insert the value you wouldn't expect to see the
value in the db.  It's probably not inserting the
value because it's connection to the db is broken.
So it tries to reconnect to the db.

Something, somewhere, probably not in sqlgrey,
probably not even in perl -- although all these
probablies are just a guess, is breaking the 
connection to the db.

As I mentioned before, the most straightforward
approach is to instrument your db and get logs
of when connections break or otherwise go away.
Your db must know when this happens.


Karl <ko...@me...>
Free Software:  "You don't pay back, you pay forward."
                 -- Robert A. Heinlein

Re: [Sqlgrey-users] Frequent disconnect/reconnect issues

[Alex <mys...@gm...>]

Hi guys,

I'm still having database problems. Can someone tell me under what
circumstances the database would expect to see a value that doesn't
exist?

Aug 19 00:07:39 mail02 sqlgrey: dbaccess: warning: couldn't do
query:#012INSERT INTO from_awl (sender_name, sender_domain, src,
first_seen, last_seen)
VALUES('shimvebvl','uwerim.ru','91.121.21','2015-08-19
00:00:57',NOW()):#012, reconnecting to DB

And why this would cause a disconnect/reconnect to occur?

I've searched all three systems in the DB_Cluster, and none of them
have any of these values. Why would sqlgrey think it's there in the
first place?

This appears to be the original occurrence of that entry, just a few
minutes prior:

Aug 19 00:00:57 mail02 postfix/smtpd[24228]: NOQUEUE: reject: RCPT
from webboundaries.com[91.121.21.199]: 450 4.7.1
<lan...@ex...>: Recipient address rejected: Greylisted
for 5 minutes; from=<shi...@uw...>
to=<lan...@ex...> proto=ESMTP helo=<webboundaries.com>

It then failed open, where postfix accepted it and was spamassassin
marked it as spam.

Thanks,
Alex

On Tue, Aug 18, 2015 at 11:58 AM, Alex <mys...@gm...> wrote:
> Hi guys,
>
>> I'd expect sqlgrey to be configured to log to the
>> mail log, although I suppose yours could be different.
>> This will give you timestamps to use when looking through
>> the db log.
>
> I've figured out some new information on this problem.
>
> Aug 18 06:06:19 mail01 sqlgrey: warning: Use of uninitialized value
> $DBI::errstr in concatenation (.) or string at /usr/sbin/sqlgrey line
> 250.
> Aug 18 06:06:19 mail01 sqlgrey: dbaccess: warning: couldn't do
> query:#012INSERT INTO from_awl (sender_name, sender_domain, src,
> first_seen, last_seen)
> VALUES('return','nl2go.com','91.121.21','2015-08-18
> 05:59:38',NOW()):#012, reconnecting to DB
> Aug 18 06:06:19 mail01 sqlgrey: dbaccess: Using DBIx:DBCluster
> Aug 18 06:06:19 mail01 sqlgrey: dbaccess: Read_hosts: localhost,
> ns1.example.com, mail02.example.com, mail03.example.com,
> mail01.example.com
>
> Aug 17 01:43:40 mail02 sqlgrey: dbaccess: warning: couldn't do
> query:#012INSERT INTO from_awl (sender_name, sender_domain, src,
> first_seen, last_seen)
> VALUES('financeiro','teamplayy.com.br','91.121.21','2015-08-17
> 01:37:00',NOW()):#012, reconnecting to DB
> Aug 17 01:53:29 mail01 sqlgrey: dbaccess: warning: couldn't do
> query:#012INSERT INTO from_awl (sender_name, sender_domain, src,
> first_seen, last_seen)
> VALUES('shefdemwp','uwerim.ru','91.121.21','2015-08-17
> 01:46:49',NOW()):#012, reconnecting to DB
> Aug 17 05:57:58 mail02 sqlgrey: dbaccess: warning: couldn't do
> query:#012INSERT INTO from_awl (sender_name, sender_domain, src,
> first_seen, last_seen)
> VALUES('conor.burrell','businessandfinance.com','91.121.21','2015-08-17
> 05:51:17',NOW()):#012, reconnecting to DB
> Aug 17 09:43:48 mail01 sqlgrey: dbaccess: warning: couldn't do
> query:#012INSERT INTO from_awl (sender_name, sender_domain, src,
> first_seen, last_seen)
> VALUES('claudinei.moreira','alper.com.br','91.121.21','2015-08-17
> 09:37:12',NOW()):#012, reconnecting to DB
> Aug 17 10:26:43 mail01 sqlgrey: dbaccess: warning: couldn't do
> query:#012UPDATE domain_awl SET last_seen = NOW(), first_seen =
> first_seen WHERE sender_domain = NULL AND src = NULL:#012Can't connect
> to MySQL server on 'ns1.example.com' (111), reconnecting to DB
> Aug 17 12:44:38 mail01 sqlgrey: dbaccess: warning: couldn't do
> query:#012INSERT INTO from_awl (sender_name, sender_domain, src,
> first_seen, last_seen)
> VALUES('memberservices=example-marketplace.com__#-21wpwe5yvrgp4h','rlie15guji343ap6.mnrzh7tg8d0vr194.m8vlizz.8-amgceai.na28.bnc.salesforce.com','136.146.208','2015-08-17
> 12:39:23',NOW()):#012, reconnecting to DB
>
> Our server was changed to "example" in the above. It looks like maybe
> some records were corrupted? Multiple entries contain the 91.121.21
> address. Should I just truncate all the tables and let them rebuild
> themselves with new email data or perhaps there's a more precise fix?
>
> Thanks,
> Alex