[#CONJ-702] since 2.4.0 j-connector throws sequence errors via JPA/ eclipselink on @GeneratedValue(strategy = GenerationType.IDENTITY) columns

[CONJ-702] since 2.4.0 j-connector throws sequence errors via JPA/ eclipselink on @GeneratedValue(strategy = GenerationType.IDENTITY) columns Created: 2019-05-14 Updated: 2021-11-09
Status:	Open
Project:	MariaDB Connector/J
Component/s:	None
Affects Version/s:	None
Fix Version/s:	None

Type:

Bug

Priority:

Major

Reporter:

Korbinian Bachl

Assignee:

Diego Dupin

Resolution:

Unresolved

Votes:

Labels:

None

Environment:

payara 4 with eclipselink 2.6.2; mariadb 10.2.24 on ubuntu 18.04

Description

all fine till j connector 2.3.0 - 2.4.1 and 2.4.0 throws it!

on updating/ creating an jpa entity it happens that errors occur:

Internal Exception: java.sql.SQLSyntaxErrorException: (conn=105) Table 'databasename.SEQUENCE' doesn't exist
Error Code: 1146
Call: UPDATE SEQUENCE SET SEQ_COUNT = SEQ_COUNT + ? WHERE SEQ_NAME = ?
bind => [2 parameters bound]
Query: DataModifyQuery(name="SEQ_GEN_IDENTITY" sql="UPDATE SEQUENCE SET SEQ_COUNT = SEQ_COUNT + ? WHERE SEQ_NAME = ?")
at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:331)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeDirectNoSelect(DatabaseAccessor.java:902)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeNoSelect(DatabaseAccessor.java:964)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:633)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:560)
at org.eclipse.persistence.internal.sessions.AbstractSession.basicExecuteCall(AbstractSession.java:2056)
at org.eclipse.persistence.sessions.server.ClientSession.executeCall(ClientSession.java:306)
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:242)
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:228)
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeNoSelectCall(DatasourceCallQueryMechanism.java:271)
at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeNoSelect(DatasourceCallQueryMechanism.java:251)
at org.eclipse.persistence.queries.DataModifyQuery.executeDatabaseQuery(DataModifyQuery.java:85)
at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:904)
at org.eclipse.persistence.internal.sessions.AbstractSession.internalExecuteQuery(AbstractSession.java:3271)
at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1857)
at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1839)
at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1804)
at org.eclipse.persistence.sequencing.QuerySequence.update(QuerySequence.java:340)
at org.eclipse.persistence.sequencing.QuerySequence.updateAndSelectSequence(QuerySequence.java:277)
at org.eclipse.persistence.sequencing.StandardSequence.getGeneratedVector(StandardSequence.java:71)
at org.eclipse.persistence.sequencing.Sequence.getGeneratedVector(Sequence.java:257)
at org.eclipse.persistence.internal.sequencing.SequencingManager$Preallocation_Transaction_NoAccessor_State.getNextValue(SequencingManager.java:549)
at org.eclipse.persistence.internal.sequencing.SequencingManager.getNextValue(SequencingManager.java:1107)
at org.eclipse.persistence.internal.sequencing.ClientSessionSequencing.getNextValue(ClientSessionSequencing.java:70)
at org.eclipse.persistence.internal.descriptors.ObjectBuilder.assignSequenceNumber(ObjectBuilder.java:362)
at org.eclipse.persistence.internal.descriptors.ObjectBuilder.assignSequenceNumber(ObjectBuilder.java:321)
at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.assignSequenceNumber(UnitOfWorkImpl.java:486)
at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.registerNotRegisteredNewObjectForPersist(UnitOfWorkImpl.java:4288)
at org.eclipse.persistence.internal.sessions.RepeatableWriteUnitOfWork.registerNotRegisteredNewObjectForPersist(RepeatableWriteUnitOfWork.java:521)
at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.registerNewObjectForPersist(UnitOfWorkImpl.java:4233)
at org.eclipse.persistence.internal.jpa.EntityManagerImpl.persist(EntityManagerImpl.java:507)
at com.sun.enterprise.container.common.impl.EntityManagerWrapper.persist(EntityManagerWrapper.java:287)
at de.whiskyworld.logic.ServiceBean.create(ServiceBean.java:52)
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.glassfish.ejb.security.application.EJBSecurityManager.runMethod(EJBSecurityManager.java:1081)
at org.glassfish.ejb.security.application.EJBSecurityManager.invoke(EJBSecurityManager.java:1153)
at com.sun.ejb.containers.BaseContainer.invokeBeanMethod(BaseContainer.java:4836)
at com.sun.ejb.EjbInvocation.invokeBeanMethod(EjbInvocation.java:656)
at com.sun.ejb.containers.interceptors.AroundInvokeChainImpl.invokeNext(InterceptorManager.java:836)
at com.sun.ejb.EjbInvocation.proceed(EjbInvocation.java:608)
at org.jboss.weld.ejb.AbstractEJBRequestScopeActivationInterceptor.aroundInvoke(AbstractEJBRequestScopeActivationInterceptor.java:64)
at org.jboss.weld.ejb.SessionBeanInterceptor.aroundInvoke(SessionBeanInterceptor.java:52)
at sun.reflect.GeneratedMethodAccessor100.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at com.sun.ejb.containers.interceptors.AroundInvokeInterceptor.intercept(InterceptorManager.java:895)
at com.sun.ejb.containers.interceptors.AroundInvokeChainImpl.invokeNext(InterceptorManager.java:835)
at com.sun.ejb.EjbInvocation.proceed(EjbInvocation.java:608)
at com.sun.ejb.containers.interceptors.SystemInterceptorProxy.doCall(SystemInterceptorProxy.java:163)
at com.sun.ejb.containers.interceptors.SystemInterceptorProxy.aroundInvoke(SystemInterceptorProxy.java:140)
at sun.reflect.GeneratedMethodAccessor101.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at com.sun.ejb.containers.interceptors.AroundInvokeInterceptor.intercept(InterceptorManager.java:895)
at com.sun.ejb.containers.int]]

