Ticket Navigation

Ticket #33 (closed defect: fixed)

Opened 1 year ago

Last modified 1 year ago

'0000-00-00 00:00:00' default date causing SQL errors

Reported by:	stavros	Assigned to:	areski
Priority:	major	Milestone:
Component:	General	Version:
Keywords:	SQL Postgres	Cc:	stavros@phreaker.net

Description

The schema for both MySQL and Postgres use a default value of '0000-00-00 00:00:00' for all timestamp fields.

Sadly matching this default value is throwing runtime errors in at least Postgres 8.0.13, as in this example trying to look up the destination for a DID call:

a2b=> SELECT cc_did.id, cc_did_destination.id, billingtype, tariff, destination, voip_call, username FROM cc_did, cc_did_destination, cc_card WHERE id_cc_did=cc_did.id and cc_card.id=id_cc_card and cc_did_destination.activated=1 and cc_did.activated=1 and did='44xxxxxxxxxx' AND cc_did.startingdate<= CURRENT_TIMESTAMP AND (cc_did.expirationdate > CURRENT_TIMESTAMP OR cc_did.expirationdate IS NULL OR cc_did.expirationdate = '0000-00-00 00:00:00') ORDER BY priority ASC;

ERROR: date/time field value out of range: "0000-00-00 00:00:00"

HINT: Perhaps you need a different "datestyle" setting.

Trying various 'datestyle' settings didn't make Postgres any happier.

May I suggest changing the DEFAULT for all these fields to be '0000-01-01 00:00:00', which can be parsed as a valid date.

Attachments

bad-dates.diff (2.4 kB) - added by stavros on 08/06/07 09:36:19.

Change History

06/19/07 11:14:36 changed by stavros

summary changed from a2b=> SELECT cc_did.id, cc_did_destination.id, billingtype, tariff, destination, voip_call, username FROM cc_did, cc_did_destination, cc_card WHERE id_cc_did=cc_did.id and cc_card.id=id_cc_card and cc_did_destination.activated=1 and cc_did.activated=1 and did='442030263892' AND cc_did.startingdate<= CURRENT_TIMESTAMP AND (cc_did.expirationdate > CURRENT_TIMESTAMP OR cc_did.expirationdate IS NULL OR cc_did.expirationdate = '0000-00-00 00:00:00') ORDER BY priority ASC; ERROR: date/time field value out of range: "0000-00-00 00:00:00" '0000-00-00 00:00:00' default date causing SQL errors to '0000-00-00 00:00:00' default date throwing SQL errors at runtime ERROR: date/time field value out of range: "0000-00-00 00:00:00" '0000-00-00 00:00:00' default date causing SQL errors.

07/19/07 08:56:19 changed by stavros

cc set to stavros.

On further investigation I'd no longer recommend changing the default to what I previously suggested, as it's not a valid MySQL timestamp. According to the MySQL 5.0 documentation '1970-01-01 00:00:00' should be a valid timestamp that might reasonably be used for initialised dates in both Postgres and MySQL. My install of 5.0.44 barfs on it, although '1970-01-01 02:00:00' works.

After a brief review of the code and a little testing it seems that any entries in the cc_did table should always have a expirationdate set to creationdate + 25 years, unless another value was specified. Therefore I think the simplest way to regain Postgres compatibility for DIDs in the AGI would be to remove the test:

Index: branches/1.3/A2Billing_AGI/a2billing.php
===================================================================
--- branches/1.3/A2Billing_AGI/a2billing.php    (revision 86)
+++ branches/1.3/A2Billing_AGI/a2billing.php    (working copy)
@@ -345,8 +345,12 @@
                $QUERY =  "SELECT cc_did.id, cc_did_destination.id, billingtype, tariff, destination,  voip_call, username".
                        " FROM cc_did, cc_did_destination,  cc_card ".
                        " WHERE id_cc_did=cc_did.id and cc_card.id=id_cc_card and cc_did_destination.activated=1  and cc_did.activated=1 and did='$mydnid' ".
-                       " AND cc_did.startingdate<= CURRENT_TIMESTAMP AND (cc_did.expirationdate > CURRENT_TIMESTAMP OR cc_did.expirationdate IS NULL OR ".
-                       " cc_did.expirationdate = '0000-00-00 00:00:00') ORDER BY priority ASC";
+                       " AND cc_did.startingdate<= CURRENT_TIMESTAMP AND (cc_did.expirationdate > CURRENT_TIMESTAMP OR cc_did.expirationdate IS NULL";
+               if ($A2B->config["database"]['dbtype'] != "postgres"){
+                       // MYSQL
+                       $QUERY .= " OR cc_did.expirationdate = '0000-00-00 00:00:00'";
+               }
+               $QUERY .= ") ORDER BY priority ASC";

                $A2B -> debug( VERBOSE | WRITELOG, $agi, __FILE__, __LINE__, $QUERY);
                $result = $A2B -> instance_table -> SQLExec ($A2B->DBHandle, $QUERY);

What are your thoughts on this? Does someone know a better way to remain database agnostic?

If we're permitted to change the database schema during the v1.3 lifetime we could add default values for Postgres and test for them both here and in various places in the Customer+Admin UI code that currently match only '0000-00-00 00:00:00'.

08/02/07 11:31:32 changed by HandbagsforU

cc changed from stavros to Ny.
keywords set to MESSAGE.
summary changed from '0000-00-00 00:00:00' default date causing SQL errors to 0000-00-00 00:00:00' default date causing SQL errors.

08/03/07 12:17:30 changed by sapalo

cc changed from stavros to Ny.
keywords changed from SQL Postgres to MESSAGE.
summary changed from '0000-00-00 00:00:00' default date causing SQL errors to 0000-00-00 00:00:00' default date causing SQL errors.