MySQL Forums
Forum List  »  Partitioning

missing data after partitioning - bug?
Posted by: rol and
Date: May 23, 2014 12:51PM

I know a couple of things, but table partitioning isn't one of them.

i have a 35 GB sized table which is causing performance issues, my research came across partitioning, which is what i tried and failed with today.

i tried altering the table, that kept going for 2 hours with absolutely no change ( i checked under /var/lib/mysql/DBname/Tablename*

then decided to create a new identical table, with the added partitions.

my goal is to partition my table by month, using the timestamp column.

i did the following (i googled A LOT to find a way to auto add months instead of manually adding them, i'm afraid i couldn't find any decent how to)

CREATE TABLE `p3_dna_new` (
`userid` int(11) NOT NULL,
`action` int(11) NOT NULL,
`playper` float DEFAULT NULL,
`songid` int(11) NOT NULL,
`timestamp` datetime NOT NULL,
`playlstid` int(11) DEFAULT NULL,
`playlstcnt` int(11) DEFAULT NULL,
`acttype` tinyint(4) DEFAULT NULL,
`inserted` int(11) DEFAULT NULL,
`connectiontype` int(11) DEFAULT NULL,
`reservedcol` tinyint(4) DEFAULT NULL,
UNIQUE KEY `p3_plays_nodups` (`userid`,`action`,`playper`,`songid`,`timestamp`),
KEY `p3_plays_inserted` (`inserted`),
KEY `sp` (`songid`),
KEY `p3_plays_timestamp` (`timestamp`),
KEY `playlstid` (`playlstid`)
)
PARTITION BY RANGE ( TO_DAYS(timestamp) ) (

PARTITION Feb2012 VALUES LESS THAN (TO_DAYS('2012-03-01')),
PARTITION Mar2012 VALUES LESS THAN (TO_DAYS('2012-04-01')),
PARTITION Apr2012 VALUES LESS THAN (TO_DAYS('2012-05-01')),
PARTITION May2012 VALUES LESS THAN (TO_DAYS('2012-06-01')),
PARTITION Jun2012 VALUES LESS THAN (TO_DAYS('2012-07-01')),
PARTITION Jul2012 VALUES LESS THAN (TO_DAYS('2012-08-01')),
PARTITION Aug2012 VALUES LESS THAN (TO_DAYS('2012-09-01')),
PARTITION Sept2012 VALUES LESS THAN (TO_DAYS('2012-10-01')),
PARTITION Oct2012 VALUES LESS THAN (TO_DAYS('2012-11-01')),
PARTITION Nov2012 VALUES LESS THAN (TO_DAYS('2012-12-01')),
PARTITION Dec2012 VALUES LESS THAN (TO_DAYS('2013-01-01')),
PARTITION Jan2013 VALUES LESS THAN (TO_DAYS('2013-02-01')),
PARTITION Feb2013 VALUES LESS THAN (TO_DAYS('2013-03-01')),
PARTITION Mar2013 VALUES LESS THAN (TO_DAYS('2013-04-01')),
PARTITION Apr2013 VALUES LESS THAN (TO_DAYS('2013-05-01')),
PARTITION May2013 VALUES LESS THAN (TO_DAYS('2013-06-01')),
PARTITION Jun2013 VALUES LESS THAN (TO_DAYS('2013-07-01')),
PARTITION Jul2013 VALUES LESS THAN (TO_DAYS('2013-08-01')),
PARTITION Aug2013 VALUES LESS THAN (TO_DAYS('2013-09-01')),
PARTITION Sept2013 VALUES LESS THAN (TO_DAYS('2013-10-01')),
PARTITION Oct2013 VALUES LESS THAN (TO_DAYS('2013-11-01')),
PARTITION Nov2013 VALUES LESS THAN (TO_DAYS('2013-12-01')),
PARTITION Dec2013 VALUES LESS THAN (TO_DAYS('2014-01-01')),
PARTITION Jan2014 VALUES LESS THAN (TO_DAYS('2014-02-01')),
PARTITION Feb2014 VALUES LESS THAN (TO_DAYS('2014-03-01')),
PARTITION Mar2014 VALUES LESS THAN (TO_DAYS('2014-04-01')),
PARTITION Apr2014 VALUES LESS THAN (TO_DAYS('2014-05-01')),
PARTITION May2014 VALUES LESS THAN (TO_DAYS('2014-06-01')),
PARTITION Jun2014 VALUES LESS THAN (TO_DAYS('2014-07-01')),
PARTITION Jul2014 VALUES LESS THAN (TO_DAYS('2014-08-01')),
PARTITION Aug2014 VALUES LESS THAN (TO_DAYS('2014-09-01')),
PARTITION Sept2014 VALUES LESS THAN (TO_DAYS('2014-10-01')),
PARTITION Oct2014 VALUES LESS THAN (TO_DAYS('2014-11-01')),
PARTITION Nov2014 VALUES LESS THAN (TO_DAYS('2014-12-01')),
PARTITION Dec2014 VALUES LESS THAN (TO_DAYS('2015-01-01')),
PARTITION Jan2015 VALUES LESS THAN (TO_DAYS('2015-02-01')),
PARTITION Feb2015 VALUES LESS THAN (TO_DAYS('2015-03-01')),
PARTITION Mar2015 VALUES LESS THAN (TO_DAYS('2015-04-01')),
PARTITION Apr2015 VALUES LESS THAN (TO_DAYS('2015-05-01')),
PARTITION May2015 VALUES LESS THAN (TO_DAYS('2015-06-01')),
PARTITION Jun2015 VALUES LESS THAN (TO_DAYS('2015-07-01')),
PARTITION Jul2015 VALUES LESS THAN (TO_DAYS('2015-08-01')),
PARTITION Aug2015 VALUES LESS THAN (TO_DAYS('2015-09-01')),
PARTITION Sept2015 VALUES LESS THAN (TO_DAYS('2015-10-01')),
PARTITION Oct2015 VALUES LESS THAN (TO_DAYS('2015-11-01')),
PARTITION Nov2015 VALUES LESS THAN (TO_DAYS('2015-12-01')),
PARTITION Dec2015 VALUES LESS THAN (TO_DAYS('2016-01-01')) );


Then issued:

INSERT INTO p3_dna_new SELECT * FROM p3_dna_old;

It finishes after a while, with absolutely no error nor warning.

if i issue the following:
select count(*) from p3_dna_new;

it will be different than the result of

select count(*) from p3_dna_old;


If there's an issue, shouldn't i get a warning or error message?

NOTE: data spans between 2004 and may 2014, and the table will still be used in the future, which is why i added extra month

Options: ReplyQuote


Subject
Views
Written By
Posted
missing data after partitioning - bug?
2229
May 23, 2014 12:51PM


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.