[asterisk-bugs] [JIRA] (ASTERISK-29453) Missing fields in "ps_endpoints" table and sql query error

[Daniel Thümen (JIRA)]

     [ https://issues.asterisk.org/jira/browse/ASTERISK-29453?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Daniel Thümen updated ASTERISK-29453:
-------------------------------------

    Attachment: asterisk.sql

Exported SQL schema from alembic

> Missing fields in "ps_endpoints" table and sql query error
> ----------------------------------------------------------
>
>                 Key: ASTERISK-29453
>                 URL: https://issues.asterisk.org/jira/browse/ASTERISK-29453
>             Project: Asterisk
>          Issue Type: Bug
>      Security Level: None
>          Components: Contrib/General, Core/Sorcery
>    Affects Versions: 18.3.0, 18.4.0
>         Environment: OS: Debian 10
> PostgreSQL version: 11.12
>            Reporter: Daniel Thümen
>            Severity: Major
>              Labels: fax, webrtc
>         Attachments: asterisk.sql
>
>
> Hi,
> I configured Asterisk 18.4 with a realtime in pgsql.
> The schema was created via alembic with the config file asterisk-18.4.0/contrib/ast-db-manage/config.ini.sample
> I just changed the sqlalchemy.url.
> Then I tried to add an user via ari (I've used the example ari configs from [https://wiki.asterisk.org/wiki/display/AST/Getting+Started+with+ARI])
> Ari parameter:
> * configClass: res_pjsip
> * objectType: endpoint
> * id: 9100
> * fields: {noformat}{"fields": [ 
> {"attribute": "from_user", "value": "9100" },
> {"attribute": "allow", "value": "!all,g722,ulaw,alaw"}, 
> {"attribute": "ice_support", "value": "yes"}, 
> {"attribute": "force_rport", "value": "yes"}, 
> {"attribute": "rewrite_contact", "value": "yes"}, 
> {"attribute": "rtp_symmetric", "value": "yes"}, 
> {"attribute": "context", "value": "default" }, 
> {"attribute": "auth", "value": "9100" },
> {"attribute": "aors", "value": "9100"} ] }
> {noformat}
> Asterisk creates from this ari request the following SQL Query:
> {code:sql}
> INSERT INTO ps_endpoints (id, rpid_immediate, webrtc, ignore_183_without_sdp, device_state_busy_at, t38_udptl_maxdatagram, codec_prefs_incoming_answer, dtls_rekey, named_pickup_group, direct_media_method, send_rpid, pickup_group, sdp_session, dtls_verify, message_context, mailboxes, record_on_feature, dtls_private_key, dtls_fingerprint, outgoing_call_offer_pref, from_domain, timers_sess_expires, named_call_group, dtls_cipher, media_encryption_optimistic, suppress_q850_reason_headers, aors, identify_by, callerid_privacy, mwi_subscribe_replaces_unsolicited, cos_audio, follow_early_media_fork, context, rtp_symmetric, transport, moh_suggest, t38_udptl, fax_detect, tos_video, srtp_tag_32, refer_blind_progress, max_audio_streams, bundle, use_avpf, call_group, send_connected_line, fax_detect_timeout, sdp_owner, force_rport, callerid_tag, rtp_timeout_hold, use_ptime, media_address, voicemail_extension, rtp_timeout, stir_shaken, set_var, contact_acl, preferred_codec_only, force_avp, record_off_feature, from_user, send_diversion, send_history_info, t38_udptl_ipv6, tone_zone, language, allow_subscribe, rtp_ipv6, callerid, moh_passthrough, cos_video, dtls_auto_generate_cert, asymmetric_rtp_codec, ice_support, aggregate_mwi, one_touch_recording, mwi_from_user, accountcode, codec_prefs_outgoing_answer, allow, rewrite_contact, user_eq_phone, rtp_engine, subscribe_context, notify_early_inuse_ringing, incoming_mwi_mailbox, incoming_call_offer_pref, allow_unauthenticated_options, auth, direct_media_glare_mitigation, trust_id_inbound,
> bind_rtp_to_media_address, disable_direct_media_on_nat, media_encryption, media_use_received_transport, allow_overlap, dtmf_mode, outbound_auth, tos_audio, dtls_cert_file, dtls_ca_path, dtls_setup, connected_line_method, g726_non_standard, 100rel, timers, direct_media, acl, timers_min_se, trust_id_outbound, sub_min_expiry, rtcp_mux, max_video_streams, accept_multiple_sdp_answers, codec_prefs_incoming_offer, codec_prefs_outgoing_offer, trust_connected_line, send_pai, rtp_keepalive, t38_udptl_ec, t38_udptl_nat, allow_transfer, dtls_ca_file, outbound_proxy, inband_progress) VALUES ('9100', 'false', 'no', 'false', '0', '0', 'prefer:pending, operation:intersect, keep:all, transcode:allow', '0', '', 'invite', 'false', '', 'Asterisk', 'No', '', '', 'automixmon', '', 'SHA-256', 'remote_merge', '', '1800', '',
> '', 'false', 'false', '9100', 'username,ip', 'allowed_not_screened', 'no', '0', 'true', 'default', 'true', '', 'default', 'false', 'false', '0', 'false', 'true', '1', 'false', 'false', '', 'yes', '0', '-', 'true', '', '0', 'false', '', '', '0', 'false', '', '', 'false',
> 'false', 'automixmon', '9100', 'true', 'false', 'false', '', '', 'true', 'false', '<unknown>', 'false', '0', 'No', 'false', 'true', 'true', 'false', '', '', 'prefer:pending, operation:intersect, keep:all, transcode:allow', '(g722|ulaw|alaw)', 'true', 'false', 'asterisk', '', 'false', '', 'local', 'false', '9100', 'none', 'false', 'false', 'false', 'no', 'false', 'true', 'rfc4733', '', '0', '', '', 'active', 'invite', 'false', 'yes', 'yes', 'true', '', '90', 'false', '0', 'false', '1', 'false', 'prefer:pending, operation:intersect, keep:all, transcode:allow', 'prefer:pending, operation:union, keep:all, transcode:allow', 'yes', 'false', '0', 'none', 'false', 'true', '', '', 'false')
> {code}
> And prints the this error Message:
> {noformat}
> [May 25 14:18:52] ERROR[659] res_config_pgsql.c: PostgreSQL RealTime: Query Failed because: ERROR:  syntax error at or near "100"
> LINE 1: ..._setup, connected_line_method, g726_non_standard, 100rel, ti...
> {noformat}
> So I've tested this query locally with quotation marks around 100rel and I figured out, that there are two missing fields inside the ps_endpoints table:
> * incoming_call_offer_pref
> * outgoing_call_offer_pref



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