MySQL MySQLSyntaxErrorException：表 XYZ 不存在

MySQLSyntaxErrorException: Table XYZ doesn't exist

提问by barclay

I am using JPA and c3p0 and attempting to query a table and getting back a stack trace claiming that the table doesn't exist. I can open a connection to the db in, for example, DbVisualizer, and see the table there. In fact, the debug statements from my app show it is able to make a connection and test its viability. But then it is not finding the table.

我正在使用 JPA 和 c3p0 并尝试查询表并获取声称该表不存在的堆栈跟踪。例如，我可以在 DbVisualizer 中打开与数据库的连接，然后查看那里的表。事实上，我的应用程序的调试语句表明它能够建立连接并测试其可行性。但后来它没有找到桌子。

15:45:53.940 [http-8080-1] DEBUG o.h.e.j.i.LogicalConnectionImpl - Obtaining JDBC connection
15:45:53.940 [http-8080-1] DEBUG c.m.v.c.i.C3P0PooledConnectionPool - Testing PooledConnection [com.mchange.v2.c3p0.impl.NewPooledConnection@4d687dcd] on CHECKOUT.
15:45:53.949 [http-8080-1] DEBUG c.m.v.c.i.C3P0PooledConnectionPool - Test of PooledConnection [com.mchange.v2.c3p0.impl.NewPooledConnection@4d687dcd] on CHECKOUT has SUCCEEDED.
15:45:53.950 [http-8080-1] DEBUG c.m.v.resourcepool.BasicResourcePool - trace com.mchange.v2.resourcepool.BasicResourcePool@7930ebb [managed: 3, unused: 2, excluded: 0] (e.g. com.mchange.v2.c3p0.impl.NewPooledConnection@3e30e173)
15:45:53.950 [http-8080-1] DEBUG o.h.e.j.i.LogicalConnectionImpl - Obtained JDBC connection
15:45:53.966 [http-8080-1] DEBUG org.hibernate.SQL - select alert0_.rrdb_key as rrdb1_0_, alert0_.date as date0_, alert0_.hostname as hostname0_, alert0_.message as message0_, alert0_.program as program0_ from reportsDb.alerts alert0_ where (alert0_.message not like '%Anomolous%') and (alert0_.message not like '%Requeue%')
Hibernate: select alert0_.rrdb_key as rrdb1_0_, alert0_.date as date0_, alert0_.hostname as hostname0_, alert0_.message as message0_, alert0_.program as program0_ from reportsDb.alerts alert0_ where (alert0_.message not like '%Anomolous%') and (alert0_.message not like '%Requeue%')
15:45:54.013 [http-8080-1] DEBUG c.m.v2.c3p0.impl.NewPooledConnection - com.mchange.v2.c3p0.impl.NewPooledConnection@4d687dcd handling a throwable.
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'reportsDb.alerts' doesn't exist
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[na:1.6.0_45]
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39) ~[na:1.6.0_45]
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27) ~[na:1.6.0_45]
    at java.lang.reflect.Constructor.newInstance(Constructor.java:513) ~[na:1.6.0_45]
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:406) ~[mysql-connector-java-5.1.6.jar:na]
...

Here is persistence.xml (in /src/main/resources/META-INF):

这是persistence.xml（在/src/main/resources/META-INF）：

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="1.0" xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
             xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_1_0.xsd">
    <persistence-unit name="reportsDb" transaction-type="RESOURCE_LOCAL">
        <description>Hibernate</description>
        <class>com.pronto.mexp.common.entity.Alert</class>
    </persistence-unit>
</persistence>

A subsection of applicationContext.xml:

applicationContext.xml 的一个小节：

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:context="http://www.springframework.org/schema/context"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
       http://www.springframework.org/schema/beans/spring-beans-2.0.xsd
       http://www.springframework.org/schema/context
       http://www.springframework.org/schema/context/spring-context-3.1.xsd">

    <bean id="jpaDialect" class="org.springframework.orm.jpa.vendor.HibernateJpaDialect"/>

    <bean id="reportsDbEntityManagerFactory" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
        <property name="dataSource" ref="reportsDbDataSource" />
        <property name="jpaVendorAdapter">
            <bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter">
                <property name="showSql" value="true"/>
                <property name="generateDdl" value="false" />
                <property name="databasePlatform" value="org.hibernate.dialect.MySQL5InnoDBDialect" />
            </bean>
        </property>
        <property name="persistenceUnitName" value="reportsDb" />
        <property name="jpaDialect" ref="jpaDialect"/>
    </bean>

    <bean id="reportsDbDataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
        <property name="driverClass" value="com.mysql.jdbc.Driver"/>
        <!--<property name="jdbcUrl" value="jdbc:mysql://devdbrw01:3306/mexp"/>-->
        <property name="jdbcUrl" value="jdbc:mysql://report101:3306/worker_events"/>
        <property name="user" value="********"/>
        <property name="password" value="********"/>
        <property name="acquireRetryDelay" value="1000"/>
        <property name="acquireRetryAttempts" value="4"/>
        <property name="breakAfterAcquireFailure" value="false"/>
        <property name="testConnectionOnCheckout" value="true"/>
        <property name="maxConnectionAge" value="14400"/>
        <property name="maxIdleTimeExcessConnections" value="1800"/>
    </bean>

    <!-- DAOs -->
    <bean id="genericReportsDbDAO" class="com.pronto.mexp.common.dal.GenericReportsDbJPADAOImpl"/>

    <bean id="alertJPADAO" class="com.pronto.mexp.dal.AlertJPADAOImpl" parent="genericReportsDbDAO"/>
