Cdr ODBC doesn't work

Asterisk Asterisk Support
1

We are migrating from cdr mysql to cdr odbc. I’ve installed unixODBC and conector MySQL (my database is mysql) I’m running a test using Asterisk 13.19.0 on CentOS 7.9 I’ve using this guide: https://wiki.asterisk.org/wiki/display/AST/Configuring+res_odbc but when I try to put a call, I’m getting this message: res_odbc.c:958 odbc_obj_connect: res_odbc: Error SQLConnect=-1 errno=2002 [unixODBC][MySQL][ODBC 5.3(w) Driver]Can’t connect to local MySQL server through socket '/tmp/mysql
[Jan 5 16:05:00] ERROR[68847]: cdr_odbc.c:175 odbc_log: Unable to retrieve database handle. CDR failed.
I configured /etc/odbcinst.ini, /etc/odbc.ini, /etc/asterisk/res_odbc.conf, /etc/asterisk/res_odbc.conf
but when I execute odbc show, I got this:
ODBC DSN Settings
-----------------

** Name: asterisk**
** DSN: asterisk**
** Last connection attempt: 2021-01-05 16:06:11**
** Number of active connections: 0 (out of 1)**

2

Does the mysql socket exist in /tmp/mysql? Does asterisk have permission to read and write to the socket? Seems like an odd place for a socket.

Can you share your config files?

1 Like
3

Sure, here is my config files

/etc/odbcinst.ini
[MySQL]
Description = MySQL driver
#Driver = /usr/lib/odbc/libmyodbc.so
#Setup = /usr/lib/odbc/libodbcmyS.so
Driver = /usr/lib64/odbc/libmyodbc5w.so
Setup = /usr/lib64/odbc/libmyodbc5a.so
CPTimeout =
CPReuse =

/etc/odbc.ini
[asterisk]
Driver = MySQL
Description = MySQL ODBC 3.51 Driver DSN
Server = localhost
Port = 3306
Database = asterisk
Option = 3
Socket =

/etc/asterisk/res_odbc.conf
[ENV]

[asterisk]

enabled => yes
dsn => asterisk
username => asterisk
password => asterisk
pre-connect => yes

[mysql2]
enabled => no
dsn => MySQL-asterisk
username => myuser
password => mypass
pre-connect => yes

[sqlserver]
enabled => no
dsn => mickeysoft
max_connections => 5
username => oscar
password => thegrouch
pre-connect => yes
sanitysql => select count(*) from systables

backslash_is_escape => no

there is no mysql socket in /tmp/

4

Try commenting out or deleting Socket = in /etc/odbc.ini and restart asterisk.

1 Like
5

@erichowey thanks for help. I did like you said, but it still getting same message.

6

does isql -v asterisk work? I would check out the link below to help rule out if this is an odbc config issue or something more related to asterisk.

1 Like
7

Identify where MySQL’s socket is and tell ODBC.

Maybe one of these will help:

mysql --execute="show variables like 'socket';"
+---------------+---------------------------+
| Variable_name | Value                     |
+---------------+---------------------------+
| socket        | /var/lib/mysql/mysql.sock |
+---------------+---------------------------+

rgrep --ignore-case sock /etc/my.cnf*
/etc/my.cnf:	socket				= /var/lib/mysql/mysql.sock

sudo lsof -c mysqld | grep --ignore-case sock
mysqld    5205 mysql   19u  unix 0xffff9482c1e43b80       0t0 83865932 /var/lib/mysql/mysql.sock
(note that this depends on the socket name containing 'sock')
1 Like
8

I’ve got this output

isql -v asterisk
[08S01][unixODBC][MySQL][ODBC 5.3(w) Driver]Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’ (2)
[ISQL]ERROR: Could not SQLConnect

9

I’ve got this:

mysql --execute=“show variables like ‘socket’;”
ERROR 1045 (28000): Access denied for user ‘root’@‘localhost’ (using password: NO)

grep --ignore-case sock /etc/my.cnf*
/etc/my.cnf:socket=/var/lib/mysql/mysql.sock
grep: /etc/my.cnf.d: Is a directory

lsof -c mysqld | grep --ignore-case sock
mysqld 1140 mysql 15u unix 0xffff92c3b7f3cc80 0t0 20768 /var/lib/mysql/mysql.sock

How can I tell to ODBC where is MySQL socket?

10

You don’t have ‘~/.my.cnf’ configured to provide host, database, user, and password.

You missed the ‘r’ in ‘rgrep’. ‘rgrep’ is an alias (or shell script) for ‘grep --recursive’

I don’t use ODBC so I’m guessing something like ‘socket = /var/lib/mysql/mysql.sock’ in ‘/etc/odbc.ini’ may do the trick.

1 Like
11

I configured odbc.ini with path to socket, but it doesn’t work. How can I configure to have my.cnf?

12

~/.my.cnf is unrelated to ODBC. It is only for the MySQL shell.

13

That would be done with the socket variable in my.cnf.

I think you need to take a step back and figure out what you’re trying to accomplish. Do you want to configure ODBC to connect via socket or do you want it to connect via IP?.. or are you just desperately trying to get it to work any method possible? However if it was used via IP when cdr mysql worked, I would continue to connect via IP instead of mucking around with your mysql instance.

Since your ODBC connection is failing, you need to look at your odbc config extremely carefully and ensure that all of your required dependencies are installed for a successful Mysql/ODBC connection. The full list of ODBC DSN settings for the mysql odbc connector can be found here:
https://dev.mysql.com/doc/connector-odbc/en/connector-odbc-configuration-connection-parameters.html

Below is a sample of a working odbc.ini from one of my systems:

[asterisk]
Driver = MySQL
Server = 192.168.110.25
User = <redacted>
Password = <redacted>
Database = <redacted>
readtimeout = 10
writetimeout = 10

Maybe try using Server 127.0.0.1 instead of localhost?
Maybe try setting your User and Password parameters in odbc.ini?

2 Likes
14

This is incorrect. socket in my.cnf sets where the MySQL server socket is located.

15

Please note ‘~/.my.cnf’ (‘tilde slash dot’) is for the shell. ‘/etc/my.cnf’ (and /etc/my.cnf.d/ if used) is for the daemon. In any case, both are unrelated to ODBC – except to give you a clue to the location of the MySQL daemon socket.

1 Like
16

That’s work now!
My mistake, I was using “localhost” but I’m using an external database, so I inserted the database server IP!

Thanks for help!

[asterisk]
Driver = MySQL
Server = my_database_IP
User = <redacted>
Password = <redacted>
Database = <redacted>
readtimeout = 10
writetimeout = 10
1 Like
17

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.