sqlgrey-users — Users of SQLgrey

Re: [Sqlgrey-users] SPF records check

[Klaus A. S. <kse...@gm...>]

Yawhen Kasarzhewski wrote:

> I am using 1.6.8. How to make it to check SPF records?

I am using the whitelister policy d=E6mon:

 =B7 http://packages.debian.org/whitelister

Permitted senders the bypasses greylisting.

Cheers,

--=20
Klaus Alexander Seistrup
http://klaus.seistrup.dk/

Re: [Sqlgrey-users] SPF records check

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

Yawhen Kasarzhewski wrote:
> Hi,
>
> I am using 1.6.8. How to make it to check SPF records?
>   

"Teach how to fish" answer :

grep -ri spf <sqlgrey_source_directory>

Short answer :

SQLgrey doesn't support SPF even in the development branch. Using SPF is 
probably better done with a dedicated SPF policy service.

Lionel

[Sqlgrey-users] SPF records check

[Yawhen K. <ye...@pi...>]

Hi,

I am using 1.6.8. How to make it to check SPF records?

Re: [Sqlgrey-users] greylisting and UTF8

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

Karl O. Pinc wrote:
> On 11/25/2007 05:10:23 AM, Lionel Bouton wrote:
>   
>> Nicolas STRANSKY wrote the following on 25.11.2007 11:53 :
>>
>> This isn't the problem. Mail addresses use ASCII. In ASCII some
>> characters aren't valid UTF-8 sequences. So you should't use UTF-8 to
>> store email adresses...
>>     
>
> There are (now) unicode domain name representations.
>   

They are quite old. The mechanism used by IDN predates the first SQLgrey
release :-)

> Does sqlgrey deal with internationalized domain names (RFC3490)
> punycode (RFC3492) and friends (RFC3454, RFC3491) or is that already
> taken care of by the MTAs?
>   

SQLgrey doesn't care *one bit*. IDN uses an Unicode encoding which is
meant to work with tools understanding ASCII only (which SQLgrey is).
IDN wouldn't be possible if you'd have to change each and every name
server, mail server, tool used for mail handling out there...

The only greylisting problem with SQLgrey could be the heuristics used
to guess if a name is more likely to be a mail server or a personal
(dialup/ADSL/cable/...) connection. Even then, the heuristics only care
about the first part of the DNS name, not the DNS zone it is attached
to. So they should be OK even with IDN.
> What does domain name internationalization mean for the content
> of sqlgrey's db?
>   

Nothing much, if you have tools designed to show the DB content, you'll
have to make them IDN-aware (which means they'll output unicode by the
way) in order to decode the domain name.

The only real practical problem with SQLgrey and IDN is the log. You'll
have the ASCII-encoded names in them and that could make things
difficult to diagnose. Though I suspect that everybody logs
ASCII-encoded names, so matching SQLgrey log entries with Postfix' or
other mail-related tool's ones should be OK.

Lionel

Re: [Sqlgrey-users] greylisting and UTF8

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

On 11/25/2007 05:10:23 AM, Lionel Bouton wrote:
> Nicolas STRANSKY wrote the following on 25.11.2007 11:53 :
>=20
> This isn't the problem. Mail addresses use ASCII. In ASCII some
> characters aren't valid UTF-8 sequences. So you should't use UTF-8 to
> store email adresses...

There are (now) unicode domain name representations.
Does sqlgrey deal with internationalized domain names (RFC3490)
punycode (RFC3492) and friends (RFC3454, RFC3491) or is that already
taken care of by the MTAs?

What does domain name internationalization mean for the content
of sqlgrey's db?

