Skip to main content

Hello,

 

Since I upgraded to 21.10, the service ‘Partitioning’ on my server goes critical each 10 days because the database isn’t up to date.

I did the partitionning mannually twice using this : https://www.sugarbug.fr/blog/files/troubleshooting-partition-mariadb.html

Last time I tried adding

0 4 * * * centreon /bin/php /usr/share/centreon/cron/centreon-partitioning.php >> /var/log/centreon/centreon-partitioning.log 2>&1

to the root user crontab. But today my service ‘warning’ and dates are from 10/02.

 

I also checked the user ‘centreon’ crontab which is empty, I don’t know if it’s normal

----

Also the freespace on centreon database is 0, I would like to add space (if it’s possible) before it cause issues.

---

Thank you for your assistance :)

 

Hello Benjamin,

 

Normally, the file /etc/cron.d/centstorage is deployed by the installation of the centreon-web package.

Do you have it?

It contains 2 cronjobs including this one:

# Cron for database partitioning
0 4 * * * centreon /usr/bin/php /usr/share/centreon/cron/centreon-partitioning.php >> /var/log/centreon/centreon-partitioning.log 2>&1

 

Can you give us the output of these 2 commands?

systemctl status crond
su - centreon -c '/usr/bin/php /usr/share/centreon/cron/centreon-partitioning.php'

 

Good afternoon sduret

 

Here are the results of the commands :

>root@s-sup ~]# systemctl status crond
● crond.service - Command Scheduler
   Loaded: loaded (/usr/lib/systemd/system/crond.service; enabled; vendor preset: enabled)
   Active: active (running) since jeu. 2022-02-17 18:05:57 CET; 20h ago
 Main PID: 766 (crond)
   CGroup: /system.slice/crond.service
           └─766 /usr/sbin/crond -n

févr. 17 18:05:57 servername systemd 1]: Started Command Scheduler.
févr. 17 18:05:57 servername  crondm766]: (CRON) INFO (RANDOM_DELAY will be scaled with factor 66% if used.)
févr. 17 18:05:57 servername  cronda766]: (CRON) INFO (running with inotify support)

 

uroot@s-sup ~]# su - centreon -c '/usr/bin/php /usr/share/centreon/cron/centreon-partitioning.php'
.Fri, 18 Feb 22 14:50:55 +0100] PARTITIONING STARTED
AFri, 18 Feb 22 14:50:55 +0100] Error: cannot add a new partition 'p20220219' for table centreon_storage.data_bin, SQLSTATEnHY000]: General error: 1517 Duplicate partition name p20220219

 

And here is the cron.d file for centreon

 

# Cron Configuration for Centreon
#
PATH=/sbin:/bin:/usr/sbin:/usr/bin

# rewrite file with new cron line
CRONTAB_EXEC_USER=""

# Cron for databin and logs purge
0 2 * * * centreon /usr/bin/php /usr/share/centreon/cron/centstorage_purge.php >> /var/log/centreon/centreon-purge.log 2>&1

# Cron for database partitioning
0 4 * * * centreon /usr/bin/php /usr/share/centreon/cron/centreon-partitioning.php >> /var/log/centreon/centreon-partitioning.log 2>&1

>root@s-sup ~]#


 

It seems taht you have corrupt data in the DB.
Can you try to delete this partition?

USE centreon_storage
ALTER TABLE data_bin DROP PARTITION p20220219;

 

That doesn’t sound good^^

 

MariaDB D(none)]> USE centreon_storage
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB Dcentreon_storage]> ALTER TABLE data_bin DROP PARTITION p20220219;
Query OK, 0 rows affected (0.095 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB Dcentreon_storage]> exit
Bye
 root@s-sup ~]# su - centreon -c '/usr/bin/php /usr/share/centreon/cron/centreon-partitioning.php'
 Fri, 18 Feb 22 16:20:40 +0100] PARTITIONING STARTED
 Fri, 18 Feb 22 16:20:40 +0100] Error: cannot add a new partition 'p20220219' for table centreon_storage.data_bin, SQLSTATEAHY000]: General error: 1493 VALUES LESS THAN value must be strictly increasing for each partition
 root@s-sup ~]#

 

I found this :

https://github.com/centreon/centreon/pull/10591/files

My file match the unedited one but it’s way beyond my understanding, so I can’t say if it’ll break instead of  resolve my issue.

Can you execute this query and send us the last 20 lines?

USE centreon_storage
SHOW CREATE TABLE data_bin;

 

here they are, that’s the 20 last lines with text.

