Open Microscopy Environment

hi all,

Since I upgraded to 4.4.1, I have this error in the PixelData logs (every 4 min it seems...). I did not spot anything wrong in the actual use, so the only consequence afaik is bloated logs. Any idea what it is amd can I safely ignore it for now? I am on windows server 2008, I pasted the diagnostics below as well.

Many thanks


================================================================================
OMERO Diagnostics 4.4.1-ice33-b3035
================================================================================

Commands: java -version 1.7.0 (C:\Program Files (x86)\Java\jre7\bin\java.EXE -- 2 others)
Commands: python -V 2.5.5 (C:\Python25\python.EXE)
Commands: icegridnode --version 3.3.1 (C:\Ice-3.3.1\bin\icegridnode.EXE)
Commands: icegridadmin --version 3.3.1 (C:\Ice-3.3.1\bin\icegridadmin.EXE)
Commands: psql --version not found

Server: icegridnode running
Server: Blitz-0 active (pid = 2292, enabled)
Server: DropBox active (pid = 9080, enabled)
Server: FileServer active (pid = 2628, enabled)
Server: Indexer-0 active (pid = 6468, enabled)
Server: MonitorServer active (pid = 5548, enabled)
Server: OMERO.Glacier2 active (pid = 5400, enabled)
Server: OMERO.IceStorm active (pid = 7436, enabled)
Server: PixelData-0 active (pid = 6044, enabled)
Server: Processor-0 active (pid = 6552, enabled)
Server: Tables-0 inactive (disabled)
Server: TestDropBox inactive (enabled)

Log dir: C:\Users\omero\Desktop\OMERO.server-4.4.1-ice33-b3035\var\log exists

Log files: Blitz-0.log 267.0 MB errors=55 warnings=557
Log files: DropBox.log 1.0 KB
Log files: FileServer.log 0.0 KB
Log files: Indexer-0.log 161.0 KB errors=12 warnings=121
Log files: MonitorServer.log 0.0 KB
Log files: OMEROweb.log 787.0 KB
Log files: OMEROweb_request.log 0.0 KB
Log files: PixelData-0.log 376.0 MB errors=127440 warnings=31860
Log files: PixelData-0.log.1 512.0 MB errors=173178 warnings=43294
Log files: PixelData-0.log.2 512.0 MB errors=173178 warnings=43295
Log files: PixelData-0.log.3 512.0 MB errors=173178 warnings=43294
Log files: PixelData-0.log.4 512.0 MB errors=173178 warnings=43295
Log files: PixelData-0.log.5 512.0 MB errors=173176 warnings=43294
Log files: Processor-0.log 478.0 KB
Log files: Processor-0.log.1 4.0 MB
Log files: Processor-0.log.2 4.0 MB
Log files: Processor-0.log.3 4.0 MB
Log files: Processor-0.log.4 4.0 MB
Log files: Tables-0.log n/a
Log files: TestDropBox.log n/a
Log files: master.err 0.0 KB
Log files: master.out 0.0 KB
Log files: Total size 3226.65 MB

Parsing Blitz-0.log:[line:30] => Server restarted <=

Environment:OMERO_HOME=(unset)
Environment:OMERO_NODE=(unset)
Environment:OMERO_MASTER=(unset)
Environment:PATH=C:\Windows\system32\WindowsPowerShell\v1.0\;C:\Program Files (x86)\Java\jre7\bin\;C:\Python25\;C:\Windo
ws\system32;C:\Windows;C:\Windows\System32\Wbem;C:\Windows\System32\WindowsPowerShell\v1.0\;C:\Ice-3.3.1\bin;C:\Program
Files\MySQL\MySQL Server 5.1\bin
Environment:ICE_HOME=(unset)
Environment:LD_LIBRARY_PATH=(unset)
Environment:DYLD_LIBRARY_PATH=(unset)

OMERO data dir: 'E:\\OMERO' Exists? True Is writable? True
OMERO.web status... [NOT STARTED]

The error:



2012-07-26 11:25:16,015 WARN [ ome.services.util.ServiceHandler] (2-thread-5) InvalidDataAccessResourceUsageException thrown.

org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [select * from (select *, row_number() over (partition by entityid) as dupe from (select e.experimenter, el.id as eventlog, entityid, row_number() over (partition by experimenter) as row from event e, eventlog el, pixels p where e.id = el.event and el.id > ? and action = 'PIXELDATA' and entitytype = 'ome.model.core.Pixels' and p.id = el.entityid and p.repo is null group by e.experimenter, el.id, el.entityid) as x where row <= ? order by row, eventlog asc) as y where dupe = 1]; nested exception is org.postgresql.util.PSQLException: ERROR: syntax error at or near "over"
Position: 39
at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:233)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)

