[GeoNetwork-devel] Indexing / SpatialIndexWriter failed to remove features in PostGIS spatial index

Hi all,

I've a node (using trunk) which fails removing features from the
PostGIS spatial index. It happens at some point in SpatialIndexWriter
removeFeatures on harvesting.
The catalogue DB connection is still working, only the datastore one
looks to be disconnected. Search does not return records because
they're not indexed anymore due to the error.
So I was thinking something is wrong with the datastore DB connection.
I tried both JDBC or JNDI but got the same.

Any ideas ?

Here is the exception
2012-08-06 18:00:01,033 ERROR [geonetwork.datamanager] - The metadata
document index with id=339 is corrupt/invalid - ignoring it. Error:
null
java.io.IOException
        at org.geotools.jdbc.JDBCFeatureStore.removeFeatures(JDBCFeatureStore.java:398)
        at org.fao.geonet.kernel.search.spatial.SpatialIndexWriter.delete(SpatialIndexWriter.java:216)
        at org.fao.geonet.kernel.search.SearchManager.deleteGroup(SearchManager.java:741)
        at org.fao.geonet.kernel.search.SearchManager.buildIndexDocument(SearchManager.java:763)
        at org.fao.geonet.kernel.search.SearchManager.indexGroup(SearchManager.java:709)
        at org.fao.geonet.kernel.DataManager.indexMetadata(DataManager.java:583)
        at org.fao.geonet.kernel.DataManager.indexMetadataGroup(DataManager.java:431)
        at org.fao.geonet.kernel.harvest.harvester.geonet.Aligner.updateMetadata(Aligner.java:640)
        at org.fao.geonet.kernel.harvest.harvester.geonet.Aligner.access$400(Aligner.java:61)
        at org.fao.geonet.kernel.harvest.harvester.geonet.Aligner$2.handleInfo(Aligner.java:539)
        at org.fao.geonet.kernel.mef.MEFVisitor.handleXml(MEFVisitor.java:88)
        at org.fao.geonet.kernel.mef.MEFVisitor.visit(MEFVisitor.java:48)
        at org.fao.geonet.kernel.mef.MEFLib.visit(MEFLib.java:185)
        at org.fao.geonet.kernel.harvest.harvester.geonet.Aligner.updateMetadata(Aligner.java:523)
        at org.fao.geonet.kernel.harvest.harvester.geonet.Aligner.align(Aligner.java:172)
        at org.fao.geonet.kernel.harvest.harvester.geonet.Harvester.harvest(Harvester.java:126)
        at org.fao.geonet.kernel.harvest.harvester.geonet.GeonetHarvester.doHarvest(GeonetHarvester.java:264)
        at org.fao.geonet.kernel.harvest.harvester.AbstractHarvester$HarvestWithIndexProcessor.process(AbstractHarvester.java:397)
        at org.fao.geonet.kernel.MetadataIndexerProcessor.processWithFastIndexing(MetadataIndexerProcessor.java:39)
        at org.fao.geonet.kernel.harvest.harvester.AbstractHarvester.harvest(AbstractHarvester.java:427)
        at org.fao.geonet.kernel.harvest.harvester.HarvesterJob.execute(HarvesterJob.java:29)
        at org.quartz.core.JobRunShell.run(JobRunShell.java:213)
        at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:557)
Caused by: org.postgresql.util.PSQLException: An I/O error occured
while sending to the backend.
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:283)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:479)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:367)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:271)
        at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
        at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
        at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
        at org.geotools.jdbc.JDBCDataStore.ensureAuthorization(JDBCDataStore.java:1911)
        at org.geotools.jdbc.JDBCFeatureStore.removeFeatures(JDBCFeatureStore.java:395)
        ... 22 more
Caused by: java.io.EOFException
        at org.postgresql.core.PGStream.ReceiveChar(PGStream.java:261)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1620)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
        ... 30 more

Could we catch the exception and try to reset the datastore connection ?

Thanks.

Francois

Hi Francois,

Same thing happening with Oracle here - in fact I was going to open a bug about it too.

Happens here if the oracle database is restarted and may happen at other times too (was trying to nail these down). It seems to me that the spatialindexwriter (or geotools) code might be/is probably holding onto a connection and thus when the database is restarted/reset it ends up trying to use a closed connection and fails. I was planning to spend a bit more time on this at it is obviously critical/blocker.

Cheers,
Simon
________________________________________
From: Francois Prunayre [fx.prunayre@anonymised.com]
Sent: Tuesday, 7 August 2012 5:33 PM
To: Devel geonetwork-devel@lists.sourceforge.net
Subject: [GeoNetwork-devel] Indexing / SpatialIndexWriter failed to remove features in PostGIS spatial index

Hi all,

I've a node (using trunk) which fails removing features from the
PostGIS spatial index. It happens at some point in SpatialIndexWriter
removeFeatures on harvesting.
The catalogue DB connection is still working, only the datastore one
looks to be disconnected. Search does not return records because
they're not indexed anymore due to the error.
So I was thinking something is wrong with the datastore DB connection.
I tried both JDBC or JNDI but got the same.

Any ideas ?

Here is the exception
2012-08-06 18:00:01,033 ERROR [geonetwork.datamanager] - The metadata
document index with id=339 is corrupt/invalid - ignoring it. Error:
null
java.io.IOException
        at org.geotools.jdbc.JDBCFeatureStore.removeFeatures(JDBCFeatureStore.java:398)
        at org.fao.geonet.kernel.search.spatial.SpatialIndexWriter.delete(SpatialIndexWriter.java:216)
        at org.fao.geonet.kernel.search.SearchManager.deleteGroup(SearchManager.java:741)
        at org.fao.geonet.kernel.search.SearchManager.buildIndexDocument(SearchManager.java:763)
        at org.fao.geonet.kernel.search.SearchManager.indexGroup(SearchManager.java:709)
        at org.fao.geonet.kernel.DataManager.indexMetadata(DataManager.java:583)
        at org.fao.geonet.kernel.DataManager.indexMetadataGroup(DataManager.java:431)
        at org.fao.geonet.kernel.harvest.harvester.geonet.Aligner.updateMetadata(Aligner.java:640)
        at org.fao.geonet.kernel.harvest.harvester.geonet.Aligner.access$400(Aligner.java:61)
        at org.fao.geonet.kernel.harvest.harvester.geonet.Aligner$2.handleInfo(Aligner.java:539)
        at org.fao.geonet.kernel.mef.MEFVisitor.handleXml(MEFVisitor.java:88)
        at org.fao.geonet.kernel.mef.MEFVisitor.visit(MEFVisitor.java:48)
        at org.fao.geonet.kernel.mef.MEFLib.visit(MEFLib.java:185)
        at org.fao.geonet.kernel.harvest.harvester.geonet.Aligner.updateMetadata(Aligner.java:523)
        at org.fao.geonet.kernel.harvest.harvester.geonet.Aligner.align(Aligner.java:172)
        at org.fao.geonet.kernel.harvest.harvester.geonet.Harvester.harvest(Harvester.java:126)
        at org.fao.geonet.kernel.harvest.harvester.geonet.GeonetHarvester.doHarvest(GeonetHarvester.java:264)
        at org.fao.geonet.kernel.harvest.harvester.AbstractHarvester$HarvestWithIndexProcessor.process(AbstractHarvester.java:397)
        at org.fao.geonet.kernel.MetadataIndexerProcessor.processWithFastIndexing(MetadataIndexerProcessor.java:39)
        at org.fao.geonet.kernel.harvest.harvester.AbstractHarvester.harvest(AbstractHarvester.java:427)
        at org.fao.geonet.kernel.harvest.harvester.HarvesterJob.execute(HarvesterJob.java:29)
        at org.quartz.core.JobRunShell.run(JobRunShell.java:213)
        at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:557)
