MySQL Forums
Forum List  »  Partitioning

CREATE_TIME is NULL for all partitions
Posted by: Urs Weiss
Date: January 25, 2011 08:32AM

On a database i made some events which creates and deletes partitions automatically. Creation of them works just fine. But now i saw that there was never a parition deleted.

Errors in log are:
====================
110124 1:00:11 [Warning] Event Scheduler: [root@localhost][xyz.trends_del_partition] No data - zero rows fetched, selected, or processed
110124 1:00:11 [ERROR] Event Scheduler: [root@localhost][xyz.trends_del_partition] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NULL' at line 1
110124 1:00:11 [Note] Event Scheduler: [root@localhost].[xyz.trends_del_partition] event execution failed.
====================


I searched a bit around and had a look into the "information_schema.PARTITIONS" table.

There i saw that all tables with partitions don't have a CREATE_TIME set (NULL). But the CREATE_TIME is used by my event to find the tables it has to delete. Most other tables without partitions have a CREATE_TIME set.


The event-body is:
====================
BEGIN
DECLARE old_partitions CHAR(64) DEFAULT '';
SELECT CONCAT( 'ALTER TABLE trends DROP PARTITION ',
GROUP_CONCAT( PARTITION_NAME ))
INTO @s
FROM information_schema.PARTITIONS
WHERE TABLE_SCHEMA=schema() AND
TABLE_NAME='trends' AND
CREATE_TIME < DATE_SUB( CURDATE(), INTERVAL 14 MONTH )
GROUP BY TABLE_NAME;

PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END
====================

Is there a reason partitions have no CREATE_TIME set?

MySQL Version used: 5.1.48 (CentOS 5.5 - from Remi Repository)


Thanks
Urs

Options: ReplyQuote


Subject
Views
Written By
Posted
CREATE_TIME is NULL for all partitions
4621
January 25, 2011 08:32AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.