Bug 862058

Could you try whether changing standard_conforming_strings in postgresql configuration fixes the issue?

See https://www.redhat.com/archives/spacewalk-devel/2012-June/msg00023.html

I have tried setting standard_conforming_strings to off without any luck. When I made the change now osa-dispatcher is having problems reconnecting. I get the following error when I try to restart osa-dispatcher after changing the setting of postgresql and even setting it back I still get the error.

2012/10/03 10:16:55 -07:00 9473 0.0.0.0: rhnSQL/driver_postgresql._execute_('ERROR', 'ERROR: \n        update rhnPushClient\n           set state_id = %(offline_id)s\n         where state_id = %(online_id)s\n           and last_ping_time is not null\n           and current_timestamp > next_action_time\n     ')
2012/10/03 10:16:55 -07:00 9473 0.0.0.0: rhnSQL/driver_postgresql._execute_('ERROR', "PARAMS: {'online_id': 1, 'offline_id': 2}")
2012/10/03 10:16:55 -07:00 9473 0.0.0.0: rhnSQL/driver_postgresql._execute_('ERROR', 'ERROR: \n        select id, name, shared_key, jabber_id\n          from rhnPushClient\n         where state_id = %(online_id)s\n           and last_ping_time is not null\n           and next_action_time is null\n           and jabber_id is not null\n     ')
2012/10/03 10:16:55 -07:00 9473 0.0.0.0: rhnSQL/driver_postgresql._execute_('ERROR', "PARAMS: {'online_id': 1}")
2012/10/03 10:16:55 -07:00 9473 0.0.0.0: rhnSQL/driver_postgresql._execute_('ERROR', "ERROR: \n        select a.id, sa.server_id, pc.jabber_id,\n               date_diff_in_days(current_timestamp, earliest_action) * 86400 delta\n          from\n               rhnServerAction sa,\n               rhnAction a,\n               rhnPushClient pc\n         where pc.server_id = sa.server_id\n           and sa.action_id = a.id\n           and sa.status in (0, 1) -- Queued or picked up\n           and not exists (\n               -- This is like saying 'this action has no\n               -- prerequisite or has a prerequisite that has completed\n               -- (status = 2)\n               select 1\n                 from rhnServerAction sap\n                where sap.server_id = sa.server_id\n                  and sap.action_id = a.prerequisite\n                  and sap.status != 2\n            )\n         order by earliest_action\n     ")
2012/10/03 10:16:55 -07:00 9473 0.0.0.0: rhnSQL/driver_postgresql._execute_('ERROR', 'PARAMS: {}')


This seems to be caused by changing the standard_conforming_strings and restarting the services. So with that change it actually broke Spacewalk further than what it was.

I have found that remote commands set via API are not having the issue. It only seems like remote command run from the web ui are causing the internal server error. Any ideas as to how to fix this issue?

Any updates as to the status of this ticket?

The issue has been fixed in spacewalk master
commit 42c5187caf34d2cc64876209e660e046b8dda83a
    Use the binary binding.
commit 4148c37113015260df7982d81f8e5dbb6b522c41
    Bind bytea with PG_BYTEA.
    Addressing
    DBD::Pg::st execute failed: ERROR:  invalid input syntax for type bytea

The above fix seems to have resolved the issue noted in this bug. 

The only problem I have right now is that during the troubleshooting phase when I was asked to set PostgreSQL setting it caused osa-dispatcher to fail to start with the errors noted above. Is there anything I can do to resolve this related issue?