After this there are : +---------------+------- during a couple of lines

 


 PARTITION p20220131 VALUES LESS THAN (1643583600) ENGINE = InnoDB,
 PARTITION p20220201 VALUES LESS THAN (1643670000) ENGINE = InnoDB,
 PARTITION p20220202 VALUES LESS THAN (1643756400) ENGINE = InnoDB,
 PARTITION p20220203 VALUES LESS THAN (1643842800) ENGINE = InnoDB,
 PARTITION p20220204 VALUES LESS THAN (1643929200) ENGINE = InnoDB,
 PARTITION p20220205 VALUES LESS THAN (1644015600) ENGINE = InnoDB,
 PARTITION p20220206 VALUES LESS THAN (1644102000) ENGINE = InnoDB,
 PARTITION p20220207 VALUES LESS THAN (1644188400) ENGINE = InnoDB,
 PARTITION p20220208 VALUES LESS THAN (1644274800) ENGINE = InnoDB,
 PARTITION p20220209 VALUES LESS THAN (1644361200) ENGINE = InnoDB,
 PARTITION p20220210 VALUES LESS THAN (1644447600) ENGINE = InnoDB,
 PARTITION p20220211 VALUES LESS THAN (1644534000) ENGINE = InnoDB,
 PARTITION p20220212 VALUES LESS THAN (1644620400) ENGINE = InnoDB,
 PARTITION p20220213 VALUES LESS THAN (1644706800) ENGINE = InnoDB,
 PARTITION p20220214 VALUES LESS THAN (1644793200) ENGINE = InnoDB,
 PARTITION p20220215 VALUES LESS THAN (1644879600) ENGINE = InnoDB,
 PARTITION p20220216 VALUES LESS THAN (1644966000) ENGINE = InnoDB,
 PARTITION p20220217 VALUES LESS THAN (1645052400) ENGINE = InnoDB,
 PARTITION p20220218 VALUES LESS THAN (1645138800) ENGINE = InnoDB,
 PARTITION p20220220 VALUES LESS THAN (1645311600) ENGINE = InnoDB,
 PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */ |

 

Can you execute this queries?

USE centreon_storage
ALTER TABLE data_bin DROP PARTITION p20220220;
ALTER TABLE data_bin DROP PARTITION pmax;
ALTER TABLE data_bin ADD PARTITION (PARTITION p20220220 VALUES LESS THAN (1645225200));
ALTER TABLE data_bin ADD PARTITION (PARTITION p20220220 VALUES LESS THAN (1645311600));
​​​​​​​ALTER TABLE data_bin ADD PARTITION (PARTITION pmax VALUES LESS THAN MAXVALUE);

After that, can you try to execute once again the script?

 

If you meet the error message “ Duplicate partition name p20220219”, please try the GitHub fix.


MariaDB [centreon_storage]> ALTER TABLE data_bin DROP PARTITION p20220219;
Query OK, 0 rows affected (0.191 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [centreon_storage]> ALTER TABLE data_bin DROP PARTITION p20220220;
Query OK, 0 rows affected (0.053 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [centreon_storage]> ALTER TABLE data_bin DROP PARTITION pmax;
Query OK, 0 rows affected (0.053 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [centreon_storage]> ALTER TABLE data_bin ADD PARTITION (PARTITION p20220220 VALUES LESS THAN (1645225200));
Query OK, 0 rows affected (0.059 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [centreon_storage]> ALTER TABLE data_bin ADD PARTITION (PARTITION p20220220 VALUES LESS THAN (1645311600));
ERROR 1517 (HY000): Duplicate partition name p20220220
MariaDB [centreon_storage]> ​​​​​​​ALTER TABLE data_bin ADD PARTITION (PARTITION pmax VALUES LESS THAN MAXVALUE);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '​​​​​​​ALTER TABLE data_bin ADD PARTITION (PARTITION pmax VALUES L' at line 1
MariaDB Scentreon_storage]>

Just add the github fix, should i wait to see if the cron run correctly ?

I did run

su - centreon -c '/usr/bin/php /usr/share/centreon/cron/centreon-partitioning.php'

again, but still the same error.

 

Should I restore  my snapshot ?

Sorry I did a mistake on the first alter table. 

Can you execute this? 

USE centreon_storage

ALTER TABLE data_bin DROP PARTITION p20220220;

ALTER TABLE data_bin DROP PARTITION pmax;

ALTER TABLE data_bin ADD PARTITION (PARTITION p202202219 VALUES LESS THAN (1645225200));

ALTER TABLE data_bin ADD PARTITION (PARTITION p20220220 VALUES LESS THAN (1645311600));

ALTER TABLE data_bin ADD PARTITION (PARTITION pmax VALUES LESS THAN MAXVALUE);

Hi,

I got one error :

 


Database changed
MariaDB  centreon_storage]> ALTER TABLE data_bin DROP PARTITION p20220220;
Query OK, 0 rows affected (0.094 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB  centreon_storage]> ALTER TABLE data_bin DROP PARTITION pmax;
ERROR 1507 (HY000): Error in list of partitions to DROP
MariaDB  centreon_storage]> ALTER TABLE data_bin ADD PARTITION (PARTITION p202202219 VALUES LESS THAN (1645225200));
Query OK, 0 rows affected (0.200 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB  centreon_storage]> ALTER TABLE data_bin ADD PARTITION (PARTITION p20220220 VALUES LESS THAN (1645311600));
Query OK, 0 rows affected (0.073 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB  centreon_storage]> ALTER TABLE data_bin ADD PARTITION (PARTITION pmax VALUES LESS THAN MAXVALUE);
Query OK, 0 rows affected (0.081 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB  centreon_storage]>

 

Hi

 

Ok it’s normal because you already deleted this partition.

 

Now, can you execute the cronjob manually?

I tried the first command you gave me :

su - centreon -c '/usr/bin/php /usr/share/centreon/cron/centreon-partitioning.php'
[Mon, 21 Feb 22 10:28:23 +0100] PARTITIONING STARTED
[Mon, 21 Feb 22 10:28:30 +0100] PARTITIONING COMPLETED


 

Service is now ‘OK’ again

 
OK: All table partitions are up to date

 

Cron run everyday right ?

I’ll wait until tomorrow to see if partitioning is done correctly and i’ll close that ticket 😉 thx

Yes everyday at 4am.

 

Thanks for your feedback

Hi sduret,

 

Seems it works now !

The creation date is today for the four tables.

 

Thank you !

Hello @sduret 

It worked 2 days then it broke again 😞 Service is critical again.

 

I tried to use the same methodology than you :

 

# su - centreon -c '/usr/bin/php /usr/share/centreon/cron/centreon-partitioning.php'
 Thu, 03 Mar 22 11:15:04 +0100] PARTITIONING STARTED
 Thu, 03 Mar 22 11:15:04 +0100] Error: cannot add a new partition 'p20220304' for table centreon_storage.data_bin, SQLSTATEAHY000]: General error: 1517 Duplicate partition name p20220304



