Re: [SR-Users] ?==?utf-8?q? 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 &lt;hw(a)gilawa.com&gt; 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 &lt;sr-users-bounces(a)lists.kamailio.org&gt; On Behalf Of Youssef Boujraf Sent: Monday, March 28, 2022 5:47 PM To: sr-users(a)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(a)192.168.1.41; +--------------------------------------------------------------------------------------------------------------------+ | Grants for kamailio(a)192.168.1.41                                                                                   | +--------------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO `kamailio`(a)`192.168.1.41` IDENTIFIED BY PASSWORD '*157PASSWORD' | | GRANT ALL PRIVILEGES ON `kamailio`.* TO `kamailio`(a)`192.168.1.41`                                                  | +--------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.000 sec) MariaDB [(none)]> SHOW GRANTS FOR kamailio(a)192.168.1.39; +--------------------------------------------------------------------------------------------------------------------+ | Grants for kamailio(a)192.168.1.39                                                                                   | +--------------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO `kamailio`(a)`192.168.1.39` IDENTIFIED BY PASSWORD '*157PASSWORD' | | GRANT ALL PRIVILEGES ON `kamailio`.* TO `kamailio`(a)`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(a)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.