sqlgrey-users — Users of SQLgrey

Re: [Sqlgrey-users] MySQL: first_seen updated when it shouldn't

[Michel B. <mi...@bo...>]

Le Dimanche 05 Juin 2005 20:15, Lionel Bouton a =E9crit :
>
> >Other subject: Lionel, have you had time to think again about the
> > tarpitting / throttling feature that I had suggested ? I still would =
like
> > it ;-)
>
> I'm not yet convinced it's a good idea. I'll sum up what I understand
> about tarpitting below for you to point mistakes or missing points.
>
> Tarpitting (refusing to create new connect entries if there are already
> <n> existing entries with the same source, with refinements to disable
> tarpitting when domain_awl holds entries for the source or enough
> entries exist in from_awl) could help preventing pollutions of the
> connect table from one single src.

Yes, and it might by the way prevent a possible attack against a greylist=
ing=20
mailserver, as otherwise it would be easy for an evil system to flood a=20
connect table with thousands of entries or more...

> This would have two main benefits :
> B1/ faster DB access to the connect table (which probably is the most
> used on common config under heavy Zombie pressure...) from SQLgrey.
> B2/ easier analysis of the connect table by a curious sysadmin.

and B3/ the connect table wouldn't grow too big on disk

> Here are the risks I'm worried about :
> R1/ using tarpitting will also interfere with legit mails that don't
> match AWLs (more retries).

I have already stated that I believe it probably wouldn't be an issue,=20
especially if using some refinements which you citate above.

> R2/ SQLgrey will have to do another query on the connect table which
> would most probably kill the performance advantage we get from a smalle=
r
> connect table

Yes, that's one more query on "connect", but this will only affect=20
"newcomers", and not sources that are already AWL'd.

Some time ago, you were considering adding supplementary tables (such as =
a=20
"connect_awl" and even a "src_awl") and the fact that this needed=20
supplementary queries for most of the messages didn't seem to be a=20
show-stopper ;-)

> and for the refinements, one query involving domain_awl=20
> and if needed another involving from_awl.

Yes, but those "refinements" will be used only for sources which already =
have=20
more than "n" records in connect, basically zombies. If we're under zombi=
e=20
attack, the corresponding DB pages will probably be in-cache and the quer=
ies=20
will be fast.

And when the "refinements" are called for non-zombies (big sites), their =
very=20
purpose it to make sure the messages will be accepted fast, so the=20
refinements won't be repeatedly used for long...

> My main problem for me was with R1 until the number of queries piled up
> to avoid the fact that in some cases (big ISPs consolidating email
> infrastructure, new smtp-out adresses, ...) you might very well
> introduce huge delays or even bounce mails (which was a show stopper fo=
r
> me). Now I think R2 will make the whole thing pointless.

I still believe that this idea should be tried out to experiment how it=20
behaves in "real life".

Avoiding zombie of virussed machines to pollute connect to a great deal, =
and=20
making sure that a "connect flooding attack" cannot be done seems to me g=
ood=20
enough reasons to make it interesting...

--=20
Michel Bouissou <mi...@bo...> OpenPGP ID 0xDDE8AC6E

Re: [Sqlgrey-users] MySQL: first_seen updated when it shouldn't

[Michel B. <mi...@bo...>]

Le Dimanche 05 Juin 2005 20:15, Lionel Bouton a =E9crit :
>
> CURRENT_TIMESTAMP won't work with SQLite. If I need to change the table
> creation, I'd better change the order of the columns.

Another method would be to specify "default 0" for all timestamps, and th=
is=20
should prevent them to be auto-updated.

> Although I have found another way: if the UPDATE statements are modifie=
d
> to look like:
> UDPATE <awl> SET first_seen =3D first_seen, last_seen =3D <now> WHERE
> <cond>. MySQL doesn't change first_seen (at least 4.1.12 doesn't). As
> the 2 timestamp columns are forced, I can't imagine a way for any MySQL
> version to change the values.
>
> This is a quick fix, won't create ugly special cases and the database
> system will probably optimize the query properly.

This looks good as well, provided every "UPDATE" statements is modified t=
his=20
way. Maybe a combination of specifying "default 0" for the timestamps and=
=20
using this method could help accomodate with every SQL variant ?

