How to run MySQL asynchronously.

Discussion in 'Plugin Development' started by flash110, Aug 17, 2016.

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

    flash110

    Ok, so I have used MySQL in my plugins for a little while, and just added something that needs to use MySQL to update every second. This is an example of what I use to get data from my database:
    Code:
    public Integer hasKitBridger(Player p) {
            try {
                    PreparedStatement statement = connection.prepareStatement("select KITBRIDGER from players where UUID='" + p.getUniqueId() + "'");
                    ResultSet result = statement.executeQuery();
                 
                    if (result.next()) {
                            return result.getInt("KITBRIDGER");
                    } else {
                            return 0;
                    }
            } catch (Exception e) {
                    e.printStackTrace();
                    return 0;
            }
        }
    I want to know what I need to change to be able to use this on a different thread so the server does not pause when getting information.
     
  2. Offline

    Zombie_Striker

    @flash110
    Store the value "result.getInt(KITBRIDGER)" somewhere in your main thread, and update that value every second in the SQL thread.
     
  3. Offline

    flash110

  4. @flash110
    Schedule async BukkitRunnables?
     
  5. Offline

    flash110

    Thought it would be more complicated than that xD

    @Zombie_Striker would you just store all of the players shards, which is the currency in a hashmap and keep adding/removing as they join and leave?

    EDIT by Moderator: merged posts, please use the edit button instead of double posting.
     
  6. Offline

    Zombie_Striker

    @flash110
    If you are currently storing currency on the MySQL server, then I suggest the SQL data be just used for longtime storage. While your server is up, the currency for a player should be stored inside the plugin itself. The only time you should retrieve the currency should be when a player joins (which should not cause any problems. Not many players buy/sell stuff right when they join a game).
     
  7. Offline

    flash110

    @Zombie_Striker I have 1 more thing that I need to figure out. I have multiple game servers that update their gamestates, game, players and so on by sending data to a MySQL database, and then the hub server updates that info pretty regularly.
     
  8. Offline

    mythbusterma

    @flash110

    If that's the case, rely on something else, as using a database as a command queue is an antipattern. Instead, establish a connection between the servers and send messages, or consider something actually designed for this, such as a publisher-subscriber relationship (this can be done by something like Redis, or if you feel like going overboard, Apache Kafka).

    You have your case backwards in your SQL statements. SQL keywords are upper case, and fields and table names are lower case, seperated with underscores (consider "SELECT kit_bridger FROM players WHERE..."). Though, this is more of a nit.

    To avoid contention when doing this concurrency, you should make sure that any values you touch on more than one thread are atomic. Do not share a HashMap between threads, HashMaps are not designed to be concurrent. You can use the ConcurrentHashMap, but do not share the Player instance between threads, instead prefer things that are constant, for example the player's UUID. If you make your own objects, make sure that they are thread safe as well.

    Also, don't just catch "Exception," that's extremely lazy. Only catch the errors that you're going to handle. Why are you returning "Integer?" Use the base type "int" where possible, although you really should be returning a more meaningful value (I'm not sure what this is supposed to indicate, but a boolean would be the best choice here, my guess is).


    Keep in mind that you will not be able to create methods like this if you're doing this concurrently (you can't concurrently "return" a value). You'll either have to load all of the data you need when the server starts (or when the player logs in), periodically request it from the SQL server, or load it on demand and check for it at some point in the future.
     
    flash110 likes this.
  9. Offline

    flash110

    --This was edited because I could not figure out how to delete this little comment--​
    I will save the whole redis conversation for another thread once I figure out all of this stuff about atomic variables and async runnables and tasks and all of that 'fun' stuff :)
     
    Last edited: Aug 17, 2016
  10. Offline

    mythbusterma

    @flash110

    Redis is probably overkill as well, I would recommend establishing a direct connection to the "main" server.
     
  11. Offline

    flash110

    @mythbusterma I did do that at first, but you know the ServerPingEvent? while pinging the server it did not get the updated motd I used for the gamestate and map of the game. It just used the one set in the config. I think it may have been because I was using the old 1.7 way of pinging the server as someone had suggested. I just do not know how to use the new one and get all of the info from it. I tried looking through the protocol page but I failed to get any of it to make sense. (Yes I found the old bit of code from another thread because I have no clue how to use packets :p)
     
  12. Offline

    mythbusterma

    @flash110

    That's not what I meant. Open a socket. Send a message.
     
  13. Offline

    flash110

    @mythbusterma I will try to get that to work, if I succeed that means a whole table in SQL gone! :D Would the socket that is receiving the information be able to receive it from multiple sockets? I read that it is a TCP connection which means you need to portforward each one right..? If you need to portforward it how would you go on doing that if you have a server that you can only administer through Multicraft. 1 more thing as well... If I had multiple hubs would I need to send this info to each hub or should I send it all to hub 1 and have hub 1 send to hub 2 and so on...? And 1 thing above all, how would you get or place a currency value from the database in an async way? Can I have an example of how you would do it because I have no clue. Do you know how to do any of this @Zombie_Striker
     
    Last edited: Aug 18, 2016
  14. Offline

    flash110

  15. Offline

    mythbusterma

    @flash110

    You establish a server socket, which listens for client connections, when a client connects you will get a Socket, which you can use to communicate back and forth.

    I suppose if it was hidden behind NAT, yes, you would need to portforward? Only for the server though. The fact that it's TCP doesn't have much to do with that, you'd run in to the same issue with UDP. I don't know of any servers that are hidden behind NAT (other than shitty home ones) so that shouldn't be an issue. You'll have to make sure that you're allowed to use the TCP port you wind up assigning it to, if you don't run the box yourself your host should be able to help you with this.

    Why would one "depedant" server connect to more than one "hub" server? That doesn't make much sense.

    The async one is a hard problem to solve, and one of the largest reason I almost always recommend against using databases. I would recommend loading the currency value when the player logs in, and writing it out occasionally.
     
  16. Offline

    flash110

    Should I go about getting the initial values when the player logs in and just work off of those and write them all back out when they either need written and when they log off? And would the same problem that happens with database lag while waiting happen with the 'minigame' server writing to the hub server or the hub server getting the info?
     
  17. Offline

    mythbusterma

    @flash110

    Yes, that makes sense.

    Reads and writes to the other server need to be done on another thread.
     
  18. Offline

    flash110

    Isn't that the same problem as the database then?
     
  19. Offline

    mythbusterma

    @flash110

    No. You can't have synchronous inter process communication on a real time system. Surely you can see why that's completely nonsensical?

    Also, you don't have the same issue as polling a database, which is what that article recommended, if you read it.
     
  20. Offline

    flash110

    @mythbusterma Can I have an example of how to send and receive info through sockets in an async way?
     
  21. Offline

    mythbusterma

    @flash110

    Create a new thread, and send and receive on it. There's plenty of stuff online on using Unix Sockets.
     
Thread Status:
Not open for further replies.

Share This Page