Request: Moving Transactions to MySQL database

[TomLewis]

I have used Signshop since 2015. My transaction log is over 700MB.
Whenever I have to investigate reported sales or people cheating money from people using signs and making people false click (say though a gate) the process of filtering this mammoth file is a task in itself.

I request that you add MySQL support for recording transactions, this gives us so many options for future improvements:

1. We can query and find information 100000x faster.
2. We can have a searchable web UI for all sales (I will build this)
3. We can have in-game queries for sales, shop owners could log on and have a history of their sales since they were offline.
4. Players could look up their entire history of sales to track their balance over time and their buying/selling over time.
5. Admins could also look up sales over time by interacting with signs, maybe shift+right click could bring up that signs specific sales in a timeframe to investigate.

All of this would be possible as soon as transactions are stored in MySQL so it can be queried.

[weaves7]

I definitely like this idea and would like to utilize databases for the rest of the data storage as well. Unfortunately I am not well versed in databases and I am also very busy with a newborn right now so non-critical development for SignShop will be slow at best for now.

[TomLewis]

@weaves7 Congrats on the newborn!

What about if someone was able to help understand how to use MySQL well inside plugin development, the amazing @Creepermorderen might be able to lend a hand!

I can supply a database schema at the very least and also a massive, massive transactions history to work with and test!

[dustyhunsaker]

Whatever we end up doing here, I think it's important to make sure the transactions can still happen quickly. Having used several plugins in the past with dbs is places other than on the server, many of them would hang waiting for a response from the other server making everything feel sluggish. I think the best way around such a problem would be the option to just keep the db local (like we do with the sqlite files already), and if we were to also offer remote connection ability, we would keep transactions local until they can be written to the remote db (potentially on a separate thread? I can't remember if this is possible on MC servers.).

[weaves7]

What about if someone was able to help understand how to use MySQL well inside plugin development, the amazing @Creepermorderen might be able to lend a hand!

Thanks. I would certainly appreciate any help regarding this. I would also like to acquire both your shop data and logs for testing purposes at some point.

Whatever we end up doing here...

We definitely need to consider transaction execution time. We have also had issues with economy plugins that use a remote database slowing down transactions. The way around this is to run the database code async and have the data cached for the transaction itself. Minecraft is mostly single threaded. Anything that touches the server code has to be done on the main thread. The server does allow for multithreading for I/O and other data processing as long as you don't touch any server methods. For logging I don't think it would be a problem to log to the remote database on a separate thread as long as the method does not return anything that the transaction would have to wait for.

I think there should be a couple options regarding what storage is used for what. There are two things that need stored one being the logs and the other being the shop/player data. There should be options for each for which storage method to use. I would like to move away from flatfiles for data storage so the options for it could be sqlite and mysql. The logs should be human readable for some servers so it could be flatfile and mysql. There would need to be config options for this of course.

Since this is a decent sized change I think that it should be developed under a feature branch so that other development could continue on the main branch.

[TomLewis]

Thanks. I would certainly appreciate any help regarding this. I would also like to acquire both your shop data and logs for testing purposes at some point.

No sweat, My Transactions log is a whopping 631mb So it will be the biggest, longest-running example of signshop history you could get your hands on for testing! Not quite sure how I will get this to you, I can upload it somewhere and email it/send a link on discord?

[weaves7]

Sweet, you can find me on the SignShop Discord. https://discord.gg/4wnyysf

[TomLewis]

Bump! Lifes been mental for me in teh last year, I cant remember where we got with this, did you want me to dm you on discord?