### Description
Using DB_UNIXODBC with an Azure SQL database works, however when starting Kamailio, errors are shown for :
``` 26(80403) ERROR: db_unixodbc [connection.c:219]: db_unixodbc_extract_error(): unixodbc:SQLDriverConnect=01000:3:5701:[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Changed database context to 'database-name'. ERROR: db_unixodbc [connection.c:219]: db_unixodbc_extract_error(): unixodbc:SQLDriverConnect=01000:2:5703:[unixODBC][Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Changed language setting to us_english. ```
Regarding changing the DB context, the database to be used is defined in my ```odbc.ini``` file
``` [dsnName] Driver=ODBC Driver 17 for SQL Server Description=Azure MS SQL Server Trace=No Server=tcp:azure-sql-server-name.database.windows.net,1433 Encrypt=yes TrustServerCertificate=no Connection Timeout=30 Database=databaseName ```
I'm not able to `USE databaseName` when connecting to an Azure DB. The default language is already US-English, and i'm guessing it's also Azure specific that it cannot be changed?
### Troubleshooting
I'm not sure what troubleshooting can be done with this. The DB connection works, it's just these commands that fail, but since the result is already correct, it doesn't seem to functionally matter, aside from the errors being thrown.
#### Reproduction
This can be reproduced using an Azure SQL database. Create a single database and a dedicated user (with db_owner membership), and configure the ODBC connection as above.
#### Debugging Data
I'm not sure any additional debugging information is available. I'm happy to provide anything specific requested.
#### Log Messages
``` 26(80403) ERROR: db_unixodbc [connection.c:219]: db_unixodbc_extract_error(): unixodbc:SQLDriverConnect=01000:3:5701:[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Changed database context to 'database-name'. ERROR: db_unixodbc [connection.c:219]: db_unixodbc_extract_error(): unixodbc:SQLDriverConnect=01000:2:5703:[unixODBC][Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Changed language setting to us_english. ```
### Possible Solutions
I would suspect that a parameter could be added to the DB_UNIXODBC module to make each of these behaviors (changing database and changing language) optional. Default config would retain the existing behavior, so it's just an override if desired.
### Additional Information
* **Kamailio Version** - output of `kamailio -v`
``` version: kamailio 5.1.8 (x86_64/linux) flags: STATS: Off, USE_TCP, USE_TLS, USE_SCTP, TLS_HOOKS, USE_RAW_SOCKS, DISABLE_NAGLE, USE_MCAST, DNS_IP_HACK, SHM_MEM, SHM_MMAP, PKG_MALLOC, Q_MALLOC, F_MALLOC, TLSF_MALLOC, DBG_SR_MEMORY, USE_FUTEX, FAST_LOCK-ADAPTIVE_WAIT, USE_DNS_CACHE, USE_DNS_FAILOVER, USE_NAPTR, USE_DST_BLACKLIST, HAVE_RESOLV_RES ADAPTIVE_WAIT_LOOPS=1024, MAX_RECV_BUFFER_SIZE 262144 MAX_URI_SIZE 1024, BUF_SIZE 65535, DEFAULT PKG_SIZE 8MB poll method support: poll, epoll_lt, epoll_et, sigio_rt, select. id: unknown compiled with gcc 6.3.0 ```
* **Operating System**:
``` Debian Stretch: 4.19.0-0.bpo.4-cloud-amd64 #1 SMP Debian 4.19.28-2~bpo9+1 (2019-03-27) x86_64 GNU/Linux ```
I worked around this issue (it's more a sql azure thing than kamailio) by creating a user on the dB and setting default language, db and schema on the login that was enough to get it to connect.
Be warned though using sql azure with either freetds or the ms Linux driver I experienced random hangs of kamailio related to odbc handling, was unable to find root cause and it seemed unique to sql azure as a local sql server instance worked without issue. My working theory was that the sql azure drops a connection to a node from its load balancer somewhere inside that black box but kamailio attempts to reuse that connection and hangs. Also due to how sql azure works I found query time to variable as well and based on dB size the number of open connections seems limiting. To overcome this I ended up writing a simple proxy Web API in. Net core which ran on local host And cached results to a local redis instance which gave consistent sub 10ms responses
I'm able to connect, the bug report here is more about the errors being written to the kamailio log.
I'm also experiencing hangs, and hadn't considered that this could be from the ODBC connection, so I'll look into this.
You indicate that you're working around this by proxying your query through a separate application, so I'm guessing that you're not using the Azure DB as the back end for other modules? For example, I'm using azure as the back end for the UAC and db_alias modules, where db connectivity is set in modparams. I'm not sure how I could push that through an external application.
Originally i had everything being backed by ODBC tables, but could never get the hangs to stop, usually presenting it self as an inability to auth registrations randomly. I think i also stumbled upon some hard coded MySQL syntax specific queries in various modules typically using the MySQL specific REPLACE (upsert) keyword. Getting around the lack of USE keyword validity in SQL Azure was acheived like i said by specifying on the server login and user entities rather than in the connection string.
As a result of the random nature of the hangs we re-architected to using intermediate API for our dynamic data access and for infrequently changing data (dispatcher for example) generated textdb files, when a change is submitted through the back end. At its simplest we just set a flag saying file has changed, and poll the api, if the flag is set local copy is overritten by the downloaded generated file, and the data gets reloaded via kamcmd. Since having done that all our relational data stays in sql azure, but is never directly accessed by kamailio, and have not had a single hang like we were experiencing. Like i said the odd thing was if i pointed the connection string to a local SQL server it would work perfectly, just undermined our long established SQL Azure consolidation project...
Closed #1993.
If I got it right, it seems that the solution is not about Kamailio, but settings inside the sql server for access user. If there is something kamailio shoud do, then a pull request would be good (not sure if any developer has access to SQL Azure to play with) and eventually reopen the item here.
**tl;dr:** This is a minor bug report for Kamailio, and regarding access to SQL Azure, I'd be happy to finance an Azure SQL database for testing if desired.
<hr />
The issue is about _assumed_ behavior within the DB_UNIXODBC module.
The behavior in Azure SQL that is atypical, because trying to issue a ```USE``` query gives the following error:
``` USE statement is not supported to switch between databases. Use a new connection to connect to a different database. ```
I think @nakchak may be using an Azure SQL Server (managed instance) vs an Azure SQL Database (single database). Here's my attempt and error trying to assign a default DB to a login in my Azure database:
Query: ``` Alter login kamailioRoTest with default_database = ops-qa-sqldir1 ``` Error: ``` Keyword or statement option 'default_database' is not supported in this version of SQL Server. ```
From a purely functional standpoint (i.e. to get Kamailio **working** with the Azure DB), the solution is to specify the database in the connection string (see the ODBC.ini example in my original post). The request is to have an option to just have the module use the default DB from the connection and not attempt to change to the DB once connected.
Think of it like the following if using MySQL:
``` bash > mysql -D kamailio mysql > USE kamailoio; ```
The second line is superfluous (but not problematic).
For DB_UNIXODBC, setting the DB in the ODBC string WORKS currently. It just clutters the log with errors. Thus, the bug report is not critical.
@whosgonna thank you for the clarification. If somebody can work on this issue, just re-open it (or simple create a pull request). I am happy to look to it as well.
-
Ben Kaufman -
Daniel-Constantin Mierla
-
Henning Westerholt
-
Tim Chubb