[Home]

Summary:ASTERISK-27272: res_config_pjsql, contrib: Invalid input value for enum yesno_values: "true"
Reporter:Ilgiz Badamshin (i.badamshin)Labels:
Date Opened:2017-09-14 05:34:54Date Closed:
Priority:MinorRegression?
Status:Open/NewComponents:Contrib/General Resources/res_config_pgsql
Versions:14.6.1 16.18.0 18.4.0 Frequency of
Occurrence
Related
Issues:
is duplicated byASTERISK-27407 Boolean types in sorcery that map to yes/no Enum values are not correctly mapped to database
is duplicated byASTERISK-28747 YES/NO attributes are not set properly when creating PJSIP sorcery objects via ARI
is duplicated byASTERISK-29452 Invalid Input Value for enum yesno_values in SQL Query
Environment:Attachments:
Description:Configured Asterisk 14.6.1 with realtime in pgsql.
Some columns has yesno_value enum type in database. But ARI tries insert there boolean values. So sql execution failed and data isn't saved in database:
bq. res_config_pgsql.c: PostgreSQL RealTime: Query Failed because: ERROR: invalid input value for enum yesno_values: "true"

Details: https://community.asterisk.org/t/troubles-with-yesno-values-enum-in-postgresql/71993

Hi.
I configured Asterisk 14.6.1 realtime with pgsql. Schema was created from asterisk-14.6.1/contrib/ast-db-manage/realtime/postgresql/postgresql_config.sql
PostgresSql version: 9.5.8.

Then I enabled ARI and try push configs by exmples from: https://wiki.asterisk.org/wiki/display/AST/ARI+Push+Configuration

In asterisk cli i see errors:
{noformat}
res_config_pgsql.c: PostgreSQL RealTime: Failed to query ‘ps_aors@asterisk-db’.
res_config_pgsql.c: PostgreSQL RealTime: Query Failed: INSERT INTO ps_aors (id, minimum_expiration, default_expiration, qualify_timeout, mailboxes, support_path, voicemail_extension, max_contacts, authenticate_qualify, contact, maximum_expiration, qualify_frequency, remove_existing, outbound_proxy) VALUES (‘alice’, ‘60’, ‘3600’, ‘3.000000’, ‘’, ‘true’, ‘’, ‘1’, ‘false’, ‘’, ‘7200’, ‘0’, ‘true’, ‘’)
res_config_pgsql.c: PostgreSQL RealTime: Query Failed because: ERROR: invalid input value for enum yesno_values: "true"
LINE 1: …y) VALUES (‘alice’, ‘60’, ‘3600’, ‘3.000000’, ‘’, ‘true’, ‘’…
That happens with other tables too.
{noformat}
Is it bug and should be fixed by developers or I should change column type to boolean or add “true”, “false” to enum yesno_values?
Comments:By: Asterisk Team (asteriskteam) 2017-09-14 05:34:54.755-0500

Thanks for creating a report! The issue has entered the triage process. That means the issue will wait in this status until a Bug Marshal has an opportunity to review the issue. Once the issue has been reviewed you will receive comments regarding the next steps towards resolution.

A good first step is for you to review the [Asterisk Issue Guidelines|https://wiki.asterisk.org/wiki/display/AST/Asterisk+Issue+Guidelines] if you haven't already. The guidelines detail what is expected from an Asterisk issue report.

Then, if you are submitting a patch, please review the [Patch Contribution Process|https://wiki.asterisk.org/wiki/display/AST/Patch+Contribution+Process].
By: Rusty Newton (rnewton) 2017-09-14 16:32:40.284-0500

Opened the issue and attached your details from the linked community forum post. Per the guidelines we want all details directly on the issue when possible.

Remember that res_config_pjsql and the contrib items are under [extended support|https://wiki.asterisk.org/wiki/display/AST/Asterisk+Module+Support+States]. That means the fastest way to get this fixed is to submit a patch yourself and walk it [through the process.|https://wiki.asterisk.org/wiki/display/AST/Patch+Contribution+Process]

Thanks!


By: Sean Bright (seanbright) 2017-09-22 14:41:37.196-0500

What does {{realtime show pgsql cache ps_aors}} show?
By: Sean Bright (seanbright) 2017-09-22 15:03:42.336-0500

Also, the file you referenced - {{asterisk-14.6.1/contrib/ast-db-manage/realtime/postgresql/postgresql_config.sql}} - does not exist in the Asterisk source tarball, so I'm not sure where you got it.
By: Ilgiz Badamshin (i.badamshin) 2017-09-26 04:04:19.647-0500

Sorry, mistake with sql. Used three files from github 14.6 branch: https://github.com/asterisk/asterisk/tree/14.6/contrib/realtime/postgresql

realtime show pgsql cache ps_aors:
{code}
Columns for Table Cache 'ps_aors':
Name                 Type                 Len Nullable
id                   varchar               40 NOT NULL
contact              varchar              255        
default_expiration   int4                   4        
mailboxes            varchar               80        
max_contacts         int4                   4        
minimum_expiration   int4                   4        
remove_existing      yesno_values           4        
qualify_frequency    int4                   4        
authenticate_qualify yesno_values           4        
maximum_expiration   int4                   4        
outbound_proxy       varchar               40        
support_path         yesno_values           4        
qualify_timeout      float8                 8        
voicemail_extension  varchar               40  
{code}
Generated sql for update:
{code}
UPDATE ps_aors SET minimum_expiration = '60', default_expiration = '3600', qualify_timeout = '3.000000', mailboxes = '', support_path = 'true', voicemail_extension = '', max_contacts = '1', authenticate_qualify = 'false', contact = '', maximum_expiration = '7200', qualify_frequency = '0', remove_existing = 'true', outbound_proxy = '' WHERE id = 'alice'
{code}
Column from table:
{code}
ALTER TABLE public.ps_aors ADD COLUMN support_path yesno_values;
{code}
And its type:
{code}
CREATE TYPE public.yesno_values AS ENUM
  ('yes',
   'no');
{code}

There also exist type "yes_no_values" with underscore.