Solved BungeeCord Async MySQL Updating Problem

Discussion in 'Spigot Plugin Development' started by Chilybones, Aug 17, 2018.

  1. Currently, I have a BungeeCord server that updates coins that players receive via in-game to MySQL when they leave and gets the coins when they join another server. I did some digging to find an answer and I had read that for BungeeCord servers, the PlayerJoinEvent on the new server is called before the PlayerQuitEvent on the old server, as BungeeCord doesn't disconnect you until you are connected to the new server.

    After reading that, I modified the code a bit, so when you go to connect to a new server a 100 tick delay is applied (5 seconds), and during this async task is when the .executeUpdate method is run. Once the 5-second delay expires, it connects you. However, this did not fix my problem, and the new server still grabs old data.

    The only way it updates on all servers is if the player physically quits to the server list, and reconnects. I'm open to any ideas or suggestions that might resolve this. Thank you.

    Update from Old Server
    Code (Java):

    private static final HashMap<Player, Integer> playerTimer = new HashMap<>();
    private static final HashMap<Player, String> playerServer = new HashMap<>();

    public static void addPlayer(Player player, int time){
        if (!playerTimer.containsKey(player)) {
            Bukkit.getConsoleSender().sendMessage("Updating coins...");
            new CoinEvents().updateCoins(player);
            Bukkit.getConsoleSender().sendMessage("Coins Updated");
            playerTimer.put(player, time);
        }
    }

    public BukkitRunnable connectDelay(){
            BukkitRunnable r = new BukkitRunnable() {
                @Override
                public void run() {
                    for (Player player: playerTimer.keySet()){
                        int timer = playerTimer.get(player);
                        if (timer == 0) {
                            ByteArrayDataOutput out = ByteStreams.newDataOutput();
                            out.writeUTF("Connect");
                            out.writeUTF("hub");

                            player.sendPluginMessage(IcefuseHubCMD.get(), "BungeeCord", out.toByteArray());
                            player.sendMessage(ChatColor.GREEN + "Connecting you to the hub...");
                            playerTimer.remove(player);
                        } else {
                            timer--;
                            playerTimer.put(player, timer);
                        }
                    }
                }
            };
            return r;
        }

    public BukkitRunnable updateCoins(Player player){
        SQLClass sql = new SQLClass();
        BukkitRunnable r = new BukkitRunnable() {
            @Override
            public void run() {
                try {
                    sql.openConnection();

                    Statement statement = sql.getConnection().createStatement();
                    int totalCoins = db.getInt("local_sql_cache", player.getUniqueId().toString() + ".total_coins"); // This is a cache that stores the coins from the MySQL server.
                    int earnedCoins = db.getInt("players", player.getUniqueId().toString() + ".coins"); // This is the coins they earned during the game.
                    int sumCoins = totalCoins + earnedCoins;

                    statement.executeUpdate("UPDATE PlayerCoins SET total_coins=" + sumCoins + " WHERE player_uuid = '" + player.getUniqueId() + "';");
                    Bukkit.getConsoleSender().sendMessage(ChatColor.GREEN + "Successfully updated coins for " + player.getDisplayName());
                    db.set("local_sql_cache", player.getUniqueId().toString(), null);
                    db.set("players", player.getUniqueId().toString(), null);
                } catch (ClassNotFoundException e){
                    e.printStackTrace();
                } catch (SQLException e){
                    Bukkit.getConsoleSender().sendMessage(ChatColor.RED + "FAILED to update coins for " + player.getDisplayName());
                    e.printStackTrace();
                } finally {
                    sql.closeConnection();
                }
            }
        };
        return r;
    }
     
    Get from New Server
    Code (Java):
    public BukkitRunnable getCoins(Player player){
            SQLClass sql = new SQLClass();
            BukkitRunnable r = new BukkitRunnable() {
                @Override
                public void run() {
                    try {
                      sql.openConnection();
                      Statement statement = sql.getConnection().createStatement();

                      Bukkit.getConsoleSender().sendMessage(ChatColor.YELLOW + "Fetching coins for " + player.getDisplayName() + "...");
                      ResultSet result = statement.executeQuery("SELECT total_coins FROM PlayerCoins WHERE player_uuid = '" + player.getUniqueId() + "';");
                      result.next();
                      int totalCoins = result.getInt("total_coins");
                      db.set("local_sql_cache", player.getUniqueId().toString() + ".total_coins", totalCoins);
                      Bukkit.getConsoleSender().sendMessage(ChatColor.GREEN + "Successfully fetched coins for " + player.getDisplayName());
                    } catch (ClassNotFoundException e){
                        e.printStackTrace();
                    } catch (SQLException e){
                        Bukkit.getConsoleSender().sendMessage(ChatColor.RED + "FAILED to fetch coins for " + player.getDisplayName() +". Possible new player");
                        e.printStackTrace();
                        Bukkit.getConsoleSender().sendMessage(ChatColor.YELLOW + "Attempting to create new user information for coins");
                        createUser(player).runTaskAsynchronously(plugin);
                    } finally {
                        sql.closeConnection();
                    }
                }
            };
            return r;
        }
     
  2. You could update the coins to MySQL when the player receives the coins instead of doing it on server change
     
  3. I thought about doing that as well, however, players earn coins frequently, so I imagine constant updates would cause quite some lag and possibly flood the database, correct?
     
  4. MySQL/MariaDB can handle a fuckton. You dont really have to worry about that unless you have like 300 players earning money every second :p (with a decently optimized setup of course)

    You can also make a locking system, instead of sending the player later, just send a bungee message to every server when an update is done (on quit), and load the data from the database on both login and that message.
     
  5. You open and close connection everytime you query your database, you’d better use a pool of connections(HikariCP) or hold single connection
     
  6. Thank you for both of your suggestions. I am going to look into both. (y)
     
  7. If you're doing one connection, it'd be better to just use a pool of size 1.

    @OP don't abuse static like that. It's not an access modifier to allow global access.
     
  8. Yes, I am aware. I only made it static on my test server to debug. However, thank you guys for the help. Luckily I was able to resolve the issue by setting up a runnable to get the players information every 30 seconds.

    Thanks again. :)