@IdSequence not using additional datasource

jw90 · May 14, 2019, 7:08am

Hi there,

I’m using an oracle database as an additional data source.
When I’m using @IdSequence on an Entity, then a new IdSequence is created in the main database, but the sequence in oracle database is not used.

Furthermore there are sequences created in the main database, that are not used by the entities for the additional datastore.

Can you fix this, please.

Thank you very much!

Kind regards,
J

subbotin · May 24, 2019, 1:57pm

Hi,
Look at the property Appendix B: Application Properties - CUBA Platform. Developer’s Manual

jw90 · June 5, 2019, 5:47am

Thank you, that worked.

jw90 · June 25, 2019, 5:49pm

Hi,
I have another question about @IdSequence.

Why does CUBA Platform 7.0.6 tries to create sequences, that already exists?

create sequence VORGANG_ID_SEQ increment by 100 start with 0 nocache minvalue 0

Our database blocks it, and then the view stops working with an “ORA-01031” exception.
I haven’t found any other error.

I really would appreciate your help.

Kind regards,
J

krivopustov · June 26, 2019, 11:02am

Could you clarify how you use this annotation and where do you see this SQL command?

jw90 · June 27, 2019, 11:27am

My entity starts like this:

package de.nexloy.crm.vorgangsverwaltung.entity;

import com.haulmont.chile.core.annotations.Composition;
import com.haulmont.chile.core.annotations.MetaClass;
import com.haulmont.chile.core.annotations.NamePattern;
import com.haulmont.cuba.core.entity.BaseLongIdEntity;
import com.haulmont.cuba.core.entity.annotation.IdSequence;
import com.haulmont.cuba.core.entity.annotation.OnDelete;
import com.haulmont.cuba.core.entity.annotation.UnavailableInSecurityConstraints;
import com.haulmont.cuba.core.global.DeletePolicy;
import com.haulmont.cuba.core.global.DesignSupport;

import javax.persistence.*;
import java.util.Date;
import java.util.List;

@MappedSuperclass
@MetaClass(name = "sys$BaseIdentityIdEntity")
@UnavailableInSecurityConstraints
@NamePattern("%s %s|id,bezug")
@DesignSupport("{'imported':true}")
@Table(name = "VORGANG")
@Entity(name = "vorgangsverwaltung$Vorgang")
@IdSequence(name = "VORGANG_ID_SEQ",cached = true)
public class Vorgang extends BaseLongIdEntity
{
// ...
// long list of variables and getters and setters - nothing special
// ...
}

Our database logs all SQL errors, so I can check if something went wrong. That’s the reason why I know, what has been executed by CUBA Platform and failed and this point.

krivopustov · June 28, 2019, 3:11pm

Try to remove @MappedSuperclass from your entity class, it’s incorrect.
Please let us know if it doesn’t solve the issue.

jw90 · June 28, 2019, 5:53pm

Still the same exception:

