DB Exec Failed

[macdaddy]

I have an install of Cacti 0.8.7g that's 2 weeks old on a fresh CentOS box. I added the latest plugin architecture as well. I added 21 devices and about 5500 graphs. I ran out of drive space last Friday night and didn't discover it until Monday. I didn't realize that this was the problem initially. The last log entry in the Cacti log was complaining about MySQL. I restarted MySQL to no avail. That's when I looked for other causes and found the drive space issue. I freed up space by dumping a syslog log file that grew faster than I'd expected. I restarted MySQL again but Cacti still isn't happy. For grins I restarted the host as well just in case some other process failed when it couldn't write to disk. Nothing. The Cacti log file is now filling with errors like these:

09/14/2010 09:40:42 AM - CMDPHP: Poller[0] ERROR: A DB Exec Failed!, Error:'1017', SQL:"insert into poller_output (local_data_id, rrd_name, time, output) values (344, 'discards_out', '2010-09-14 09:40:35', '0')'

I Googled the error and found this discussion from 08:

http://forums.cacti.net/about29416.html

Per the advice there I killed the poller and ran rebuild_poller_cache.php. It took a couple minutes to run. Cacti still isn't happy though.

I'm at a loss here. I'm not a DBA but I'm guessing I corrupted something when either I ran out of disk space or restarted the daemon (or both at the same time). I'd really like to not scrap the data and start over. Suggestions? Thanks

[macdaddy]

No ideas on this one? I'd really like to not have to recreate all 5500 graphs if it can be helped. That was painful enough the first time around. Thanks...

[BSOD2600]

Mysql ERROR 1017 means it can't find a file (thanks google!)

the cacti technical support page show any issues?
mysql log show any issues?

[macdaddy]

Thanks for the reply. The only thing in red is a warning for the PHP memory_limit. It was 168MB from 128MB. Are any of the non-red items useful for troubleshooting? The last entries in the MySQL log are from when I was out of drive space.

100914 13:15:02 [ERROR] /usr/libexec/mysqld: Disk is full writing '/var/lib/mysql/cacti/poller_item.MYI' (Errcode: 28). Waiting for someone to free space... Retry in 60 secs

Baffling... I'm logging nans. Prior to the kill and reboot it was perfect.

[gandalf]

That reminds me of binary logging. That may fill up your space. It's a MySql option in /etc/my.ini (or the like).
R.

[macdaddy]

Sorry for the delay. I tabled Cacti to work on other urgent things. I'm ready to focus on Cacti again.

Here's where I'm at. My Cacti install is all but completely shot at this point. I don't have a single graph functional after that drive space issue and DB corruption. I can't find a way to fix it either. If someone has any suggestions on how to wipe the RR data for each graph, make Cacti happy and let is start over again then I'm all for it. If not I think I want to wipe it and start over. I think that would be easier.

If I wipe it and start over I'm wondering if there's any way to save some of the existing data and not have to recreate it. Is it possible to save my device info and reimport that into a clean install? Perhaps reimport device info and basic graph info (like this SNMP interface on this device, graph these things)? If not I'll just wipe it all and start over, but I thought I'd ask first. I'm not a DBA.

Thanks, guys

[gandalf]

To wipe, please delete all *.rrd files from ./rra directory. Cacti will recreate them freshly on next poller's run
R.

[macdaddy]

So I went to add a new host today and for some reason I couldn't login. It accepted the userid/passwd but kept sending me back to the login page (bad credentials were indicated as such when I tried that for grins). Ultimately I figured out what was wrong. I was out of drive space again. We're undergoing a quarterly pen test and it filled my logs. So I fixed the drive space issue, saw errors scrolling by in cacti.log so I gracefully shutdown MySQL. Then killed all the existing php processes. Then brought MySQL back online and let cron fire off Cacti again. And guess what, the DB is toast AGAIN!!

Code: Select all

12/03/2010 09:41:53 AM - CMDPHP: Poller[0] ERROR: SQL Assoc Failed!, Error:'1017', SQL:"select  poller_output.output,  poller_output.time,  poller_output.local_data_id,  poller_item.rrd_path,  poller_item.rrd_name,  poller_item.rrd_num  from (poller_output,poller_item)  where (poller_output.local_data_id=poller_item.local_data_id and poller_output.rrd_name=poller_item.rrd_name)  LIMIT 10000"

So last week I finished recreating all 6100+ graphs. I would really like to not do that yet again. Is there any way to repair the DB? All my hosts and config are there. I have no idea what it's actually complaining about. Given how much time I've reinvested in trying to recover from the last DB failure, I'd love to try and fix this problem rather than recreate everything again.

Does anyone know why MySQL freaks out so badly when it runs out of disk space? I can see why it couldn't write changes to disk when there's no more room on disk, but does it have to totally trash everything just because it can? This install of Cacti is in a VM that's my temporary location for the NOC server. I'll be moving it to hard iron just as soon as I get time (events like this do not help free up my time). I have significantly more drive space there to work with than this temp VM.

Suggestions? Thanks
Mac

[macdaddy]

If I can at least save my hosts, templates, and the basic graph config and then let Cacti recreate the graph data, I'd be fat, dumb and happy. I'm less concerned about a week's worth of graphing than I am about all those hours of creating devices and graphs.

[gandalf]

First of all, see http://docs.cacti.net/manual:087:4_help ... _logrotate to backup your db on a regular basis. Then, try a repair of your tables; there's a related script in the cli directory.
R.