Summary:	ASTERISK-23351: [patch]Updating realtime sippeers using res_config_pgsql backend fails when 'port' column is null
Reporter:	jmls (jmls)	Labels:
Date Opened:	2014-02-23 11:06:15.000-0600	Date Closed:	2017-03-27 14:41:48
Priority:	Minor	Regression?	No
Status:	Closed/Complete	Components:	Resources/res_config_pgsql
Versions:	12.0.0	Frequency of Occurrence	Occasional
Related Issues:	is related to ASTERISK-23459 [patch]Incorrect check for key field and NULL column values in update_odbc
Environment:	debian on raspberry pi	Attachments:	( 0) patch.txt
Description:	when using postgres with sip realtime, an update fails when a port number is '' . The actual error is below: [Feb 23 11:37:47] ERROR[32292] res_config_pgsql.c: PostgreSQL RealTime: Query Failed: UPDATE sippeers SET fullcontact = '', lastms = '0', useragent = '', regserver = '', regseconds = '0', port = '', ipaddr = '' WHERE name = '<name>' [Feb 23 11:37:47] ERROR[32292] res_config_pgsql.c: PostgreSQL RealTime: Query Failed because: ERROR:  invalid input syntax for integer: "" LINE 1: ...nt = '', regserver = '', regseconds = '0', port = '', ipaddr...                                                             ^ (PGRES_FATAL_ERROR)
Comments:	By: Matt Jordan (mjordan) 2014-02-23 17:35:37.372-0600 We require a complete debug log to help triage the issue. This document will provide instructions on how to collect debugging logs from an Asterisk machine for the purpose of helping bug marshals troubleshoot an issue: https://wiki.asterisk.org/wiki/display/AST/Collecting+Debug+Information Please make sure you have sip debugging enabled as well. By: Rusty Newton (rnewton) 2014-03-11 13:38:02.219-0500 Suspended due to lack of activity. Please request a bug marshal in #asterisk-bugs on the IRC network irc.freenode.net to reopen the issue should you have the additional information requested.  Further information can be found at http://www.asterisk.org/developers/bug-guidelines By: Walter Doekes (wdoekes) 2014-03-12 03:59:04.463-0500 This is still a bug, and easy to reproduce. [~jmls]: could you paste my workaround if you still have it? By: jmls (jmls) 2014-03-12 04:32:59.924-0500 this patch was supplied by wdoekes. I've been running with it for a while now, and it seems to have fixed the problem By: zvision (zvision) 2014-03-12 04:42:10.590-0500 I run into a similar issue with res_config_odbc [here|https://issues.asterisk.org/jira/browse/ASTERISK-23459], I have created a patch for this issue. Revieving the attached patch for res_config_pgsql I have noticed it sets all nullable columns to NULL if an empty string is found. I think a better approach would be like in res_config_odbc, where only integer columns are set to NULL (and probably also time/date columns should be set to NULL as well). By: Walter Doekes (wdoekes) 2014-03-12 06:08:37.266-0500 Yes, zvision, the approach in patch.txt was a workaround. The proper fix probably looks more like yours. By: Rusty Newton (rnewton) 2014-03-12 07:40:04.776-0500 Linking to ASTERISK-23459 due to similarity of issue. By: Rusty Newton (rnewton) 2014-03-12 07:43:05.880-0500 The next step here will be for someone to throw a patch on reviewboard: [Code Review Process|https://wiki.asterisk.org/wiki/display/AST/Code+Review] By: zvision (zvision) 2014-03-12 16:00:38.130-0500 If no one is working on the patch, I could provide one. By: zvision (zvision) 2014-03-13 05:08:26.120-0500 I have just created a patch and put it on Review Board [here|https://reviewboard.asterisk.org/r/3346/]. It is for branch 11. By: Walter Doekes (wdoekes) 2014-03-17 06:49:37.190-0500 [~jmls]: can you confirm that the latest reviewboard version works for you? By: jmls (jmls) 2014-03-20 09:27:46.217-0500 I have tried to apply the patch, but got patching file res/res_config_pgsql.c patch: **** malformed patch at line 16: @@ -683,7 +688,7 @@ By: zvision (zvision) 2014-03-20 09:46:59.245-0500 Which patch version did you try? The one for 1.8 or 12? I have just tried the v12 patch with the following command: patch -p0 -i res_config_pgsql.diff and got no problems with SVN branch 12. Please make sure to use the patch posted on Review Board. By: zvision (zvision) 2014-03-28 14:22:20.394-0500 @Matt I see no patch is committed to fix this issue, so it is not fixed. The linked issue fixes the same problem for ODBC config, but not for res_config_pgsql module. By: Walter Doekes (wdoekes) 2014-03-31 02:29:05.067-0500 Like zvision said. This patch is still in Review (waiting for testing). The odbc version of this patch was indeed committed. By: Sean Bright (seanbright) 2017-03-27 14:41:48.140-0500 I'm not able to reproduce with Asterisk 13.15. If you are able to reproduce with 13.15 or newer, please feel free to re-open.