Caused by: org.postgresql.util.PSQLException: An I/O error occured
while sending to the backend.
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:283)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:479)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:367)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:271)
        at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
        at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
        at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
        at org.geotools.jdbc.JDBCDataStore.ensureAuthorization(JDBCDataStore.java:1911)
        at org.geotools.jdbc.JDBCFeatureStore.removeFeatures(JDBCFeatureStore.java:395)
        ... 22 more
Caused by: java.io.EOFException
        at org.postgresql.core.PGStream.ReceiveChar(PGStream.java:261)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1620)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
        ... 30 more

Could we catch the exception and try to reset the datastore connection ?

Thanks.

Francois

------------------------------------------------------------------------------
Live Security Virtual Conference
Exclusive live event will cover all the ways today's security and
threat landscape has changed and how IT managers can respond. Discussions
will include endpoint security, mobile security and the latest in malware
threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/
_______________________________________________
GeoNetwork-devel mailing list
GeoNetwork-devel@lists.sourceforge.net
geonetwork-devel List Signup and Options
GeoNetwork OpenSource is maintained at GeoNetwork - Geographic Metadata Catalog download | SourceForge.net

Hello Simon,

2012/8/7 <Simon.Pigot@anonymised.com>:

Hi Francois,

Same thing happening with Oracle here - in fact I was going to open a bug about it too.

Happens here if the oracle database is restarted and may happen at other times too (was trying to nail these down). It seems to me that the spatialindexwriter (or geotools) code might be/is probably holding onto a connection and thus when the database is restarted/reset it ends up trying to use a closed connection and fails. I was planning to spend a bit more time on this at it is obviously critical/blocker.

It could be nice for the JNDI pool to fallback creating a shapefile if
something goes wrong in setting the DB datastore instead of returning
throw new IllegalArgumentException("unknown database vendor ..
We could also keep using Postgresql driver via JNDI + shapefile in some setup.

What do you think ?

Cheers

Francois

Cheers,
Simon
________________________________________
From: Francois Prunayre [fx.prunayre@anonymised.com]
Sent: Tuesday, 7 August 2012 5:33 PM
To: Devel geonetwork-devel@lists.sourceforge.net
Subject: [GeoNetwork-devel] Indexing / SpatialIndexWriter failed to remove features in PostGIS spatial index

Hi all,

I've a node (using trunk) which fails removing features from the
PostGIS spatial index. It happens at some point in SpatialIndexWriter
removeFeatures on harvesting.
The catalogue DB connection is still working, only the datastore one
looks to be disconnected. Search does not return records because
they're not indexed anymore due to the error.
So I was thinking something is wrong with the datastore DB connection.
I tried both JDBC or JNDI but got the same.

Any ideas ?

Here is the exception
2012-08-06 18:00:01,033 ERROR [geonetwork.datamanager] - The metadata
document index with id=339 is corrupt/invalid - ignoring it. Error:
null
java.io.IOException
        at org.geotools.jdbc.JDBCFeatureStore.removeFeatures(JDBCFeatureStore.java:398)
        at org.fao.geonet.kernel.search.spatial.SpatialIndexWriter.delete(SpatialIndexWriter.java:216)
        at org.fao.geonet.kernel.search.SearchManager.deleteGroup(SearchManager.java:741)
        at org.fao.geonet.kernel.search.SearchManager.buildIndexDocument(SearchManager.java:763)
        at org.fao.geonet.kernel.search.SearchManager.indexGroup(SearchManager.java:709)
        at org.fao.geonet.kernel.DataManager.indexMetadata(DataManager.java:583)
        at org.fao.geonet.kernel.DataManager.indexMetadataGroup(DataManager.java:431)
        at org.fao.geonet.kernel.harvest.harvester.geonet.Aligner.updateMetadata(Aligner.java:640)
        at org.fao.geonet.kernel.harvest.harvester.geonet.Aligner.access$400(Aligner.java:61)
        at org.fao.geonet.kernel.harvest.harvester.geonet.Aligner$2.handleInfo(Aligner.java:539)
        at org.fao.geonet.kernel.mef.MEFVisitor.handleXml(MEFVisitor.java:88)
        at org.fao.geonet.kernel.mef.MEFVisitor.visit(MEFVisitor.java:48)
        at org.fao.geonet.kernel.mef.MEFLib.visit(MEFLib.java:185)
        at org.fao.geonet.kernel.harvest.harvester.geonet.Aligner.updateMetadata(Aligner.java:523)
        at org.fao.geonet.kernel.harvest.harvester.geonet.Aligner.align(Aligner.java:172)
        at org.fao.geonet.kernel.harvest.harvester.geonet.Harvester.harvest(Harvester.java:126)
        at org.fao.geonet.kernel.harvest.harvester.geonet.GeonetHarvester.doHarvest(GeonetHarvester.java:264)
        at org.fao.geonet.kernel.harvest.harvester.AbstractHarvester$HarvestWithIndexProcessor.process(AbstractHarvester.java:397)
        at org.fao.geonet.kernel.MetadataIndexerProcessor.processWithFastIndexing(MetadataIndexerProcessor.java:39)
        at org.fao.geonet.kernel.harvest.harvester.AbstractHarvester.harvest(AbstractHarvester.java:427)
        at org.fao.geonet.kernel.harvest.harvester.HarvesterJob.execute(HarvesterJob.java:29)
        at org.quartz.core.JobRunShell.run(JobRunShell.java:213)
        at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:557)
Caused by: org.postgresql.util.PSQLException: An I/O error occured
while sending to the backend.
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:283)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:479)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:367)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:271)
        at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
        at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
        at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
        at org.geotools.jdbc.JDBCDataStore.ensureAuthorization(JDBCDataStore.java:1911)
        at org.geotools.jdbc.JDBCFeatureStore.removeFeatures(JDBCFeatureStore.java:395)
        ... 22 more
Caused by: java.io.EOFException
        at org.postgresql.core.PGStream.ReceiveChar(PGStream.java:261)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1620)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
        ... 30 more

Could we catch the exception and try to reset the datastore connection ?

Thanks.

Francois

------------------------------------------------------------------------------
Live Security Virtual Conference
Exclusive live event will cover all the ways today's security and
threat landscape has changed and how IT managers can respond. Discussions
will include endpoint security, mobile security and the latest in malware
threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/
_______________________________________________
GeoNetwork-devel mailing list
GeoNetwork-devel@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geonetwork-devel
GeoNetwork OpenSource is maintained at http://sourceforge.net/projects/geonetwork

Hi Francois,

An update. Seems that by:

1- setting the spatialindexwriter to autocommit (set maxWritesInTransaction to 1 in WEB-INF/config.xml) and
2- patching the geotools 2.7.4 gt-jdbc-2.7.4.jar according to http://jira.codehaus.org/browse/GEOS-4789 (patch is in http://jira.codehaus.org/browse/GEOT-4053)

we *may* be able to get round this problem which I can reliably reproduce by restarting oracle (or postgres) whilst geonetwork with spatial database connection is up.

