search

geonetwork / core-geonetwork

GeoNetwork is a catalog application to manage spatially referenced resources. It provides powerful metadata editing and search functions as well as an interactive web map viewer. It is currently used in numerous Spatial Data Infrastructure initiatives across the world.
http://geonetwork-opensource.org/
GNU General Public License v2.0
428 stars 489 forks source link

GeoNetwork 4.0 - Unable to deploy in Oracle #5141

Open OscarD31 opened 4 years ago

OscarD31 commented 4 years ago

We are facing issues when deploying GeoNetwork 4.0 in an Oracle Database 11g Release 11.2.0.4.0 - 64bit.

The log says the following: 

java.sql.SQLSyntaxErrorException: ORA-00920: operador relacional no v⭩do.

That error in Oracle means:

Error: ORA-00920: invalid relational operator
Causa: A search condition was entered with an invalid or missing relational operator.
Acción: Include a valid relational operator such as =, !=, ^=, <>, >, <, >=, <=, ALL, ANY, [NOT] BETWEEN, EXISTS, [NOT] IN, IS [NOT] NULL, or [NOT] LIKE in the condition.

Not sure if it is related with the deployment issue.

We have also changed the schema in the config-spring-geonetwork.xml file, but it hasn't solved the problem.

OscarD31 commented 3 years ago

Good morning.

We are still unable to deploy GeoNetwork 4.0 in Oracle. We have located an issue with the table "SETTINGS".

The field "VALUE" is created as a LONG type instead of a CHAR type. The INSERTS in that table fail. We have changed the data type and perform all the inserts operations manually, but we still get errors when deploying GeoNetwork and it doesn't load.

fxprunayre commented 3 years ago

The field "VALUE" is created as a LONG type instead of a CHAR type.

Sounds odd, can you check other column like data in metadata table which use the same definition https://github.com/geonetwork/core-geonetwork/blob/4.0.x/domain/src/main/java/org/fao/geonet/domain/AbstractMetadata.java#L117-L120 as value in setting https://github.com/geonetwork/core-geonetwork/blob/4.0.x/domain/src/main/java/org/fao/geonet/domain/Setting.java#L66-L68

Maybe turn on hibernate log on startup to check which queries are used?

OscarD31 commented 3 years ago

We have destroyed the database and do a new deployment of GeoNetwork 4.0.1.

This is the METADATA table that is created:

image

This the SETTINGS table that is created:

image

In version 3.10.5 the database is not created with those datatypes:

https://github.com/geonetwork/core-geonetwork/blob/3.10.x/domain/src/main/java/org/fao/geonet/domain/Setting.java#L66-L68

Also, we are unable to see any SQL sentence in the log. Maybe we have not enabled the debugging correctly. Is there any guide about it?

geonetwork (1).log

juanluisrp commented 3 years ago

In the log you attached the error is raised when populating the database with initial data:

Caused by: java.sql.SQLException: ORA-00604: error occurred at recursive SQL level 1
ORA-01000: maximum open cursors exceeded
ORA-00604: error occurred at recursive SQL level 1
ORA-01000: maximum open cursors exceeded
ORA-01000: maximum open cursors exceeded
OscarD31 commented 3 years ago

In the log you attached the error is raised when populating the database with initial data:

Yes, because there is a mismatch between the data types created in Oracle and the inserts it tries to perform against those tables.

fxprunayre commented 3 years ago

Maybe you can try some of the suggestions in https://stackoverflow.com/questions/13090089/org-hibernate-type-texttype-and-oracle and see if you can make it work? Hibernate was updated in version 4 so it sounds related to this.

juanluisrp commented 3 years ago

After increasing the number of open cursors allowed in the database I get the database created but GN throws this error

