New Server: Troubleshooting PostgreSQL Syntax Error

Question

I've been hired to move a website that runs on a PHP / PostgreSQL backend from 1 server to another. I've never used PostgreSQL, and thought that this would be a very simple task. I was wrong.

I successfully troubleshot some issues already related to Privileges and permission denied issues.

My current issue has stumped me. When I visit the homepage, I get a 500 error.

The new server is running PostgreSQL 9.2.14, and I don't know what version of PostgreSQL was on the old. I only received files & a database dump - I never had access to the old server itself.

Checking the error log, I see this:

[26-Feb-2016 08:06:45 America/New_York] PHP Fatal error:  Uncaught exception 'DbQueryException' with message 'ERROR:  syntax error at or near ")" LINE 1: SELECT ABS(right)
                        ^ QUERY:  SELECT ABS(right) CONTEXT:  PL/pgSQL function _acl_right_agg(integer[],acl_entity_rights) line 21 at assignment' in /home/user/public_html/includes/lib/db/postgres.inc.php:208 Stack trace:
#0 /home/user/public_html/includes/lib/db/base.inc.php(439): PostgresDB->query('\n              ...', 3)
#1 /home/user/public_html/includes/wt-navigation.php(3831): BaseDB->getScalar('\n              ...', '-2', 'anonymous','pub...')
#2 /home/user/public_html/includes/wt-navigation.php(3913): NavCache::lookupNodeId('-1')
#3 /home/user/public_html/includes/wt-node-types.php(112): NavCache::lookupEdgeName('/', Object(NavNodeTop))
#4 /home/user/public_html/includes/wt-navigation.php(3499): NavNodeRoot->_loadProperties()
#5 /home/user/public_html/init/site.inc.php(22): require_once('/home/user... in /home/user/public_html/includes/lib/db/postgres.inc.php on line 208

The code isn't commented and doesn't indicate what (if any) CMS it actually is built on.

How can I troubleshoot this syntax error, knowing that the website worked perfectly fine on a previous server? Are there any debugging steps that I should take (editing the code) to track this down?

Alex K. · Accepted Answer · 2016-02-26 13:42:15Z

1

right is a reserved word.

It should be escaped in the query: SELECT ABS("right") ...

answered Feb 26, 2016 at 13:42

Alex K.

177k32 gold badges276 silver badges299 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

David W Over a year ago

Thank you. That puts me onto the right track, anyway. The code itself doesn't actually represent that specific query on line 21 or line 208 of postgres.inc.php, so now my task is to figure out what's generating that query!

Alex K. Over a year ago

I think its always been reserved either because of right join or right() so no idea why it worked on the other installation.

David W Over a year ago

I found it! I didn't realize that the _acl_right_agg was a "function" in the database itself - here I was searching the PHP files. I ran a grep for that function on the PHP files and found some upgrade scripts that was "inserting" that function into the database, which tipped me off to the truth. Running into a new issue now, but this is progress. Thank you.

Collectives™ on Stack Overflow

New Server: Troubleshooting PostgreSQL Syntax Error

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related