Hi,
I have recently started to encounter an error in GeoServer, it can no longer connect to the EPSG database. I have been running it fine for the last 2 months without any problems. The only thing out of the ordinary i have done today is to build a version of hibernate, and now i get the following exceptions on startup:
16:16:35,716 INFO [STDOUT] 16:16:35,716 INFO [geotools] Factory implementations for category CRSAuthorityFactory:
org.geotools.referencing.factory.epsg.FactoryOnHSQL
org.geotools.referencing.factory.epsg.DefaultFactory
org.geotools.referencing.factory.epsg.FactoryUsingWKT
org.geotools.referencing.factory.epsg.LongitudeFirstFactory
org.geotools.referencing.factory.wms.AutoCRSFactory
org.geotools.referencing.factory.wms.WebCRSFactory
org.geotools.referencing.factory.URN_AuthorityFactory
org.geotools.referencing.factory.HTTP_AuthorityFactory
org.vfny.geoserver.crs.GeoserverCustomWKTFactory
16:16:36,177 INFO [STDOUT] 16:16:36,170 WARN [geotools] Unavailable authority factory: European Petroleum Survey Group
org.opengis.referencing.FactoryException: Failed to connect to the EPSG database.
at org.geotools.referencing.factory.epsg.ThreadedEpsgFactory.createBackingStore(ThreadedEpsgFactory.java:483)
at org.geotools.referencing.factory.DeferredAuthorityFactory.getBackingStore(DeferredAuthorityFactory.java:138)
at org.geotools.referencing.factory.BufferedAuthorityFactory.isAvailable(BufferedAuthorityFactory.java:232)
at org.geotools.referencing.factory.DeferredAuthorityFactory.isAvailable(DeferredAuthorityFactory.java:126)
at org.geotools.factory.FactoryRegistry.isAvailable(FactoryRegistry.java:663)
at org.geotools.factory.FactoryRegistry.isAcceptable(FactoryRegistry.java:491)
and Caused by: java.sql.SQLException: User not found: SA
at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
at org.hsqldb.jdbc.jdbcConnection.(Unknown Source)
at org.hsqldb.jdbcDriver.getConnection(Unknown Source)
at org.hsqldb.jdbc.jdbcDataSource.getConnection(Unknown Source)
at org.hsqldb.jdbc.jdbcDataSource.getConnection(Unknown Source)
at org.geotools.referencing.factory.epsg.FactoryOnHSQL.createBackingStore(FactoryOnHSQL.java:317)
at org.geotools.referencing.factory.epsg.ThreadedEpsgFactory.createBackingStore0(ThreadedEpsgFactory.java:420)
at org.geotools.referencing.factory.epsg.ThreadedEpsgFactory.createBackingStore(ThreadedEpsgFactory.java:476)
… 179 more
So the user SA does not exist, but i have never deleted it !! does anyone have any tips on how to recreate a valid EPSG database that GeoServer can use ?
Thanks in advance,
Andy
Andy Dale ha scritto:
Hi,
I have recently started to encounter an error in GeoServer, it can no longer connect to the EPSG database. I have been running it fine for the last 2 months without any problems. The only thing out of the ordinary i have done today is to build a version of hibernate, and now i get the following exceptions on startup:
16:16:35,716 INFO [STDOUT] 16:16:35,716 INFO [geotools] Factory implementations for category CRSAuthorityFactory:
org.geotools.referencing.factory.epsg.FactoryOnHSQL
org.geotools.referencing.factory.epsg.DefaultFactory
org.geotools.referencing.factory.epsg.FactoryUsingWKT
org.geotools.referencing.factory.epsg.LongitudeFirstFactory
org.geotools.referencing.factory.wms.AutoCRSFactory
org.geotools.referencing.factory.wms.WebCRSFactory
org.geotools.referencing.factory.URN_AuthorityFactory
org.geotools.referencing.factory.HTTP_AuthorityFactory
org.vfny.geoserver.crs.GeoserverCustomWKTFactory
16:16:36,177 INFO [STDOUT] 16:16:36,170 WARN [geotools] Unavailable authority factory: European Petroleum Survey Group
org.opengis.referencing.FactoryException: Failed to connect to the EPSG database.
at org.geotools.referencing.factory.epsg.ThreadedEpsgFactory.createBackingStore(ThreadedEpsgFactory.java:483)
at org.geotools.referencing.factory.DeferredAuthorityFactory.getBackingStore(DeferredAuthorityFactory.java:138)
at org.geotools.referencing.factory.BufferedAuthorityFactory.isAvailable(BufferedAuthorityFactory.java:232)
at org.geotools.referencing.factory.DeferredAuthorityFactory.isAvailable(DeferredAuthorityFactory.java:126)
at org.geotools.factory.FactoryRegistry.isAvailable(FactoryRegistry.java:663)
at org.geotools.factory.FactoryRegistry.isAcceptable(FactoryRegistry.java:491)
and Caused by: java.sql.SQLException: User not found: SA
at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
at org.hsqldb.jdbc.jdbcConnection.<init>(Unknown Source)
at org.hsqldb.jdbcDriver.getConnection(Unknown Source)
at org.hsqldb.jdbc.jdbcDataSource.getConnection(Unknown Source)
at org.geotools.referencing.factory.epsg.FactoryOnHSQL.createBackingStore(FactoryOnHSQL.java:317)
at org.geotools.referencing.factory.epsg.ThreadedEpsgFactory.createBackingStore0(ThreadedEpsgFactory.java:420)
at org.geotools.referencing.factory.epsg.ThreadedEpsgFactory.createBackingStore(ThreadedEpsgFactory.java:476)
... 179 more
So the user SA does not exist, but i have never deleted it !! does anyone have any tips on how to recreate a valid EPSG database that GeoServer can use ?
This is really weird. Anyways, stop GeoServer, lookup the temp directory used by your web container and wipe out any GeoTools subdirectory you may find, then restart GeoServer and the db should be re-created.
Where is that temp directory, it really depends... it may be your
user's one, or one set by the container itself (Tomcat has its own
below the tomcat directory, for example).
Hope this helps
Cheers
Andrea
Hi,
I am running it from JBoss so the tmp dir is $JBOSS_HOME/server/default/tmp. I have cleared that directory, but the problem still exists.
Cheers,
Andy
On 18/02/2008, Andrea Aime <aaime@anonymised.com> wrote:
Andy Dale ha scritto:
Hi,
I have recently started to encounter an error in GeoServer, it can no
longer connect to the EPSG database. I have been running it fine for
the last 2 months without any problems. The only thing out of the
ordinary i have done today is to build a version of hibernate, and now i
get the following exceptions on startup:
16:16:35,716 INFO [STDOUT] 16:16:35,716 INFO [geotools] Factory
implementations for category CRSAuthorityFactory:
org.geotools.referencing.factory.epsg.FactoryOnHSQL
org.geotools.referencing.factory.epsg.DefaultFactory
org.geotools.referencing.factory.epsg.FactoryUsingWKT
org.geotools.referencing.factory.epsg.LongitudeFirstFactory
org.geotools.referencing.factory.wms.AutoCRSFactory
org.geotools.referencing.factory.wms.WebCRSFactory
org.geotools.referencing.factory.URN_AuthorityFactory
org.geotools.referencing.factory.HTTP_AuthorityFactory
org.vfny.geoserver.crs.GeoserverCustomWKTFactory
16:16:36,177 INFO [STDOUT] 16:16:36,170 WARN [geotools] Unavailable
authority factory: European Petroleum Survey Group
org.opengis.referencing.FactoryException: Failed to connect to the EPSG
database.
at
org.geotools.referencing.factory.epsg.ThreadedEpsgFactory.createBackingStore(ThreadedEpsgFactory.java:483)
at
org.geotools.referencing.factory.DeferredAuthorityFactory.getBackingStore(DeferredAuthorityFactory.java:138)
at
org.geotools.referencing.factory.BufferedAuthorityFactory.isAvailable(BufferedAuthorityFactory.java:232)
at
org.geotools.referencing.factory.DeferredAuthorityFactory.isAvailable(DeferredAuthorityFactory.java:126)
at
org.geotools.factory.FactoryRegistry.isAvailable(FactoryRegistry.java:663)
at
org.geotools.factory.FactoryRegistry.isAcceptable(FactoryRegistry.java:491)
and Caused by: java.sql.SQLException: User not found: SA
at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
at org.hsqldb.jdbc.jdbcConnection.(Unknown Source)
at org.hsqldb.jdbcDriver.getConnection(Unknown Source)
at org.hsqldb.jdbc.jdbcDataSource.getConnection(Unknown Source)
at org.hsqldb.jdbc.jdbcDataSource.getConnection(Unknown Source)
at
org.geotools.referencing.factory.epsg.FactoryOnHSQL.createBackingStore(FactoryOnHSQL.java:317)
at
org.geotools.referencing.factory.epsg.ThreadedEpsgFactory.createBackingStore0(ThreadedEpsgFactory.java:420)
at
org.geotools.referencing.factory.epsg.ThreadedEpsgFactory.createBackingStore(ThreadedEpsgFactory.java:476)
… 179 more
So the user SA does not exist, but i have never deleted it !! does
anyone have any tips on how to recreate a valid EPSG database that
GeoServer can use ?
This is really weird. Anyways, stop GeoServer, lookup the temp directory
used by your web container and wipe out any GeoTools subdirectory you
may find, then restart GeoServer and the db should be re-created.
Where is that temp directory, it really depends… it may be your
user’s one, or one set by the container itself (Tomcat has its own
below the tomcat directory, for example).
Hope this helps
Cheers
Andrea
Andy Dale ha scritto:
Hi,
I am running it from JBoss so the tmp dir is $JBOSS_HOME/server/default/tmp. I have cleared that directory, but the problem still exists.
Hum, maybe the hibernate build kicked into the jboss classpath a different version of hsql?
Cheers
Andrea
Hi,
My last response was a bit short as i had to catch a train. I forgot to mention that i run JBoss/Geoserver with the -DGEOSERVER_DATA_DIR, the problem exists with this startup parameter, and without it.
From inspecting the JBoss lib dir, the hsqldb jars are still from October 17 2006, so it has not replaced any jars.
I am really at a loss as how to go about fixing this, out of interest how do I make geoserver use a postgres database for the EPSG database, and can it use the spatial_ref_sys table generated by Postgis ??
Cheers,
Andy
On 18/02/2008, Andrea Aime <aaime@anonymised.com> wrote:
Andy Dale ha scritto:
Hi,
I am running it from JBoss so the tmp dir is
$JBOSS_HOME/server/default/tmp. I have cleared that directory, but the
problem still exists.
Hum, maybe the hibernate build kicked into the jboss classpath a
different version of hsql?
Cheers
Andrea
Andy Dale ha scritto:
Hi,
My last response was a bit short as i had to catch a train. I forgot to mention that i run JBoss/Geoserver with the -DGEOSERVER_DATA_DIR, the problem exists with this startup parameter, and without it.
From inspecting the JBoss lib dir, the hsqldb jars are still from October 17 2006, so it has not replaced any jars.
Does running GeoServer on a separate Tomcat install work?
I am really at a loss as how to go about fixing this, out of interest how do I make geoserver use a postgres database for the EPSG database, and can it use the spatial_ref_sys table generated by Postgis ??
No, that table has no more sufficient information for GeoServer
to work properly. It lack the datum shift parameters as well as
the area of definition of a CRS. It could be usable only if you
don't plan to reproject the data, but we don't have any support
for it in practice.
There is an extension in geotools that allows to stick the official
epsg database into postgis but I've never used it, and I guess it
would require some modifications in GeoServer to make it usable
(for example, where do you specify the connection parameters?).
Anyone else with experience on that one?
Cheers
Andrea
Hi,
I can confirm that under a standalone tomcat installation it works without any problems. I have stripped out everything from JBoss, just leaving it to deploy geoserver.war, and i still get the cannot connect to the EPSG database because the user SA does not exist.
I will try and investigate why geoserver cannot connect to hsqldb anymore.
Cheers,
Andy
On 19/02/2008, Andrea Aime <aaime@anonymised.com> wrote:
Andy Dale ha scritto:
Hi,
My last response was a bit short as i had to catch a train. I forgot to
mention that i run JBoss/Geoserver with the -DGEOSERVER_DATA_DIR, the
problem exists with this startup parameter, and without it.
From inspecting the JBoss lib dir, the hsqldb jars are still from
October 17 2006, so it has not replaced any jars.
Does running GeoServer on a separate Tomcat install work?
I am really at a loss as how to go about fixing this, out of interest
how do I make geoserver use a postgres database for the EPSG database,
and can it use the spatial_ref_sys table generated by Postgis ??
No, that table has no more sufficient information for GeoServer
to work properly. It lack the datum shift parameters as well as
the area of definition of a CRS. It could be usable only if you
don’t plan to reproject the data, but we don’t have any support
for it in practice.
There is an extension in geotools that allows to stick the official
epsg database into postgis but I’ve never used it, and I guess it
would require some modifications in GeoServer to make it usable
(for example, where do you specify the connection parameters?).
Anyone else with experience on that one?
Cheers
Andrea
Hi,
Can you tell me where the datasource definition file is for hsqldb in GeoServer (the connection parameters).
Cheers,
Andy
On 19/02/2008, Andy Dale <andy.dale@anonymised.com> wrote:
Hi,
I can confirm that under a standalone tomcat installation it works without any problems. I have stripped out everything from JBoss, just leaving it to deploy geoserver.war, and i still get the cannot connect to the EPSG database because the user SA does not exist.
I will try and investigate why geoserver cannot connect to hsqldb anymore.
Cheers,
Andy
On 19/02/2008, Andrea Aime <aaime@anonymised.com> wrote:
Andy Dale ha scritto:
Hi,
My last response was a bit short as i had to catch a train. I forgot to
mention that i run JBoss/Geoserver with the -DGEOSERVER_DATA_DIR, the
problem exists with this startup parameter, and without it.
From inspecting the JBoss lib dir, the hsqldb jars are still from
October 17 2006, so it has not replaced any jars.
Does running GeoServer on a separate Tomcat install work?
I am really at a loss as how to go about fixing this, out of interest
how do I make geoserver use a postgres database for the EPSG database,
and can it use the spatial_ref_sys table generated by Postgis ??
No, that table has no more sufficient information for GeoServer
to work properly. It lack the datum shift parameters as well as
the area of definition of a CRS. It could be usable only if you
don’t plan to reproject the data, but we don’t have any support
for it in practice.
There is an extension in geotools that allows to stick the official
epsg database into postgis but I’ve never used it, and I guess it
would require some modifications in GeoServer to make it usable
(for example, where do you specify the connection parameters?).
Anyone else with experience on that one?
Cheers
Andrea
Andy Dale ha scritto:
Hi,
Can you tell me where the datasource definition file is for hsqldb in GeoServer (the connection parameters).
They are hard coded, since the code creates the database as well so
it should have complete control on it... maybe not this time, thought
I don't understand why...
http://svn.geotools.org/geotools/branches/2.4.x/modules/plugin/epsg-hsql/src/main/java/org/geotools/referencing/factory/epsg/HSQLDataSource.java
Have you located the jboss temp directory and tried to wipe it out
clean? Or maybe try to set -DEPSG-HSQL.directory=/my/new/path
so that the epsg module is forced to recreate the database in a new
folder.
Cheers
Andrea
Andy Dale ha scritto:
Hi,
I have cleared out the tmp but this has no effect on the problem. However setting the DEPSG-HSQL.directory=/my/new/path startup param does fix the problem. The question is where was it storing the data before ??
From reading the code, it seems if no property is set for EPSG-HSQL.directory it should use the java.io.tmpdir vairable and create a folder Geotools. After inspecting my hard drive i can confirm that it is using the /tmp in Linux as there is a folder Geotools there. After looking in the following dir /tmp/Geotools/Databases/HSQL it only has the EPSG.properties file in there and not the .data or .script files. So in my opinion building Hibernate did not effect GeoServer, but it is quite possible that the OS "cleaned" up the /tmp dir.
Thanks for your help.
Hmmm... we probably should consider moving the associated jira issue (http://jira.codehaus.org/browse/GEOS-1134) to 1.6.1 or 1.6.2.
I remembered it was already opened when you talked about the global
/tmp directory. On windows it's not an issue (the OS does not clean
up the temp), on linux and tomcat neither because Tomcat overrides
the tmp dir, but evidently JBoss does not...
Cheers
Andrea