oracle 在命名空间 (, ) 中找到多个表 - SchemaExtractionException

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

提问by Anuj

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.

我在 Java 应用程序中尝试使用 Hibernate 将一些值持久化到表中时遇到了这个奇怪的异常。但是，此异常仅发生在一个特定表/实体的其余表中，我能够通过 Hibernate 执行 crud 操作。

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

请在 Stacktrace 下方找到并让我知道这是否与 Java 代码相关，或者是数据库设计错误。

 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

在此先感谢您的帮助

采纳答案by RichB

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.

从您的堆栈跟踪中，我可以看到您已将 hibernate.hbm2ddl.auto 设置为升级架构。作为其中的一部分，它试图查找 hibernate 知道的所有表的元数据，并为其中一个得到不明确的答案，因为元数据查询返回的表或视图元数据不止一行。

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.

在我的情况下，这是由我们的表命名约定引起的。我们有一个名为（比如）“AAA_BBB”的表，它出错了。现在，就我所知，在表名中使用下划线是完全可以接受的，并且是非常普遍的做法。然而，下划线也是单个字符的 SQL 通配符；查看数据库元数据的代码，我可以看到它在 DatabaseMetaData.getTables(...) 方法中执行“WHERE table_name LIKE ...”，这就是 hibernate 在这里使用的方法。

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.

现在，在我的模式中，我还有一个名为“AAA1BBB”的表，因此这两个表都与元数据查找相匹配，因此它为每个表返回了一个元数据行。如果表元数据查找的结果集返回多于一行，则休眠方法被编写为仅下降。我猜它应该检查可用的行并找出是否有一个与指定的表名完全匹配的行。

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

我对 Oracle 和 MySQL 进行了测试，结果相同。

回答by ashwin

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

似乎设置为更新的属性 hibernate.hbm2ddl.auto 导致了这里的问题。尝试从您的休眠配置 xml 中删除它。

回答by Naor Bar

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.

如果存在另一个具有相同表名的数据库模式/用户，并且 db 用户具有查看此表的足够权限，则 Hibernate 更新机制可能会因此异常而失败。

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

因此，在您的情况下，可能会在多个数据库用户/模式中找到“YYYYYYYY”表，并且您的 db 用户具有“DBA”权限。

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

要解决此问题，您可以查找并删除不明确的表或删除用户的冗余权限。

回答by Abbas Tofighi

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

除了亲爱的 RichB 所说的以外，可能会发生另一种情况。在 ORACLE 中，每个用户都有单独的架构，因此可能在两个不同的架构中有两个同名的表，那么您应该在 persistence.xml 中使用以下属性指定默认架构

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

回答by Mohammad Badiuzzaman

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

将目录值与 一起使用@Table，即：

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

}

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

我现在没有这个错误。希望这项工作。

回答by gene b.

We had a Spring Data / JPA application and this error started happening after upgrading to Postgres 10.6(from 10).

我们有一个 Spring Data/JPA 应用程序，在升级到 Postgres 10.6（从 10开始）后开始发生此错误。

Our solution was as follows, in our JPA configuration class: note the new commented line,

我们的解决方案如下，在我们的 JPA 配置类中：注意新的注释行，

props.put("hibernate.hbm2ddl.auto", "none"); //POSTGRES 10 --> 10.6 migration

props.put("hibernate.hbm2ddl.auto", "none"); //POSTGRES 10 --> 10.6 migration

Class:

班级：

@Configuration
@EnableJpaRepositories(basePackages = "app.dao")
@ComponentScan(basePackages = { "app.service" })
@EnableTransactionManagement
public class JpaConfig {

    @Autowired
    DataSource dataSource;

    @Bean
    public Map<String, Object> jpaProperties() {
        Map<String, Object> props = new HashMap<String, Object>();
        props.put("hibernate.dialect", PostgreSQL95Dialect.class.getName());
        props.put("hibernate.hbm2ddl.auto", "none"); //POSTGRES 10 --> 10.6 migration.
        return props;
    }

回答by Wen Cheng

I also came across this issue. Here is my solution:

我也遇到过这个问题。这是我的解决方案：

the error: https://gist.github.com/wencheng1994

错误：https: //gist.github.com/wencheng1994

I solve that. It mainly because the db account has a higher authority. I set the "hibernate.hbm2ddl.auto=update", So when hbm2ddl works, it tried to find all exists shcema I defied. But there is two schema exist the table with the same name. then the db account can find that. so it found "more than one table in the namespace"

我解决那个。主要是因为db账户的权限更高。我设置了“hibernate.hbm2ddl.auto=update”，所以当 hbm2ddl 工作时，它试图找到所有存在的 shcema 我违抗。但是有两个模式存在同名的表。然后数据库帐户可以找到。所以它发现“命名空间中有多个表”

All I need to do is to make the db account lower authority so that it can not find table in other schema. (one shcema relation one db account).

我需要做的就是将db帐户的权限降低，以便它无法在其他模式中找到表。（一个 shcema 关系一个 db 帐户）。

回答by Yury

I have same issue with such configuration

我对这种配置有同样的问题

@Entity
@Table(name = "NOTIFICATION")
public class Notification {
    ...
}

issue was resolved for me when I moved table name from @Table to @Entity

当我将表名从@Table 移动到 @Entity 时，问题已为我解决

@Entity(name = "NOTIFICATION")
@Table
public class Notification {
    ...
}

回答by Yury

Simply, if u are using two schemas then u will get this error. To resolve this error u can use these steps :

简单地说，如果您使用两个模式，那么您将收到此错误。要解决此错误，您可以使用以下步骤：

1. You need to delete extra schema.
2. Or u can define default schemas or that schema are u using.

spring.jpa.properties.hibernate.default_schema=nameOfSchema
and
jdbc:postgresql://localhost:5432/databaseName?currentSchema=nameOfSchema