[Home]

Summary:ASTERISK-15475: [patch] Double fields in SQL query
Reporter:Yaroslav (yarik_rad)Labels:
Date Opened:2010-01-20 04:48:08.000-0600Date Closed:2011-04-14 08:24:41
Priority:MinorRegression?No
Status:Closed/CompleteComponents:CDR/cdr_pgsql
Versions:Frequency of
Occurrence
Related
Issues:
Environment:Attachments:( 0) patch.duplicatefields.issue16655.diff
Description:Try to connect Asterisk server with PostgreSQl database I've double fields in SQL quere send to datbase.

Here is log of database server:

COMMAND:  INSERT INTO cdr ("calldate","clid","src","dst","dcontext","channel","dstchannel","lastapp","lastdata","duration","billsec","disposition","amaflags",
"uniqueid","calldate","clid","src","dst","dcontext","channel","dstchannel","lastapp","lastdata","duration","billsec","disposition","amaflags","uniqueid","calldate",
"clid","src","dst","dcontext","channel","dstchannel","lastapp","lastdata","duration","billsec","disposition","amaflags","accountcode","uniqueid","userfield",
"calldate","clid","src","dst","dcontext","channel","dstchannel","lastapp","lastdata","duration","billsec","disposition","amaflags","accountcode","uniqueid","userfield")
VALUES ('2010-01-20 13:43:16','"User714" <714>','714','9060','kvartira','SIP/714-00000057','SIP/pstn-00000058','Dial','SIP/999060@pstn',13,2,'ANSWERED',3,'1263984196.87',
'2010-01-20 13:43:16','"User714" <714>','714','9060','kvartira','SIP/714-00000057','SIP/pstn-00000058','Dial','SIP/999060@pstn',13,2,'ANSWERED',3,'1263984196.87',
'2010-01-20 13:43:16','"User714" <714>','714','9060','kvartira','SIP/714-00000057','SIP/pstn-00000058','Dial','SIP/999060@pstn',13,2,'ANSWERED',3,'','1263984196.87','',
'2010-01-20 13:43:16','"User714" <714>','714','9060','kvartira','SIP/714-00000057','SIP/pstn-00000058','Dial','SIP/999060@pstn',13,2,'ANSWERED',3,'','1263984196.87','')


Comments:By: Leif Madsen (lmadsen) 2010-01-21 10:40:31.000-0600

You have not provided enough information to reproduce this problem. Please provide all the necessary information and steps required to reproduce.
By: Yaroslav (yarik_rad) 2010-01-23 06:32:27.000-0600

I'm trying put CDR data information into PostgreSQL database. I use cdr_pgsql.
So I got this log information in asterisk:
[Jan 23 15:28:34] ERROR[28400]: cdr_pgsql.c:309 pgsql_log: cdr_pgsql: Failed to insert call detail record into database!
[Jan 23 15:28:34] ERROR[28400]: cdr_pgsql.c:310 pgsql_log: cdr_pgsql: Reason: ERROR:  column "calldate" specified more than once
LINE 1: ...","amaflags","accountcode","uniqueid","userfield","calldate"...
                                                            ^

[Jan 23 15:28:34] ERROR[28400]: cdr_pgsql.c:311 pgsql_log: cdr_pgsql: Connection may have been lost... attempting to reconnect.
[Jan 23 15:28:34] ERROR[28400]: cdr_pgsql.c:314 pgsql_log: cdr_pgsql: Connection reestablished.
[Jan 23 15:28:34] ERROR[28400]: cdr_pgsql.c:320 pgsql_log: cdr_pgsql: HARD ERROR!  Attempted reconnection failed.  DROPPING CALL RECORD!
[Jan 23 15:28:34] ERROR[28400]: cdr_pgsql.c:321 pgsql_log: cdr_pgsql: Reason: ERROR:  column "calldate" specified more than once
LINE 1: ...","amaflags","accountcode","uniqueid","userfield","calldate"...
                                                            ^
In database log file I got this:
ERROR:  column "calldate" specified more than once at character 186
STATEMENT:  INSERT INTO cdr ("calldate","clid","src","dst","dcontext","channel",
"dstchannel","lastapp","lastdata","duration","billsec","disposition","amaflags",
"accountcode","uniqueid","userfield","calldate","clid","src","dst","dcontext",
"channel","dstchannel","lastapp","lastdata","duration","billsec",
"disposition","amaflags","uniqueid") VALUES ('2010-01-23 15:28:20','"User714" <714>','714','9060','kvartira',
'SIP/714-0000000f','SIP/pstn-00000010','Dial','SIP/999060@pstn',14,6,'ANSWERED',3,'','1264249700.15','
','2010-01-23 15:28:20','"User714" <714>','714','9060','kvartira','SIP/714-0000000f','SIP/pstn-00000010',
'Dial','SIP/999060@pstn',14,6,'ANSWERED',3,'1264249700.15')