(...)

Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near "over"
Position: 39
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2103)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1836)

(...)

2012-07-26 11:25:16,015 ERROR [ ome.services.pixeldata.PixelDataThread] (2-thread-1) ExceptionException!
ome.conditions.ApiUsageException: PreparedStatementCallback; bad SQL grammar [select * from (select *, row_number() over (partition by entityid) as dupe from (select e.experimenter, el.id as eventlog, entityid, row_number() over (partition by experimenter) as row from event e, eventlog el, pixels p where e.id = el.event and el.id > ? and action = 'PIXELDATA' and entitytype = 'ome.model.core.Pixels' and p.id = el.entityid and p.repo is null group by e.experimenter, el.id, el.entityid) as x where row <= ? order by row, eventlog asc) as y where dupe = 1]; nested exception is org.postgresql.util.PSQLException: ERROR: syntax error at or near "over"
Position: 39
at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:233)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
(...)



2012-07-26 11:25:16,015 WARN [ ome.services.util.ServiceHandler] (2-thread-4) InvalidDataAccessResourceUsageException thrown.

org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [select * from (select *, row_number() over (partition by entityid) as dupe from (select e.experimenter, el.id as eventlog, entityid, row_number() over (partition by experimenter) as row from event e, eventlog el, pixels p where e.id = el.event and el.id > ? and action = 'PIXELDATA' and entitytype = 'ome.model.core.Pixels' and p.id = el.entityid and p.repo is null group by e.experimenter, el.id, el.entityid) as x where row <= ? order by row, eventlog asc) as y where dupe = 1]; nested exception is org.postgresql.util.PSQLException: ERROR: syntax error at or near "over"
Position: 39
at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:233)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
(...)


Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near "over"
Position: 39
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2103)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1836)
(...)

2012-07-26 11:25:16,015 ERROR [ ome.services.pixeldata.PixelDataThread] (2-thread-1) ExceptionException!
ome.conditions.ApiUsageException: PreparedStatementCallback; bad SQL grammar [select * from (select *, row_number() over (partition by entityid) as dupe from (select e.experimenter, el.id as eventlog, entityid, row_number() over (partition by experimenter) as row from event e, eventlog el, pixels p where e.id = el.event and el.id > ? and action = 'PIXELDATA' and entitytype = 'ome.model.core.Pixels' and p.id = el.entityid and p.repo is null group by e.experimenter, el.id, el.entityid) as x where row <= ? order by row, eventlog asc) as y where dupe = 1]; nested exception is org.postgresql.util.PSQLException: ERROR: syntax error at or near "over"
Position: 39
at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:233)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
(...)

Hi,

It looks like your problems are the result of this error:

Code: Select all

ERROR: syntax error at or near "over"

It's not clear from the information you provided which version of PostgreSQL you are using. The minimum version required by OMERO 4.4.1 is PostgreSQL 8.4; earlier versions do not support SQL window functions (OVER).

I would advise checking that you are using PostgreSQL 8.4 or later. It looks like you're running OMERO on Windows, so I would suggest going directly to version 9.1 (since the Windows port is quite new, a lot of bugs were fixed and while earlier versions will work, you'll get better reliability and performance out of the latest stable release).

For reference, please see the compatibility table here:
https://www.openmicroscopy.org/site/sup ... postgresql
and you can download version 9.1 here:
http://www.enterprisedb.com/products-se ... ad#windows
(there are both 32 and 64 bit versions)

Regards,
Roger

Thanks for the info. Indeed, I do use 8.3; i'll upgrade to 9.1. I did not see that change on the dependencies; you might want to put a warning about it in the upgrade page...

What would be the consequences of that? Could anything be wrong with the database/imports/analysis done so far?

Many thanks...

While it's documented elsewhere, it is indeed missing from the upgrade page. Apologies for that omission--I'll update it shortly.

There should be few, if any, consequences of this. The upgrade will not have been affected, since this new syntax is not used in the upgrade script. It only affects a single query which is used in nextPixelsDataLogForRepo() in components/model/src/ome/util/SqlAction.java which is only used in a single place, in query() in components/server/src/ome/services/pixeldata/PersistentEventLogLoader.java. From what I can tell, this should only affect pyramid generation for big images. Given that it was throwing an exception due to the query failing, simply upgrading and restarting the server should be sufficient to allow it to continue processing--you should not have any issues with the data being incorrect or anything being broken, it will just continue and complete the actions which were not carried out due to the query failing.


Regards,
Roger