BadSqlGrammarException

Support
1

Hi there.

I deployed ODK with PostgreSQL 9.0 on RHEL 6.4 to use for a project in
South Africa.

We developed two forms and data was being uploaded with no problem until
yesterday when the data gatherers started complaining that they couldn't
connect to the Aggregate Server.

I logged in and found that Tomcat was not running and restarted it. Again
it was stable until I logged in to Aggregate after which it crashed. The
only error I could find in /var/log/tomcat6/catalina.out is this:

at java.lang.Thread.run(Thread.java:679)

Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or
near ")"
Position: 248
at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2102)
at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1835)
at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:500)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:388)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:273)
at
com.jolbox.bonecp.PreparedStatementHandle.executeQuery(PreparedStatementHandle.java:172)
at
org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:644)
at
org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:587)
... 67 more
org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback;
bad SQL grammar [SELECT "_URI", "_CREATOR_URI_USER", "_CREATION_DATE",
"_LAST_UPDATE_URI_USER", "_LAST_UPDATE_DATE", "TABLE_ID", "TABLE_KEY",
"DB_TABLE_NAME", "TYPE", "TABLE_ID_ACCESS_CONTROLS" FROM
"edm_prod"."__ODKTABLES__TABLE_DEFINITIONS" WHERE "TABLE_ID" IN ();];
nested exception is org.postgresql.util.PSQLException: ERROR: syntax error
at or near ")"
Position: 248

I am a sysadmin and don't know any Java at all but it seems to me like ODK
generated a malformed SQL statement which executes when ODK Aggregate is
viewed.

I have no idea where to even start looking to correct this and any help
will be much appreciated.

Thanks a million

Lawrence

2

Are there any other errors? This error should not have caused the server to
stop.
Look in the catalina.log, stdout.log and stderr.log in the Tomcat logs
directory.

This particular error is fixed in ODK Aggregate 1.4. See
http://code.google.com/p/opendatakit/wiki/AggregateReleaseNotes
for caveats / instructions on upgrading.

Mitch

··· On Tue, Nov 26, 2013 at 9:58 AM, Lawrence Strydom wrote:

Hi there.

I deployed ODK with PostgreSQL 9.0 on RHEL 6.4 to use for a project in
South Africa.

We developed two forms and data was being uploaded with no problem until
yesterday when the data gatherers started complaining that they couldn't
connect to the Aggregate Server.

I logged in and found that Tomcat was not running and restarted it. Again
it was stable until I logged in to Aggregate after which it crashed. The
only error I could find in /var/log/tomcat6/catalina.out is this:

at java.lang.Thread.run(Thread.java:679)

Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or
near ")"
Position: 248
at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2102)
at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1835)
at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:500)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:388)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:273)
at
com.jolbox.bonecp.PreparedStatementHandle.executeQuery(PreparedStatementHandle.java:172)
at
org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:644)
at
org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:587)
... 67 more
org.springframework.jdbc.BadSqlGrammarException:
PreparedStatementCallback; bad SQL grammar [SELECT "_URI",
"_CREATOR_URI_USER", "_CREATION_DATE", "_LAST_UPDATE_URI_USER",
"_LAST_UPDATE_DATE", "TABLE_ID", "TABLE_KEY", "DB_TABLE_NAME", "TYPE",
"TABLE_ID_ACCESS_CONTROLS" FROM "edm_prod"."__ODKTABLES__TABLE_DEFINITIONS"
WHERE "TABLE_ID" IN ();]; nested exception is
org.postgresql.util.PSQLException: ERROR: syntax error at or near ")"
Position: 248

I am a sysadmin and don't know any Java at all but it seems to me like ODK
generated a malformed SQL statement which executes when ODK Aggregate is
viewed.

I have no idea where to even start looking to correct this and any help
will be much appreciated.

Thanks a million

Lawrence

--

Post: opendatakit@googlegroups.com
Unsubscribe: opendatakit+unsubscribe@googlegroups.com
Options: http://groups.google.com/group/opendatakit?hl=en

You received this message because you are subscribed to the Google Groups
"ODK Community" group.
To unsubscribe from this group and stop receiving emails from it, send an
email to opendatakit+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

--
Mitch Sundt
Software Engineer
University of Washington
mitchellsundt@gmail.com

3

Hi Mitch.

Thanks for your reply.

I let the server run overnight and it was fine. After reading your post
this morning I logged in to the web interface and it promptly crashed.
Here is the output of the catalina.out logfile. I did a full system search
for the other logs you mentioned but they are not to be found. I can only
assume that the default RHEL installation of Tomcat writes to a single log
file only.

at

org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)
at java.lang.Thread.run(Thread.java:679)
Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or
near ")"
Position: 248
at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2102)
at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1835)
at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:500)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:388)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:273)
at
com.jolbox.bonecp.PreparedStatementHandle.executeQuery(PreparedStatementHandle.java:172)
at
org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:644)
at
org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:587)
... 67 more

Thanks

Lawrence

