Hello,
I have a problem with centreon_storage.logs table and partitioning.
I’ve drop table, remove /var/lib/mysql/logs*.frm and .idb
I recreated the table with contents of file :
/usr/share/centreon/config/partition.d/partitioning-logs.xml
CREATE TABLE `logs` (
`log_id` bigint(20) NOT NULL AUTO_INCREMENT,
`ctime` int(11) DEFAULT NULL,
`host_id` int(11) DEFAULT NULL,
`host_name` varchar(255) DEFAULT NULL,
`instance_name` varchar(255) NOT NULL,
`issue_id` int(11) DEFAULT NULL,
`msg_type` tinyint(4) DEFAULT NULL,
`notification_cmd` varchar(255) DEFAULT NULL,
`notification_contact` varchar(255) DEFAULT NULL,
`output` text,
`retry` int(11) DEFAULT NULL,
`service_description` varchar(255) DEFAULT NULL,
`service_id` int(11) DEFAULT NULL,
`status` tinyint(4) DEFAULT NULL,
`type` smallint(6) DEFAULT NULL,
KEY `log_id` (`log_id`),
KEY `host_name` (`host_name`(64)),
KEY `service_description` (`service_description`(64)),
KEY `status` (`status`),
KEY `instance_name` (`instance_name`),
KEY `ctime` (`ctime`),
KEY `rq1` (`host_id`,`service_id`,`msg_type`,`status`,`ctime`),
KEY `rq2` (`host_id`,`msg_type`,`status`,`ctime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Then I run the convert script (source : https://docs.centreon.com/fr/docs/administration/database-partitioning/#migration-des-tables-non-partitionn%C3%A9es)
/bin/php /usr/share/centreon/bin/centreon-partitioning.php -m logs
which returns me :
aThu, 18 Jul 24 10:35:22 +0200] PARTITIONING STARTED
bThu, 18 Jul 24 10:35:22 +0200]0migrate] Renaming table `centreon_storage`.logs TO `centreon_storage`.logs_old
bThu, 18 Jul 24 10:35:22 +0200]0migrate] Creating parts for new table `centreon_storage`.logs
bThu, 18 Jul 24 10:35:29 +0200] Error : Cannot create table `centreon_storage`.logs with partitions, SQLSTATES42S01]: Base table or view already exists: 1050 Table '`centreon_storage`.`logs`
/* Partition `p20240328` */' already exists
The played partitioning query is :
--- PARTITION BY RANGE(ctime) (PARTITION p20240120 VALUES LESS THAN (1705705200),PARTITION p20240121 VALUES LESS THAN (1705791600),PARTITION p20240122 VALUES LESS THAN (1705878000),PARTITION
p20240123 VALUES LESS THAN (1705964400),PARTITION p20240124 VALUES LESS THAN (1706050800),PARTITION p20240125 VALUES LESS THAN (1706137200),PARTITION p20240126 VALUES LESS THAN (1706223600)
,PARTITION p20240127 VALUES LESS THAN (1706310000),PARTITION p20240128 VALUES LESS THAN (1706396400),PARTITION p20240129 VALUES LESS THAN (1706482800),PARTITION p20240130 VALUES LESS THAN (1
706569200),PARTITION p20240131 VALUES LESS THAN (1706655600),PARTITION p20240201 VALUES LESS THAN (1706742000),PARTITION p20240202 VALUES LESS THAN (1706828400),PARTITION p20240203 VALUES LE
SS THAN (1706914800),PARTITION p20240204 VALUES LESS THAN (1707001200),PARTITION p20240205 VALUES LESS THAN (1707087600),PARTITION p20240206 VALUES LESS THAN (1707174000),PARTITION p20240207
VALUES LESS THAN (1707260400),PARTITION p20240208 VALUES LESS THAN (1707346800),PARTITION p20240209 VALUES LESS THAN (1707433200),PARTITION p20240210 VALUES LESS THAN (1707519600),PARTITION
p20240211 VALUES LESS THAN (1707606000),PARTITION p20240212 VALUES LESS THAN (1707692400),PARTITION p20240213 VALUES LESS THAN (1707778800),PARTITION p20240214 VALUES LESS THAN (1707865200)
,PARTITION p20240215 VALUES LESS THAN (1707951600),PARTITION p20240216 VALUES LESS THAN (1708038000),PARTITION p20240217 VALUES LESS THAN (1708124400),PARTITION p20240218 VALUES LESS THAN (1
708210800),PARTITION p20240219 VALUES LESS THAN (1708297200),PARTITION p20240220 VALUES LESS THAN (1708383600),PARTITION p20240221 VALUES LESS THAN (1708470000),PARTITION p20240222 VALUES LE
SS THAN (1708556400),PARTITION p20240223 VALUES LESS THAN (1708642800),PARTITION p20240224 VALUES LESS THAN (1708729200),PARTITION p20240225 VALUES LESS THAN (1708815600),PARTITION p20240226
VALUES LESS THAN (1708902000),PARTITION p20240227 VALUES LESS THAN (1708988400),PARTITION p20240228 VALUES LESS THAN (1709074800),PARTITION p20240229 VALUES LESS THAN (1709161200),PARTITION
p20240301 VALUES LESS THAN (1709247600),PARTITION p20240302 VALUES LESS THAN (1709334000),PARTITION p20240303 VALUES LESS THAN (1709420400),PARTITION p20240304 VALUES LESS THAN (1709506800)
,PARTITION p20240305 VALUES LESS THAN (1709593200),PARTITION p20240306 VALUES LESS THAN (1709679600),PARTITION p20240307 VALUES LESS THAN (1709766000),PARTITION p20240308 VALUES LESS THAN (1
709852400),PARTITION p20240309 VALUES LESS THAN (1709938800),PARTITION p20240310 VALUES LESS THAN (1710025200),PARTITION p20240311 VALUES LESS THAN (1710111600),PARTITION p20240312 VALUES LE
SS THAN (1710198000),PARTITION p20240313 VALUES LESS THAN (1710284400),PARTITION p20240314 VALUES LESS THAN (1710370800),PARTITION p20240315 VALUES LESS THAN (1710457200),PARTITION p20240316
VALUES LESS THAN (1710543600),PARTITION p20240317 VALUES LESS THAN (1710630000),PARTITION p20240318 VALUES LESS THAN (1710716400),PARTITION p20240319 VALUES LESS THAN (1710802800),PARTITION
p20240320 VALUES LESS THAN (1710889200),PARTITION p20240321 VALUES LESS THAN (1710975600),PARTITION p20240322 VALUES LESS THAN (1711062000),PARTITION p20240323 VALUES LESS THAN (1711148400)
,PARTITION p20240324 VALUES LESS THAN (1711234800),PARTITION p20240325 VALUES LESS THAN (1711321200),PARTITION p20240326 VALUES LESS THAN (1711407600),PARTITION p20240327 VALUES LESS THAN (1
711494000),PARTITION p20240328 VALUES LESS THAN (1711580400),PARTITION p20240329 VALUES LESS THAN (1711666800),PARTITION p20240330 VALUES LESS THAN (1711753200),PARTITION p20240331 VALUES LE
SS THAN (1711839600),PARTITION p20240401 VALUES LESS THAN (1711922400),PARTITION p20240402 VALUES LESS THAN (1712008800),PARTITION p20240403 VALUES LESS THAN (1712095200),PARTITION p20240404
VALUES LESS THAN (1712181600),PARTITION p20240405 VALUES LESS THAN (1712268000),PARTITION p20240406 VALUES LESS THAN (1712354400),PARTITION p20240407 VALUES LESS THAN (1712440800),PARTITION
p20240408 VALUES LESS THAN (1712527200),PARTITION p20240409 VALUES LESS THAN (1712613600),PARTITION p20240410 VALUES LESS THAN (1712700000),PARTITION p20240411 VALUES LESS THAN (1712786400)
,PARTITION p20240412 VALUES LESS THAN (1712872800),PARTITION p20240413 VALUES LESS THAN (1712959200),PARTITION p20240414 VALUES LESS THAN (1713045600),PARTITION p20240415 VALUES LESS THAN (1
713132000),PARTITION p20240416 VALUES LESS THAN (1713218400),PARTITION p20240417 VALUES LESS THAN (1713304800),PARTITION p20240418 VALUES LESS THAN (1713391200),PARTITION p20240419 VALUES LE
SS THAN (1713477600),PARTITION p20240420 VALUES LESS THAN (1713564000),PARTITION p20240421 VALUES LESS THAN (1713650400),PARTITION p20240422 VALUES LESS THAN (1713736800),PARTITION p20240423
VALUES LESS THAN (1713823200),PARTITION p20240424 VALUES LESS THAN (1713909600),PARTITION p20240425 VALUES LESS THAN (1713996000),PARTITION p20240426 VALUES LESS THAN (1714082400),PARTITION
p20240427 VALUES LESS THAN (1714168800),PARTITION p20240428 VALUES LESS THAN (1714255200),PARTITION p20240429 VALUES LESS THAN (1714341600),PARTITION p20240430 VALUES LESS THAN (1714428000)
,PARTITION p20240501 VALUES LESS THAN (1714514400),PARTITION p20240502 VALUES LESS THAN (1714600800),PARTITION p20240503 VALUES LESS THAN (1714687200),PARTITION p20240504 VALUES LESS THAN (1
714773600),PARTITION p20240505 VALUES LESS THAN (1714860000),PARTITION p20240506 VALUES LESS THAN (1714946400),PARTITION p20240507 VALUES LESS THAN (1715032800),PARTITION p20240508 VALUES LE
SS THAN (1715119200),PARTITION p20240509 VALUES LESS THAN (1715205600),PARTITION p20240510 VALUES LESS THAN (1715292000),PARTITION p20240511 VALUES LESS THAN (1715378400),PARTITION p20240512
VALUES LESS THAN (1715464800),PARTITION p20240513 VALUES LESS THAN (1715551200),PARTITION p20240514 VALUES LESS THAN (1715637600),PARTITION p20240515 VALUES LESS THAN (1715724000),PARTITION
p20240516 VALUES LESS THAN (1715810400),PARTITION p20240517 VALUES LESS THAN (1715896800),PARTITION p20240518 VALUES LESS THAN (1715983200),PARTITION p20240519 VALUES LESS THAN (1716069600)
,PARTITION p20240520 VALUES LESS THAN (1716156000),PARTITION p20240521 VALUES LESS THAN (1716242400),PARTITION p20240522 VALUES LESS THAN (1716328800),PARTITION p20240523 VALUES LESS THAN (1
716415200),PARTITION p20240524 VALUES LESS THAN (1716501600),PARTITION p20240525 VALUES LESS THAN (1716588000),PARTITION p20240526 VALUES LESS THAN (1716674400),PARTITION p20240527 VALUES LE
SS THAN (1716760800),PARTITION p20240528 VALUES LESS THAN (1716847200),PARTITION p20240529 VALUES LESS THAN (1716933600),PARTITION p20240530 VALUES LESS THAN (1717020000),PARTITION p20240531
VALUES LESS THAN (1717106400),PARTITION p20240601 VALUES LESS THAN (1717192800),PARTITION p20240602 VALUES LESS THAN (1717279200),PARTITION p20240603 VALUES LESS THAN (1717365600),PARTITION
p20240604 VALUES LESS THAN (1717452000),PARTITION p20240605 VALUES LESS THAN (1717538400),PARTITION p20240606 VALUES LESS THAN (1717624800),PARTITION p20240607 VALUES LESS THAN (1717711200)
,PARTITION p20240608 VALUES LESS THAN (1717797600),PARTITION p20240609 VALUES LESS THAN (1717884000),PARTITION p20240610 VALUES LESS THAN (1717970400),PARTITION p20240611 VALUES LESS THAN (1
718056800),PARTITION p20240612 VALUES LESS THAN (1718143200),PARTITION p20240613 VALUES LESS THAN (1718229600),PARTITION p20240614 VALUES LESS THAN (1718316000),PARTITION p20240615 VALUES LE
SS THAN (1718402400),PARTITION p20240616 VALUES LESS THAN (1718488800),PARTITION p20240617 VALUES LESS THAN (1718575200),PARTITION p20240618 VALUES LESS THAN (1718661600),PARTITION p20240619
VALUES LESS THAN (1718748000),PARTITION p20240620 VALUES LESS THAN (1718834400),PARTITION p20240621 VALUES LESS THAN (1718920800),PARTITION p20240622 VALUES LESS THAN (1719007200),PARTITION
p20240623 VALUES LESS THAN (1719093600),PARTITION p20240624 VALUES LESS THAN (1719180000),PARTITION p20240625 VALUES LESS THAN (1719266400),PARTITION p20240626 VALUES LESS THAN (1719352800)
,PARTITION p20240627 VALUES LESS THAN (1719439200),PARTITION p20240628 VALUES LESS THAN (1719525600),PARTITION p20240629 VALUES LESS THAN (1719612000),PARTITION p20240630 VALUES LESS THAN (1
719698400),PARTITION p20240701 VALUES LESS THAN (1719784800),PARTITION p20240702 VALUES LESS THAN (1719871200),PARTITION p20240703 VALUES LESS THAN (1719957600),PARTITION p20240704 VALUES LE
SS THAN (1720044000),PARTITION p20240705 VALUES LESS THAN (1720130400),PARTITION p20240706 VALUES LESS THAN (1720216800),PARTITION p20240707 VALUES LESS THAN (1720303200),PARTITION p20240708
VALUES LESS THAN (1720389600),PARTITION p20240709 VALUES LESS THAN (1720476000),PARTITION p20240710 VALUES LESS THAN (1720562400),PARTITION p20240711 VALUES LESS THAN (1720648800),PARTITION
p20240712 VALUES LESS THAN (1720735200),PARTITION p20240713 VALUES LESS THAN (1720821600),PARTITION p20240714 VALUES LESS THAN (1720908000),PARTITION p20240715 VALUES LESS THAN (1720994400)
,PARTITION p20240716 VALUES LESS THAN (1721080800),PARTITION p20240717 VALUES LESS THAN (1721167200),PARTITION p20240718 VALUES LESS THAN (1721253600),PARTITION p20240719 VALUES LESS THAN (1
721340000),PARTITION p20240720 VALUES LESS THAN (1721426400),PARTITION p20240721 VALUES LESS THAN (1721512800),PARTITION p20240722 VALUES LESS THAN (1721599200),PARTITION p20240723 VALUES LE
SS THAN (1721685600),PARTITION p20240724 VALUES LESS THAN (1721772000),PARTITION p20240725 VALUES LESS THAN (1721858400),PARTITION p20240726 VALUES LESS THAN (1721944800),PARTITION p20240727
VALUES LESS THAN (1722031200),PARTITION p20240728 VALUES LESS THAN (1722117600));---
I’ll try to dump db centron_storage, drop it and reimport it without logs table.
But if anyone has an explanation…
Regards