I need to do some more testing to make sure this is a fix and then we need to figure out how to get the patched 2.7.4

Cheers,
Simon
________________________________________
From: Francois Prunayre [fx.prunayre@anonymised.com]
Sent: Tuesday, 7 August 2012 10:48 PM
To: Pigot, Simon (CMAR, Hobart)
Cc: geonetwork-devel@lists.sourceforge.net
Subject: Re: [GeoNetwork-devel] Indexing / SpatialIndexWriter failed to remove features in PostGIS spatial index

Hello Simon,

2012/8/7 <Simon.Pigot@anonymised.com>:

Hi Francois,

Same thing happening with Oracle here - in fact I was going to open a bug about it too.

Happens here if the oracle database is restarted and may happen at other times too (was trying to nail these down). It seems to me that the spatialindexwriter (or geotools) code might be/is probably holding onto a connection and thus when the database is restarted/reset it ends up trying to use a closed connection and fails. I was planning to spend a bit more time on this at it is obviously critical/blocker.

It could be nice for the JNDI pool to fallback creating a shapefile if
something goes wrong in setting the DB datastore instead of returning
throw new IllegalArgumentException("unknown database vendor ..
We could also keep using Postgresql driver via JNDI + shapefile in some setup.

What do you think ?

Cheers

Francois

Cheers,
Simon
________________________________________
From: Francois Prunayre [fx.prunayre@anonymised.com]
Sent: Tuesday, 7 August 2012 5:33 PM
To: Devel geonetwork-devel@lists.sourceforge.net
Subject: [GeoNetwork-devel] Indexing / SpatialIndexWriter failed to remove features in PostGIS spatial index

Hi all,

I've a node (using trunk) which fails removing features from the
PostGIS spatial index. It happens at some point in SpatialIndexWriter
removeFeatures on harvesting.
The catalogue DB connection is still working, only the datastore one
looks to be disconnected. Search does not return records because
they're not indexed anymore due to the error.
So I was thinking something is wrong with the datastore DB connection.
I tried both JDBC or JNDI but got the same.

Any ideas ?

Here is the exception
2012-08-06 18:00:01,033 ERROR [geonetwork.datamanager] - The metadata
document index with id=339 is corrupt/invalid - ignoring it. Error:
null
java.io.IOException
        at org.geotools.jdbc.JDBCFeatureStore.removeFeatures(JDBCFeatureStore.java:398)
        at org.fao.geonet.kernel.search.spatial.SpatialIndexWriter.delete(SpatialIndexWriter.java:216)
        at org.fao.geonet.kernel.search.SearchManager.deleteGroup(SearchManager.java:741)
        at org.fao.geonet.kernel.search.SearchManager.buildIndexDocument(SearchManager.java:763)
        at org.fao.geonet.kernel.search.SearchManager.indexGroup(SearchManager.java:709)
        at org.fao.geonet.kernel.DataManager.indexMetadata(DataManager.java:583)
        at org.fao.geonet.kernel.DataManager.indexMetadataGroup(DataManager.java:431)
        at org.fao.geonet.kernel.harvest.harvester.geonet.Aligner.updateMetadata(Aligner.java:640)
        at org.fao.geonet.kernel.harvest.harvester.geonet.Aligner.access$400(Aligner.java:61)
        at org.fao.geonet.kernel.harvest.harvester.geonet.Aligner$2.handleInfo(Aligner.java:539)
        at org.fao.geonet.kernel.mef.MEFVisitor.handleXml(MEFVisitor.java:88)
        at org.fao.geonet.kernel.mef.MEFVisitor.visit(MEFVisitor.java:48)
        at org.fao.geonet.kernel.mef.MEFLib.visit(MEFLib.java:185)
        at org.fao.geonet.kernel.harvest.harvester.geonet.Aligner.updateMetadata(Aligner.java:523)
        at org.fao.geonet.kernel.harvest.harvester.geonet.Aligner.align(Aligner.java:172)
        at org.fao.geonet.kernel.harvest.harvester.geonet.Harvester.harvest(Harvester.java:126)
        at org.fao.geonet.kernel.harvest.harvester.geonet.GeonetHarvester.doHarvest(GeonetHarvester.java:264)
        at org.fao.geonet.kernel.harvest.harvester.AbstractHarvester$HarvestWithIndexProcessor.process(AbstractHarvester.java:397)
        at org.fao.geonet.kernel.MetadataIndexerProcessor.processWithFastIndexing(MetadataIndexerProcessor.java:39)
        at org.fao.geonet.kernel.harvest.harvester.AbstractHarvester.harvest(AbstractHarvester.java:427)
        at org.fao.geonet.kernel.harvest.harvester.HarvesterJob.execute(HarvesterJob.java:29)
        at org.quartz.core.JobRunShell.run(JobRunShell.java:213)
        at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:557)
Caused by: org.postgresql.util.PSQLException: An I/O error occured
while sending to the backend.
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:283)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:479)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:367)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:271)
        at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
        at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
        at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
        at org.geotools.jdbc.JDBCDataStore.ensureAuthorization(JDBCDataStore.java:1911)
        at org.geotools.jdbc.JDBCFeatureStore.removeFeatures(JDBCFeatureStore.java:395)
        ... 22 more
Caused by: java.io.EOFException
        at org.postgresql.core.PGStream.ReceiveChar(PGStream.java:261)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1620)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
        ... 30 more

Could we catch the exception and try to reset the datastore connection ?

Thanks.

Francois

------------------------------------------------------------------------------
Live Security Virtual Conference
Exclusive live event will cover all the ways today's security and
threat landscape has changed and how IT managers can respond. Discussions
will include endpoint security, mobile security and the latest in malware
threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/
_______________________________________________
GeoNetwork-devel mailing list
GeoNetwork-devel@lists.sourceforge.net
geonetwork-devel List Signup and Options
GeoNetwork OpenSource is maintained at http://sourceforge.net/projects/geonetwork

Hello Simon,

2012/8/9 <Simon.Pigot@anonymised.com>:

Hi Francois,

An update. Seems that by:

1- setting the spatialindexwriter to autocommit (set maxWritesInTransaction to 1 in WEB-INF/config.xml) and
2- patching the geotools 2.7.4 gt-jdbc-2.7.4.jar according to http://jira.codehaus.org/browse/GEOS-4789 (patch is in http://jira.codehaus.org/browse/GEOT-4053)

I'm testing it with geotools 2.7.5 which has the patch but get

WARNING: Error occurred closing connection
2012-09-03 15:51:43,252 WARN [geonetwork.spatial] - Failure to make
_writer, maybe a problem but might also not be an issue:<error
id="error">
  <message>Error occurred getting table name list.</message>
  <class>IOException</class>
  <stack>
    <at class="org.geotools.jdbc.JDBCDataStore"
file="JDBCDataStore.java" line="864" method="createTypeNames" />
    <at class="org.geotools.data.store.ContentDataStore"
file="ContentDataStore.java" line="299" method="getTypeNames" />
    <at class="org.fao.geonet.kernel.search.spatial.SpatialIndexWriter"
file="SpatialIndexWriter.java" line="407"
method="findSpatialIndexStore" />
    <at class="org.fao.geonet.kernel.search.spatial.SpatialIndexWriter"
file="SpatialIndexWriter.java" line="393" method="createFeatureStore"
/>
    <at class="org.fao.geonet.kernel.search.spatial.SpatialIndexWriter"
