As I came back to Matchlogger development today after a short break, I wanted to implement adding matches. I added a Liquibase migration, created all the required repositories and an entity class that looked like this:

When I opened the application and triggered a controller method that aimed to get all matches from the database, I saw an error message and a stack trace that had a cryptic message at the top:

2017-04-24 20:08:29.337 WARN 3190 --- [nio-8080-exec-4] o.h.engine.jdbc.spi.SqlExceptionHelper : SQL Error: 1064, SQLState: 42000
2017-04-24 20:08:29.337 ERROR 3190 --- [nio-8080-exec-4] o.h.engine.jdbc.spi.SqlExceptionHelper : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'match match0_' at line 1
2017-04-24 20:08:29.357 ERROR 3190 --- [nio-8080-exec-4] o.a.c.c.C.[.[.[/].[dispatcherServlet] : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [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] with root cause

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'match match0_' at line 1
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[na:1.8.0_121]
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) ~[na:1.8.0_121]
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[na:1.8.0_121]
	at java.lang.reflect.Constructor.newInstance(Constructor.java:423) ~[na:1.8.0_121]
...

Not exactly the most meaningful message. So I added more logs into application.properties (Spring Boot FTW):

spring.jpa.properties.hibernate.show_sql=true
spring.jpa.properties.hibernate.use_sql_comments=true
spring.jpa.properties.hibernate.format_sql=true

Right now I at least had an SQL that I could run:

sql> select
  match0_.id as id1_1_,
  match0_.first_team_id as first_te3_1_,
  match0_.match_date as match_da2_1_,
  match0_.second_team_id as second_t4_1_
from
  match match0_
[2017-04-24 20:09:08] [42000][1064] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'match match0_' at line 7

As you can see, the message is still cryptic. There is a mention of error 1064 here. I found a reference that suggested that it might be because of reserved words. DB engines use some specific words that may be part of their syntax and using them usually requires adding backticks to the name. Is match such a word? Turns out it is.

How to solve that?

The solution is simple. The only thing I needed to do was to add quotation marks to the table name in entity class:

You will see the same issue especially with users. An obvious choice is always naming the table with users records as user which is a reserved, well, anywhere I remember. I did foresee that one and named the table ml_user, but didn’t expect to come across the same issue with matches.