SQLSyntaxErrorException: Unknown column <columnName> in field list


#1

During Hibernate upgrade from 3 to 5.1.15 i am getting below error(Full error stack trace added).
Entity definition is mentioned below.

@ org.hibernate.annotations.Entity(optimisticLock = OptimisticLockType.DIRTY, dynamicUpdate = true)
The above line was replaced by below line as part of upgrade

@ OptimisticLocking(type = OptimisticLockType.DIRTY)
@ DynamicUpdate

I have ensured the following

  1. Schema exists
  2. Table exists
  3. Column name is proper and we get it via inheritance(Resource which is super class of all entities like cluster)
@ HistoryCollected(idColumn = "clusterId")
@ GenerateMutator
@ Table(name = "cluster", schema = "upperLevelSchema")
@ SecondaryTable(name = "Cluster", pkJoinColumns = { @PrimaryKeyJoinColumn(name = "id") })
@ org.hibernate.annotations.Table(appliesTo = "Cluster", optional = false)
@ Entity
@ OptimisticLocking(type = OptimisticLockType.DIRTY)
@ DynamicUpdate
@ Tuplizer(impl = DynamicEntityTuplizer.class)
public class Cluster extends Resource
2018-09-25 15:45:53,368 ERROR [org.jboss.msc.service.fail] (ServerService Thread Pool -- 147) MSC000001: Failed to start service jboss.undertow.deployment.default-server.default-host./: org.jboss.msc.service.StartException in service jboss.undertow.deployment.default-server.default-host./: java.lang.RuntimeException: org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet
	at org.wildfly.extension.undertow.deployment.UndertowDeploymentService$1.run(UndertowDeploymentService.java:81) [wildfly-undertow-12.0.0.Final.jar:12.0.0.Final]
	at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) [rt.jar:1.8.0_162]
	at java.util.concurrent.FutureTask.run(FutureTask.java:266) [rt.jar:1.8.0_162]
	at org.jboss.threads.ContextClassLoaderSavingRunnable.run(ContextClassLoaderSavingRunnable.java:35) [jboss-threads-2.3.1.Final.jar:2.3.1.Final]
	at org.jboss.threads.EnhancedQueueExecutor.safeRun(EnhancedQueueExecutor.java:1985) [jboss-threads-2.3.1.Final.jar:2.3.1.Final]
	at org.jboss.threads.EnhancedQueueExecutor$ThreadBody.doRunTask(EnhancedQueueExecutor.java:1487) [jboss-threads-2.3.1.Final.jar:2.3.1.Final]
	at org.jboss.threads.EnhancedQueueExecutor$ThreadBody.run(EnhancedQueueExecutor.java:1378) [jboss-threads-2.3.1.Final.jar:2.3.1.Final]
	at java.lang.Thread.run(Thread.java:748) [rt.jar:1.8.0_162]
	at org.jboss.threads.JBossThread.run(JBossThread.java:485) [jboss-threads-2.3.1.Final.jar:2.3.1.Final]
Caused by: java.lang.RuntimeException: org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet
	at io.undertow.servlet.core.DeploymentManagerImpl.deploy(DeploymentManagerImpl.java:251) [undertow-servlet-2.0.0.Final.jar:2.0.0.Final]
	at org.wildfly.extension.undertow.deployment.UndertowDeploymentService.startContext(UndertowDeploymentService.java:96) [wildfly-undertow-12.0.0.Final.jar:12.0.0.Final]
	at org.wildfly.extension.undertow.deployment.UndertowDeploymentService$1.run(UndertowDeploymentService.java:78) [wildfly-undertow-12.0.0.Final.jar:12.0.0.Final]
	... 8 more
