[Home]

Summary:ASTERISK-23177: [patch] RealTime cant update sipbuddies table when registering or updating friend
Reporter:Denis (den159)Labels:
Date Opened:2014-01-22 08:27:30.000-0600Date Closed:2014-01-23 16:56:01.000-0600
Priority:MajorRegression?Yes
Status:Closed/CompleteComponents:Addons/res_config_mysql
Versions:12.0.0 Frequency of
Occurrence		Constant
Related
Issues:
is caused byASTERISK-20867 Create a realtime configuration sorcery wizard using a new schema for chan_gulp
Environment:Centos 6.5 on Oracle VirtualBoxAttachments:( 0) asterisk-23177-use-field-name.diff
( 1) debug_log
( 2) files.zip
( 3) files2.zip
Description:{noformat}
Connected to Asterisk 12.0.0 currently running on localhost (pid = 6257)
 == Manager 'manager' logged on from 192.168.1.25
   -- Registered SIP '101' at 192.168.1.25:5065
      > Saved useragent "PJSUA v2.1 win32-5.1/i386/msvc-15.0" for peer 101
[Jan 22 09:26:16] WARNING[6284]: res_config_mysql.c:631 update_mysql: MySQL RealTime: Failed to update database: Unknown column 'sip:101@192.168.1.25:5065;ob' in 'field list'
{noformat}
Comments:By: Michael L. Young (elguero) 2014-01-22 17:01:38.240-0600

Thanks for the report.  Before we can proceed, it would be helpful to have a bit more information.

Please include your configs and perhaps your database schema (no data).  Also debug logs would be helpful, from when Asterisk starts up to where this "warning" message occurs.

https://wiki.asterisk.org/wiki/display/AST/Asterisk+Issue+Guidelines#AsteriskIssueGuidelines-Howtoreportabug

https://wiki.asterisk.org/wiki/display/AST/Collecting+Debug+Information

Thanks
By: Denis (den159) 2014-01-23 09:03:33.562-0600

I've attached conf files and db schema. There are no debug logs, because this error occurs right after Aterisk start when registering user or peer and goes on all the time, i can make calls and recieve calls but table t_sippeus is not updated after registering user or peer. This error also where discussed with no success on http://forums.asterisk.org/viewtopic.php?f=1&t=87972. As answered david55 "It looks like the user agent string from the peer contains an SQL string delimiter, which is not being escaped properly when the query is being constructed". Application MYSQL works fine. In Asterisk 11 with the same configs and db schema this error does not occur.
By: Michael L. Young (elguero) 2014-01-23 12:30:48.948-0600

First thing that caught my eye is that your database table for t_sippeus has not been updated to the latest schema for sippeers.  Therefore, some of those columns are not big enough to hold the data that might be saved to it.  You will possibly get truncated data.

Second thing is that if you could enable debugging "core set debug 5", we could see the query that is trying to be run... at least see what exactly is being sent to the MySQL database.  That would help narrow this down very quickly.  I am looking at the code and have an idea but that debug would help to confirm it.
By: Denis (den159) 2014-01-23 13:52:17.309-0600

i`ve delete most of columns and for others set big types, also set debug to 5, error the same, printscreens attached.
Definitely error occurs when sql try to update field "fullcontact" but i cant delete this field because occurs other error, what data was send to this field i have no idea, debug 5 also does not show such info.
By: Michael L. Young (elguero) 2014-01-23 14:06:06.767-0600

Please follow this guide on getting debugging information:

https://wiki.asterisk.org/wiki/display/AST/Collecting+Debug+Information

Thanks
By: Denis (den159) 2014-01-23 14:26:05.234-0600

excuse me, debug log attached this is error place:
[Jan 23 15:24:31] DEBUG[9004] res_config_mysql.c: MySQL RealTime: Connection okay.
[Jan 23 15:24:31] DEBUG[9004] res_config_mysql.c: MySQL RealTime: Update SQL: UPDATE t_sippeus SET `ipaddr` = '192.168.1.25', `sip:101@192.168.1.25:5065;ob` = 'sip:101@192.168.1.25:5065^3Bob', `0` = '0', `PJSUA v2.1 win32-5.1/i386/msvc-15.0` = 'PJSUA v2.1 win32-5.1/i386/msvc-15.0', `101` = '101', `1390508971` = '1390508971', `5065` = '5065' WHERE `name` = '101'
[Jan 23 15:24:31] WARNING[9004] res_config_mysql.c: MySQL RealTime: Failed to update database: Unknown column 'sip:101@192.168.1.25:5065;ob' in 'field list'
By: Michael L. Young (elguero) 2014-01-23 14:51:24.879-0600

Denis - thanks for the info.  Found the bug... it was staring me right in the face but I found it.
By: Michael L. Young (elguero) 2014-01-23 14:53:11.638-0600

Give this patch a try and report back.  Thanks
By: Michael L. Young (elguero) 2014-01-23 14:58:41.858-0600

For history tracking purposes, this bug was introduced in revision 386731.
By: Denis (den159) 2014-01-23 15:09:11.897-0600

Thank You Michael, I`m newbie in Asterisk and never used pathes, just hint me how to apply this patch, have i edit distrib code and reinstall Asterisk or maybe other way ?
By: Michael L. Young (elguero) 2014-01-23 15:19:05.593-0600

How are you installing Asterisk?

You would need to have the source code for Asterisk 12. From within the Asterisk source directory run:
{noformat}"wget -O - https://issues.asterisk.org/jira/secure/attachment/49168/asterisk-23177-use-field-name.diff | patch -p0"{noformat}

Then compile and install.
By: Matt Jordan (mjordan) 2014-01-23 15:28:23.962-0600

I'd say Ship It! on your patch Michael. That's pretty clearly a fat finger of {{->value}} over {{->name}}.
By: Michael L. Young (elguero) 2014-01-23 15:34:57.217-0600

Okay, cool.  Thanks Matt.
By: Denis (den159) 2014-01-23 15:47:34.405-0600

what i did:
cd /usr/src/asterisk*
wget -O - https://issues.asterisk.org/jira/secure/attachment/49168/asterisk-23177-use-field-name.diff | patch -p0
./configure
make && make install
make config
make clean && make distclean

restart Asterisk
the same error
where is my fault ?
By: Denis (den159) 2014-01-23 16:37:46.486-0600

manualy replaced field->value on field->name in res_config_mysql.c, recompile Asterisk and now i`m happy, very big thanks!
By: Michael L. Young (elguero) 2014-01-23 17:06:30.824-0600

Thanks Denis for testing and reporting back.  This fix has been committed and will be in future releases.  Thanks for your contribution.