file="SpatialIndexWriter.java" line="126" method="&lt;init&gt;" />
    <at class="org.fao.geonet.kernel.search.SearchManager$Spatial"
file="SearchManager.java" line="1569" method="createWriter" />
    <at class="org.fao.geonet.kernel.search.SearchManager$Spatial"
file="SearchManager.java" line="1547" method="&lt;init&gt;" />
    <at class="org.fao.geonet.kernel.search.SearchManager"
file="SearchManager.java" line="510" method="&lt;init&gt;" />
    <at class="org.fao.geonet.Geonetwork" file="Geonetwork.java"
line="317" method="start" />
    <at class="jeeves.server.JeevesEngine" file="JeevesEngine.java"
line="541" method="initAppHandler" />
    <at class="jeeves.server.JeevesEngine" file="JeevesEngine.java"
line="170" method="init" />
    <at class="jeeves.server.sources.http.JeevesServlet"
file="JeevesServlet.java" line="85" method="init" />
    <skip>...</skip>
  </stack>
</error>
when maxWritesInTransaction is 1.

Any idea ?

Cheers.

Francois

we *may* be able to get round this problem which I can reliably reproduce by restarting oracle (or postgres) whilst geonetwork with spatial database connection is up.

I need to do some more testing to make sure this is a fix and then we need to figure out how to get the patched 2.7.4

Cheers,
Simon
________________________________________
From: Francois Prunayre [fx.prunayre@anonymised.com]
Sent: Tuesday, 7 August 2012 10:48 PM
To: Pigot, Simon (CMAR, Hobart)
Cc: geonetwork-devel@lists.sourceforge.net
Subject: Re: [GeoNetwork-devel] Indexing / SpatialIndexWriter failed to remove features in PostGIS spatial index

Hello Simon,

2012/8/7 <Simon.Pigot@anonymised.com>:

Hi Francois,

Same thing happening with Oracle here - in fact I was going to open a bug about it too.

Happens here if the oracle database is restarted and may happen at other times too (was trying to nail these down). It seems to me that the spatialindexwriter (or geotools) code might be/is probably holding onto a connection and thus when the database is restarted/reset it ends up trying to use a closed connection and fails. I was planning to spend a bit more time on this at it is obviously critical/blocker.

It could be nice for the JNDI pool to fallback creating a shapefile if
something goes wrong in setting the DB datastore instead of returning
throw new IllegalArgumentException("unknown database vendor ..
We could also keep using Postgresql driver via JNDI + shapefile in some setup.

What do you think ?

Cheers

Francois

Cheers,
Simon
________________________________________
From: Francois Prunayre [fx.prunayre@anonymised.com]
Sent: Tuesday, 7 August 2012 5:33 PM
To: Devel geonetwork-devel@lists.sourceforge.net
Subject: [GeoNetwork-devel] Indexing / SpatialIndexWriter failed to remove features in PostGIS spatial index

Hi all,

I've a node (using trunk) which fails removing features from the
PostGIS spatial index. It happens at some point in SpatialIndexWriter
removeFeatures on harvesting.
The catalogue DB connection is still working, only the datastore one
looks to be disconnected. Search does not return records because
they're not indexed anymore due to the error.
So I was thinking something is wrong with the datastore DB connection.
I tried both JDBC or JNDI but got the same.

Any ideas ?

Here is the exception
2012-08-06 18:00:01,033 ERROR [geonetwork.datamanager] - The metadata
document index with id=339 is corrupt/invalid - ignoring it. Error:
null
java.io.IOException
        at org.geotools.jdbc.JDBCFeatureStore.removeFeatures(JDBCFeatureStore.java:398)
        at org.fao.geonet.kernel.search.spatial.SpatialIndexWriter.delete(SpatialIndexWriter.java:216)
        at org.fao.geonet.kernel.search.SearchManager.deleteGroup(SearchManager.java:741)
        at org.fao.geonet.kernel.search.SearchManager.buildIndexDocument(SearchManager.java:763)
        at org.fao.geonet.kernel.search.SearchManager.indexGroup(SearchManager.java:709)
        at org.fao.geonet.kernel.DataManager.indexMetadata(DataManager.java:583)
        at org.fao.geonet.kernel.DataManager.indexMetadataGroup(DataManager.java:431)
        at org.fao.geonet.kernel.harvest.harvester.geonet.Aligner.updateMetadata(Aligner.java:640)
        at org.fao.geonet.kernel.harvest.harvester.geonet.Aligner.access$400(Aligner.java:61)
        at org.fao.geonet.kernel.harvest.harvester.geonet.Aligner$2.handleInfo(Aligner.java:539)
        at org.fao.geonet.kernel.mef.MEFVisitor.handleXml(MEFVisitor.java:88)
        at org.fao.geonet.kernel.mef.MEFVisitor.visit(MEFVisitor.java:48)
        at org.fao.geonet.kernel.mef.MEFLib.visit(MEFLib.java:185)
        at org.fao.geonet.kernel.harvest.harvester.geonet.Aligner.updateMetadata(Aligner.java:523)
        at org.fao.geonet.kernel.harvest.harvester.geonet.Aligner.align(Aligner.java:172)
        at org.fao.geonet.kernel.harvest.harvester.geonet.Harvester.harvest(Harvester.java:126)
        at org.fao.geonet.kernel.harvest.harvester.geonet.GeonetHarvester.doHarvest(GeonetHarvester.java:264)
        at org.fao.geonet.kernel.harvest.harvester.AbstractHarvester$HarvestWithIndexProcessor.process(AbstractHarvester.java:397)
        at org.fao.geonet.kernel.MetadataIndexerProcessor.processWithFastIndexing(MetadataIndexerProcessor.java:39)
        at org.fao.geonet.kernel.harvest.harvester.AbstractHarvester.harvest(AbstractHarvester.java:427)
        at org.fao.geonet.kernel.harvest.harvester.HarvesterJob.execute(HarvesterJob.java:29)
        at org.quartz.core.JobRunShell.run(JobRunShell.java:213)
        at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:557)
Caused by: org.postgresql.util.PSQLException: An I/O error occured
while sending to the backend.
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:283)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:479)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:367)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:271)
        at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
        at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
        at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
        at org.geotools.jdbc.JDBCDataStore.ensureAuthorization(JDBCDataStore.java:1911)
        at org.geotools.jdbc.JDBCFeatureStore.removeFeatures(JDBCFeatureStore.java:395)
        ... 22 more
Caused by: java.io.EOFException
        at org.postgresql.core.PGStream.ReceiveChar(PGStream.java:261)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1620)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
        ... 30 more

Could we catch the exception and try to reset the datastore connection ?

Thanks.

Francois

------------------------------------------------------------------------------
Live Security Virtual Conference
Exclusive live event will cover all the ways today's security and
threat landscape has changed and how IT managers can respond. Discussions
will include endpoint security, mobile security and the latest in malware
threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/
_______________________________________________
GeoNetwork-devel mailing list
GeoNetwork-devel@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geonetwork-devel
GeoNetwork OpenSource is maintained at http://sourceforge.net/projects/geonetwork

Just recently I noticed that we will need to create the gt_pk_metadata table in the postgis creation sql script something like the following to get rid of this error:

gndb4=> \d gt_pk_metadata
           Table "public.gt_pk_metadata"
    Column | Type | Modifiers
