Res_config_odbc.c: No database handle available with the name of 'asterisk' (check res_odbc.conf)

silimms · June 2, 2016, 7:16am

hi, I’m getting this error on my pbx

ERROR[6723] res_config_odbc.c: No database handle available with the name of ‘asterisk’ (check res_odbc.conf)

Asterisk Version - 13.7.2
OS - Unbuntu Server 14-04(64 Bit)

This is my setting:

root@pbx:~# cat /etc/odbcinst.ini
[MySQL]
Description = ODBC for MySQL
Driver = /usr/lib/x86_64-linux-gnu/odbc/libmyodbc.so
Setup = /usr/lib/x86_64-linux-gnu/odbc/libodbcmyS.so
FileUsage = 1

root@pbx:~# cat /etc/odbc.ini
[MYSQL-TravMan4]
Description = TravMan4 MySQL ODBC
Driver = /usr/lib/x86_64-linux-gnu/odbc/libmyodbc.so
Socket = /var/run/mysqld/mysqld.sock
Server = localhost
User = root
Password= 123456
Port = 3306
Database= TravMan4
Option = 3

[MYSQL-asteridex]
Description = asteridex MySQL ODBC
Driver = /usr/lib/x86_64-linux-gnu/odbc/libmyodbc.so
Socket = /var/run/mysqld/mysqld.sock
Server = localhost
User = root
Password= 123456
Port = 3306
Database= asteridex
Option = 3

[MYSQL-asterisk]
Description = asterisk MySQL ODBC
Driver = /usr/lib/x86_64-linux-gnu/odbc/libmyodbc.so
Socket = /var/run/mysqld/mysqld.sock
Server = localhost
User = root
Password= 123456
Port = 3306
Database= asterisk
Option = 3

[MYSQL-asteriskcdrdb]
Description = asteriskcdrdb MySQL ODBC
Driver = /usr/lib/x86_64-linux-gnu/odbc/libmyodbc.so
Socket = /var/run/mysqld/mysqld.sock
Server = localhost
User = root
Password= 123456
Port = 3306
Database= asteriskcdrdb
Option = 3

[MYSQL-mysql]
Description = mysql MySQL ODBC
Driver = /usr/lib/x86_64-linux-gnu/odbc/libmyodbc.so
Socket = /var/run/mysqld/mysqld.sock
Server = localhost
User = root
Password= 123456
Port = 3306
Database= mysql
Option = 3

[MYSQL-performance_schema]
Description = performance_schema MySQL ODBC
Driver = /usr/lib/x86_64-linux-gnu/odbc/libmyodbc.so
Socket = /var/run/mysqld/mysqld.sock
Server = localhost
User = root
Password= 123456
Port = 3306
Database= performance_schema
Option = 3

[MYSQL-timeclock]
Description = timeclock MySQL ODBC
Driver = /usr/lib/x86_64-linux-gnu/odbc/libmyodbc.so
Socket = /var/run/mysqld/mysqld.sock
Server = localhost
User = root
Password= 123456
Port = 3306
Database= timeclock
Option = 3

[MYSQL-zipcodes]
Description = zipcodes MySQL ODBC
Driver = /usr/lib/x86_64-linux-gnu/odbc/libmyodbc.so
Socket = /var/run/mysqld/mysqld.sock
Server = localhost
User = root
Password= 123456
Port = 3306
Database= zipcodes
Option = 3

root@pbx:~# cat /etc/asterisk/res_odbc.conf
;--------------------------------------------------------------------------------;
; Do NOT edit this file as it is auto-generated by FreePBX. All modifications to ;
; this file must be done via the web gui. There are alternative files to make ;
; custom modifications, details at: http://freepbx.org/configuration_files ;
;--------------------------------------------------------------------------------;
;
; This file is part of FreePBX.
;
; FreePBX is free software: you can redistribute it and/or modify
; it under the terms of the GNU General Public License as published by
; the Free Software Foundation, either version 2 of the License, or
; (at your option) any later version.
;
; FreePBX is distributed in the hope that it will be useful,
; but WITHOUT ANY WARRANTY; without even the implied warranty of
; MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
; GNU General Public License for more details.
;
; You should have received a copy of the GNU General Public License
; along with FreePBX. If not, see http://www.gnu.org/licenses/.
;
; Copyright © 2012 Astrogen LLC (USA)

#include res_odbc_custom.conf
#include res_odbc_additional.conf

root@pbx:~# cat /etc/asterisk/res_odbc_custom.conf
[MySQL-TravMan4]
enabled => yes
dsn => MySQL-TravMan4
username => root
password => 123456
preconnect => yes

[MySQL-asteridex]
enabled => yes
dsn => MySQL-asteridex
username => root
password => 123456
preconnect => yes

[MySQL-asterisk]
enabled => yes
dsn => MySQL-asterisk
username => root
password => 123456
preconnect => yes

