JakrataEE code runs on TomEE with JTA datasource, but failes with RESOURCE

I am runnng Apache TomEE 9.0.0-M7 with Jakarta EE 9.1 Web Profile. When I run the application using a JTA datasource configuration, the code runs, however when I change to a RESOURCE_LOCAL configuration, the code fails with the exception below. The SQL in the query runs on the JTA configuration.

Please assist.

Internal Exception: java.sql.SQLSyntaxErrorException: user lacks privilege or object not found: TOWER
Error Code: -5501
Call: SELECT towerNumber, latitude, longitude, state, towerName, siteNumber FROM tower
Query: ReadAllQuery(referenceClass=Tower sql="SELECT towerNumber, latitude, longitude, state, towerName, siteNumber FROM tower")
    at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:343)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:691)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:567)
    at org.eclipse.persistence.internal.sessions.AbstractSession.basicExecuteCall(AbstractSession.java:2051)
    at org.eclipse.persistence.sessions.server.ServerSession.executeCall(ServerSession.java:611)
    at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:282)
    at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:268)
    at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeSelectCall(DatasourceCallQueryMechanism.java:341)
    at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.selectAllRows(DatasourceCallQueryMechanism.java:773)
    at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.selectAllRowsFromTable(ExpressionQueryMechanism.java:2848)
    at org.eclipse.persistence.internal.queries.ExpressionQueryMechanism.selectAllRows(ExpressionQueryMechanism.java:2801)
    at org.eclipse.persistence.queries.ReadAllQuery.executeObjectLevelReadQuery(ReadAllQuery.java:584)
    at org.eclipse.persistence.queries.ObjectLevelReadQuery.executeDatabaseQuery(ObjectLevelReadQuery.java:1236)
    at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:912)
    at org.eclipse.persistence.queries.ObjectLevelReadQuery.execute(ObjectLevelReadQuery.java:1195)
    at org.eclipse.persistence.queries.ReadAllQuery.execute(ReadAllQuery.java:485)
    at org.eclipse.persistence.queries.ObjectLevelReadQuery.executeInUnitOfWork(ObjectLevelReadQuery.java:1283)
    at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:3008)
    at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1844)
    at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1826)
    at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1791)
    at org.eclipse.persistence.internal.jpa.QueryImpl.executeReadQuery(QueryImpl.java:262)
    at org.eclipse.persistence.internal.jpa.QueryImpl.getResultList(QueryImpl.java:482)
    at za.co.ezimax.dashboard.Database.getTowers(Database.java:76)
    at za.co.ezimax.dashboard.Map.<init>(Map.java:15)
    at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:77)
    at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.base/java.lang.reflect.Constructor.newInstanceWithCaller(Constructor.java:499)
    at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:480)
    at org.apache.webbeans.inject.InjectableConstructor.doInjection(InjectableConstructor.java:72)
    at org.apache.webbeans.portable.InjectionTargetImpl.newInstance(InjectionTargetImpl.java:201)
    at org.apache.webbeans.portable.InjectionTargetImpl.produce(InjectionTargetImpl.java:184)
    at org.apache.webbeans.portable.AbstractProducer.produce(AbstractProducer.java:134)
    at org.apache.webbeans.component.AbstractOwbBean.create(AbstractOwbBean.java:124)
    at org.apache.webbeans.component.ManagedBean.create(ManagedBean.java:66)
    at org.apache.webbeans.context.creational.BeanInstanceBag.create(BeanInstanceBag.java:76)
    at org.apache.webbeans.context.AbstractContext.getInstance(AbstractContext.java:159)
    at org.apache.webbeans.context.AbstractContext.get(AbstractContext.java:125)
    at org.apache.webbeans.intercept.NormalScopedBeanInterceptorHandler.getContextualInstance(NormalScopedBeanInterceptorHandler.java:101)
    at org.apache.webbeans.intercept.RequestScopedBeanInterceptorHandler.getContextualInstance(RequestScopedBeanInterceptorHandler.java:76)
    at org.apache.webbeans.intercept.NormalScopedBeanInterceptorHandler.get(NormalScopedBeanInterceptorHandler.java:71)
    at za.co.ezimax.dashboard.Map$$OwbNormalScopeProxy0.getTowers(za/co/ezimax/dashboard/Map.java)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.base/java.lang.reflect.Method.invoke(Method.java:568)
    at jakarta.el.BeanELResolver.getValue(BeanELResolver.java:88)
    at jakarta.el.CompositeELResolver.getValue(CompositeELResolver.java:62)
    at org.apache.myfaces.el.unified.resolver.FacesCompositeELResolver.getValue(FacesCompositeELResolver.java:179)
    at org.apache.el.parser.AstValue.getValue(AstValue.java:169)
    at org.apache.el.ValueExpressionImpl.getValue(ValueExpressionImpl.java:190)
    at org.apache.webbeans.el22.WrappedValueExpression.getValue(WrappedValueExpression.java:68)
    at org.apache.myfaces.view.facelets.el.ContextAwareTagValueExpression.getValue(ContextAwareTagValueExpression.java:93)
    at jakarta.faces.component._DeltaStateHelper.eval(_DeltaStateHelper.java:357)
    at org.apache.myfaces.view.facelets.component.UIRepeat.getValue(UIRepeat.java:303)
    at org.apache.myfaces.view.facelets.component.UIRepeat.createDataModel(UIRepeat.java:226)
    at org.apache.myfaces.view.facelets.component.UIRepeat.getDataModel(UIRepeat.java:218)
    at org.apache.myfaces.view.facelets.component.UIRepeat._validateAttributes(UIRepeat.java:892)
    at org.apache.myfaces.view.facelets.component.UIRepeat.process(UIRepeat.java:1022)
    at org.apache.myfaces.view.facelets.component.UIRepeat.encodeChildren(UIRepeat.java:1816)
    at jakarta.faces.component.UIComponentBase.encodeAll(UIComponentBase.java:545)
    at jakarta.faces.component.UIComponentBase.encodeAll(UIComponentBase.java:554)
    at jakarta.faces.component.UIComponentBase.encodeAll(UIComponentBase.java:554)
    at org.apache.myfaces.view.facelets.FaceletViewDeclarationLanguage.renderView(FaceletViewDeclarationLanguage.java:1897)
    at org.apache.myfaces.application.ViewHandlerImpl.renderView(ViewHandlerImpl.java:315)
    at jakarta.faces.application.ViewHandlerWrapper.renderView(ViewHandlerWrapper.java:73)
    at org.apache.myfaces.lifecycle.RenderResponseExecutor.execute(RenderResponseExecutor.java:122)
    at org.apache.myfaces.lifecycle.LifecycleImpl.render(LifecycleImpl.java:266)
    at jakarta.faces.webapp.FacesServlet.service(FacesServlet.java:206)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:223)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:158)
    at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:185)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:158)
    at org.apache.openejb.server.httpd.EEFilter.doFilter(EEFilter.java:67)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:185)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:158)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:202)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:97)
    at org.apache.tomee.catalina.OpenEJBValve.invoke(OpenEJBValve.java:45)
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:543)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)
    at org.apache.tomee.catalina.OpenEJBSecurityListener$RequestCapturer.invoke(OpenEJBSecurityListener.java:97)
    at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:690)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:78)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:353)
    at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:374)
    at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65)
    at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:870)
    at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1696)
    at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
    at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136)
    at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635)
    at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
    at java.base/java.lang.Thread.run(Thread.java:833)
