asterisk users - Feb 2015 - Problem with odbc connector with cdr

Hello,

I'm stuck with getting cdr records stored in MySql database. I have a
working realtime environment and have verified that the db connection works
fine when used via res_config_mysql.conf. I'd appreciate Your help on how
to get the odbc connector working as I think there's something wrong with
its configuration.

The problem presented itself as an error when making a call that was
supposed to be stored in mysql cdr: Unable to retrieve database handle.
CDR failed.
This kept popping up every time I was trying to have a call be stored in my
mysql cdr table, and I traced the problem to the odbc connector, where my
knowhow is about to end.

One problem may be that odbcinst -j outputs wrong ini files, I believe I
need these to be under /etc/ instead of /usr/local/etc/, but I'm unsure
about how to change those permanently.

# odbcinst -j
unixODBC 2.3.2
DRIVERS............: /usr/local/etc/odbcinst.ini
SYSTEM DATA SOURCES: /usr/local/etc/odbc.ini
FILE DATA SOURCES..: /usr/local/etc/ODBCDataSources
USER DATA SOURCES..: /root/.odbc.ini
SQLULEN Size.......: 4
SQLLEN Size........: 4
SQLSETPOSIROW Size.: 2

Odbcinst does see the connection, it reads it from
/usr/local/etc/odbcinst.ini.
# odbcinst -q -d
[MySQL]

I tried copying my connection information onto those files listed above and
I'm not getting any errors in Asterisk logs when restarting Asterisk.
However:
# echo "select 1" | isql -v MySQL
[IM002][unixODBC][Driver Manager]Data source name not found, and no default
driver specified
[ISQL]ERROR: Could not SQLConnect


Here's the contents of odbc.ini:

[MySQL-asterisk]
Description = MySQL Asterisk database
Trace = Off
TraceFile = stderr
Driver = MySQL
SERVER = serverip
USER = myuser
PASSWORD = mypass
PORT = 3306
DATABASE = asterisk

And odbcinst.ini:
[MySQL]
Description     = ODBC for MySQL
Driver          = /usr/lib/libmyodbc5.so
Setup           = /usr/lib/libodbcmyS.so
Driver64        = /usr/lib64/libmyodbc5.so
Setup64         = /usr/lib64/libodbcmyS.so
FileUsage       = 1

And here I hit a wall, how can I fix the configuration?


MySQL-asterisk is referred to in res_odbc.conf, whose label is referred to
in cdr_odbc.conf. When the odbc connector starts working, is this the
correct way to configure the cdr db connection in Asterisk?

Thanks,
Olli
-------------- next part --------------
An HTML attachment was scrubbed...
URL:
<http://lists.digium.com/pipermail/asterisk-users/attachments/20150203/02120820/attachment.html>

I notice you have MySQL-asterisk as your definition in your odbc.ini but
you are trying to connect to simply 'MySQL' with your 'isql'
command.

Does isql work with 'MySQL-asterisk' as the DSN instead of simply
'MySQL' ?

I have machines that use /etc/odbc.ini and machines that use
/usr/local/etc/odbc.ini depending on if I used a package to instal ODBC or
if I compiled ODBC myself.

