MySQLSyntaxErrorException: Table XYZ doesn't exist
Solution 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.
Solution 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.
I just had this issue. Explanation here: 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.
--end of edit--
Here is how I fixed it:
- Drop the database.
-
Add this to /etc/mysql/my.conf:
set lower_case_table_names=1 #(default value '0').
- Restart mysqld.
- Recreate the Database.
- ( optional? ) change annotation/hbm.xml table references to lower case.
Solution 3
In my case it was an issue of Hibernate converting my table to lower case.
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).
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.
Solution 4
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.
Solution 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 by adding :
lower_case_table_names=1
in my.cnf.
Also make sure to include
[mysqld]
at the beginning of my.cnf to avoid another error
"MySQL my.cnf file - Found option without preceding group"
barclay
BY DAY: Senior Engineer at Brace.ai. OTHERWISE: Fiber artist. Someday I'd like to own sheep. Not sure what my township would think of that plan.
Updated on August 03, 2022Comments
-
barclay almost 2 years
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] ...
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>
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?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; } }
-
barclay about 11 yearsi discovered that i have another piece that needs to point to a different database on the same server, so i removed the database "worker_events" from my data source spec in applicationContext.xml, and added back
catalog='worker_events'
to my entity. that worked. originally, i had been trying to point catalog at the overall data source name, rather than at the database name. my test shows that catalogs do work with MySQL. -
Poliakoff about 8 yearsSteps 1-4 did not help.
-
PrestigeDev over 7 yearsI had a query with uppercase tablenames and I got always the message table doesn't exist. Thank you for your lowercase hint. Everything works smoothly now! :)