getting data problem with MYSQL under linux

Question

Hi all,

Recently I started to use Linux (Ubuntu 9.10) instead of windows. I am working on a java web application with Spring, MYSQL with jpa. However, before to install linux I made a backup file from the database, then installed linux, installed the MYSQL Query Browser and Administrator tools, and using the Admin tool restored the backup file, then got all the tables and made a simple select statement from one of the tables and got result normally and everything seems to work just fine.

There a USER table, and there's a namedQuery defined to get a user by userName, the problem is that when I pass a correct userName I still get nothing!

I really don't know what is the problem! The application was working perfectly under windows!

Please, can anyone help me to solve this problem?

Thank you in advance.

Answer 1

The first thing that comes to mind is that MySQL table names are case sensitive on Linux but not on Windows. i.e. this might have worked on Windows:

SELECT ... FROM User ...

but on Linux you need to use the correct case for the table name:

SELECT ... FROM USER ...

but without more information it's really difficult to tell.

Answer 2

There are some settings that have different defaults under Windows and Linux, case sensitivity of table names etc. being one of them. It could be that a non-specified setting defaulted to one value on windows, but another under linux.

Answer 3

You're all right, and for my case I found out why wasn't I able to login passing the userName! The problem is that when I restore schema from the backup file, all the tables are being created with lower case names, for example, the USER table would be user, then.. When I boot up tomcat, JPA creates automatically all the @Entity, therefore it creates a User tables for the @Entity User, the result is that I have two tables for USER, a user table which has all the data, and an empty User table, and this is the one that is used by the application! That's why I was not able to retrieve any data because actually there's no data to retrieve from the table.

A workaround to solve this problem I modified the backup file renaming all the tables as expected by JPA to avoid creating them again when booting up the application.

I really don't know if there's a better solution, but it worked like this for me.

Finally, thank you very much for your help.