<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40"><head><meta http-equiv=Content-Type content="text/html; charset=iso-8859-1"><meta name=Generator content="Microsoft Word 12 (filtered medium)"><!--[if !mso]><style>v\:* {behavior:url(#default#VML);}
o\:* {behavior:url(#default#VML);}
w\:* {behavior:url(#default#VML);}
.shape {behavior:url(#default#VML);}
</style><![endif]--><style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri","sans-serif";}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
p
{mso-style-priority:99;
mso-margin-top-alt:auto;
margin-right:0cm;
mso-margin-bottom-alt:auto;
margin-left:0cm;
font-size:12.0pt;
font-family:"Times New Roman","serif";}
code
{mso-style-priority:99;
font-family:"Courier New";}
pre
{mso-style-priority:99;
mso-style-link:"Formateret HTML Tegn";
margin:0cm;
margin-bottom:.0001pt;
font-size:10.0pt;
font-family:"Courier New";}
p.MsoAcetate, li.MsoAcetate, div.MsoAcetate
{mso-style-priority:99;
mso-style-link:"Markeringsbobletekst Tegn";
margin:0cm;
margin-bottom:.0001pt;
font-size:8.0pt;
font-family:"Tahoma","sans-serif";}
span.MarkeringsbobletekstTegn
{mso-style-name:"Markeringsbobletekst Tegn";
mso-style-priority:99;
mso-style-link:Markeringsbobletekst;
font-family:"Tahoma","sans-serif";}
span.EmailStyle19
{mso-style-type:personal;
font-family:"Calibri","sans-serif";
color:windowtext;}
span.apple-style-span
{mso-style-name:apple-style-span;}
span.EmailStyle22
{mso-style-type:personal-reply;
font-family:"Calibri","sans-serif";
color:#1F497D;}
span.FormateretHTMLTegn
{mso-style-name:"Formateret HTML Tegn";
mso-style-priority:99;
mso-style-link:"Formateret HTML ";
font-family:"Courier New";}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:3.0cm 2.0cm 3.0cm 2.0cm;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]--></head><body lang=DA link=blue vlink=purple><div class=WordSection1><p class=MsoNormal><span lang=EN-GB style='color:#1F497D'>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).<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-GB style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-GB style='color:#1F497D'>This is from the book(</span><a href="http://www.asteriskdocs.org/en/3rd_Edition/asterisk-book-html-chunk/I_section12_tt1465.html#static_realtime"><span lang=EN-GB>http://www.asteriskdocs.org/en/3rd_Edition/asterisk-book-html-chunk/I_section12_tt1465.html#static_realtime</span></a><span lang=EN-GB>)</span><span lang=EN-GB style='color:#1F497D'><o:p></o:p></span></p><div style='mso-element:para-border-div;border-top:dotted 1.0pt;border-left:dotted 1.0pt;border-bottom:solid 1.5pt;border-right:solid 1.5pt;border-color:black;padding:6.0pt 6.0pt 6.0pt 6.0pt;background:white;margin-left:18.0pt;margin-right:18.0pt'><p class=MsoNormal style='mso-margin-top-alt:12.0pt;margin-right:0cm;margin-bottom:12.0pt;margin-left:0cm;background:white;border:none;padding:0cm'><span lang=EN-GB style='font-size:9.0pt;font-family:"Courier New";color:black'>; /etc/asterisk/extconfig.conf<o:p></o:p></span></p><p class=MsoNormal style='mso-margin-top-alt:12.0pt;margin-right:0cm;margin-bottom:12.0pt;margin-left:0cm;background:white;border:none;padding:0cm'><span lang=EN-GB style='font-size:9.0pt;font-family:"Courier New";color:black'>filename.conf => driver,database[,table]<o:p></o:p></span></p></div><p class=MsoNormal><span lang=EN-GB style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-GB style='color:#1F497D'>This is from the Asterisk Wiki (</span><a href="https://wiki.asterisk.org/wiki/display/AST/Realtime+Database+Configuration"><span lang=EN-GB>https://wiki.asterisk.org/wiki/display/AST/Realtime+Database+Configuration</span></a><span lang=EN-GB>)</span><span lang=EN-GB style='color:#1F497D'>:<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-GB style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='text-indent:65.2pt'><span lang=EN-GB style='font-size:10.0pt;font-family:"Courier New";color:#333333'><family> => <realtime driver>,<db name>[,<table>]</span><span lang=EN-GB style='font-size:10.0pt;font-family:"Times New Roman","serif";color:#333333'><o:p></o:p></span></p><p class=MsoNormal><span lang=EN-GB style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-GB style='color:#1F497D'>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:<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-GB style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-GB>musiconhold.conf => odbc,asterisk_development,asterisk_files<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-GB style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-GB style='color:#1F497D'>where asterisk_development is the name of the MySQL database. That didn’t work, but this works!:<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-GB style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-GB>musiconhold.conf => odbc,asterisk,asterisk_files<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-GB style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-GB style='color:#1F497D'>where asterisk is the name of the database user.<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-GB style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-GB style='color:#1F497D'>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.<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-GB style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-GB style='color:#1F497D'>Well, that took me a whole day to sort out ... <o:p></o:p></span></p><p class=MsoNormal><span lang=EN-GB style='color:#1F497D'><o:p> </o:p></span></p><div><div style='border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0cm 0cm 0cm'><p class=MsoNormal><b><span style='font-size:10.0pt;font-family:"Tahoma","sans-serif"'>Fra:</span></b><span style='font-size:10.0pt;font-family:"Tahoma","sans-serif"'> asterisk-users-bounces@lists.digium.com [mailto:asterisk-users-bounces@lists.digium.com] <b>På vegne af </b>Brynjolfur Thorvardsson<br><b>Sendt:</b> 16. december 2011 13:06<br><b>Til:</b> asterisk-users@lists.digium.com<br><b>Emne:</b> [asterisk-users] ODBC problem - static realtime file not loading<o:p></o:p></span></p></div></div><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal><span lang=EN-GB>Hi all<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-GB><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-GB>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</span>”. <o:p></o:p></p><p class=MsoNormal><span lang=EN-GB><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-GB>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<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-GB><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-GB>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) <o:p></o:p></span></p><p class=MsoNormal><span lang=EN-GB><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-GB>$ odbcinst -q -d<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-GB>[MySQL]<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-GB>$echo "select 1" | sudo isql -v asterisk-connector<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-GB>+---------------------------------------+<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-GB>| Connected! |<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-GB>| |<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-GB>| sql-statement |<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-GB>| help [tablename] |<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-GB>| quit |<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-GB>| |<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-GB>+---------------------------------------+<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-GB>SQL> select 1<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-GB>+---------------------+<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-GB>| 1 |<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-GB>+---------------------+<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-GB>| 1 |<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-GB>+---------------------+<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-GB>SQLRowCount returns 1<o:p></o:p></span></p><p class=MsoNormal>1 rows fetched<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal><span lang=EN-GB>The extconfig.conf file gets parsed, and looks like this:<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-GB><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-GB>[settings]<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-GB>musiconhold.conf => odbc,asterisk_development,asterisk_files<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-GB><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-GB>The modules.conf contains only:<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-GB><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-GB>[modules]<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-GB>preload => res_odbc.so<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-GB>preload => res_config_odbc.so<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-GB>autoload=yes<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-GB><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-GB>On starting Asterisk with –cv I get the following:<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-GB><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-GB>[Dec 16 11:08:38] WARNING[1632]: res_musiconhold.c:1309 load_module: No music on hold classes configured, disabling music on hold.<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-GB>[Dec 16 11:08:38] res_musiconhold.so => (Music On Hold Resource)<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-GB><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-GB style='font-size:10.0pt;font-family:"Arial","sans-serif"'>The second line loads the module in spite of the warning in the first line. The following commands give:<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-GB style='font-size:10.0pt;font-family:"Arial","sans-serif"'><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-GB style='font-size:10.0pt;font-family:"Arial","sans-serif"'>*CLI> moh show classes<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-GB style='font-size:10.0pt;font-family:"Arial","sans-serif"'>*CLI> odbc show<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-GB style='font-size:10.0pt;font-family:"Arial","sans-serif"'>Name: asterisk<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-GB style='font-size:10.0pt;font-family:"Arial","sans-serif"'>DSN: asterisk-connector<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-GB style='font-size:10.0pt;font-family:"Arial","sans-serif"'>Pooled: no<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-GB style='font-size:10.0pt;font-family:"Arial","sans-serif"'>Connected: yes<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-GB style='font-size:10.0pt;font-family:"Arial","sans-serif"'>*CLI> module reload res_musiconhold.so<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-GB style='font-size:10.0pt;font-family:"Arial","sans-serif"'>*CLI>moh show classes<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-GB style='font-size:10.0pt;font-family:"Arial","sans-serif"'>*CLI><o:p></o:p></span></p><p class=MsoNormal><span lang=EN-GB style='font-size:10.0pt;font-family:"Arial","sans-serif"'><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-GB style='font-size:10.0pt;font-family:"Arial","sans-serif"'>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. <o:p></o:p></span></p><p class=MsoNormal><span lang=EN-GB style='font-size:10.0pt;font-family:"Arial","sans-serif"'><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-GB style='font-size:10.0pt;font-family:"Arial","sans-serif"'>The SQL insert for the database looks like this:<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-GB style='font-size:10.0pt;font-family:"Arial","sans-serif"'><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-GB style='font-size:10.0pt;font-family:"Arial","sans-serif"'>INSERT INTO `asterisk_files` (`id`, `cat_metric`, `var_metric`, `filename`, `category`, `var_name`, `var_val`, `commented`, `created_at`, `updated_at`) VALUES<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-GB style='font-size:10.0pt;font-family:"Arial","sans-serif"'>(1, 1, 1, 'musiconhold.conf', 'default', 'mode', 'files', 0, NULL, NULL),<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-GB style='font-size:10.0pt;font-family:"Arial","sans-serif"'>(2, 1, 2, 'musiconhold.conf', 'default', 'directory', '/var/lib/asterisk/moh', 0, NULL, NULL);<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-GB style='font-size:10.0pt;font-family:"Arial","sans-serif"'><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-GB style='font-size:10.0pt;font-family:"Arial","sans-serif"'>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.<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-GB style='font-size:10.0pt;font-family:"Arial","sans-serif"'><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-GB style='font-size:10.0pt;font-family:"Arial","sans-serif"'>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!<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-GB style='font-size:10.0pt;font-family:"Arial","sans-serif"'> </span><span lang=EN-GB><o:p></o:p></span></p><p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial","sans-serif"'>Regards</span><o:p></o:p></p><p class=MsoNormal> <o:p></o:p></p><p class=MsoNormal><b><span style='font-size:10.0pt;font-family:"Arial","sans-serif"'>Binni</span></b><o:p></o:p></p><p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial","sans-serif"'> </span><o:p></o:p></p><p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial","sans-serif"'>ITAnet</span><o:p></o:p></p><p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial","sans-serif"'>Kirkestien 20<br>9230 Svenstrup</span><o:p></o:p></p><p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial","sans-serif"'> </span><o:p></o:p></p><p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial","sans-serif"'>Telefon: 3020 0868</span><o:p></o:p></p><p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial","sans-serif"'> </span><o:p></o:p></p><p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial","sans-serif"'>Email: </span><span lang=EN-GB style='font-size:10.0pt;font-family:"Arial","sans-serif";color:#1F497D'><a href="mailto:info@itanet.nu"><span lang=DA style='color:#1F497D'>binni@itanet.nu</span></a></span><o:p></o:p></p><p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial","sans-serif"'>WWW: <span style='color:#1F497D'><a href="http://www.itanet.nu/"><span style='color:#1F497D'>http://www.itanet.nu</span></a></span></span><o:p></o:p></p><p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial","sans-serif"'> </span><o:p></o:p></p><p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial","sans-serif"'> </span><o:p></o:p></p><p class=MsoNormal><span style='font-size:10.0pt;font-family:"Arial","sans-serif"'><img border=0 width=150 height=62 id="Picture_x0020_1" src="cid:image001.gif@01CCBCA0.81833270" alt="cid:image001.gif@01C74AF4.363299F0"></span><o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p> <o:p></o:p></p></div></body></html>