Mysql Range partition

MySQLpartitioning

I have the next table structure:

 CREATE TABLE `sat_msgPos` (
  `idOwnApp` mediumint(11) NOT NULL DEFAULT '0',
  `idMsg` bigint(11) unsigned NOT NULL AUTO_INCREMENT,
  `idVeh` mediumint(11) NOT NULL DEFAULT '0',
  `idProviderComm` mediumint(11) DEFAULT NULL,
  `txMsg` varchar(255) DEFAULT NULL,
  `tmMsg` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `amLat` double DEFAULT NULL,
  `amLong` double DEFAULT NULL,
  `tmUpdate` datetime DEFAULT NULL,
  `idMsgBlobParent` int(11) DEFAULT NULL,
  `txDescPos` varchar(255) DEFAULT NULL,
  `idMsgVpr` int(11) DEFAULT NULL,
  `amSpeedCurr` float DEFAULT NULL,
  `idSts` char(1) DEFAULT NULL,
  `idTrigger` tinyint(3) unsigned DEFAULT NULL,
  `amDistTot` mediumint(9) DEFAULT NULL,
  `amEngMinsTot` float DEFAULT NULL,
  `inException` tinyint(2) DEFAULT '0',
  PRIMARY KEY (`idMsg`,`idVeh`,`tmMsg`),
  KEY `X1` (`idMsgBlobParent`),
  KEY `X2` (`idMsgVpr`),
  KEY `X3` (`tmMsg`),
  KEY `X4` (`idVeh`,`tmMsg`,`idTrigger`)
) ENGINE=InnoDB AUTO_INCREMENT=2102445247 DEFAULT CHARSET=utf8
/*!50500 PARTITION BY RANGE  COLUMNS(tmMsg)
(PARTITION a9 VALUES LESS THAN ('2017-02-01') ENGINE = InnoDB,
 PARTITION a10 VALUES LESS THAN ('2017-03-01') ENGINE = InnoDB,
 PARTITION a11 VALUES LESS THAN ('2017-04-01') ENGINE = InnoDB,
 PARTITION a12 VALUES LESS THAN ('2017-05-01') ENGINE = InnoDB,
 PARTITION a13 VALUES LESS THAN ('2017-06-01') ENGINE = InnoDB,
 PARTITION a14 VALUES LESS THAN ('2017-07-01') ENGINE = InnoDB,
 PARTITION b1 VALUES LESS THAN ('2017-08-01') ENGINE = InnoDB,
 PARTITION b2 VALUES LESS THAN ('2017-09-01') ENGINE = InnoDB,
 PARTITION b3 VALUES LESS THAN ('2017-10-01') ENGINE = InnoDB,
 PARTITION b4 VALUES LESS THAN ('2017-11-01') ENGINE = InnoDB,
 PARTITION b5 VALUES LESS THAN ('2017-12-01') ENGINE = InnoDB,
 PARTITION b6 VALUES LESS THAN ('2018-01-01') ENGINE = InnoDB,
 PARTITION b7 VALUES LESS THAN ('2018-02-01') ENGINE = InnoDB,
 PARTITION b8 VALUES LESS THAN ('2018-03-01') ENGINE = InnoDB,
 PARTITION b9 VALUES LESS THAN ('2018-04-01') ENGINE = InnoDB,
 PARTITION b10 VALUES LESS THAN ('2018-05-01') ENGINE = InnoDB,
 PARTITION b11 VALUES LESS THAN ('2018-06-01') ENGINE = InnoDB,
 PARTITION b12 VALUES LESS THAN ('2018-07-01') ENGINE = InnoDB,
 PARTITION b13 VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB)

I try to reorganize the b13 partition with following command

ALTER TABLE sat_msgPos REORGANIZE PARTITION b13 INTO(PARTITION c1 VALUES LESS THAN ('2018-08-01'), PARTITION c2 VALUES LESS THAN ('2018-09-01'));

I am receiving following error:

"ERROR 1520 (HY000): Reorganize of range partitions cannot change total ranges except for last partition where it can extend the range
"

How can i split b13 into 2 new partitons without lose the data?

Thanks a lot!

Best Answer

Think of it this way -- REORGANIZE PARTITION reads the source partition(s) and creates the destination partition(s), putting each row wherever it belongs. If the source range and destination range are identical (but broken up differently), then nothing can get lost.

Some comments on the schema:

PRIMARY KEY (`idMsg`,`idVeh`,`tmMsg`),

idMsg is unique; tmMsg needed to be added because it is the "partition key". But is there any reason to include idVeh? (There may be, bot note each of your 4 secondary keys will include all 3 of these columns -- somewhat bulky.)

KEY `X3` (`tmMsg`),

It is usually useless to have the partition key at the start of an index. (Again, you may have a good reason; I am suggesting that this might be a useless index.)

KEY `X4` (`idVeh`,`tmMsg`,`idTrigger`)

Unless you are using = when testing tmMsg, this index is unlikely to get to the idTrigger column. (Again, I am asking you to justify it; there could be a SELECT that shines because of this index; but it seems unlikely.)

`amLat` double DEFAULT NULL,
`amLong` double DEFAULT NULL,

Are you tracking fleas on dogs? 16 significant digits is overkill. See here.

About the only benefit in PARTITION by a time is to purge old data. (DROP PARTITION is a lot less invasive than DELETE.) If you think you are getting some other performance benefit, let me know; I may argue with you.

Related Question