--=20
Michel Bouissou <mi...@bo...> OpenPGP ID 0xDDE8AC6E

Re: [Sqlgrey-users] MySQL: first_seen updated when it shouldn't

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

Michel Bouissou wrote:

>Le Jeudi 02 Juin 2005 10:28, Lionel Bouton a =E9crit :
> =20
>
>>MySQL really doesn't want to behave :-(
>>
>>If there isn't a workaround to disable this behaviour, then switching
>>first_seen and last_seen would be a quick fix.
>>   =20
>>
>
>Using MySQL-4.1.12, I found a workaround to this issue by executing the=20
>following commands :
>
>alter table from_awl modify first_seen timestamp default 0;
>
>alter table from_awl modify last_seen timestamp default CURRENT_TIMESTAM=
P on=20
>update CURRENT_TIMESTAMP;
>
>alter table domain_awl modify first_seen timestamp default 0;
>
>alter table domain_awl modify last_seen timestamp default CURRENT_TIMEST=
AMP on=20
>update CURRENT_TIMESTAMP;
>
>alter table connect modify first_seen timestamp default CURRENT_TIMESTAM=
P;
> =20
>

CURRENT_TIMESTAMP won't work with SQLite. If I need to change the table
creation, I'd better change the order of the columns.

Although I have found another way: if the UPDATE statements are modified
to look like:
UDPATE <awl> SET first_seen =3D first_seen, last_seen =3D <now> WHERE
<cond>. MySQL doesn't change first_seen (at least 4.1.12 doesn't). As
the 2 timestamp columns are forced, I can't imagine a way for any MySQL
version to change the values.

This is a quick fix, won't create ugly special cases and the database
system will probably optimize the query properly.

>Other subject: Lionel, have you had time to think again about the tarpit=
ting /=20
>throttling feature that I had suggested ? I still would like it ;-)
>
> =20
>

I'm not yet convinced it's a good idea. I'll sum up what I understand
about tarpitting below for you to point mistakes or missing points.

Tarpitting (refusing to create new connect entries if there are already
<n> existing entries with the same source, with refinements to disable
tarpitting when domain_awl holds entries for the source or enough
entries exist in from_awl) could help preventing pollutions of the
connect table from one single src.
This would have two main benefits :
B1/ faster DB access to the connect table (which probably is the most
used on common config under heavy Zombie pressure...) from SQLgrey.
B2/ easier analysis of the connect table by a curious sysadmin.

Here are the risks I'm worried about :
R1/ using tarpitting will also interfere with legit mails that don't
match AWLs (more retries).
R2/ SQLgrey will have to do another query on the connect table which
would most probably kill the performance advantage we get from a smaller
connect table and for the refinements, one query involving domain_awl
and if needed another involving from_awl.

My main problem for me was with R1 until the number of queries piled up
to avoid the fact that in some cases (big ISPs consolidating email
infrastructure, new smtp-out adresses, ...) you might very well
introduce huge delays or even bounce mails (which was a show stopper for
me). Now I think R2 will make the whole thing pointless.

Lionel.

Re: [Sqlgrey-users] MySQL: first_seen updated when it shouldn't

[Michel B. <mi...@bo...>]

Le Jeudi 02 Juin 2005 10:28, Lionel Bouton a =E9crit :
>
> MySQL really doesn't want to behave :-(
>
> If there isn't a workaround to disable this behaviour, then switching
> first_seen and last_seen would be a quick fix.

Using MySQL-4.1.12, I found a workaround to this issue by executing the=20
following commands :

alter table from_awl modify first_seen timestamp default 0;

alter table from_awl modify last_seen timestamp default CURRENT_TIMESTAMP=
 on=20
update CURRENT_TIMESTAMP;

alter table domain_awl modify first_seen timestamp default 0;

alter table domain_awl modify last_seen timestamp default CURRENT_TIMESTA=
MP on=20
update CURRENT_TIMESTAMP;

alter table connect modify first_seen timestamp default CURRENT_TIMESTAMP=
;


Then, as all my "first_seen" had unfortunaltely been destroyed during the=
 last=20
weeks, I set them to 2005-01-01 with the following statements:

update from_awl set first_seen =3D '2005-01-01 00:00:00', last_seen =3D l=
ast_seen=20
where first_seen =3D last_seen;

