My problem
I am working on a Spring Boot project with JPA (Hibernate implementation), and the database underlying is MySQL running on Windows. The table names are in lower case. One day I moved the database from Windows to Linux, imported all data, and then restarted the application. Hibernate created some of the tables for me, with names same as entity class, including capitalisation. And it reads the data from the new table instead of the old table with lower case name. I tried to find out why and finally got the reason.
Database and table name of MySQL
First I need to talk about history. In the time of MyISAM storage engine, a database is just a folder and a table is a group of files in that folder. The name of the database is the name of the folder. In Windows, when accessing a folder, the case of name is not considered, so "Student" and "student" means the same folder. But in Linux, the folder name is case sensitive. So when running two SQL query below, different behavior can be seen when running on Windows and Linux.
SELECT `name`, `class` from `student`;
SELECT `name`, `class` from `Student`;
And now lower_case_table_names system variable determines the behavior of case of table name. For details please read this.
When Hibernate will help you generate table name
When an entity class is not specified a table name by @Table annotation, Hibernate will determine the table name. Hibernate first read the system variable lower_case_table_names, and then decided to use the name including capitalisation or not by this value.
Back to my problem
In the newly setup Linux MySQL database, add the following line into my.cnf. Restart both MySQL server and my application, and the problem is solved.
[mysqld]
lower_case_table_names = 1