MySQLSyntaxErrorException: Table 'db.settings' doesn't exist

alertThe same application code, the same database engine but it works on one server, on another does not. Why?

 

 

Problem

My application is heavily based on Spring. Most importantly - Spring Data with Hibernate. It connects to MySQL database.

Locally, everything works fine but after deployment to AWS, the application logs this exception:

MySQLSyntaxErrorException: Table 'db.settings' doesn't exist

I manually checked - the database db exists, SETTINGS table as well. Oh ... wait. SETTINGS table exists but settings does not? I checked a JPA entity - there is SETTINGS table required so why the application is looking for settings?

 

Solution

I added the following entry to application.yml in my application which fixed the issue.

spring.jpa.hibernate.naming.physical-strategy: org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl

It enforces JPA to use original case for names of database objects. The default setting changes it to lower case. There is some rationale behind the default value. Lower case enforces some naming convention - names of all objects are lower case even if a programmer typed them otherwise. Unfortunately, in my case it was not a good thing.

 

Why did it work locally?

It would not be so big issue if it behaved the same way during development on my local environment. So why did it work correctly? A mistery was in MySQL and the operating system. MySQL is case sensitiveness inherits from the operating system. Do you already know what happened? Yes. I locally use case insensitive Windows but deployed to AWS on case sensitive Linux.

Mistery solved.

If you like what I do, consider buying me a coffee :)

Buy me a coffeeBuy me a coffee