Hello,

have a look to these pages for example for further ideas how to debug it:

https://www.percona.com/blog/2016/05/16/mysql-got-an-error-reading-communication-packet-errors/

https://www.percona.com/blog/2008/08/23/how-to-track-down-the-source-of-aborted_connects/

Cheers,

Henning

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

From: Youssef Boujraf <yboujraf@by-research.be>
Sent: Monday, March 28, 2022 6:31 PM
To: Henning Westerholt <hw@gilawa.com>
Cc: Kamailio (SER) - Users Mailing List <sr-users@lists.kamailio.org>
Subject: RE: [SR-Users] kamailio - mariadb hosted on remote server - issue

Dear,

The mysql server no log file in /var/log/mysql or I don't know where mysql stores the logs.

I forgot to inform the OS is debian 11.

Best Regards,

On Monday, March 28, 2022 18:15 CEST, Henning Westerholt <hw@gilawa.com> wrote:

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"
class="MsoNormal" style="margin-left:35.4pt">
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

--
Youssef BOUJRAF | Managing Director

Direct Line: +32 (0)2 880 33 30
Mobile: +32 (0)477 94 86 89
Dubai Mobile : +971 (0)55 9928871
Fax: +32 (0)2 318 82 21
Skype: y_boujraf
Email: yboujraf@by-research.be
Web : www.by-systems.be/

BY-SYSTEMS sprl| Avenue Charles Plisnier 13 | B-1070 Anderlecht | Belgium | TVA: BE 0819.832.617

This communication, issued by BY-SYSTEMS SPRL, is confidential and we do not waive confidentiality by mistransmission. If you have received it by mistake please notify the sender immediately by reply and then delete it permanently from your system without copying it, using it for any purposes or disclosing its contents to any other person. Thank you for your cooperation. Any views expressed in this message are those of the individual sender and may not necessarily reflect the views of BY-SYSTEMS SPRL. Emails are not secure and cannot be guaranteed to be error free as they can be intercepted, amended, lost or destroyed, or contain viruses. Anyone who communicates with us by email is taken to accept these risks.