2020-11-10 13:34:56,650 ERROR [jeeves.engine] - {Message=could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet, Error=Raised exception while starting the application. Fix the error and restart., Handler=org.fao.geonet.Geonetwork, Exception=org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet, Stack=org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet
     at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:281)
     at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:255)
     at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:528)
     at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:61)
     at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:242)
     at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:153)
     at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
     at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:178)
     at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
     at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:95)
     at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
     at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212)
     at com.sun.proxy.$Proxy173.findAll(Unknown Source)
     at org.fao.geonet.camelPeriodicProducer.MessageProducerService.configure(MessageProducerService.java:84)
     at org.fao.geonet.camelPeriodicProducer.MessageProducerService.onApplicationEvent(MessageProducerService.java:78)
     at org.fao.geonet.camelPeriodicProducer.MessageProducerService.onApplicationEvent(MessageProducerService.java:52)
     at org.springframework.context.event.SimpleApplicationEventMulticaster.doInvokeListener(SimpleApplicationEventMulticaster.java:172)
     at org.springframework.context.event.SimpleApplicationEventMulticaster.invokeListener(SimpleApplicationEventMulticaster.java:165)
     at org.springframework.context.event.SimpleApplicationEventMulticaster.multicastEvent(SimpleApplicationEventMulticaster.java:139)
     at org.springframework.context.support.AbstractApplicationContext.publishEvent(AbstractApplicationContext.java:403)
     at org.springframework.context.support.AbstractApplicationContext.publishEvent(AbstractApplicationContext.java:360)
     at org.fao.geonet.Geonetwork.start(Geonetwork.java:337)
     at jeeves.server.JeevesEngine.initAppHandler(JeevesEngine.java:425)
     at jeeves.server.JeevesEngine.init(JeevesEngine.java:170)
     at jeeves.server.sources.http.JeevesServlet.init(JeevesServlet.java:80)
     at javax.servlet.GenericServlet.init(GenericServlet.java:244)
     at org.eclipse.jetty.servlet.ServletHolder$Wrapper.init(ServletHolder.java:1313)
     at org.eclipse.jetty.servlet.ServletHolder.initServlet(ServletHolder.java:602)
     at org.eclipse.jetty.servlet.ServletHolder.initialize(ServletHolder.java:414)
     at org.eclipse.jetty.servlet.ServletHandler.lambda$initialize$0(ServletHandler.java:772)
     at java.util.stream.SortedOps$SizedRefSortingSink.end(SortedOps.java:357)
     at java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:483)
     at java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:472)
     at java.util.stream.StreamSpliterators$WrappingSpliterator.forEachRemaining(StreamSpliterators.java:313)
     at java.util.stream.Streams$ConcatSpliterator.forEachRemaining(Streams.java:743)
     at java.util.stream.ReferencePipeline$Head.forEach(ReferencePipeline.java:647)
     at org.eclipse.jetty.servlet.ServletHandler.initialize(ServletHandler.java:796)
     at org.eclipse.jetty.servlet.ServletContextHandler.startContext(ServletContextHandler.java:368)
     at org.eclipse.jetty.webapp.WebAppContext.startWebapp(WebAppContext.java:1457)
     at org.eclipse.jetty.webapp.WebAppContext.startContext(WebAppContext.java:1422)
     at org.eclipse.jetty.server.handler.ContextHandler.doStart(ContextHandler.java:911)
     at org.eclipse.jetty.servlet.ServletContextHandler.doStart(ServletContextHandler.java:277)
     at org.eclipse.jetty.webapp.WebAppContext.doStart(WebAppContext.java:524)
     at org.eclipse.jetty.util.component.AbstractLifeCycle.start(AbstractLifeCycle.java:72)
     at org.eclipse.jetty.deploy.bindings.StandardStarter.processBinding(StandardStarter.java:46)
     at org.eclipse.jetty.deploy.AppLifeCycle.runBindings(AppLifeCycle.java:188)
     at org.eclipse.jetty.deploy.DeploymentManager.requestAppGoal(DeploymentManager.java:517)
     at org.eclipse.jetty.deploy.DeploymentManager.addApp(DeploymentManager.java:157)
     at org.eclipse.jetty.deploy.providers.ScanningAppProvider.fileAdded(ScanningAppProvider.java:173)
     at org.eclipse.jetty.deploy.providers.WebAppProvider.fileAdded(WebAppProvider.java:427)
     at org.eclipse.jetty.deploy.providers.ScanningAppProvider$1.fileAdded(ScanningAppProvider.java:66)
     at org.eclipse.jetty.util.Scanner.reportAddition(Scanner.java:784)
     at org.eclipse.jetty.util.Scanner.reportDifferences(Scanner.java:753)
     at org.eclipse.jetty.util.Scanner.scan(Scanner.java:641)
     at org.eclipse.jetty.util.Scanner.doStart(Scanner.java:540)
     at org.eclipse.jetty.util.component.AbstractLifeCycle.start(AbstractLifeCycle.java:72)
     at org.eclipse.jetty.deploy.providers.ScanningAppProvider.doStart(ScanningAppProvider.java:146)
     at org.eclipse.jetty.util.component.AbstractLifeCycle.start(AbstractLifeCycle.java:72)
     at org.eclipse.jetty.deploy.DeploymentManager.startAppProvider(DeploymentManager.java:605)
     at org.eclipse.jetty.deploy.DeploymentManager.doStart(DeploymentManager.java:252)
     at org.eclipse.jetty.util.component.AbstractLifeCycle.start(AbstractLifeCycle.java:72)
     at org.eclipse.jetty.util.component.ContainerLifeCycle.start(ContainerLifeCycle.java:169)
     at org.eclipse.jetty.server.Server.start(Server.java:423)
     at org.eclipse.jetty.util.component.ContainerLifeCycle.doStart(ContainerLifeCycle.java:117)
     at org.eclipse.jetty.server.handler.AbstractHandler.doStart(AbstractHandler.java:97)
     at org.eclipse.jetty.server.Server.doStart(Server.java:387)
     at org.eclipse.jetty.util.component.AbstractLifeCycle.start(AbstractLifeCycle.java:72)
     at org.eclipse.jetty.xml.XmlConfiguration.lambda$main$2(XmlConfiguration.java:1908)
     at java.security.AccessController.doPrivileged(Native Method)
     at org.eclipse.jetty.xml.XmlConfiguration.main(XmlConfiguration.java:1857)
