I have developed a web app with Spring MVC/Maven/Hibernate/phpMyadmin. On my local machine my application works perfectly on Apache Tomcat 8.5.0. When I uploaded to my CentOS VPS only some of the web app worked. All my categories are been fetched from the database and displayed so the database interaction code is fine. When I try and get a list of products by category ID, this is where the problem starts. All the data is present in the database so the both the web app on my local machine and the web app on the VPS are near to identical.
Hopefully someone can shed some light on my problem. Below is my stacktace and code.
Thank and regards,
HTTP Status 500 - Request processing failed; nested exception is org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet
type Exception report
message Request processing failed; nested exception is org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet
description The server encountered an internal error that prevented it from fulfilling this request.
Since it's an SQLGrammarException, check the SQL queries generated by Hibernate. Either set hibernate configuration's "hibernate.show_sql" to true, or set the logging category "org.hibernate.SQL" to debug or trace.
See Hibernate Logging.
There are many ways in which queries that work in a dev machine may fail in a production machine.
One possibility is that the database versions on dev and production are very different, and what is valid in newer version dialect may not be in the older one.
Another example is a MySQL gotcha related to how OSes handle filename cases that I had run into a long time back.
My dev machine was a Windows machine and production was a Linux machine. MySQL stores each database and table as files.
Since Windows ignores filename cases, "mydir/mytable" and "mydir/MyTable" are the same file in Windows. An SQL query can use any case variation on that and still work because the OS finds the table file just fine.
But not so in case sensitive Linux.
I had to change the schema creation scripts and my queries to use the same case. Everything in lower case always is a good rule to follow.
I don't know if that's the problem here, but verify once that cases are consistent, especially if your dev machine is Windows.
Thank you for the very informative and helpful reply. My major issue is that the query to display all the categories in my db is working fine, therefore I don't think there is any OS or case-sensitivity issues. When the app is asked to display products by category that is when the issue arises.