MySQLSyntaxErrorException:表XYZ不存在。

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，并试图查询一个表，并返回一个堆栈跟踪，声称表不存在。我可以打开与db的连接，例如，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):

这是持久性。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:

一个分段中:

<?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?

我发现可疑的是hibernate查询的一部分，它试图查询select…从reportsDb。提醒alert0_——我如何确认“reportsDb”实际上代表了我在applicationContext.xml中指定的数据源?

ETA: The entity, Alert, looks like this:

实体，警报，看起来是这样的:

@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;
    }
}

7 个解决方案

#1

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'”部分，因为它被用于构建类似于“reportsDb.alerts”的查询。Mysql不使用目录，AFAIK。

#2

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是上框的。我使用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.

——编辑——回想起来，最好是找到并更改任何@Table或hbm。与数据库匹配的xml引用。我运行了一个生成hbm的向导。以大写字母命名的xml——直到现在才意识到它在我的项目中。我把这个放在这里，让人们意识到这个案例的敏感性。

--end of edit--

——编辑——结束

Here is how I fixed it:

我是这样修正的:

Drop the database.
减少数据库。
Add this to /etc/mysql/my.conf:

添加这个/etc/mysql/my.conf:
```
set lower_case_table_names=1 #(default value '0'). 
```
Restart mysqld.
重启mysqld。
Recreate the Database.
重新创建数据库。
( optional? ) change annotation/hbm.xml table references to lower case.
(可选)注释/ hbm变化。xml表引用到小写。

#3

After exasperadetly eliminating every single occurence of table XYZ in my code, I found the actual issue: XYZ wasn'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触发器所引用。可能考虑在代码之外查找错误。

#4

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。my_database_name MySQLSyntaxErrorException:表”。*’不存在

But the query itself contained no my_database_name references 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.

与其他查询相比，我们发现查询和错误的差异和添加顺序消失了。可能是针对jdbc或c3p0逻辑的黑客攻击，但它对我们有用。

#5

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 my adding

我有同样的问题，我的mysql db在windows上，但我把它移到linux上，这导致mysql语法没有识别表。原因是，windows上的mysql不区分大小写，在linux上区分大小写，我可以解决这个问题。

lower_case_table_names=1

lower_case_table_names = 1

in my.cnf.

。在my . 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文件-没有前一组的选项"

#6

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上，所以我删除了子查询的左外连接，并在没有子查询的情况下将其替换为left outer。

#7

-1

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>

来

  <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"

用“update”替换“createdrop”

thanks

谢谢

#1