[solved] How to add missing items to Cacti database

[alm]

Hi, I'm new to MySQL and don't know how to fix this.

I have a Cacti 0.8.6k installation running on Windows (MySQL 4.1.x and PHP 5.2.x) and want to upgrade it to the latest version but when I try this I get the following error messages:

Code: Select all

0.8.6k -> 0.8.7

[Fail] ALTER TABLE `poller_item` ADD INDEX `rrd_next_step`(`rrd_next_step`)

0.8.7b -> 0.8.7c

[Fail] ALTER TABLE `poller_item` MODIFY COLUMN `rrd_next_step` MEDIUMINT(8) NOT NULL DEFAULT '0';

[Fail] ALTER TABLE `poller_item` MODIFY COLUMN `rrd_step` MEDIUMINT(8) UNSIGNED NOT NULL DEFAULT '300';

[Fail] ALTER TABLE `poller_time` MODIFY COLUMN `pid` INT(11) UNSIGNED NOT NULL DEFAULT '0';

[Fail] ALTER TABLE `poller_item` MODIFY COLUMN `rrd_step` MEDIUMINT(8) NOT NULL DEFAULT 300

I have not installed it myself, but it was done by someone else who I cannot consult for this (also to long ago). I think it was originally installed with cacti 0.8.6c because those zips also existed on the server. Checking the database with 'describe poller_item;' shows exactly the items that where created by cacti.sql of version 0.8.6c. Above items are missing and it looks like the upgrade that was done from 0.8.6c to 0.8.6j wasn't completely successful. I upgraded to 0.8.6k, but that did not give any errors.

I also tried to enter the commands manually into mysql, but that does not work either. I use root user so I guess I have complete access to the database.

I have been looking for answers for this on the net and in this forum, but unfortunately I could not find any.

If someone could help me on my way I would be grateful.

Thank you!

[BSOD2600]

I also tried to enter the commands manually into mysql, but that does not work either.

What were the errors?

Is there a lot of data/config you need to keep? If not, might be easier to install 0.8.7d clean.

[alm]

The errors of manual inputting the failed commands:

Error for the first command:
ERROR 1072 (42000): Key column 'rrd_next_step' doesn't exist in table

Error second command:
ERROR 1054 (42S22): Unknown column 'rrd_next_step' in 'poller_item'

Error third command:
ERROR 1054 (42S22): Unknown column 'pid' in 'poller_time'

Error fourth command:
ERROR 1054 (42S22): Unknown column 'rrd_step' in 'poller_item'

I was hoping to keep the data that I now have, maybe exporting it and importing again in new clean database perhaps? But I don't know if that is possible.

[alm]

Thinking about it, I've looked for a consistancy check that can be done on a cacti database, but I have not found it. Does something like that exist?

I'm going through http://www.mysqltutorial.org now to learn more about MySQL as I hope there is a way to fix my issue. Still hope someone can help me with this though.

[BSOD2600]

Thinking about it, I've looked for a consistancy check that can be done on a cacti database, but I have not found it. Does something like that exist?

mysqlcheck --auto-repair --databases cacti

For your corner case, it might be worthwhile to manually the database altering commands (cacti\install\0_8_6h_to_0_8_6i.php, etc) in sequence to re-force the various upgrades. Make sure you backup the DB first...

[alm]

mysqlcheck --auto-repair --databases cacti

Ah, but that's not for checking if the structure matches the right structure given the version of Cacti, is it? I think the database isn't damaged though.

For your corner case, it might be worthwhile to manually the database altering commands (cacti\install\0_8_6h_to_0_8_6i.php, etc) in sequence to re-force the various upgrades. Make sure you backup the DB first...

Ok, but is it possible to run it in one go? Perhaps by setting the version in the database to a lower version? Don't know if it then runs automatically?

[BSOD2600]

Ah, but that's not for checking if the structure matches the right structure given the version of Cacti, is it? I think the database isn't damaged though.

I'm not aware of any cacti tool which does that. Event the current repair script just runs a mysql repair...

Ok, but is it possible to run it in one go? Perhaps by setting the version in the database to a lower version? Don't know if it then runs automatically?

Yes, you could change the version back down, which would then invoke the cacti upgrade tool (again). Not sure how well that would work, as over time, various columns/tables change between versions. Suppose it's worth trying though...

Really though, one needs to find why the original upgrade script(s) failed...

[alm]

It's working.

I've made a backup of the database and altered the version in the database and set it back to 0.8.6c (which was originally installed I think as it matched the database content of at least the poller_item table).

Then I accessed the cacti website, where it saw that the database was older than the current version and wanted to do an upgrade. I got no failure messages, so the database was ok now. Did some tests and all seemed ok, so then decided to do the upgrade to 0.8.7d. This gave a list of failures, but when I manually input these in mysql it showed that the items already existed, which I think is ok.

I think the problem was caused by installation errors, but how I can only guess as I did not do it myself. A problem might be that when doing an upgrade and when there are failure messages in the upgrade of the database, you can just continue as if nothing happened and end up with a wrongly setup database.

Thank you for your help!