Caused by: java.sql.SQLSyntaxErrorException: user lacks privilege or object not found: TOWER
    at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
    at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
    at org.hsqldb.jdbc.JDBCPreparedStatement.<init>(Unknown Source)
    at org.hsqldb.jdbc.JDBCConnection.prepareStatement(Unknown Source)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.base/java.lang.reflect.Method.invoke(Method.java:568)
    at org.apache.tomcat.jdbc.pool.ProxyConnection.invoke(ProxyConnection.java:126)
    at org.apache.tomcat.jdbc.pool.JdbcInterceptor.invoke(JdbcInterceptor.java:108)
    at org.apache.tomcat.jdbc.pool.interceptor.AbstractCreateStatementInterceptor.invoke(AbstractCreateStatementInterceptor.java:75)
    at org.apache.tomcat.jdbc.pool.JdbcInterceptor.invoke(JdbcInterceptor.java:108)
    at org.apache.tomcat.jdbc.pool.DisposableConnectionFacade.invoke(DisposableConnectionFacade.java:81)
    at jdk.proxy3/jdk.proxy3.$Proxy106.prepareStatement(Unknown Source)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.prepareStatement(DatabaseAccessor.java:1598)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.prepareStatement(DatabaseAccessor.java:1547)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseCall.prepareStatement(DatabaseCall.java:814)
    at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:628)
    ... 95 more
