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