[Home]

Summary:ASTERISK-22826: cdr_adaptive_odbc SQL execute error with PostgreSQL
Reporter:Anthony Messina (amessina)Labels:
Date Opened:2013-11-05 04:57:17.000-0600Date Closed:2014-01-15 17:03:51.000-0600
Priority:MinorRegression?No
Status:Closed/CompleteComponents:CDR/cdr_adaptive_odbc Resources/res_odbc
Versions:Frequency of
Occurrence
Related
Issues:
is the original version of this clone:ASTERISK-12275 [patch] Column names causes
Environment:Attachments:
Description:Names "start" and "end" is reserwed keywords in postgresql and other sql-compliant databases.

When using following cdr table scheme:
{noformat}
CREATE TABLE cdr (
   id bigint NOT NULL,
   accountcode character varying(20) NOT NULL,
   src character varying(80) DEFAULT ''::character varying NOT NULL,
   dst character varying(80) DEFAULT ''::character varying NOT NULL,
   dcontext character varying(80) DEFAULT ''::character varying NOT NULL,
   clid character varying(80) DEFAULT ''::character varying NOT NULL,
   channel character varying(80) DEFAULT ''::character varying NOT NULL,
   dstchannel character varying(80) DEFAULT ''::character varying NOT NULL,
   lastapp character varying(80) DEFAULT ''::character varying NOT NULL,
   lastdata character varying(80) DEFAULT ''::character varying NOT NULL,
   start timestamp with time zone DEFAULT now() NOT NULL,
   answer timestamp with time zone,
   end timestamp with time zone,
   duration integer DEFAULT 0,
   billsec integer DEFAULT 0,
   disposition disposition_t,
   amaflags character varying(80) DEFAULT ''::character varying,
   uniqueid character varying(32) DEFAULT ''::character varying,
   userfield character varying(255)
);
{noformat}

, there will be an error inserting into cdr table:

{noformat}
[Jun 29 11:20:12] ERROR[8297]: cdr_pgsql.c:297 pgsql_log: cdr_pgsql: Failed to insert call detail record into database!
[Jun 29 11:20:12] ERROR[8297]: cdr_pgsql.c:298 pgsql_log: cdr_pgsql: Reason: ERROR:  syntax error at or near "end"
LINE 1: ...ntcode,src,dst,dcontext,clid,channel,start,answer,end,durati...
                                                            ^

[Jun 29 11:20:12] ERROR[8297]: cdr_pgsql.c:299 pgsql_log: cdr_pgsql: Connection may have been lost... attempting to reconnect.
[Jun 29 11:20:12] ERROR[8297]: cdr_pgsql.c:302 pgsql_log: cdr_pgsql: Connection reestablished.
[Jun 29 11:20:12] ERROR[8297]: cdr_pgsql.c:308 pgsql_log: cdr_pgsql: HARD ERROR!  Attempted reconnection failed.  DROPPING CALL RECORD!
[Jun 29 11:20:12] ERROR[8297]: cdr_pgsql.c:309 pgsql_log: cdr_pgsql: Reason: ERROR:  syntax error at or near "end"
LINE 1: ...ntcode,src,dst,dcontext,clid,channel,start,answer,end,durati...
                                                            ^

[Jun 29 11:20:12] ERROR[8297]: cdr_pgsql.c:297 pgsql_log: cdr_pgsql: Failed to insert call detail record into database!
[Jun 29 11:20:12] ERROR[8297]: cdr_pgsql.c:298 pgsql_log: cdr_pgsql: Reason: ERROR:  syntax error at or near "end"
LINE 1: ...,channel,dstchannel,lastapp,lastdata,start,answer,end,durati...
                                                            ^

[Jun 29 11:20:12] ERROR[8297]: cdr_pgsql.c:299 pgsql_log: cdr_pgsql: Connection may have been lost... attempting to reconnect.
[Jun 29 11:20:12] ERROR[8297]: cdr_pgsql.c:302 pgsql_log: cdr_pgsql: Connection reestablished.
[Jun 29 11:20:12] ERROR[8297]: cdr_pgsql.c:308 pgsql_log: cdr_pgsql: HARD ERROR!  Attempted reconnection failed.  DROPPING CALL RECORD!
[Jun 29 11:20:12] ERROR[8297]: cdr_pgsql.c:309 pgsql_log: cdr_pgsql: Reason: ERROR:  syntax error at or near "end"
LINE 1: ...,channel,dstchannel,lastapp,lastdata,start,answer,end,durati...
{noformat}

, proposed to rename column "end" to "callend" , "start" to "callstart", and for better naming "answer" to "callanswer".


****** ADDITIONAL INFORMATION ******

