Caveat: schema differences in Acct(Start|Stop)Time

[Stefan Winter]

Hi,

(this goes into a Wiki page as well)

Today I fell over some caveat when it comes to handling AcctStopTime in 
databases. In mysql, the schema defines

  acctstarttime datetime NOT NULL default '0000-00-00 00:00:00',
  acctstoptime datetime NOT NULL default '0000-00-00 00:00:00',

and the accounting queries set a literal '0' on starts.

postgresql is different though:

        AcctStartTime           TIMESTAMP with time zone,
        AcctStopTime            TIMESTAMP with time zone,

and doesn't set anything on starts, which makes the content a NULL.

The difference comes into play for example when you want to delete old 
records: a

DELETE * from radacct WHERE AcctStopTime < $YOUR_THRESHOLD_DATE

will

a) on postgresql only delete closed sessions older than your threshold age
b) on mysql will additionally DELETE ALL YOUR OPEN SESSIONS (because they have
   AcctStopTime = 0000-00-00 00:00 and that is smaller than your threshold
   date)

I strongly suggest updating CVS head (+will there be a 1.1.8?) with a better 
schema for mysql (i.e. follow postgresql's NULL). I would do it myself right 
now, but I'm off for holidays in a few hours. So if someone feels like doing 
it, there you go.
If not: I'll put a Post-It on my computer :-)

Greetings,

Stefan Winter

-- 
Stefan WINTER

Stiftung RESTENA - Réseau Téléinformatique de l'Education Nationale et de 
la Recherche
Ingenieur Forschung & Entwicklung

6, rue Richard Coudenhove-Kalergi
L-1359 Luxembourg
E-Mail: stefan.winter at restena.lu     Tel.:     +352 424409-1
http://www.restena.lu                Fax:      +352 422473
-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 189 bytes
Desc: This is a digitally signed message part.
URL: <http://lists.freeradius.org/pipermail/freeradius-users/attachments/20070831/aa391262/attachment.pgp>