On Tue, Feb 3, 2015 at 1:35 AM, Olli Heiskanen <
ohjelmistoarkkitehti at gmail.com> wrote:
>
> Hello,
>
> I'm stuck with getting cdr records stored in MySql database. I have a
> working realtime environment and have verified that the db connection works
> fine when used via res_config_mysql.conf. I'd appreciate Your help on
how
> to get the odbc connector working as I think there's something wrong
with
> its configuration.
>
> The problem presented itself as an error when making a call that was
> supposed to be stored in mysql cdr: Unable to retrieve database handle.
> CDR failed.
> This kept popping up every time I was trying to have a call be stored in
> my mysql cdr table, and I traced the problem to the odbc connector, where
> my knowhow is about to end.
>
> One problem may be that odbcinst -j outputs wrong ini files, I believe I
> need these to be under /etc/ instead of /usr/local/etc/, but I'm unsure
> about how to change those permanently.
>
> # odbcinst -j
> unixODBC 2.3.2
> DRIVERS............: /usr/local/etc/odbcinst.ini
> SYSTEM DATA SOURCES: /usr/local/etc/odbc.ini
> FILE DATA SOURCES..: /usr/local/etc/ODBCDataSources
> USER DATA SOURCES..: /root/.odbc.ini
> SQLULEN Size.......: 4
> SQLLEN Size........: 4
> SQLSETPOSIROW Size.: 2
>
> Odbcinst does see the connection, it reads it from
> /usr/local/etc/odbcinst.ini.
> # odbcinst -q -d
> [MySQL]
>
> I tried copying my connection information onto those files listed above
> and I'm not getting any errors in Asterisk logs when restarting
Asterisk.
> However:
> # echo "select 1" | isql -v MySQL
> [IM002][unixODBC][Driver Manager]Data source name not found, and no
> default driver specified
> [ISQL]ERROR: Could not SQLConnect
>
>
> Here's the contents of odbc.ini:
>
> [MySQL-asterisk]
> Description = MySQL Asterisk database
> Trace = Off
> TraceFile = stderr
> Driver = MySQL
> SERVER = serverip
> USER = myuser
> PASSWORD = mypass
> PORT = 3306
> DATABASE = asterisk
>
> And odbcinst.ini:
> [MySQL]
> Description     = ODBC for MySQL
> Driver          = /usr/lib/libmyodbc5.so
> Setup           = /usr/lib/libodbcmyS.so
> Driver64        = /usr/lib64/libmyodbc5.so
> Setup64         = /usr/lib64/libodbcmyS.so
> FileUsage       = 1
>
> And here I hit a wall, how can I fix the configuration?
>
>
> MySQL-asterisk is referred to in res_odbc.conf, whose label is referred to
> in cdr_odbc.conf. When the odbc connector starts working, is this the
> correct way to configure the cdr db connection in Asterisk?
>
> Thanks,
> Olli
>
> --
> _____________________________________________________________________
> -- Bandwidth and Colocation Provided by http://www.api-digital.com --
> New to Asterisk? Join us for a live introductory webinar every Thurs:
>                http://www.asterisk.org/hello
>
> asterisk-users mailing list
> To UNSUBSCRIBE or update options visit:
>    http://lists.digium.com/mailman/listinfo/asterisk-users
>


-- 
A human being should be able to change a diaper, plan an invasion, butcher
a hog, conn a ship, design a building, write a sonnet, balance accounts,
build a wall, set a bone, comfort the dying, take orders, give orders,
cooperate, act alone, solve equations, analyze a new problem, pitch manure,
program a computer, cook a tasty meal, fight efficiently, die gallantly.
Specialization is for insects.
---Heinlein
-------------- next part --------------
An HTML attachment was scrubbed...
URL:
<http://lists.digium.com/pipermail/asterisk-users/attachments/20150203/b3aa6fb3/attachment.html>

Thanks John,

At first got an error using MySQL-asterisk, but then I removed /etc/ ini
files and used the DSN in /usr/local/etc/odbc.ini, that did the trick for
isql. I must have created the files /etc/ while following a guide online.
Nice!

After some meddling with the Asterisk conf files to have the correct values
I got the cdrs working, thanks dude!

cheers,
Olli






