My Asterisk server is not connecting with MySQL using odbc connector after restarting the VM.
Before restart:
Asterisk-VirtualBox*CLI> odbc show
ODBC DSN Settings
-----------------
Name: asterisk
DSN: asterisk
Number of active connections: 1 (out of 1)
Logging: Disabled
After restart:
Asterisk-VirtualBox*CLI> odbc show
ODBC DSN Settings
-----------------
Name: asterisk
DSN: asterisk
Last fail connection attempt: 2021-08-26 18:34:25
Number of active connections: 0 (out of 1)
Logging: Disabled
Warning:
WARNING[1787]: res_odbc.c:1067 odbc_obj_connect: res_odbc: Error SQLConnect=-1 errno=1698 [unixODBC][MySQL][ODBC 8.0(w) Driver]Access denied for user 'root'@'localhost'
Why it can not access the user ‘root’ and password ‘0000’? How can I solve this problem?
/etc/odbcinst.ini
[MySQL ODBC 8.0 Unicode Driver]
Description = ODBC for MySQL
Driver=/usr/lib/x86_64-linux-gnu/odbc/libmyodbc8w.so
Setup =/usr/lib/x86_64-linux-gnu/odbc/libodbcmyS.so
UsageCount=1
[MySQL ODBC 8.0 ANSI Driver]
DescriDescription = ODBC for MySQL
Driver=/usr/lib/x86_64-linux-gnu/odbc/libmyodbc8a.so
Setup =/usr/lib/x86_64-linux-gnu/odbc/libodbcmyS.so
UsageCount=1
/etc/odbc.ini
[asterisk]
Description = MySQL connection to ‘asterisk’ database
Driver = MySQL ODBC 8.0 Unicode Driver
Server = localhost
User = root
Password = 0000
Port = 3306
Database = asterisk
Socket = /var/run/mysqld/mysqld.sock
/etc/asterisk/res_odbc.conf
;;; odbc setup file
; ENV is a global set of environmental variables that will get set.
; Note that all environmental variables can be seen by all connections,
; so you can't have different values for different connections.
[ENV]
;INFORMIXSERVER => my_special_database
;INFORMIXDIR => /opt/informix
;ORACLE_HOME => /home/oracle
ODBCSYSINI => /etc
ODBCINI => /etc/odbc.ini
; All other sections are arbitrary names for database connections.
;
; The context name is what will be used in other configuration files, such
; as extconfig.conf and func_odbc.conf, to reference this connection.
[asterisk]
;
; Permit disabling sections without needing to comment them out.
; If not specified, it is assumed the section is enabled.
enabled => yes
dsn => asterisk
username => root
password => 0000
pooling => no
limit => 1
pre-connect =>yes
;
; This value should match an entry in /etc/odbc.ini
; (or /usr/local/etc/odbc.ini, on FreeBSD and similar systems).
;dsn => asterisk
;
; Username for connecting to the database. The user defaults to the context name if unspecified.
;username => myuser
;
; Password for authenticating the user to the database. The default
; password is blank.
;password => mypass
;
; Build a connection at startup?
;pre-connect => yes
;
; What should we execute to ensure that our connection is still alive? The
; statement should return a non-zero value in the first field of its first
; record. The default is "select 1".
;sanitysql => select 1
;
; The maximum number of connections to have open at any given time.
; This defaults to 1 and it is highly recommended to only set this higher
; if using a version of UnixODBC greater than 2.3.1.
;max_connections => 10
;
; When the channel is destroyed, should any uncommitted open transactions
; automatically be committed?
;forcecommit => no
;
; How should we perceive data in other transactions within the database?
; Possible values are read_uncommitted, read_committed, repeatable_read,
; and serializable. The default is read_committed.
;isolation => repeatable_read
;
; Is the backslash a native escape character? The default is yes, but for
; MS SQL Server, the answer is no.
;backslash_is_escape => yes
;
; How long (in seconds) should we attempt to connect before considering the
; connection dead? The default is 10 seconds, but you may wish to reduce it,
; to increase responsiveness.
;connect_timeout => 10
;
; When a connection fails, how long (in seconds) should we cache that
; information before we attempt another connection? This increases
; responsiveness, when a database resource is not working.
;negative_connection_cache => 300
;
; Enable query logging. This keeps track of the number of prepared queries
; and executed queries as well as the query that has taken the longest to
; execute. This can be useful for determining the latency with a database.
; The data can be viewed using the "odbc show" CLI command.
; Note that only successful queries are logged currently.
;logging => yes
sudo isql asterisk -v output:
sudo isql asterisk -v
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL> show tables;
+-----------------------------------------------------------------+
| Tables_in_asterisk |
+-----------------------------------------------------------------+
| alembic_version_config |
| extensions |
| iaxfriends |
| meetme |
| musiconhold |
| musiconhold_entry |
| ps_aors |
| ps_asterisk_publications |
| ps_auths |
| ps_contacts |
| ps_domain_aliases |
| ps_endpoint_id_ips |
| ps_endpoints |
| ps_globals |
| ps_inbound_publications |
| ps_outbound_publishes |
| ps_registrations |
| ps_resource_list |
| ps_subscription_persistence |
| ps_systems |
| ps_transports |
| queue_members |
| queue_rules |
| queues |
| sippeers |
| voicemail |
+-----------------------------------------------------------------+
SQLRowCount returns 26
26 rows fetched
Also I added 101, 102 two endpoints using database but can not register the softphones in asterisk.