(See also: http://en.wikipedia.org/wiki/Internationalized_domain_name)


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

Re: [Sqlgrey-users] greylisting and UTF8

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

Nicolas STRANSKY wrote the following on 25.11.2007 11:53 :
> Hi,
>
> I have encountered this error while using sqlgrey:
>
> Nov 25 08:43:20 ovh sqlgrey: mail: failed to send: Cannot allocate memory
> Nov 25 08:43:20 ovh sqlgrey: dbaccess: error: couldn't access from_awl
> table:
> Nov 25 08:43:20 ovh sqlgrey: dbaccess: warning: couldn't do query:
> UPDATE from_awl SET last_seen = NOW(), first_seen = first_seen WHERE
> sender_name = 'robertandr�2' AND sender_domain = 'ohiohills.com' AND src
> = '222.123.27.243': ERREUR:  Séquence d'octets invalide pour le codage
> «UTF8» : 0xe93227 HINT:  Cette erreur peut aussi survenir si la séquence
> d'octets ne correspond au jeu de caractères attendu par le serveur et
> qui est contrôlé par « client_encoding ». , reconnecting to DB
>   

Your database is configured to use the UTF-8 encoding, use ASCII instead.

> Nov 25 08:43:30 ovh sqlgrey: mail: failed to send: Cannot allocate memory
>   

It seems you have other problems...

> The important thing is that by using UTF8 characters in the FROM adress,
> any machine is able to bypass sqlgrey because it fails to deal with UTF8
> characters...

This isn't the problem. Mail addresses use ASCII. In ASCII some
characters aren't valid UTF-8 sequences. So you should't use UTF-8 to
store email adresses...

Lionel

[Sqlgrey-users] greylisting and UTF8

[Nicolas S. <Ni...@st...>]

Hi,

I have encountered this error while using sqlgrey:

Nov 25 08:43:20 ovh sqlgrey: mail: failed to send: Cannot allocate memory
Nov 25 08:43:20 ovh sqlgrey: dbaccess: error: couldn't access from_awl
table:
Nov 25 08:43:20 ovh sqlgrey: dbaccess: warning: couldn't do query:
UPDATE from_awl SET last_seen = NOW(), first_seen = first_seen WHERE
sender_name = 'robertandr�2' AND sender_domain = 'ohiohills.com' AND src
= '222.123.27.243': ERREUR:  Séquence d'octets invalide pour le codage
«UTF8» : 0xe93227 HINT:  Cette erreur peut aussi survenir si la séquence
d'octets ne correspond au jeu de caractères attendu par le serveur et
qui est contrôlé par « client_encoding ». , reconnecting to DB
Nov 25 08:43:30 ovh sqlgrey: mail: failed to send: Cannot allocate memory

The important thing is that by using UTF8 characters in the FROM adress,
any machine is able to bypass sqlgrey because it fails to deal with UTF8
characters... Is there any workaround for this ? I'm using sqlgrey 1.7.3
with postgresql.

Thanks,
-- 
Nico
La science ne se soucie ni de plaire, ni de déplaire, elle est
inhumaine.
	-+- Jacques Anatole François Thibault dit Anatole France
	    (1844-1924) -+-

Re: [Sqlgrey-users] SQLGREY-libmysqlclient.so.14 centos4

[Dan F. <da...@ha...>]

Hey Joshua.

This looks very much like your perl DBI or perl DBD-Mysql is broken 
after upgrade.
Id suggest reinstalling either/both of these.

Either way, the "module DBD::mysql: libmysqlclient.so.14: cannot open 
shared object file: No such file or directory" error is coming from 
perl, not from sqlgrey, so any perl-script using DBD-Mysql will fail.

- Dan

Joshua Brewer wrote:
>
> Hey guys,
>
>    I have recently upgraded to mysql 5 via yum on a centos 4 install, and it
> has since broken my sqlgrey 1.6.7 install:
>
>
> Sep 17 13:56:27 vps-100_mx1 sqlgrey: fatal: Can't load
> '/usr/lib64/perl5/site_perl/5.8.5/x86_64-linux-thread-multi/auto/DBD/mysql/m
> ysql.so' for module DBD::mysql: libmysqlclient.so.14: cannot open shared
> object file: No such file or directory at
> /usr/lib64/perl5/5.8.8/x86_64-linux-thread-multi/DynaLoader.pm line 230.  at
> (eval 9) line 3 
> Sep 17 13:56:27 vps-100_mx1 sqlgrey: fatal: Can't load
> '/usr/lib64/perl5/site_perl/5.8.5/x86_64-linux-thread-multi/auto/DBD/mysql/m
> ysql.so' for module DBD::mysql: libmysqlclient.so.14: cannot open shared
> object file: No such file or directory at
> /usr/lib64/perl5/5.8.8/x86_64-linux-thread-multi/DynaLoader.pm line 230.  at
> (eval 9) line 3 Compilation failed in require at (eval 9) line 3.
> Sep 17 13:56:27 vps-100_mx1 sqlgrey: fatal: install_driver(mysql) failed:
> Can't load 
> '/usr/lib64/perl5/site_perl/5.8.5/x86_64-linux-thread-multi/auto/DBD/mysql/m
> ysql.so' for module DBD::mysql: libmysqlclient.so.14: cannot open shared
> object file: No such file or directory at
> /usr/lib64/perl5/5.8.8/x86_64-linux-thread-multi/DynaLoader.pm line 230.  at
> (eval 9) line 3 Compilation failed in require at (eval 9) line 3. Perhaps a
> required shared library or dll isn't installed where expected  at
> /usr/sbin/sqlgrey line 775
>
> [root@vps-100_mx1 lib]#
> [root@vps-100_mx1 lib]# locate mysql.so
> /var/lib/mysql/mysql.sock
> /usr/lib64/perl5/site_perl/5.8.5/x86_64-linux-thread-multi/auto/DBD/mysql/my
> sql.so
> /usr/lib64/perl5/vendor_perl/5.8.5/x86_64-linux-thread-multi/auto/DBD/mysql/
> mysql.so
> /root/.cpan/build/DBD-mysql-4.005/blib/arch/auto/DBD/mysql/mysql.so
> [root@vps-100_mx1 lib]# locate libmysqlclient.so.14
> /lib/mysql/libmysqlclient.so.14.0.0
> /lib/mysql/libmysqlclient.so.14
> /lib64/mysql/libmysqlclient.so.14.0.0
> /lib64/mysql/libmysqlclient.so.14
>
> I have tried symlinks at would-be common locations, any ideas?
>
> -jb
>
>
>
> -------------------------------------------------------------------------
> This SF.net email is sponsored by: Microsoft
> Defy all challenges. Microsoft(R) Visual Studio 2005.
> http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/
> _______________________________________________
> Sqlgrey-users mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlgrey-users
>
>   

[Sqlgrey-users] SQLGREY-libmysqlclient.so.14 centos4

[Joshua B. <jb...@at...>]

Hey guys,

   I have recently upgraded to mysql 5 via yum on a centos 4 install, and it
has since broken my sqlgrey 1.6.7 install:


Sep 17 13:56:27 vps-100_mx1 sqlgrey: fatal: Can't load
'/usr/lib64/perl5/site_perl/5.8.5/x86_64-linux-thread-multi/auto/DBD/mysql/m
ysql.so' for module DBD::mysql: libmysqlclient.so.14: cannot open shared
object file: No such file or directory at
/usr/lib64/perl5/5.8.8/x86_64-linux-thread-multi/DynaLoader.pm line 230.  at
(eval 9) line 3 
Sep 17 13:56:27 vps-100_mx1 sqlgrey: fatal: Can't load
'/usr/lib64/perl5/site_perl/5.8.5/x86_64-linux-thread-multi/auto/DBD/mysql/m
ysql.so' for module DBD::mysql: libmysqlclient.so.14: cannot open shared
object file: No such file or directory at
/usr/lib64/perl5/5.8.8/x86_64-linux-thread-multi/DynaLoader.pm line 230.  at
(eval 9) line 3 Compilation failed in require at (eval 9) line 3.
Sep 17 13:56:27 vps-100_mx1 sqlgrey: fatal: install_driver(mysql) failed:
Can't load 
'/usr/lib64/perl5/site_perl/5.8.5/x86_64-linux-thread-multi/auto/DBD/mysql/m
ysql.so' for module DBD::mysql: libmysqlclient.so.14: cannot open shared
object file: No such file or directory at
/usr/lib64/perl5/5.8.8/x86_64-linux-thread-multi/DynaLoader.pm line 230.  at
(eval 9) line 3 Compilation failed in require at (eval 9) line 3. Perhaps a
required shared library or dll isn't installed where expected  at
/usr/sbin/sqlgrey line 775

[root@vps-100_mx1 lib]#
[root@vps-100_mx1 lib]# locate mysql.so
/var/lib/mysql/mysql.sock
/usr/lib64/perl5/site_perl/5.8.5/x86_64-linux-thread-multi/auto/DBD/mysql/my
sql.so
/usr/lib64/perl5/vendor_perl/5.8.5/x86_64-linux-thread-multi/auto/DBD/mysql/
mysql.so
/root/.cpan/build/DBD-mysql-4.005/blib/arch/auto/DBD/mysql/mysql.so
[root@vps-100_mx1 lib]# locate libmysqlclient.so.14
/lib/mysql/libmysqlclient.so.14.0.0
/lib/mysql/libmysqlclient.so.14
/lib64/mysql/libmysqlclient.so.14.0.0
/lib64/mysql/libmysqlclient.so.14

I have tried symlinks at would-be common locations, any ideas?

-jb

Re: [Sqlgrey-users] Solution to sqlgrey & postgresql memory leaks?

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

On 08/06/2007 09:37:20 AM, Karl O. Pinc wrote:

> Tentively, after running for one night, 1.6.8 seems to have
> made the leak go away.  Yay.
> 
> I'll report back again in a week or so.

The bad leak is definitely gone.

The sqlgrey process does seem to use very slightly more
ram as time goes on, but the growth appears
to happen ever more slowly so it's not a problem.

FYI
ps axv on Linux 2.6.18-4-486 (Debian Etch) has
DSS (whatever that is, "man ps" says nothing about it)
goes from 11561 to 12089 whereas RSS goes from
9096 to 9652.  That's over about a 11 day period.

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

Re: [Sqlgrey-users] Solution to sqlgrey & postgresql memory leaks?

[Steve M. <st...@op...>]

SGkNCg0KTmV3IHVzZXIgaGVyZS4NCg0KSSBoYXZlIDIgaW5jb21pbmcgbWFpbCBnYXRld2F5cyBy
dW5uaW5nIGp1c3Qgbm93LiAxIG9uIEZlZG9yYSBDb3JlIDYgJiAxIG9uIEZlZG9yYSA3LiBCb3Ro
IHBhdGNoZWQgJiB1cCB0byBkYXRlIHJ1bm5pbmcgdGhlIGxhdGVzdCB2ZXJzaW9ucyBvZiBQb3N0
Zml4ICYgU1FMR3JleS4NCg0KSSBoYXZlIHVzZWQgdGhlIGFzeW5jaHJvbm91cyBjbGVhbnVwIHNp
bmNlIGRheSBvbmUuLndoaWNoIHdhcyAyIHdlZWtzIGFnby4gTm90IG9uZSBsb2NrdXAuIEJvdGgg
c2VydmVycyBhdmVyYWdlIGFyb3VuZCA0MCAtIDUwLDAwMCBlbWFpbHMgYSBkYXksIHRoZSBtYWpv
cml0eSwgYXJvdW5kIDkwJSBiZWluZyBncmV5bGlzdGVkIGFuZCBkcm9wcGVkLiBPbiBhdmVyYWdl
IGJvdGggZGF0YWJhc2VzIHNob3cgYXJvdW5kIDMwLDAwMCBlbnRyaWVzIHRoYXQgYXJlIGN1cnJl
bnRseSBncmV5bGlzdGVkLg0KDQpCb3RoIGFyZSBydW5uaW5nIGluIEVTWCAzLjAyIGFuZCBJIGhh
dmUgbm90IG5vdGljZWQgYW55IG1lbW9yeSBsZWFrcy4gV2hhdCBJIGhhdmUgbm90aWNlZCB0aG91
Z2gsIG9uIGJvdGggbXkgbWFjaGluZXMgaXMgdGhhdCB0aGUgc3luYyBjbGVhbnVwIG5ldmVyIHdv
cmtlZCwgd2hpY2ggaXMgd2h5IEkgdG9vayB0aGUgY2hhbmNlIGFuZCBtb3ZlZCB0byBhc3luY2hy
b25vdXMuDQoNClN0ZXZlDQoNCi0tLS0tT3JpZ2luYWwgTWVzc2FnZS0tLS0tDQpGcm9tOiBzcWxn
cmV5LXVzZXJzLWJvdW5jZXNAbGlzdHMuc291cmNlZm9yZ2UubmV0IFttYWlsdG86c3FsZ3JleS11
c2Vycy1ib3VuY2VzQGxpc3RzLnNvdXJjZWZvcmdlLm5ldF0gT24gQmVoYWxmIE9mIExpb25lbCBC
b3V0b24NClNlbnQ6IFNhdHVyZGF5LCBBdWd1c3QgMTEsIDIwMDcgODoxMiBBTQ0KVG86IFNRTGdy
ZXkgdXNlcnMgbWFpbGluZy1saXN0DQpTdWJqZWN0OiBSZTogW1NxbGdyZXktdXNlcnNdIFNvbHV0
aW9uIHRvIHNxbGdyZXkgJiBwb3N0Z3Jlc3FsIG1lbW9yeSBsZWFrcz8NCg0KQWRhbSBTasO4Z3Jl
biB3cm90ZSB0aGUgZm9sbG93aW5nIG9uIDExLjA4LjIwMDcgMTI6NTYgOg0KPiBPbiBNb24sIDA2
IEF1ZyAyMDA3IDE2OjQ4OjA0ICswMjAwLCBMaW9uZWwgd3JvdGU6DQo+DQo+ICAgDQo+PiBTbyB0
aGlzIGxlYWsgc2VlbWVkIHRvIGJlIGluIHRoZSBJbmFjdGl2ZURlc3Ryb3kgaGFuZGxpbmcgb2Yg
cmVjZW50IERCSQ0KPj4gb3IgREJELSouDQo+PiAgICAgDQo+DQo+IFRoZXJlIHdhcyByZWNlbnRs
eSBhIG5pY2UgZGVzY3JpcHRpb24gb2YgaG93IHRvIGhhbmRsZSAkZGJoLCBmb3JraW5nIGFuZA0K
PiBJbmFjdGl2ZURlc3Ryb3kgb3ZlciBvbiB0aGUgREJEOjpQZyBtYWlsaW5nbGlzdDoNCj4NCj4g
IDxodHRwOi8vYXJ0aWNsZS5nbWFuZS5vcmcvZ21hbmUuY29tcC5kYi5wb3N0Z3Jlc3FsLmRiZHBn
LzIyNzY+DQo+DQo+ICAgDQoNCkkgc3VzcGVjdCB0aGF0IHRoZSBwcm9ibGVtIHdpdGggYXN5bmNo
cm9ub3VzIGNsZWFudXAgaXMgbGlua2VkIHRvIHRoZQ0KZmFjdCB0aGF0IEkgcmV1c2UgdGhlIHNh
bWUgdmFyaWFibGUgaW4gdGhlIGZvcmtlZCBwcm9jZXNzIHRvIHN0b3JlIHRoZQ0KbmV3IGRhdGFi
YXNlIGhhbmRsZS4gVG8gYXZvaWQgdGhlIGhhbmRsZSB0byBiZSBjbG9zZWQgSSBzZXQgaXQgdXAg
dG8NCkluYWN0aXZlRGVzdHJveSwgYnV0IGl0IHNlZW1zIHRoZSBuYW1lIG9mIHRoaXMgaGFzaCBr
ZXkgZG9lcyBub3QgcmVmbGVjdA0KaXRzIHJlYWwgcHVycG9zZS4gSWYgSSByZW1lbWJlciBjb3Jy
ZWN0bHkgSSB0aG91Z2h0IHRoYXQgdGhlIGxvZ2ljIHdhcw0KYWN0dWFsbHkgdGhlIG9wcG9zaXRl
IG9mIHRoZSBvbmUgZGVzY3JpYmVkIGluIHRoZSBwZXJsZG9jDQooSW5hY2l2ZURlc3Ryb3kgb24g
dGhlIGZhdGhlciBpbnN0ZWFkIG9mIHRoZSBjaGlsZCkgYmVjYXVzZSBpdCB3YXMgdGhlDQpvbmx5
IHNldHVwIHRoYXQgc29tZXRpbWVzIHdvcmtlZC4NCkl0IHNlZW1zIHlvdSBzaW1wbHkgY2FuJ3Qg
cmV1c2UgdGhlIHNhbWUgdmFyaWFibGUgaW4gdGhlIGNoaWxkcmVuLg0KT2RkLi4uIEknbGwgaGF2
ZSBhIGxvb2sgYXQgdGhhdCBpbiB0aGUgZnV0dXJlIHRvIHJlc3VycmVjdCB0aGUNCmFzeW5jaHJv
bm91cyBjbGVhbnVwLg0KDQpMaW9uZWwNCg0KDQoNCi0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t
LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0NClRoaXMgU0Yu
bmV0IGVtYWlsIGlzIHNwb25zb3JlZCBieTogU3BsdW5rIEluYy4NClN0aWxsIGdyZXBwaW5nIHRo
cm91Z2ggbG9nIGZpbGVzIHRvIGZpbmQgcHJvYmxlbXM/ICBTdG9wLg0KTm93IFNlYXJjaCBsb2cg
ZXZlbnRzIGFuZCBjb25maWd1cmF0aW9uIGZpbGVzIHVzaW5nIEFKQVggYW5kIGEgYnJvd3Nlci4N
CkRvd25sb2FkIHlvdXIgRlJFRSBjb3B5IG9mIFNwbHVuayBub3cgPj4gIGh0dHA6Ly9nZXQuc3Bs
dW5rLmNvbS8NCl9fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19fX19f
DQpTcWxncmV5LXVzZXJzIG1haWxpbmcgbGlzdA0KU3FsZ3JleS11c2Vyc0BsaXN0cy5zb3VyY2Vm
b3JnZS5uZXQNCmh0dHBzOi8vbGlzdHMuc291cmNlZm9yZ2UubmV0L2xpc3RzL2xpc3RpbmZvL3Nx
bGdyZXktdXNlcnMNCg==

Re: [Sqlgrey-users] Solution to sqlgrey & postgresql memory leaks?

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

Adam Sjøgren wrote the following on 11.08.2007 12:56 :
> On Mon, 06 Aug 2007 16:48:04 +0200, Lionel wrote:
>
>   
>> So this leak seemed to be in the InactiveDestroy handling of recent DBI
>> or DBD-*.
>>     
>
> There was recently a nice description of how to handle $dbh, forking and
> InactiveDestroy over on the DBD::Pg mailinglist:
>
>  <http://article.gmane.org/gmane.comp.db.postgresql.dbdpg/2276>
>
>   

I suspect that the problem with asynchronous cleanup is linked to the
fact that I reuse the same variable in the forked process to store the
new database handle. To avoid the handle to be closed I set it up to
InactiveDestroy, but it seems the name of this hash key does not reflect
its real purpose. If I remember correctly I thought that the logic was
actually the opposite of the one described in the perldoc
(InaciveDestroy on the father instead of the child) because it was the
only setup that sometimes worked.
It seems you simply can't reuse the same variable in the children.
Odd... I'll have a look at that in the future to resurrect the
asynchronous cleanup.

Lionel

Re: [Sqlgrey-users] Solution to sqlgrey & postgresql memory leaks?

[<as...@ko...>]

On Mon, 06 Aug 2007 16:48:04 +0200, Lionel wrote:

> So this leak seemed to be in the InactiveDestroy handling of recent DBI
> or DBD-*.

There was recently a nice description of how to handle $dbh, forking and
InactiveDestroy over on the DBD::Pg mailinglist:

 <http://article.gmane.org/gmane.comp.db.postgresql.dbdpg/2276>


  Best regards,

    Adam

-- 
 "Be?"                                                        Adam Sjøgren
 "Be. Be period. Is."                                    as...@ko...

Re: [Sqlgrey-users] Solution to sqlgrey & postgresql memory leaks?

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

Karl O. Pinc wrote:
> Tentively, after running for one night, 1.6.8 seems to have
> made the leak go away.  Yay.
>
> I'll report back again in a week or so.
>
>   

1.7.6 made the leak go away on my configuration too.

So this leak seemed to be in the InactiveDestroy handling of recent DBI
or DBD-*.

Knowing where this beast is located is a huge relief :-)