[2019-05-14T10:08:55.422+0200] [Payara 4.1] [INFORMATION] [] [] [tid: _ThreadID=37 _ThreadName=http-thread-pool::http-listener-2(1)] [timeMillis: 1557821335422] [levelValue: 800] [[
erceptors.AroundInvokeChainImpl.invokeNext(InterceptorManager.java:835)
at com.sun.ejb.containers.interceptors.InterceptorManager.intercept(InterceptorManager.java:374)
at com.sun.ejb.containers.BaseContainer.__intercept(BaseContainer.java:4808)
at com.sun.ejb.containers.BaseContainer.intercept(BaseContainer.java:4796)
at com.sun.ejb.containers.EJBLocalObjectInvocationHandler.invoke(EJBLocalObjectInvocationHandler.java:212)
... 99 more
Caused by: java.sql.SQLSyntaxErrorException: (conn=105) Table 'databasename.SEQUENCE' doesn't exist
at org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.get(ExceptionMapper.java:236)
at org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.getException(ExceptionMapper.java:165)
at org.mariadb.jdbc.MariaDbStatement.executeExceptionEpilogue(MariaDbStatement.java:238)
at org.mariadb.jdbc.ClientSidePreparedStatement.executeInternal(ClientSidePreparedStatement.java:230)
at org.mariadb.jdbc.ClientSidePreparedStatement.execute(ClientSidePreparedStatement.java:157)
at org.mariadb.jdbc.ClientSidePreparedStatement.executeUpdate(ClientSidePreparedStatement.java:192)
at com.sun.gjc.spi.base.PreparedStatementWrapper.executeUpdate(PreparedStatementWrapper.java:125)
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 com.sun.gjc.spi.jdbc40.ProfiledConnectionWrapper40$1.invoke(ProfiledConnectionWrapper40.java:437)
at com.sun.proxy.$Proxy408.executeUpdate(Unknown Source)
at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeDirectNoSelect(DatabaseAccessor.java:892)
... 159 more
Caused by: java.sql.SQLException: Table 'databasename.SEQUENCE' doesn't exist
at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.readErrorPacket(AbstractQueryProtocol.java:1594)
at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.readPacket(AbstractQueryProtocol.java:1453)
at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.getResult(AbstractQueryProtocol.java:1415)
at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.executeQuery(AbstractQueryProtocol.java:288)
at org.mariadb.jdbc.ClientSidePreparedStatement.executeInternal(ClientSidePreparedStatement.java:221)
... 169 more
]]

Comments

Comment by Diego Dupin [ 2019-05-15 ]

It must be related to https://jira.mariadb.org/browse/CONJ-654.
Can you enable the option "useMysqlMetadata" to confirm that ?

_checking eclipselink source to confirm by my side _

Comment by Korbinian Bachl [ 2019-05-15 ]

ok, I added

"useMysqlMetadata" "true" into the name + value of the JDBC connection pool properties, next to user, port, password etc.
put 2.4.1 into the lib of the payara
restarted it (the whole server to make sure) and still get the same error as before - he wants to write into a databasename.SEQUENCE table that of course doesnt exist...

Caused by: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.6.4.qualifier): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLSyntaxErrorException: (conn=43) Table 'databasename.SEQUENCE' doesn't exist

PS: reverted to connector 2.3.0 - all working fine....

Comment by Gonzalo García [ 2019-08-06 ]

Hello,

I have the same problem, just to complement, when I create the EntityManagerFactory (EMF) manually (without any application server / container) it works fine.
If I use Tomcat and add the connection to DB as a resource in order to use DBCP, when I create the EMF, I got the problem mentioned in this issue.
Conector version 2.3.x works fine, the problem happens with 2.4.x only.

Comment by Sebastian Knop [ 2019-08-23 ]

