On MySQL locks exceeds the lock table size when trying to purge large numbers of actions. Add index on actions.time to fix #352
Description
Expected behavior
Purging a table with a large number of actions (1mio+) should work without issues
Observed/actual behavior
On MySQL, purging a table with a very large number of actions caused the purge SQL query to fail with "The total number of locks exceeds the lock table size"
Steps/models to reproduce
Setup a MySQL database with default configuration values and connect the mod to the database
Add a large number of actions to the action table, like 1 1/2 - 2 million entries in the actions table
Enable auto purge
Start the Minecraft server
What operating system are you running
Linux
Minecraft version
1.21.10
Ledger version
1.3.16
Logs
Agreements
- I am running the latest version of the mod.
- My version of Minecraft is supported.
- I have searched for and ensured there isn't already an open issue regarding this.
Other
On our SMP I have set the autoPurgeDays to a value of 120 days. Today I noticed that I was getting "The total number of locks exceeds the lock table size" on this query: DELETE FROM actions WHERE actions.time <= ?
I noticed that there is no index on actions.time, so as the purge did not clean up anything yet and the number of actions kept growing and growing (it reached 1,252,397 actions in our case) this all of a sudden happened. Without the index it seems to do a full table scan, and when doing so on over a sufficiently large number of rows the database runs out of lock table space.
I have manually added an index on actions.time: ALTER TABLE actions ADD INDEX actions_time (time); This immediately resolved the issue for me on our server. But it might be worthwhile adding this index to the schema in this mod to avoid this from happening and to make the query more efficient instead of doing a full table scan.