[Home]

Summary:ASTERISK-03259: [patch] realtime intermittent SQL lookup failure
Reporter:ennuyeux72 (ennuyeux72)Labels:
Date Opened:2005-01-12 06:40:51.000-0600Date Closed:2011-06-07 14:10:32
Priority:MinorRegression?No
Status:Closed/CompleteComponents:Channels/chan_iax2
Versions:Frequency of
Occurrence
Related
Issues:
Environment:Attachments:( 0) res_odbc_rev2.diff
Description:From time to time I am getting a forbidden message when trying to make calls thru an asterisk server where realtime is being used to store the iax entitities. Typically a call will not go through with the error shown below in 'additional information'. If the same call is made immediately afterwards then the call goes through fine.
We are using odbc to connect to a mysql database.
A tentative guess is that perhaps asterisk is not check if its db connection is up before attempting a query hence the error below.

****** ADDITIONAL INFORMATION ******


Jan 12 12:34:03 WARNING[19411]: SQL Execute error!
[SELECT * FROM iax WHERE name = ?]

Jan 12 12:34:03 NOTICE[19411]: Rejected connect attempt from 83.146.53.36, who w
as trying to reach '442086733557@'

Redhat 9
2.4.20-8 kernel
Flexiserve Server
Comments:By: Mark Spencer (markster) 2005-01-12 09:31:16.000-0600

Can you confirm that is what the problem is and also provide a patch for how you can connect?  Is there a way to make mysql not automatically drop the database connects?
By: ennuyeux72 (ennuyeux72) 2005-01-12 10:38:37.000-0600

Presumably I can just look in the asterisk source code and determine if it does a connection check?...

As this is on a live system and the database is remote we close any connections that are up longer than 5 mintes on the mysql server side.
By: Mark Spencer (markster) 2005-01-12 10:48:24.000-0600

Is that not a feature of the MySQL ODBC driver though?
By: Brian West (bkw918) 2005-01-12 10:53:08.000-0600

sounds like a config option is b0rked...
By: ennuyeux72 (ennuyeux72) 2005-01-12 11:11:03.000-0600

1. looking to see if reconnect is a mysql odbc driver feature.

2. Here is config in the meantime:

res_odbc.conf

[MySQL-ast-extc]
dsn => MySQL-ast-extc
username => xxxxxxxxx
password => xxxxxxxxx
pre-connect => yes


extconfig.conf

iaxfriends => odbc,MySQL-ast-extc,iax
sipfriends => odbc,MySQL-ast-extc,sip
voicemail => odbc,MySQL-ast-extc,voicemail
extensions => odbc,MySQL-ast-extc,extensions


odbc.ini

[MySQL-ast-extc]
DSN             = MySQL-ast-extc
DESCRIPTION     = MySQL For Asterisk extconfig
DRIVER          = MySQL
PORT            = 3306
SOCKET          = /var/lib/mysql/mysql.sock
SERVER          = xxxxxxxxxx
USER            = xxxxxxxxxx
PASSWORD        = xxxxxxxxxx
DATABASE        = xxxxxxxxxx
OPTION          = 3
TRACEFILE       = /tmp/odbc.MySQL.trace
TRACE           = On
DEBUG           = On
DEBUGFILE       = /tmp/odbc.MySQL.debug
By: () 2005-01-12 16:06:46.000-0600

You can add a [Communications] and try that. Or try adding ConnectOptions under the database header, such as:

[Mysql]
..
..
ConnectOptions = <server connect options>
By: ennuyeux72 (ennuyeux72) 2005-01-13 14:14:58.000-0600

Will put on the Communications/Connectionoptions stuff today and report back in 36 hours.

In the meantime here is some trace from odbc mysql driver

>do_query
| >check_if_server_is_alive
| | info: server status:0
| <check_if_server_is_alive
| error: Message: Lost connection to MySQL server during query
| >set_dbc_error
| | error: message: Lost connection to MySQL server during query
| <set_dbc_error
| >translate_error
| <translate_error
<do_query
By: delvar (delvar) 2005-01-17 09:48:27.000-0600

ok call me stupid, but i cant find out what options i should use, could someone point me to a setup guide with these options... or just give them me :)