··· On Tuesday, 26 November 2013 19:58:10 UTC+2, Lawrence Strydom wrote: > > Hi there. > > I deployed ODK with PostgreSQL 9.0 on RHEL 6.4 to use for a project in > South Africa. > > We developed two forms and data was being uploaded with no problem until > yesterday when the data gatherers started complaining that they couldn't > connect to the Aggregate Server. > > I logged in and found that Tomcat was not running and restarted it. Again > it was stable until I logged in to Aggregate after which it crashed. The > only error I could find in /var/log/tomcat6/catalina.out is this: > > at java.lang.Thread.run(Thread.java:679) > Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or > near ")" > Position: 248 > at > org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2102) > at > org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1835) > at > org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257) > at > org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:500) > at > org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:388) > at > org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:273) > at > com.jolbox.bonecp.PreparedStatementHandle.executeQuery(PreparedStatementHandle.java:172) > at > org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:644) > at > org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:587) > ... 67 more > org.springframework.jdbc.BadSqlGrammarException: > PreparedStatementCallback; bad SQL grammar [SELECT "_URI", > "_CREATOR_URI_USER", "_CREATION_DATE", "_LAST_UPDATE_URI_USER", > "_LAST_UPDATE_DATE", "TABLE_ID", "TABLE_KEY", "DB_TABLE_NAME", "TYPE", > "TABLE_ID_ACCESS_CONTROLS" FROM "edm_prod"."__ODKTABLES__TABLE_DEFINITIONS" > WHERE "TABLE_ID" IN ();]; nested exception is > org.postgresql.util.PSQLException: ERROR: syntax error at or near ")" > Position: 248 > > I am a sysadmin and don't know any Java at all but it seems to me like ODK > generated a malformed SQL statement which executes when ODK Aggregate is > viewed. > > I have no idea where to even start looking to correct this and any help > will be much appreciated. > > Thanks a million > > Lawrence > > >
4

In the mean time I will attempt an upgrade to V1.4

thanks

··· On Tuesday, 26 November 2013 19:58:10 UTC+2, Lawrence Strydom wrote: > > Hi there. > > I deployed ODK with PostgreSQL 9.0 on RHEL 6.4 to use for a project in > South Africa. > > We developed two forms and data was being uploaded with no problem until > yesterday when the data gatherers started complaining that they couldn't > connect to the Aggregate Server. > > I logged in and found that Tomcat was not running and restarted it. Again > it was stable until I logged in to Aggregate after which it crashed. The > only error I could find in /var/log/tomcat6/catalina.out is this: > > at java.lang.Thread.run(Thread.java:679) > Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or > near ")" > Position: 248 > at > org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2102) > at > org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1835) > at > org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257) > at > org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:500) > at > org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:388) > at > org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:273) > at > com.jolbox.bonecp.PreparedStatementHandle.executeQuery(PreparedStatementHandle.java:172) > at > org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:644) > at > org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:587) > ... 67 more > org.springframework.jdbc.BadSqlGrammarException: > PreparedStatementCallback; bad SQL grammar [SELECT "_URI", > "_CREATOR_URI_USER", "_CREATION_DATE", "_LAST_UPDATE_URI_USER", > "_LAST_UPDATE_DATE", "TABLE_ID", "TABLE_KEY", "DB_TABLE_NAME", "TYPE", > "TABLE_ID_ACCESS_CONTROLS" FROM "edm_prod"."__ODKTABLES__TABLE_DEFINITIONS" > WHERE "TABLE_ID" IN ();]; nested exception is > org.postgresql.util.PSQLException: ERROR: syntax error at or near ")" > Position: 248 > > I am a sysadmin and don't know any Java at all but it seems to me like ODK > generated a malformed SQL statement which executes when ODK Aggregate is > viewed. > > I have no idea where to even start looking to correct this and any help > will be much appreciated. > > Thanks a million > > Lawrence > > >
5

OK it seems the upgrade did the trick.

thanks!

··· On Tuesday, 26 November 2013 19:58:10 UTC+2, Lawrence Strydom wrote: > > Hi there. > > I deployed ODK with PostgreSQL 9.0 on RHEL 6.4 to use for a project in > South Africa. > > We developed two forms and data was being uploaded with no problem until > yesterday when the data gatherers started complaining that they couldn't > connect to the Aggregate Server. > > I logged in and found that Tomcat was not running and restarted it. Again > it was stable until I logged in to Aggregate after which it crashed. The > only error I could find in /var/log/tomcat6/catalina.out is this: > > at java.lang.Thread.run(Thread.java:679) > Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or > near ")" > Position: 248 > at > org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2102) > at > org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1835) > at > org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257) > at > org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:500) > at > org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:388) > at > org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:273) > at > com.jolbox.bonecp.PreparedStatementHandle.executeQuery(PreparedStatementHandle.java:172) > at > org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:644) > at > org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:587) > ... 67 more > org.springframework.jdbc.BadSqlGrammarException: > PreparedStatementCallback; bad SQL grammar [SELECT "_URI", > "_CREATOR_URI_USER", "_CREATION_DATE", "_LAST_UPDATE_URI_USER", > "_LAST_UPDATE_DATE", "TABLE_ID", "TABLE_KEY", "DB_TABLE_NAME", "TYPE", > "TABLE_ID_ACCESS_CONTROLS" FROM "edm_prod"."__ODKTABLES__TABLE_DEFINITIONS" > WHERE "TABLE_ID" IN ();]; nested exception is > org.postgresql.util.PSQLException: ERROR: syntax error at or near ")" > Position: 248 > > I am a sysadmin and don't know any Java at all but it seems to me like ODK > generated a malformed SQL statement which executes when ODK Aggregate is > viewed. > > I have no idea where to even start looking to correct this and any help > will be much appreciated. > > Thanks a million > > Lawrence > > >