Hello,

 

yes, DB connection is usually straight forward.

 

Do you see any errors in the mysql server logs? Any errors visible with some network debugging tools?

 

Cheers,

 

Henning

 

--

Henning Westerholt – https://skalatan.de/blog/

Kamailio services – https://gilawa.com

 

From: sr-users <sr-users-bounces@lists.kamailio.org> On Behalf Of Youssef Boujraf
Sent: Monday, March 28, 2022 5:47 PM
To: sr-users@lists.kamailio.org
Subject: [SR-Users] kamailio - mariadb hosted on remote server - issue

 

Dear all,

I don't belive to connect Kamailio to a remote Mariadb will take too much time.


STEP 1 : KAMAILIO :
--------------------------------

$ sudo kamailio -v
version: kamailio 5.5.4 (x86_64/linux)
flags: USE_TCP, USE_TLS, USE_SCTP, TLS_HOOKS, USE_RAW_SOCKS, DISABLE_NAGLE, USE_MCAST, DNS_IP_HACK, 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_BLOCKLIST, HAVE_RESOLV_RES, TLS_PTHREAD_MUTEX_SHARED
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 10.2.1


STEP 2 : Set the kamctlrc config
------------------------------------------------

KAMAILIO IP : 192.168.1.41
MARIADB IP : 192.168.1.39

- /etc/kamailio/kamctlrc

SIP_DOMAIN=example.be
DBENGINE=MYSQL
DBHOST=192.168.1.39 // where the mariadb is installed
DBNAME=kamailio
DBRWUSER="kamailio"
DBRWPW="XX"
DBROUSER="kamailioro"
DBROPW="XX"
DBACCESSHOST=192.168.1.41 // where kamctl is launched (here kamailio srv)
DBROOTUSER="root"
DBROOTPW="kamailio"
CHARSET="latin1"


STEP 3 : MARIADB : hosted on different srv than kamailio
--------------------------------------------------------------------------------------

IP : 192.168.1.39

- /etc/mysql/mariadb.conf.d/50-server.cnf

# this is only for the mysqld standalone daemon
[mysqld]

# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address            = 127.0.0.1
bind-address            = 0.0.0.0



STEP 4 : TEST : Reote connection with the DB
----------------------------------------------------------------------

REMOTE IP : 192.168.1.41
DB IP : 192.168.1.39
 


$ sudo mysql -u root -h 192.168.1.39 -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 298
Server version: 10.5.15-MariaDB-1:10.5.15+maria~bullseye mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

The connection is established with success.


STEP 5 : KAMAILIO DB : creation
----------------------------------------------------

