MySQL Syntax Error

Discussion in 'Spigot Plugin Development' started by XZot1K, May 31, 2016.

  1. Hey ive been working on this for hours now and just can't figure out the issue i get this error:
    Code (Text):
    [14:18:06] [Server thread/WARN]: 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 'd8993-50ab-4d7d-9490-7776742241bc' at line 1
    [14:18:06] [Server thread/WARN]:     at sun.reflect.GeneratedConstructorAccessor256.newInstance(Unknown Source)
    [14:18:06] [Server thread/WARN]:     at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
    [14:18:06] [Server thread/WARN]:     at java.lang.reflect.Constructor.newInstance(Unknown Source)
    [14:18:06] [Server thread/WARN]:     at com.mysql.jdbc.Util.handleNewInstance(Util.java:404)
    [14:18:06] [Server thread/WARN]:     at com.mysql.jdbc.Util.getInstance(Util.java:387)
    [14:18:06] [Server thread/WARN]:     at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:941)
    [14:18:06] [Server thread/WARN]:     at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3870)
    [14:18:06] [Server thread/WARN]:     at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3806)
    [14:18:06] [Server thread/WARN]:     at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2470)
    [14:18:06] [Server thread/WARN]:     at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2617)
    [14:18:06] [Server thread/WARN]:     at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2546)
    [14:18:06] [Server thread/WARN]:     at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2504)
    [14:18:06] [Server thread/WARN]:     at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1370)
    [14:18:06] [Server thread/WARN]:     at org.zotdev.plugins.kmc.utils.Manager.getKills(Manager.java:148)
    [14:18:06] [Server thread/WARN]:     at org.zotdev.plugins.kmc.listeners.PlayTime.onJoin(PlayTime.java:15)
    [14:18:06] [Server thread/WARN]:     at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    [14:18:06] [Server thread/WARN]:     at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    [14:18:06] [Server thread/WARN]:     at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    [14:18:06] [Server thread/WARN]:     at java.lang.reflect.Method.invoke(Unknown Source)
    [14:18:06] [Server thread/WARN]:     at org.bukkit.plugin.java.JavaPluginLoader$1.execute(JavaPluginLoader.java:306)
    [14:18:06] [Server thread/WARN]:     at org.bukkit.plugin.RegisteredListener.callEvent(RegisteredListener.java:62)
    [14:18:06] [Server thread/WARN]:     at org.bukkit.plugin.SimplePluginManager.fireEvent(SimplePluginManager.java:502)
    [14:18:06] [Server thread/WARN]:     at org.bukkit.plugin.SimplePluginManager.callEvent(SimplePluginManager.java:487)
    [14:18:06] [Server thread/WARN]:     at net.minecraft.server.v1_9_R2.PlayerList.onPlayerJoin(PlayerList.java:333)
    [14:18:06] [Server thread/WARN]:     at net.minecraft.server.v1_9_R2.PlayerList.a(PlayerList.java:159)
    [14:18:06] [Server thread/WARN]:     at net.minecraft.server.v1_9_R2.LoginListener.b(LoginListener.java:144)
    [14:18:06] [Server thread/WARN]:     at net.minecraft.server.v1_9_R2.LoginListener.c(LoginListener.java:54)
    [14:18:06] [Server thread/WARN]:     at net.minecraft.server.v1_9_R2.NetworkManager.a(NetworkManager.java:233)
    [14:18:06] [Server thread/WARN]:     at net.minecraft.server.v1_9_R2.ServerConnection.c(ServerConnection.java:140)
    [14:18:06] [Server thread/WARN]:     at net.minecraft.server.v1_9_R2.MinecraftServer.D(MinecraftServer.java:825)
    [14:18:06] [Server thread/WARN]:     at net.minecraft.server.v1_9_R2.DedicatedServer.D(DedicatedServer.java:399)
    [14:18:06] [Server thread/WARN]:     at net.minecraft.server.v1_9_R2.MinecraftServer.C(MinecraftServer.java:665)
    [14:18:06] [Server thread/WARN]:     at net.minecraft.server.v1_9_R2.MinecraftServer.run(MinecraftServer.java:564)
    [14:18:06] [Server thread/WARN]:     at java.lang.Thread.run(Unknown Source)
    [14:18:06] [Server thread/INFO]: 0
    And i know that its coming from my code here:
    Code (Text):
        public static int getKills(UUID id) {
            try {
                ResultSet rs = plugin.c.createStatement().executeQuery("SELECT * FROM kmc WHERE uuid = " + id.toString() + ";");
                if(rs.next()) {
                    return rs.getInt("kills");
                } else {
                    PreparedStatement ps = plugin.c.prepareStatement("INSERT INTO kmc (uuid, kills) VALUES (?, ?);");
                    ps.setString(1, id.toString());
                    ps.setInt(2, 0);
                    ps.executeQuery();
                    ps.close();
                }
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return 0;
        }
    Here is the connection that is registered as well:
    Code (Text):
    try {
                Class.forName("com.mysql.jdbc.Driver");
                c = DriverManager.getConnection(url, getConfig().getString("MySQL.Username"),
                        getConfig().getString("MySQL.Password"));
                Statement statement = c.createStatement();
                c.prepareStatement("CREATE DATABASE IF NOT EXISTS kms_database").execute();
                statement.executeUpdate(
                        "CREATE TABLE IF NOT EXISTS kmc (UUID varchar(32),kills int,deaths int,currentks int,highestks int,playtime long"
                                + ",killsda int,killsla int,killsga int)");
                statement.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
     
  2. You're just throwing it there.
    Atleast in MySQL and SQLite you need single-quotes (') around Strings/VARCHARs
     
  3. Ive tried putting the single quotes around the uuid string, however i then get a error telling me to remove them.
     
  4. @JKMODz put the quotes inside of the string
     
  5. Inside the string? Which string should i insert them?
     
  6. @JKMODz
    Code (Java):
    "uuid = '" + id.toString() + "';"
     
  7. Yeah i tried that as well i got the following error:
    Code (Text):
    [14:38:27] [Server thread/WARN]: 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 'd8993-50ab-4d7d-9490-7776742241bc' at line 1
    [14:38:27] [Server thread/WARN]:     at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    [14:38:27] [Server thread/WARN]:     at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
    [14:38:27] [Server thread/WARN]:     at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
    [14:38:27] [Server thread/WARN]:     at java.lang.reflect.Constructor.newInstance(Unknown Source)
    [14:38:27] [Server thread/WARN]:     at com.mysql.jdbc.Util.handleNewInstance(Util.java:404)
    [14:38:27] [Server thread/WARN]:     at com.mysql.jdbc.Util.getInstance(Util.java:387)
    [14:38:27] [Server thread/WARN]:     at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:941)
    [14:38:27] [Server thread/WARN]:     at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3870)
    [14:38:27] [Server thread/WARN]:     at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3806)
    [14:38:27] [Server thread/WARN]:     at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2470)
    [14:38:27] [Server thread/WARN]:     at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2617)
    [14:38:27] [Server thread/WARN]:     at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2546)
    [14:38:27] [Server thread/WARN]:     at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2504)
    [14:38:27] [Server thread/WARN]:     at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1370)
    [14:38:27] [Server thread/WARN]:     at org.zotdev.plugins.kmc.utils.Manager.getKills(Manager.java:148)
    [14:38:27] [Server thread/WARN]:     at org.zotdev.plugins.kmc.listeners.PlayTime.onJoin(PlayTime.java:15)
    [14:38:27] [Server thread/WARN]:     at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    [14:38:27] [Server thread/WARN]:     at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    [14:38:27] [Server thread/WARN]:     at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    [14:38:27] [Server thread/WARN]:     at java.lang.reflect.Method.invoke(Unknown Source)
    [14:38:27] [Server thread/WARN]:     at org.bukkit.plugin.java.JavaPluginLoader$1.execute(JavaPluginLoader.java:306)
    [14:38:27] [Server thread/WARN]:     at org.bukkit.plugin.RegisteredListener.callEvent(RegisteredListener.java:62)
    [14:38:27] [Server thread/WARN]:     at org.bukkit.plugin.SimplePluginManager.fireEvent(SimplePluginManager.java:502)
    [14:38:27] [Server thread/WARN]:     at org.bukkit.plugin.SimplePluginManager.callEvent(SimplePluginManager.java:487)
    [14:38:27] [Server thread/WARN]:     at net.minecraft.server.v1_9_R2.PlayerList.onPlayerJoin(PlayerList.java:333)
    [14:38:27] [Server thread/WARN]:     at net.minecraft.server.v1_9_R2.PlayerList.a(PlayerList.java:159)
    [14:38:27] [Server thread/WARN]:     at net.minecraft.server.v1_9_R2.LoginListener.b(LoginListener.java:144)
    [14:38:27] [Server thread/WARN]:     at net.minecraft.server.v1_9_R2.LoginListener.c(LoginListener.java:54)
    [14:38:27] [Server thread/WARN]:     at net.minecraft.server.v1_9_R2.NetworkManager.a(NetworkManager.java:233)
    [14:38:27] [Server thread/WARN]:     at net.minecraft.server.v1_9_R2.ServerConnection.c(ServerConnection.java:140)
    [14:38:27] [Server thread/WARN]:     at net.minecraft.server.v1_9_R2.MinecraftServer.D(MinecraftServer.java:825)
    [14:38:27] [Server thread/WARN]:     at net.minecraft.server.v1_9_R2.DedicatedServer.D(DedicatedServer.java:399)
    [14:38:27] [Server thread/WARN]:     at net.minecraft.server.v1_9_R2.MinecraftServer.C(MinecraftServer.java:665)
    [14:38:27] [Server thread/WARN]:     at net.minecraft.server.v1_9_R2.MinecraftServer.run(MinecraftServer.java:564)
    [14:38:27] [Server thread/WARN]:     at java.lang.Thread.run(Unknown Source)
    [14:38:27] [Server thread/INFO]: 0
     
  8. @JKMODz show your current code
     
  9. So far this is the method im checking on join i fed it the player uuid:
    Code (Text):
        public static int getKills(UUID id) {
            try {
                ResultSet rs = plugin.c.createStatement().executeQuery("SELECT * FROM kmc WHERE uuid ='" + id.toString() + "';");
                if(rs.next()) {
                    return rs.getInt("kills");
                } else {
                    PreparedStatement ps = plugin.c.prepareStatement("INSERT INTO kmc (uuid, kills) VALUES (?, ?);");
                    ps.setString(1, id.toString());
                    ps.setInt(2, 0);
                    ps.executeQuery();
                    ps.close();
                }
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return 0;
        }
    The here is my code establishing the connection in the onEnable():
    Code (Text):
            try {
                Class.forName("com.mysql.jdbc.Driver");
                c = DriverManager.getConnection(url, getConfig().getString("MySQL.Username"),
                        getConfig().getString("MySQL.Password"));
                Statement statement = c.createStatement();
                c.prepareStatement("CREATE DATABASE IF NOT EXISTS kms_database").execute();
                statement.executeUpdate(
                        "CREATE TABLE IF NOT EXISTS kmc (UUID varchar(32),kills int,deaths int,currentks int,highestks int,playtime long"
                                + ",killsda int,killsla int,killsga int)");
                statement.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
     
  10. @JKMODz UUIDs without dashes have a length of 32. With them (which you get with .toString()) it has a length of 36. You created the table with uuid length of 32
     
  11. Use PreparedStatements. Ezpz

    Sent from my SGH-I747M using Tapatalk
     
  12. Just tried changing it to varchar(60) since that is the typical Java UUID length and still didn't work same error. its saying its a right syntax to use near then the uuid. This is actually confusing...
     
  13. @JKMODz it will always have a length of 36. Have you deleted the table first? As it will only recreate if it doesn't exist
     
  14. Alright cool got that working! I forgot to drop the table already on there this time and now i have, which i believe is the last error, "can not issue data manipulation statements with executeQuery().". So this is a issue with my prepared statement any thoughts?
     
  15. You need to use #executeUpdate for updating or inserting data

    Sent from my SGH-I747M using Tapatalk