Caused by: 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.hibernate5.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:224)
	at org.springframework.orm.hibernate5.HibernateExceptionTranslator.convertHibernateAccessException(HibernateExceptionTranslator.java:68)
	at org.springframework.orm.hibernate5.HibernateExceptionTranslator.translateExceptionIfPossible(HibernateExceptionTranslator.java:49)
	at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:59)
	at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:213)
	at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:147)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
	at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
	at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:655)
	at com.myCompany.product.entity.inventory.ontap.fas.ClusterEntityManager$$EnhancerBySpringCGLIB$$bcb0960d.getClusterList(<generated>)
	at com.myCompany.product.impl.ems.ClusterInventoryChangeListener.checkEMSConfigForClusters(ClusterInventoryChangeListener.java:58)
	at com.myCompany.product.impl.ems.ClusterInventoryChangeListener$$FastClassBySpringCGLIB$$f4d39117.invoke(<generated>)
	at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
	at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:651)
	at com.myCompany.product.impl.ems.ClusterInventoryChangeListener$$EnhancerBySpringCGLIB$$1fa19b4d.checkEMSConfigForClusters(<generated>)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) [rt.jar:1.8.0_162]
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) [rt.jar:1.8.0_162]
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.8.0_162]
	at java.lang.reflect.Method.invoke(Method.java:498) [rt.jar:1.8.0_162]
	at com.myCompany.product.app.common.lifecycle.OnStartupMethodInvoker$1.doWith(OnStartupMethodInvoker.java:52)
	at org.springframework.util.ReflectionUtils.doWithMethods(ReflectionUtils.java:530)
	at org.springframework.util.ReflectionUtils.doWithMethods(ReflectionUtils.java:537)
	at com.myCompany.product.app.common.lifecycle.OnStartupMethodInvoker.onApplicationEvent(OnStartupMethodInvoker.java:47)
	at com.myCompany.product.app.common.lifecycle.OnStartupMethodInvoker.onApplicationEvent(OnStartupMethodInvoker.java:27)
	at org.springframework.context.event.SimpleApplicationEventMulticaster.invokeListener(SimpleApplicationEventMulticaster.java:166)
	at org.springframework.context.event.SimpleApplicationEventMulticaster.multicastEvent(SimpleApplicationEventMulticaster.java:138)
	at org.springframework.context.support.AbstractApplicationContext.publishEvent(AbstractApplicationContext.java:383)
	at org.springframework.context.support.AbstractApplicationContext.publishEvent(AbstractApplicationContext.java:337)
	at org.springframework.context.support.AbstractApplicationContext.start(AbstractApplicationContext.java:1307)
	at com.myCompany.product.app.common.spring.ProductContextLoaderListener.initWebApplicationContext(ProductContextLoaderListener.java:36)
	at org.springframework.web.context.ContextLoaderListener.contextInitialized(ContextLoaderListener.java:107)
	at io.undertow.servlet.core.ApplicationListeners.contextInitialized(ApplicationListeners.java:187) [undertow-servlet-2.0.0.Final.jar:2.0.0.Final]
	at io.undertow.servlet.core.DeploymentManagerImpl$1.call(DeploymentManagerImpl.java:215) [undertow-servlet-2.0.0.Final.jar:2.0.0.Final]
	at io.undertow.servlet.core.DeploymentManagerImpl$1.call(DeploymentManagerImpl.java:184) [undertow-servlet-2.0.0.Final.jar:2.0.0.Final]
	at io.undertow.servlet.core.ServletRequestContextThreadSetupAction$1.call(ServletRequestContextThreadSetupAction.java:42) [undertow-servlet-2.0.0.Final.jar:2.0.0.Final]
	at io.undertow.servlet.core.ContextClassLoaderSetupAction$1.call(ContextClassLoaderSetupAction.java:43) [undertow-servlet-2.0.0.Final.jar:2.0.0.Final]
	at org.wildfly.extension.undertow.security.SecurityContextThreadSetupAction.lambda$create$0(SecurityContextThreadSetupAction.java:105) [wildfly-undertow-12.0.0.Final.jar:12.0.0.Final]
	at org.wildfly.extension.undertow.deployment.UndertowDeploymentInfoService$UndertowThreadSetupAction.lambda$create$0(UndertowDeploymentInfoService.java:1526) [wildfly-undertow-12.0.0.Final.jar:12.0.0.Final]
	at org.wildfly.extension.undertow.deployment.UndertowDeploymentInfoService$UndertowThreadSetupAction.lambda$create$0(UndertowDeploymentInfoService.java:1526) [wildfly-undertow-12.0.0.Final.jar:12.0.0.Final]
	at org.wildfly.extension.undertow.deployment.UndertowDeploymentInfoService$UndertowThreadSetupAction.lambda$create$0(UndertowDeploymentInfoService.java:1526) [wildfly-undertow-12.0.0.Final.jar:12.0.0.Final]
	at org.wildfly.extension.undertow.deployment.UndertowDeploymentInfoService$UndertowThreadSetupAction.lambda$create$0(UndertowDeploymentInfoService.java:1526) [wildfly-undertow-12.0.0.Final.jar:12.0.0.Final]
	at io.undertow.servlet.core.DeploymentManagerImpl.deploy(DeploymentManagerImpl.java:249) [undertow-servlet-2.0.0.Final.jar:2.0.0.Final]
	... 10 more
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:111)
	at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:97)
	at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:79)
	at org.hibernate.loader.Loader.getResultSet(Loader.java:2122)
	at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1905)
	at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1881)
	at org.hibernate.loader.Loader.doQuery(Loader.java:925)
	at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:342)
	at org.hibernate.loader.Loader.doList(Loader.java:2622)
	at org.hibernate.loader.Loader.doList(Loader.java:2605)
	at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2434)
	at org.hibernate.loader.Loader.list(Loader.java:2429)
	at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:109)
	at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1787)
	at org.hibernate.internal.CriteriaImpl.list(CriteriaImpl.java:363)
	at com.myCompany.product.entity.inventory.ontap.fas.ClusterEntityManager.getClusterList(ClusterEntityManager.java:151)
	at com.myCompany.product.entity.inventory.ontap.fas.ClusterEntityManager$$FastClassBySpringCGLIB$$c22c3cad.invoke(<generated>)
	at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:720)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
	at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
	at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:282)
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
	at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:136)
	... 47 more
