MySQLSyntaxErrorException:表XYZ不存在 [英] MySQLSyntaxErrorException: Table XYZ doesn't exist
本文介绍了MySQLSyntaxErrorException:表XYZ不存在的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我正在使用JPA和c3p0并尝试查询表并返回堆栈跟踪，声称该表不存在.我可以在例如DbVisualizer中打开与数据库的连接，然后查看其中的表.实际上，我的应用程序中的调试语句表明它能够建立连接并测试其可行性.但是，这并没有找到表格.
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.
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]
...
这是persistence.xml(在/src/main/resources/META-INF中):
Here is persistence.xml (in /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>
applicationContext.xml的小节:
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>
我发现可疑的是休眠查询的一部分，它尝试查询select ... from reportsDb.alerts alert0_-如何确认"reportsDb"实际上代表我在applicationContext.xml中指定的数据源?
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?
ETA: 实体Alert如下所示:
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;
}
}
推荐答案
从您的实体定义中删除catalog = 'reportsDb'部分，因为它已用于构建类似于select from 'reportsDb.alerts'的查询.
Mysql不使用目录AFAIK.
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.
这篇关于MySQLSyntaxErrorException:表XYZ不存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
