upgrading from FS1.6 mysql5.1 to FS1.10.11 mysql 8.3. sip_profile loading fails

[prady77]

Describe the bug Trying to upgrade FS1.6 odbc driver 5.1 to FS1.10.11 odbc driver 8.3. sip_profile loading fails, with odbc error.

2024-04-11 17:41:12.671438 0.00% [INFO] sofia.c:6187 Setting MAX Auth Validity to 0 Attempts
2024-04-11 17:41:12.671582 0.00% [NOTICE] sofia.c:6347 Connecting ODBC Profile external [sofia_reg_external]
2024-04-11 17:41:12.722420 0.00% [ERR] switch_odbc.c:529 ERR: [delete from sip_registrations where sub_host is null and hostname='nxpdev002fsw101' and network_ip like '%' and network_port like '%' and sip_username like '%' and mwi_user  like '%' and mwi_host like '%' and orig_server_host like '%' and orig_hostname like '%';delete from sip_registrations where sub_host is null and hostname='nxpdev002fsw101' and network_ip like '%' and network_port like '%' and sip_username like '%' and mwi_user  like '%' and mwi_host like '%' and orig_server_host like '%' and orig_hostname like '%']
[STATE: 42000 CODE 1064 ERROR: [MySQL][ODBC 8.3(a) Driver][mysqld-8.0.36-0ubuntu0.22.04.1]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 'delete from sip_registrations where sub_host is null and hostname='nxpdev002fsw1' at line 1
]
2024-04-11 17:41:12.722478 0.00% [ERR] switch_core_sqldb.c:728 [db="freeswitch";type="odbc"user="freeswitch";pass="freeswitchpw"] ODBC SQL ERR [STATE: 42000 CODE 1064 ERROR: [MySQL][ODBC 8.3(a) Driver][mysqld-8.0.36-0ubuntu0.22.04.1]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 'delete from sip_registrations where sub_host is null and hostname='nxpdev002fsw1' at line 1
]
delete from sip_registrations where sub_host is null and hostname='nxpdev002fsw101' and network_ip like '%' and network_port like '%' and sip_username like '%' and mwi_user  like '%' and mwi_host like '%' and orig_server_host like '%' and orig_hostname like '%';delete from sip_registrations where sub_host is null and hostname='nxpdev002fsw101' and network_ip like '%' and network_port like '%' and sip_username like '%' and mwi_user  like '%' and mwi_host like '%' and orig_server_host like '%' and orig_hostname like '%'
2024-04-11 17:41:12.722491 0.00% [CRIT] sofia_glue.c:2679 GREAT SCOTT!!! Cannot execute batched statements! [STATE: 42000 CODE 1064 ERROR: [MySQL][ODBC 8.3(a) Driver][mysqld-8.0.36-0ubuntu0.22.04.1]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 'delete from sip_registrations where sub_host is null and hostname='nxpdev002fsw1' at line 1
]
If you are using mysql, make sure you are using MYODBC 3.51.18 or higher and enable FLAG_MULTI_STATEMENTS
2024-04-11 17:41:12.722520 0.00% [CRIT] sofia.c:3165 Cannot Open SQL Database [external]!
2024-04-11 17:41:13.672311 0.00% [WARNING] sofia.c:5328 rtp-timeout-sec deprecated use media_timeout variable.
2024-04-11 17:41:13.672322 0.00% [WARNING] sofia.c:5335 rtp-hold-timeout-sec deprecated use media_hold_timeout variable.

To Reproduce Steps to reproduce the behavior:

Adding to the profile internal.xml: <param name="odbc-dsn" value="freeswitch:freeswitch:xxxxxxxxxx"/> sofia profile internal restart and getting above errors same problem with external profile core-db works, problem is only with sip_profiles. FreeSWITCH (Version 1.10.11 -release 64bit)Ubuntu 22..04 ODBC 8.3 Driver FLAG_MULTI_STATEMENTS is enabled by OPTION=67108864

root@nxpdev002fsw101:/usr/local/freeswitch/conf/sip_profiles# cat /etc/odbc.ini
[ODBC Data Sources]
myodbc8w     = MyODBC 8.3 UNICODE Driver DSN
myodbc8a     = MyODBC 8.3 ANSI Driver DSN

[freeswitch]
Driver       = /usr/lib/x86_64-linux-gnu/odbc/libmyodbc8a.so
Description  = Connector/ODBC 8.3 UNICODE Driver DSN
SERVER       = 10.80.64.75
PORT         = 3306
USER         = freeswitch
Password     = xxxxxx
Database     = freeswitch
OPTION       = 67108864

[freeswitch]
Driver       = /usr/lib/x86_64-linux-gnu/odbc/libmyodbc8w.so
Description  = Connector/ODBC 8.3 ANSI Driver DSN
SERVER       = 10.80.64.75
PORT         = 3306
USER         = freeswitch
Password     = xxxxxx
Database     = freeswitch
OPTION       = 67108864
root@nxpdev002fsw101:/usr/local/freeswitch/conf/sip_profiles# cat /etc/odbcinst.ini
[MySQL ODBC 8.3 Unicode Driver]
DRIVER=/usr/lib/x86_64-linux-gnu/odbc/libmyodbc8w.so
SETUP=/usr/lib/x86_64-linux-gnu/odbc/libmyodbc8S.so
UsageCount   = 1
FileUsage    = 1
Threading    = 0

[MySQL ODBC 8.3 ANSI Driver]
DRIVER=/usr/lib/x86_64-linux-gnu/odbc/libmyodbc8a.so
SETUP=/usr/lib/x86_64-linux-gnu/odbc/libmyodbc8S.so
UsageCount   = 1
FileUsage    = 1
Threading    = 0

root@nxpdev002fsw101:/usr/local/freeswitch/conf/sip_profiles#

This problem prevents from upgrading to newer FS versions (upgrading the components)

[andywolk]

having the old mysql database dump here would help

[prady77]

This is a fresh installation with below versions: we need to upgrade FS as we need to install commercial "mod_amd" module from signalwire

FreeSWITCH (Version 1.10.11 -release 64bit)Ubuntu 22..04 ODBC 8.3 Driver FLAG_MULTI_STATEMENTS is enabled by OPTION=67108864 MySQL Server: mysql Ver 8.0.36-0ubuntu0.22.04.1 for Linux on x86_64 ((Ubuntu))

We are trying to upgrade the infrastructure from

FreeSWITCH 1.6 ODBC 5.1.13 Driver MySQL Server: 5.7

[prady77]

@andywolk any suggestions or recommendations?

[andywolk]

Try mod_mariadb instead of odbc

[prady77]

adding mod_mariadb, sip_profiles loaded successfully and working for FS 1.10.11 with MySql 8.3.