$ sudo kamdbctl create
-e \E[37;33mINFO: creating database kamailio ...
-e \E[37;33mINFO: granting privileges to database kamailio ...
-e \E[37;33mINFO: creating standard tables into kamailio ...
-e \E[37;33mINFO: Core Kamailio tables successfully created.
Create the presence related tables? (y/n): y
-e \E[37;33mINFO: creating presence tables into kamailio ...
-e \E[37;33mINFO: Presence tables successfully created.
Create the tables for imc cpl siptrace domainpolicy carrierroute
                drouting userblocklist htable purple uac pipelimit mtree sca mohqueue
                rtpproxy rtpengine secfilter? (y/n): y
-e \E[37;33mINFO: creating extra tables into kamailio ...
-e \E[37;33mINFO: Extra tables successfully created.
Create the tables for uid_auth_db uid_avp_db uid_domain uid_gflags
                uid_uri_db? (y/n): y
-e \E[37;33mINFO: creating uid tables into kamailio ...
-e \E[37;33mINFO: UID tables successfully created.


STEP 6 : DB : verify the users creation and rights
--------------------------------------------------------------------------

MariaDB [(none)]> select user, host from mysql.user;
+-------------+--------------+
| User        | Host         |
+-------------+--------------+
| kamailio    | 192.168.1.39 |
| kamailioro  | 192.168.1.39 |
| kamailio    | 192.168.1.41 |
| kamailioro  | 192.168.1.41 |
| root        | 192.168.1.41 |
| kamailio    | localhost    |
| kamailioro  | localhost    |
| mariadb.sys | localhost    |
| mysql       | localhost    |
| root        | localhost    |
+-------------+--------------+
10 rows in set (0.001 sec)

MariaDB [(none)]> SHOW GRANTS FOR kamailio@192.168.1.41;
+--------------------------------------------------------------------------------------------------------------------+
| Grants for kamailio@192.168.1.41                                                                                   |
+--------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `kamailio`@`192.168.1.41` IDENTIFIED BY PASSWORD '*157PASSWORD' |
| GRANT ALL PRIVILEGES ON `kamailio`.* TO `kamailio`@`192.168.1.41`                                                  |
+--------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)

MariaDB [(none)]> SHOW GRANTS FOR kamailio@192.168.1.39;
+--------------------------------------------------------------------------------------------------------------------+
| Grants for kamailio@192.168.1.39                                                                                   |
+--------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `kamailio`@`192.168.1.39` IDENTIFIED BY PASSWORD '*157PASSWORD' |
| GRANT ALL PRIVILEGES ON `kamailio`.* TO `kamailio`@`192.168.1.39`                                                  |
+--------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)

MariaDB [(none)]> SHOW GRANTS FOR kamailio@localhost;
+-----------------------------------------------------------------------------------------------------------------+
| Grants for kamailio@localhost                                                                                   |
+-----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `kamailio`@`localhost` IDENTIFIED BY PASSWORD '*157PASSWORD' |
| GRANT ALL PRIVILEGES ON `kamailio`.* TO `kamailio`@`localhost`                                                  |
+-----------------------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)

STEP 7 : ADDRESS TABLE : add new ip address

$ sudo systemctl restart kamailio
sipsecure@kamailio:/etc$ sudo kamctl address add 0 192.168.1.26 32 0 HP
-e \E[37;33mINFO: execute '/sbin/kamctl address reload' to synchronize cache and database

 


STEP 8 : DB : verify if the new ip address is created ? Yes

MariaDB [(none)]> select * from kamailio.address;
+----+-----+--------------+------+------+------+
| id | grp | ip_addr      | mask | port | tag  |
+----+-----+--------------+------+------+------+
|  1 |   0 | 192.168.1.26 |   32 |    0 | HP   |
+----+-----+--------------+------+------+------+
1 row in set (0.000 sec)


STEP 9 : Reload the content of the DB into kamailio. It failed
----------------------------------------------------------------------------------------

The host: '192.168.1.41' is kamailio (remote client)

$ sudo kamctl address reload
{
  "jsonrpc":  "2.0",
  "error":  {
    "code": 500,
    "message":  "Reload failed."
  },
  "id": 12397
}

 

In /var/log/daemon.log

Mar 28 17:09:49 db04srv mariadbd[2430]: 2022-03-28 17:09:49 281 [Warning] Aborted connection 281 to db: 'kamailio' user: 'kamailio' host: '192.168.1.41' (Got an error reading communication packets)
Mar 28 17:09:49 db04srv mariadbd[2430]: 2022-03-28 17:09:49 273 [Warning] Aborted connection 273 to db: 'kamailio' user: 'kamailio' host: '192.168.1.41' (Got an error reading communication packets)
Mar 28 17:09:49 db04srv mariadbd[2430]: 2022-03-28 17:09:49 295 [Warning] Aborted connection 295 to db: 'kamailio' user: 'kamailio' host: '192.168.1.41' (Got an error reading communication packets)
Mar 28 17:09:49 db04srv mariadbd[2430]: 2022-03-28 17:09:49 264 [Warning] Aborted connection 264 to db: 'kamailio' user: 'kamailio' host: '192.168.1.41' (Got an error reading communication packets)


Now, if I am trying to restart "kamailio" service, I got an error :

Mar 28 17:36:30 kamailio /usr/sbin/kamailio[14196]: DEBUG: <core> [core/sr_module.c:940]: init_mod(): permissions
Mar 28 17:36:30 kamailio /usr/sbin/kamailio[14196]: DEBUG: permissions [address.c:179]: reload_address_db_table(): Number of rows in address table: 1
Mar 28 17:36:30 kamailio /usr/sbin/kamailio[14196]: DEBUG: permissions [address.c:191]: reload_address_db_table(): failure during checks of database value 1 (group) in address table
Mar 28 17:36:30 kamailio /usr/sbin/kamailio[14196]: ERROR: permissions [address.c:233]: reload_address_db_table(): database problem - invalid record
Mar 28 17:36:30 kamailio /usr/sbin/kamailio[14196]: CRITICAL: permissions [address.c:536]: init_addresses(): reload of address table failed
Mar 28 17:36:30 kamailio /usr/sbin/kamailio[14196]: ERROR: permissions [permissions.c:657]: mod_init(): failed to initialize the allow_address function
Mar 28 17:36:30 kamailio /usr/sbin/kamailio[14196]: ERROR: <core> [core/sr_module.c:942]: init_mod(): Error while initializing module permissions (/usr/lib/x86_64-linux-gnu/kamailio/modules/permissions.so)
Mar 28 17:36:30 kamailio kamailio: DEBUG: <core> [core/cfg.y:1860]: yyparse(): loading module permissions.so
Mar 28 17:36:30 kamailio kamailio: DEBUG: <core> [core/sr_module.c:513]: ksr_locate_module(): found module to load </usr/lib/x86_64-linux-gnu/kamailio/modules/permissions.so>
Mar 28 17:36:30 kamailio kamailio: DEBUG: <core> [core/sr_module.c:563]: load_module(): trying to load </usr/lib/x86_64-linux-gnu/kamailio/modules/permissions.so>
Mar 28 17:36:30 kamailio kamailio: DEBUG: <core> [core/kemi.c:3090]: sr_kemi_modules_add(): adding module: permissions
Mar 28 17:36:30 kamailio kamailio: DEBUG: <core> [core/cfg.lex:1963]: pp_define(): defining id: MOD_permissions
Mar 28 17:36:30 kamailio kamailio: DEBUG: <core> [core/modparam.c:112]: set_mod_param_regex(): 'permissions' matches module 'permissions'
Mar 28 17:36:30 kamailio kamailio: DEBUG: <core> [core/sr_module.c:811]: find_param_export(): found <db_url> in module permissions [/usr/lib/x86_64-linux-gnu/kamailio/modules/permissions.so]
Mar 28 17:36:30 kamailio kamailio: DEBUG: <core> [core/modparam.c:128]: set_mod_param_regex(): found <db_url> in module permissions [/usr/lib/x86_64-linux-gnu/kamailio/modules/permissions.so]
Mar 28 17:36:30 kamailio kamailio: DEBUG: <core> [core/modparam.c:112]: set_mod_param_regex(): 'permissions' matches module 'permissions'
Mar 28 17:36:30 kamailio kamailio: DEBUG: <core> [core/sr_module.c:811]: find_param_export(): found <load_backends> in module permissions [/usr/lib/x86_64-linux-gnu/kamailio/modules/permissions.so]
Mar 28 17:36:30 kamailio kamailio: DEBUG: <core> [core/modparam.c:128]: set_mod_param_regex(): found <load_backends> in module permissions [/usr/lib/x86_64-linux-gnu/kamailio/modules/permissions.so]
Mar 28 17:36:31 kamailio kamailio: DEBUG: <core> [core/sr_module.c:719]: find_mod_export_record(): found export of <allow_source_address> in module permissions [/usr/lib/x86_64-linux-gnu/kamailio/modules/permissions.so]
Mar 28 17:36:31 kamailio kamailio: DEBUG: <core> [core/sr_module.c:719]: find_mod_export_record(): found export of <allow_source_address_group> in module permissions [/usr/lib/x86_64-linux-gnu/kamailio/modules/permissions.so

If I remove the record from the database manually, I can restart kamailio but no way to add any address.

Thanks if someone could highlight my path.

Best Regards,

--
Youssef