[Home]

Summary:ASTERISK-23594: Problem inserting CEL records when certain characters are used
Reporter:Krzysztof Chmielewski (kristoff)Labels:
Date Opened:2014-04-07 04:05:41Date Closed:2014-04-24 18:24:34
Priority:MajorRegression?No
Status:Closed/CompleteComponents:CEL/cel_odbc Resources/res_odbc
Versions:1.8.19.1 1.8.26.1 11.10.2 Frequency of
Occurrence		Constant
Related
Issues:
is a clone ofASTERISK-24053 CLONE - Problem inserting CEL records when certain characters are used
is a clone ofASTERISK-24054 CLONE - Problem inserting CEL records when certain characters are used
Environment:Attachments:( 0) asteriskUtf8.cap
( 1) utf8Error.pcap
Description:Hello,
we noticed encoding error in Asterisk logs every time when our asterisk receives SIP packet with polish special sign.

The error is:
{code}
[2014-04-04 08:29:26] NOTICE[5630] res_odbc.c: res_odbc: Connected to asterisk [postgresql]
[2014-04-04 08:29:26] WARNING[5630] res_odbc.c: SQL Execute returned an error -1: HY000: ERROR:  invalid byte sequence for encoding "UTF8": 0xb3;
Error while executing the query (88)
[2014-04-04 08:29:26] WARNING[5630] res_odbc.c: SQL Execute error -1! Verifying connection to asterisk [postgresql]...
[2014-04-04 08:29:26] WARNING[5630] res_odbc.c: Connection is down attempting to reconnect...
[2014-04-04 08:29:26] DEBUG[5630] res_odbc.c: Disconnected 0 from asterisk [postgresql]
[2014-04-04 08:29:26] DEBUG[5630] res_odbc.c: Database handle 0xb43d2aa8 deallocated
[2014-04-04 08:29:26] NOTICE[5630] res_odbc.c: Connecting asterisk
[2014-04-04 08:29:26] NOTICE[5630] res_odbc.c: res_odbc: Connected to asterisk [postgresql]
[2014-04-04 08:29:26] WARNING[5630] cel_odbc.c: Insert failed on 'asterisk:cel'.  CEL failed: INSERT INTO cel (eventtype,eventtime,userdeftype,cid_name,cid_num,cid_ani,cid_rdnis,cid_dnid,exten,context,channame,appname,amaflags,accountcode,peeraccount,uniqueid,linkedid,userfield,peer,extra,cel_state,queueinfo,sipcallid,rtpqos,preuid,preprocessexten) VALUES ('CHAN_START',{ ts '2014-04-04 08:29:26' },'','Rafał Gawra','rgawra','','','','932','CALLEX','SIP/rgawra-000075dd','',3,'rgawra','rgawra','1396592966.35521','1396592966.35521','','','','','','','','','')
{code}

Problem consider the cel_odbc mechanism - we are using CEL mechanism and cel_odbc. Asterisk is trying to insert CHAN_START event to our Postgresql database with CID value "Rafał Gawra" Letter "ł" seams as a problem here.

Our Postgresql database is in UTF-8 coding and in /etc/locale.gen file (we are using Debian OS) we have:

{code}
en_US.UTF-8 UTF-8
pl_PL ISO-8859-2
pl_PL.UTF-8 UTF-8
{code}

We noticed that Asterisk can crash from this error when system is under bigger load and this error occurred couple of times at the same time.

Problem seams to be similar to ASTERISK-15724 (problem with cdr_pgsql )

Problem is very easy to reproduce. You just need to send SIP Packet (invite) with polish sign in FROM or TO header.
Comments:By: Rusty Newton (rnewton) 2014-04-09 07:31:17.836-0500

You reported this as a regression. Was it working in a previous version?

I'm going to de-select the regression field until you confirm.

Please provide a backtrace from the crash. Please follow the instructions exactly as in the link indicated.
https://wiki.asterisk.org/wiki/display/AST/Getting+a+Backtrace

I'm going to have someone look at this that knows more about character encoding than I.
By: Krzysztof Chmielewski (kristoff) 2014-04-09 08:38:11.624-0500

Hi,
I'm sorry, the problem is in older versions also.

I will try to provide Backtrace, but please noticed that crash occurs only sometimes under a heavy load. The main problem is encoding problem (similar to ASTERISK-15724)

Best Regards
K.


By: Rusty Newton (rnewton) 2014-04-09 16:23:00.675-0500

I cannot reproduce this with MySQL instead of PostgreSQL, I think the issue is either in your ODBC or PostgreSQL configuration.

Can you attach the configuration of your /etc/odbc.ini ? I'm curious what you have for the Charset value and if you have set that, have you restarted everything?

By: Krzysztof Chmielewski (kristoff) 2014-04-11 04:44:27.089-0500

Hi,
my /etc/odbc.ini is:

{code}
[PostgreSQL]
Description     = PostgreSQL settings
Driver          = PostgreSQL
Trace           = yes
TraceFile       = /var/log/odbc.log
Database        = testvoip
Servername      = 127.0.0.1
UserName        = *********
Password        = *********
Port            = 5432
Protocol        = 8.3
ReadOnly        = no
RowVersioning   = no
ShowSystemTables= no
ShowOidColumn   = no
FakeOidIndex    = no
ConnSettings    =
{code}


By: Rusty Newton (rnewton) 2014-04-13 15:45:55.020-0500

Try setting "Charset = utf8" for the connection in your odbc.ini file. Restart the whole system and see if you can still reproduce the issue.
By: Krzysztof Chmielewski (kristoff) 2014-04-14 10:26:16.393-0500