update domain_awl set first_seen =3D '2005-01-01 00:00:00', last_seen =3D=
=20
last_seen where first_seen =3D last_seen;


This being done, the create statements for the tables (as generated by a=20
"mysqldump") appears as follows:

CREATE TABLE `connect` (
  `sender_name` varchar(64) NOT NULL default '',
  `sender_domain` varchar(255) NOT NULL default '',
  `src` varchar(39) NOT NULL default '',
  `rcpt` varchar(255) NOT NULL default '',
  `first_seen` timestamp NOT NULL default CURRENT_TIMESTAMP,
  KEY `connect_idx` (`src`,`sender_domain`,`sender_name`),
  KEY `connect_fseen` (`first_seen`)
) ENGINE=3DInnoDB DEFAULT CHARSET=3Dlatin1;

CREATE TABLE `domain_awl` (
  `sender_domain` varchar(255) NOT NULL default '',
  `src` varchar(39) NOT NULL default '',
  `first_seen` timestamp NOT NULL default '0000-00-00 00:00:00',
  `last_seen` timestamp NOT NULL default CURRENT_TIMESTAMP on update=20
CURRENT_TIMESTAMP,
  PRIMARY KEY  (`src`,`sender_domain`),
  KEY `domain_awl_lseen` (`last_seen`)
) ENGINE=3DInnoDB DEFAULT CHARSET=3Dlatin1;

CREATE TABLE `from_awl` (
  `sender_name` varchar(64) NOT NULL default '',
  `sender_domain` varchar(255) NOT NULL default '',
  `src` varchar(39) NOT NULL default '',
  `first_seen` timestamp NOT NULL default '0000-00-00 00:00:00',
  `last_seen` timestamp NOT NULL default CURRENT_TIMESTAMP on update=20
CURRENT_TIMESTAMP,
  PRIMARY KEY  (`src`,`sender_domain`,`sender_name`),
  KEY `from_awl_lseen` (`last_seen`)
) ENGINE=3DInnoDB DEFAULT CHARSET=3Dlatin1;


Some notes (according to the documentation and my observations):

- For "first_seen" in domain_awl and from_awl, <<default '0000-00-00=20
00:00:00'>> is necessary, as if no default is specified, for the first=20
timestamp column in a table, MySQL would use by itself <<default=20
CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP>>

- For the "connect" table, "first_seen" column, <<default CURRENT_TIMESTA=
MP>>=20
_without_ <<on update CURRENT_TIMESTAMP>> will cause first_seen to defaul=
t to=20
CURRENT_TIMESTAMP if not specified when a row is inserted, but will not=20
automatically update when an update is performed.


Other subject: Lionel, have you had time to think again about the tarpitt=
ing /=20
throttling feature that I had suggested ? I still would like it ;-)

Cheers.

--=20
Michel Bouissou <mi...@bo...> OpenPGP ID 0xDDE8AC6E

Re: [Sqlgrey-users] Memory leak in 1.5.x

[Who K. <qui...@me...>]

Michel Bouissou wrote:

>I'm running MySQL-4.1.11.
>
>I have just compared on several machines on which SQLgrey has been
>installed the same day last week, some running MySQL 4.0.x and others
>running MySQL 4.1.x, all having installed SQLgrey 1.5.6 from scratch.
>
>The problem clearly appears ONLY on machines using MySQL 4.1, and not on
>machines running MySQL 4.0. Only machines running 4.1 show a DEFAULT
>CURRENT_TIMESTAMP for first_seen.
>
>The MySQL documentation also discusses differences in the way "timestamp"
>type fields work in 4.0 in 4.1, although this doesn't look neither very
>clear nor very logical to me, but I just had time to take a very quick
>glimpse at the documentation...
>
>  
>
Those of us still running a redhat/fedora "out-of-the-box" release are 
running a MySQL-3.23.58-x version.

[Sqlgrey-users] RELEASE: 1.5.8 (packaging fix)

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

Hi,

1.5.7 rpm build didn't work. 1.5.8 only provides working rpms, the code
didn't change from 1.5.7 (meaning people with 1.5.7 don't need to upgrade).

You can fin the usual tar.bz2 sources and src/noarch RPMs on sourceforge.

