asterisk users - Jul 2017 - Asterisk realtime in combination with ARI - error while trying to prepare SQL statement for writing into database

Hey guys!

I successfully got Asterisk realtime (14.6.0) with MariaDB (MySQL fork) running
on Debian 9.

I will document the steps to do so shortly (the main difference is default
encoding and the odbc connector & its configuration).

What I?m trying to do now is to use ARI to create PJSIP endpoints as outlined in
this wiki article:
https://wiki.asterisk.org/wiki/display/AST/ARI+Push+Configuration

So far so good. Excecuting the examples using curl PUT aor and auth was working
fine, when I ran the line for the endpoint object I got an error in the Asterisk
CLI and log stating:

res_config_odbc.c: SQL Prepare failed! [INSERT INTO ps_endpoints (id,
rpid_immediate, device_state_busy_at, t38_udptl_maxdatagram, 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, from_domain, timers_sess_expires, named_call_group,
dtls_cipher, media_encryption_optimistic, aors, identify_by, callerid_privacy,
mwi_subscribe_replaces_unsolicited, cos_audio, context, rtp_symmetric,
transport, moh_suggest, t38_udptl, fax_detect, tos_video, srtp_tag_32, use_avpf,
call_group, fax_detect_timeout, sdp_owner, force_rport, callerid_tag,
rtp_timeout_hold, use_ptime, media_address, voicemail_extension, rtp_timeout,
set_var, contact_acl, force_avp, record_off_feature, from_user, send_diversion,
t38_udptl_ipv6, tone_zone, language, allow_subscribe, rtp_ipv6, callerid,
moh_passthrough, cos_video, asymmetric_rtp_codec, ice_support, aggregate_mwi,
one_touch_recording, mwi_from_user, accountcode, allow, rewrite_contact,
user_eq_phone, rtp_engine, subscribe_context, 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, send_pai, rtp_keepalive, t38_udptl_ec, t38_udptl_nat, allow_transfer,
dtls_ca_file, outbound_proxy, inband_progress) VALUES (?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?)]

Carefully comparing the attributes from the JSON output of the curl statement
(which was successfully completed) and the columns in the database I found the
culprit:

The database is missing the following attribute: ?dtls_fingerprint?
I prepared my database using the alembic scripts from the 14.6.0 source. After
manually adding the column to the database and restarting Asterisk the statement
was successfully executed and everything works fine now.

In addition to the missing attribute there are few columns in the database table
that do not match to a JSON attribute in the pjsip endpoint object.
These are:

-          contact_deny

-          contact_permit

-          contact_user

-          deny

-          disallow

-          external_media_address

-          permit

-          redirect_method


So far to my findings.

Should I file a bug-report for this?


With best regards

Florian Floimair


-------------- next part --------------
An HTML attachment was scrubbed...
URL:
<http://lists.digium.com/pipermail/asterisk-users/attachments/20170713/41655fc0/attachment.html>

On Thu, Jul 13, 2017, at 10:01 AM, Floimair Florian
wrote:
> Hey guys!
> 
> I successfully got Asterisk realtime (14.6.0) with MariaDB (MySQL fork)
> running on Debian 9.
> 
> I will document the steps to do so shortly (the main difference is
> default encoding and the odbc connector & its configuration).
> 
> What I?m trying to do now is to use ARI to create PJSIP endpoints as
> outlined in this wiki article:
> https://wiki.asterisk.org/wiki/display/AST/ARI+Push+Configuration
> 
> So far so good. Excecuting the examples using curl PUT aor and auth was
> working fine, when I ran the line for the endpoint object I got an error
> in the Asterisk CLI and log stating:
<snip>
> 
> Carefully comparing the attributes from the JSON output of the curl
> statement (which was successfully completed) and the columns in the
> database I found the culprit:
> 
> The database is missing the following attribute: ?dtls_fingerprint?
> I prepared my database using the alembic scripts from the 14.6.0 source.
> After manually adding the column to the database and restarting Asterisk
> the statement was successfully executed and everything works fine now.
> 
> In addition to the missing attribute there are few columns in the
> database table that do not match to a JSON attribute in the pjsip
> endpoint object.
> These are:
> 
> -          contact_deny
> 
> -          contact_permit
> 
> -          contact_user
> 
> -          deny
> 
> -          disallow
> 
> -          external_media_address
> 
> -          permit
> 
> -          redirect_method
> 
> 
> So far to my findings.
> 
> Should I file a bug-report for this?
Yes, an issue should be reported for this.

-- 
Joshua Colp
Digium, Inc. | Senior Software Developer
445 Jan Davis Drive NW - Huntsville, AL 35806 - US
Check us out at: www.digium.com & www.asterisk.org