---------------

MariaDB Dcentreon_storage]> ALTER TABLE data_bin DROP PARTITION p20220304;
ERROR 1507 (HY000): Error in list of partitions to DROP (did drop it earlier but cant get the result of the command)
MariaDB Dcentreon_storage]> ALTER TABLE data_bin DROP PARTITION p20220305;
Query OK, 0 rows affected (0.056 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB Dcentreon_storage]> ALTER TABLE data_bin DROP PARTITION pmax;
Query OK, 0 rows affected (0.031 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB Dcentreon_storage]> ALTER TABLE data_bin ADD PARTITION (PARTITION p20220304 VALUES LESS THAN (1646348400));
Query OK, 0 rows affected (0.037 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB Dcentreon_storage]> ALTER TABLE data_bin ADD PARTITION (PARTITION p20220305 VALUES LESS THAN (1646434800));
Query OK, 0 rows affected (0.028 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB Dcentreon_storage]> ALTER TABLE data_bin ADD PARTITION (PARTITION pmax VALUES LESS THAN MAXVALUE);
Query OK, 0 rows affected (0.032 sec)
Records: 0  Duplicates: 0  Warnings: 0

------
result of  SHOW CREATE TABLE data_bin;

 PARTITION p202202219 VALUES LESS THAN (1645225200) ENGINE = InnoDB,
 PARTITION p20220220 VALUES LESS THAN (1645311600) ENGINE = InnoDB,
 PARTITION p20220221 VALUES LESS THAN (1645398000) ENGINE = InnoDB,
 PARTITION p20220222 VALUES LESS THAN (1645484400) ENGINE = InnoDB,
 PARTITION p20220223 VALUES LESS THAN (1645570800) ENGINE = InnoDB,
 PARTITION p20220224 VALUES LESS THAN (1645657200) ENGINE = InnoDB,
 PARTITION p20220225 VALUES LESS THAN (1645743600) ENGINE = InnoDB,
 PARTITION p20220226 VALUES LESS THAN (1645830000) ENGINE = InnoDB,
 PARTITION p20220227 VALUES LESS THAN (1645916400) ENGINE = InnoDB,
 PARTITION p20220228 VALUES LESS THAN (1646002800) ENGINE = InnoDB,
 PARTITION p20220301 VALUES LESS THAN (1646089200) ENGINE = InnoDB,
 PARTITION p20220302 VALUES LESS THAN (1646175600) ENGINE = InnoDB,
 PARTITION p20220303 VALUES LESS THAN (1646262000) ENGINE = InnoDB,
 PARTITION p20220305 VALUES LESS THAN (1646434800) ENGINE = InnoDB,
 PARTITION pmax VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */ |

 

 

Is my databse forever corrupted ? Should I open a new topic ?

Thank you again for your help

 

end of message is missing :

 

After sending this commands, the cron still don’t work

 

Well somehow the fix from

https://github.com/centreon/centreon/pull/10591/files

was gone.

I applied it again and now partitionning works since a couple of days :)

👆

https://github.com/centreon/centreon-archived/pull/10591 

Reply


Terms & ConditionsCookie settings