Caused by: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
     at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:63)
     at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
     at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:113)
     at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:99)
     at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:67)
     at org.hibernate.loader.Loader.getResultSet(Loader.java:2285)
     at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:2038)
     at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:2000)
     at org.hibernate.loader.Loader.doQuery(Loader.java:951)
     at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:352)
     at org.hibernate.loader.Loader.doList(Loader.java:2831)
     at org.hibernate.loader.Loader.doList(Loader.java:2813)
     at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2645)
     at org.hibernate.loader.Loader.list(Loader.java:2640)
     at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:506)
     at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:400)
     at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:219)
     at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1412)
     at org.hibernate.query.internal.AbstractProducedQuery.doList(AbstractProducedQuery.java:1565)
     at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1533)
     at org.hibernate.query.Query.getResultList(Query.java:165)
     at org.hibernate.query.criteria.internal.compile.CriteriaQueryTypeQueryAdapter.getResultList(CriteriaQueryTypeQueryAdapter.java:76)
     at org.springframework.data.jpa.repository.support.SimpleJpaRepository.findAll(SimpleJpaRepository.java:355)
     at org.springframework.data.jpa.repository.support.SimpleJpaRepository.findAll(SimpleJpaRepository.java:78)
     at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
     at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
     at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
     at java.lang.reflect.Method.invoke(Method.java:498)
     at org.springframework.data.repository.core.support.RepositoryComposition$RepositoryFragments.invoke(RepositoryComposition.java:371)
     at org.springframework.data.repository.core.support.RepositoryComposition.invoke(RepositoryComposition.java:204)
     at org.springframework.data.repository.core.support.RepositoryFactorySupport$ImplementationMethodExecutionInterceptor.invoke(RepositoryFactorySupport.java:657)
     at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
     at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.doInvoke(RepositoryFactorySupport.java:621)
     at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:605)
     at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
     at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:80)
     at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
     at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:366)
     at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:118)
     at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
     at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:139)
     ... 64 more
Caused by: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

     at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:509)
     at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:461)
     at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1104)
     at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:550)
     at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:268)
     at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:655)
     at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:270)
     at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:91)
     at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:807)
     at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:983)
     at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1168)
     at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3666)
     at oracle.jdbc.driver.T4CPreparedStatement.executeInternal(T4CPreparedStatement.java:1426)
     at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3713)
     at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1167)
     at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:122)
     at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:122)
     at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:122)
     at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:57)
     ... 100 more
Caused by: Error : 942, Position : 260, Sql = select messagepro0_.id as id1_27_, messagepro0_.cronExpression as cronexpression2_27_, messagepro0_.metadataUuid as metadatauuid3_27_, messagepro0_.strategy as strategy4_27_, messagepro0_.typeName as typename5_27_, messagepro0_.url as url6_27_ from GEONETWORK.MessageProducerEntity messagepro0_, OriginalSql = select messagepro0_.id as id1_27_, messagepro0_.cronExpression as cronexpression2_27_, messagepro0_.metadataUuid as metadatauuid3_27_, messagepro0_.strategy as strategy4_27_, messagepro0_.typeName as typename5_27_, messagepro0_.url as url6_27_ from GEONETWORK.MessageProducerEntity messagepro0_, Error Msg = ORA-00942: table or view does not exist

     at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:513)
     ... 118 more
juanluisrp commented 3 years ago

The above error (the one related to MessageProducerEntity table) happens in GN 4.0.1. I think this one is a new entity introduced in that version. Could it be caused by that the @Table annotation doesn't define a name @fxprunayre? https://github.com/geonetwork/core-geonetwork/blob/4e0d8b7f88e1a0abeee4459b111d21bb8b7babf3/domain/src/main/java/org/fao/geonet/domain/MessageProducerEntity.java#L35