No luck here with charset = utf8.
I attached sip trace with INVITE contains invalid character. The problematic charter is in To header. Soft-phone sending this package is http://www.portsip.com/  

By: Rusty Newton (rnewton) 2014-04-16 12:50:20.888-0500

Does the issue only occur when the character is present in the To header of the INVITE?  Or does it also occur if you put that character, for instance, in the callerid field for a SIP peer? (assuming you have that CEL field being written to database)
By: Krzysztof Chmielewski (kristoff) 2014-04-17 01:50:18.097-0500

Hi,
issue also occurs if character is present in FROM header.
By: Rusty Newton (rnewton) 2014-04-24 18:24:03.801-0500

I could not reproduce the issue.

I made calls to and from Asterisk in Asterisk 1.8 and 11, using this character string "Zażółć gęślą jaźń" in the From and To headers, which contains all of the Polish diacritic characters. The CEL entries all work fine using cel_odbc. The characters end up in the database looking fine as well.  Originally I did run into the issue of invalid letters in the database, which was resolved by making sure the /etc/odbc.ini charset was set to utf8 and restarting everything. I already had MySQL setup so that is what I tested with, but I went ahead and installed PostgresQL just to be sure, that worked fine as well.

I don't know what else to try. Asterisk, the odbc drivers, odbc itself and MySQL, PostgresQL all work fine with those characters. I can't reproduce your issue, therefore the issue is likely configuration on your end.

Here is my configuration in case it helps you out:

Using Postgres 9.1.13, this config works with current Asterisk 1.8 or 11

/etc/odbc.ini
{noformat}
[asterisk-connector]
Description           = MySQL connection to 'asterisk' database
Driver                = PostgreSQL
Database              = asterisk
Servername            = localhost
Port                  = 5432
Charset               = utf8
{noformat}

/etc/odbcinst.ini
{noformat}
[PostgreSQL]
Description = ODBC for PostgreSQL
Driver = /usr/lib/i386-linux-gnu/odbc/psqlodbca.so
Setup = /usr/lib/i386-linux-gnu/odbc/libodbcpsqlS.so
FileUsage = 1
{noformat}

/etc/asterisk/res_odbc.conf
{noformat}
[ENV]

[asterisk]
enabled => yes
dsn => asterisk-connector
username => asterisk
password => **********
pooling => no
limit => 1
pre-connect => yes
{noformat}

/etc/asterisk/cel_odbc.conf
{noformat}
[mytable]
connection=asterisk
table=cel
{noformat}

Table schema
{noformat}
CREATE TABLE cel (
       id serial ,
       eventtype varchar (30) NOT NULL ,
       eventtime timestamp NOT NULL ,
       userdeftype varchar(255) NOT NULL ,
       cid_name varchar (80) NOT NULL ,
       cid_num varchar (80) NOT NULL ,
       cid_ani varchar (80) NOT NULL ,
       cid_rdnis varchar (80) NOT NULL ,
       cid_dnid varchar (80) NOT NULL ,
       exten varchar (80) NOT NULL ,
       context varchar (80) NOT NULL ,
       channame varchar (80) NOT NULL ,
       appname varchar (80) NOT NULL ,
       appdata varchar (80) NOT NULL ,
       amaflags int NOT NULL ,
       accountcode varchar (20) NOT NULL ,
       peeraccount varchar (20) NOT NULL ,
       uniqueid varchar (150) NOT NULL ,
       linkedid varchar (150) NOT NULL ,
       userfield varchar (255) NOT NULL ,
       peer varchar (80) NOT NULL
);
{noformat}

An example of the working sql INSERT
{noformat}
   -- Called SIP/6004
   -- SIP/6004-00000005 is ringing
      > [INSERT INTO cel (eventtype,eventtime,userdeftype,cid_name,cid_num,cid_ani,cid_rdnis,cid_dnid,exten,context,channame,appname,appdata,amaflags,accountcode,peeraccount,uniqueid,linkedid,userfield,peer) VALUES ('CHAN_START',{ ts '2014-04-24 18:18:48' },'','Zażółć gęślą jaźń','6003','','','','6004','from-internal','SIP/6003-00000004','','',3,'','','1398381528.4','1398381528.4','','')]
{noformat}

The insert succeeds as I can see the characters in the DB
{noformat}
13 | HANGUP     | 2014-04-24 18:09:04 |             | Zażółć gęślą jaźń |
{noformat}

I'm going to close the issue out since we can't reproduce and you haven't been able to provide feedback on the crash. If you can provide a backtrace as requested for the crash, feel free to open a new issue for that crash. Good luck on the failed INSERT. If you get more information on how to reproduce it , or how to fix it, please let us know. You can usually contact a bug marshal in irc.freenode.net at #asterisk-bugs.

Thanks,
By: Krzysztof Chmielewski (kristoff) 2014-07-17 09:39:15.952-0500

Hello,
I have new info about this problem. I can easily reproduce this error by calling from Zoiper Free using special Polish characters like "śćśćśćść" in Extension field (To Header)

I cannot reproduced Asterisk crush after this on my test system but on my prod system i can see this error in syslog
{code}
kernel: [1206130.300777] asterisk[663]: segfault at 270 ip b57694ab sp b541c570 error 4 in libodbc.so.1.0.0[b5759000+5b000]
{code}

and there is asterisk crush under heavy load.

SIp trace in attachment utf8Error.cap
By: Krzysztof Chmielewski (kristoff) 2014-07-21 03:42:16.070-0500

ping