Lionel.

Re: [Sqlgrey-users] RELEASE: 1.5.7 (BUGFIXES)

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

Beast wrote:

> Lionel Bouton wrote:
>
>> Beast wrote:
>>
>>
>>> I have ver 1.5.5 installed, is it safe to just copy sqlgrey(.pl) to
>>> /usr/sbin ?
>>>
>>
>>
>> Yes, or better save your sqlgrey.conf file and "make install" from the
>> source directory (you'll have examples for the new parameters). If you
>
>
> What is the different between optinout and clients_*_whitelist?
>
>

## Whitelists

clients_*_whitelist goal is to allow known servers to bypass
greylisting. The distributed whitelists are used to:
- help performance for known MTA pools where each server can retry to
send the message,
- resolve cases where badly designed MTA don't retry a temporary failed
delivery.

You can add entries to clients_*_whitelist.local to:
- solve the same problems than above until I update the repository from
which SQLgrey users can fetch up-to-date whitelists,
- help performance on highly loaded sites by moving the full fqdns
(although using regexp or *.domain.nam is supported, it doesn't scale as
well) of your heavy senders : SQLgrey won't have to query the database.

## OPTIN/OUT

This is to control which users benefit from greylisting.
This can be used to:
- solve weird problems (like users willing to get spammed!),
- allow spamtraps to be spammed...,
- some admins might want postmaster, info, ... not being greylisted for
various reasons,
- make it a pay-per-user thingy on big systems where using greylisting
can mean serious money for the database system.

There's a README.OPTINOUT which details how to use it.

Lionel.

Re: [Sqlgrey-users] Memory leak in 1.5.x

[Michel B. <mi...@bo...>]

Michel Bouissou a =E9crit :
>
> The MySQL documentation also discusses differences in the way "timestam=
p"
> type fields work in 4.0 in 4.1, although this doesn't look neither very
> clear nor very logical to me, but I just had time to take a very quick
> glimpse at the documentation...

Related MySQL documentation:

11.3.1. The DATETIME, DATE, and TIMESTAMP Types
http://dev.mysql.com/doc/mysql/en/datetime.html

11.3.1.1. TIMESTAMP Properties Prior to MySQL 4.1
http://dev.mysql.com/doc/mysql/en/timestamp-pre-4-1.html

11.3.1.2. TIMESTAMP Properties as of MySQL 4.1
http://dev.mysql.com/doc/mysql/en/timestamp-4-1.html

--=20
Michel Bouissou <mi...@bo...> OpenPGP ID 0xDDE8AC6E

Re: [Sqlgrey-users] RELEASE: 1.5.7 (BUGFIXES)

[Beast <be...@i6...>]

Lionel Bouton wrote:
> Beast wrote:
> 
> 
>>I have ver 1.5.5 installed, is it safe to just copy sqlgrey(.pl) to
>>/usr/sbin ?
>>
> 
> 
> Yes, or better save your sqlgrey.conf file and "make install" from the
> source directory (you'll have examples for the new parameters). If you

What is the different between optinout and clients_*_whitelist?


-- 

--beast

Re: [Sqlgrey-users] Memory leak in 1.5.x

[Michel B. <mi...@bo...>]

Lionel Bouton a =E9crit :
>
> MySQL really doesn't want to behave :-(
>
> If there isn't a workaround to disable this behaviour, then switching
> first_seen and last_seen would be a quick fix.

Could be the easiest way.

> I'll have to be sure about this though. Michel, what is the exact MySQL
> version you are currently running ? I'll emerge the most close from
> Portage and do some tests with it.

I'm running MySQL-4.1.11.

I have just compared on several machines on which SQLgrey has been
installed the same day last week, some running MySQL 4.0.x and others
running MySQL 4.1.x, all having installed SQLgrey 1.5.6 from scratch.

The problem clearly appears ONLY on machines using MySQL 4.1, and not on
machines running MySQL 4.0. Only machines running 4.1 show a DEFAULT
CURRENT_TIMESTAMP for first_seen.

The MySQL documentation also discusses differences in the way "timestamp"
type fields work in 4.0 in 4.1, although this doesn't look neither very
clear nor very logical to me, but I just had time to take a very quick
glimpse at the documentation...

--=20
Michel Bouissou <mi...@bo...> OpenPGP ID 0xDDE8AC6E

Re: [Sqlgrey-users] RELEASE: 1.5.7 (BUGFIXES)

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

Beast wrote:

> I have ver 1.5.5 installed, is it safe to just copy sqlgrey(.pl) to
> /usr/sbin ?
>

Yes, or better save your sqlgrey.conf file and "make install" from the
source directory (you'll have examples for the new parameters). If you
need an init-script you can use "make rh-install" instead, it installs a
SysV init script, "make gentoo-install" installs a Gentoo init script.

That said, if you can, it's even safer to use the packaging system of
your distribution (easy uninstall, dependency resolution, ...).

On RPM-based distros, you can make an rpm by running "rpmbuild --ta
sqlgrey-1.5.7.tar.bz2", on Gentoo you can fetch an ebuild from sqlgrey's
sourceforge page (http://sqlgrey.sf.net/).

I'll start to provide rpms on sourceforge again shortly.

Lionel.

Re: [Sqlgrey-users] RELEASE: 1.5.7 (BUGFIXES)

[Beast <be...@i6...>]

Lionel Bouton wrote:
> 1.5.7 is on sourceforge.
> 
> - The memory leak should be fixed.
> - Migrating from earlier version will not lose first_seen values.
> - The config directory can be changed if needed.
> 
> 
I have ver 1.5.5 installed, is it safe to just copy sqlgrey(.pl) to 
/usr/sbin ?


-- 

--beast

Re: [Sqlgrey-users] Memory leak in 1.5.x

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

Who Knows wrote:

> Lionel Bouton wrote:
>
>> MySQL may change its behaviour from versions to versions, anyway, it
>> doesn't seem to like 'NOT NULL' for timestamps... :
>>
>> Here's from_awl freshly created with SQLgrey 1.5.7 :
>>
>> +---------------+---------------+------+-----+----------------+-------+
>> | Field         | Type          | Null | Key | Default        | Extra |
>> +---------------+---------------+------+-----+----------------+-------+
>> | sender_name   | varchar(64)   |      | PRI |                |       |
>> | sender_domain | varchar(255)  |      | PRI |                |       |
>> | src           | varchar(39)   |      | PRI |                |       |
>> | first_seen    | timestamp(14) | YES  |     | NULL           |       |
>> | last_seen     | timestamp(14) | YES  | MUL | 00000000000000 |       |
>> +---------------+---------------+------+-----+----------------+-------+
>>  
>>
> As I understand MySQL behavior the first field of type 'timestamp' is
> automatically updated updated to reflect the time of the last update
> to any field in the record.


MySQL really doesn't want to behave :-(

If there isn't a workaround to disable this behaviour, then switching
first_seen and last_seen would be a quick fix.

I'll have to be sure about this though. Michel, what is the exact MySQL
version you are currently running ? I'll emerge the most close from
Portage and do some tests with it.

Lionel

Re: [Sqlgrey-users] Memory leak in 1.5.x

[Who K. <qui...@me...>]

Lionel Bouton wrote:

>MySQL may change its behaviour from versions to versions, anyway, it
>doesn't seem to like 'NOT NULL' for timestamps... :
>
>Here's from_awl freshly created with SQLgrey 1.5.7 :
>
>+---------------+---------------+------+-----+----------------+-------+
>| Field         | Type          | Null | Key | Default        | Extra |
>+---------------+---------------+------+-----+----------------+-------+
>| sender_name   | varchar(64)   |      | PRI |                |       |
>| sender_domain | varchar(255)  |      | PRI |                |       |
>| src           | varchar(39)   |      | PRI |                |       |
>| first_seen    | timestamp(14) | YES  |     | NULL           |       |
>| last_seen     | timestamp(14) | YES  | MUL | 00000000000000 |       |
>+---------------+---------------+------+-----+----------------+-------+
>  
>
As I understand MySQL behavior the first field of type 'timestamp' is 
automatically updated updated to reflect the time of the last update to 
any field in the record.

Re: [Sqlgrey-users] Memory leak in 1.5.x

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

David Rees wrote:

>On 6/1/05, Michel Bouissou <mi...@bo...> wrote:
>  
>
>>Yes. I took a look at the code and can't explain either. And if I try to alter
>>the table to change/remove the defaults, MySQL accepts the command, but a
>>following "desc" shows that it is actually purely ignored... Go figure.
>>    
>>
>
>Gotta love the way MySQL tends to do that...
>
>  
>

MySQL may change its behaviour from versions to versions, anyway, it
doesn't seem to like 'NOT NULL' for timestamps... :

Here's from_awl freshly created with SQLgrey 1.5.7 :

+---------------+---------------+------+-----+----------------+-------+
| Field         | Type          | Null | Key | Default        | Extra |
+---------------+---------------+------+-----+----------------+-------+
| sender_name   | varchar(64)   |      | PRI |                |       |
| sender_domain | varchar(255)  |      | PRI |                |       |
| src           | varchar(39)   |      | PRI |                |       |
| first_seen    | timestamp(14) | YES  |     | NULL           |       |
| last_seen     | timestamp(14) | YES  | MUL | 00000000000000 |       |
+---------------+---------------+------+-----+----------------+-------+

Michel, you can force SQLgrey to create new tables from scratch and
populate them with your current data with SQLgrey 1.5.7:
- Backup your database!
- Update the 'config' table so that the stored "version" is '2'
(currently it should be '3' on your config).
- Restart with SQLgrey 1.5.7.

The 2 -> 3 upgrade process doesn't do anything fancy with the data (it
only fetches the content in the old tables and put them in new ones with
enough room for IPv6 addresses) so it should be pretty safe.
Please post "desc from_awl;" result again if it changes.

MySQL here is 4.0.24, could make a difference...

Lionel

[Sqlgrey-users] RELEASE: 1.5.7 (BUGFIXES)

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

1.5.7 is on sourceforge.

- The memory leak should be fixed.
- Migrating from earlier version will not lose first_seen values.
- The config directory can be changed if needed.


There's a preliminary log parser, it doesn't do anything exciting and
wasn't meant to be released in this shape but there were urgent bugfixes
to release. Anyway you can check it out.

Lionel.

Re: [Sqlgrey-users] Memory leak in 1.5.x

[David R. <dr...@gm...>]

On 6/1/05, Michel Bouissou <mi...@bo...> wrote:
>=20
> Yes. I took a look at the code and can't explain either. And if I try to =
alter
> the table to change/remove the defaults, MySQL accepts the command, but a
> following "desc" shows that it is actually purely ignored... Go figure.

Gotta love the way MySQL tends to do that...

-Dave

Re: [Sqlgrey-users] Memory leak in 1.5.x

[Michel B. <mi...@bo...>]

Le Mercredi 01 Juin 2005 20:00, Lionel Bouton a =E9crit :
>
> But I can't explain how "current_timestamp" got there. Especially since
> the "create_from_awl" method uses the very same declarations for
> first_seen and last_seen (and from the CVS, it always did). The fact
> that Null is "YES" for these 2 tables is suspicious too : both column
> are created "NOT NULL" in SQLgrey !

Yes. I took a look at the code and can't explain either. And if I try to =
alter=20
the table to change/remove the defaults, MySQL accepts the command, but a=
=20
following "desc" shows that it is actually purely ignored... Go figure.

--=20
Michel Bouissou <mi...@bo...> OpenPGP ID 0xDDE8AC6E

Re: [Sqlgrey-users] Memory leak in 1.5.x

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

Michel Bouissou wrote:

>Le Mercredi 01 Juin 2005 18:17, Lionel Bouton a =E9crit :
> =20
>
>>I checked the code and didn't spot any obvious mistake (no UPDATE
>>involves first_seen).
>>   =20
>>
>
>Possibly an unwanted consequence of this column definition that may beha=
ve=20
>differently in MySQL compared to PostgreSQL ?
>
>mysql> desc from_awl;
>+---------------+--------------+------+-----+---------------------+-----=
--+
>| Field         | Type         | Null | Key | Default             | Extr=
a |
>+---------------+--------------+------+-----+---------------------+-----=
--+
>| sender_name   | varchar(64)  |      | PRI |                     |     =
  |
>| sender_domain | varchar(255) |      | PRI |                     |     =
  |
>| src           | varchar(39)  |      | PRI |                     |     =
  |
>| first_seen    | timestamp    | YES  |     | CURRENT_TIMESTAMP   |     =
  |
>| last_seen     | timestamp    | YES  | MUL | 0000-00-00 00:00:00 |     =
  |
>+---------------+--------------+------+-----+---------------------+-----=
--+
>
>Do you think this may cause first_seen to be automagically set to=20
>CURRENT_TIMESTAMP each time a record is updated without specifying anyth=
ing=20
>about first_seen     ?
> =20
>

Huh-hoh...

In fact the conversion bug is in 1.5.6, not 1.5.5 (too much time since I
did a release apparently).

But I can't explain how "current_timestamp" got there. Especially since
the "create_from_awl" method uses the very same declarations for
first_seen and last_seen (and from the CVS, it always did). The fact
that Null is "YES" for these 2 tables is suspicious too : both column
are created "NOT NULL" in SQLgrey !

I'll run some tests on MySQL later.

Lionel.

Re: [Sqlgrey-users] Memory leak in 1.5.x

[Michel B. <mi...@bo...>]

Le Mercredi 01 Juin 2005 18:17, Lionel Bouton a =E9crit :
>
> I checked the code and didn't spot any obvious mistake (no UPDATE
> involves first_seen).

Possibly an unwanted consequence of this column definition that may behav=
e=20
differently in MySQL compared to PostgreSQL ?

mysql> desc from_awl;
+---------------+--------------+------+-----+---------------------+------=
-+
| Field         | Type         | Null | Key | Default             | Extra=
 |
+---------------+--------------+------+-----+---------------------+------=
-+
| sender_name   | varchar(64)  |      | PRI |                     |      =
 |
| sender_domain | varchar(255) |      | PRI |                     |      =
 |
| src           | varchar(39)  |      | PRI |                     |      =
 |
| first_seen    | timestamp    | YES  |     | CURRENT_TIMESTAMP   |      =
 |
| last_seen     | timestamp    | YES  | MUL | 0000-00-00 00:00:00 |      =
 |
+---------------+--------------+------+-----+---------------------+------=
-+

Do you think this may cause first_seen to be automagically set to=20
CURRENT_TIMESTAMP each time a record is updated without specifying anythi=
ng=20
about first_seen     ?

--=20
Michel Bouissou <mi...@bo...> OpenPGP ID 0xDDE8AC6E

Re: [Sqlgrey-users] Memory leak in 1.5.x

[Michel B. <mi...@bo...>]

Le Mercredi 01 Juin 2005 18:17, Lionel Bouton a =E9crit :
> >
> >SQLgrey 1.5.6 + MySQL: In both AWLs, everytime last_seen is updated,
> >first_seen is updated as well, where it should not.
>
> This one obviously got through though :-)
>
> I checked the code and didn't spot any obvious mistake (no UPDATE
> involves first_seen).
>
> *But* there was a bug in the database migration code from version 2 to =
3
> in 1.5.5 IIRC : it resets the first_seen values to last_seen (drag and
> drop of code from the earlier migration where first_seen didn't exit
> yet). It is solved in 1.5.6 IIRC.