Caused by: java.sql.SQLSyntaxErrorException: Unknown column 'this_.objid' in 'field list'
	at org.mariadb.jdbc.internal.SQLExceptionMapper.get(SQLExceptionMapper.java:138)
	at org.mariadb.jdbc.internal.SQLExceptionMapper.throwException(SQLExceptionMapper.java:106)
	at org.mariadb.jdbc.MySQLStatement.executeQueryEpilog(MySQLStatement.java:252)
	at org.mariadb.jdbc.MySQLStatement.execute(MySQLStatement.java:278)
	at org.mariadb.jdbc.MySQLStatement.executeQuery(MySQLStatement.java:333)
	at org.mariadb.jdbc.MySQLPreparedStatement.executeQuery(MySQLPreparedStatement.java:104)
	at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
	at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96)
	at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:70)
	... 69 more
Caused by: org.mariadb.jdbc.internal.common.QueryException: Unknown column 'this_.objid' in 'field list'
	at org.mariadb.jdbc.internal.mysql.MySQLProtocol.getResult(MySQLProtocol.java:983)
	at org.mariadb.jdbc.internal.mysql.MySQLProtocol.executeQuery(MySQLProtocol.java:1037)
	at org.mariadb.jdbc.internal.mysql.MySQLProtocol.executeQuery(MySQLProtocol.java:1019)
	at org.mariadb.jdbc.MySQLStatement.execute(MySQLStatement.java:271)
	... 74 more