PostgreSQL is 8.3.

cdr_pgsql.conf :
[global]
hostname=127.0.0.1
port=5432
dbname=asteriskDB
password=XXXXXX
user=asterisk1
table=cdr               ;SQL table where CDRs will be inserted
spool=pgsql.spool


By: Miguel Molina (coolmig) 2010-03-16 12:08:45

Same here, asterisk 1.6.0.26 connecting to PostgreSQL 8.4.

What I find is that fields are duplicate when the module loads, that's why the insert queryf fails:

*CLI> module load cdr_pgsql.so
 == Parsing '/etc/asterisk/cdr_pgsql.conf':   == Found
*CLI>        > Found column 'calldate' of type 'timestamptz'
      > Found column 'calldate' of type 'timestamptz'
      > Found column 'clid' of type 'varchar'
      > Found column 'clid' of type 'varchar'
      > Found column 'src' of type 'varchar'
      > Found column 'src' of type 'varchar'
      > Found column 'dst' of type 'varchar'
      > Found column 'dst' of type 'varchar'
      > Found column 'dcontext' of type 'varchar'
      > Found column 'dcontext' of type 'varchar'
      > Found column 'channel' of type 'varchar'
      > Found column 'channel' of type 'varchar'
      > Found column 'dstchannel' of type 'varchar'
      > Found column 'dstchannel' of type 'varchar'
      > Found column 'lastapp' of type 'varchar'
      > Found column 'lastapp' of type 'varchar'
      > Found column 'lastdata' of type 'varchar'
      > Found column 'lastdata' of type 'varchar'
      > Found column 'duration' of type 'int8'
      > Found column 'duration' of type 'int8'
      > Found column 'billsec' of type 'int8'
      > Found column 'billsec' of type 'int8'
      > Found column 'disposition' of type 'varchar'
      > Found column 'disposition' of type 'varchar'
      > Found column 'amaflags' of type 'int8'
      > Found column 'amaflags' of type 'int8'
      > Found column 'accountcode' of type 'varchar'
      > Found column 'accountcode' of type 'varchar'
      > Found column 'uniqueid' of type 'varchar'
      > Found column 'uniqueid' of type 'varchar'
      > Found column 'userfield' of type 'varchar'
      > Found column 'userfield' of type 'varchar'
Loaded cdr_pgsql.so => (PostgreSQL CDR Backend)


Finding a solution...
By: Miguel Molina (coolmig) 2010-03-16 12:20:01

Found the cause, I have two different schemas with the table cdr (same name). The query that brings the columns is this one, according to cdr_pgsql.c source code:

select a.attname, t.typname, a.attlen, a.attnotnull, d.adsrc from pg_class c, pg_type t, pg_attribute a left outer join pg_attrdef d on a.atthasdef and d.adrelid = a.attrelid and d.adnum = a.attnum where c.oid = a.attrelid and a.atttypid = t.oid and (a.attnum > 0) and c.relname = 'cdr' order by c.relname, attnum;

If I change the name of the table on the other schema, the fields are brought ok.

One workaround is to have different names for the CDR table, the solution would be fixing the query to seek only in the schema where we have permission...
By: Miguel Molina (coolmig) 2010-03-16 13:01:54

I modified the query to bring only the fields that are owned by the user with what we are connecting to the database. I tested the query, and it brings the correct fields (non duplicate) from the correct schema and table where the user is the owner.

I've created a patch for the cdr_pgsql module with the changes. Feel free to review the patch, it works flawlessly for me and is already inserting records! But I did not test the case where we are not the owner of the table, it surely would bring no fields.

Hope it solves the issue once for all!
By: Miguel Molina (coolmig) 2010-04-09 17:12:22

I was testing this with 1.6.2.7-rc1, and I found that it works fine, the query involved (in line 515 of cdr_pgsql.c) is different but takes both the table and schema into account. So this issue dissappears if you upgrade to 1.6.2.X with no needs of patches like the one I made, it looks like someone else already did the job on this version.

I tested it works fine with two asterisk instances pointing to a table named "cdr" on different schemas in the same PostgreSQL database:

asterisk1.cdr
asterisk2.cdr

Records are correctly inserted in the corresponding schema with no errors.
By: Leif Madsen (lmadsen) 2010-04-15 10:40:58

OK, I'm going to close this issue then. Because 1.6.0 and 1.6.1 are nearly their maintenance release period (next month) there is very little chance this will get reviewed and committed prior to the expiry of maintenance on those branches.

Since this works with 1.6.2.x, then we'll close this issue and state that people who need this functionality should move to 1.6.2.x

Thanks for the update!