[Home]

Summary:ASTERISK-17260: PGSQL db update issue
Reporter:Kirill Garbar (hydr0gen)Labels:
Date Opened:2011-01-17 09:44:42.000-0600Date Closed:2011-06-07 14:00:43
Priority:CriticalRegression?No
Status:Closed/CompleteComponents:CDR/cdr_pgsql
Versions:Frequency of
Occurrence
Related
Issues:
Environment:Attachments:
Description:I can't reproduce error. It happend sometimes. Asterisk is trying to insert wrong SQL code.

from Postgresql debug:
Jan 17 15:27:58 TuX postgres[26769]: [4-1] ERROR:  syntax error at or near "VALUES" at character 329
Jan 17 15:27:58 TuX postgres[26769]: [4-2] STATEMENT:   VALUES ("calldate"'2011-01-17 15:30:03',"clid",'"759" <759>',"src",'759',"dst",'1007',"dcontext",'local',"channel",'SIP/759-00000371',"dstchannel",'SIP/1007-00000372',"lastapp",'Dial',"lastdata",'SIP/1007,20,rtT',"duration",82,"billsec",75,"disposition",'ANSWERED',"amaflags",3,"accountcode",'759',"uniqueid",'1295278203.882') VALUES ("calldate"'2011-01-17 15:30:03',"clid",'"759" <759>',"src",'759',"dst",'1007',"dcontext",'local',"channel",'SIP/759-00000371',"dstchannel",'SIP/1007-00000372',"lastapp",'Dial',"lastdata",'SIP/1007,20,rtT',"duration",82,"billsec",75,"disposition",'ANSWERED',"amaflags",3,"accountcode",'759',"uniqueid",'1295278203.882')

as you can see asterisk is trying to insert wrong code, the values, witch goes past "VALUES" consist values name.

The thing is - when it's happen asterisk core dumps. I havn't seen this issue before i start using:

sip.conf:
limitonpeer=yes
notifyringing=yes
rtcachefriends=yes
rtupdate=yes

I'm using REALTIME SIP/Queues/MOH/VM

Today i updated from 1.6.2.15 (freebsd) to 1.6.2.16. Nothing changed. Asterisk still sometimes core dumps.
Comments:By: Kirill Garbar (hydr0gen) 2011-01-17 09:57:40.000-0600

It's look like some garbage goes to sql buffer.

First time when i got this issue, it was when asterisk try to update suo table in pgsql. And crashesh because fullname field was 97 char leng, but db field was only 64.