</beans>

The thing I find suspicious is the part of the hibernate query where it tries to query select ... from reportsDb.alerts alert0_- how do I confirm that "reportsDb" actually stands for my data source that I spec'd in applicationContext.xml?

我发现可疑的是它试图查询的休眠查询的一部分select ... from reportsDb.alerts alert0_- 我如何确认“reportsDb”实际上代表我在 applicationContext.xml 中指定的数据源？

ETA: The entity, Alert, looks like this:

ETA：实体 Alert 如下所示：

@Entity
@Table(name = "alerts", catalog = "reportsDb")
public class Alert {

    int rrdbKey;
    String hostname = "";
    String message = "";
    String program = "";
    Date date = new Date();

    @javax.persistence.Column(name = "rrdb_key", nullable = false, insertable = false, updatable = false, length = 10, precision = 0)
    @Id
    public int getRrdbKey() {
        return rrdbKey;
    }

    public void setRrdbKey(int rrdbKey) {
        this.rrdbKey = rrdbKey;
    }

    @javax.persistence.Column(name = "hostname", nullable = false, insertable = false, updatable = false, length = 32, precision = 0)
    @Basic
    public String getHostname() {
        return hostname;
    }

    public void setHostname(String hostname) {
        this.hostname = hostname;
    }

    @javax.persistence.Column(name = "message", nullable = false, insertable = false, updatable = false, length = 128, precision = 0)
    @Basic
    public String getMessage() {
        return message;
    }

    public void setMessage(String message) {
        this.message = message;
    }

    @javax.persistence.Column(name = "program", nullable = true, insertable = false, updatable = false, length = 40, precision = 0)
    @Basic
    public String getProgram() {
        return program;
    }

    public void setProgram(String program) {
        this.program = program;
    }

    @javax.persistence.Column(name = "date", nullable = false, insertable = false, updatable = false, length = 19, precision = 0)
    @Basic
    public Date getDate() {
        return date;
    }

    public void setDate(Date date) {
        this.date = date;
    }
}

采纳答案by German

From your entity definition, remove the catalog = 'reportsDb'part, since it is being used to build the query like select from 'reportsDb.alerts'. Mysql doesn't use catalogs, AFAIK.

从您的实体定义中删除该catalog = 'reportsDb'部分，因为它用于构建查询，如select from 'reportsDb.alerts'. Mysql 不使用目录，AFAIK。

回答by David Lotts

If the table really, really, does exist in mySQL, and your using Linux/Unix, and the error shows the table name in wrong/upper-case, then the issue is that table names in MySQL are case sensitive and hibernate is upper casing them. I'm using hibernate 4.3.

如果该表确实存在于 mySQL 中，并且您使用的是 Linux/Unix，并且错误显示表名错误/大写，那么问题是 MySQL 中的表名区分大小写，而 hibernate 是大写他们。我正在使用休眠 4.3。

I just had this issue. Explanation here: lower_case_table_names=1

我刚刚有这个问题。此处解释：lower_case_table_names=1

--edit-- In retrospect, it's probably better to find and change any @Table or hbm.xml references to match the database. I ran a wizard that generated an hbm.xml with uppercase names -- didn't realize that it was in my project until just now. I'll leave this here to make people aware of the case sensitivity.

--edit-- 回想起来，最好找到并更改任何 @Table 或 hbm.xml 引用以匹配数据库。我运行了一个向导，它生成了一个带有大写名称的 hbm.xml —— 直到现在才意识到它在我的项目中。我将把这个留在这里，让人们意识到区分大小写。

--end of edit--

——编辑结束——

Here is how I fixed it:

这是我修复它的方法：

1. Drop the database.

2. Add this to /etc/mysql/my.conf:

   set lower_case_table_names=1 #(default value '0'). 
   

3. Restart mysqld.
4. Recreate the Database.
5. ( optional? ) change annotation/hbm.xml table references to lower case.

1. 删除数据库。

2. 将此添加到/etc/mysql/my.conf：

   set lower_case_table_names=1 #(default value '0'). 
   

3. 重启mysqld。
4. 重新创建数据库。
5. （可选？）将 annotation/hbm.xml 表引用更改为小写。

回答by phil294

After exasperadetly eliminating every single occurence of table XYZin my code, I found the actual issue: XYZwasn't being referenced by JPA, but by an old, invalid mysql trigger. Maybe consider looking for the error outside of your code.

