25 Jun
2019
25 Jun
'19
3:19 p.m.
### 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
```
--
You are receiving this because you are subscribed to this thread.
Reply to this email directly or view it on GitHub:
https://github.com/kamailio/kamailio/issues/1993
26 Jun
26 Jun
4:16 a.m.
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
--
You are receiving this because you are subscribed to this thread.
Reply to this email directly or view it on GitHub:
https://github.com/kamailio/kamailio/issues/1993#issuecomment-505771401
8:36 a.m.
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.
--
You are receiving this because you are subscribed to this thread.
Reply to this email directly or view it on GitHub:
https://github.com/kamailio/kamailio/issues/1993#issuecomment-505856110
10:25 a.m.
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...
--
You are receiving this because you are subscribed to this thread.
Reply to this email directly or view it on GitHub:
https://github.com/kamailio/kamailio/issues/1993#issuecomment-505899274
3 Jul
3 Jul
10:48 a.m.
Closed #1993.
--
You are receiving this because you are subscribed to this thread.
Reply to this email directly or view it on GitHub:
https://github.com/kamailio/kamailio/issues/1993#event-2457847396
10:48 a.m.
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.
--
You are receiving this because you are subscribed to this thread.
Reply to this email directly or view it on GitHub:
https://github.com/kamailio/kamailio/issues/1993#issuecomment-508125595
12:44 p.m.
**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.
--
You are receiving this because you are subscribed to this thread.
Reply to this email directly or view it on GitHub:
https://github.com/kamailio/kamailio/issues/1993#issuecomment-508170051
1:01 p.m.
@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.
--
You are receiving this because you are subscribed to this thread.
Reply to this email directly or view it on GitHub:
https://github.com/kamailio/kamailio/issues/1993#issuecomment-508175930
1894
days inactive
1902
days old
7 comments
4 participants
participants (4)
-
Ben Kaufman -
Daniel-Constantin Mierla
-
Henning Westerholt
-
Tim Chubb