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


#12

Jira is untouched. i don’t see any updates on that jira.
How to check if the jira is accepted and what is the time line for the fix ?


#13

In the true spirit of open-source software development, you should send us a Pull Request with the fix. Looking forward to reviewing it.


#14

This is an interesting issue becoming a blocker for us too. Any earlier resolution to this is really appreciable.


#15

If it’s a blocker issue for you, then you should investigate it and send a Pull Request with the fix.

Pull Requests have a higher priority than issues without a Pull Request.


#16

I took a look on your test case, and found the following problems.

First, you are using MariaDB which doe snot support schemas:

settings.put(Environment.DRIVER, "org.mariadb.jdbc.Driver");
settings.put(Environment.URL, "jdbc:mysql://localhost:3306/schema1");

MariaDB, just like MySQL, supports catalogs.

But, your mapping looks like this:

@Table(name = "cluster", schema = "schema1")
@SecondaryTable(name = "Cluster", schema = "schema2", pkJoinColumns = @PrimaryKeyJoinColumn(name = "clusterId"))

Try with the catalog instead:

@Table(name = "cluster", catalog = "schema1")
//@SecondaryTable(name = "Cluster", pkJoinColumns = @PrimaryKeyJoinColumn(name = "clusterId", referencedColumnName = "objid"))
@SecondaryTable(name = "Cluster", catalog = "schema2", pkJoinColumns = @PrimaryKeyJoinColumn(name = "clusterId"))

#17

@sandeep_kamath Just like HHH-12423, I closed this issue since it works just fine. Just check the SecondaryTableSchemaTest on the Hibernate ORM project and see that it runs just fine.

Please don’t open the third issue for the same mapping and tell that it does not work as long as this test case proves that the mapping works just fine. If you think that’s not the case, you need to prove it with a Pull Request for this test case.

Otherwise, pay attention to your mappings, like acknowledging that MariaDB does not use schemas since it only supports catalogs.


#18

Thanks a lot Vlad for trying the test case.
I think there is some misunderstanding about this case.
Here, I was talking about Table schema and not secondary table schema.
Also the attached image showing query was explicitly using schema name for secondary thable but not for table schema.
That made me think issue could be with Table annotation as secondarytable was using schema name.

Let me explain the case we have there are 2 things.
Default database and main database.
schema1 is populated by another application which is our default database(Used in Table schema).
schema2 is managed by our application and is our main database(Used in Secondary Table schema).

Regarding using mariadb:
In hibernate 3, we were using schema attribute and it was working fine.
I did not find anything in the docs specifying removal of support for mariadb in schema attribute.
http://docs.jboss.org/hibernate/orm/4.3/javadocs/
https://docs.jboss.org/hibernate/orm/5.1/javadocs/

Thank you for pointing it out.
Does that mean, support for schema attribute is removed in 4 or 5 for mariadb?
In case of mariadb, should we use catalog attribute in all places instead of schema ? like join column

I will try out this for mysql and mariadb and let you know the results soon.


#19

MariaDB does not support sequences, hence what exactly should the schema attribute be used by Hibernate in this case? Again, you should use the catalog attribute instead.

I did not find anything in the docs specifying removal of support for mariadb in schema attribute.

But the MariaDB documentation explains that a SCHEMA is practically a CATALOG. However, it does not support the CATALOG -> SCHEMA -> STRUCTURE.

Default database and main database.
schema1 is populated by another application which is our default database(Used in Table schema).
schema2 is managed by our application and is our main database(Used in Secondary Table schema).

Again, you are not creating schemas, but catalogs or databases.


#20

Thank you vlad. It worked.

using catalog instead of schema, worked. I am able to proceed.
I feel, documenting this would be better.