Solved MySQL Help

Discussion in 'Plugin Development' started by The Fancy Whale, Jul 27, 2014.

Thread Status:
Not open for further replies.
  1. Offline

    The Fancy Whale

    For some reason, when I check this boolean, there is an error.
    Code:
    Code:java
    1. public static boolean playerExists(String playername) {
    2. try {
    3. ResultSet res = s.executeQuery("SELECT * FROM playerpoints WHERE username = '" + playername + "';");//line 152
    4. if(res.next()) {
    5. if(res.getString("username") == null) {
    6. return false;
    7. } else {
    8. return true;
    9. }
    10. } else {
    11. return false;
    12. }
    13. } catch (SQLException e1) {
    14. e1.printStackTrace();
    15. }
    16. return false;
    17. }

    Error:
    Code:
    27.07 15:58:32 [Multicraft] Skipped 63 lines due to rate limit (30/s)
    27.07 15:58:25 [Server] INFO [15:58:25 WARN]:    at net.minecraft.server.v1_7_R3.MinecraftServer.run(MinecraftServer.java:489)
    27.07 15:58:25 [Server] INFO [15:58:25 WARN]:    at net.minecraft.server.v1_7_R3.MinecraftServer.u(MinecraftServer.java:583)
    27.07 15:58:25 [Server] INFO [15:58:25 WARN]:    at net.minecraft.server.v1_7_R3.DedicatedServer.v(DedicatedServer.java:283)
    27.07 15:58:25 [Server] INFO [15:58:25 WARN]:    at net.minecraft.server.v1_7_R3.MinecraftServer.v(MinecraftServer.java:720)
    27.07 15:58:25 [Server] INFO [15:58:25 WARN]:    at net.minecraft.server.v1_7_R3.ServerConnection.c(ServerConnection.java:81)
    27.07 15:58:25 [Server] INFO [15:58:25 WARN]:    at net.minecraft.server.v1_7_R3.NetworkManager.a(NetworkManager.java:180)
    27.07 15:58:25 [Server] INFO [15:58:25 WARN]:    at net.minecraft.server.v1_7_R3.PacketPlayInChat.handle(PacketPlayInChat.java:65)
    27.07 15:58:25 [Server] INFO [15:58:25 WARN]:    at net.minecraft.server.v1_7_R3.PacketPlayInChat.a(PacketPlayInChat.java:28)
    27.07 15:58:25 [Server] INFO [15:58:25 WARN]:    at net.minecraft.server.v1_7_R3.PlayerConnection.a(PlayerConnection.java:830)
    27.07 15:58:25 [Server] INFO [15:58:25 WARN]:    at net.minecraft.server.v1_7_R3.PlayerConnection.handleCommand(PlayerConnection.java:999)
    27.07 15:58:25 [Server] INFO [15:58:25 WARN]:    at org.bukkit.craftbukkit.v1_7_R3.CraftServer.dispatchCommand(CraftServer.java:726)
    27.07 15:58:25 [Server] INFO [15:58:25 WARN]:    at org.bukkit.command.SimpleCommandMap.dispatch(SimpleCommandMap.java:180)
    27.07 15:58:25 [Server] INFO [15:58:25 WARN]:    at org.bukkit.command.PluginCommand.execute(PluginCommand.java:44)
    27.07 15:58:25 [Server] INFO [15:58:25 WARN]:    at tfw.MGTokens.MGTokens.onCommand(MGTokens.java:96)
    27.07 15:58:25 [Server] INFO [15:58:25 WARN]:    at tfw.MGTokens.MGTokens.checkCoins(MGTokens.java:175)
    27.07 15:58:25 [Server] INFO [15:58:25 WARN]:    at tfw.MGTokens.MGTokens.playerExists(MGTokens.java:152)
    27.07 15:58:25 [Server] INFO [15:58:25 WARN]:    at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1339)
    27.07 15:58:25 [Server] INFO [15:58:25 WARN]:    at com.mysql.jdbc.StatementImpl.checkClosed(StatementImpl.java:402)
    27.07 15:58:25 [Server] INFO [15:58:25 WARN]:    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:927)
    27.07 15:58:25 [Server] INFO [15:58:25 WARN]:    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:982)
    27.07 15:58:25 [Server] INFO [15:58:25 WARN]:    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:987)
    27.07 15:58:25 [Server] INFO [15:58:25 WARN]:    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1013)
    27.07 15:58:25 [Server] INFO [15:58:25 WARN]:    at com.mysql.jdbc.Util.getInstance(Util.java:382)
    27.07 15:58:25 [Server] INFO [15:58:25 WARN]:    at com.mysql.jdbc.Util.handleNewInstance(Util.java:407)
    27.07 15:58:25 [Server] INFO [15:58:25 WARN]:    at java.lang.reflect.Constructor.newInstance(Unknown Source)
    27.07 15:58:25 [Server] INFO [15:58:25 WARN]:    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
    27.07 15:58:25 [Server] INFO [15:58:25 WARN]:    at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
    27.07 15:58:25 [Server] INFO [15:58:25 WARN]:    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    27.07 15:58:25 [Server] INFO [15:58:25 WARN]: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after statement closed.
    27.07 15:58:25 [Multicraft] Skipped 52 lines due to rate limit (30/s)
    27.07 15:58:19 [Server] INFO [15:58:19 WARN]:    at org.bukkit.plugin.SimplePluginManager.callEvent(SimplePluginManager.java:487)
    27.07 15:58:19 [Server] INFO [15:58:19 WARN]:    at org.bukkit.plugin.SimplePluginManager.fireEvent(SimplePluginManager.java:502)
    27.07 15:58:19 [Server] INFO [15:58:19 WARN]:    at org.bukkit.plugin.TimedRegisteredListener.callEvent(TimedRegisteredListener.java:30)
    27.07 15:58:19 [Server] INFO [15:58:19 WARN]:    at org.bukkit.plugin.RegisteredListener.callEvent(RegisteredListener.java:62)
    27.07 15:58:19 [Server] INFO [15:58:19 WARN]:    at org.bukkit.plugin.java.JavaPluginLoader$1.execute(JavaPluginLoader.java:292)
    27.07 15:58:19 [Server] INFO [15:58:19 WARN]:    at java.lang.reflect.Method.invoke(Unknown Source)
    27.07 15:58:19 [Server] INFO [15:58:19 WARN]:    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    27.07 15:58:19 [Server] INFO [15:58:19 WARN]:    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    27.07 15:58:19 [Server] INFO [15:58:19 WARN]:    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    27.07 15:58:19 [Server] INFO [15:58:19 WARN]:    at tfw.MGTokens.MGTokens.onPlayerJoin(MGTokens.java:137)
    27.07 15:58:19 [Server] INFO [15:58:19 WARN]:    at tfw.MGTokens.MGTokens.createPlayer(MGTokens.java:141)
    27.07 15:58:19 [Server] INFO [15:58:19 WARN]:    at tfw.MGTokens.MGTokens.playerExists(MGTokens.java:152)
    27.07 15:58:19 [Server] INFO [15:58:19 WARN]:    at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1474)
    27.07 15:58:19 [Server] INFO [15:58:19 WARN]:    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2570)
    27.07 15:58:19 [Server] INFO [15:58:19 WARN]:    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2620)
    27.07 15:58:19 [Server] INFO [15:58:19 WARN]:    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2140)
    27.07 15:58:19 [Server] INFO [15:58:19 WARN]:    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1986)
    27.07 15:58:19 [Server] INFO [15:58:19 WARN]:    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3516)
    27.07 15:58:19 [Server] INFO [15:58:19 WARN]:    at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2968)
    27.07 15:58:19 [Server] INFO [15:58:19 WARN]:    at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3082)
    27.07 15:58:19 [Server] INFO [15:58:19 WARN]:    at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1116)
    27.07 15:58:19 [Server] INFO [15:58:19 WARN]:    at com.mysql.jdbc.Util.handleNewInstance(Util.java:407)
    27.07 15:58:19 [Server] INFO [15:58:19 WARN]:    at java.lang.reflect.Constructor.newInstance(Unknown Source)
    27.07 15:58:19 [Server] INFO [15:58:19 WARN]:    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
    27.07 15:58:19 [Server] INFO [15:58:19 WARN]:    at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
    27.07 15:58:19 [Server] INFO [15:58:19 WARN]:    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
     
  2. Offline

    mythbusterma

    The Fancy Whale

    First and foremost, do not use MySQL on the main thread, it will hang the entire sever for an indeterminate amount of time while the remote server replies.

    Second, you should have "s" declared inside the method, only cache the connection, if that is the connection you should create a method to asuure the connection is still usable before handing it to the method (this is your error).

    Third, use prepared statements when doing SQL, it looks cleaner and it helps prevent injection.
     
    The Fancy Whale likes this.
  3. Offline

    The Fancy Whale

    I would suggest you create your own thread if you would like to get some answers on your questions.
    mythbusterma What if the connection is not usable? What should I do then?

    Editing code to change methods and open/close the connection when required.

    EDIT by Moderator: merged posts, please use the edit button instead of double posting.
     
    Last edited by a moderator: Jun 9, 2016
  4. Offline

    mythbusterma

    The Fancy Whale

    The connection to the database is automatically closed after some time, you must keep it alive.
     
    The Fancy Whale likes this.
  5. Offline

    Excalibur

    Keeping your MySQL info saved into variables like that is very dangerous, especially considering they are static. At the very least make them private.

    I suggest keeping some Strings in your config that store these values and have them set in your onEnable. As for the error, the error clearly says that it has timed out.

    I personally am a huge MySQL fan from previous game API I've worked on, since yesterday. I discovered how easy it is to manipulate the YAML API methods to do exactly what you are doing in MySQL, except much easier.
     
    The Fancy Whale likes this.
  6. Offline

    The Fancy Whale

    Oh okay, so what I could do is make a method that re-opens the connection. And every time I use the connection I check if it is closed, and if it is closed run the re-open method?

    Was just storing them like that for testing purposes. Going to change the MySQL password right now. Forgot to blur it out like I originally planned.

    EDIT by Moderator: merged posts, please use the edit button instead of double posting.
     
    Last edited by a moderator: Jun 9, 2016
  7. Offline

    Excalibur


    Just create a new repeating task that runs x amount of times every hour. You haven't told us how long it takes to time out, but manipulate the end long accordingly.

    Code:
        public static void mySQLKeepAlive() {
            Bukkit.getServer().getScheduler().scheduleSyncRepeatingTask(<your plugin instance>, new BukkitRunnable() {
                public void run() {
                    // Check if connection is timed out and reconnect.
                }
            }, 0L, 40L);
        }

    If you are going to continue to use MySQL, give this a read and apply it to one master class that handles all your SQL.

    http://www.javaworld.com/article/2073352/core-java/simply-singleton.html

    EDIT by Moderator: merged posts, please use the edit button instead of double posting.
     
    Last edited by a moderator: Jun 9, 2016
  8. Offline

    The Fancy Whale

    I got it working by just adding a check to see if the connection was open. If the connection is not open I re-open it. Rather than running a timer. Thanks for the help!
    Excalibur Thanks for all the help!
     
  9. Offline

    mythbusterma

    Or, you know, don't create unnecessary singletons which can have adverse effects instead of using the normal, more sane way of having a main class that stores an instance of it.
     
  10. Offline

    Excalibur


    How is creating a Singleton for a security heavy function like MySQL unnecessary? It is a common practice with many of today's business applications.

    Please enlighten me on the adverse affects.
     
  11. Offline

    mythbusterma

  12. Offline

    Excalibur

Thread Status:
Not open for further replies.

Share This Page