[OpenSIPS-Users] Opensip 2.4.5 Subscriber module on ODBC (MSSQL 2016 )

Wed Jun 5 11:17:58 EDT 2019

Hello Bogdan,

Thank you for the explanation.  The patch you did worked.  Although I would
think the "fault" would be on my part as I didn't realize the table field
type was changed to BIGINT on the "new" table.  I've checked the "version"
table on my old database (that I used for 1.11), the field type was "INT".
I guess the "conversion" program that I used (to convert MySQL tables to
MSSQL), it used BIGINT instead.  

With that said, I guess, now I know what "invalid type (1) or nul (0)" error
means, I can go ahead try to make sure that all field in the table (which I
get this error), is same as the old tables.

Thank you very much for your help!

Frank

From: Bogdan-Andrei Iancu [mailto:bogdan at opensips.org] 
Sent: Wednesday, June 5, 2019 7:12 AM
To: Frank Lee <Frank at wtild.com>; 'OpenSIPS users mailling list'
<users at lists.opensips.org>
Subject: Re: [OpenSIPS-Users] Opensip 2.4.5 Subscriber module on ODBC (MSSQL
2016 )

Hi Frank,

Usually we look for the most expected types (according to the DB schema we
use)- there is not rule to try to cover all the types (maybe some of them
have no meaning in conjunction with OpenSIPS).

Give it a try and let me know. If it solves the problem, I can do a backport
to 3.0 and 2.4 .

Regards,

Bogdan-Andrei Iancu

OpenSIPS Founder and Developer
  https://www.opensips-solutions.com
OpenSIPS Summit 2019
  https://www.opensips.org/events/Summit-2019Amsterdam/

On 06/05/2019 04:22 PM, Frank Lee wrote:

Hello Bogdan,

Thank you very  much for your help.  I will try out and see about the "int"
type and my driver.  So mostly in the code, when this error happens, are you
guys looking for "int" type or do you check all type of all fields (e.g.
Char has to be the right length or bit field)

Thank you!

From: Bogdan-Andrei Iancu [mailto:bogdan at opensips.org] 
Sent: Tuesday, June 4, 2019 9:42 AM
To: Frank Lee  <mailto:Frank at wtild.com> <Frank at wtild.com>; 'OpenSIPS users
mailling list'  <mailto:users at lists.opensips.org> <users at lists.opensips.org>
Subject: Re: [OpenSIPS-Users] Opensip 2.4.5 Subscriber module on ODBC (MSSQL
2016 )

Hi Frank,

The official DB schema is here :
    https://github.com/OpenSIPS/opensips/tree/master/db/schema

We use that in order to generate the DB engine specific schema, see:
    https://github.com/OpenSIPS/opensips/tree/master/scripts

But as it is not native, there is nothing for MS SQL .

Are you able to pull the latest master and check my fix
(https://github.com/OpenSIPS/opensips/commit/dafa8f6a50958e1c6905621cceb2457
f94163cc5 ) ?

As I said, nothing changed in the OpenSIPS unixodbc support - but maybe
something changed in the unixodbc lib or in the MS SQL driver.

Regards,

Bogdan-Andrei Iancu

OpenSIPS Founder and Developer
  https://www.opensips-solutions.com
OpenSIPS Summit 2019
  https://www.opensips.org/events/Summit-2019Amsterdam/

On 06/03/2019 04:55 PM, Frank Lee wrote:

Hello Bogdan,

Thank you very much for looking into this for me.  So you guys actually
check the data type of each field before query is done?  Do you know of
anyone who has a TSQL script that generate all the tables for Opensips
2.4.5?   I do see you guys have a link that has the table structure for
opensips tables:

https://opensips.org/html/docs/db/db-schema-devel.html#AEN9331

Is this information "complete" and is the data field type "correct" for
2.4.5?  Because it seesm the table field type was used for 1.11 is
"different" from the 2.4.5?  And is that why my Microsoft sql server table
that worked for 1.11 no longer works for 2.4.5?

Frank

