Asterisk and mysql realtime

Hi, trying to run asterisk with realtime sip peers configuration via mysql and from time to time, after server restart asterisk can’t connect to mysql and just logging such messages:

[Mar 27 20:30:41] WARNING[1109]: res_config_mysql.c:1552 mysql_reconnect: MySQL RealTime: Insufficient memory to allocate MySQL resource.
[Mar 27 20:30:41] WARNING[1109]: res_config_mysql.c:1552 mysql_reconnect: MySQL RealTime: Insufficient memory to allocate MySQL resource.
[Mar 27 20:30:41] WARNING[1109]: res_config_mysql.c:1552 mysql_reconnect: MySQL RealTime: Insufficient memory to allocate MySQL resource.
[Mar 27 20:30:41] WARNING[1109]: res_config_mysql.c:1552 mysql_reconnect: MySQL RealTime: Insufficient memory to allocate MySQL resource.
[Mar 27 20:30:41] WARNING[1109]: res_config_mysql.c:1552 mysql_reconnect: MySQL RealTime: Insufficient memory to allocate MySQL resource.
[Mar 27 20:30:41] WARNING[1109]: res_config_mysql.c:1552 mysql_reconnect: MySQL RealTime: Insufficient memory to allocate MySQL resource.
[Mar 27 20:30:41] NOTICE[1109]: chan_sip.c:28003 handle_request_register: Registration from '202 <sip:202@10.76.0.221:5060>' failed for '192.168.6.21:5060' - Wrong password

Restarting asterisk daemon solve the problem, but do not always have such an opportunity: to connect and restart the service.

I have several servers with such problem(about five or six), running ubuntu 12.04/14.04 and latest certified asterisk 11.

Maybe somebody faced with similar problem and knows how to resolve it.
Unfortunately I did not find any answers on this issue in internet and documentation.

1 Like

Do you have autoload=yes in modules.conf?
How many client connections are configured in my.ini?
Have you considered changing to res_odbc?

1 Like

Hi mkozusnik.
Yes, in /etc/asterisk/modules.conf, i have autoload mode on:

[modules]
autoload=yes

I didn’t configured MySQL, so it has default settings.
And beneath i provide my MySQL/realtime and ODBC settings:

extconfig.conf

sippeers => mysql,general,sip_buddies
sipusers => mysql,general,sip_buddies

res_config_mysql.conf

[general]
dbhost = 127.0.0.1
dbname = asterisk
dbuser = root
dbpass = qwerty
dbport = 3306
;dbsock = /tmp/mysql.sock
dbcharset = utf8

And i have some configs for odbc, i using it to write some cdr information in dialplan():

/etc/odbc.ini

[asterisk-connector]
Driver = MySQL
Description = Connector/ODBC 3.51 Driver DSN
Server = localhost
Port = 3306
User = root
Password = qwerty
Database = calls
Socket = /var/run/mysqld/mysqld.sock

/etc/odbcinst.ini

[MySQL]
Description = ODBC for MySQL
Driver = /usr/lib/i386-linux-gnu/odbc/libmyodbc.so
Setup = /usr/lib/i386-linux-gnu/odbc/libodbcmyS.so
FileUsage = 1

/etc/asterisk/res_odbc.conf
[ENV]

[asterisk]
enabled = yes
dsn = asterisk-connector
username = root
password = qwerty
pooling = no
limit = 1
pre-connect = yes

Hope this more detailed information can help to resolve this bad issue.
And i think, i do not try realtime over odbc, yet.

One difference I notice between yours and an old 1.4 installation I have is I’m using the socket. It’s faster if your asterisk and mysql are on the same server and you don’t have the tcp overhead.
dbsock = /tmp/mysql.sock
;dbcharset = latin1

I can also give you a config for res_odbc option if you would like to try it.

1 Like

Thank’s, sure i like to try, may be via odbc it can work without that problem.
Think problem can be in ubuntu startup system, or mysql start up scripts in ubuntu packages.

Hi,

You can connect directly to Mysql without ODBC.

Good luck

I try it, and it works very bad, see my first post…Problem resolving with odbc.

I have several servers so and never had this problem, see my configuration files.

res_config_mysql.conf
[pbxip]
dbhost = 127.0.0.1
dbname = db name
dbuser = db user
dbpass = db password
dbport = 3306
dbsock = /var/run/mysqld/mysqld.sock
dbcharset = latin1
requirements=warn ; or createclose or create char

extconfig.conf
sipusers => mysql,pbxip,cc_sip_buddies
sippeers => mysql,pbxip,cc_sip_buddies
iaxusers => mysql,pbxip,cc_iax_buddies
iaxpeers => mysql,pbxip,cc_iax_buddies

1 Like

Thank’s a lot roger, do you use ubuntu on your servers?
I think the problem may be this certain distr. of linux.

1 Like

What is the solution for this issue? I am facing the same problem. Did rogger settings fixed your issue?

Hi, Sivan.
My solution for this heartache issue is in using odbc driver, instead native mysql.
On two different instalations it works very well.

1 Like

You mean you uninstalled native mysql driver and you used just odbc driver to connect to mysql?

I just changed asterisk configuration to use odbc for realtime.

1 Like

Thanks for replying.
Could you tell me what all conf files you changed in order to do that?
Did you still end up using res_config_mysql.conf and res_mysql.conf ?