Asterisk: asterisk-1.6.0-beta9
System: Gentoo Linux AMD64, latest
Database: postgresql-8.3.3
Comments:By: Anthony Messina (amessina) 2013-11-05 05:10:27.467-0600

I cloned this issue because the same error occurs with cdr_adaptive_odbc when using PostgreSQL and Asterisk 11.6.0

{noformat}
[Nov  5 04:43:25] WARNING[2610][C-00000002]: res_odbc.c:645 ast_odbc_prepare_and_execute: SQL Execute returned an error -1: 42601: ERROR: syntax error at or near "end";
Error while executing the query (69)
[Nov  5 04:43:25] WARNING[2610][C-00000002]: res_odbc.c:657 ast_odbc_prepare_and_execute: SQL Execute error -1! Verifying connection to asterisk [asterisk]...
[Nov  5 04:43:25] WARNING[2610][C-00000002]: res_odbc.c:761 ast_odbc_sanity_check: Connection is down attempting to reconnect...
[Nov  5 04:43:25] NOTICE[2610][C-00000002]: res_odbc.c:1527 odbc_obj_connect: Connecting asterisk
[Nov  5 04:43:25] NOTICE[2610][C-00000002]: res_odbc.c:1559 odbc_obj_connect: res_odbc: Connected to asterisk [asterisk]
[Nov  5 04:43:25] WARNING[2610][C-00000002]: res_odbc.c:645 ast_odbc_prepare_and_execute: SQL Execute returned an error -1: 42601: ERROR: syntax error at or near "end";
Error while executing the query (69)
[Nov  5 04:43:25] WARNING[2610][C-00000002]: res_odbc.c:657 ast_odbc_prepare_and_execute: SQL Execute error -1! Verifying connection to asterisk [asterisk]...
[Nov  5 04:43:25] WARNING[2610][C-00000002]: res_odbc.c:761 ast_odbc_sanity_check: Connection is down attempting to reconnect...
[Nov  5 04:43:25] NOTICE[2610][C-00000002]: res_odbc.c:1527 odbc_obj_connect: Connecting asterisk
[Nov  5 04:43:25] NOTICE[2610][C-00000002]: res_odbc.c:1559 odbc_obj_connect: res_odbc: Connected to asterisk [asterisk]
[Nov  5 04:43:25] WARNING[2610][C-00000002]: cdr_adaptive_odbc.c:739 odbc_log: cdr_adaptive_odbc: Insert failed on 'asterisk:cdr_adaptive'.
{noformat}

Currently, I have set an alias for the 'start' and 'end' columns, which works around the issue, but it would be good to have a proper fix by quoting the columns.
By: Rusty Newton (rnewton) 2013-11-14 18:52:24.406-0600

Thanks for the report, though you haven't provided enough information to determine what is wrong.

First, Please remove any information from the description or other fields that doesn't apply to your problem or scenario. Generally cloning from an old issue, especially a closed one is not advisable.

We'll need the following info:

* PostgreSQL version and connector version
* res_odbc.conf, cdr_adaptive_odbc.conf
* Asterisk log, showing VERBOSE, DEBUG messages, turned up to level 5 (see logger.conf and asterisk.conf, https://wiki.asterisk.org/wiki/display/AST/Collecting+Debug+Information )

Be sure to attach files (More Actions > Attach Files) in a .txt format so they are easy to view. And of course sanitize your passwords or sensitive information.

By: Anthony Messina (amessina) 2013-11-15 15:02:06.891-0600

Ok Rusty, but I cloned the resolved issue on purpose becuase the original report referred to cdr_odbc which was indeed fixed properly (for PostgreSQL) by quoting the column names in the source.  For some reason, the same fix never made it to cdr_adaptive_odbc.  I was trying to point to the fix that was applied in a "related" file.
By: Rusty Newton (rnewton) 2013-12-12 18:15:35.326-0600

Roger that. I misunderstood. Thanks for the clarification and report!
By: Kevin Harwell (kharwell) 2014-01-15 17:03:31.863-0600

Quoting the columns in cdr_adaptive_odbc would possibly break other types of SQL servers (for instance, MySQL doesn't accept quoted column names by default) and we don't want to require users to have to configure their SQL servers a certain way for Asterisk.

When using a postgres server and cdr_adaptive_odbc utilizing aliases is currently the best solution to this problem.
By: Marcelo Terres (mhterres) 2016-01-12 08:04:43.912-0600

What's the solution?

It continues to not working in Asterisk 13.6. And now that cdr_odbc show not be used anymore (https://issues.asterisk.org/jira/browse/ASTERISK-23770), I think it will be nice to solve this question correctly.