Weird enough. I have the positive certitude that the first_seen column ge=
ts=20
updated everytime the last_seen column is, on my system.

An example that is clear enouch about this ;-) :

| sqlgrey-users-admin | lists.sourceforge.net | 66.35.250 | 2005-06-01=20
18:29:00 | 2005-06-01 18:29:00 |

Cheers.

--=20
Michel Bouissou <mi...@bo...> OpenPGP ID 0xDDE8AC6E

Re: [Sqlgrey-users] Memory leak in 1.5.x

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

Michel Bouissou wrote:

>Le Mercredi 01 Juin 2005 16:59, Lionel Bouton a =E9crit :
> =20
>
>>Hi, here's a bounce I got as list admin (I doubt it ever reached the
>>list as I've no trace of a delivery attempt from sourceforge in my own
>>server logs).
>>   =20
>>
>
>I've received this message this morning however...
>
>BTW, I didn't get any answer about my bug report of last saturday:
>
><<<
>SQLgrey 1.5.6 + MySQL: In both AWLs, everytime last_seen is updated,=20
>first_seen is updated as well, where it should not.
> =20
>
>
>Did you get it ?
> =20
>

No !

This one obviously got through though :-)

I checked the code and didn't spot any obvious mistake (no UPDATE
involves first_seen).

