[OpenSIPS-Users] /usr/sbin/opensips[7617]: ERROR:auth_db:get_ha1: failed to query database - Errors

[Shah Hussain Khattak]

Hi Folks,

I recently set up OpenSIPs 3.2 on AWS. All the installation and control panel setup went fine. I am facing two issues and was looking for some help to further troubleshoot it.

1 - When I try to configure the users via the OpenSIPs control panel, I get following error when I click on Register:

 Inserting user record into DB failed: Array ( [0] => 42S22 [1] => 1054 [2] => Unknown column 'ha1b' in 'field list' )

I have no idea what is causing this error.

2 - I am able to add users via OpenSIPs CLI:

opensips-cli -x user add alice at my.pub.lic.ip 123456

but when alice is trying to register, I am seeing the following errors in the syslog file:

Jul 27 05:35:17 ip-172-31-46-21 /usr/sbin/opensips[7618]: INFO:db_mysql:switch_state_to_disconnected: disconnect event for 0x7f51bb655e10
Jul 27 05:35:17 ip-172-31-46-21 /usr/sbin/opensips[7618]: INFO:db_mysql:reset_all_statements: resetting all statements on connection: (0x7f51bb6566b0) 0x7f51bb655e10
Jul 27 05:35:17 ip-172-31-46-21 /usr/sbin/opensips[7618]: INFO:db_mysql:connect_with_retry: re-connected successful for 0x7f51bb655e10
Jul 27 05:35:17 ip-172-31-46-21 /usr/sbin/opensips[7618]: INFO:db_mysql:db_mysql_do_prepared_query: reconnected to mysql server -> re-init the statement
Jul 27 05:35:17 ip-172-31-46-21 /usr/sbin/opensips[7618]: ERROR:db_mysql:db_mysql_store_result: driver error: Commands out of sync; you can't run this command now
Jul 27 05:35:17 ip-172-31-46-21 /usr/sbin/opensips[7618]: ERROR:auth_db:get_ha1: failed to query database
Jul 27 05:35:18 ip-172-31-46-21 /usr/sbin/opensips[7615]: ERROR:db_mysql:db_mysql_store_result: driver error: Commands out of sync; you can't run this command now
Jul 27 05:35:18 ip-172-31-46-21 /usr/sbin/opensips[7615]: ERROR:auth_db:get_ha1: failed to query database
Jul 27 05:35:18 ip-172-31-46-21 /usr/sbin/opensips[7617]: ERROR:db_mysql:db_mysql_store_result: driver error: Commands out of sync; you can't run this command now
Jul 27 05:35:18 ip-172-31-46-21 /usr/sbin/opensips[7617]: ERROR:auth_db:get_ha1: failed to query database

I am using 'opensips' user for the MySQL database, I can log in with opensips user to the database:

MariaDB [(none)]> SELECT user FROM mysql. user;
+----------+
| user     |
+----------+
| opensips |
| root     |
+----------+
2 rows in set (0.000 sec)

MariaDB [(none)]> exit

root at ip-172-31-46-21:/etc/opensips# mysql -u opensips -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 251
Server version: 10.3.29-MariaDB-0+deb10u1 Debian 10

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)]>

 and the URL I have specified in the opensips.cfg file is:


#### USeR LOCation module
loadmodule "usrloc.so"
modparam("usrloc", "nat_bflag", "NAT")
modparam("usrloc", "working_mode_preset", "single-instance-sql-write-back")
modparam("usrloc", "db_url",
        "mysql://opensips:opensipsrw@localhost/opensips") # CUSTOMIZE ME


>From socket perspective, the MySQL is binded to local host 127.0.0.1 and opensips is binded to my VM private IP:

root at ip-172-31-46-21:/etc/opensips# netstat -natup | grep mysql
tcp        0      0 127.0.0.1:3306          0.0.0.0:*               LISTEN      7700/mysqld
root at ip-172-31-46-21:/etc/opensips# netstat -natup | grep opensips
tcp        0      0 172.31.46.21:5060       0.0.0.0:*               LISTEN      7609/opensips
tcp        0      0 0.0.0.0:8888            0.0.0.0:*               LISTEN      7611/opensips
udp        0      0 172.31.46.21:5060       0.0.0.0:*                           7609/opensips

my opensips-cli.cfg contains:


root at ip-172-31-46-21:~# more ~/.opensips-cli.cfg
[default]
log_level: WARNING
prompt_name: cli
prompt_intro: Welcome to CLI!
prompt_emptyline_repeat_cmd: False
history_file: ~/.opensips-cli.history
history_file_size: 1000
output_type: pretty-print
communication_type: fifo
fifo_file: /tmp/opensips_fifo
domain: opensips.org
#database_modules: acc clusterer dialog dialplan dispatcher domain rtpproxy usrloc
database_modules: ALL

#database_admin_url: postgres://root@localhost
database_admin_url: mysql://root@localhost

root at ip-172-31-46-21:~#


I am looking for some help, how can I troubleshoot the above two mentioned issues. Sorry if it is too basic a question as I am new to OpenSIPs and hope to learn from the great community.


Regards,
Shah Hussain
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.opensips.org/pipermail/users/attachments/20210727/e0959610/attachment.html>