1

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!

1

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.

0

In your query you are missing MAXVAL partition. Use following:

ALTER TABLE sat_msgPos REORGANIZE PARTITION b13 INTO(PARTITION c1 VALUES LESS THAN ('2018-08-01'), PARTITION c2 VALUES LESS THAN MAXVALUE);
  • 1
    Thank you! The query work well. I have tried on an empty table. One more question please to be sure i do not lose the data from partition b13? – Isabella Popa Aug 22 '18 at 10:32
  • 1
    @IsabellaPopa i do not lose the data from partition b13? Error message you obtaines instead of performing the action prevents possible data loss. New query have no this hole, so all your data will be kept. – Akina Aug 22 '18 at 10:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.