[asterisk-bugs] [JIRA] (ASTERISK-26146) INSERT of record fails due to empty string instead of proper timestamp for $answer variable when disposition = 'NO ANSWER'

[JOJ (JIRA)]

JOJ created ASTERISK-26146:
------------------------------

             Summary: INSERT of record fails due to empty string instead of proper timestamp for $answer variable when disposition = 'NO ANSWER'
                 Key: ASTERISK-26146
                 URL: https://issues.asterisk.org/jira/browse/ASTERISK-26146
             Project: Asterisk
          Issue Type: Bug
      Security Level: None
          Components: CDR/cdr_pgsql
    Affects Versions: 13.1.0
         Environment: Ubuntu 16.04 Xenial, Asterisk 13.1 (latest release available in the 16.04 repos), Postgres 9.5.

{code}
dpkg -l | grep asterisk:

ii  asterisk                         1:13.1.0~dfsg-1.1ubuntu4                 amd64        Open Source Private Branch Exchange (PBX)
ii  asterisk-config                  1:13.1.0~dfsg-1.1ubuntu4                 all          Configuration files for Asterisk
ii  asterisk-core-sounds-en          1.4.22-1                                 all          asterisk PBX sound files - US English
ii  asterisk-core-sounds-en-gsm      1.4.22-1                                 all          asterisk PBX sound files - en-us/gsm
ii  asterisk-dahdi                   1:13.1.0~dfsg-1.1ubuntu4                 amd64        DAHDI devices support for the Asterisk PBX
ii  asterisk-modules                 1:13.1.0~dfsg-1.1ubuntu4                 amd64        loadable modules for the Asterisk PBX
ii  asterisk-moh-opsound-gsm         2.03-1                                   all          asterisk extra sound files - English/gsm
ii  asterisk-mp3                     1:13.1.0~dfsg-1.1ubuntu4                 amd64        MP3 playback support for the Asterisk PBX
ii  asterisk-voicemail               1:13.1.0~dfsg-1.1ubuntu4                 amd64        simple voicemail support for the Asterisk PBX

dpkg -l | grep postgres:

ii  postgresql                       9.5+173                                  all          object-relational SQL database (supported version)
ii  postgresql-9.5                   9.5.3-0ubuntu0.16.04                     amd64        object-relational SQL database, version 9.5 server
ii  postgresql-client-9.5            9.5.3-0ubuntu0.16.04                     amd64        front-end programs for PostgreSQL 9.5
ii  postgresql-client-common         173                                      all          manager for multiple PostgreSQL client versions
ii  postgresql-common                173                                      all          PostgreSQL database-cluster manager
ii  postgresql-contrib-9.5           9.5.3-0ubuntu0.16.04                     amd64        additional facilities for PostgreSQL
{code}
            Reporter: JOJ
            Severity: Minor


While porting our configuration from 1.8 to 13.1, I noticed the following behaviour change in cdr_pgsql which I believe is a regression:

We were using the following CDR table format with 1.8. Whenever a call was entered with the 'NO ANSWER' disposition, the answer column got set to a timestamp equivalent to 0 epoch (1970-yadayada):

{code}
CREATE TABLE cdr ( 
        id serial NOT NULL,
        start timestamp with time zone NOT NULL ,
        answer timestamp with time zone NOT NULL , 
        "end" timestamp with time zone NOT NULL , 
        clid varchar (80) NOT NULL , 
        src varchar (80) NOT NULL , 
        dst varchar (80) NOT NULL , 
        dcontext varchar (80) NOT NULL , 
        channel varchar (80) NOT NULL , 
        dstchannel varchar (80) NOT NULL , 
        lastapp varchar (80) NOT NULL , 
        lastdata varchar (80) NOT NULL , 
        duration int NOT NULL , 
        billsec int NOT NULL , 
        disposition varchar (45) NOT NULL , 
        amaflags int NOT NULL , 
        accountcode varchar (20) NOT NULL , 
        uniqueid varchar (150) NOT NULL , 
        userfield varchar (255) NOT NULL ,
        peeraccount varchar(20) NOT NULL ,
        linkedid varchar(150) NOT NULL ,
        sequence int NOT NULL
);
{code}
When attempting to use the same data format on 13.1, inserting calls with the NO ANSWER dispostion fails hard to the effect that the CDR gets dropped, the reason being taht instead of a valid timestamp, cdr_pgsql tries to pass an empty string:

{code}
[Jun 23 03:14:30] ERROR[27597] cdr_pgsql.c: Failed to insert call detail record into database!
 985 [Jun 23 03:14:30] ERROR[27597] cdr_pgsql.c: Reason: ERROR:  invalid input syntax for type timestamp with time zone: ""
 986 LINE 1: ...nkedid","sequence") VALUES ('2016-06-23 03:14:06','','2016-0...
{code}

Everything else continues to work properly. I have for now worked around this issue by dropping the answer column and deriving that timestamp using start + (duration-billsec) when applicable.

I'm reporting this since the passing of the empty string is not compliant with postgres's strict type requirements.

I should also note that this occurred when using chan_sip.




--
This message was sent by Atlassian JIRA
(v6.2#6252)