Creating a countdown timer w/ mysql

Discussion in 'Spigot Plugin Development' started by DotRar, Jun 12, 2016.

  1. So I have created a coins booster system. Basically, when a command is executed, an entry will be added to the database (Which happens fine) and then every minute, the "Seconds" column will decrease by 60 and when the value gets to 0, it is removed from the database. However, the seconds value never changes. Here is my database, it has been like this for days:

    Code (Text):
    mysql> SELECT * FROM doubleCoins;
    | NAME         | SECONDS |
    | Nex_Perkelle |    3600 |
    | MEMES        |    3600 |
    | Nex_Perkelle |    3600 |
    3 rows in set (0.00 sec)
    Here is my code:

    I have done a lot of successful troubleshooting today, but I am really confused by this one. I can run debug messages if the error is not obvious. Thanks <3
  2. Don't use a second values that you decrement, that's like the worst idea performance-wise. Instead, store a DateTime containing the time at which the booster should end, and compare the current time with that time.
    • Agree Agree x 2
    • Like Like x 1
  3. However, if the server closes for say 10 minutes then the booster will only be 50 minutes long instead of an hour
  4. Repeatedly writing to a database is a bit of a performance hit. Same with opening, closing, and compacting sqlite databases. 60 seconds will create a lot of I/O overhead for your plugin.

    Store a timestamp in a new table when the plugin is enabled and disabled. Use that table of timestamps to help you find and account for downtime. It's the same effect as decrementing your variable (since plugin disable would stop the decrements), but requires far fewer writes.

    As for your problem, try using UPDATE WHERE in place of INSERT INTO since ON DUPLICATE KEY doesn't seem to exist in sqlite. When in doubt, download SQLiteBrowser and run arbitrary commands on a test database to see if/how they work.

    I would also suggest making a utility class dedicated to handling sqlite connections like one I've written here for AS3 (a port wouldn't be difficult). It's not the most beautiful code, but it gets the job done, and helps keep your main code more organized by forcing you to write class-specific functions in the class they should actually be in.
    #4 egg82, Jun 13, 2016
    Last edited: Jun 13, 2016
  5. Why are you using a database to keep track of the seconds to begin with? Shouldn't this be done in memory, then dump the remaining seconds to the database onDisable()..?

    Also, index that table. You're using a relational database, not some text file.
  6. I need multiple servers to know how long a booster has left though, e.g. bungee for the motd and game servers for awarding double coins
  7. Then you can't really that, if every server has it's own time, then you can't for example put on the bungee if there's a booster active, because the booster on the bungee might be still active but not on the survival server for example, or the booster might be active on survival but not on bungee, let's say survival went down it won't be active, so if you keep the time when survival went down and the do the maths the survival might be still on but not on skywars. So if you want to do something like that you should make a booster / server or ignore when the server went down and still count the time.

    Im probably not explaining this well, but i hope you get what im trying to say.
  8. That's why I'm using mysql
  9. MySQL has nothing to do with what i said. If you want to have a global booster but with a different time on every server (if server goes down) what you're doing won't work.
  10. It's the whole dedi going down I'm worried about
  11. Like i have already said many times, with your system that can't be done, you're storing a single variable for every server, you would have to store one per server, or don't take in count if a server goes down.
  12. Ohhhh I get you. Sorry :p the code will only be running on our 1 hub server.
  13. Whoops, MySQL not SQLite. This is what I get for reading forums at 2 AM.

    This is a better solution, since you wouldn't need to keep track of times: