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

[Ree, Jan-Albert van]

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: spacewalk-list-bounces at redhat.com <spacewalk-list-bounces at redhat.com> on behalf of Rob Sterenborg <r.sterenborg at netmatch.nl>
Sent: Thursday, February 23, 2017 14:14
To: 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
Spacewalk-list at redhat.com
https://www.redhat.com/mailman/listinfo/spacewalk-list