Question

Unable to partition the "data_bin" table anymore as it has been deleted.

  • 11 December 2023
  • 3 replies
  • 321 views
A
Badge +1

Hi everyone,

I hope you're all doing well. I'm facing an issue with our database.

Here's the situation:

Recently, our database encountered a crash due to being full, and as a result, we lost some data. Specifically, the partitioned table "data_bin" was inadvertently deleted during the crash. Now, I'm trying to repartition the table, but I'm encountering an error stating that a partition already exists.

-bash-4.2# /bin/php /usr/share/centreon/bin/centreon-partitioning.php -m data_bin

[Mon, 11 Dec 23 17:31:23 +0100] PARTITIONING STARTED

[Mon, 11 Dec 23 17:31:23 +0100][migrate] Renaming table centreon_storage.data_bin TO centreon_storage.data_bin_old

[Mon, 11 Dec 23 17:31:23 +0100][migrate] Creating parts for new table centreon_storage.data_bin

[Mon, 11 Dec 23 17:31:23 +0100] Error : Cannot create table centreon_storage.data_bin with partitions, SQLSTATE[HY000]: General error: 1813 Tablespace for table '`centreon_storage`.`data_bin` /* Partition `p20221211` */' exists. Please DISCARD the tablespace before IMPORT

[Mon, 11 Dec 23 17:31:23 +0100] PARTITIONING EXITED

I have tried various approaches to resolve this issue, but so far, I haven't been successful. I'm reaching out to see if anyone has experienced a similar problem or has any suggestions on how to recover the partitioned table and get it working properly again.

Thank you in advance for your help!

3 replies

Laurent
Rank
Userlevel 6
Badge +18

Hi @Achraf04 

Create table using:

CREATE TABLE IF NOT EXISTS `data_bin` (
  `id_metric` int(11) DEFAULT NULL,
  `ctime` int(11) DEFAULT NULL,
  `value` float DEFAULT NULL,
  `status` enum('0','1','2','3','4') DEFAULT NULL,
  KEY `index_metric` (`id_metric`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Then execute cron manually:

php /usr/share/centreon/cron/centreon-partitioning.php

 

A
Badge +1

Thank you for your answer @Laurent.

However, I have already created the table after the crash. Unfortunately, I encountered an error indicating that a partition already exists.

One thing that caught my attention is when I manually executed the partition cron, the output of the partition p<year><month><day> showed the same day as the execution, but the year was different.

Should I make any changes in the configuration or do I need to delete all partitions in the MySQL disk storage /var/lib/mysql/centreon_storage/? I'm concerned about losing the data bin_data.

AltGr
Userlevel 1
Badge +4

Hi @Achraf04 

 

Are you sure that you will need the older data_bin datas in the future ? :)


Actually, they’re required if you want to rebuild your graphs, of if you use Centreon MBI and need to rebuild your performances datas. That’s all.
So you certainly can restart with an empty data_bin table, it will directly affect the monitoring only if you have to rebuild some graphs (their history will start at the first data_bin data if you rebuild).

 

If you still have a lot of datas on the centreon_storage database (a big retention conf. for logs, etc.) and a whole dump seems to be complicated due to the time it will take, you can try to manually delete the partitions on the disk, but it can be risky to broke the consistency even more.

I’m not a DBA but  IMO the proper way to resolve such an inconsistency should be to :

  1. Make a backup (VM snapshot, LVM snapshot...)
  1. dump centreon_storage database (as its data_bin table is corrupted, you certainly have nothing from it on the dump)
  2. drop the centreon_storage DB (from the SQL console, not on the FS!)
  3. stop MySQL and check that the centreon_storage folder had been deleted. If it’s not the case, rm it
  4. start MySQL, create a new empty centreon_storage database
  5. import your dump
  6. create the data_bin table with the Laurent’s infos
  7. use the PHP partitioning script on data_bin
  8. check & enjoy

 

Last but not least, the good PHP script to use is /usr/share/centreon/bin/centreon-partitioning.php , not /usr/share/centreon/cron/centreon-partitioning.php as the last one will not create all your partitions since your retention conf, just update the futures ones.

Reply


Terms & ConditionsCookie settings