Requesting help with Java, MySQL, Threads & Downtime handling.

Discussion in 'Programming' started by SeiRruf, Jun 12, 2015.

  1. Hello friends!

    I have been working on a project recently, but am stumped.

    Briefly, I am wanting to code a plugin similar to "Log Block" but focused to my own backend and features. However, I also need this to be robust and fail-safe.

    So far, I have a plugin developed that does pretty much what I need, but it has some issues. The plugin involves sending data in-game via MySQL to a remote server. It is currently half on its own thread, and half not. When the remote server drops, the plugin crashes every server it is installed on.

    What I tried: I tried moving everything to its own thread on the CPU, and checking for the MySQL connection. However, that resulted in logs completely going crazy, where when one person said something, it would log it under 5 other people in place of what ever they said. For some reason, the thread was cross-mixing calls, and reporting the wrong data to the MySQL server.

    Also, I would like to have a fail safe feature. Due to the sensitive nature of the data collected, I host this at a private home server. Occasionally it goes down. My fail safe idea I had in mind was to detect if the DB was down before sending each query, and if so, seamlessly switch over to a flat-file DB, which would be uploaded in chunks once the MySQL connection re-opened. To that, it would also check if the MySQL connection re-opened, and would automatically reconnect.

    The players must not experience any lag, down time, or ect.

    I am not begging for this to be completely coded, I have coded a whole plugin already, it is just not what I imagine. I am experienced in web development (PHP/HTML/CSS/javascript/MySQL) not Java.

    Any and all help would be appreciated :)

    Currently, I have not programmed
    - A working fail-safe
    - A lag-proof design, where a delay on the MySQL server end would not effect in-game players
    - Auto-reconnection, should the DB drop

    I do have
    - Everything coded to record data
    - Connection to the MySQL DB
    - Everything else to make it run (assuming the DB would never drop, since there's no handling)

    For those who are very fluent in what I am looking for, and can help for a favor/trade (as I am unable to make any payments at the time), are welcome to PM me to arrange access to any source needed. Due to the confidentiality of the code, I wish not to release it publicly.

    Thank you in advanced! And my apologies if this made it in the wrong thread D:
    • Like Like x 1
    • Optimistic Optimistic x 1
  2. Stack. Trace.
  3. For bukkit plugins, you should have all your calls to the MySQL database done in its own thread. This means to have one thread constantly running that executes code from a stack controlled by a lock (for thread safety). The way I have gone about it is to implement a way so I can execute a query or update, and then get a map of all the results back in a callback. Something like this:

    DatabaseAccess.executeQuery((results) -> {
    // do something with 'results', whatever you wrote it to be.
    } "SELECT * FROM 'Players' WHERE 'info' = ?", "unknown");

    DatabaseAccess would perform the queries/updates itself in another thread, leaving all the networking to be threaded (which is good, we don't know the overhead of that and we don't want it affecting our plugin). The (results) -> { ... } is a lambda acting as a callback Runnable, and the results passed to the callback are grabbed and mapped from the result set from MySQL.

    You can then have the logic that operates on the data back in the main thread - that part shouldn't be anywhere near as slow as actually requesting the data from the MySQL database. If you want to ensure that the database going down won't affect your plugin, you're going to need to have proper error handling and procedures in your threaded MySQL calls to make the swap to a flatfile when needed. Connector/J will throw the appropriate errors if the connection times out or is lost/closed.

    This is an issue with your code. You need to show us your code and stack traces if you want any help with that, it's not your database.

    You can easily program functionality to get the plugin to do what you want. If the MySQL server is down, you can easily check by opening a socket in attempt to connect with the database - if the connection is refused, use a flat file backend for the time being. If you want to know when the MySQL server starts while you are using a flatfile, then you'll need to be constantly checking to see if you can bind a socket to its address and port.