From: Bogdan-Andrei Iancu [mailto:bogdan at opensips.org] 
Sent: Monday, June 3, 2019 12:27 AM
To: OpenSIPS users mailling list  <mailto:users at lists.opensips.org>
<users at lists.opensips.org>; Frank Lee  <mailto:frank at wtild.com>
<frank at wtild.com>
Subject: Re: [OpenSIPS-Users] Opensip 2.4.5 Subscriber module on ODBC (MSSQL
2016 )

Hi Frank,

There were no changes in the db_unixodbc driver in OpenSIPS for some time.
Maybe something changed in the unixodbc driver for MS SQL.

Based on the this log:
   core:db_table_version: invalid type (1) or nul (0)

I see that that the "table_version" from the version table is returned as
BIGINT, while OpenSIPS expects INT. Of course, this is something we can
quickly adjust.

In regards to the 'avp_db_query', could you post the level 3 logs
corresponding to the query ? Maybe it will give us a clue.

Regards,

Bogdan-Andrei Iancu

OpenSIPS Founder and Developer
  https://www.opensips-solutions.com
OpenSIPS Summit 2019
  https://www.opensips.org/events/Summit-2019Amsterdam/

On 05/25/2019 07:33 AM, Frank Lee wrote:

Hello, 

Was there some major change between version 1.11 and 2.4.5 on supporting of
the ODBC connection of Microsoft SQL Server?  It seems I had 1.11 running
fine on SQL server (or at least partial table on sql server such as location
table).  But now I use 2.4.5  it seems all the module that uses odbc
connector instead of the mysql connector it will have the error below

core:db_table_version: invalid type (1) or nul (0)

Thus, I changed all the module to use mysql connector (except the AVPOPS
Module because I have custom query in the script which need to query the
Microsoft sql server) which made Opensips start out fine.  But as soon as it
uses the avp_db_query in the script it seems it would not work.  Alhtough I
don't see any error in the syslog file (although I had log_level set to 3,
do I need to set it higher?)

Anyway, it just seems all the query/support to Microsoft SQL server doesn't
run right in 2.4.5 vs 1.11.

Is there some dependency that I am suppose to install which is different?  

I had my 1.11 run on Debian 7.  Now we are moving to Azure cloud, I had run
Ubuntu 18.04 since Debian 7 is not support on Azure.

I had install odbc version 2.3.4 and also tdsodbc 1.00

Are these not to correct version to interface between 2.4.5 and Microsoft
sql server (I tried both version msssql 2008 and 2016).

Anyway, any  pointer or help will be greatly appreciated!

Thank you!

From: Frank Lee [mailto:frank at wtild.com] 
Sent: Friday, May 24, 2019 10:58 AM
To: 'users at lists.opensips.org <mailto:users at lists.opensips.org> '
<mailto:users at lists.opensips.org> <users at lists.opensips.org>
Subject: Opensip 2.4.5 Subscriber module on ODBC (MSSQL 2016 )

Hello,  

This is the first time I use this mail message, so let me know if this is
not the correct way of posting question.

Anyway, I am running Opensips 2.4.5 and when I use MYSQL database on all
module, opensips runs fine.  But as soon as I switch over the database
source to use ODBC driver (which is linked to Microsoft SQL server 2016), I
get this error.  What is It mean?

May 24 17:36:48 osipibDprimary /usr/local/sbin/opensips[13534]:
ERROR:core:db_table_version: invalid type (1) or nul (0) version columns for
subscriber

May 24 17:36:48 osipibDprimary /usr/local/sbin/opensips[13534]:
ERROR:core:db_check_table_version: querying version for table subscriber

May 24 17:36:48 osipibDprimary /usr/local/sbin/opensips[13534]:
ERROR:uri:mod_init: Invalid table version.

I first created the MySQL table usinmg the command:

opensipsdbctl create

And then I transfer the tables to MSSQL using the:

Microsoft SQL Server Migration Assistant 8.1 for MySQL

All the table named corrected and all the index was created too.

Thank you very much!

Frank

_______________________________________________
Users mailing list
Users at lists.opensips.org <mailto:Users at lists.opensips.org> 
http://lists.opensips.org/cgi-bin/mailman/listinfo/users

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.opensips.org/pipermail/users/attachments/20190605/ee8b602c/attachment-0001.html>