(In reply to comment #6)
> The above fix seems to have resolved the issue noted in this bug. 
> 
> The only problem I have right now is that during the troubleshooting phase
> when I was asked to set PostgreSQL setting it caused osa-dispatcher to fail
> to start with the errors noted above. Is there anything I can do to resolve
> this related issue?

Revert the postgresql.conf changes -- you should not need to change the default.

I reverted back to the defaults before making the changes that resolved the original issue. Even reverting back it still is causing osa-dispatcher to return those errors.

Any other ideas as to how to resolve the osa-dispatcher issue that was caused by switching the standard_conforming_strings setting. After setting that setting osa-dispatcher started giving the errors above. I have changed the standard_conforming_strings = on to off and reset all back to defaults. Even after changing the settings back to the defaults osa-dispatcher still continues to give the same errors. Not really sure what is going on. Any ideas that could be provided would be greatly appreciated. Thank you for your time and have a great day!

One additional thing I just found. It seems that remote commands are actually going and working with osa-dispatcher as /var/log/osad on the client shows the rhn_check running. So it seems like osa-dispatcher is running just giving those errors.

Moving ON_QA. Packages that address this bugzilla should now be available in yum repos at http://yum.spacewalkproject.org/nightly/

Spacewalk 1.8 has been released: https://fedorahosted.org/spacewalk/wiki/ReleaseNotes18

Why was this closed when I was still requesting assistance? In troubleshooting this bug I came into an issue that is causing a whole bunch of errors with osa-dispatcher and even spacewalk-repo-sync. Below you will find the error in osa-dispatcher. I would like to get this resolved before we close this bug as it relates to this bug. 

2012/10/03 10:16:55 -07:00 9473 0.0.0.0: rhnSQL/driver_postgresql._execute_('ERROR', 'ERROR: \n        update rhnPushClient\n           set state_id = %(offline_id)s\n         where state_id = %(online_id)s\n           and last_ping_time is not null\n           and current_timestamp > next_action_time\n     ')
2012/10/03 10:16:55 -07:00 9473 0.0.0.0: rhnSQL/driver_postgresql._execute_('ERROR', "PARAMS: {'online_id': 1, 'offline_id': 2}")
2012/10/03 10:16:55 -07:00 9473 0.0.0.0: rhnSQL/driver_postgresql._execute_('ERROR', 'ERROR: \n        select id, name, shared_key, jabber_id\n          from rhnPushClient\n         where state_id = %(online_id)s\n           and last_ping_time is not null\n           and next_action_time is null\n           and jabber_id is not null\n     ')
2012/10/03 10:16:55 -07:00 9473 0.0.0.0: rhnSQL/driver_postgresql._execute_('ERROR', "PARAMS: {'online_id': 1}")
2012/10/03 10:16:55 -07:00 9473 0.0.0.0: rhnSQL/driver_postgresql._execute_('ERROR', "ERROR: \n        select a.id, sa.server_id, pc.jabber_id,\n               date_diff_in_days(current_timestamp, earliest_action) * 86400 delta\n          from\n               rhnServerAction sa,\n               rhnAction a,\n               rhnPushClient pc\n         where pc.server_id = sa.server_id\n           and sa.action_id = a.id\n           and sa.status in (0, 1) -- Queued or picked up\n           and not exists (\n               -- This is like saying 'this action has no\n               -- prerequisite or has a prerequisite that has completed\n               -- (status = 2)\n               select 1\n                 from rhnServerAction sap\n                where sap.server_id = sa.server_id\n                  and sap.action_id = a.prerequisite\n                  and sap.status != 2\n            )\n         order by earliest_action\n     ")
2012/10/03 10:16:55 -07:00 9473 0.0.0.0: rhnSQL/driver_postgresql._execute_('ERROR', 'PARAMS: {}')

You said

  The above fix seems to have resolved the issue noted in this bug.

for the original remote_command_conf.pxt issue and as such we've marked the bugzilla as resolved.

If you experience issues with osa-dispatcher after tweaking the standard_conforming_strings settings, it would be good to open new bugzilla for that, with postgresql-*.log lines included because clearly the client side errors (from osa-dispatcher.log, I assume) don't provide any clue as to what the problem might be. Now you even mention some issues with spacewalk-repo-sync -- again, please open a separate bugzilla with full description and logs because clearly noone is able to resolve that without having more information.

This bugzilla is marked as resolved for the remote command issue from comment 0.

I will go ahead and open a new bug if you really want me to. But I really don't think the original issue is fully resolved as in troubleshooting this bug it caused the other errors to be generated and I feel is the same bug. I looked at the postgresql-*.log and it gives me these errors. 

WARNING:  nonstandard use of \\ in a string literal at character 1247
HINT:  Use the escape string syntax for backslashes, e.g., E'\\'.

This leads me to think that this bug is still causing issues and should stay open until I do not see any errors (on something that was caused by this bug.).

I truly feel this bug is not resolved until all the escape issues are resolved as escapes is the reason for opening this bug in the first place. Also you did try to help before and I replied back with no answer until it was closed. Why didn't you say to open a new bug at that time and why the change now?

(In reply to comment #15)
> I will go ahead and open a new bug if you really want me to.

Yes I do.

> But I really
> don't think the original issue is fully resolved

Does remote_command_conf.pxt work on Spacewalk 1.8 with stock PostgreSQL settings or not?

> bug it caused the other errors to be generated and I feel is the same bug. I
> looked at the postgresql-*.log and it gives me these errors. 
> 
> WARNING:  nonstandard use of \\ in a string literal at character 1247
> HINT:  Use the escape string syntax for backslashes, e.g., E'\\'.

These are warnings, not errors. You need to look for lines that have ERROR on them.

> This leads me to think that this bug is still causing issues and should stay
> open until I do not see any errors (on something that was caused by this

No. Having a bug open for multiple releases with changing topics and problem descriptions is not how we want to track Spacewalk issues in bugzilla. If someone has a problem with remote commands on Spacewalk 1.7, I want them to find resolved bugzilla, pointing them to upgrade to 1.8. Not a still-open bug with multiple different tracebacks.

> I truly feel this bug is not resolved until all the escape issues are
> resolved as escapes is the reason for opening this bug in the first place.

To my knowledge, they are resolved in Spacewalk 1.8 (to my knowledge == I spent nontrivial amounts of time on the issue and verified the fixes).

You never mentioned osa-dispatcher issue before changing the setting. Maybe by doing so, something got inserted to database in a format / way that hindered osa-dispatcher's operation. It needs to be investigated and fixed. But not under the generic manifest of "Escapes are broken".

> Also you did try to help before and I replied back with no answer until it
> was closed. Why didn't you say to open a new bug at that time and why the
> change now?

Because I see you throwing more and more random cruft in here -- lately the mention of spacewalk-repo-sync.

https://bugzilla.redhat.com/show_bug.cgi?id=878195  Bug created. Please assist on that new bug (even though I still think it is the same bug).