asterisk users - Jun 2012 - Asterisk 1.8.13.0 / problem with cdr logging (mysql, odbc)

Hi,

I am trying now for over 4 hours setting up cdr-logging via odbc into a 
mysql database. But with no success. Do you have any hint for me?

cat /etc/odbc.ini
------------------
[MySQL-asterisk]
Description = MySQL ODBC Driver
Driver = MySQL
Socket = /var/run/mysqld/mysqld.sock
Server = localhost
User = xxx
Password = xxx
Database = asterisk
Option = 3
Port 
and

/etc/odbcinst.ini
--------------------
[MySQL]
Description = MySQL ODBC MyODBC Driver
Driver = /usr/lib/x86_64-linux-gnu/odbc/libmyodbc.so
FileUsage = 1

When testing this setup I can see, that this basic setup ist fine:

~$ isql MySQL-asterisk asterisk qpalym -v
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>

So here are the config file for asterisk.

/etc/asterisk/res_odbc.conf
-----------------------------
[mysql]
enabled => yes
dsn => MySQL-asterisk
username => asterisk
password => qpalym
pre-connect => yes

and

/etc/asterisk/cdr_odbc.conf
----------------------------
[global]
dsn=MySQL-asterisk
loguniqueid=yes
dispositionstring=yes
table=cdr

But after a call hangup I get the following error:
cdr_odbc.c: Unable to retrieve database handle. CDR failed.

What is going wrong here, please?

-Thorsten-

----- Original Message -----
> From: "Thorsten G?llner" <tg at ovm-group.com>
> To: "Asterisk Users Mailing List - Non-Commercial Discussion"
<asterisk-users at lists.digium.com>
> Sent: Monday, June 18, 2012 11:52:15 AM
> Subject: [asterisk-users] Asterisk 1.8.13.0 / problem with cdr logging
(mysql, odbc)
> 
>
> /etc/odbcinst.ini
> --------------------
> [MySQL]
> Description = MySQL ODBC MyODBC Driver
> Driver = /usr/lib/x86_64-linux-gnu/odbc/libmyodbc.so
> FileUsage = 1
Try adding:

"Setup = /usr/lib/x86_64-linux-gnu/odbc/libodbcmyS.so"

Adjust the path according to where this file can be found on your system.

> So here are the config file for asterisk.
> 
> /etc/asterisk/res_odbc.conf
> -----------------------------
> [mysql]
> enabled => yes
> dsn => MySQL-asterisk
> username => asterisk
> password => qpalym
> pre-connect => yes
For MySQL, I think you also need:

"backslash_is_escape => yes"

Give those two things a try and see if that helps.

Regards,
Michael

I think you need the DSN in car_odbr.ini to refer to the one in res_odbc.conf as
below

On 19/06/2012, at 3:52 AM, Thorsten G?llner wrote:
> Hi,
> 
> I am trying now for over 4 hours setting up cdr-logging via odbc into a
mysql database. But with no success. Do you have any hint for me?
> 
> cat /etc/odbc.ini
> ------------------
> [MySQL-asterisk]
> Description = MySQL ODBC Driver
> Driver = MySQL
> Socket = /var/run/mysqld/mysqld.sock
> Server = localhost
> User = xxx
> Password = xxx
> Database = asterisk
> Option = 3
> Port > 
> and
> 
> /etc/odbcinst.ini
> --------------------
> [MySQL]
> Description = MySQL ODBC MyODBC Driver
> Driver = /usr/lib/x86_64-linux-gnu/odbc/libmyodbc.so
> FileUsage = 1
> 
> When testing this setup I can see, that this basic setup ist fine:
> 
> ~$ isql MySQL-asterisk asterisk qpalym -v
> +---------------------------------------+
> | Connected!                            |
> |                                       |
> | sql-statement                         |
> | help [tablename]                      |
> | quit                                  |
> |                                       |
> +---------------------------------------+
> SQL>
> 
> So here are the config file for asterisk.
> 
> /etc/asterisk/res_odbc.conf
> -----------------------------
> [mysql]
> enabled => yes
> dsn => MySQL-asterisk
> username => asterisk
> password => qpalym
> pre-connect => yes
> 
> and
> 
> /etc/asterisk/cdr_odbc.conf
> ----------------------------
> [global]
> dsn=MySQL-asterisk
dsn=mysql
> loguniqueid=yes
> dispositionstring=yes
> table=cdr
> 
> But after a call hangup I get the following error:
> cdr_odbc.c: Unable to retrieve database handle. CDR failed.
> 
> What is going wrong here, please?
> 
> -Thorsten-
> 
> 
> --
> _____________________________________________________________________
> -- 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

On 6/18/2012 11:52 AM, Thorsten G?llner wrote:
> Hi,
>
> I am trying now for over 4 hours setting up cdr-logging via odbc into a
> mysql database. But with no success. Do you have any hint for me?
>
*SNIP*
> But after a call hangup I get the following error:
> cdr_odbc.c: Unable to retrieve database handle. CDR failed.
>
> What is going wrong here, please?
The DSN that you specify in cdr_odbc.con should be the DSN you 
configured in res_odbc.conf, in this case "mysql" versus
"MySQL-asterisk".

I beat head against the desk for hours because of this same issue.

Am 18.06.2012 21:49, schrieb James Sharp:
> On 6/18/2012 11:52 AM, Thorsten G?llner wrote:
>> Hi,
>>
>> I am trying now for over 4 hours setting up cdr-logging via odbc into a
>> mysql database. But with no success. Do you have any hint for me?
>>
>
> *SNIP*
>
>> But after a call hangup I get the following error:
>> cdr_odbc.c: Unable to retrieve database handle. CDR failed.
>>
>> What is going wrong here, please?
>
> The DSN that you specify in cdr_odbc.con should be the DSN you 
> configured in res_odbc.conf, in this case "mysql" versus 
> "MySQL-asterisk".
>
> I beat head against the desk for hours because of this same issue.
>
This solved the problem. The CDR is written to the mysql database. 
Thanks. It would never have occurred to me.

BUT the cli command "odbc show all" still shows and
"uninitialized" last
connection attempt. Never mind?

CLI> odbc show all

ODBC DSN Settings
-----------------

   Name:   mysql
   DSN:    MySQL-asterisk
     Last connection attempt: 1970-01-01 01:00:00
   Pooled: No
   Connected: Yes