在令人恼火地消除XYZ了我的代码中出现的每一个表之后，我发现了实际问题：XYZ不是被 JPA 引用，而是被一个旧的、无效的 mysql 触发器引用。也许考虑在代码之外寻找错误。

回答by orrymr

In my case it was an issue of Hibernate converting my table to lower case.

就我而言，这是 Hibernate 将我的表转换为小写的问题。

My error was:

我的错误是：

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'Pluto.c_story' doesn't exist

Pluto is my db and C_Story is my table (NB: not c_story - lower case).

Pluto 是我的数据库，C_Story 是我的表（注意：不是 c_story - 小写）。

All I had to do was the following:

我所要做的就是以下几点：

spring.jpa.hibernate.naming.physical-strategy=org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl

Well, I hope this helps someone.

好吧，我希望这对某人有所帮助。

回答by Olakunle Awotunbo

I had the same issue, my mysql db was on windows but i moved it to linux which resulted in the mysql syntax of not recognizing the table. The cause was that mysql on windows is case insensitive and case sensitive on linux I was able to resolve this by adding :

我遇到了同样的问题，我的 mysql 数据库在 Windows 上，但我将它移到了 linux，这导致 mysql 语法无法识别该表。原因是 Windows 上的 mysql 不区分大小写，而在 linux 上区分大小写我能够通过添加来解决这个问题：

lower_case_table_names=1

lower_case_table_names=1

in my.cnf.

在我的.cnf。

Also make sure to include

还要确保包括

[mysqld]

[mysqld]

at the beginning of my.cnf to avoid another error

在 my.cnf 的开头以避免另一个错误

"MySQL my.cnf file - Found option without preceding group"

"MySQL my.cnf file - Found option without preceding group"

回答by Marv mash

had a similar error on my code and I changed the persistence file

我的代码有类似的错误，我更改了持久性文件

   <properties>
     <!-- Properties for Hibernate -->
     <property name="hibernate.hbm2ddl.auto" value="create-drop" />
     <property name="hibernate.show_sql" value="false" />
   </properties>

to

到

  <properties>
     <!-- Properties for Hibernate -->
     <property name="hibernate.hbm2ddl.auto" value="update" />
     <property name="hibernate.show_sql" value="false" />
  </properties>

replaced the "create-drop" with "update"

将“create-drop”替换为“update”

thanks

谢谢

回答by Atequer Rahman

I also faced this error. In my case, I wrote the wrong database name in jdbc datasource class.

我也遇到了这个错误。就我而言，我在 jdbc 数据源类中写了错误的数据库名称。

I wrote cms

我写 cms

jdbc:mysql://localhost:3306/cms

jdbc:mysql://localhost:3306/cms

 <bean id="jdbcDataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    <property name="driverClassName" value="com.mysql.jdbc.Driver" />
    <property name="url" value="jdbc:mysql://localhost:3306/cms" />
    <property name="username" value="root" />   
    <property name="password" value="" />
</bean>

Actually, the name of my database was hit_kcsl

实际上，我的数据库的名称是 hit_kcsl

jdbc:mysql://localhost:3306/hit_kcsl

jdbc:mysql://localhost:3306/hit_kcsl

 <bean id="jdbcDataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    <property name="driverClassName" value="com.mysql.jdbc.Driver" />
    <property name="url" value="jdbc:mysql://localhost:3306/hit_kcsl" />
    <property name="username" value="root" />   
    <property name="password" value="" />
</bean>

回答by Zon

We faced the same issue. There was one SQL query that didn't pass with an error like

我们面临同样的问题。有一个 SQL 查询未通过，并出现类似错误

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'my_database_name.*' doesn't exist

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 表 'my_database_name.*' 不存在

But the query itself contained no my_database_namereferences or even * signs.

但是查询本身不包含my_database_name引用，甚至不包含* 符号。

Comparing to other queries, we found the difference and added ORDER BY to query and error disappeared. Could be a hack around jdbc or c3p0 logics, but it worked for us.

与其他查询相比，我们发现了不同之处，并在查询中添加了 ORDER BY，错误消失了。可能是对 jdbc 或 c3p0 逻辑的一种黑客攻击，但它对我们有用。

回答by shareef

In my case the reason was because of left outer join on subquery with alias , that is working on sql editor but not on JDBCspring, so i removed the left outer join of subquery and replaced it with left outer with no subquery

在我的情况下，原因是因为子查询上的左外连接带有别名，它在 sql 编辑器上工作，而不是在 JDBCspring 上工作，所以我删除了子查询的左外连接并将其替换为没有子查询的左外连接

回答by Avinash Mamidipalli

In my case i mapped wrong dialect in cfg.xml, I am using MySQL57 db but used MySQL dialect. When I replaced correct dialect to MySQL57Dialect it worked.

就我而言，我在 cfg.xml 中映射了错误的方言，我使用的是 MySQL57 db，但使用的是 MySQL 方言。当我将正确的方言替换为 MySQL57Dialect 时，它起作用了。