the closest thing iv found is 'options=' in the myodbc manual but i cant see any options for auto reconnect.
By: Mark Spencer (markster) 2005-01-27 23:38:46.000-0600

So where does this stand?
By: ennuyeux72 (ennuyeux72) 2005-01-31 12:40:27.000-0600

Not sure what or where to put the Communications/Connectionoptions  stuff.
By: ennuyeux72 (ennuyeux72) 2005-02-04 09:35:15.000-0600

This seems to be related. On a box where realtime is enabled. I get the following error. But in this error just scrolls down the cli and a restart is required to get the SQL queries working again.

Feb  2 06:59:09 WARNING[19480]: SQL Execute error!
[SELECT * FROM sip WHERE name = ?]
By: () 2005-02-04 09:39:32.000-0600

This may be related to bug 3504.
By: ennuyeux72 (ennuyeux72) 2005-02-06 13:21:03.000-0600

Am still getting this error. It doesn't seem to be the number of mysql connections as the database server is used for a number of other services that are not affected.

Why does the SQL Execute error just repeat itself down the screen? Shouldn't a reconnect of some sort be attempted? Only restarting asterisk makes any difference at all and allows asterisk to reconnect to the database.
By: ennuyeux72 (ennuyeux72) 2005-02-06 19:24:22.000-0600

Feb  7 01:24:53 WARNING[32761]: res_config_odbc.c:105 realtime_odbc:  SQL Execut
e error!
[SELECT * FROM sip WHERE name = ?]

Feb  7 01:24:53 WARNING[32761]: res_config_odbc.c:105 realtime_odbc:  SQL Execut
e error!
[SELECT * FROM sip WHERE name = ?]

Just got this again on a live box. Had to restart asterisk.
By: Mark Spencer (markster) 2005-02-13 01:33:51.000-0600

Try the patch from ASTERISK-3452.
By: ennuyeux72 (ennuyeux72) 2005-02-13 04:36:38.000-0600

sorry i should have added a note earlier.
anthm's patch has resolved this issue for us. We have not had a single occurrence of the problem in any of our production servers since the patch was installed over a week ago. The problem used to happen several times per day.
By: nick (nick) 2005-02-13 08:42:32.000-0600

So we can go ahead and close this out?
By: Mark Spencer (markster) 2005-02-13 11:11:51.000-0600

