[asterisk-bugs] [JIRA] (ASTERISK-23594) Problem inserting CEL records when certain characters are used

Thu Apr 24 18:24:18 CDT 2014

    [ https://issues.asterisk.org/jira/browse/ASTERISK-23594?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=217582#comment-217582 ] 

Rusty Newton commented on ASTERISK-23594:
-----------------------------------------

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,

> Problem inserting CEL records when certain characters are used
> --------------------------------------------------------------
>
>                 Key: ASTERISK-23594
>                 URL: https://issues.asterisk.org/jira/browse/ASTERISK-23594
>             Project: Asterisk
>          Issue Type: Bug
>      Security Level: None
>          Components: CEL/cel_odbc, Resources/res_odbc
>    Affects Versions: 1.8.19.1, 1.8.26.1
>            Reporter: Krzysztof Chmielewski
>            Assignee: Krzysztof Chmielewski
>         Attachments: utf8Error.pcap
>
>
> 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.

--
This message was sent by Atlassian JIRA
(v6.2#6252)