*But* there was a bug in the database migration code from version 2 to 3
in 1.5.5 IIRC : it resets the first_seen values to last_seen (drag and
drop of code from the earlier migration where first_seen didn't exit
yet). It is solved in 1.5.6 IIRC.

Lionel.

Re: [Sqlgrey-users] Memory leak in 1.5.x

[Michel B. <mi...@bo...>]

Le Mercredi 01 Juin 2005 16:59, Lionel Bouton a =E9crit :
>
> Hi, here's a bounce I got as list admin (I doubt it ever reached the
> list as I've no trace of a delivery attempt from sourceforge in my own
> server logs).

I've received this message this morning however...

BTW, I didn't get any answer about my bug report of last saturday:

<<<
SQLgrey 1.5.6 + MySQL: In both AWLs, everytime last_seen is updated,=20
first_seen is updated as well, where it should not.
>>>

Did you get it ?

--=20
Michel Bouissou <mi...@bo...> OpenPGP ID 0xDDE8AC6E

[Sqlgrey-users] Memory leak in 1.5.x

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

Hi, here's a bounce I got as list admin (I doubt it ever reached the
list as I've no trace of a delivery attempt from sourceforge in my own
server logs).


> Dear Friends,
>
> SQLgrey works fine for me except one thing. The memory usage of the
> process
> keep increasing and never stops. I doubt if there is some memory leak
> problem
> within the program or even the perl program.
>
> The situation is once the process begins, the memory usage which can
> be monitored
> by ps or top keep increasing and never stops until it reachs an OS
> limit and the
> process will be terminated.
>
> I would like to know if there exist some garbage collection machenism
> in the sqlgrey or perl code?
>
> The OS is freebsd 4.11
>    perl is made from /usr/ports of freebsd and the version is 5.8.6
>    sqlgrey is 1.5.6
>    the config of sqlgrey is almost the default.
>    the backend database used is postgresql 7.4.7
>
> sqlgrey=# SELECT count(*) from connect ;
> count -------
> 95284
> (1 row)
>
> ps axuww | grep sqlgrey
> sqlgrey    33263  0.0  4.5 248452 246964  pa  S+    3:17PM   0:12.56
> /usr/bin/perl -w /usr/local/sbin/sqlgrey (perl5.8.6)
>
> Any suggestion is welcome.
>
> David Wang