2018-09-25 15:45:53,416 ERROR [org.jboss.as.controller.management-operation] (Controller Boot Thread) WFLYCTL0013: Operation ("deploy") failed - address: ([("deployment" => "product-app.war")]) - failure description: {"WFLYCTL0080: Failed services" => {"jboss.undertow.deployment.default-server.default-host./" => "java.lang.RuntimeException: org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet
    Caused by: java.lang.RuntimeException: org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet
    Caused by: org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet
    Caused by: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
    Caused by: java.sql.SQLSyntaxErrorException: Unknown column 'this_.objid' in 'field list'
    Caused by: org.mariadb.jdbc.internal.common.QueryException: Unknown column 'this_.objid' in 'field list'"}}
2018-09-25 15:45:53,622 ERROR [org.jboss.as] (Controller Boot Thread) WFLYSRV0026: WildFly Full 12.0.0.Final (WildFly Core 4.0.0.Final) started (with errors) in 94915ms - Started 13255 of 13533 services (2 services failed or missing dependencies, 475 services are lazy, passive or on-demand)

#2

Try to replicate with this test case, and if you can, open a Jira issue.


#3

Tried a simple testcase for Table and SecondaryTable, it is working fine with no such error.

What are possible causes for this issue ?
How can i try to figure out what could be the issue ?


#4

If you used the master branch, it means there might have been an issue which got fixed.

Try upgrading from 5.1.15 to 5.3.6.


#5

Agree, this is our final target(move to latest hibernate), infinispan-hibernate-cashe also gets changed with this.
As phase 1 of the upgrade, we are aiming for 5.1 series.

Just want to know how to debug the issue, what could be the issues, where to look into.
Can this be linked to EventListenerRegistry not registered properly ? Doesn’t seem like but wanted to confirm.

Any such pointer will be helpful.


#6

You said it’s working with the simple test case, right? Do a debug and compare why the test case works and your application does not.


#7

Entity definition:
@ HistoryCollected(idColumn = “clusterId”)
@ GenerateMutator
@ Table(name = “cluster”, schema = “schema1”)
@ SecondaryTable(name = “Cluster”, pkJoinColumns = { @PrimaryKeyJoinColumn(name = “id”) })
@ org.hibernate.annotations.Table(appliesTo = “Cluster”, optional = false)
@ Entity
@ OptimisticLocking(type = OptimisticLockType.DIRTY)
@ DynamicUpdate
@ Tuplizer(impl = DynamicEntityTuplizer.class)
public class Cluster

Criteria used:
@ Transactional(readOnly = true)
public List getClusterList() {
return session.createCriteria(Cluster.class).list();
}

I debugged it in executeQueryStatement() in org.hibernate.loader.Loader
HQL generated:
select *** from cluster this_ inner join Cluster this_1_ on this_.objid=this_1_.id;
I was expecting select * from schema1.cluster this_ inner join Cluster this_1_ on this_.objid=this_1_.id;
How is schema used in this case ?
I am getting the following error
SQLException: Unknown column ‘this_.objid’ in ‘field list’


#8

Try to replicate it with this test case, so we can better investigate it.


#9

@ Table(name = “cluster”, schema = “schema1”)
@ SecondaryTable(name = “Cluster”, schema = “schema2”, pkJoinColumns = @ PrimaryKeyJoinColumn(name = “clusterId”, referencedColumnName = “objid”))
@ org.hibernate.annotations.Table(appliesTo = “Cluster”, optional = false)
@ Entity
@ OptimisticLocking(type = OptimisticLockType.DIRTY)
@ DynamicUpdate
public class Cluster implements Serializable {

PrimaryAndSecondaryTables

My expectation was schema name would come for primary table as well as secondary table.
Schema name is not taken into consideration from table annotation.
Can you please let me know if my expectation is proper ?

Earlier similar issue was seen for Secondary table.

I am able to replicate it with test case. Do you feel it is burt ?


#10

If have a replicating test case, open a Jira issue and attach the test case.


#11

I have raised a jira on this.
https://hibernate.atlassian.net/browse/HHH-13032