Will Mysql Partitioning auto-add a new partition?
database-partitioning, mysql
Solution
There are several partitioning methods in MySQL, but they all reduce to spreading data over a predetermined number of partitions (see syntax here).
You can, however, setup a cron job or use the event scheduler to add new partitions as time passes.
This answer offers a nice procedure that could be triggered at regular intervals to achieve this effect. Modifying the procedure so that it partitions by year (instead of days) seems to be straightforward.
Problem
I am setting up partitions to separate very large datasets by quarter. Is there a way to have Mysql automatically create a new partition each quarter or do I have to pre-allocate them? What are best practices for this? Perhaps to just make all the partitions for the next few years and forget about it? Or run a quarterly cron? Thanks for any advice.