---------------+------------------------+-----------
table_name | character varying(250) | not null
pk_column_idx | character varying(250) |
Indexes:
    "gt_pk_metadata_pkey" PRIMARY KEY, btree (table_name)

gndb4=> select * from gt_pk_metadata;
  table_name | pk_column_idx
--------------+------------------
spatialIndex | spatialIndexNDX1
(1 row)

Cheers,
Simon
________________________________________
From: Francois Prunayre [fx.prunayre@anonymised.com]
Sent: Tuesday, 4 September 2012 12:18 AM
To: Pigot, Simon (CMAR, Hobart)
Cc: geonetwork-devel@lists.sourceforge.net
Subject: Re: [GeoNetwork-devel] Indexing / SpatialIndexWriter failed to remove features in PostGIS spatial index

Hello Simon,

2012/8/9 <Simon.Pigot@anonymised.com>:

Hi Francois,

An update. Seems that by:

1- setting the spatialindexwriter to autocommit (set maxWritesInTransaction to 1 in WEB-INF/config.xml) and
2- patching the geotools 2.7.4 gt-jdbc-2.7.4.jar according to http://jira.codehaus.org/browse/GEOS-4789 (patch is in http://jira.codehaus.org/browse/GEOT-4053)

I'm testing it with geotools 2.7.5 which has the patch but get

WARNING: Error occurred closing connection
2012-09-03 15:51:43,252 WARN [geonetwork.spatial] - Failure to make
_writer, maybe a problem but might also not be an issue:<error
id="error">
  <message>Error occurred getting table name list.</message>
  <class>IOException</class>
  <stack>
    <at class="org.geotools.jdbc.JDBCDataStore"
file="JDBCDataStore.java" line="864" method="createTypeNames" />
    <at class="org.geotools.data.store.ContentDataStore"
file="ContentDataStore.java" line="299" method="getTypeNames" />
    <at class="org.fao.geonet.kernel.search.spatial.SpatialIndexWriter"
file="SpatialIndexWriter.java" line="407"
method="findSpatialIndexStore" />
    <at class="org.fao.geonet.kernel.search.spatial.SpatialIndexWriter"
file="SpatialIndexWriter.java" line="393" method="createFeatureStore"
/>
    <at class="org.fao.geonet.kernel.search.spatial.SpatialIndexWriter"
file="SpatialIndexWriter.java" line="126" method="&lt;init&gt;" />
    <at class="org.fao.geonet.kernel.search.SearchManager$Spatial"
file="SearchManager.java" line="1569" method="createWriter" />
    <at class="org.fao.geonet.kernel.search.SearchManager$Spatial"
file="SearchManager.java" line="1547" method="&lt;init&gt;" />
    <at class="org.fao.geonet.kernel.search.SearchManager"
file="SearchManager.java" line="510" method="&lt;init&gt;" />
    <at class="org.fao.geonet.Geonetwork" file="Geonetwork.java"
line="317" method="start" />
    <at class="jeeves.server.JeevesEngine" file="JeevesEngine.java"
line="541" method="initAppHandler" />
    <at class="jeeves.server.JeevesEngine" file="JeevesEngine.java"
line="170" method="init" />
    <at class="jeeves.server.sources.http.JeevesServlet"
file="JeevesServlet.java" line="85" method="init" />
    <skip>...</skip>
  </stack>
</error>
when maxWritesInTransaction is 1.

Any idea ?

Cheers.

Francois

we *may* be able to get round this problem which I can reliably reproduce by restarting oracle (or postgres) whilst geonetwork with spatial database connection is up.

I need to do some more testing to make sure this is a fix and then we need to figure out how to get the patched 2.7.4

Cheers,
Simon
________________________________________
From: Francois Prunayre [fx.prunayre@anonymised.com]
Sent: Tuesday, 7 August 2012 10:48 PM
To: Pigot, Simon (CMAR, Hobart)
Cc: geonetwork-devel@lists.sourceforge.net
Subject: Re: [GeoNetwork-devel] Indexing / SpatialIndexWriter failed to remove features in PostGIS spatial index

Hello Simon,

2012/8/7 <Simon.Pigot@anonymised.com>:

Hi Francois,

Same thing happening with Oracle here - in fact I was going to open a bug about it too.

Happens here if the oracle database is restarted and may happen at other times too (was trying to nail these down). It seems to me that the spatialindexwriter (or geotools) code might be/is probably holding onto a connection and thus when the database is restarted/reset it ends up trying to use a closed connection and fails. I was planning to spend a bit more time on this at it is obviously critical/blocker.

It could be nice for the JNDI pool to fallback creating a shapefile if
something goes wrong in setting the DB datastore instead of returning
throw new IllegalArgumentException("unknown database vendor ..
We could also keep using Postgresql driver via JNDI + shapefile in some setup.

What do you think ?

Cheers

Francois

Cheers,
Simon
________________________________________
From: Francois Prunayre [fx.prunayre@anonymised.com]
Sent: Tuesday, 7 August 2012 5:33 PM
To: Devel geonetwork-devel@lists.sourceforge.net
Subject: [GeoNetwork-devel] Indexing / SpatialIndexWriter failed to remove features in PostGIS spatial index

Hi all,

I've a node (using trunk) which fails removing features from the
PostGIS spatial index. It happens at some point in SpatialIndexWriter
removeFeatures on harvesting.
The catalogue DB connection is still working, only the datastore one
looks to be disconnected. Search does not return records because
they're not indexed anymore due to the error.
So I was thinking something is wrong with the datastore DB connection.
I tried both JDBC or JNDI but got the same.

Any ideas ?

Here is the exception
2012-08-06 18:00:01,033 ERROR [geonetwork.datamanager] - The metadata
document index with id=339 is corrupt/invalid - ignoring it. Error:
null
java.io.IOException
        at org.geotools.jdbc.JDBCFeatureStore.removeFeatures(JDBCFeatureStore.java:398)
        at org.fao.geonet.kernel.search.spatial.SpatialIndexWriter.delete(SpatialIndexWriter.java:216)
        at org.fao.geonet.kernel.search.SearchManager.deleteGroup(SearchManager.java:741)
        at org.fao.geonet.kernel.search.SearchManager.buildIndexDocument(SearchManager.java:763)
        at org.fao.geonet.kernel.search.SearchManager.indexGroup(SearchManager.java:709)
        at org.fao.geonet.kernel.DataManager.indexMetadata(DataManager.java:583)
        at org.fao.geonet.kernel.DataManager.indexMetadataGroup(DataManager.java:431)
        at org.fao.geonet.kernel.harvest.harvester.geonet.Aligner.updateMetadata(Aligner.java:640)
        at org.fao.geonet.kernel.harvest.harvester.geonet.Aligner.access$400(Aligner.java:61)
        at org.fao.geonet.kernel.harvest.harvester.geonet.Aligner$2.handleInfo(Aligner.java:539)
        at org.fao.geonet.kernel.mef.MEFVisitor.handleXml(MEFVisitor.java:88)
        at org.fao.geonet.kernel.mef.MEFVisitor.visit(MEFVisitor.java:48)
        at org.fao.geonet.kernel.mef.MEFLib.visit(MEFLib.java:185)
        at org.fao.geonet.kernel.harvest.harvester.geonet.Aligner.updateMetadata(Aligner.java:523)
        at org.fao.geonet.kernel.harvest.harvester.geonet.Aligner.align(Aligner.java:172)
        at org.fao.geonet.kernel.harvest.harvester.geonet.Harvester.harvest(Harvester.java:126)
        at org.fao.geonet.kernel.harvest.harvester.geonet.GeonetHarvester.doHarvest(GeonetHarvester.java:264)
        at org.fao.geonet.kernel.harvest.harvester.AbstractHarvester$HarvestWithIndexProcessor.process(AbstractHarvester.java:397)
        at org.fao.geonet.kernel.MetadataIndexerProcessor.processWithFastIndexing(MetadataIndexerProcessor.java:39)
        at org.fao.geonet.kernel.harvest.harvester.AbstractHarvester.harvest(AbstractHarvester.java:427)
        at org.fao.geonet.kernel.harvest.harvester.HarvesterJob.execute(HarvesterJob.java:29)
        at org.quartz.core.JobRunShell.run(JobRunShell.java:213)
        at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:557)
