[Spacewalk-list] Spacewalk, PostgreSQL 9.6, SQL reserved words

[Rob Sterenborg]

Right, I didn't find that but it should do the trick I guess.


--
Thanks,
Rob




From: spacewalk-list-bounces at redhat.com [mailto:spacewalk-list-bounces at redhat.com] On Behalf Of Andreas Dijkman
Sent: donderdag 23 februari 2017 16:41
To: spacewalk-list at redhat.com
Subject: Re: [Spacewalk-list] Spacewalk, PostgreSQL 9.6, SQL reserved words

Or you could fix the query as stated in this PR on GitHub: https://github.com/spacewalkproject/spacewalk/pull/465  

This adds PostgreSQL 9.6-support.

Met vriendelijke groet,

Andreas Dijkman
http://www.cygnis.nl/ | Stationsweg 3B | 9726 AC Groningen

On 23 Feb 2017, at 14:32, Ree, Jan-Albert van <mailto:J.A.v.Ree at marin.nl> wrote:

Just use the stock CentOS7 PostgreSQL 9.2 , which works flawless out of the box.

And as a tip : also use the same PostgreSQL database for your jabberd database, this for us has finally completely fixed all OSAD problems.
Been running over a month without any machine losing connection through OSAD.
--
Jan-Albert



Jan-Albert van Ree  | Linux System Administrator | MARIN Support Group
MARIN | T +31 317 49 35 48 | mailto:J.A.v.Ree at marin.nl | http://www.marin.nl

MARIN news: http://www.marin.nl/web/News/News-items/Few-places-left-for-Offshore-and-Ship-hydrodynamics-courses.htm

________________________________________
From: mailto:spacewalk-list-bounces at redhat.com <mailto:spacewalk-list-bounces at redhat.com> on behalf of Rob Sterenborg <mailto:r.sterenborg at netmatch.nl>
Sent: Thursday, February 23, 2017 14:14
To: mailto:spacewalk-list at redhat.com
Subject: [Spacewalk-list] Spacewalk, PostgreSQL 9.6, SQL reserved words

Hi,

We currently use Spacewalk 2.2 with PostgreSQL 8.4 on CentOS 6 and we have problems with it (TRACEBACK emails), so I want to replace these old servers. I'm now setting up Spacewalk 2.6 with PostgreSQL 9.6 on CentOS 7.

Installing and importing the database seems to have worked, however..

Now I get a different "TRACEBACK" error email from Spacewalk, and my eye fell on this:
==========
Extra information about this error:
SQL Error generated: ('ERROR:  syntax error at or near "method"\nLINE 3: ...                        sa.remaining_tries, at.label method,\n                                                                ^\n', <connection object at 0x7f8d7b8271e0; dsn: 'port=5432 host=nmo\-spd\-001\.netmatch\.local password=xxxxxxx dbname=spaceschema user=spaceuser', closed: 0>, '\n                    select sa.action_id id, a.version,\n                           sa.remaining_tries, at.label method,\n                           at.unlocked_only,\n                           a.prerequisite\n                      from rhnServerAction sa,\n                           rhnAction a,\n                           rhnActionType at\n                     where sa.server_id = %(server_id)s\n                       and sa.action_id = a.id\n                       and a.action_type = at.id\n                       and sa.status in (0, 1) -- Queued or picked up\n                       and a.earliest_action <= curren
t_timest
amp -- Check earliest_action\n                       and not exists (\n                           select 1\n                             from rhnServerAction sap\n                            where sap.server_id = %(server_id)s\n                              and sap.action_id = a.prerequisite\n                              and sap.status != 2 -- completed\n                           )\n                      order by a.earliest_action, a.prerequisite nulls first, a.id\n    ')
==========

Searching for this error I found these:
http://git.net/ml/spacewalk-devel/2016-11/msg00004.html
https://github.com/spacewalkproject/spacewalk/blob/master/backend/server/handlers/xmlrpc/queue.py

In /usr/share/rhn/server/handlers/xmlrpc/queue.py, "def _future_actions_enabled(self):" reads from line 229:
==========
   _query_queue_future = rhnSQL.Statement("""
                   select sa.action_id id, a.version,
                          sa.remaining_tries, at.label method,
                          at.unlocked_only,
                          a.prerequisite
                     from rhnServerAction sa,
                          rhnAction a,
                          rhnActionType at
                    where sa.server_id = :server_id
                      and sa.action_id = a.id
                      and a.action_type = at.id
                      and sa.status in (0, 1) -- Queued or picked up
                      and a.earliest_action <= current_timestamp + numtodsinterval(:time_window * 3600, 'second')  -- Check earliest_action
                      and at.label in ('packages.update', 'errata.update',
                           'packages.runTransaction', 'packages.fullUpdate')
                     order by a.earliest_action, a.prerequisite nulls first, a.id
   """)
==========

I tried this query manually and it failed on "at" and "method". Both look like reserved words to me (my SQL editor agrees in this), and when I replace those with "atype" and "method1" the query works.

Of course this is not *the* solution, because my Python-fu isn't that great and I haven't looked where this "method" is being used. However, I do think that this needs to be fixed.

When I read about installing Spacewalk[1] I found that I can use PostgreSQL > 8.4, so I thought 9.6 should be fine. Now I'm not so sure anymore. Do I need to downgrade PostgreSQL for this to work, and if yes, to which version?


[1] https://fedorahosted.org/spacewalk/wiki/PostgreSQLServerSetup


--
Thanks,
Rob


_______________________________________________
Spacewalk-list mailing list
mailto:Spacewalk-list at redhat.com
https://www.redhat.com/mailman/listinfo/spacewalk-list

_______________________________________________
Spacewalk-list mailing list
mailto:Spacewalk-list at redhat.com
https://www.redhat.com/mailman/listinfo/spacewalk-list