Not until we merge anthm's patch or a variation of it.  I'd rather the process be a bit more transparent (anthm's patch performs an additional query at *each* attempt to query.  I will work with anthm to do an updated version that should be more efficient and then lets confirm that fixes it for this user before closing this one out.
By: Anthony Minessale (anthm) 2005-02-17 09:59:57.000-0600

The patch is reworked please try it.
By: Mark Spencer (markster) 2005-02-17 10:33:37.000-0600

Please confirm latest CVS head (with no additional patches) fixes the problem, so we can close this out.  Thanks!
By: drmac (drmac) 2005-02-17 17:02:34.000-0600

what version of mysql?
What version of the mysql-odbc driver? mysql-odbc driver version 2 cannot connect to post 4.0 databases. you need version 3.

How does ODBC handle pre 4.0 versions of mysql which have no support for prepared statements?
By: ennuyeux72 (ennuyeux72) 2005-02-18 04:04:42.000-0600

ok i'll update the cvs on one of our boxes tonight and give it a go.
By: ennuyeux72 (ennuyeux72) 2005-02-22 03:25:14.000-0600

Ok CVS head for 21 Feb 05 does not work for me so I have reverted to the patched version. I left the box running overnight and woke to find the following error message repeated on the asterisk cli.

WARNING[22366]: SQL Alloc Handle failed!

Could not make calls. Restarting the box allowed me to make calls again.
By: Anthony Minessale (anthm) 2005-02-22 13:37:04.000-0600

Try this patch (disclaimer on file) this implements the config option sanitycheck => X in res_odbc.conf

set it to 1 to check when an error is encountered
set it to 2 to check everytime before doing any sql

2 is the way it was with my first patch
1 is the way the second way i patched it (default)
By: Mark Spencer (markster) 2005-02-22 23:12:24.000-0600

But why didn't it work the way it was?
By: Anthony Minessale (anthm) 2005-02-23 09:11:06.000-0600

I think different people's demands and reliability of sql server may call for more or less agressive sanity check so that's why I made it variable strength.
By: ennuyeux72 (ennuyeux72) 2005-02-23 09:35:04.000-0600

Will this patch apply to the latest cvs head?
By: delvar (delvar) 2005-03-01 10:15:34.000-0600

i applied the patch but it wouldnt compile, to get it to compile i had to manualy modify the /usr/src/asterisk/include/asterisk/res_odbc.h file and add the folowing,
at line 36 (in odbc_obj struct): 'int sanitycheck;'
at line 43 modify the 'odbc_obj *new_odbc_obj' line and add ', int sanitycheck' after 'char *password'

it compiled fine after that i will make a diff file if anyone wants it but i dont see the point with only 2 changes.
By: ennuyeux72 (ennuyeux72) 2005-03-08 06:00:58.000-0600

finally got round to putting this patch in.

sanitycheck is set to 2. If there is a problem I expect it to appear withing the next 3 hours. I will let you know about then.
By: damin (damin) 2005-03-17 21:17:48.000-0600

ennuyeux72: In your last update, you mentioned that you were going to followup on the updated patch that anthm posted. That was on the 8th, and you expected it would fail within three hours. It has now been over 216 hours and we don't have any followup. Should we assume that the patch fixed the issue?
By: ennuyeux72 (ennuyeux72) 2005-03-18 05:22:12.000-0600

Hi this works if sanitycheck is set to 2. Sorry for delay.
By: ennuyeux72 (ennuyeux72) 2005-03-21 03:51:50.000-0600

So is there any chance of getting this in cvs because as it stands the realtime version in cvs just won't stay up in our production environment? We seem to only get things working with the sanitycheck option set to 2.
Are there any more tests anyone would like me to run?
By: damin (damin) 2005-03-21 17:26:59.000-0600

As I understand the issue, Mark needs to make a decision here, and until he does I'm not sure what else we can do. I do not want to speak for Mark, but I think the concern is the balancing the performance impact of additional, potentially unneccessary SQL queries versus Anthm's desire to know the instant a failure might occurs at the expense of a little processor and bandwidth. I believe that Anthm has provided an option with his latest patch that allows the user the flexilibity of choosing the model to use, which is probably the route that I would choose if I were coding it. I'm in favor of applying the patch and letting the user have choice, but Mark needs to make the decision on this and/or make a suggestion/modification to the patch. Perhaps defaulting to the less intensive method, but adding comments to allow users to choose their own in the config file?

Or maybe I'm just way off base. ;)
By: Mark Spencer (markster) 2005-03-26 01:44:17.000-0600

It should be possible to *only* execute this "check for the db connection to be up" when there is na actual failure in the SQLExecute.  My understanding is that this is what has already been implemented.  I want to see this be diagnosed if it's not fixing it, not simply create another, completely unnecessary mode in which the "check for the db being up" SQLExecute is executed arbitrarily in the absense of any reason to think the connection is in the down state.
By: Michael Jerris (mikej) 2005-05-23 21:33:28

Okay, let's bring this one back up again.  Can we get a clearer issue of this so we can troubleshoot the root issue.  Pleas provide details of versions of mysql, mysql odbc driver and odbc.  Also, does this problem persist (without the patch, or with sanity check set to 1) if you turn off the 5 minute connection timeout.  If I read the error messages correctly, you are getting an error when the connection is dropped in the middle of a query, could this be related to an unreliable network?  Does the same behavior happen if it is from a local database with the same 5 minute timeout.  Please provide as much detail as possible on this so we can get this one resolved for good.
By: ennuyeux72 (ennuyeux72) 2005-07-13 10:27:41

Apologies for long delay.

mysql version 4.0.21
unixodbc 2.2.6
myodbc 3.51.06

Will test by removing the 5 minute disconnect thing and get back to you.
By: Michael Jerris (mikej) 2005-07-20 22:57:59

suspended this issue due to lack of response from the original poster.  We can not resolve this issue with a propper solution without the requested information.  Please re-open this issue when you are able to provide answers to the outstanding questions if this is still an issue for you.