[asterisk-bugs] [JIRA] (ASTERISK-22826) cdr_adaptive_odbc SQL execute error with PostgreSQL (CLONE - [patch] Column names causes)
Anthony Messina (JIRA)
noreply at issues.asterisk.org
Tue Nov 5 05:12:03 CST 2013
[ https://issues.asterisk.org/jira/browse/ASTERISK-22826?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=211514#comment-211514 ]
Anthony Messina commented on ASTERISK-22826:
--------------------------------------------
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.
> cdr_adaptive_odbc SQL execute error with PostgreSQL (CLONE - [patch] Column names causes)
> -----------------------------------------------------------------------------------------
>
> Key: ASTERISK-22826
> URL: https://issues.asterisk.org/jira/browse/ASTERISK-22826
> Project: Asterisk
> Issue Type: Bug
> Components: CDR/cdr_adaptive_odbc
> Reporter: Anthony Messina
> Severity: Minor
>
> Names "start" and "end" is reserwed keywords in postgresql and other sql-compliant databases.
> When using following cdr table scheme:
> 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)
> );
> , there will be an error inserting into cdr table:
> [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...
> , 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
--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators: https://issues.asterisk.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira
More information about the asterisk-bugs
mailing list