Using Events to manage Table Partitioning by Date: wrap-up

As a follow up to the series of posts I've been making, I wanted to post what I ended up with.  Thanks to everyone who posted comments, your help was extremely useful. To recap, I have a working pair of events to add and remove partitions to this table:

create table log (
    logged datetime not null,
    id int not null auto_increment,
    text varchar(256),
    PRIMARY KEY ( logged, id )
    PARTITION p20080206 VALUES LESS THAN (733444),
    PARTITION p20080207 VALUES LESS THAN (733445),
    PARTITION p20080208 VALUES LESS THAN (733446)

Props to drupal

Congrats to Drupal for releasing version 6.0 yesterday.  This site has been running 6.0 since about rc2 (upgrading every release along the way) and I've been very happy with it.  Also, thanks to the developer(s) of the 'Channel Nine' theme, which I've adopted for my own (with some modifications).

Event Scheduling reliability

I created my Events to handle adding new partitions (and removing old ones) from my log table and they seem to be working.  However, today I noticed that the events ran a few hours later than they ran yesterday.  

          EXECUTE_AT: NULL
              STARTS: 2008-02-13 05:36:21
                ENDS: NULL
              STATUS: ENABLED
             CREATED: 2008-02-13 05:36:21
        LAST_ALTERED: 2008-02-13 05:36:21
       LAST_EXECUTED: 2008-02-14 07:32:22

My addpartition event is current date dependent: it creates a partition for tomorrow hopefully before tomorrow comes so we don't miss a partition and so my INSERTs don't get "No partition" errors.  So if it waits a few extra hours more each day, gradually it will push past midnight and screw things up.  

Ruby Succinctness competition

For some reason I never was that excited about trying to accomplish these sorts of things in Perl:

1..rand(256)).inject("") {|string,n| string + ('a'..'z').to_a[rand(25)] }

This generates a string from 1 to 256 characters long, containing random letters from 'a' to 'z'.  

Normally, I wouldn't use something that's quite so unreadable, but I'm starting to like Ruby's way of taking what should be a very simple operation that I might have done in 3-5 lines in Perl and boiling it down to a single line.

I'd love to see if anyone can make this more succinct (but one line is still a requirement!)

Here's the code in it's context. It's just a test script writing into my partition table every minute so I can verify my Events from previous posts are working correctly.

Using Events to manage a Table Partitioned by Date: part 2

Thanks all for the help on getting my log_addpartition event running, dynamic SQL was just the ticket. 

I'm now focusing on writing the sister event: 'log_removepartition', which will find and purge partitions older than some time interval. The information schema comes in quite handy here, as I can query the PARTITIONS table to see all partitions.

About Me

Jay Janssen
Yahoo!, Inc.
jayj at yahoo dash inc dot com
High Availability
Global Load Balancing
View Jay Janssen on Twitter  View Jay Janssen's LinkedIn profile View Jay Janssen's Facebook profile