In GeoNetwork 4.0.0 the database is created OK and GN works after increasing the max number of open cursors in Oracle. I set them in 4000.

See https://stackoverflow.com/a/16677244/1140558

fxprunayre commented 3 years ago

Could it be caused by that the @Table annotation doesn't define a name @fxprunayre?

I don't know, could you test if you've an oracle db for testing ?

In GeoNetwork 4.0.0 the database is created OK and GN works after increasing the max number of open cursors in Oracle. I set them in 4000.

And TextType is created as Long in Oracle as indicated by @OscarD31 ?

juanluisrp commented 3 years ago

And TextType is created as Long in Oracle as indicated by @OscarD31 ?

Yes, in the table column is defined as LONG but Oracle driver is able to interpret it with the right text value: image

image

juanluisrp commented 3 years ago

I don't know, could you test if you've an oracle db for testing ?

Setting the logger to print SQL executed I can see this:

create table MessageProducerEntity (
  id number(19,0) generated as identity, 
  cronExpression varchar2(255 char), 
  metadataUuid varchar2(255 char), 
  strategy varchar2(255 char), 
  typeName varchar2(255 char), 
  url varchar2(255 char), 
  primary key (id))

However the table is not created. If I manually try to create it with that SQL sentence Oracle 11g returns

SQL Error [2000] [42000]: ORA-02000: missing ALWAYS keyword

Oracle 11g doesn't support generated as identity. Maybe we should use a sequence as in other tables for the id: https://github.com/geonetwork/core-geonetwork/blob/4e0d8b7f88e1a0abeee4459b111d21bb8b7babf3/domain/src/main/java/org/fao/geonet/domain/MessageProducerEntity.java#L38-L40

is used instead of https://github.com/geonetwork/core-geonetwork/blob/4e0d8b7f88e1a0abeee4459b111d21bb8b7babf3/domain/src/main/java/org/fao/geonet/domain/Address.java#L57-L59 or https://github.com/geonetwork/core-geonetwork/blob/4e0d8b7f88e1a0abeee4459b111d21bb8b7babf3/domain/src/main/java/org/fao/geonet/domain/Link.java#L83-L86

Using a sequence the table is created:

diff --git a/domain/src/main/java/org/fao/geonet/domain/MessageProducerEntity.java b/domain/src/main/java/org/fao/geonet/domain/MessageProducerEntity.java
index 211bd9d295..49fe27f170 100644
--- a/domain/src/main/java/org/fao/geonet/domain/MessageProducerEntity.java
+++ b/domain/src/main/java/org/fao/geonet/domain/MessageProducerEntity.java
@@ -28,15 +28,18 @@ import javax.persistence.Entity;
 import javax.persistence.GeneratedValue;
 import javax.persistence.GenerationType;
 import javax.persistence.Id;
+import javax.persistence.SequenceGenerator;
 import javax.persistence.Table;
 import javax.persistence.UniqueConstraint;

 @Entity
 @Table(uniqueConstraints = @UniqueConstraint(columnNames = {"url", "typeName"}))
+@SequenceGenerator(name = Address.ID_SEQ_NAME, initialValue = 100, allocationSize = 1)
 public class MessageProducerEntity {
+    static final String ID_SEQ_NAME = "message_producer_entity_id_seq";

     @Id
-    @GeneratedValue(strategy = GenerationType.IDENTITY)
+    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = ID_SEQ_NAME)
     private Long id;

     private WfsHarvesterParamEntity wfsHarvesterParamEntity;

Generated SQL:

create table MessageProducerEntity (
  id number(19,0) not null, 
  cronExpression varchar2(255 char), 
  metadataUuid varchar2(255 char), 
  strategy varchar2(255 char), 
  typeName varchar2(255 char), 
  url varchar2(255 char), primary key (id))

image

and GN 4.0.1 can start.

fxprunayre commented 3 years ago

Using a sequence the table is created:

I don't know why @cmangeat used Identity but it is probably better to have it consistent with other tables. Could you apply your change to 4.0.x @juanluisrp ? Thanks

OscarD31 commented 3 years ago

This issue with permissions has been solved and it was unrelated to GeoNetwork

Good morning!

I have tried following the same steps with the version 4.0.0 of GeoNetwork, but we are still unable to make it work.

geonetwork_20201111.log

Now it says it cannot access a file.

This issue with permissions has been solved and it was unrelated to GeoNetwork

OscarD31 commented 3 years ago

Thanks for all the answers! We have been able to make GeoNetwork 4.0.0 work with Oracle.

One question, in future realeses are you going to change the issue with the LONG fields? I know it is working with them, but we prefer to avoid deprecated fields.