[Home]

Summary:ASTERISK-28455: res_odbc: Connection through proxysql fails
Reporter:cagdas kopuz (cagdas)Labels:
Date Opened:2019-06-20 03:05:44Date Closed:2019-06-20 06:15:12
Priority:MinorRegression?
Status:Closed/CompleteComponents:Resources/res_odbc
Versions:13.26.0 Frequency of
Occurrence		Constant
Related
Issues:
Environment:centos 7Attachments:( 0) core-asterisk-running-2019-06-20T12-39-42+0300-brief.txt
( 1) core-asterisk-running-2019-06-20T12-39-42+0300-full.txt
( 2) core-asterisk-running-2019-06-20T12-39-42+0300-locks.txt
( 3) core-asterisk-running-2019-06-20T12-39-42+0300-thread1.txt
( 4) func_odbc_without_proxysql.txt
( 5) func_odbc_with_proxysql.txt
Description:Asterisk realtime and func_odbc can not connect proxysql server.
I tried all mysql-connector-odbc 5.0 to 8.0.
When I connect same database without proxysql everything is fine.

Asterisk gives me these errors:
[Jun 20 10:55:21] ERROR[16739]: res_odbc.c:292 ast_odbc_find_table: Unable to query database columns on connection 'proxysql'.
[Jun 20 10:55:21] ERROR[16739]: res_odbc.c:292 ast_odbc_find_table: Unable to query database columns on connection 'proxysql2'.
[Jun 20 10:55:21] WARNING[16739]: res_odbc.c:550 ast_odbc_print_errors: SQL Execute returned an error: 42S22: [MySQL][ODBC 5.1 Driver][mysqld-5.5.30]Unknown column 'data' in 'field list'
[Jun 20 10:55:21] WARNING[16739]: res_odbc.c:445 ast_odbc_prepare_and_execute: SQL Execute error -1!
*CLI> [Jun 20 10:55:21] WARNING[16739]: res_odbc.c:550 ast_odbc_print_errors: SQL Execute returned an error: 42S22: [MySQL][ODBC 5.1 Driver][mysqld-5.5.30]Unknown column 'data' in 'field list'
[Jun 20 10:55:21] WARNING[16739]: res_odbc.c:445 ast_odbc_prepare_and_execute: SQL Execute error -1!
[Jun 20 10:55:25] ERROR[16771]: res_odbc.c:292 ast_odbc_find_table: Unable to query database columns on connection 'pbxconf1'.
[Jun 20 10:55:25] WARNING[16771]: res_odbc.c:550 ast_odbc_print_errors: SQL Execute returned an error: 42000: [MySQL][ODBC 5.1 Driver][mysqld-5.5.30]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE name='startrinity'' at line 1
[Jun 20 10:55:25] WARNING[16771]: res_odbc.c:445 ast_odbc_prepare_and_execute: SQL Execute error -1!


realtime reloading:
[Jun 20 11:17:28] DEBUG[18214]: res_odbc.c:982 _ast_odbc_request_obj2: Reusing ODBC handle 0x7fd648001ac8 from class 'pbxconf1'
[Jun 20 11:17:28] WARNING[18214]: res_odbc.c:550 ast_odbc_print_errors: SQL Execute returned an error: 08S01: [MySQL][ODBC 5.1 Driver][mysqld-5.5.30]Lost connection to MySQL server during query
[Jun 20 11:17:28] WARNING[18214]: res_odbc.c:445 ast_odbc_prepare_and_execute: SQL Execute error -1!
[Jun 20 11:17:28] WARNING[18214]: res_config_odbc.c:974 config_odbc: SQL select error! [SELECT MAX(LENGTH(var_val)) FROM dialplan WHERE filename='extensions_realtime.include']
[Jun 20 11:17:28] DEBUG[18214]: res_odbc.c:825 ast_odbc_release_obj: Releasing ODBC handle 0x7fd648001ac8 into pool



odbcinst.ini
[MySQL ODBC 5.1 Driver]
Driver=/usr/lib64/libmyodbc5.so
UsageCount=1
-------------------------
odbc.ini
[proxysql]
Description = pbxproxy
Driver = MySQL ODBC 5.1 Driver
Server = 10.10.10.221
Port = 6033
User = pbxuser
Password = passw0rd
Database = pbxlogs
Option = 3
--------------------------
res_odbc.conf
[proxysql]
enabled => yes
dsn => proxysql
username => pbxuser
password => passw0rd
pre-connect => yes
-----------------------
asterisk cli: odbc show all
ODBC DSN Settings

 Name:   proxysql
 DSN:    proxysql
   Last connection attempt: 1970-01-01 02:00:00
   Number of active connections: 1 (out of 1)
   Logging: Disabled


I can connect proxysql from host with isql command or php-pdo.

root@netsantral-test modules]# isql proxysql pbxuser passw0rd -vvv
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> SELECT start,linkedid FROM pbxlogs.cdr limit 1;
+--------------------+---------------------------------+
| start              | linkedid                        |
+--------------------+---------------------------------+
| 2019-06-20 10:52:09| 1561017129.475827               |
+--------------------+---------------------------------+
SQLRowCount returns 1
1 rows fetched
Comments:By: Asterisk Team (asteriskteam) 2019-06-20 03:05:46.384-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: Asterisk Team (asteriskteam) 2019-06-20 03:05:48.653-0500

The severity of this issue has been automatically downgraded from "Blocker" to "Major". The "Blocker" severity is reserved for issues which have been determined to block the next release of Asterisk. This severity can only be set by privileged users. If this issue is deemed to block the next release it will be updated accordingly during the triage process.
By: cagdas kopuz (cagdas) 2019-06-20 03:44:30.849-0500

With proxysql, asterisk can not continue working to " res_odbc.c:825 ast_odbc_release_obj: Releasing ODBC handle 0x17b6628 into pool"
By: Joshua C. Colp (jcolp) 2019-06-20 04:23:50.975-0500

We suspect that you have a deadlock occurring within Asterisk. Please follow the instructions on the wiki [1] for obtaining debug relevant to a deadlock. Once you have that information, attach it to the issue. Be sure the instructions are followed exactly as the debug may otherwise not be useful.

Thanks!

[1] https://wiki.asterisk.org/wiki/display/AST/Getting+a+Backtrace#GettingaBacktrace-GettingInformationForADeadlock


By: Joshua C. Colp (jcolp) 2019-06-20 05:06:00.190-0500

The problem seems to be down in the MySQL library that the ODBC connector is using. It appears to be deadlocking on the "SQLCloseCursor" call. Why that is I do not know. Our specific usage isn't that complex, so it may be some weird interaction in the library between it and proxysql, or our specific calls are exposing a bug in it.

Specifically this is in an error case, so that may contribute to it.
By: Joshua C. Colp (jcolp) 2019-06-20 06:15:12.385-0500

I'm closing this out as proxysql has not been tested and is not really supported for use with Asterisk. While it should work transparently as you can see it is not. Making things work would be considered a new feature.

If this is something you would like to explore and add support for then this issue can be reopened.