[MySQL-asteriskcdrdb]
enabled => yes
dsn => MySQL-asteriskcdrdb
username => root
password => 123456
preconnect => yes

[MySQL-mysql]
enabled => yes
dsn => MySQL-mysql
username => root
password => 123456
preconnect => yes

[MySQL-performance_schema]
enabled => yes
dsn => MySQL-performance_schema
username => root
password => 123456
preconnect => yes

[MySQL-timeclock]
enabled => yes
dsn => MySQL-timeclock
username => root
password => 123456
preconnect => yes

[MySQL-zipcodes]
enabled => yes
dsn => MySQL-zipcodes
username => root
password => 123456
preconnect => yes

root@pbx:~# cat /etc/asterisk/res_odbc_additional.conf
;--------------------------------------------------------------------------------;
; Do NOT edit this file as it is auto-generated by FreePBX. ;
;--------------------------------------------------------------------------------;
; For information on adding additional paramaters to this file, please visit the ;
; FreePBX.org wiki page, or ask on IRC. This file was created by the new FreePBX ;
; BMO - Big Module Object. Any similarity in naming with BMO from Adventure Time ;
; is totally deliberate. ;
;--------------------------------------------------------------------------------;
[asteriskcdrdb]
enabled=>yes
dsn=>MySQL-asteriskcdrdb
pooling=>no
limit=>1
pre-connect=>yes
username=>asteriskuser
password=>123456

silimms · June 2, 2016, 7:17am

root@pbx:~# cat /etc/asterisk/extconfig.conf

