Table annotation is not taking into account schema

Description

In our working project,as part of upgrade from hibernate 3 to hibernate 5, I am getting error "SQLSyntaxErrorException: Unknown column <columnName> in field list".
Digging more into the issue found table annotation is not taking into account the schema name.
Tried a standalone test case to confirm the issue.

Let me explain about the scenario. There are 2 schemas, say schema1 and schema2.
schema1 is populated by another aplication/process, schema2 is dependednt schema managed by our application.
So schema2 is default schema and schema1 is main(used in table annotation) schema.

Hibernate version used: 5.1.15. Discussed this issue in hibernate forum
https://discourse.hibernate.org/t/sqlsyntaxerrorexception-unknown-column-columnname-in-field-list/1448
When i use even most simple criteria like below, i get this error
List<Cluster> clustersList = session.createCriteria(Cluster.class).list();
Tried a standalone test case to confirm the issue (getting empty list).
hhh123Test test in org.hibernate.bugs.ORMUnitTestCase class is using criteria to fetch values.
Attached image of schema/table, entity definition and error trace for more details.

2018-10-25 15:46:53 INFO pooling:235 - HHH10001008: Cleaning up connection pool [jdbc:mysql://localhost:3306/schema2]
Tests run: 1, Failures: 0, Errors: 1, Skipped: 0, Time elapsed: 1.241 sec <<< FAILURE!
hhh123Test(org.hibernate.bugs.ORMUnitTestCase) Time elapsed: 0.689 sec <<< ERROR!
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 org.hibernate.bugs.ORMUnitTestCase.hhh123Test(ORMUnitTestCase.java:91)
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.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
at org.hibernate.testing.junit4.ExtendedFrameworkMethod.invokeExplosively(ExtendedFrameworkMethod.java:45)
at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:27)
at org.junit.internal.runners.statements.FailOnTimeout$CallableStatement.call(FailOnTimeout.java:298)
at org.junit.internal.runners.statements.FailOnTimeout$CallableStatement.call(FailOnTimeout.java:292)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at java.lang.Thread.run(Thread.java:748)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'this_.objid' in 'field list'
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
at com.mysql.jdbc.Util.getInstance(Util.java:408)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:944)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3976)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3912)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2530)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2683)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2486)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1858)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1966)
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:70)
... 28 more

Results :

Tests in error:
hhh123Test(org.hibernate.bugs.ORMUnitTestCase): could not extract ResultSet

Tests run: 3, Failures: 0, Errors: 1, Skipped: 0

Environment

None

Activity

Show:
Vlad Mihalcea
November 7, 2018, 7:57 PM

Please don't submit duplicates of issues that we already closed. The mapping for this issue is the same as the one you used for HHH-12423.

So, in the Hibernate ORM project, you can find the SecondaryTableSchemaTest which works perfectly and you can see that for yourself.

Now, if it does not work for you, it's surely because of a faulty database configuration. I noticed you used schemas, while MySQL uses catalogs.

Assignee

Unassigned

Reporter

sandeep kamath

Fix versions

None

Labels

None

backPortable

None

Suitable for new contributors

None

Requires Release Note

None

Pull Request

None

backportDecision

None

Components

Affects versions

Priority

Major
Configure