Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

JdbcTemplate throws BadSqlGrammarException on a MySQL query

Errororg.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar

Short answer

BadSqlGrammarException is Spring's wrapper for an invalid SQL statement, with the driver's SQLException inside. Read the root cause first: it holds MySQL's own error. The usual reasons are a reserved word used as a name without backticks, and a table name whose letter case differs from the real table on Linux.

  • Applies to Spring Framework 6, MySQL 8.4, Java 21
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    Print the SQL and the wrapped SQLException. getSql() is the statement, and the cause holds the SQLState and MySQL's own message.

    catch (BadSqlGrammarException e) {
        log.error("SQL: {}", e.getSql());
        log.error("Cause: {}", e.getSQLException() == null ? "none" : e.getSQLException().getMessage());
    }
  2. 2Match what you see, then jump to its fix

    1. 1MySQL message says You have an error in your SQL syntax ... near the wordA reserved word is used as a table or column nameFix
    2. 2Table 'app.Users' doesn't exist, but users exists; it works on a Windows or Mac laptopThe table name has a different letter case than the real tableFix

What does the error look like?

  • org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar
  • nested exception is java.sql.SQLSyntaxErrorException

Cause 1 of 2

A reserved word is used as a table or column name

1Check

Words like INTERVAL are reserved. Using one as an identifier without quoting causes a syntax error right at that word.

SELECT * FROM information_schema.KEYWORDS WHERE WORD = 'INTERVAL' AND RESERVED = 1;

2Fix

Quote the identifier with backticks in the SQL string, or rename the column.

jdbcTemplate.query("SELECT `interval` FROM `schedule` WHERE id = ?", mapper, id);

3Verify

The same query now runs in the mysql client and from Spring.

SELECT `interval` FROM `schedule` LIMIT 1;

Confirmed by dev.mysql.com

Did this fix it?

Cause 2 of 2

The table name has a different letter case than the real table

1Check

On most Unix systems table names are case-sensitive; Windows and default macOS file systems are not. lower_case_table_names defaults to 0 on Unix.

SHOW TABLES;
SHOW VARIABLES LIKE 'lower_case_table_names';

2Fix

Use the exact stored case in every query. The manual recommends one convention, such as always lowercase, for portability.

jdbcTemplate.query("SELECT id, name FROM users WHERE id = ?", mapper, id);

3Verify

The query runs on the Linux server as it did on the laptop.

SHOW TABLES LIKE 'users';

Confirmed by dev.mysql.com

Did this fix it?

How do I stop it coming back?

  • Name tables and columns in lowercase with underscores and avoid words on the reserved list.
  • Run integration tests against the same MySQL major version and OS family as production.
  • Log getSql() with the exception so the failing statement is never a guess.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Projects of mine built on the same technologies:

Related fixes

Databases

MySQL refuses to delete a row because other rows point to it

SQLIntegrityConstraintViolationException: Cannot delete or update a parent row: a foreign key constraint fails

Error 1451 means at least one child row still references the parent row you are deleting or changing.

3 causes, each with a fix

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.