Hi!

currently there are many limitations with the partitioning function syntax - hopefully, these will be lifted. But in the mean while, maybe you should post a feature request.

However, you can get it to work using dynamic SQL:

DELIMITER $

CREATE EVENT log_add_partition
ON SCHEDULE EVERY 1 DAY DO
BEGIN
    SET @stmt := CONCAT(
        'ALTER TABLE log ADD PARTITION ('
    ,   'PARTITION p'
    ,   DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL 1 DAY ), '%Y%m%d' )
    ,   ' VALUES LESS THAN ('
    ,   TO_DAYS( CURDATE() ) + 1
    ,   '))'
    );
    PREPARE stmt FROM @stmt;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END
$

DELIMITER ;


(Note that I had to adjust 2 things: first of all, I had to adjust the table definition to explicitly include an ENGINE=MYISAM clause. That is because this trick:

    logged datetime not null,
    id int not null auto_increment,

    PRIMARY KEY ( logged, id )

works only for MyISAM.

Secondly, I had to adjust the CURDATE without parenthesis from your declaration:

DECLARE max_day INTEGER DEFAULT TO_DAYS( CURDATE ) + 1;

to one including parenthesis - I don't understand why but the parenthesis are apparently required in the SET @stmt etc expression.

Anyway, I hope this helps ;)

kind regards,

ROland.

As far as I know this won't work as events are not allowed/able to make information scheme changes.
Sorry, don't have links for that, but you might be able to find out about the limitations of events somewhere in the documentation...

hth

I made a couple of small changes to make this work:

DELIMITER |
CREATE EVENT log_addpartition ON SCHEDULE EVERY 1 DAY

DO
  BEGIN
    DECLARE new_partition CHAR(32) DEFAULT
      CONCAT ('p', DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL 1 DAY), '%Y%m%d'));
    DECLARE max_day INTEGER DEFAULT TO_DAYS(NOW()) +1;

    SET @s =
      CONCAT('ALTER TABLE fwlogs ADD PARTITION (PARTITION ', new_partition,
      ' VALUES LESS THAN (', max_day, '))');
    PREPARE stmt FROM @s;
    EXECUTE stmt;
  END |
DELIMITER ;

I suspect that it is not legal to use variables in partitioning expressions. But I think that you can use variables to create _text_ of your ALTER statement first and then execute it using SQL syntax for prepared statements (http://dev.mysql.com/doc/refman/5.1/en/sqlps.html).

I'd create a stored proc with dynamic sql in it to build the new partition and call it from events or whatever cron

Try this. At least, it accepts the syntax, I'm not sure it it'd work

DELIMITER |
DROP EVENT IF EXISTS log_add_partition|
CREATE EVENT log_add_partition ON SCHEDULE EVERY 1 DAY DO
    BEGIN
        DECLARE new_partition CHAR(32) DEFAULT
            CONCAT( 'p',
                DATE_FORMAT( DATE_ADD( CURDATE(), INTERVAL 1 DAY ), '%Y%m%d' )
            ); 
        DECLARE max_day INTEGER DEFAULT TO_DAYS( CURDATE ) + 1;
        SET @stmt = CONCAT("ALTER TABLE log ADD PARTITION (PARTITION ", new_partition, " VALUES LESS THAN (", max_day, "))");
        PREPARE stmt FROM @stmt;
        EXECUTE stmt;
    END|
DELIMITER ;

Thanks a lot for the info (both provided by you and by your readers).

I've been using a very error-prone script to accomplish exactly the same thing, and had no idea that it could be done this way.