Caused by: org.postgresql.util.PSQLException: An I/O error occured
while sending to the backend.
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:283)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:479)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:367)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:271)
        at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
        at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
        at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
        at org.geotools.jdbc.JDBCDataStore.ensureAuthorization(JDBCDataStore.java:1911)
        at org.geotools.jdbc.JDBCFeatureStore.removeFeatures(JDBCFeatureStore.java:395)
        ... 22 more
Caused by: java.io.EOFException
        at org.postgresql.core.PGStream.ReceiveChar(PGStream.java:261)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1620)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
        ... 30 more

Could we catch the exception and try to reset the datastore connection ?

Thanks.

Francois

------------------------------------------------------------------------------
Live Security Virtual Conference
Exclusive live event will cover all the ways today's security and
threat landscape has changed and how IT managers can respond. Discussions
will include endpoint security, mobile security and the latest in malware
threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/
_______________________________________________
GeoNetwork-devel mailing list
GeoNetwork-devel@lists.sourceforge.net
geonetwork-devel List Signup and Options
GeoNetwork OpenSource is maintained at http://sourceforge.net/projects/geonetwork

2012/9/3 <Simon.Pigot@anonymised.com>:

Just recently I noticed that we will need to create the gt_pk_metadata table in the postgis creation sql script something like the following to get rid of this error:

I created it too already but still get the error.

Thanks.

Francois

gndb4=> \d gt_pk_metadata
           Table "public.gt_pk_metadata"
    Column | Type | Modifiers
---------------+------------------------+-----------
table_name | character varying(250) | not null
pk_column_idx | character varying(250) |
Indexes:
    "gt_pk_metadata_pkey" PRIMARY KEY, btree (table_name)

gndb4=> select * from gt_pk_metadata;
  table_name | pk_column_idx
--------------+------------------
spatialIndex | spatialIndexNDX1
(1 row)

Cheers,
Simon
________________________________________
From: Francois Prunayre [fx.prunayre@anonymised.com]
Sent: Tuesday, 4 September 2012 12:18 AM
To: Pigot, Simon (CMAR, Hobart)
Cc: geonetwork-devel@lists.sourceforge.net
Subject: Re: [GeoNetwork-devel] Indexing / SpatialIndexWriter failed to remove features in PostGIS spatial index

Hello Simon,

2012/8/9 <Simon.Pigot@anonymised.com>:

Hi Francois,

An update. Seems that by:

1- setting the spatialindexwriter to autocommit (set maxWritesInTransaction to 1 in WEB-INF/config.xml) and
2- patching the geotools 2.7.4 gt-jdbc-2.7.4.jar according to http://jira.codehaus.org/browse/GEOS-4789 (patch is in http://jira.codehaus.org/browse/GEOT-4053)

I'm testing it with geotools 2.7.5 which has the patch but get

WARNING: Error occurred closing connection
2012-09-03 15:51:43,252 WARN [geonetwork.spatial] - Failure to make
_writer, maybe a problem but might also not be an issue:<error
id="error">
  <message>Error occurred getting table name list.</message>
  <class>IOException</class>
  <stack>
    <at class="org.geotools.jdbc.JDBCDataStore"
file="JDBCDataStore.java" line="864" method="createTypeNames" />
    <at class="org.geotools.data.store.ContentDataStore"
file="ContentDataStore.java" line="299" method="getTypeNames" />
    <at class="org.fao.geonet.kernel.search.spatial.SpatialIndexWriter"
file="SpatialIndexWriter.java" line="407"
method="findSpatialIndexStore" />
    <at class="org.fao.geonet.kernel.search.spatial.SpatialIndexWriter"
file="SpatialIndexWriter.java" line="393" method="createFeatureStore"
/>
    <at class="org.fao.geonet.kernel.search.spatial.SpatialIndexWriter"
file="SpatialIndexWriter.java" line="126" method="&lt;init&gt;" />
    <at class="org.fao.geonet.kernel.search.SearchManager$Spatial"
file="SearchManager.java" line="1569" method="createWriter" />
    <at class="org.fao.geonet.kernel.search.SearchManager$Spatial"
file="SearchManager.java" line="1547" method="&lt;init&gt;" />
    <at class="org.fao.geonet.kernel.search.SearchManager"
file="SearchManager.java" line="510" method="&lt;init&gt;" />
    <at class="org.fao.geonet.Geonetwork" file="Geonetwork.java"
line="317" method="start" />
    <at class="jeeves.server.JeevesEngine" file="JeevesEngine.java"
line="541" method="initAppHandler" />
    <at class="jeeves.server.JeevesEngine" file="JeevesEngine.java"
line="170" method="init" />
    <at class="jeeves.server.sources.http.JeevesServlet"
file="JeevesServlet.java" line="85" method="init" />
    <skip>...</skip>
  </stack>
</error>
when maxWritesInTransaction is 1.

Any idea ?

Cheers.

Francois

we *may* be able to get round this problem which I can reliably reproduce by restarting oracle (or postgres) whilst geonetwork with spatial database connection is up.

I need to do some more testing to make sure this is a fix and then we need to figure out how to get the patched 2.7.4

Cheers,
Simon
________________________________________
From: Francois Prunayre [fx.prunayre@anonymised.com]
Sent: Tuesday, 7 August 2012 10:48 PM
To: Pigot, Simon (CMAR, Hobart)
Cc: geonetwork-devel@lists.sourceforge.net
Subject: Re: [GeoNetwork-devel] Indexing / SpatialIndexWriter failed to remove features in PostGIS spatial index

Hello Simon,

2012/8/7 <Simon.Pigot@anonymised.com>:

Hi Francois,

Same thing happening with Oracle here - in fact I was going to open a bug about it too.

Happens here if the oracle database is restarted and may happen at other times too (was trying to nail these down). It seems to me that the spatialindexwriter (or geotools) code might be/is probably holding onto a connection and thus when the database is restarted/reset it ends up trying to use a closed connection and fails. I was planning to spend a bit more time on this at it is obviously critical/blocker.