2015-02-03 18:04 GMT+02:00 John Kiniston <johnkiniston at gmail.com>:
> I notice you have MySQL-asterisk as your definition in your odbc.ini but
> you are trying to connect to simply 'MySQL' with your
'isql' command.
>
> Does isql work with 'MySQL-asterisk' as the DSN instead of simply
'MySQL' ?
>
> I have machines that use /etc/odbc.ini and machines that use
> /usr/local/etc/odbc.ini depending on if I used a package to instal ODBC or
> if I compiled ODBC myself.
>
> On Tue, Feb 3, 2015 at 1:35 AM, Olli Heiskanen <
> ohjelmistoarkkitehti at gmail.com> wrote:
>
>>
>> Hello,
>>
>> I'm stuck with getting cdr records stored in MySql database. I have
a
>> working realtime environment and have verified that the db connection
works
>> fine when used via res_config_mysql.conf. I'd appreciate Your help
on how
>> to get the odbc connector working as I think there's something
wrong with
>> its configuration.
>>
>> The problem presented itself as an error when making a call that was
>> supposed to be stored in mysql cdr: Unable to retrieve database handle.
>> CDR failed.
>> This kept popping up every time I was trying to have a call be stored
in
>> my mysql cdr table, and I traced the problem to the odbc connector,
where
>> my knowhow is about to end.
>>
>> One problem may be that odbcinst -j outputs wrong ini files, I believe
I
>> need these to be under /etc/ instead of /usr/local/etc/, but I'm
unsure
>> about how to change those permanently.
>>
>> # odbcinst -j
>> unixODBC 2.3.2
>> DRIVERS............: /usr/local/etc/odbcinst.ini
>> SYSTEM DATA SOURCES: /usr/local/etc/odbc.ini
>> FILE DATA SOURCES..: /usr/local/etc/ODBCDataSources
>> USER DATA SOURCES..: /root/.odbc.ini
>> SQLULEN Size.......: 4
>> SQLLEN Size........: 4
>> SQLSETPOSIROW Size.: 2
>>
>> Odbcinst does see the connection, it reads it from
>> /usr/local/etc/odbcinst.ini.
>> # odbcinst -q -d
>> [MySQL]
>>
>> I tried copying my connection information onto those files listed above
>> and I'm not getting any errors in Asterisk logs when restarting
Asterisk.
>> However:
>> # echo "select 1" | isql -v MySQL
>> [IM002][unixODBC][Driver Manager]Data source name not found, and no
>> default driver specified
>> [ISQL]ERROR: Could not SQLConnect
>>
>>
>> Here's the contents of odbc.ini:
>>
>> [MySQL-asterisk]
>> Description = MySQL Asterisk database
>> Trace = Off
>> TraceFile = stderr
>> Driver = MySQL
>> SERVER = serverip
>> USER = myuser
>> PASSWORD = mypass
>> PORT = 3306
>> DATABASE = asterisk
>>
>> And odbcinst.ini:
>> [MySQL]
>> Description     = ODBC for MySQL
>> Driver          = /usr/lib/libmyodbc5.so
>> Setup           = /usr/lib/libodbcmyS.so
>> Driver64        = /usr/lib64/libmyodbc5.so
>> Setup64         = /usr/lib64/libodbcmyS.so
>> FileUsage       = 1
>>
>> And here I hit a wall, how can I fix the configuration?
>>
>>
>> MySQL-asterisk is referred to in res_odbc.conf, whose label is referred
>> to in cdr_odbc.conf. When the odbc connector starts working, is this
the
>> correct way to configure the cdr db connection in Asterisk?
>>
>> Thanks,
>> Olli
>>
>> --
>> _____________________________________________________________________
>> -- Bandwidth and Colocation Provided by http://www.api-digital.com --
>> New to Asterisk? Join us for a live introductory webinar every Thurs:
>>                http://www.asterisk.org/hello
>>
>> asterisk-users mailing list
>> To UNSUBSCRIBE or update options visit:
>>    http://lists.digium.com/mailman/listinfo/asterisk-users
>>
>
>
>
> --
> A human being should be able to change a diaper, plan an invasion, butcher
> a hog, conn a ship, design a building, write a sonnet, balance accounts,
> build a wall, set a bone, comfort the dying, take orders, give orders,
> cooperate, act alone, solve equations, analyze a new problem, pitch manure,
> program a computer, cook a tasty meal, fight efficiently, die gallantly.
> Specialization is for insects.
> ---Heinlein
>
> --
> _____________________________________________________________________
> -- Bandwidth and Colocation Provided by http://www.api-digital.com --
> New to Asterisk? Join us for a live introductory webinar every Thurs:
>                http://www.asterisk.org/hello
>
> asterisk-users mailing list
> To UNSUBSCRIBE or update options visit:
>    http://lists.digium.com/mailman/listinfo/asterisk-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL:
<http://lists.digium.com/pipermail/asterisk-users/attachments/20150203/14d7b94b/attachment.html>