Summary:	ASTERISK-28356: app_queue: CLI set ringinuse for realtime member not working
Reporter:	Michael (ringo)	Labels:	pjsip
Date Opened:	2019-03-27 15:58:09	Date Closed:	2021-03-30 04:41:28
Priority:	Minor	Regression?
Status:	Closed/Complete	Components:	Applications/app_queue
Versions:	13.38.1 16.2.0 18.1.0	Frequency of Occurrence
Related Issues:
Environment:	debian 9 amd64 asterisk v16.2.0 using pjsip mysql ODBC	Attachments:
Description:	when changing the ringinuse option for a queue member added via realtime sql, failure occures. *CLI>queue show techsupport techsupport has 0 calls (max unlimited) in 'ringall' strategy (0s holdtime, 0s talktime), W:0, C:0, A:0, SL:0.0%, SL2:0.0% within 0s   Members:      Mike (PJSIP/52501) (ringinuse enabled) (dynamic) (Not in use) has taken no calls yet      Mike (PJSIP/5250) (ringinuse enabled) (realtime) (Not in use) has taken no calls yet   No Callers *CLI>queue set ringinuse no on PJSIP/52501 in techsupport Set ringinuse on interface 'PJSIP/52501' from queue 'techsupport' *CLI>queue set ringinuse no on PJSIP/5250 in techsupport Set ringinuse on interface 'PJSIP/5250' from queue 'techsupport' [Mar 27 16:49:49] WARNING[19466]: res_odbc.c:439 ast_odbc_print_errors: SQL Prepare returned an error: 42000: [MySQL][ODBC 8.0(a) Driver][mysqld-5.5.5-10.1.37-MariaDB-0+deb9u1]You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WHERE uniqueid=?' at line 1 [Mar 27 16:49:49] WARNING[19466]: res_config_odbc.c:122 custom_prepare: SQL Prepare failed! [UPDATE queue_members SET  WHERE uniqueid=?] [Mar 27 16:49:49] WARNING[19466]: res_odbc.c:439 ast_odbc_print_errors: SQL Prepare returned an error: 42000: [MySQL][ODBC 8.0(a) Driver][mysqld-5.5.5-10.1.37-MariaDB-0+deb9u1]You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WHERE uniqueid=?' at line 1 [Mar 27 16:49:49] WARNING[19466]: res_config_odbc.c:122 custom_prepare: SQL Prepare failed! [UPDATE queue_members SET  WHERE uniqueid=?]
Comments:	By: Asterisk Team (asteriskteam) 2019-03-27 15:58:09.844-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]. Please note that once your issue enters an open state it has been accepted. As Asterisk is an open source project there is no guarantee or timeframe on when your issue will be looked into. If you need expedient resolution you will need to find and pay a suitable developer. Asking for an update on your issue will not yield any progress on it and will not result in a response. All updates are posted to the issue when they occur. By: Chris Savinovich (csavinovich) 2019-04-01 12:18:06.848-0500 Hello Michael,  there is a lot of information missing for us to recreate the same error on our side.  The error says syntax error in the SLQ statement, yet you did not entered the sql you are sending.   We are going to need the corresponding sections of all related files:Extensions.conf. queue.conf. pjsip.conf, the SQL you are sending, etc. Thanks C. Savinovich By: Michael (ringo) 2019-04-01 12:47:26.035-0500 I'm not using any of my own custom sql statements.  I'm just doing this on the asterisk cli: "queue set ringinuse no on PJSIP/52501 in techsupport" on a realtime member, and the sql errors are just what I see.  I assumed this sql query is generated internally to asterisk.  There's no dialplan involved.  Sorry there's nothing that i know of that is related in extensions.conf or queues.conf to send.  I assume what is happening is that when the ringinuse option is set on the asterisk cli for a realtime queue member, asterisk tries to update the member entry in the sql but fails.  I don't see anything related to ringinuse in the sql table structure for queue_members. MariaDB [hpbx3]> desc queue_members; +-----------------+-------------+------+-----+---------+----------------+ | Field           | Type        | Null | Key | Default | Extra          | +-----------------+-------------+------+-----+---------+----------------+ | queue_name      | varchar(80) | NO   | PRI | NULL    |                | | interface       | varchar(80) | NO   | PRI | NULL    |                | | membername      | varchar(80) | YES  |     | NULL    |                | | state_interface | varchar(80) | YES  |     | NULL    |                | | penalty         | int(11)     | YES  |     | NULL    |                | | paused          | int(11)     | YES  |     | NULL    |                | | uniqueid        | int(11)     | NO   | UNI | NULL    | auto_increment | | wrapuptime      | int(11)     | YES  |     | NULL    |                | +-----------------+-------------+------+-----+---------+----------------+ 8 rows in set (0.00 sec) By: Chris Savinovich (csavinovich) 2019-04-01 13:04:31.887-0500 Please go over my message and provide us with the items we requested.  You have not sent us yet how you configure your endpoints in pjsip, and you have not sent us the contents of the queue table, if you define your queue in realtime or in queue.conf. Again I don't see how the agents login to the queue. Thanks C. Savinovich By: Michael (ringo) 2019-04-01 13:35:31.249-0500 Not sure what to tell ya.  I can't provide items that don't exist.  There is no dialplan.  No pjsip extensions.  The queue is added into sql via insert statement, and with 1 realtime member. another example: insert into queues (name,announce,ringinuse) values ('techsupport','queue-markq','no'); insert into queue_members (queue_name, interface, membername) values ('techsupport','PJSIP/100','ext 100'); test-system*CLI>dialplan show -= 0 extensions (0 priorities) in 0 contexts. =- test-system*CLI>queue show techsupport has 0 calls (max unlimited) in 'ringall' strategy (0s holdtime, 0s talktime), W:0, C:0, A:0, SL:0.0%, SL2:0.0% within 0s   Members:      ext 100 (PJSIP/100) (ringinuse disabled) (realtime) (Invalid) has taken no calls yet   No Callers test-system*CLI>queue set ringinuse yes on PJSIP/100 in techsupport Set ringinuse on interface 'PJSIP/100' from queue 'techsupport' [Apr  1 14:28:25] WARNING[21115]: res_odbc.c:439 ast_odbc_print_errors: SQL Prepare returned an error: 42000: [MySQL][ODBC 8.0(a) Driver][mysqld-5.5.5-10.1.26-MariaDB-0+deb9u1]You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WHERE uniqueid=?' at line 1 [Apr  1 14:28:25] WARNING[21115]: res_config_odbc.c:122 custom_prepare: SQL Prepare failed! [UPDATE queue_members SET  WHERE uniqueid=?] [Apr  1 14:28:25] WARNING[21115]: res_odbc.c:439 ast_odbc_print_errors: SQL Prepare returned an error: 42000: [MySQL][ODBC 8.0(a) Driver][mysqld-5.5.5-10.1.37-MariaDB-0+deb9u1]You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WHERE uniqueid=?' at line 1 [Apr  1 14:28:25] WARNING[21115]: res_config_odbc.c:122 custom_prepare: SQL Prepare failed! [UPDATE queue_members SET  WHERE uniqueid=?] test-system*CLI> By: Chris Savinovich (csavinovich) 2019-04-01 13:48:33.980-0500 Are you saying you don't create pre-existing extensions 100, 5250, or 52501 in your pjsip configuration? Interesting. By: Michael (ringo) 2019-04-01 13:52:03.745-0500 correct. By: Chris Savinovich (csavinovich) 2019-04-01 14:23:51.791-0500 I will like to help you but you make it difficult because I have to ask you a few times for the same items.  Had you send us your realtime configuration from the start I would have been able to assist you already, Again: database structure, how did you create the tables. *Are you using the Alembic scripts?* full structure of table queues, full structure of table queue_members, etc, etc.permissions on the table and/or ringinuse column. C. Savinovich By: Michael (ringo) 2019-04-01 14:37:43.771-0500 I'm being difficult?  Help Me?  lol.  It is actually me that is trying to help out the Asterisk project.  I'm trying to help you.  I myself am not concerned with this problem.  I don't believe i'm being difficult at all.  If you're not interested in this issue, that's fine.  just close the ticket.  Or i can send you empty config files if you absolutely insist on me sending you files. I have created the tables using the provided mysql_config.sql in the asterisk source: /opt/src/asterisk/contrib/realtime/mysql/mysql_config.sql MariaDB [hpbx3]> desc queues; +-----------------------------+------------------------------------------------------------------------------------------------+------+-----+---------+-------+ | Field                       | Type                                                                                           | Null | Key | Default | Extra | +-----------------------------+------------------------------------------------------------------------------------------------+------+-----+---------+-------+ | name                        | varchar(128)                                                                                   | NO   | PRI | NULL    |       | | musiconhold                 | varchar(128)                                                                                   | YES  |     | NULL    |       | | announce                    | varchar(128)                                                                                   | YES  |     | NULL    |       | | context                     | varchar(128)                                                                                   | YES  |     | NULL    |       | | timeout                     | int(11)                                                                                        | YES  |     | NULL    |       | | ringinuse                   | enum('yes','no')                                                                               | YES  |     | NULL    |       | | setinterfacevar             | enum('yes','no')                                                                               | YES  |     | NULL    |       | | setqueuevar                 | enum('yes','no')                                                                               | YES  |     | NULL    |       | | setqueueentryvar            | enum('yes','no')                                                                               | YES  |     | NULL    |       | | monitor_format              | varchar(8)                                                                                     | YES  |     | NULL    |       | | membermacro                 | varchar(512)                                                                                   | YES  |     | NULL    |       | | membergosub                 | varchar(512)                                                                                   | YES  |     | NULL    |       | | queue_youarenext            | varchar(128)                                                                                   | YES  |     | NULL    |       | | queue_thereare              | varchar(128)                                                                                   | YES  |     | NULL    |       | | queue_callswaiting          | varchar(128)                                                                                   | YES  |     | NULL    |       | | queue_quantity1             | varchar(128)                                                                                   | YES  |     | NULL    |       | | queue_quantity2             | varchar(128)                                                                                   | YES  |     | NULL    |       | | queue_holdtime              | varchar(128)                                                                                   | YES  |     | NULL    |       | | queue_minutes               | varchar(128)                                                                                   | YES  |     | NULL    |       | | queue_minute                | varchar(128)                                                                                   | YES  |     | NULL    |       | | queue_seconds               | varchar(128)                                                                                   | YES  |     | NULL    |       | | queue_thankyou              | varchar(128)                                                                                   | YES  |     | NULL    |       | | queue_callerannounce        | varchar(128)                                                                                   | YES  |     | NULL    |       | | queue_reporthold            | varchar(128)                                                                                   | YES  |     | NULL    |       | | announce_frequency          | int(11)                                                                                        | YES  |     | NULL    |       | | announce_to_first_user      | enum('yes','no')                                                                               | YES  |     | NULL    |       | | min_announce_frequency      | int(11)                                                                                        | YES  |     | NULL    |       | | announce_round_seconds      | int(11)                                                                                        | YES  |     | NULL    |       | | announce_holdtime           | varchar(128)                                                                                   | YES  |     | NULL    |       | | announce_position           | varchar(128)                                                                                   | YES  |     | NULL    |       | | announce_position_limit     | int(11)                                                                                        | YES  |     | NULL    |       | | periodic_announce           | varchar(50)                                                                                    | YES  |     | NULL    |       | | periodic_announce_frequency | int(11)                                                                                        | YES  |     | NULL    |       | | relative_periodic_announce  | enum('yes','no')                                                                               | YES  |     | NULL    |       | | random_periodic_announce    | enum('yes','no')                                                                               | YES  |     | NULL    |       | | retry                       | int(11)                                                                                        | YES  |     | NULL    |       | | wrapuptime                  | int(11)                                                                                        | YES  |     | NULL    |       | | penaltymemberslimit         | int(11)                                                                                        | YES  |     | NULL    |       | | autofill                    | enum('yes','no')                                                                               | YES  |     | NULL    |       | | monitor_type                | varchar(128)                                                                                   | YES  |     | NULL    |       | | autopause                   | enum('yes','no','all')                                                                         | YES  |     | NULL    |       | | autopausedelay              | int(11)                                                                                        | YES  |     | NULL    |       | | autopausebusy               | enum('yes','no')                                                                               | YES  |     | NULL    |       | | autopauseunavail            | enum('yes','no')                                                                               | YES  |     | NULL    |       | | maxlen                      | int(11)                                                                                        | YES  |     | NULL    |       | | servicelevel                | int(11)                                                                                        | YES  |     | NULL    |       | | strategy                    | enum('ringall','leastrecent','fewestcalls','random','rrmemory','linear','wrandom','rrordered') | YES  |     | NULL    |       | | joinempty                   | varchar(128)                                                                                   | YES  |     | NULL    |       | | leavewhenempty              | varchar(128)                                                                                   | YES  |     | NULL    |       | | reportholdtime              | enum('yes','no')                                                                               | YES  |     | NULL    |       | | memberdelay                 | int(11)                                                                                        | YES  |     | NULL    |       | | weight                      | int(11)                                                                                        | YES  |     | NULL    |       | | timeoutrestart              | enum('yes','no')                                                                               | YES  |     | NULL    |       | | defaultrule                 | varchar(128)                                                                                   | YES  |     | NULL    |       | | timeoutpriority             | varchar(128)                                                                                   | YES  |     | NULL    |       | +-----------------------------+------------------------------------------------------------------------------------------------+------+-----+---------+-------+ 55 rows in set (0.00 sec) MariaDB [hpbx3]> desc queue_members; +-----------------+-------------+------+-----+---------+----------------+ | Field           | Type        | Null | Key | Default | Extra          | +-----------------+-------------+------+-----+---------+----------------+ | queue_name      | varchar(80) | NO   | PRI | NULL    |                | | interface       | varchar(80) | NO   | PRI | NULL    |                | | membername      | varchar(80) | YES  |     | NULL    |                | | state_interface | varchar(80) | YES  |     | NULL    |                | | penalty         | int(11)     | YES  |     | NULL    |                | | paused          | int(11)     | YES  |     | NULL    |                | | uniqueid        | int(11)     | NO   | UNI | NULL    | auto_increment | | wrapuptime      | int(11)     | YES  |     | NULL    |                | +-----------------+-------------+------+-----+---------+----------------+ 8 rows in set (0.00 sec) for table permissions, i just allow to the whole db: MariaDB [hpbx3]> select Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv from mysql.db; +------+-------+-----------+-------------+-------------+-------------+-------------+ | Host | Db    | User      | Select_priv | Insert_priv | Update_priv | Delete_priv | +------+-------+-----------+-------------+-------------+-------------+-------------+ | %    | hpbx3 | hpbx3_ast | Y           | Y           | Y           | Y           | +------+-------+-----------+-------------+-------------+-------------+-------------+ 1 row in set (0.00 sec) By: Chris Savinovich (csavinovich) 2019-04-05 11:45:59.678-0500 Hello Michael, after looking into this issue, I have the hunch the behavior could be caused by a bug in the version you are using (16.2.0).  Could you please try using 16.3 and let us know if the issue persist? Thanks Chris By: Michael (ringo) 2019-04-05 14:35:01.907-0500 download/recompiled and tried the latest 16.3.0  problem still happens. It happens with v16,v15,v13.21-cert3.  Keep in mind this only happens with realtime queue members added via sql 'queue_members' table.  When changing the ringinuse option on the asterisk cli, it would make sense that the action needs to change the entry for that member in the sql table.  However, I don't see a ringinuse column in that table.  So, i would assume the table is not complete.  It needs a ringinuse column would it not?  And then the appropriate sql command in the asterisk source code to accomplish the change of setting?  I'm not exactly sure i'm just trying to help. By: Chris Savinovich (csavinovich) 2019-04-05 16:00:22.871-0500 >>> So, i would assume the table is not complete. It needs a ringinuse column would it not? That's what I thought would be solved in 16.3... Okay, I will keep looking and let you know. Thanks By: Michael (ringo) 2019-04-05 16:05:08.165-0500 ya was worth a shot.  Thanks.  Looking in asterisk-16.3.0/contrib/realtime/mysql/mysql_config.sql i see no ringinuse option in the table: CREATE TABLE queue_members (    queue_name VARCHAR(80) NOT NULL,    interface VARCHAR(80) NOT NULL,    uniqueid VARCHAR(80) NOT NULL,    membername VARCHAR(80),    state_interface VARCHAR(80),    penalty INTEGER,    paused INTEGER,    PRIMARY KEY (queue_name, interface) ); By: Sean Bright (seanbright) 2021-03-23 16:06:25.835-0500 The issue is simply that the {{ringinuse}} column is not in the {{queue_members}} table. By: Michael (ringo) 2021-03-23 20:25:23.906-0500 adding column does improved for sure. ringinuse column in queue_members not found in contrib/ast-db-manage/config/* or contrib/realtime/mysql/mysql_config.sql after manually adding this column, you can configure ringinuse yes/no in the sql table, but still not on the cli.  When using the cli, setting option yes sets sql column to 'yes' however when setting option no, sql column is set to blank which still seems to set ringinuse enabled (ie. yes). By: Sean Bright (seanbright) 2021-03-24 14:47:45.219-0500 Right, so it's not just as simple as adding the column. I am not sure what a good fix is here to be honest. By: Michael (ringo) 2021-03-24 15:20:21.245-0500 Well ya, I'd say it is just as simple as adding the column.  It does work after that with all versions i tested (v13, v16, v18) when setting the ringinuse sql option through sql insert/update statement, which would be the main functionality.  Thanks.  I was just adding as a note to anyone else reading this that the CLI 'no' option doesn't work.  Secondary functionality. By: Friendly Automation (friendly-automation) 2021-03-30 04:41:32.301-0500 Change 15697 merged by Friendly Automation: app_queue: Add alembic migration to add ringinuse to queue_members. [https://gerrit.asterisk.org/c/asterisk/+/15697|https://gerrit.asterisk.org/c/asterisk/+/15697] By: Friendly Automation (friendly-automation) 2021-03-30 04:46:56.625-0500 Change 15706 merged by Joshua Colp: app_queue: Add alembic migration to add ringinuse to queue_members. [https://gerrit.asterisk.org/c/asterisk/+/15706|https://gerrit.asterisk.org/c/asterisk/+/15706] By: Friendly Automation (friendly-automation) 2021-03-30 04:47:17.187-0500 Change 15705 merged by Joshua Colp: app_queue: Add alembic migration to add ringinuse to queue_members. [https://gerrit.asterisk.org/c/asterisk/+/15705|https://gerrit.asterisk.org/c/asterisk/+/15705]