Solved Error in SQL syntax

Discussion in 'Spigot Plugin Development' started by HockeyFreak062, Jul 8, 2021.

  1. Hey! I'm trying to create tables in my SQL database. But when I start the server up I get this error:

    [12:38:32] [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 '(LOCATION))' at line 1
    [12:38:32] [Server thread/WARN]: at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    [12:38:32] [Server thread/WARN]: at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:78)
    [12:38:32] [Server thread/WARN]: at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    [12:38:32] [Server thread/WARN]: at java.base/java.lang.reflect.Constructor.newInstanceWithCaller(Constructor.java:499)
    [12:38:32] [Server thread/WARN]: at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:480)
    [12:38:32] [Server thread/WARN]: at com.mysql.jdbc.Util.handleNewInstance(Util.java:403)
    [12:38:32] [Server thread/WARN]: at com.mysql.jdbc.Util.getInstance(Util.java:386)
    [12:38:32] [Server thread/WARN]: at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:944)
    [12:38:32] [Server thread/WARN]: at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3933)
    [12:38:32] [Server thread/WARN]: at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3869)
    [12:38:32] [Server thread/WARN]: at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2524)
    [12:38:32] [Server thread/WARN]: at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2675)
    [12:38:32] [Server thread/WARN]: at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2465)
    [12:38:32] [Server thread/WARN]: at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1915)
    [12:38:32] [Server thread/WARN]: at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2136)
    [12:38:32] [Server thread/WARN]: at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2070)
    [12:38:32] [Server thread/WARN]: at com.mysql.jdbc.PreparedStatement.executeLargeUpdate(PreparedStatement.java:5187)
    [12:38:32] [Server thread/WARN]: at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2055)
    [12:38:32] [Server thread/WARN]: at Main.SQL.SQLGetter.createTable(SQLGetter.java:26)
    [12:38:32] [Server thread/WARN]: at Main.Main.onEnable(Main.java:50)
    [12:38:32] [Server thread/WARN]: at org.bukkit.plugin.java.JavaPlugin.setEnabled(JavaPlugin.java:263)
    [12:38:32] [Server thread/WARN]: at org.bukkit.plugin.java.JavaPluginLoader.enablePlugin(JavaPluginLoader.java:342)
    [12:38:32] [Server thread/WARN]: at org.bukkit.plugin.SimplePluginManager.enablePlugin(SimplePluginManager.java:480)
    [12:38:32] [Server thread/WARN]: at org.bukkit.craftbukkit.v1_17_R1.CraftServer.enablePlugin(CraftServer.java:495)
    [12:38:32] [Server thread/WARN]: at org.bukkit.craftbukkit.v1_17_R1.CraftServer.enablePlugins(CraftServer.java:409)
    [12:38:32] [Server thread/WARN]: at net.minecraft.server.MinecraftServer.loadWorld(MinecraftServer.java:608)
    [12:38:32] [Server thread/WARN]: at net.minecraft.server.dedicated.DedicatedServer.init(DedicatedServer.java:264)
    [12:38:32] [Server thread/WARN]: at net.minecraft.server.MinecraftServer.x(MinecraftServer.java:987)
    [12:38:32] [Server thread/WARN]: at net.minecraft.server.MinecraftServer.lambda$0(MinecraftServer.java:307)
    [12:38:32] [Server thread/WARN]: at java.base/java.lang.Thread.run(Thread.java:831)

    My code here is:
    Code (Java):


    private Main plugin;
    public SQLGetter(Main plugin){
        this.plugin = plugin;
    }

    public void createTable(){
        PreparedStatement ps;
        PreparedStatement ps1;

        try{
            ps = plugin.SQL.getConnection().prepareStatement("CREATE TABLE IF NOT EXISTS prisons "
                    + "(NAME VARCHAR(100), UUID VARCHAR(100), XP INT(100), LEVEL INT(100), MONEY INT(100)," +
                    " COAL INT(100), IRON INT(100), LAPIS INT(100), GOLD INT(100), DIAMOND INT(100)" +
                    ", EMERALD INT(100) PRIMARY KEY (NAME))");
           [Line26 of Main.SQL.SQLGetter] ps.executeUpdate();
            ps1 = plugin.SQL.getConnection().prepareStatement("CREATE TABLE IF NOT EXISTS locations " +
                    "(LOCATION VARCHAR(100), X INT(100), Y INT(100), Z INT(100)," +
                    " YAW INT(100), PITCH INT(100) PRIMARY KEY (LOCATION))");
            ps1.executeUpdate();
        } catch (SQLException e){
            e.printStackTrace();
        }
    }

    if (SQL.isConnected()){
        Bukkit.getLogger().info("Database is connected!");
        [Line 50 in Main] data.createTable();
    }
     
    I hope someone can help me, Database is starting up. First time of starting the plugin it created the "prisons" table, after the next restarts it gave me this error and does not create the "Locations" table....
     
  2. Hi, what is on line 26?
     
  3. You forgot the comma before the primary key
     
  4. You need a comma before that PRIMARY KEY statement.
    You can use sites like this to check and validate your statements:
    https://www.eversql.com/sql-syntax-check-validator/
     
  5. Omg im so stupid.... Thanks! This solved it :D
     
    • Like Like x 1