Caused by: org.hsqldb.HsqlException: user lacks privilege or object not found: TOWER
    at org.hsqldb.error.Error.error(Unknown Source)
    at org.hsqldb.error.Error.error(Unknown Source)
    at org.hsqldb.SchemaManager.getTable(Unknown Source)
    at org.hsqldb.ParserDQL.readTableName(Unknown Source)
    at org.hsqldb.ParserDQL.readTableOrSubquery(Unknown Source)
    at org.hsqldb.ParserDQL.XreadTableReference(Unknown Source)
    at org.hsqldb.ParserDQL.XreadFromClause(Unknown Source)
    at org.hsqldb.ParserDQL.XreadTableExpression(Unknown Source)
    at org.hsqldb.ParserDQL.XreadQuerySpecification(Unknown Source)
    at org.hsqldb.ParserDQL.XreadSimpleTable(Unknown Source)
    at org.hsqldb.ParserDQL.XreadQueryPrimary(Unknown Source)
    at org.hsqldb.ParserDQL.XreadQueryTerm(Unknown Source)
    at org.hsqldb.ParserDQL.XreadQueryExpressionBody(Unknown Source)
    at org.hsqldb.ParserDQL.XreadQueryExpression(Unknown Source)
    at org.hsqldb.ParserDQL.compileCursorSpecification(Unknown Source)
    at org.hsqldb.ParserCommand.compilePart(Unknown Source)
    at org.hsqldb.ParserCommand.compileStatement(Unknown Source)
    at org.hsqldb.Session.compileStatement(Unknown Source)
    at org.hsqldb.StatementManager.compile(Unknown Source)
    at org.hsqldb.Session.execute(Unknown Source)
    ... 111 more

Here is my Persistance.xml

<?xml version="1.0" encoding="UTF-8"?>
<persistence xmlns="https://jakarta.ee/xml/ns/persistence"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="https://jakarta.ee/xml/ns/persistence https://jakarta.ee/xml/ns/persistence/persistence_3_0.xsd"
    version="3.0">
    <persistence-unit name="dashboard" transaction-type="RESOURCE_LOCAL">
        <provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>
        <exclude-unlisted-classes>false</exclude-unlisted-classes>      
        <properties>
            <property name="jakarta.persistence.jdbc.driver" value="com.mysql.cj.jdbc.Driver" />
            <property name="jakarta.persistence.jdbc.url" value="jdbc:mysql://localhost:3306/dashboard" />
            <property name="jakarta.persistence.jdbc.user" value="root" />
            <property name="jakarta.persistence.jdbc.password" value="********" />
            <property name="eclipselink.logging.level" value="FINEST" />            
        </properties>               
    </persistence-unit>
</persistence>

CodePudding user response：

By default you are running on an EE container, so it is using a JTA datasource.

If you really want to use RESOURCE_LOCAL, you should check this link on the TomEE documentation, i.e. you define a non-jta datasource in the container (via resources.xml or tomee.xml) and reference it via <non-jta-data-source> in the persistente.xml

You can find some information regarding the configuration of a datasource on the TomEE website.