Bad SQL grammar exception after deploying in server

Question

I am working with MySql, Spring MVC.

When I run my code on my computer (localhost) it works fine. But after deploying it on remote server it shows Bad SQL grammar exception.

Here is screen shot from my remote server database custom error. (Screenshot from phpMyAdmin)

Here is the error in text formate:

PreparedStatementCallback; bad SQL grammar [insert into matchs (id, title, location, number_of_players, over, team1, team2, toss, status, result, team_init, match_end, match_started, match_views, bookmarked, announcement, tournament, create_date, start_date, active_date, asst_scorer, start_date_string) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)]; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'over, team1, team2, toss, status, result, team_init, match_end, match_started, m' at line 1

Here is my Java code to insert data in MySql Table

public boolean createMatch(Match match) {

    BeanPropertySqlParameterSource params = new BeanPropertySqlParameterSource(match);

    return jdbc.update(
        "insert into matchs (id, title, location, number_of_players, over, team1, team2, toss, status, result, team_init, match_end, match_started, match_views, bookmarked, announcement, tournament, create_date, start_date, active_date, asst_scorer, start_date_string) values (:id, :title, :location, :number_of_players, :over, :team1, :team2, :toss, :status, :result, :team_init, :match_end, :match_started, :match_views, :bookmarked, :announcement, :tournament, :create_date, :start_date, :active_date, :asst_scorer, :start_date_string)",
        params) == 1;
}

The question is tagged with Mysql but the stacktrace belongs to MariaDb, are you running different DB servers in localhost and remote? — Aritz, Oct 14 '17 at 10:01
You should run the same environment in local and production. This way you would avoid this kind of issues. — Aritz, Oct 14 '17 at 10:05

score 3 · Accepted Answer · edited Oct 14 '17 at 10:19

3

OVER is a MariaDB keyword: https://mariadb.com/kb/en/library/window-functions-overview/

Rename that column to something else. I would also strongly advise using the same database in all your environments, otherwise your tests will detect bugs that don't happen in production, or won't detect bugs that happen in production.

edited Oct 14 '17 at 10:19

cEz

4,932
1
25
38

answered Oct 14 '17 at 10:08

JB Nizet

678,734
91
1,224
1,255

1

It is also in MySQL 8. Backticks on the field would avoid the issue if unable to rename – cEz Oct 14 '17 at 10:20

Bad SQL grammar exception after deploying in server

1 Answers1