It could be nice for the JNDI pool to fallback creating a shapefile if
something goes wrong in setting the DB datastore instead of returning
throw new IllegalArgumentException("unknown database vendor ..
We could also keep using Postgresql driver via JNDI + shapefile in some setup.

What do you think ?

Cheers

Francois

Cheers,
Simon
________________________________________
From: Francois Prunayre [fx.prunayre@anonymised.com]
Sent: Tuesday, 7 August 2012 5:33 PM
To: Devel geonetwork-devel@lists.sourceforge.net
Subject: [GeoNetwork-devel] Indexing / SpatialIndexWriter failed to remove features in PostGIS spatial index

Hi all,

I've a node (using trunk) which fails removing features from the
PostGIS spatial index. It happens at some point in SpatialIndexWriter
removeFeatures on harvesting.
The catalogue DB connection is still working, only the datastore one
looks to be disconnected. Search does not return records because
they're not indexed anymore due to the error.
So I was thinking something is wrong with the datastore DB connection.
I tried both JDBC or JNDI but got the same.

Any ideas ?

Here is the exception
2012-08-06 18:00:01,033 ERROR [geonetwork.datamanager] - The metadata
document index with id=339 is corrupt/invalid - ignoring it. Error:
null
java.io.IOException
        at org.geotools.jdbc.JDBCFeatureStore.removeFeatures(JDBCFeatureStore.java:398)
        at org.fao.geonet.kernel.search.spatial.SpatialIndexWriter.delete(SpatialIndexWriter.java:216)
        at org.fao.geonet.kernel.search.SearchManager.deleteGroup(SearchManager.java:741)
        at org.fao.geonet.kernel.search.SearchManager.buildIndexDocument(SearchManager.java:763)
        at org.fao.geonet.kernel.search.SearchManager.indexGroup(SearchManager.java:709)
        at org.fao.geonet.kernel.DataManager.indexMetadata(DataManager.java:583)
        at org.fao.geonet.kernel.DataManager.indexMetadataGroup(DataManager.java:431)
        at org.fao.geonet.kernel.harvest.harvester.geonet.Aligner.updateMetadata(Aligner.java:640)
        at org.fao.geonet.kernel.harvest.harvester.geonet.Aligner.access$400(Aligner.java:61)
        at org.fao.geonet.kernel.harvest.harvester.geonet.Aligner$2.handleInfo(Aligner.java:539)
        at org.fao.geonet.kernel.mef.MEFVisitor.handleXml(MEFVisitor.java:88)
        at org.fao.geonet.kernel.mef.MEFVisitor.visit(MEFVisitor.java:48)
        at org.fao.geonet.kernel.mef.MEFLib.visit(MEFLib.java:185)
        at org.fao.geonet.kernel.harvest.harvester.geonet.Aligner.updateMetadata(Aligner.java:523)
        at org.fao.geonet.kernel.harvest.harvester.geonet.Aligner.align(Aligner.java:172)
        at org.fao.geonet.kernel.harvest.harvester.geonet.Harvester.harvest(Harvester.java:126)
        at org.fao.geonet.kernel.harvest.harvester.geonet.GeonetHarvester.doHarvest(GeonetHarvester.java:264)
        at org.fao.geonet.kernel.harvest.harvester.AbstractHarvester$HarvestWithIndexProcessor.process(AbstractHarvester.java:397)
        at org.fao.geonet.kernel.MetadataIndexerProcessor.processWithFastIndexing(MetadataIndexerProcessor.java:39)
        at org.fao.geonet.kernel.harvest.harvester.AbstractHarvester.harvest(AbstractHarvester.java:427)
        at org.fao.geonet.kernel.harvest.harvester.HarvesterJob.execute(HarvesterJob.java:29)
        at org.quartz.core.JobRunShell.run(JobRunShell.java:213)
        at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:557)
Caused by: org.postgresql.util.PSQLException: An I/O error occured
while sending to the backend.
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:283)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:479)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:367)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:271)
        at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
        at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
        at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
        at org.geotools.jdbc.JDBCDataStore.ensureAuthorization(JDBCDataStore.java:1911)
        at org.geotools.jdbc.JDBCFeatureStore.removeFeatures(JDBCFeatureStore.java:395)
        ... 22 more
Caused by: java.io.EOFException
        at org.postgresql.core.PGStream.ReceiveChar(PGStream.java:261)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1620)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
        ... 30 more

Could we catch the exception and try to reset the datastore connection ?

Thanks.

Francois

------------------------------------------------------------------------------
Live Security Virtual Conference
Exclusive live event will cover all the ways today's security and
threat landscape has changed and how IT managers can respond. Discussions
will include endpoint security, mobile security and the latest in malware
threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/
_______________________________________________
GeoNetwork-devel mailing list
GeoNetwork-devel@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geonetwork-devel
GeoNetwork OpenSource is maintained at http://sourceforge.net/projects/geonetwork

? No problems here with GN on ubuntu 12.04, 64bit, postgres 9.1.5/postgis 1.x, gt 2.7.4, tomcat6, jndi provider, maxWrites = 1

In any case we should change the subject as I dont think it is appropriate to this error?

________________________________________
From: Francois Prunayre [fx.prunayre@anonymised.com]
Sent: Tuesday, 4 September 2012 12:29 AM
To: Pigot, Simon (CMAR, Hobart)
Cc: geonetwork-devel@lists.sourceforge.net
Subject: Re: [GeoNetwork-devel] Indexing / SpatialIndexWriter failed to remove features in PostGIS spatial index

2012/9/3 <Simon.Pigot@anonymised.com>:

Just recently I noticed that we will need to create the gt_pk_metadata table in the postgis creation sql script something like the following to get rid of this error:

I created it too already but still get the error.

Thanks.

Francois

gndb4=> \d gt_pk_metadata
           Table "public.gt_pk_metadata"
    Column | Type | Modifiers
---------------+------------------------+-----------
table_name | character varying(250) | not null
pk_column_idx | character varying(250) |
Indexes:
    "gt_pk_metadata_pkey" PRIMARY KEY, btree (table_name)

gndb4=> select * from gt_pk_metadata;
  table_name | pk_column_idx
--------------+------------------
spatialIndex | spatialIndexNDX1
(1 row)

Cheers,
Simon
________________________________________
From: Francois Prunayre [fx.prunayre@anonymised.com]
Sent: Tuesday, 4 September 2012 12:18 AM
To: Pigot, Simon (CMAR, Hobart)
Cc: geonetwork-devel@lists.sourceforge.net
Subject: Re: [GeoNetwork-devel] Indexing / SpatialIndexWriter failed to remove features in PostGIS spatial index

Hello Simon,

2012/8/9 <Simon.Pigot@anonymised.com>:

Hi Francois,

An update. Seems that by:

1- setting the spatialindexwriter to autocommit (set maxWritesInTransaction to 1 in WEB-INF/config.xml) and
2- patching the geotools 2.7.4 gt-jdbc-2.7.4.jar according to http://jira.codehaus.org/browse/GEOS-4789 (patch is in http://jira.codehaus.org/browse/GEOT-4053)

I'm testing it with geotools 2.7.5 which has the patch but get

WARNING: Error occurred closing connection
2012-09-03 15:51:43,252 WARN [geonetwork.spatial] - Failure to make
_writer, maybe a problem but might also not be an issue:<error
id="error">
  <message>Error occurred getting table name list.</message>
  <class>IOException</class>
  <stack>
    <at class="org.geotools.jdbc.JDBCDataStore"
file="JDBCDataStore.java" line="864" method="createTypeNames" />
    <at class="org.geotools.data.store.ContentDataStore"
file="ContentDataStore.java" line="299" method="getTypeNames" />
    <at class="org.fao.geonet.kernel.search.spatial.SpatialIndexWriter"
