Solved MySQL Help

Discussion in 'Plugin Development' started by Wizehh, Jan 27, 2014.

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

    Wizehh

  2. Offline

    calebbfmv

    [18:52:03 WARN]: at com.tribes.tribes.Tribe.isInTribe(Tribe.java:197)
    [
    18:52:03 WARN]: at com.tribes.tcommands.Info.onCommand(Info.java:20)
    [
    18:52:03 WARN]: at com.tribes.tribes.CommandManager.onCommand(CommandManager.java:80)

    See whats going on with those lines.
     
  3. Offline

    1Rogue

    The disconnect was the driver itself. Instanciation of SQL instances from a separate thread and execution are def the cleanest solution to this problem, which was already suggested (on a phone ATM)
     
    L33m4n123 likes this.
  4. Offline

    Wizehh

    1Rogue feed me please?

    1.
    Code:java
    1. result = stmt.executeQuery();

    2.
    Code:java
    1. if (!Tribe.isInTribe(p)) {

    3.
    Code:java
    1. target.onCommand(p, args);


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

    calebbfmv

    OHHHHH
    That helps me so much....
    Listen, you are making multiple instances of your SQL, my guess is poor OOP. Thats your issue, now fix it.
     
  6. Offline

    Wizehh

    calebbfmv Uhm..
    That's not.. that's not very helpful :/
     
  7. Offline

    calebbfmv

    Wizehh
    Neither is you putting out of context code.
    If you want help, post full code.
     
  8. Offline

    Wizehh

    calebbfmv
    Code:java
    1. try {
    2. if (Main.connection == null) {
    3. Main.openConnection();
    4. }
    5.  
    6. if (Main.playerDataContainsPlayer(p)) {
    7. PreparedStatement sql = Main.connection.prepareStatement("SELECT tribe FROM `player_data` WHERE player=?;");
    8. sql.setString(1, p.getName());
    9.  
    10. ResultSet result = sql.executeQuery();
    11. result.next();
    12.  
    13. PreparedStatement tribeUpdate = Main.connection.prepareStatement("UPDATE `player_data` SET tribe=? WHERE player=?;");
    14. tribeUpdate.setString(1, "****");
    15. tribeUpdate.setString(2, p.getName());
    16. tribeUpdate.executeUpdate();
    17.  
    18. tribeUpdate.close();
    19. sql.close();
    20. result.close();
    21. }
    22.  

    Edit: Sorry about the formatting.

    Hmm. I even went into the MySQL workbench and ran this:
    PHP:
    SET SESSION wait_timeout 99999999999
    ;unfortunately, I don't think that helped.

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

    1Rogue

    Post the full class with the code you just posted.
     
  10. Offline

    Wizehh

    I think this is enough code:
    Code:java
    1. public synchronized static void openConnection() {
    2. try {
    3. connection = DriverManager.getConnection("jdbc:mysql://mrctribes.db.12279463.hostedresource.com:3306/mrctribes?autoReconnect=true", "mrctribes", "Riceco10#");
    4. Console.info("MySQL connection opened.");
    5. } catch (Exception e) {
    6. Console.severe("SQL Connection could not be opened!");
    7. e.printStackTrace();
    8. }
    9. }
    10.  
    11. public synchronized static void closeConnection() {
    12. try {
    13. connection.close();
    14. Console.info("SQL Connection has been closed!");
    15. } catch (Exception e) {
    16. Console.severe("Connection could not be closed!");
    17. e.printStackTrace();
    18. }
    19. }
    20.  
    21. public synchronized static boolean playerDataContainsPlayer(Player p) {
    22.  
    23. try {
    24. if (Main.connection == null) {
    25. Main.openConnection();
    26. }
    27. PreparedStatement ps = connection.prepareStatement("SELECT * FROM `player_data` WHERE player=?;");
    28. ps.setString(1, p.getName());
    29. ResultSet rs = ps.executeQuery();
    30. boolean containsPlayer = rs.next();
    31.  
    32. ps.close();
    33. rs.close();
    34. return containsPlayer;
    35. } catch (Exception e) {
    36. e.printStackTrace();
    37. return false;
    38. }
    39. }


    Code:java
    1. public static void addToTribe(TribeType type, Player p){
    2.  
    3. switch (type){
    4.  
    5. case WATER:
    6. try {
    7. if (Main.connection == null) {
    8. Main.openConnection();
    9. }
    10.  
    11. if (Main.playerDataContainsPlayer(p)) {
    12. PreparedStatement sql = Main.connection.prepareStatement("SELECT tribe FROM `player_data` WHERE player=?;");
    13. sql.setString(1, p.getName());
    14.  
    15. ResultSet result = sql.executeQuery();
    16. result.next();
    17.  
    18. PreparedStatement tribeUpdate = Main.connection.prepareStatement("UPDATE `player_data` SET tribe=? WHERE player=?;");
    19. tribeUpdate.setString(1, "****");
    20. tribeUpdate.setString(2, p.getName());
    21. tribeUpdate.executeUpdate();
    22.  
    23. tribeUpdate.close();
    24. sql.close();
    25. result.close();
    26. }
    27.  
    28. ********;
    29.  
    30. } catch (Exception e) {
    31. e.printStackTrace();
    32. return;
    33. } finally {
    34. // Main.closeConnection();
    35. }
    36.  
    37. break;

    Edit: :mad:

    -bump-

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

    -_Husky_-

  12. Offline

    Wizehh

    -_Husky_- Same problem :/
    Show Spoiler

    PHP:
    [17:30:02 WARN]: com.mysql.jdbc.exceptions.jdbc4.CommunicationsExceptionCommunications link failure
     
    The last packet successfully received from the server was 577
    ,069 milliseconds ago.  The last packet sent successfu
    lly to the server was 5 milliseconds ago
    .
    [
    17:30:02 WARN]:        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    [
    17:30:02 WARN]:        at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
    [
    17:30:02 WARN]:        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
    [
    17:30:02 WARN]:        at java.lang.reflect.Constructor.newInstance(Unknown Source)
    [
    17:30:02 WARN]:        at com.mysql.jdbc.Util.handleNewInstance(Util.java:407)
    [
    17:30:02 WARN]:        at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1116)
    [
    17:30:02 WARN]:        at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3082)
    [
    17:30:02 WARN]:        at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2968)
    [
    17:30:02 WARN]:        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3516)
    [
    17:30:02 WARN]:        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1986)
    [
    17:30:02 WARN]:        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2140)
    [
    17:30:02 WARN]:        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2626)
    [
    17:30:02 WARN]:        at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2111)
    [
    17:30:02 WARN]:        at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2273)
    [
    17:30:02 WARN]:        at com.tribes.tribes.Tribe.isInTribe(Tribe.java:182)
    [
    17:30:02 WARN]:        at com.tribes.tribes.Tribe.getTribe(Tribe.java:213)
    [
    17:30:02 WARN]:        at com.tribes.tcommands.Info.onCommand(Info.java:24)
    [
    17:30:02 WARN]:        at com.tribes.tribes.CommandManager.onCommand(CommandManager.java:80)
    [
    17:30:02 WARN]:        at org.bukkit.command.PluginCommand.execute(PluginCommand.java:44)
    [
    17:30:02 WARN]:        at org.bukkit.command.SimpleCommandMap.dispatch(SimpleCommandMap.java:196)
    [
    17:30:02 WARN]:        at org.bukkit.craftbukkit.v1_7_R1.CraftServer.dispatchCommand(CraftServer.java:542)
    [
    17:30:02 WARN]:        at net.minecraft.server.v1_7_R1.PlayerConnection.handleCommand(PlayerConnection.java:932)
    [
    17:30:02 WARN]:        at net.minecraft.server.v1_7_R1.PlayerConnection.a(PlayerConnection.java:814)
    [
    17:30:02 WARN]:        at net.minecraft.server.v1_7_R1.PacketPlayInChat.a(PacketPlayInChat.java:28)
    [
    17:30:02 WARN]:        at net.minecraft.server.v1_7_R1.PacketPlayInChat.handle(PacketPlayInChat.java:47)
    [
    17:30:02 WARN]:        at net.minecraft.server.v1_7_R1.NetworkManager.a(NetworkManager.java:146)
    [
    17:30:02 WARN]:        at net.minecraft.server.v1_7_R1.ServerConnection.c(SourceFile:134)
    [
    17:30:02 WARN]:        at net.minecraft.server.v1_7_R1.MinecraftServer.u(MinecraftServer.java:655)
    [
    17:30:02 WARN]:        at net.minecraft.server.v1_7_R1.DedicatedServer.u(DedicatedServer.java:250)
    [
    17:30:02 WARN]:        at net.minecraft.server.v1_7_R1.MinecraftServer.t(MinecraftServer.java:545)
    [
    17:30:02 WARN]:        at net.minecraft.server.v1_7_R1.MinecraftServer.run(MinecraftServer.java:457)
    [
    17:30:02 WARN]:        at net.minecraft.server.v1_7_R1.ThreadServerApplication.run(SourceFile:617)
    [
    17:30:02 WARN]: Caused byjava.io.EOFExceptionCan not read response from serverExpected to read 4 bytesread
    0 bytes before connection was unexpectedly lost
    .
    [
    17:30:02 WARN]:        at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:2529)
    [
    17:30:02 WARN]:        at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2979)
    [
    17:30:02 WARN]:        ... 25 more
    [17:30:02 INFO]: An error has occurednull
    [17:30:02 WARN]: java.lang.NullPointerException
    [17:30:02 WARN]:        at com.tribes.tcommands.Info.onCommand(Info.java:24)
    [
    17:30:02 WARN]:        at com.tribes.tribes.CommandManager.onCommand(CommandManager.java:80)
    [
    17:30:02 WARN]:        at org.bukkit.command.PluginCommand.execute(PluginCommand.java:44)
    [
    17:30:02 WARN]:        at org.bukkit.command.SimpleCommandMap.dispatch(SimpleCommandMap.java:196)
    [
    17:30:02 WARN]:        at org.bukkit.craftbukkit.v1_7_R1.CraftServer.dispatchCommand(CraftServer.java:542)
    [
    17:30:02 WARN]:        at net.minecraft.server.v1_7_R1.PlayerConnection.handleCommand(PlayerConnection.java:932)
    [
    17:30:02 WARN]:        at net.minecraft.server.v1_7_R1.PlayerConnection.a(PlayerConnection.java:814)
    [
    17:30:02 WARN]:        at net.minecraft.server.v1_7_R1.PacketPlayInChat.a(PacketPlayInChat.java:28)
    [
    17:30:02 WARN]:        at net.minecraft.server.v1_7_R1.PacketPlayInChat.handle(PacketPlayInChat.java:47)
    [
    17:30:02 WARN]:        at net.minecraft.server.v1_7_R1.NetworkManager.a(NetworkManager.java:146)
    [
    17:30:02 WARN]:        at net.minecraft.server.v1_7_R1.ServerConnection.c(SourceFile:134)
    [
    17:30:02 WARN]:        at net.minecraft.server.v1_7_R1.MinecraftServer.u(MinecraftServer.java:655)
    [
    17:30:02 WARN]:        at net.minecraft.server.v1_7_R1.DedicatedServer.u(DedicatedServer.java:250)
    [
    17:30:02 WARN]:        at net.minecraft.server.v1_7_R1.MinecraftServer.t(MinecraftServer.java:545)
    [
    17:30:02 WARN]:        at net.minecraft.server.v1_7_R1.MinecraftServer.run(MinecraftServer.java:457)
    [
    17:30:02 WARN]:        at net.minecraft.server.v1_7_R1.ThreadServerApplication.run(SourceFile:617)


    1Rogue

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

    calebbfmv

    Wizehh
    Stop
    Bumping
    Every
    HOUR!
     
  14. Offline

    Wizehh

    -.-
     
  15. Offline

    1Rogue

    Same problem? Can you show us your implementation at this very moment then?

    If you aren't willing to post relevant code, I cannot help you. I'd ask that you not tag me until then.
     
    -_Husky_- likes this.
  16. Offline

    GaaTavares

    It may look weird but what about if you rewrite your code? Use -_Husky_- example, bumping isn't cool at all, we know your issue isn't solved, just wait until someone reply :/
     
  17. Offline

    Wizehh

    1Rogue
    Code:java
    1. public static MySQL MySQL = new MySQL(plugin, "mrctribes.db.12279463.hostedresource.com", "3306", "mrctribes?autoReconnect=true", "mrctribes", "Riceco10#");
    2. public static Connection c = null;
    3.  
    4. public synchronized static boolean playerDataContainsPlayer(Player p) throws SQLException, Exception {
    5.  
    6. try {
    7. PreparedStatement ps = c.prepareStatement("SELECT * FROM `player_data` WHERE player=?;");
    8. ps.setString(1, p.getName());
    9. ResultSet rs = ps.executeQuery();
    10. boolean containsPlayer = rs.next();
    11. ps.close();
    12. rs.close();
    13. return containsPlayer;
    14. } catch (Exception e) {
    15. e.printStackTrace();
    16. }
    17. return false;
    18. }
     
  18. Offline

    1Rogue

    And your MySQL class?
     
  19. Offline

    Maurdekye

    1Rogue It's not a Java problem, i'm telling you. There's something up with the server he's hosting, or the Java version he's using. There's nothing wrong with his code.
     
  20. Offline

    1Rogue

    Code:
    [13:47:27 WARN]: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed.Connection was implicitly closed by the driver.
    This is usually caused by using a consistant connection (rather than opening when needed). However the OP refuses to listen to that as a solution. So I've got nothing.
     
  21. Offline

    Maurdekye

    1Rogue I use a consistent connection, and have no problems. I don't open and close it as necessary because I've heard that creates abundant amounts of lag.
     
  22. Offline

    1Rogue


    It will only create lag if you utilize it on the main thread...

    As long as you do it correctly it's perfectly efficient.
     
  23. Offline

    Maurdekye

    1Rogue How do you suggest I do it correctly? I'm not experienced in multithreading.
     
  24. Offline

    1Rogue


    Build your API and then call it from tasks/threads
     
  25. Offline

    Maurdekye

    1Rogue You must be an expert at making vague, mysterious replies. We're not all Java gurus on this forum.
     
    Wizehh likes this.
  26. Offline

    1Rogue


    Make whatever methods you need for calling specific queries/etc, and then whenever you need to use an SQL query, just use Bukkit's Scheduler: http://wiki.bukkit.org/Scheduler_Programming
     
  27. Offline

    Maurdekye

    1Rogue Huh. I tried making an API for that once, but a big problem arose in creating a valid datatype that resembles a ResultSet and doesn't have to be closed.
     
  28. Offline

    SuperOmegaCow

    Maurdekye
    this is my code to save data to my data base:
    Code:java
    1. public class ThreadUp implements Runnable {
    2.  
    3. public boolean running = false;
    4. Object[] data = null;
    5. private PreparedStatement sql = null;
    6. private ResultSet res = null;
    7. private boolean contains = false;
    8.  
    9. public ThreadUp(Object[] data) {
    10. Thread thread = new Thread(this);
    11. this.data = data;
    12. thread.start();
    13. }
    14.  
    15. @Override
    16. public void run() {
    17. this.running = true;
    18. Bukkit.getPluginManager().callEvent(new ThreadUpEvent(this.running, this, (String) data[0]));
    19. try {
    20. sql = Main.getInstance().c.prepareStatement("SELECT * FROM thecube WHERE user_id=?;");
    21. sql.setString(1, (String) data[0]);
    22. res = sql.executeQuery();
    23. if (res.next()) {
    24. this.contains = true;
    25. }
    26. sql.close();
    27. res.close();
    28. if (contains) {
    29. sql = Main.getInstance().c.prepareStatement("UPDATE `globalplayerdata`.`thecube` SET kills=?, deaths=?, games_played=?, wins=?, cubeits=?, vip=?, bought_classes=? WHERE user_id=?;");
    30. sql.setString(8, (String) data[0]);
    31. sql.setInt(1, (Integer) data[1]);
    32. sql.setInt(2, (Integer) data[2]);
    33. sql.setInt(3, (Integer) data[3]);
    34. sql.setInt(4, (Integer) data[4]);
    35. sql.setInt(5, (Integer) data[5]);
    36. sql.setBoolean(6, (Boolean) data[6]);
    37. sql.setString(7, (String) data[7]);
    38. sql.executeUpdate();
    39. } else {
    40. sql = Main.getInstance().c.prepareStatement("INSERT INTO `globalplayerdata`.`thecube` (`user_id`, `kills`, `deaths`, `games_played`, `wins`, `cubeits`, `vip`, `bought_classes`) VALUES (?,?,?,?,?,?,?,?);");
    41. sql.setString(1, (String) data[0]);
    42. sql.setInt(2, (Integer) data[1]);
    43. sql.setInt(3, (Integer) data[2]);
    44. sql.setInt(4, (Integer) data[3]);
    45. sql.setInt(5, (Integer) data[4]);
    46. sql.setInt(6, (Integer) data[5]);
    47. sql.setBoolean(7, (Boolean) data[6]);
    48. sql.setString(8, (String) data[7]);
    49. sql.executeUpdate();
    50. }
    51. } catch (SQLException e) {
    52. e.printStackTrace();
    53. } catch (NullPointerException e) {
    54. e.printStackTrace();
    55. } finally {
    56. try {
    57. if (res != null) {
    58. res.close();
    59. }
    60. if (sql != null) {
    61. sql.close();
    62. }
    63. } catch (SQLException e) {
    64. e.printStackTrace();
    65. }
    66. }
    67. this.running = false;
    68. Bukkit.getPluginManager().callEvent(new ThreadUpEvent(this.running, this, (String) data[0]));
    69. }
    70. }


    and to start it just simply
    new ThreadUp(your data object);
     
  29. Offline

    1Rogue


    You simply return out of the method. An example from some of my (very) old code:

    Code:java
    1. public Map<String, Integer> getTopPlayers(String data, byte amount) {
    2. this.db = new MySQL();
    3. Map<String, Integer> players = new HashMap();
    4. try {
    5. this.db.open();
    6. ResultSet result = this.db.query("SELECT * FROM `playTime` ORDER BY `" + data + "` DESC LIMIT " + amount);
    7. boolean end = false;
    8. while (!end) {
    9. if (result.next()) {
    10. players.put(result.getString("username"), result.getInt(data));
    11. } else {
    12. end = true;
    13. }
    14. }
    15. } catch (SQLException e) {
    16. this.plugin.getLogger().log(Level.SEVERE, "{0}", this.plugin.getDebug() >= 3 ? e : "null");
    17. } finally {
    18. this.db.close();
    19. }
    20. return players;
    21. }
     
  30. Offline

    Maurdekye

    1Rogue Sure, but that's for a specific case. I'd like to make a method that takes a query as input, and returns every bit of data from the ResultSet.
     
Thread Status:
Not open for further replies.

Share This Page