Solved MySQL Syntax Error

Discussion in 'Spigot Plugin Development' started by jvdberg08, Jul 14, 2019 at 12:17 PM.

  1. Hello everyone,

    I'm having some issues with MySQL giving me syntax errors, but I can't for the life of me figure out what's wrong. It's probably something easy I miss everytime, but if you guys could help me out I'd appreciate that!

    Code (Text):
    [13:15:23 WARN]: 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 'GROUP='DEFAULT' WHERE UUID='1f232cf1-cc5e-31dc-8ee9-e79960fa97d3'' at line 1
    Code that gives the error:
    Code (Java):
    try {
                PreparedStatement statement = instance.getDatabaseHandler().getConnection().prepareStatement("UPDATE user_data SET GROUP=? WHERE UUID=?");
                statement.setString(1, groupName);
                statement.setString(2, player.getUniqueId().toString());
                statement.executeUpdate(); // <---- this line throws the error (ofcourse, since that's where the sql command gets send.)
            } catch (SQLException e) {
                e.printStackTrace();
                sender.sendMessage(ChatColor.RED + "Something went wrong executing the command!");
                return;
            }
     
  2. I think you should use insert instead of update
     
  3. GROUP is a reserved keyword, used by e.g. "GROUP BY".

    Solution: surround group with backticks to escape the reserved keyword.

    Code (Text):
    UPDATE user_data SET `GROUP`=? WHERE UUID=?
     
    • Agree Agree x 2
  4. Thanks, I didn't know that!