file="SpatialIndexWriter.java" line="407"
method="findSpatialIndexStore" />
    <at class="org.fao.geonet.kernel.search.spatial.SpatialIndexWriter"
file="SpatialIndexWriter.java" line="393" method="createFeatureStore"
/>
    <at class="org.fao.geonet.kernel.search.spatial.SpatialIndexWriter"
file="SpatialIndexWriter.java" line="126" method="&lt;init&gt;" />
    <at class="org.fao.geonet.kernel.search.SearchManager$Spatial"
file="SearchManager.java" line="1569" method="createWriter" />
    <at class="org.fao.geonet.kernel.search.SearchManager$Spatial"
file="SearchManager.java" line="1547" method="&lt;init&gt;" />
    <at class="org.fao.geonet.kernel.search.SearchManager"
file="SearchManager.java" line="510" method="&lt;init&gt;" />
    <at class="org.fao.geonet.Geonetwork" file="Geonetwork.java"
line="317" method="start" />
    <at class="jeeves.server.JeevesEngine" file="JeevesEngine.java"
line="541" method="initAppHandler" />
    <at class="jeeves.server.JeevesEngine" file="JeevesEngine.java"
line="170" method="init" />
    <at class="jeeves.server.sources.http.JeevesServlet"
file="JeevesServlet.java" line="85" method="init" />
    <skip>...</skip>
  </stack>
</error>
when maxWritesInTransaction is 1.

Any idea ?

Cheers.

Francois

we *may* be able to get round this problem which I can reliably reproduce by restarting oracle (or postgres) whilst geonetwork with spatial database connection is up.

I need to do some more testing to make sure this is a fix and then we need to figure out how to get the patched 2.7.4

Cheers,
Simon
________________________________________
From: Francois Prunayre [fx.prunayre@anonymised.com]
Sent: Tuesday, 7 August 2012 10:48 PM
To: Pigot, Simon (CMAR, Hobart)
Cc: geonetwork-devel@lists.sourceforge.net
Subject: Re: [GeoNetwork-devel] Indexing / SpatialIndexWriter failed to remove features in PostGIS spatial index

Hello Simon,

2012/8/7 <Simon.Pigot@anonymised.com>:

Hi Francois,

Same thing happening with Oracle here - in fact I was going to open a bug about it too.

Happens here if the oracle database is restarted and may happen at other times too (was trying to nail these down). It seems to me that the spatialindexwriter (or geotools) code might be/is probably holding onto a connection and thus when the database is restarted/reset it ends up trying to use a closed connection and fails. I was planning to spend a bit more time on this at it is obviously critical/blocker.

It could be nice for the JNDI pool to fallback creating a shapefile if
something goes wrong in setting the DB datastore instead of returning
throw new IllegalArgumentException("unknown database vendor ..
We could also keep using Postgresql driver via JNDI + shapefile in some setup.

What do you think ?

Cheers

Francois

Cheers,
Simon
________________________________________
From: Francois Prunayre [fx.prunayre@anonymised.com]
Sent: Tuesday, 7 August 2012 5:33 PM
To: Devel geonetwork-devel@lists.sourceforge.net
Subject: [GeoNetwork-devel] Indexing / SpatialIndexWriter failed to remove features in PostGIS spatial index

Hi all,

I've a node (using trunk) which fails removing features from the
PostGIS spatial index. It happens at some point in SpatialIndexWriter
removeFeatures on harvesting.
The catalogue DB connection is still working, only the datastore one
looks to be disconnected. Search does not return records because
they're not indexed anymore due to the error.
So I was thinking something is wrong with the datastore DB connection.
I tried both JDBC or JNDI but got the same.

Any ideas ?

Here is the exception
2012-08-06 18:00:01,033 ERROR [geonetwork.datamanager] - The metadata
document index with id=339 is corrupt/invalid - ignoring it. Error:
null
java.io.IOException
        at org.geotools.jdbc.JDBCFeatureStore.removeFeatures(JDBCFeatureStore.java:398)
        at org.fao.geonet.kernel.search.spatial.SpatialIndexWriter.delete(SpatialIndexWriter.java:216)
        at org.fao.geonet.kernel.search.SearchManager.deleteGroup(SearchManager.java:741)
        at org.fao.geonet.kernel.search.SearchManager.buildIndexDocument(SearchManager.java:763)
        at org.fao.geonet.kernel.search.SearchManager.indexGroup(SearchManager.java:709)
        at org.fao.geonet.kernel.DataManager.indexMetadata(DataManager.java:583)
        at org.fao.geonet.kernel.DataManager.indexMetadataGroup(DataManager.java:431)
        at org.fao.geonet.kernel.harvest.harvester.geonet.Aligner.updateMetadata(Aligner.java:640)
        at org.fao.geonet.kernel.harvest.harvester.geonet.Aligner.access$400(Aligner.java:61)
        at org.fao.geonet.kernel.harvest.harvester.geonet.Aligner$2.handleInfo(Aligner.java:539)
        at org.fao.geonet.kernel.mef.MEFVisitor.handleXml(MEFVisitor.java:88)
        at org.fao.geonet.kernel.mef.MEFVisitor.visit(MEFVisitor.java:48)
        at org.fao.geonet.kernel.mef.MEFLib.visit(MEFLib.java:185)
        at org.fao.geonet.kernel.harvest.harvester.geonet.Aligner.updateMetadata(Aligner.java:523)
        at org.fao.geonet.kernel.harvest.harvester.geonet.Aligner.align(Aligner.java:172)
        at org.fao.geonet.kernel.harvest.harvester.geonet.Harvester.harvest(Harvester.java:126)
        at org.fao.geonet.kernel.harvest.harvester.geonet.GeonetHarvester.doHarvest(GeonetHarvester.java:264)
        at org.fao.geonet.kernel.harvest.harvester.AbstractHarvester$HarvestWithIndexProcessor.process(AbstractHarvester.java:397)
        at org.fao.geonet.kernel.MetadataIndexerProcessor.processWithFastIndexing(MetadataIndexerProcessor.java:39)
        at org.fao.geonet.kernel.harvest.harvester.AbstractHarvester.harvest(AbstractHarvester.java:427)
        at org.fao.geonet.kernel.harvest.harvester.HarvesterJob.execute(HarvesterJob.java:29)
        at org.quartz.core.JobRunShell.run(JobRunShell.java:213)
        at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:557)
Caused by: org.postgresql.util.PSQLException: An I/O error occured
while sending to the backend.
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:283)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:479)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:367)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:271)
        at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
        at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
        at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
        at org.geotools.jdbc.JDBCDataStore.ensureAuthorization(JDBCDataStore.java:1911)
        at org.geotools.jdbc.JDBCFeatureStore.removeFeatures(JDBCFeatureStore.java:395)
        ... 22 more
Caused by: java.io.EOFException
        at org.postgresql.core.PGStream.ReceiveChar(PGStream.java:261)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1620)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
        ... 30 more

Could we catch the exception and try to reset the datastore connection ?

Thanks.

Francois

------------------------------------------------------------------------------
Live Security Virtual Conference
Exclusive live event will cover all the ways today's security and
threat landscape has changed and how IT managers can respond. Discussions
will include endpoint security, mobile security and the latest in malware
threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/
_______________________________________________
GeoNetwork-devel mailing list
GeoNetwork-devel@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geonetwork-devel
GeoNetwork OpenSource is maintained at http://sourceforge.net/projects/geonetwork