MySQLSyntaxErrorException: Table XYZ doesn't exist

Question

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

Yamamoto

How does the Entity look like? in the @Table annotation you can specify the table, catalog and schema name – Auricula

@Auricula - good point, I forgot that - edited to add. – Yamamoto

Answer 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.

Auricula

i 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. – Yamamoto

Answer 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:

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.

Hummock

Steps 1-4 did not help. – Melyndamem

I 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! :) – Haunted

Answer 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.

Doubler

Answer 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.

Yellowtail

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

Vinyl

Answer 6

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

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

But the query itself contained no my_database_name references or even * signs.

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.

Celaeno

Answer 7

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

Nissie

Answer 8

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"

thanks

Salutatory

Answer 9

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

I wrote 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

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>

Didier

Answer 10

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.

Imprecate

Answer 11

Just add the property in hibernate.cfg.xml file then update the project then run the main method and the exception will go..thank you..

<session-factory><property name="hibernate.hbm2ddl.auto">create</property> </session-factory>

Decease