Lionel

Re: [Sqlgrey-users] Solution to sqlgrey & postgresql memory leaks?

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

On 08/05/2007 01:41:10 PM, Lionel Bouton wrote:

> Here
> 
> is what I think happens:
> 
> InactiveDestroy => 1 is used to support asynchronsous cleaning of the
> DB
> (which is quite buggy, I never understood why and still recommend to
> leave it deactivated unless it really stalls the server too long and
> asynchronous cleaning actually works for you). InactiveDestroy is
> meant
> to prevent the database handle from being destroyed when it goes out
> of
> context (which happens when you fork and open a new database
> connection
> for example). I believe that this InactiveDestroy could be extended to
> 
> the prepared statements (probably erroneously, it doesn't make sense
> for
> me to do so), which would explain the leak.
> 
> I'll simply remove InactiveDestroy => 1 when asynchronous cleaning
> isn't
> active, this should help (and shouldn't have any bad side-effect).

Tentively, after running for one night, 1.6.8 seems to have
made the leak go away.  Yay.

I'll report back again in a week or so.


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

[Sqlgrey-users] [RELEASE] 1.6.8

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

The 1.6.8 release is out on sourceforge.

This release includes:
 - Crash on syslog death bugfix
 - Database handling fix (deactivated InactiveDestroy unless needed)
 - Reopen database connection on SIGUSR1

Happy greylisting,

Lionel.

[Sqlgrey-users] [RELEASE] 1.7.6

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

The 1.7.6 release is out on sourceforge.

This has the database reconnect on SIGUSR1 enhancement and the attempted
fix for memory leaks suggested by Adam.

Testing 1.6.8 now which will have these fixes plus the syslog death fix too.

Happy greylisting,

Lionel.

Re: [Sqlgrey-users] Solution to sqlgrey & postgresql memory leaks?

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

On 08/05/2007 01:41:10 PM, Lionel Bouton wrote:
> Adam Sjøgren wrote the following on 05.08.2007 20:32 :
> >
> > What is then causing this loop of a common sqlgrey-query to leak?

> Here
> 
> is what I think happens:
> 
> InactiveDestroy => 1 is used to support asynchronsous cleaning of the
> DB
> (which is quite buggy, I never understood why and still recommend to
> leave it deactivated unless it really stalls the server too long and
> asynchronous cleaning actually works for you).

FYI I'm pretty sure the leak is perl related.  I didn't have one,
in either sqlgrey or postgresql, until upgrading from Debian sarge
to etch.  I believe this left the postgresql version unchanged at 7.4,
and the sqlgrey was unchanged.  That leaves the problem on
perl's doorstep, most likely.

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

Re: [Sqlgrey-users] Solution to sqlgrey & postgresql memory leaks?

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

Adam Sjøgren wrote the following on 05.08.2007 20:32 :
> On Sun, 05 Aug 2007 19:22:12 +0200, Lionel wrote:
>
>   
>>> Here's one example from 1.6.7, there are others:
>>>       
>  
>   
>>> sub is_in_from_awl {
>>> my ($self, $sender_name, $sender_domain, $host) = @_;
>>>
>>> # last_seen less than $self->{sqlgrey}{awl_age} days ago
>>> my $sth = $self->prepare("SELECT 1 FROM $from_awl " .
>>>       
>
>   
>> Note that SQLgrey uses prepare and not prepare_cached here: the prepared 
>> statement ($sth) will be reclaimed when it goes out of scope (unless 
>> there is a bug in DBI or the driver...).
>>     
>
> This should be easy to test, comparing something like:
>
>  #!/usr/bin/perl
>
>  use strict;
>  use warnings;
>  use DBI;
>
>  print "Testing... \n"; test(); print "Done\n"; my $wait=<>;
>
>  sub test {
>      my $dbh=DBI->connect("DBI:Pg:dbname=sqlgrey", "sqlgrey", "xxxxxxxx",  { PrintError=>0, AutoCommit=>1, InactiveDestroy=>1 });
>
>      my $i=0;
>      while ($i++<10000) {
>          my $sth=$dbh->prepare("SELECT 1 FROM domain_awl WHERE sender_domain = ? AND src = ? AND last_seen > timestamp '2007-08-05 07:00:00' - INTERVAL '6 DAY';");
>          $sth->execute("xxxxxxxxxxxx", "xxxxxxxxxxxxx");
>          my $res=$sth->fetchall_arrayref;
>      }
>  }
>
> to a program where the timestamp varies in the string.
>
>                                  o o o
>
> Oh, but, running the program above on a Debian etch amd64 box uses an
> increasing amount of RAM, ending at around ~70MB.
>
> That is without varying the "dbnow" that Karl O. Pinc suggested as a
> possible leak.
>
> What is then causing this loop of a common sqlgrey-query to leak?
>
>                                  o o o
>
> Removing the "InactiveDestroy=>1" option gives the result that the
> program uses ~6MB the whole time.
>   

You are right on time, I was considering releasing 1.6.8 shortly. Here 
is what I think happens:

InactiveDestroy => 1 is used to support asynchronsous cleaning of the DB 
(which is quite buggy, I never understood why and still recommend to 
leave it deactivated unless it really stalls the server too long and 
asynchronous cleaning actually works for you). InactiveDestroy is meant 
to prevent the database handle from being destroyed when it goes out of 
context (which happens when you fork and open a new database connection 
for example). I believe that this InactiveDestroy could be extended to 
the prepared statements (probably erroneously, it doesn't make sense for 
me to do so), which would explain the leak.

I'll simply remove InactiveDestroy => 1 when asynchronous cleaning isn't 
active, this should help (and shouldn't have any bad side-effect).

Lionel

Re: [Sqlgrey-users] Solution to sqlgrey & postgresql memory leaks?

[<as...@ko...>]

On Sun, 05 Aug 2007 19:22:12 +0200, Lionel wrote:

>> Here's one example from 1.6.7, there are others:
 
>> sub is_in_from_awl {
>> my ($self, $sender_name, $sender_domain, $host) = @_;
>> 
>> # last_seen less than $self->{sqlgrey}{awl_age} days ago
>> my $sth = $self->prepare("SELECT 1 FROM $from_awl " .

> Note that SQLgrey uses prepare and not prepare_cached here: the prepared 
> statement ($sth) will be reclaimed when it goes out of scope (unless 
> there is a bug in DBI or the driver...).

This should be easy to test, comparing something like:

 #!/usr/bin/perl

 use strict;
 use warnings;
 use DBI;

 print "Testing... \n"; test(); print "Done\n"; my $wait=<>;

 sub test {
     my $dbh=DBI->connect("DBI:Pg:dbname=sqlgrey", "sqlgrey", "xxxxxxxx",  { PrintError=>0, AutoCommit=>1, InactiveDestroy=>1 });

     my $i=0;
     while ($i++<10000) {
         my $sth=$dbh->prepare("SELECT 1 FROM domain_awl WHERE sender_domain = ? AND src = ? AND last_seen > timestamp '2007-08-05 07:00:00' - INTERVAL '6 DAY';");
         $sth->execute("xxxxxxxxxxxx", "xxxxxxxxxxxxx");
         my $res=$sth->fetchall_arrayref;
     }
 }

to a program where the timestamp varies in the string.

                                 o o o

Oh, but, running the program above on a Debian etch amd64 box uses an
increasing amount of RAM, ending at around ~70MB.

That is without varying the "dbnow" that Karl O. Pinc suggested as a
possible leak.

What is then causing this loop of a common sqlgrey-query to leak?

                                 o o o

Removing the "InactiveDestroy=>1" option gives the result that the
program uses ~6MB the whole time.

                                 o o o

Quickly leafing through some of the DBI docs, I guess that this may be
handled correctly in sqlgrey, but not in my simple, non-forking
test-script. I am probably chasing a red herring here?

I may very well be, but I'll try commenting the InactiveDestroy=>1 line
out anyway and see if that alleviates my problem.


(The versions I used to test are:

 DBI        1.57
 DBD::Pg    1.49
 Postgresql 8.1.9)


  Best regards,

    Adam

-- 
 "Money always takes the place of life"                       Adam Sjøgren
                                                         as...@ko...

Re: [Sqlgrey-users] Solution to sqlgrey & postgresql memory leaks?

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

Karl O. Pinc wrote the following on 05.08.2007 20:04 :
> Thanks for looking at this.
>
> The DBI had also better be doing a "DEALLOCATE" when the
> prepared query goes out of scope, or else (in PostgreSQL)
> it won't get deallocated on the server side.
>
> I don't see the point in using prepared queries that
> are only executed once.

Protecting against SQL injection attacks. You should always use prepared 
statements for user submitted data. You can manually quote the data but 
it makes ugly code and isn't easily modified to use cached prepared 
statements when optimizing later.

This is actually why the date wasn't out of the prepared statements in 
your example: this is the only part in the condition that isn't input 
from Postfix (and so could be manipulated by the original sender, 
Postfix doesn't sanitize everything and maybe even anything before 
submiting it to SQLgrey).

Lionel.

Re: [Sqlgrey-users] Solution to sqlgrey & postgresql memory leaks?

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

On 08/05/2007 12:22:12 PM, Lionel Bouton wrote:
> Karl O. Pinc wrote the following on 05.08.2007 18:26 :
> > Hi,
> >
> > So, I looked at the code.  I'm not a perl wiz,
> > but the problem appears to be obvious. Sqlgrey
> > seems to be repeatedly creating prepared queries,
> > each of which differs from the last.  The whole
> > point of prepared queries is to have _invariant_
> > queries, substituted parameters excepted.
> >
> >
> 
> Not exactly. It's only a problem when you cache the prepared
> statements.

Thanks for looking at this.

The DBI had also better be doing a "DEALLOCATE" when the
prepared query goes out of scope, or else (in PostgreSQL)
it won't get deallocated on the server side.

I don't see the point in using prepared queries that
are only executed once.  That would only introduce
extra overhead involved in the allocation and
deallocation of the prepared queries.  Which is, I suppose, what it
is that you're looking into.  :)

(To be persnickity about it, the queries won't be optimized
any better if you use prepared queries, but you eliminiate
at least some sql parsing overhead (perhaps all depending
on the api) and you eliminate the planner overhead --
which can amount to a lot.)

I see what you mean about using prepare and not prepare_cached.
You're right about my proposal not fixing memory leaks.  But
I've a gut feeling that it will work-around the memory
leaks, which I'm guessing have to do with preparing a
lot of unique statements.

Let me know if you need someone to test something.

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

Re: [Sqlgrey-users] Signal to re-initialize database connection

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

Karl O. Pinc wrote the following on 05.08.2007 18:26 :
> Hello,
>
> In my opinion sqlgrey is missing a badly needed feature,
> the ability to re-initialize the database connection
> upon receipt of a signal.  (I suggest SIGHUP.)

Hum, SIGHUP handling in Net::Server was buggy when I tried to use it, so
we use SIGUSR1 with SQLgrey to make it reload its whitelists.
Reconnecting to the database seems a good thing to do indeed. I'm adding
it right now to both 1.6 and 1.7 branches.
I'll continue using SIGUSR1 to avoid re-debugging Net::Server, think of
it as "SIGHUP for SQLgrey". The reconnection will be done asynchronously
(we don't want to reconnect in the middle of a query) just before
processing the next mail after the SIGUSR1.

Let's hope my Perl isn't too rusty after 2 years of Ruby :-)

Lionel.

Re: [Sqlgrey-users] Solution to sqlgrey & postgresql memory leaks?

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

Karl O. Pinc wrote the following on 05.08.2007 18:26 :
> Hi,
>
> So, I looked at the code.  I'm not a perl wiz,
> but the problem appears to be obvious. Sqlgrey
> seems to be repeatedly creating prepared queries,
> each of which differs from the last.  The whole
> point of prepared queries is to have _invariant_
> queries, substituted parameters excepted.
>
>   

Not exactly. It's only a problem when you cache the prepared statements.

> Here's one example from 1.6.7, there are others:
>
> sub is_in_from_awl {
>      my ($self, $sender_name, $sender_domain, $host) = @_;
>
>      # last_seen less than $self->{sqlgrey}{awl_age} days ago
>      my $sth = $self->prepare("SELECT 1 FROM $from_awl " .
>   

Note that SQLgrey uses prepare and not prepare_cached here: the prepared 
statement ($sth) will be reclaimed when it goes out of scope (unless 
there is a bug in DBI or the driver...).

> I'd love to see this fixed.
>
>   

In fact you made me think of a performance problem. If we do what you 
ask for we won't solve any leaks (or if we do, it will be because we 
don't hit a bug in DBI anymore) but we will allow the database to 
optimize our queries a little more (unless we run on DBs where prepared 
statement are emulated in the driver, but that's another matter).

I'll take a look at that...

Lionel.

[Sqlgrey-users] Signal to re-initialize database connection

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

Hello,

In my opinion sqlgrey is missing a badly needed feature,
the ability to re-initialize the database connection
upon receipt of a signal.  (I suggest SIGHUP.)

Some databases with which I'm familiar do not update
some important values for the duration of a database
connection.  The one that's bitten me most often
in the past is the data that drives the query
planning.  I know that this is a problem with
PostgreSQL 8.1, and I've run into it in other
major database products in the past.
A long-lived database connection, over
a varying dataset, can wind up running very badly
planned queries.  This could be a problem for
sqlgrey, and certainly is a problem after the
initial install when the database is empty.

Such a signal would also provide a work-around
for bugs (memory leaks anyone?) in the database
back-end or the perl libraries.  Cron could be used
to have sqlgrey re-connect to the db either at
regular intervals, or when a particular problem
is detected.  Databases are big and complicated
and their backends are going to have bugs.

The long as the the short of it is that a lot of
databases just arn't designed to have connections
that last forever.  Of course, restarting sqlgrey
is always an option.  But that's an undesirable
interruption of the MTA's operation, resulting
in a window of time during which SMTP clients
get 450 responses and there is a consequent
unnecessary delay in mail delivery.

I know sqlgrey is not under active development
right now, but I hope that somebody will at
least consider this.  (This is understandable,
sqlgrey works -- at least until it doesn't :)
If not, then I'd appreciate
any suggestions regarding how to archive this
request so that it can be considered by
future developers.  Thank you.

Regards,

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