[Home]

Summary:ASTERISK-25891: res_odbc: Crash using ODBC in mysql with heavy usage
Reporter:Carlos Oliva (coliva)Labels:
Date Opened:2016-04-04 07:48:40Date Closed:2016-05-12 09:53:14
Priority:MajorRegression?No
Status:Closed/CompleteComponents:Resources/res_odbc
Versions:13.8.0 Frequency of
Occurrence		Frequent
Related
Issues:
is related toASTERISK-25921 res_odbc: Crash of system in case of function invocation of ODBC
Environment:debian jessie 8.4 (KVM virtual machine)Attachments:( 0) backtrace1.txt
( 1) backtrace2.txt
( 2) backtrace3.txt
( 3) bt_libmysql5_7.txt
Description:testing release 13.8.0 I found crashes making heavy use of ODBC with mysql. This issue seems to not happen in 13.7.2 release.

We make a heavy usage of mysql using ODBC, dialplan functions and CEL. The isse seems to happen with 4 or 5 calls using queues, cel logging to mysql and lots of database calls in the dialplan.

I think maybe related to Change-Id: I37a84def5ea4ddf93868ce8105f39de078297fbf (res_odbc: Remove connection management) because it directly affects to odbc connection settings.

I will attach some backtraces where you can see clearly the issue is related to odbc and libmysqlclient.so.18
Comments:By: Asterisk Team (asteriskteam) 2016-04-04 07:48:41.380-0500

Thanks for creating a report! The issue has entered the triage process. That means the issue will wait in this status until a Bug Marshal has an opportunity to review the issue. Once the issue has been reviewed you will receive comments regarding the next steps towards resolution.