java.sql.SQLSyntaxErrorException: ORA-01031: Nicht ausreichende Berechtigungen

	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:447)
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
	at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:951)
	at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:513)
	at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:227)
	at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
	at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:208)
	at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1046)
	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1336)
	at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3613)
	at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3694)
	at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:1354)
	at org.apache.tomcat.dbcp.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:97)
	at org.apache.tomcat.dbcp.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:97)
	at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeDirectNoSelect(DatabaseAccessor.java:942)
	at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeNoSelect(DatabaseAccessor.java:1014)
	at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:656)
	at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:564)
	at org.eclipse.persistence.internal.sessions.AbstractSession.basicExecuteCall(AbstractSession.java:2093)
	at org.eclipse.persistence.sessions.server.ClientSession.executeCall(ClientSession.java:311)
	at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:275)
	at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:261)
	at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeNoSelectCall(DatasourceCallQueryMechanism.java:304)
	at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeNoSelect(DatasourceCallQueryMechanism.java:284)
	at org.eclipse.persistence.queries.DataModifyQuery.executeDatabaseQuery(DataModifyQuery.java:87)
	at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:914)
	at org.eclipse.persistence.queries.DatabaseQuery.executeInUnitOfWork(DatabaseQuery.java:813)
	at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:3020)
	at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1892)
	at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1874)
	at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1839)
	at org.eclipse.persistence.internal.jpa.QueryImpl.executeUpdate(QueryImpl.java:302)
	at com.haulmont.cuba.core.sys.QueryImpl.executeUpdate(QueryImpl.java:460)
	at com.haulmont.cuba.core.app.SequencesImpl.checkSequenceExists(SequencesImpl.java:177)
	at com.haulmont.cuba.core.app.SequencesImpl.getResult(SequencesImpl.java:133)
	at com.haulmont.cuba.core.app.SequencesImpl.createNextValue(SequencesImpl.java:62)
	at com.haulmont.cuba.core.app.NumberIdWorker.createCachedLongId(NumberIdWorker.java:70)
	at com.haulmont.cuba.core.app.NumberIdServiceBean.createCachedLongId(NumberIdServiceBean.java:37)
	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.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:343)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:198)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
	at org.springframework.aop.aspectj.MethodInvocationProceedingJoinPoint.proceed(MethodInvocationProceedingJoinPoint.java:88)
	at com.haulmont.cuba.core.sys.ServiceInterceptor.aroundInvoke(ServiceInterceptor.java:117)
	at sun.reflect.GeneratedMethodAccessor210.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethodWithGivenArgs(AbstractAspectJAdvice.java:644)
	at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethod(AbstractAspectJAdvice.java:633)
	at org.springframework.aop.aspectj.AspectJAroundAdvice.invoke(AspectJAroundAdvice.java:70)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:175)
	at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:93)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212)
	at com.sun.proxy.$Proxy502.createCachedLongId(Unknown Source)
	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.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:343)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:206)
	at com.sun.proxy.$Proxy603.createCachedLongId(Unknown Source)
	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.remoting.support.RemoteInvocation.invoke(RemoteInvocation.java:215)
	at com.haulmont.cuba.core.sys.remoting.CubaRemoteInvocationExecutor.invoke(CubaRemoteInvocationExecutor.java:110)
	at org.springframework.remoting.support.RemoteInvocationBasedExporter.invoke(RemoteInvocationBasedExporter.java:78)
	at org.springframework.remoting.support.RemoteInvocationBasedExporter.invokeAndCreateResult(RemoteInvocationBasedExporter.java:114)
	at com.haulmont.cuba.core.sys.remoting.HttpServiceExporter.handleRequest(HttpServiceExporter.java:78)
	at org.springframework.web.servlet.mvc.HttpRequestHandlerAdapter.handle(HttpRequestHandlerAdapter.java:53)
	at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1038)
	at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:942)
	at com.haulmont.cuba.core.sys.remoting.RemotingServlet.doService(RemotingServlet.java:126)
	at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1005)
	at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:908)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:661)
	at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:882)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:742)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:199)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)
	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:470)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:140)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:80)
	at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:624)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:342)
	at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:799)
	at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66)
	at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:861)
	at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1458)
	at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
	at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
	at java.lang.Thread.run(Thread.java:748)

subbotin · July 1, 2019, 7:37am

Hi,

ORA-01031: Nicht ausreichende Berechtigungen says that DB user doesn’t have permissions to create a sequence.

Please check that DB user has an ability to create sequences in the DB.

jw90 · July 1, 2019, 7:55am

Hi,

I understand the error, but I don’t understand why a sequence should be generated, that already exists.

The database user is not allowed to create objects and because the sequence already exists, it is also not needed. I would appreciate a solution, that doesn’t make me give the user all permissions on the database.

Kind regards,
J

subbotin · July 1, 2019, 8:41am

CUBA tries to find a sequence using select SEQUENCE_NAME from USER_SEQUENCES where SEQUENCE_NAME = ? query and creates a sequence if it doesn’t exist.

jw90 · July 1, 2019, 9:30am

In this case the user is only seeing his own database objects and doesn’t look for global sequences with public synonyms, which can also be used. This won’t work for us.

Is there a way to solve this?

I can only think of three possibilities, to solve this:

Change the user
We won’t do that, because the other user would be the schema admin
Alter the session to use another schema.
This is probably not possible in CUBA, because it has to be triggered on every login.
Change the code in CUBA to check all database objects using select SEQUENCE_NAME from ALL_SEQUENCES where SEQUENCE_NAME = ?

jw90 · July 8, 2019, 10:39am

Some other possibilities:
4. Skip the check in CUBA
Is this even possible?
5. Manage and generate ID with sequence in my own way
In this case, I’m not sure how I should do this in CUBA.

subbotin · July 9, 2019, 9:11am

I think the best solution now will be skip sequence check.

You can override spring bean method com.haulmont.cuba.core.app.SequencesImpl#checkSequenceExists and skip sequence check for your datastore:

protected void checkSequenceExists(Sequence sequence) {
     if (!'<your store name>'.equals(sequence.getDataStore()) {
          super.checkSequenceExists(sequence)
     }
}

and register bean with the overridden method in a spring.xml for core module:

<bean id="cuba_Sequences" class="<FQN>">

jw90 · July 10, 2019, 8:51am

Thank you very much.
This solved it for me.