[
Date Prev][
Date Next][
Thread Prev][
Thread Next][
Date Index][
Thread Index]
[
List Home]
|
Re: [eclipselink-users] Table name turned lower-case and not working
|
Thank you both Chris and Tom! Especially for the debugging tips.
My tables are created by EclipseLink with my @Entity annotations automatically during deployment.
I have resolved my issue, I think what happened to my project was that,
- On one hand, all the table names are created in all uppercase. Not sure if it was EclipseLink doing that or JDBC driver or something else.
- On the other hand, when native queries are executed, the table names are converted to all lowercase even though I specified them as upper case in code (I haven't tried the \" method in code yet though).
- Therefore the mismatch and not-found exception.
- This was not a problem when the server (housing GlassFish and MySQL) was running Windows because MySQL default settings in Windows is case-insensitive. This becomes a problem however when the server runs on Debian (or any unix distro I believe) as the MySQL default settings become case-sensitive.
To resolve it, I could either
1. Specify all the table names as lower case using annotations like @Table, or
2. Change the MySQL settings (setting lower_case_table_names
= 1 to be specific) so that all table names are stored and looked-up as lower case.
I used the 2nd option as that's easier for me.
I found some related stackoverflow threads too..
Hope this will be useful for others!
Daniel
On 17 October 2012 23:59, Christopher Delahunt
<christopher.delahunt@xxxxxxxxxx> wrote:
How was your table created?
MySql is generally case sensitive on unix based systems as described here:
http://dev.mysql.com/doc/refman/5.0/en/identifier-case-sensitivity.html
and defaults to lower case - which is why MYTABLE appears as 'mytable' in the logs. If the table was defined in upper case with delimeters/quotes, you must use the same format in all SQL queries.
try "select * from \"MYTABLE\" order by rand() limit " + ApplicationConfig.RESULT_SET_LIMIT;
Best Regards,
Chris
On 17/10/2012 8:33 AM, Tom Ware wrote:
Hi Daniel,
Is there any change your JDBC driver is making the change. We do not
edit native query strings in EclipseLink - we send them as provided. You
should be able to tell what we are sending by setting the logging level
to FINER or more and looking at the log. (Persistence Unit Property:
eclipselink.logging.level=FINER)
-Tom
On 16/10/2012 7:23 PM, DL wrote:
Hello Everyone,
I'm new to this group. Nice to meet you all!
What brings me here is a problem I can't find a solution on the net..
On my native query, my table name is in upper-case,
public static final String NATIVE_QUERY_MYSQL_READ_RANDOM =
"select * from MYTABLE order by rand() limit "
+ ApplicationConfig.RESULT_SET_LIMIT;
But when the following is executed, an exception is thrown,
Query queryRandom = em.createNativeQuery(
MyClass.NATIVE_QUERY_MYSQL_READ_RANDOM,
MyClass.class
);
The exception indicates that the table name has been converted to all
lower-case.
Exception [EclipseLink-4002] (Eclipse Persistence Services -
2.3.2.v20111125-r10461):
org.eclipse.persistence.exceptions.DatabaseException
Internal Exception:
com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Table
'mydatabase.mytable' doesn't exist
Error Code: 1146
Call: SELECT * FROM mytable ORDER BY RAND() LIMIT 10
Query: ReadAllQuery(referenceClass=MyTable sql="SELECT * FROM mytable
ORDER BY
RAND() LIMIT 10")
at
org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:333)
at
org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:644)
at
org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:535)
This was not a problem when the server is on a Windows machine, but I
recently
did this on a unix machine (debian) and it has been not working since
due to
this exception.
Any suggestion help is much appreciated.. thanks!
Daniel
_______________________________________________
eclipselink-users mailing list
eclipselink-users@xxxxxxxxxxx
https://dev.eclipse.org/mailman/listinfo/eclipselink-users
_______________________________________________
eclipselink-users mailing list
eclipselink-users@xxxxxxxxxxx
https://dev.eclipse.org/mailman/listinfo/eclipselink-users
______________________________
_________________
eclipselink-users mailing list
eclipselink-users@xxxxxxxxxxx
https://dev.eclipse.org/mailman/listinfo/eclipselink-users