A good first step is for you to review the [Asterisk Issue Guidelines|https://wiki.asterisk.org/wiki/display/AST/Asterisk+Issue+Guidelines] if you haven't already. The guidelines detail what is expected from an Asterisk issue report.

Then, if you are submitting a patch, please review the [Patch Contribution Process|https://wiki.asterisk.org/wiki/display/AST/Patch+Contribution+Process].
By: Michael L. Young (elguero) 2016-04-04 10:55:30.427-0500

It seems like there have been a lot of reports of crashes for users on Debian using libmysqlclient.so.18 lately.

If you look at this issue, ASTERISK-25833, it seems that using the driver from MySQL solved the issue.  Something to try and report back perhaps?
By: Ross Beer (rossbeer) 2016-04-05 08:45:17.672-0500

I'm seeing a similar issue with CentOS and mysql-odbc-connector 5.6 and 5.7
By: Ross Beer (rossbeer) 2016-04-05 11:32:15.527-0500

Are you using Threading=0 in your config?
By: Ross Beer (rossbeer) 2016-04-06 07:55:49.278-0500

There has been a major change in how ODBC is handled in 13.8, please see change in:

https://gerrit.asterisk.org/#/c/2005/

Basically ODBC is now multi-threaded and therefore, pooling is recommended when using UnixODBC
By: Carlos Oliva (coliva) 2016-04-06 10:12:29.533-0500

Ross:

No, I'm not using Threading=0 but I'm using pooling in UnixODBC as recomended in upgrade.txt. Do you think Threading=0 can help?

My ODBC Config files are:

root@asterisktest:/# cat /etc/odbcinst.ini
[MySQL]

Description = ODBC for MySQL
Driver = /usr/local/lib/mysql-connector-odbc-5.3.6/libmyodbc5a.so
Setup  = /usr/local/lib/mysql-connector-odbc-5.3.6/libmyodbc5S.so
FileUsage = 1
Pooling = Yes
root@asterisktest:/# cat /etc/odbc.ini
[DB]
Description    = Connection to DB1
Driver         = MySQL
Database       = myDb
Server         = x.y.z.k
user           = myuser
Password       = mypass
Port           = 3306
CPTimeout      = 120

Michael:

As you suggested I tried using latest debian mysql client libraries provided by mysql (packages 5.7.11-1debian8 and with 5.6.29-1debian8 ) both with core dump result  using latest mysql-connector-odbc from mysql (BT posted as bt_libmysql_5_7.txt)

Now i'm testing with standard debian8 libmysqlclient18 (5.5.47-0+deb8u1) with latest mysql-connector-odbc (5.3.6) I'll post more feedback when I have more data
By: Carlos Oliva (coliva) 2016-04-06 10:13:35.790-0500

backtrace using latest odbc_connector fro mysql (5.3.6) and latest libmysql
By: Ross Beer (rossbeer) 2016-04-06 10:36:54.241-0500

Carlos: Threading=0 reduced the number of crashes however I have upgraded my CentOS unixODBC to 2.3.4 and the latest mysql-odbc-connector to see if this resolves the issue fully.
By: Carlos Oliva (coliva) 2016-04-06 11:46:29.827-0500

I'm using unixODBC 2.3.1. In the changelog they says that the default thread protection changed to 0 in 2.3.1 release ( http://www.unixodbc.org/unixODBC.html ) and this value asume the driver is threadsafe. Many thanks Ross for pointing me to this.

Probably (I'm just gessing) default libmyodbc in debian 8 (5.1.10) is not compiled with thread-safe and this is causing the problem. I have not seen any crashes in last two hours using mysql provided libmyodbc. Probably mysql provided binaries are compiled with thread-safe option.

I'll test for some days more and if everything is OK I'll close the issue.

Thanks Ross and Michael for the help.

Here is a little howto install last binary odbc mysql connector in debian8:

download last odbc connector from:
https://dev.mysql.com/downloads/connector/odbc/

decompress and copy files to a logic location for example:
tar -zxvf mysql-connector-odbc-5.3.6-linux-debian8-x86-64bit.tar.gz
cd mysql-connector-odbc-5.3.6-linux-debian8-x86-64bit
mkdir /usr/local/lib/mysql-connector-odbc-5.3.6
cp lib/*.so /usr/local/lib/mysql-connector-odbc-5.3.6/

modify /etc/odbcinst.ini with the new libraries:
Driver = /usr/local/lib/mysql-connector-odbc-5.3.6/libmyodbc5w.so
Setup  = /usr/local/lib/mysql-connector-odbc-5.3.6/libmyodbc5S.so

EDIT: Use unicode version of the driver ( libmyodbc5w.so) to avoid crashes ending in mysql_get_character_set_info

By: Rusty Newton (rnewton) 2016-04-06 15:24:44.676-0500

bq. I'll test for some days more and if everything is OK I'll close the issue.

Thanks. If things work out then before we close the issue we should look at updating the wiki or other documentation to help others out who run into your scenario.


By: Carlos Oliva (coliva) 2016-04-14 05:34:24.117-0500

Using latest mysql odbc connectors asterisk still crash sometimes but with less frequency. Now I'm testing with latest unixODBC. Latest version is 2.3.4 and Debian Jessie has 2.3.1

With latest unixODBC I didn't see crashes after 1 day, good news I'll test for some days more.

here is a little howto installl latest unixODBC, latest mysql connector and configure odbc connections.

@Rusty Newton, if you think is appropriate fell free to copy and paste this to the wiki but please allow me some days more to test.

1st be sure to not have debian versions installed:
#apt-get purge unixodbc-dev unixodbc odbcinst1debian2 odbcinst libodbc1 libmyodbc

get unixODBC from ftp://ftp.unixodbc.org/pub/unixODBC/unixODBC-2.3.4.tar.gz
decompress, configure, compile and install unixODBC

#./configure --prefix=/ --includedir=/usr/include
#make
#make install

Now install latest mysql connector from mysql:

download last odbc connector from: https://dev.mysql.com/downloads/connector/odbc/
decompress and copy files to a logic location for example:
tar -zxvf mysql-connector-odbc-5.3.6-linux-debian8-x86-64bit.tar.gz
cd mysql-connector-odbc-5.3.6-linux-debian8-x86-64bit
mkdir /usr/local/lib/mysql-connector-odbc-5.3.6
cp lib/*.so /usr/local/lib/mysql-connector-odbc-5.3.6/

Now configure unixODBC. According to docs in 13.8.0 version pooling in odbc is recommended. Here is an example of odbc config using pooling for mysql:

/etc/odbcinst.ini:
[MySQL]

Description = ODBC for MySQL
Driver = /usr/local/lib/mysql-connector-odbc-5.3.6/libmyodbc5w.so
Setup  = /usr/local/lib/mysql-connector-odbc-5.3.6/libmyodbc5S.so
FileUsage = 1
Pooling = Yes

/etc/odbc.ini:
[DBCON]
Description    = Connection to database
Driver         = MySQL
Database       = myDB
Server         = mySQLIPAddress
user           = myUser
Password       = myPassword
Port           = 3306
CPTimeout      = 120
OPTION         = 3

If you want to test if ODBC works you can do:
echo "select 1" | isql -v DBCON

Now recompile and install asterisk using this new headers (use --with-unixodbc in configure)


By: Rusty Newton (rnewton) 2016-04-21 09:16:26.520-0500

bq. With latest unixODBC I didn't see crashes after 1 day, good news I'll test for some days more.

Great! Let us know.

Thanks for the write up. I'll see if we can use this on the wiki in an appropriate spot.
By: Carlos Oliva (coliva) 2016-04-22 10:39:03.950-0500

After a week in production I can confirm Asterisk 13.8.0 works  using latest ODBC and latest mysql drivers in debian 8, no more coredumps. Probably all the problems in ODBC connection was caused for the res_odbc change in 13.8 release, but I can confirm with latest versions of ODBC and mysql driver Asterisk 13.8 works very well.
Maybe is only my perception but in my environment (with heavy mysql usage) seems that Asterisk 13.8.0 run faster than 13.7.2
By: Rusty Newton (rnewton) 2016-04-25 09:33:02.156-0500

Thanks for the update! That is good to hear.

Can you verify for me again what versions of all relevant libraries that you are using now?
By: Carlos Oliva (coliva) 2016-04-26 02:05:10.921-0500

Yes, we are using:

Debian Jessie 8.4 (3.16.0-4-amd64 #1 SMP Debian 3.16.7-ckt25-1 (2016-03-06) x86_64 GNU/Linux)
libmysqlclient18 5.5.47-0+deb8u1  (from Jessie standard packages)
libmysqlclient-dev 5.5.47-0+deb8u1 (from Jessie standard packages)
mysql-common 5.5.47-0+deb8u1 (from Jessie standard packages)

UnixODBC 2.3.4 from http://www.unixodbc.org/
Mysql-connector-odbc 5.3.6 ( w - unicode version) from mysql-connector-odbc-5.3.6-linux-debian8-x86-64bit binary package from https://dev.mysql.com/downloads/connector/odbc/
connected to remote mysql-5.5 database with InnoDB tables

ODBC configuration as mentioned, with pooling=yes and OPTION=3

Asterisk 13.8.0 with patch mentioned in issue ASTERISK-25888 (segfaults in function can_ring_entry() of app_queue.c) no other patches applied

if you need something more please let me know.

By: Ross Beer (rossbeer) 2016-04-26 08:01:24.348-0500

Carlos,

Have you had issues upgrading to 13.8.2? There appears to be a change that breaks func_odbc totally. Can you confirm if you have the same issue?
By: Carlos Oliva (coliva) 2016-04-27 04:31:57.033-0500

According to changelog the differences between 13.8.0 and 13.8.2 are only in pjsip. As I use chan_sip and not pjsip I did not update to 13.8.2 because It will not make any difference on my environment.

Probably the change you mentioned is Change-Id: I37a84def5ea4ddf93868ce8105f39de078297fbf introduced in 13.8.0 revision. This is a huge change of ODBC, it removes connection management inside asterisk and delegate this to ODBC. For this change I must use pooling=yes in odbcinst.ini This change introduces problems if you are using old ODBC mysql drivers or old unixODBC drivers. It produces some problems too as mentioned in ASTERISK-25938 if you try to use functions that needs that consecutive queries use the same connection, as the connections now are delegated completly to unixODBC driver.

As I can read in ASTERISK-25938 this ODBC change is going to be reverted in 13.9.0, but in my environment this change works very well and queries run faster than in 13.7.2 (is difficult to asseverate but is my impression) I can read in the issue that probably this change will be postponed to Asterisk 14

I can confirm the change-Id: I37a84def5ea4ddf93868ce8105f39de078297fbf works well in my environment because I use the latest version of unixODBC and mysql connector (if you use old versions it breaks Asterisk totally) and I don't use functions in consecutive queries that relays to be executed in the same connection.

If you use an Asterisk without this ODBC change (13.7.2 or future 13.9.0) probably you will not need the parameters "Pooling = Yes" and "CPTimeout = 120" in your unixODBC config files.
By: Joshua C. Colp (jcolp) 2016-04-27 04:37:59.014-0500

[~coliva] func_odbc will indeed return to the original behavior, but the core ODBC changes remain for use elsewhere throughout the system. Additionally ASTERISK-25955 was created to make the func_odbc change something that can be turned on/off depending on the desired behavior.
By: Ross Beer (rossbeer) 2016-04-27 04:45:01.236-0500

Joshua, can you confirm the configuration setting that allows func_odbc to use multiple threads instead of just one DSN in the latest change? We do not require the ability to retrieve the last ID.
By: Joshua C. Colp (jcolp) 2016-04-27 04:49:21.191-0500

[~rossbeer] It doesn't exist yet. That issue is for creating such an option.
By: Carlos Oliva (coliva) 2016-04-27 05:04:16.882-0500

[~jcolp] very greeted to hear this, I think is the correct way. Many thanks to all Asterisk team