MYSQL ERROR on Paper 1.16.4

[thecoolestpaul]

MYSQL ERROR on Paper 1.16.4

[TheCoolestPaul]

Hopefully with PR #60 this issue is fixed.

[TheCoolestPaul]

I will leave a server running while I'm at work and force it to have variable Token loads to see if I can recreate this issue or not.

[radaoggy]

I will leave a server running while I'm at work and force it to have variable Token loads to see if I can recreate this issue or not.

Is it possible that an error will occur if I change the data in the MySQL table remotely? For example, the number of tokens? I have no idea if there's a sync error? This is the only plugin where when the number of tokens in the database changes, the change is made immediately in the game, this is very important to me and that is why I love this plugin so much.

[rbluer]

Paul I may have fixed that issue with the MySQL changes that you just pulled from my fork. I found a couple of memory leaks in the former MySQL code and eliminated them. I also addressed issues with the SQLite too. So overall, the database transactions should be far more stable over time with the current build that you have.

radaoggy I'm about to open a new pull request in the next day, or so, that will add a fairly advanced token caching layer to Tokens. A friend was using my plugin and was generating a few hundred token database hits per minute that was generating a lot of lag. To support his server, Tokens would have to support a few thousand hits per minute with zero lag. So the caching will accomplish that, and it will handle graceful shutdown to ensure tokens are not lost. (I'm tempted to add a "fail safe" component that will prevent token loss if the database has failures, but that's another topic).

The cache will allow of rapid response to placeholders without waiting for the database. So if you have a lot of players online, and you are updating the Tokens placeholders a couple times per second, you may notice faster refresh rates, plus the token counts will be "more" instant. The caching is designed to provide instant updates of the player's current token balance, even if they earning a few hundred to a thousand+ tokens per minute. The bottom line is that caching will improve the live instant updates for the players.

The reason why I'm mentioning this, is because when the cache is active, it will be unable to pickup changes that you make to the MySQL tables though an external source. Those values will not be lost because of the way I've written the caching database interface, but it will not be reflected in game unless the player logs out (which unloads their data from the cache) and back in (loads their data from the database) or the server restarts. As I said, the changes you made to the player's balance though an external source will not be lost, but that does present an issue with the way you are using the Tokens.

And that greatly concerns me that a feature that you enjoy and that is very important to you is going to be impacted by the addition of the caching. That said, there is a config setting that will turn off the caching, and it will behave as if the caching was never there. So each time the scoreboard requests a placeholder to be resolved from Tokens, it will hit the database and return the current value that's there.

I can think if four ways we can solve the issue of updating the database through an external source.

1. Turn off caching. This is not an ideal solution though.

2. An alternative to turning off the caching, is possibly a new feature I can add to the caching that will monitor the database for changes, and then update the cache to reflect those changes. Such a feature would be suited to only "run" on a delayed basis, such as 30 seconds or every few minutes. Such a feature would be applied to all online players and would be an "active" updater (active in the sense no player would have to initiate it).