asterisk/message:
[Jan 17 15:31:25] ERROR[73256] cdr_pgsql.c: Failed to insert call detail record into database!
[Jan 17 15:31:25] ERROR[73256] cdr_pgsql.c: Reason: ERROR:  syntax error at or near "VALUES"
LINE 1: ...,"accountcode",'759',"uniqueid",'1295278203.882') VALUES ("c...
                                                            ^

[Jan 17 15:31:25] ERROR[73256] cdr_pgsql.c: Connection may have been lost... attempting to reconnect.
[Jan 17 15:31:25] ERROR[73256] cdr_pgsql.c: Connection reestablished.
[Jan 17 15:31:25] ERROR[73256] cdr_pgsql.c: HARD ERROR!  Attempted reconnection failed.  DROPPING CALL RECORD!
[Jan 17 15:31:25] ERROR[73256] cdr_pgsql.c: Reason: ERROR:  syntax error at or near "VALUES"
LINE 1: ...,"accountcode",'759',"uniqueid",'1295278203.882') VALUES ("c...
                                                            ^

[Jan 17 15:31:25] ERROR[73256] astobj2.c: bad magic number 0x35323a for 0x2a9afb00
[Jan 17 15:31:25] ERROR[73256] astobj2.c: bad magic number 0x747261 for 0x2a054f22
[Jan 17 15:31:25] ERROR[73256] astobj2.c: bad magic number 0x7261 for 0x2a054f22
[Jan 17 15:31:25] ERROR[73256] astobj2.c: bad magic number 0x7261 for 0x2a054f22
[Jan 17 15:31:25] ERROR[73256] astobj2.c: bad magic number 0x3a706973 for 0x2a9af800
By: Leif Madsen (lmadsen) 2011-01-18 09:36:57.000-0600

Can you provide a backtrace for the crash?

https://wiki.asterisk.org/wiki/display/AST/Getting+a+Backtrace
By: Kirill Garbar (hydr0gen) 2011-01-21 06:19:26.000-0600

I can't reproduce bug, because i reinstall Asterisk on life system. Right now i use 1.6.2.16 with realtime enabled. But i  use just basic configuration files with RT enabled, nothing more. I can't make blind transfer, but i have no this issue. Maybe because asterisk can't update db:

[Jan 21 12:04:40] NOTICE[2492]: chan_sip.c:18479 handle_response_peerpoke: Peer '702' is now Reachable. (153ms / 2000ms)
[Jan 21 12:04:40] ERROR[2492]: res_config_pgsql.c:641 update_pgsql: PostgreSQL RealTime: Updating on column 'lastms', but that column does not exist within the table 'system_sip'!

[Jan 21 12:05:35] ERROR[2492]: res_config_pgsql.c:641 update_pgsql: PostgreSQL RealTime: Updating on column 'ipaddr', but that column does not exist within the table 'system_sip'!

db is fine. And 1.8.2.2 connected to that tables, and no issues.

What i can do: i can get old configs and run on my test server 1.6.x
Last time i cat generate only core dump after call transfer.
In 1.8.2.2 it's just make asterisk unavailable from network. It's drop any connections :-D


By: Leif Madsen (lmadsen) 2011-01-21 08:31:01.000-0600

The errors you're seeing are typically when you are missing columns in the table you're trying to write to.

It would be ideal to get the backtrace and the debugging output from the database side to see what Asterisk is trying to send to the database (which may point out why it is failing to write).
By: Kirill Garbar (hydr0gen) 2011-01-21 08:38:49.000-0600

db table

CREATE TABLE "public"."system_sip" (
 "id" SERIAL,
 "name" VARCHAR(80) DEFAULT ''::character varying NOT NULL,
 "accountcode" VARCHAR(20),
 "amaflags" VARCHAR(7),
 "callgroup" VARCHAR(10),
 "callerid" VARCHAR(80),
 "canreinvite" VARCHAR(3) DEFAULT 'no'::character varying,
 "context" VARCHAR(80),
 "defaultip" VARCHAR(15),
 "dtmfmode" VARCHAR(7),
 "fromuser" VARCHAR(80),
 "fromdomain" VARCHAR(80),
 "host" VARCHAR(31) DEFAULT 'dynamic'::character varying NOT NULL,
 "insecure" VARCHAR(4),
 "language" VARCHAR(2),
 "mailbox" VARCHAR(50),
 "md5secret" VARCHAR(80),
 "nat" VARCHAR(5) DEFAULT 'no'::character varying NOT NULL,
 "permit" VARCHAR(95),
 "deny" VARCHAR(95),
 "mask" VARCHAR(95),
 "pickupgroup" VARCHAR(10),
 "port" VARCHAR(12) DEFAULT ''::character varying NOT NULL,
 "qualify_old" VARCHAR(3),
 "restrictcid" VARCHAR(1),
 "rtptimeout" VARCHAR(3),
 "rtpholdtimeout" VARCHAR(3),
 "secret" VARCHAR(80),
 "type" VARCHAR DEFAULT 'friend'::character varying NOT NULL,
 "defaultuser" VARCHAR(100) DEFAULT ''::character varying NOT NULL,
 "disallow" VARCHAR(100),
 "allow" VARCHAR(100) DEFAULT 'ulaw;alaw'::character varying,
 "musiconhold" VARCHAR(100),
 "regseconds" BIGINT DEFAULT 0::bigint NOT NULL,
 "ipaddr" VARCHAR(15) DEFAULT ''::character varying NOT NULL,
 "regexten" VARCHAR(80) DEFAULT ''::character varying NOT NULL,
 "cancallforward" VARCHAR(3) DEFAULT 'yes'::character varying,
 "lastms" VARCHAR(80) DEFAULT (-1) NOT NULL,
 "rtcachefriends" VARCHAR(3) DEFAULT 'yes'::character varying NOT NULL,
 "rtupdate" VARCHAR(3) DEFAULT 'yes'::character varying NOT NULL,
 "notifyringing" VARCHAR(3) DEFAULT 'yes'::character varying NOT NULL,
 "useragent" TEXT,
 "account_id" INTEGER,
 "fullcontact" VARCHAR(255),
 "regserver" VARCHAR(100),
 "busy-level" INTEGER DEFAULT 1 NOT NULL,
 "call-limit" SMALLINT DEFAULT 6 NOT NULL,
 "username" VARCHAR(100),
 "directmedia" VARCHAR(3) DEFAULT 'yes'::character varying NOT NULL
) WITHOUT OIDS;
By: Kirill Garbar (hydr0gen) 2011-01-21 08:43:56.000-0600

I can't see error on PostgreSQL logs. But i think this is helping me right now. Can't save - can't segfault.


By: Leif Madsen (lmadsen) 2011-02-10 13:03:37.000-0600

So you're saying you can't reproduce this now?