More than one table found in namespace (, ) - SchemaExtractionException

Question

I have been facing this weird exception while trying to persist some values into a table using Hibernate in a Java application. However this exception occurs only for one particular table/entity for rest of the tables i am able to perform crud operations via Hibernate.

Please find below the Stacktrace and let me know if this is anyway related to java code is or its a database design error.

 2016-04-28 11:52:34 ERROR XXXXXDao:44 - Failed to create sessionFactory object.org.hibernate.tool.schema.extract.spi.SchemaExtractionException: More than one table found in namespace (, ) : YYYYYYY
Exception in thread "main" java.lang.ExceptionInInitializerError
    at com.XX.dao.XXXXXXXDao.main(XXXXXXXXDao.java:45)
Caused by: org.hibernate.tool.schema.extract.spi.SchemaExtractionException: More than one table found in namespace (, ) : YYYYYYY
    at org.hibernate.tool.schema.extract.internal.InformationExtractorJdbcDatabaseMetaDataImpl.processGetTableResults(InformationExtractorJdbcDatabaseMetaDataImpl.java:381)
    at org.hibernate.tool.schema.extract.internal.InformationExtractorJdbcDatabaseMetaDataImpl.getTable(InformationExtractorJdbcDatabaseMetaDataImpl.java:279)
    at org.hibernate.tool.schema.internal.exec.ImprovedDatabaseInformationImpl.getTableInformation(ImprovedDatabaseInformationImpl.java:109)
    at org.hibernate.tool.schema.internal.SchemaMigratorImpl.performMigration(SchemaMigratorImpl.java:252)
    at org.hibernate.tool.schema.internal.SchemaMigratorImpl.doMigration(SchemaMigratorImpl.java:137)
    at org.hibernate.tool.schema.internal.SchemaMigratorImpl.doMigration(SchemaMigratorImpl.java:110)
    at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.performDatabaseAction(SchemaManagementToolCoordinator.java:176)
    at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.process(SchemaManagementToolCoordinator.java:64)
    at org.hibernate.internal.SessionFactoryImpl.<init>(SessionFactoryImpl.java:458)
    at org.hibernate.boot.internal.SessionFactoryBuilderImpl.build(SessionFactoryBuilderImpl.java:465)
    at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:708)
    at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:724)
    at com.xx.dao.zzzzzzzzzzzzDAOFactory.configureSessionFactory(zzzzzzzDAOFactory.java:43)
    at com.xx.dao.zzzzzzzzzzzzDAOFactory.buildSessionFactory(zzzzzzzzzDAOFactory.java:27)
    at com.xx.dao.XXXXXXXXDao.main(XXXXXXXXDao.java:41)

Thanks in advance for your help

Solution 1

I have had the same problem and was able to dig down to the code to find out the cause, at least in my case. I don't know whether it will be the same issue for you but this may be helpful.

From your stack trace I can see you have the hibernate.hbm2ddl.auto set to upgrade the schema. As part of this, it is trying to look up the metadata for all the tables hibernate knows about and for one of them getting an ambiguous answer because the metadata query is returning more than a single row of table or view metadata.

In my case this was caused by our naming convention for tables. We had a table called (say) "AAA_BBB" for which this was going wrong. Now the use of an underscore in the table name is perfectly acceptable as far as I am aware and is quite common practice. However the underscore is also the SQL wildcard for a single character; looking in the code for the database metadata I can see it is doing a "WHERE table_name LIKE ..." in DatabaseMetaData.getTables(...) method, which is what hibernate is using here.

Now, in my schema I also had a second table called "AAA1BBB" and hence both of these matched the metadata lookup and so it returned a metadata row for each of these tables. The hibernate method is written to just fall down if the result set from the table metadata lookup returns more than one row. I would guess it should examine the available row(s) and find if there is one which is an exact match with the specified table name.

I tested this for both Oracle and MySQL with the same result.

Solution 2

Seems the property hibernate.hbm2ddl.auto set to update is causing the issue here. Try removing it from your hibernate config xml.

Solution 3

This will work:

Check your database schema/s and your database user privileges;

Hibernate update mechanism may fail with this exception if there is a another database schema/user with the same table name, and the db user has the sufficient privileges to view this table.

So in your case, the table 'YYYYYYY' may be found in more than one database user/schema, and your db user has 'DBA' privileges.

To solve this you can either find and delete the ambiguous table or remove the user's redundant privileges.

Solution 4

Another situation may be occurred except whatever dear RichB has been stated. in ORACLE every user has separate schema , Therefore probably there is tow tables with the same name in two different schemes then you should specify your default schema in persistence.xml with below property

<property name="hibernate.default_schema" value="username"/>

Solution 5

Use catalog value with @Table, i.e.:

@Entity
@Table(**catalog = "MY_DB_USER"**, name = "LOOKUP")
public class Lookup implements Serializable {

}

I don't have this error now. Hope this work.

View more solutions

Comments

[RichB]

I have raised this as an issue with Hibernate ORM hibernate.atlassian.net/browse/HHH-10718 and I have a proposed fix which I am testing and hope to be submitting.

[Anuj]

Thanks RichB, However i also noticed that in the database schema there is another user created where this same table was created. This might be the reason that hibernate is able to see two table in the namespace.

[Anuj]

Is there a way to restrict hibernate search to only one user of that schema and not all the users under it ? Currently the issue is resolved by removing that user since it was not required. [ I have tested this by replicating the another table with same name in different user and it is giving the same issue.] However going further a long term solution for this will be the best, where in hibernate we could restrict our object search to a particular user of the schema and not all its users

[Anuj]

It worked by removing hbm2ddl.auto property. Thanks a lot. However an explanation why it caused this issue will be great.

[Anuj]

It worked by removing hbm2ddl.auto property. Thanks a lot.

[raffian]

Prefer this solution over disabling hbm2ddl.auto, which may create unintended side effects.