3. Similar to item 2 above, but add an admin command to refresh the whole cache if any player is found to be out of sync. I personally think this may be the best solution; to have an admin kickoff this This option could be ran in conjunction with item 1 if you will have frequent database updates from an external source. Example could be if you have two game modes that are sharing the same token source for the players (may be a neat idea, but it won't work only because if the same player makes purchases on both systems at the same time they will be able to spend more tokens than what they have).

4. Another alternative that would be passive, would be to check to see if the cache is out of sync with the database after the player's current value has been saved. That would not be an instant update either, since changes in the cache are not committed until about 10 seconds later (cache delay to reduce database activity). Also the player would have to be earning tokens to trigger the initial save to the database. The database checking would need to have a cool down so as to reduce load. I personally like this approach the least since it causes the most interference with the caching behavior.

Anyway, just wanted to let you know that I will be adding a token cache in the next day or two, but there are possible ways to work with external updates. I'll think about this challenge and will try to see what may work best for most users.

[TheCoolestPaul]

Running the latest 3.9.9 build (Windows 64-bit though I don't think that should make a difference) for 10+ hours straight with variable Token accessing/mutating, I have not gotten an error. Tokens seems to work fine and it is not hogging any more resources than when I first booted the server.

[radaoggy]

Paul I may have fixed that issue with the MySQL changes that you just pulled from my fork. I found a couple of memory leaks in the former MySQL code and eliminated them. I also addressed issues with the SQLite too. So overall, the database transactions should be far more stable over time with the current build that you have.

radaoggy I'm about to open a new pull request in the next day, or so, that will add a fairly advanced token caching layer to Tokens. A friend was using my plugin and was generating a few hundred token database hits per minute that was generating a lot of lag. To support his server, Tokens would have to support a few thousand hits per minute with zero lag. So the caching will accomplish that, and it will handle graceful shutdown to ensure tokens are not lost. (I'm tempted to add a "fail safe" component that will prevent token loss if the database has failures, but that's another topic).

The cache will allow of rapid response to placeholders without waiting for the database. So if you have a lot of players online, and you are updating the Tokens placeholders a couple times per second, you may notice faster refresh rates, plus the token counts will be "more" instant. The caching is designed to provide instant updates of the player's current token balance, even if they earning a few hundred to a thousand+ tokens per minute. The bottom line is that caching will improve the live instant updates for the players.

The reason why I'm mentioning this, is because when the cache is active, it will be unable to pickup changes that you make to the MySQL tables though an external source. Those values will not be lost because of the way I've written the caching database interface, but it will not be reflected in game unless the player logs out (which unloads their data from the cache) and back in (loads their data from the database) or the server restarts. As I said, the changes you made to the player's balance though an external source will not be lost, but that does present an issue with the way you are using the Tokens.

And that greatly concerns me that a feature that you enjoy and that is very important to you is going to be impacted by the addition of the caching. That said, there is a config setting that will turn off the caching, and it will behave as if the caching was never there. So each time the scoreboard requests a placeholder to be resolved from Tokens, it will hit the database and return the current value that's there.

I can think if four ways we can solve the issue of updating the database through an external source.

1. Turn off caching. This is not an ideal solution though.
2. An alternative to turning off the caching, is possibly a new feature I can add to the caching that will monitor the database for changes, and then update the cache to reflect those changes. Such a feature would be suited to only "run" on a delayed basis, such as 30 seconds or every few minutes. Such a feature would be applied to all online players and would be an "active" updater (active in the sense no player would have to initiate it).
3. Similar to item 2 above, but add an admin command to refresh the whole cache if any player is found to be out of sync. I personally think this may be the best solution; to have an admin kickoff this This option could be ran in conjunction with item 1 if you will have frequent database updates from an external source. Example could be if you have two game modes that are sharing the same token source for the players (may be a neat idea, but it won't work only because if the same player makes purchases on both systems at the same time they will be able to spend more tokens than what they have).
4. Another alternative that would be passive, would be to check to see if the cache is out of sync with the database after the player's current value has been saved. That would not be an instant update either, since changes in the cache are not committed until about 10 seconds later (cache delay to reduce database activity). Also the player would have to be earning tokens to trigger the initial save to the database. The database checking would need to have a cool down so as to reduce load. I personally like this approach the least since it causes the most interference with the caching behavior.

Anyway, just wanted to let you know that I will be adding a token cache in the next day or two, but there are possible ways to work with external updates. I'll think about this challenge and will try to see what may work best for most users.

For a long time I was looking for a plugin that would still have up-to-date information about tokens in the database and the game. I don't use placeholders anywhere, I use / tokens to find out the balance. But I'm not saying I wouldn't use anything like that in the future. Anyway, this feature is important to me because I have this plugin linking to an online store. Players can buy various functions and things for tokens. If the cache functions in the settings could be turned off, it would be great! I'm glad you're working on this plugin, it's light, simple, and most importantly it doesn't have unnecessary features like other plugins. :-)

[radaoggy]

Running the latest 3.9.9 build (Windows 64-bit though I don't think that should make a difference) for 10+ hours straight with variable Token accessing/mutating, I have not gotten an error. Tokens seems to work fine and it is not hogging any more resources than when I first booted the server.

I immediately downloaded the update and I'm going to test it as well.

[TheCoolestPaul]

I haven't been able to reproduce this bug since 3.9.9. (Windows 64-bit)

[TheCoolestPaul]

I have done more bug-testing and I have not been able to recreate this issue anymore. I am going to close it assuming it's fixed.