Solved

21.10 - Database Partitioning not up to date

  • 17 February 2022
  • 21 replies
  • 3329 views

Userlevel 1
Badge +6

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 :)

 

icon

Best answer by sduret 18 February 2022, 19:09

View original

21 replies

Userlevel 4
Badge +13

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'

 

Userlevel 1
Badge +6

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 crond[766]: (CRON) INFO (RANDOM_DELAY will be scaled with factor 66% if used.)
févr. 17 18:05:57 servername crond[766]: (CRON) INFO (running with inotify support)

 

[root@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
[Fri, 18 Feb 22 14:50:55 +0100] Error: cannot add a new partition 'p20220219' for table centreon_storage.data_bin, SQLSTATE[HY000]: General error: 1517 Duplicate partition name p20220219

 

Userlevel 1
Badge +6

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 ~]#


 

Userlevel 4
Badge +13

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;

 

Userlevel 1
Badge +6

That doesn’t sound good^^

 

MariaDB [(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 [centreon_storage]> ALTER TABLE data_bin DROP PARTITION p20220219;
Query OK, 0 rows affected (0.095 sec)
Records: 0 Duplicates: 0 Warnings: 0

MariaDB [centreon_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, SQLSTATE[HY000]: General error: 1493 VALUES LESS THAN value must be strictly increasing for each partition
[root@s-sup ~]#

 

Userlevel 1
Badge +6

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.

Userlevel 4
Badge +13

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

USE centreon_storage
SHOW CREATE TABLE data_bin;

 

Userlevel 1
Badge +6

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) */ |

 

Userlevel 4
Badge +13

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.

Userlevel 1
Badge +6

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 [centreon_storage]>

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

Userlevel 1
Badge +6

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 ?

Userlevel 4
Badge +13

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);

Userlevel 1
Badge +6

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]>

 

Userlevel 4
Badge +13

Hi

 

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

 

Now, can you execute the cronjob manually?

Userlevel 1
Badge +6

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

Userlevel 4
Badge +13

Yes everyday at 4am.

 

Thanks for your feedback

Userlevel 1
Badge +6

Hi sduret,

 

Seems it works now !

The creation date is today for the four tables.

 

Thank you !

Userlevel 1
Badge +6

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, SQLSTATE[HY000]: General error: 1517 Duplicate partition name p20220304



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

MariaDB [centreon_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 [centreon_storage]> ALTER TABLE data_bin DROP PARTITION p20220305;
Query OK, 0 rows affected (0.056 sec)
Records: 0 Duplicates: 0 Warnings: 0

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

MariaDB [centreon_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 [centreon_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 [centreon_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

 

Userlevel 1
Badge +6

end of message is missing :

 

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

[Thu, 03 Mar 22 11:22:31 +0100] Error: cannot add a new partition 'p20220304' for table centreon_storage.data_bin, SQLSTATE[HY000]: General error: 1517 Duplicate partition name p20220304

 

Userlevel 1
Badge +6

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 :)

Userlevel 4
Badge +13

👆

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

Reply