[asterisk-bugs] [JIRA] (ASTERISK-28069) Dropping CDRs records with local languages
Kirill Nikitin (JIRA)
noreply at issues.asterisk.org
Tue Sep 25 14:37:54 CDT 2018
[ https://issues.asterisk.org/jira/browse/ASTERISK-28069?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=244968#comment-244968 ]
Kirill Nikitin commented on ASTERISK-28069:
-------------------------------------------
{{cdr_pgsql.conf}}
{noformat}
[global]
hostname=/var/run/postgresql/
port=5432
dbname=asterisk
password=password_was_here
user=asterisk
table=cdr
encoding=UTF8
{noformat}
{{global database settings}}
{noformat}
client_encoding UTF8
lc_collate en_US.UTF-8
lc_ctype en_US.UTF-8
lc_messages en_US.UTF-8
lc_monetary en_US.UTF-8
lc_numeric en_US.UTF-8
lc_time en_US.UTF-8
server_encoding UTF8
{noformat}
{{CDR table definition}}
{noformat}
--
-- PostgreSQL database dump
--
SET statement_timeout = 0;
SET lock_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET search_path = public, pg_catalog;
SET default_tablespace = '';
SET default_with_oids = false;
--
-- Name: cdr; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
--
CREATE TABLE cdr (
calldate timestamp without time zone NOT NULL,
clid character varying(80) NOT NULL,
src character varying(80) NOT NULL,
dst character varying(80) NOT NULL,
dcontext character varying(80) NOT NULL,
channel character varying(80) NOT NULL,
dstchannel character varying(80) NOT NULL,
lastapp character varying(255) NOT NULL,
lastdata character varying(255) COLLATE pg_catalog."ru_RU" NOT NULL,
duration integer NOT NULL,
billsec integer NOT NULL,
disposition character varying(45) NOT NULL,
amaflags integer NOT NULL,
accountcode character varying(20) NOT NULL,
uniqueid character varying(150) NOT NULL,
peeraccount character varying(20) NOT NULL,
linkedid character varying(150) NOT NULL,
sequence integer NOT NULL,
userfield character varying(255) NOT NULL,
recordingpath character varying(255)
);
ALTER TABLE cdr OWNER TO postgres;
--
-- Name: cdr_dcontext_idx; Type: INDEX; Schema: public; Owner: postgres; Tablespace:
--
CREATE INDEX cdr_dcontext_idx ON cdr USING btree (dcontext);
--
-- Name: cdr_uniqueid_idx; Type: INDEX; Schema: public; Owner: postgres; Tablespace:
--
CREATE INDEX cdr_uniqueid_idx ON cdr USING btree (uniqueid);
--
-- Name: cdr; Type: ACL; Schema: public; Owner: postgres
--
REVOKE ALL ON TABLE cdr FROM PUBLIC;
REVOKE ALL ON TABLE cdr FROM postgres;
GRANT ALL ON TABLE cdr TO postgres;
GRANT ALL ON TABLE cdr TO asterisk;
GRANT SELECT ON TABLE cdr TO monitor;
--
-- PostgreSQL database dump complete
--
{noformat}
{{extensions table definition}}
{noformat}
--
-- PostgreSQL database dump
--
SET statement_timeout = 0;
SET lock_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET search_path = public, pg_catalog;
SET default_tablespace = '';
SET default_with_oids = false;
--
-- Name: extensions; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
--
CREATE TABLE extensions (
id bigint NOT NULL,
context character varying(40) NOT NULL,
exten character varying(40) NOT NULL,
app character varying(40) NOT NULL,
appdata character varying(256) NOT NULL,
priority integer
);
ALTER TABLE extensions OWNER TO postgres;
--
-- Name: extensions_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--
CREATE SEQUENCE extensions_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
ALTER TABLE extensions_id_seq OWNER TO postgres;
--
-- Name: extensions_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--
ALTER SEQUENCE extensions_id_seq OWNED BY extensions.id;
--
-- Name: id; Type: DEFAULT; Schema: public; Owner: postgres
--
ALTER TABLE ONLY extensions ALTER COLUMN id SET DEFAULT nextval('extensions_id_seq'::regclass);
--
-- Name: extensions_context_exten_priority_key; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
--
ALTER TABLE ONLY extensions
ADD CONSTRAINT extensions_context_exten_priority_key UNIQUE (context, exten, priority);
--
-- Name: extensions_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace:
--
ALTER TABLE ONLY extensions
ADD CONSTRAINT extensions_pkey PRIMARY KEY (id);
--
-- Name: extensions; Type: ACL; Schema: public; Owner: postgres
--
REVOKE ALL ON TABLE extensions FROM PUBLIC;
REVOKE ALL ON TABLE extensions FROM postgres;
GRANT ALL ON TABLE extensions TO postgres;
GRANT SELECT,INSERT,DELETE,UPDATE ON TABLE extensions TO asterisk;
GRANT SELECT ON TABLE extensions TO monitor;
--
-- Name: extensions_id_seq; Type: ACL; Schema: public; Owner: postgres
--
REVOKE ALL ON SEQUENCE extensions_id_seq FROM PUBLIC;
REVOKE ALL ON SEQUENCE extensions_id_seq FROM postgres;
GRANT ALL ON SEQUENCE extensions_id_seq TO postgres;
GRANT SELECT,UPDATE ON SEQUENCE extensions_id_seq TO asterisk;
--
-- PostgreSQL database dump complete
--
{noformat}
Sean Bright, I think you are right. But this caused CDR drops and incorrect billing data.
We didn't test with MySQL and ODBC drivers, but for all "file" format it works fine and for failed (in database) records file copy looks quite fine.
Some comments on configs above:
Connection via unix socket or via TCP socket doesn't change anything (quite obvious why)
COLLATE definition on lastdata was added later. Also nothing changed after that.
> Dropping CDRs records with local languages
> -------------------------------------------
>
> Key: ASTERISK-28069
> URL: https://issues.asterisk.org/jira/browse/ASTERISK-28069
> Project: Asterisk
> Issue Type: Bug
> Security Level: None
> Components: CDR/cdr_pgsql
> Affects Versions: 13.19.0, 13.23.0, 13.23.1
> Environment: Debian 3.16.51-2
> psql (PostgreSQL) 9.4.15
> Reporter: Kirill Nikitin
> Assignee: Kirill Nikitin
> Severity: Minor
>
> If in CDR record appears local language Asterisk may corrupt data and fail to write them in database.
> How to reproduce:
> 1. Create context in extensions in the database with some line in local language.
> Encoding of database must be UTF-8.
> {noformat}
> Example (with Russian):
> 173705
> text-machine dbd54025c33844fbe672835e5960c0b7 NoOp ------------- Text Machine -------------
> 1
> 173706
> text-machine dbd54025c33844fbe672835e5960c0b7 Answer
> 2
> 173707
> text-machine dbd54025c33844fbe672835e5960c0b7 Hangup
> 9
> 173708
> text-machine dbd54025c33844fbe672835e5960c0b7 AGI say.php,"Уважаемый Иванов Иван Иванович"
> 3
> 173709
> text-machine dbd54025c33844fbe672835e5960c0b7 AGI say.php,"приглашаем вас пройти профосмотр первого августа"
> 4
> 173710
> text-machine dbd54025c33844fbe672835e5960c0b7 AGI say.php,"по адресу улица казахстанская, дом номер один, кабинет номер двадцать"
> 5
> 173711
> text-machine dbd54025c33844fbe672835e5960c0b7 AGI say.php,"При себе иметь удостоверение личности"
> 6
> 173712
> text-machine dbd54025c33844fbe672835e5960c0b7 Wait 5
> 7
> {noformat}
> Since database accept this no special characters in that context exists.
> Initiate a call and drop a call to force asterisk write Russian characters in lastapp field in CDR.
> You will receive:
> {noformat}
> [2018-09-18 19:15:04] ERROR[10904]: cdr_pgsql.c:439 pgsql_log: Failed to insert call detail record into database!
> [2018-09-18 19:15:04] ERROR[10904]: cdr_pgsql.c:440 pgsql_log: Reason: ERROR: invalid byte sequence for encoding "UTF8": 0xd0 0x20
> [2018-09-18 19:15:04] ERROR[10904]: cdr_pgsql.c:441 pgsql_log: Connection may have been lost... attempting to reconnect.
> [2018-09-18 19:15:04] ERROR[10904]: cdr_pgsql.c:444 pgsql_log: Connection reestablished.
> [2018-09-18 19:15:04] ERROR[10904]: cdr_pgsql.c:452 pgsql_log: HARD ERROR! Attempted reconnection failed. DROPPING CALL RECORD!
> [2018-09-18 19:15:04] ERROR[10904]: cdr_pgsql.c:453 pgsql_log: Reason: ERROR: invalid byte sequence for encoding "UTF8": 0xd0 0x20
> {noformat}
> Since CDR and context are in the same database - I think this is not database locale settings.
> We test it on different 13.XX versions
> We test in on PostgreSQL 9.4.XX and 9.6.XX
> Byte sequence in error message varies a little, but first byte always 0xd0
--
This message was sent by Atlassian JIRA
(v6.2#6252)
More information about the asterisk-bugs
mailing list