[asterisk-users] SOLVED: ODBC problem - static realtime file not loading

[Brynjolfur Thorvardsson]

Hi, I solved this and thought I'd share it if anyone is interested. The problem was in the extconfig.conf file, where it says <database> in all the documentation I could find (as well as in the book itself). Apparently it should be <name of database user> (which, incidently, is "asterisk" in both cases in the book I'm using).

This is from the book(http://www.asteriskdocs.org/en/3rd_Edition/asterisk-book-html-chunk/I_section12_tt1465.html#static_realtime)
; /etc/asterisk/extconfig.conf
filename.conf => driver,database[,table]

This is from the Asterisk Wiki (https://wiki.asterisk.org/wiki/display/AST/Realtime+Database+Configuration):

<family> => <realtime driver>,<db name>[,<table>]

So following the guides (which admittedly both refer to version 1.8 - while mine is 1.4) I put in my extconfig.conf the following line:

musiconhold.conf => odbc,asterisk_development,asterisk_files

where asterisk_development is the name of the MySQL database. That didn't work, but this works!:

musiconhold.conf => odbc,asterisk,asterisk_files

where asterisk is the name of the database user.

Another thing: The general log in MySQL never shows any ODBC commands, only a "connect" entry made by the ODBC driver. I still haven't found out how to spy on the ODBC commands, but that doesn't worry me at the moment.

Well, that took me a whole day to sort out ...

Fra: asterisk-users-bounces at lists.digium.com [mailto:asterisk-users-bounces at lists.digium.com] På vegne af Brynjolfur Thorvardsson
Sendt: 16. december 2011 13:06
Til: asterisk-users at lists.digium.com
Emne: [asterisk-users] ODBC problem - static realtime file not loading

Hi all

I'm trying to configure my Asterisk setup to load the musiconhold.conf file from an ODBC connection to MySQL, working through the example given in the excellent book "Asterisk: The Definite Guide". I'm using Asterisk  1.4.19 and MySQL 5.1.58. I've configured the ODBC bit and in my GeneralLog on MySQL I can see the asterisk user connecting and sending a few SQL statements, such as "SET SQL_AUTO_IS_NULL = 0".

After connecting, the asterisk user never sends another SQL statement, at least nothing that shows up in the General log. Asterisk is running as root. I've deleted the musiconhold.conf file from /etc/asterisk

Testing odbc from command line ( there is a difference from what the book says, I need to use sudo for isql to work, presumably since * is running as root)

$ odbcinst -q -d
[MySQL]
$echo "select 1" | sudo isql -v asterisk-connector
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> select 1
+---------------------+
| 1                   |
+---------------------+
| 1                   |
+---------------------+
SQLRowCount returns 1
1 rows fetched

The extconfig.conf file gets parsed, and looks like this:

[settings]
musiconhold.conf => odbc,asterisk_development,asterisk_files

The modules.conf contains only:

[modules]
preload => res_odbc.so
preload => res_config_odbc.so
autoload=yes

On starting Asterisk with -cv I get the following:

[Dec 16 11:08:38] WARNING[1632]: res_musiconhold.c:1309 load_module: No music on hold classes configured, disabling music on hold.
[Dec 16 11:08:38] res_musiconhold.so => (Music On Hold Resource)

The second line loads the module in spite of the warning in the first line. The following commands give:

*CLI> moh show classes
*CLI> odbc show
Name: asterisk
DSN: asterisk-connector
Pooled: no
Connected: yes
*CLI> module reload res_musiconhold.so
*CLI>moh show classes
*CLI>

I guess the problem could lie with the database itself but I've checked and double-checked the column names and defs, and the asterisk user has full access rights to the database.

The SQL insert for the database looks like this:

INSERT INTO `asterisk_files` (`id`, `cat_metric`, `var_metric`, `filename`, `category`, `var_name`, `var_val`, `commented`, `created_at`, `updated_at`) VALUES
(1, 1, 1, 'musiconhold.conf', 'default', 'mode', 'files', 0, NULL, NULL),
(2, 1, 2, 'musiconhold.conf', 'default', 'directory', '/var/lib/asterisk/moh', 0, NULL, NULL);

The two last columns (created_at, updated_at) were created by Rails, but I've also tried pointing Asterisk to a view without those two columns.

Basically, the ODBC connection works but it seems as if Asterisk never tries to read the definitions from the database. Any help would be greatly appreciated!

Regards

Binni

ITAnet
Kirkestien 20
9230  Svenstrup

Telefon: 3020 0868

Email: binni at itanet.nu<mailto:info at itanet.nu>
WWW: http://www.itanet.nu<http://www.itanet.nu/>


[cid:image001.gif at 01CCBCA0.81833270]



-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.digium.com/pipermail/asterisk-users/attachments/20111217/2d130d74/attachment.htm>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image001.gif
Type: image/gif
Size: 3392 bytes
Desc: image001.gif
URL: <http://lists.digium.com/pipermail/asterisk-users/attachments/20111217/2d130d74/attachment.gif>