After careful examination I believe there is indeed a leak in SQLgrey.
I've not much mail coming on my server so I didn't spot it right away
(and my employer only uses SQLgrey since yesterday when I upgraded to
Postfix 2.1...).

The leak is quite hard to spot: I use DBI "prepare_cached" to help
performance in most cases. When I changed the timestamp related
computations during 1.5.x I didn't spot an obvious problem: many queries
now can't reuse the prepared statements since the timestamps are now
statics instead of dynamically computed in the SQL queries so they
change on each new message.

I believe that DBI is simply leaking prepared statements. Expect a 1.5.7
update with a fix shortly today.

Lionel

[Sqlgrey-users] Memory leak of sqlgrey process

[<cc...@nt...>]

Dear Friends,

SQLgrey works fine for me except one thing. The memory usage of the process
keep increasing and never stops. I doubt if there is some memory leak problem
within the program or even the perl program.

The situation is once the process begins, the memory usage which can be 
monitored
by ps or top keep increasing and never stops until it reachs an OS 
limit and the
process will be terminated.

I would like to know if there exist some garbage collection machenism 
in the sqlgrey or perl code?

The OS is freebsd 4.11
    perl is made from /usr/ports of freebsd and the version is 5.8.6
    sqlgrey is 1.5.6
    the config of sqlgrey is almost the default.
    the backend database used is postgresql 7.4.7

sqlgrey=# SELECT count(*) from connect ;
count -------
95284
(1 row)

ps axuww | grep sqlgrey
sqlgrey    33263  0.0  4.5 248452 246964  pa  S+    3:17PM   0:12.56 
/usr/bin/perl -w /usr/local/sbin/sqlgrey (perl5.8.6)

Any suggestion is welcome.

David Wang