;
; Static and realtime external configuration
; engine configuration
;
; See https://wiki.asterisk.org/wiki/display/AST/Realtime+Database+Configuration
; for basic table formatting information.
;
[settings]
;
; Static configuration files:
;
; file.conf => driver,database[,table[,priority]]
;
; maps a particular configuration file to the given
; database driver, database and table (or uses the
; name of the file as the table if not specified)
;
; Uncomment to load queues.conf via the odbc engine.
;
;queues.conf => odbc,asterisk,ast_config
;extensions.conf => sqlite,asterisk,ast_config
;
; The following files CANNOT be loaded from Realtime storage:
; asterisk.conf
; extconfig.conf (this file)
; logger.conf
;
; Additionally, the following files cannot be loaded from
; Realtime storage unless the storage driver is loaded
; early using ‘preload’ statements in modules.conf:
; manager.conf
; cdr.conf
; rtp.conf
;
; Named ACLs specified in realtime also can not be used
; from manager.conf unless the storage driver is preloaded.
; Attempting to use a realtime stored named ACL before the
; driver is loaded will result in an invalid ACL which
; rejects all addresses.
;
; Realtime configuration engine
;
; maps a particular family of realtime
; configuration to a given database driver,
; database and table (or uses the name of
; the family if the table is not specified
;
;example => odbc,asterisk,alttable,1
;example => mysql,asterisk,alttable,2
;example2 => ldap,“dc=oxymium,dc=net”,example2
;
; Additionally, priorities are now supported for use as failover methods
; for retrieving realtime data. If one connection fails to retrieve any
; information, the next sequential priority will be tried next. This
; especially works well with ODBC connections, since res_odbc now caches
; when connection failures occur and prevents immediately retrying those
; connections until after a specified timeout. Note: priorities must
; start at 1 and be sequential (i.e. if you have only priorities 1, 2,
; and 4, then 4 will be ignored, because there is no 3).
;
;
; Possible driver backends:
;
; “odbc” is shown in the examples below, but is not the only valid realtime
; engine. Here are several of the possible options:
; odbc … res_config_odbc
; sqlite … res_config_sqlite
; sqlite3 … res_config_sqlite3
; pgsql … res_config_pgsql
; curl … res_config_curl
; ldap … res_config_ldap
; mysql … res_config_mysql (available via add-ons in menuselect)
;
; Note: The res_config_pgsql and res_config_sqlite backends configure the
; database used in their respective configuration files and ignore the
; database name configured in this file.
;
;iaxusers => odbc,asterisk
;iaxpeers => odbc,asterisk
;sippeers => odbc,asterisk
;sipregs => odbc,asterisk ; (avoid sipregs if possible, e.g. by using a view)
;ps_endpoints => odbc,asterisk
ps_endpoints => odbc,asterisk,pbx_endpoint_id_ips
;ps_auths => odbc,asterisk
;ps_aors => odbc,asterisk
;ps_domain_aliases => odbc,asterisk
;ps_endpoint_id_ips => odbc,asterisk
;voicemail => odbc,asterisk
;extensions => odbc,asterisk
;meetme => mysql,general
;queues => odbc,asterisk
;queue_members => odbc,asterisk
;queue_rules => odbc,asterisk
;acls => odbc,asterisk
;musiconhold => mysql,general
;queue_log => mysql,general
;
;
; While most dynamic realtime engines are automatically used when defined in
; this file, ‘extensions’, distinctively, is not. To activate dynamic realtime
; extensions, you must turn them on in each respective context within
; extensions.conf with a switch statement. The syntax is:
; switch => Realtime/[[db_context@]tablename]/
; The only option available currently is the ‘p’ option, which disallows
; extension pattern queries to the database. If you have no patterns defined
; in a particular context, this will save quite a bit of CPU time. However,
; note that using dynamic realtime extensions is not recommended anymore as a
; best practice; instead, you should consider writing a static dialplan with
; proper data abstraction via a tool like func_odbc.

root@pbx:~# cat /etc/asterisk/modules.conf
;--------------------------------------------------------------------------------;
; Do NOT edit this file as it is auto-generated by FreePBX. ;
;--------------------------------------------------------------------------------;
; For information on adding additional paramaters to this file, please visit the ;
; FreePBX.org wiki page, or ask on IRC. This file was created by the new FreePBX ;
; BMO - Big Module Object. Any similarity in naming with BMO from Adventure Time ;
; is totally deliberate. ;
;--------------------------------------------------------------------------------;

[modules]
autoload=yes
preload = pbx_config.so
preload = chan_local.so
noload = chan_woomera.so
noload = pbx_gtkconsole.so
noload = pbx_kdeconsole.so
noload = app_intercom.so
noload = chan_modem.so
noload = chan_modem_bestdata.so
noload = chan_modem_i4l.so
noload = app_trunkisavail.so
noload = chan_alsa.so
noload = chan_oss.so
noload = app_directory_odbcstorage.so
noload = app_voicemail_odbcstorage.so
noload = chan_modem_aopen.so
noload = chan_woomera.so
noload = cdr_radius.so
noload = cel_radius.so
load = format_wav.so
load = format_pcm.so
load = format_mp3.so
load = res_musiconhold.so

and I have try to do odbc debugging

ODBC DSN Settings

Name: MySQL-TravMan4
DSN: MySQL-TravMan4
Last connection attempt: 1970-01-01 08:00:00

Name: MySQL-asteridex
DSN: MySQL-asteridex
Last connection attempt: 1970-01-01 08:00:00

Name: MySQL-asterisk
DSN: MySQL-asterisk
Last connection attempt: 1970-01-01 08:00:00

Name: MySQL-asteriskcdrdb
DSN: MySQL-asteriskcdrdb
Last connection attempt: 1970-01-01 08:00:00

Name: MySQL-mysql
DSN: MySQL-mysql
Last connection attempt: 1970-01-01 08:00:00

Name: MySQL-performance_schema
DSN: MySQL-performance_schema
Last connection attempt: 1970-01-01 08:00:00

Name: MySQL-timeclock
DSN: MySQL-timeclock
Last connection attempt: 1970-01-01 08:00:00

Name: MySQL-zipcodes
DSN: MySQL-zipcodes
Last connection attempt: 1970-01-01 08:00:00

Name: asteriskcdrdb
DSN: MySQL-asteriskcdrdb
Last connection attempt: 2016-06-02 14:44:42

anyone know how to solve this problem?
thanks in advance for your help.

astbox · June 2, 2016, 3:52pm

Change in the res_odbc_custom.conf from

[MySQL-asterisk]

to

[asterisk]

and try again.

silimms · June 3, 2016, 1:49am

the error change to show this

[2016-06-03 09:48:12] WARNING[3232] res_odbc.c: SQL Execute error -1! Verifying connection to asterisk [MySQL-asterisk]…
[2016-06-03 09:48:12] WARNING[3232] res_odbc.c: Connection is down attempting to reconnect…
[2016-06-03 09:48:12] NOTICE[3232] res_odbc.c: Connecting asterisk
[2016-06-03 09:48:12] NOTICE[3232] res_odbc.c: res_odbc: Connected to asterisk [MySQL-asterisk]
[2016-06-03 09:48:12] WARNING[3232] res_odbc.c: SQL Execute returned an error -1: 42S02: [MySQL][ODBC 5.1 Driver][mysqld-5.5.49-0ubuntu0.14.04.1]Table ‘asterisk.pbx_endpoint_id_ips’ doesn’t
exist (106)
[2016-06-03 09:48:12] WARNING[3232] res_odbc.c: SQL Execute error -1! Verifying connection to asterisk [MySQL-asterisk]…
[2016-06-03 09:48:12] WARNING[3232] res_odbc.c: Connection is down attempting to reconnect…
[2016-06-03 09:48:12] NOTICE[3232] res_odbc.c: Connecting asterisk
[2016-06-03 09:48:12] NOTICE[3232] res_odbc.c: res_odbc: Connected to asterisk [MySQL-asterisk]

jcolp · June 3, 2016, 10:18am

The warning is pretty clear - the table doesn’t exist that you are trying to use. Did you create it[1]?

[1] https://wiki.asterisk.org/wiki/display/AST/Setting+up+PJSIP+Realtime