Since MySQL doesn't support sequences, using useMysqlMetadata causes Eclipselink/JPA to not generate sequences. And Eclipselink doesn't explicitly support MariaDB, so using the connector without useMysqlMetadata causes Eclipselink to generate SQL for sequences that's invalid for MariaDB (Data type NUMBER and VARCHAR without length).

Comment by Mario Simaremare [ 2020-04-22 ]

This bug still appears in version 2.6.0 stable release.
Downgrading to 2.3.0 and it works.

Comment by Tomasz [ 2020-04-24 ]

Very annoying bug affecting basic database functionality.
Must downgrade.

Comment by Jean-Claude Stritt [ 2020-05-12 ]

Same problem in may 2020. I have downgrade from 2.5.2 to 2.3.0. Please correct this.

Comment by Kim Gabrielsen [ 2021-02-04 ]

Problem is still here in feb 2021 and v 2.7.1. I have to go back to 2.3 like everybody else.

Comment by Jan Vonde [ 2021-08-13 ]

August 2021 and v2.7.4. Same problem, reverting to 2.3.0 and everything is working again...

Comment by Diego Dupin [ 2021-11-08 ]

DatabaseMetaData.getDatabaseProductName() returns 'MariaDB' if server is MariaDB. That's not a bug, it's the expected behavior.

eclipselink doesn't support explicitily MariaDB, ok, but there is no need to revert to a previous version, only to enable useMysqlMetadata option, like setting connection string to something like :

jdbc:mariadb://host/db?useMysqlMetadata=true

Connector will then return 'MySQL' event for MariaDB database, permitting eclipselink to works ok.

Comment by Korbinian Bachl [ 2021-11-09 ]

@Diego Dupin

So your solution to this after over 2 years is to add the property "useMysqlMetadata" with value "true" to the additional properties of the JDBC Connection Pool Properties for any server using eclipselink (like payara and glassfish) ?

Let me give you 1 short hint: I did this in 2019 and I also did this just now with a fresh payara 2021.6 + mariadb-java-client-2.7.4.jar in /domain/lib and the mentioned useMysqlMetadata=true and it still fails:

Internal Exception: java.sql.SQLSyntaxErrorException: (conn=39) Table 'shopbdb.SEQUENCE' doesn't exist
Error Code: 1146
Call: UPDATE SEQUENCE SET SEQ_COUNT = SEQ_COUNT + ? WHERE SEQ_NAME = ?
bind => [2 parameters bound]
Query: DataModifyQuery(name="SEQ_GEN_IDENTITY" sql="UPDATE SEQUENCE SET SEQ_COUNT = SEQ_COUNT + ? WHERE SEQ_NAME = ?")
at com.sun.ejb.containers.EJBContainerTransactionManager.processSystemException(EJBContainerTransactionManager.java:723)
at com.sun.ejb.containers.EJBContainerTransactionManager.completeNewTx(EJBContainerTransactionManager.java:652)....

Caused by: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.7.7.payara-p3): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLSyntaxErrorException: (conn=39) Table 'shopbdb.SEQUENCE' doesn't exist
Error Code: 1146
Call: UPDATE SEQUENCE SET SEQ_COUNT = SEQ_COUNT + ? WHERE SEQ_NAME = ?
bind => [2 parameters bound]
Query: DataModifyQuery(name="SEQ_GEN_IDENTITY" sql="UPDATE SEQUENCE SET SEQ_COUNT = SEQ_COUNT + ? WHERE SEQ_NAME = ?")
at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:333)

Caused by: java.sql.SQLSyntaxErrorException: (conn=39) Table 'shopbdb.SEQUENCE' doesn't exist
at org.mariadb.jdbc.internal.util.exceptions.ExceptionFactory.createException(ExceptionFactory.java:62)
at org.mariadb.jdbc.internal.util.exceptions.ExceptionFactory.create(ExceptionFactory.java:158)

Caused by: org.mariadb.jdbc.internal.util.exceptions.MariaDbSqlException: Table 'shopbdb.SEQUENCE' doesn't exist
at org.mariadb.jdbc.internal.util.exceptions.MariaDbSqlException.of(MariaDbSqlException.java:34)

Caused by: java.sql.SQLException: Table 'shopbdb.SEQUENCE' doesn't exist
at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.readErrorPacket(AbstractQueryProtocol.java:1694)

So?

PS: version of eclipslink:

Comment by Korbinian Bachl [ 2021-11-09 ]

Follow up:

after digging deeper into eclipselink and maridb connector it seems that by using JTA via data source the parameter of the connection itself doesnt get respected from eclipselink. Furthermore https://github.com/eclipse-ee4j/eclipselink/issues/883 is still pointing to future.

However, you can add an property to your persistence.xml to have eclipselink ignore all it gets and instead force it to talk "mysql" dialect ignoring everything else:

this way connector 2.4.+ will work!

Generated at Thu Feb 08 03:17:41 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.

[CONJ-702] since 2.4.0 j-connector throws sequence errors via JPA/ eclipselink on @GeneratedValue(strategy = GenerationType.IDENTITY) columns Created: 2019-05-14 Updated: 2021-11-09