Caveat: schema differences in Acct(Start|Stop)Time
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@restena.lu Tel.: +352 424409-1 http://www.restena.lu Fax: +352 422473
Hi,
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 we've already has a discussion last week about those values being 'illegal' according to the MySQL datetime documentation. The DATETIME type is used when you need values that contain both date and time information. MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. however, just to run it in, if you enter an illegal entry (eg 123454324352) then that value gets converted to..... yes, wait for it.... 0000-00-00 00:00:00 hurrah! the docs clearly state this case too. I would say we get a little historical and set the default date to 1970-01-01 00:00:00 alan
On Fri 31 Aug 2007, Stefan Winter wrote:
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
Yep. For these and other reasons I change the Postgres schema to default to NULL many years ago :-) I agree that MySQL should change also, but at the time I go pushback for making backwards incompatible schema changes which some third party billing systems seem to rely on. I think the 2.0 release is the correct time to standardise all of this though.. Cheers -- Peter Nixon http://peternixon.net/
Peter Nixon wrote:
I agree that MySQL should change also, but at the time I go pushback for making backwards incompatible schema changes which some third party billing systems seem to rely on. I think the 2.0 release is the correct time to standardise all of this though..
Yes, please. Alan DeKok.
participants (4)
-
A.L.M.Buxey＠lboro.ac.uk -
Alan DeKok -
Peter Nixon -
Stefan Winter