I APOLOGIZE for unwilling spamming this list! i switched to plain text, and the formatting is still messed up when i paste from sublime.
Background information: i have a 35 GB sized table which is causing performance issue, which is why i decided to add partitioning by month using the timestamp column. IT would be preferable to auto add every month instead of specifying it, but i don't know how to do that, so instead i did it manually for existing, and future month (1 year ahead) altering the table went for hours without any result, so i stopped it and created a new identical(but with partitions) table and tried inserting the content into it from the existing database. this worked smoothly and took about 2 hours to finish without any errors nor warnings. but when i do a count(*) i notice that there's more than 22 million record missing from the partitioned table.. These are the steps i took: " 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')) ); " ## After which, i issued the following: ## Hoping it will increase the speed of import of one table to another, retrieved these from a mysqldump file.. alter table p3_dna_new DISABLE KEYS; LOCK TABLES `p3_dna_new` WRITE; 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? ---------------------------------------- > From: r_o_l_a_...@hotmail.com > To: mysql@lists.mysql.com > Subject: RE: missing data after partitioning an existing table > Date: Sat, 24 May 2014 08:38:06 +0300 > > > > > Excuse me , and thanks for the heads up. > 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: > > ALTER TABLE `pe_dna_new` DISABLE KEYS;LOCK TABLES `pe_dna_new` WRITE; > 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 > > > >> Date: Fri, 23 May 2014 21:19:18 +0200 >> From: t...@stuxnet.org >> To: mysql@lists.mysql.com >> Subject: Re: missing data after partitioning an existing table >> >> Hi there, >> >> Le 23/05/2014 21:06, Roland RoLaNd a écrit : >>> [...] >> >> Ouch .... >> >> This post is somewhat ... unreadable ! >> >> Please format ! >> >> Christophe. >> >> -- >> MySQL General Mailing List >> For list archives: http://lists.mysql.com/mysql >> To unsubscribe: http://lists.mysql.com/mysql >> > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql