Feature request for FOG 1.6.x - Scheduled database maintenance

[george1421]

Develop cron jobs to run against the FOG database to clean up old entries such as old multicast tasks, broken host records, etc. Its akin to this post https://wiki.fogproject.org/wiki/index.php/Troubleshoot_MySQL#Database_Maintenance_Commands

[Wayne Workman]

I could probably get this working. Just as simple as adding a cron job and script via the installer, and using the .fogsettings file to connect to the db and execute the commands.

@george1421 Considering the maintenance commands wipe out multicast sessions and stuck snapins, this is probably not something to run every hour… The most frequent I might recommend is daily. Probably sometime on Sunday night might be best.

[george1421]

@Wayne-Workman said in Feature request for FOG 1.6.x - Scheduled database maintenance:

The most frequent I might recommend is daily. Probably sometime on Sunday night might be best.

Good idea. I don’t know if the right answer is daily, weekly, or monthly. There maybe different commands that need to run at different intervals. Right now I just want to get the concept documented so we can work out the details a bit later.

[Sebastian Roth]

Some people might want to keep this information for month or even years?! Probably good to do a cleanup of false stuff but keep all the history for people to cleanup by themselves - maybe just provide a button in FOG settings for small and major cleanup instead of a cron task?!

[george1421]

@Sebastian-Roth Should this be a FOG Settings question? 0 would be to keep indefinitely and a positive integer would be days of history to keep? The FOG Settings question maybe default to cron standards, of daily, weekly, monthly, yearly?? Or allow a date value of cleanup on the 1st of every month? Just thinking in cron time space for easily setup.

Is there any value in keeping multicast task history for more than 1 day?

[Sebastian Roth]

@george1421 said:

Should this be a FOG Settings question?

Do you mean an installer question or a FOG settings value in the web UI? I guess you mean the later as an installer question seems too static where people need to re-run the installer to change this.

Using cron style times is a good idea.

Is there any value in keeping multicast task history for more than 1 day?

Some people might need to report about the deployments (even multicasts) to the company management - possibly a monthly or even annual report. I have no idea how much people use the history but I fear that people using it might update without noticing that we added this and loose their invaluable reporting stats. I am not saying that we shouldn’t add this feature but we shall provide an obvious list of changes which some people might not expect. Anyone a good idea on where we keep note of those changes?

[Wayne Workman]

@Sebastian-Roth said in Feature request for FOG 1.6.x - Scheduled database maintenance:

maybe just provide a button in FOG settings for small and major cleanup instead of a cron task?!

I disagree about this being a button. There is stuff that gets into a funky state in the database because there are no relationships enforced, thus allowing code to get written without needed checks and/or bad logic. Absent of fixing those issues, the database will get messed up and a cron job really is needed to keep things working.

The items can be dumped out somewhere to be viewed sure, but this funk in the database literally breaks functionality, preventing FOG from doing what it’s supposed to do.

I should add I’m not talking about history. I’m referring to the issues that literally break fog. This stuff:

# The following chunk of commands will clean out most problems and are safe:
DELETE FROM `hosts` WHERE `hostID` = '0';
DELETE FROM `hostMAC` WHERE hmID = '0' OR `hmHostID` = '0';
DELETE FROM `groupMembers` WHERE `gmID` = '0' OR `gmHostID` = '0' OR `gmGroupID` = '0';
DELETE FROM `snapinGroupAssoc` WHERE `sgaID` = '0' OR `sgaSnapinID` = '0' OR `sgaStorageGroupID` = '0';
DELETE from `snapinAssoc` WHERE `saID` = '0' OR `saHostID` = '0' OR `saSnapinID` = '0';
DELETE FROM `hosts` WHERE `hostID` NOT IN (SELECT `hmHostID` FROM `hostMAC` WHERE `hmPrimary` = '1');
DELETE FROM `hosts` WHERE `hostID` NOT IN (SELECT `hmHostID` FROM `hostMAC`);
DELETE FROM `hostMAC` WHERE `hmhostID` NOT IN (SELECT `hostID` FROM `hosts`);
DELETE FROM `snapinAssoc` WHERE `saHostID` NOT IN (SELECT `hostID` FROM `hosts`);
DELETE FROM `groupMembers` WHERE `gmHostID` NOT IN (SELECT `hostID` FROM `hosts`);
DELETE FROM `tasks` WHERE `taskStateID` IN ("1","2","3");
DELETE FROM `snapinTasks` WHERE `stState` in ("1","2","3");
TRUNCATE TABLE multicastSessions; 
TRUNCATE TABLE multicastSessionsAssoc; 
DELETE FROM tasks WHERE taskTypeId=8;

https://wiki.fogproject.org/wiki/index.php?title=Troubleshoot_MySQL#Database_Maintenance_Commands

A button for clearing just the history table is a good idea though.

[Sebastian Roth]

@Wayne-Workman said in Feature request for FOG 1.6.x - Scheduled database maintenance:

I should add I’m not talking about history. I’m referring to the issues that literally break fog. This stuff:

Definitely good you phrase this more explicitely!! We should discuss those two things separately (maybe not in different topics though).

While we tell people to sometimes try a cleanup of the DB I am not sure how much issues in the code are still causing this or if this is related to people coming older versions and have not done a cleanup in a long time. Don’t get me wrong, I am not saying the code is perfectly fine, I just don’t know as we don’t have enough evidence that this happens all the time. We would expect way more people to ask about this in the forums.

On the other hand I do agree that running those cleanup (not the history ones!) once a week or even daily wouldn’t hurt:

DELETE FROM `hosts` WHERE `hostID` = '0';
DELETE FROM `hostMAC` WHERE hmID = '0' OR `hmHostID` = '0';
DELETE FROM `groupMembers` WHERE `gmID` = '0' OR `gmHostID` = '0' OR `gmGroupID` = '0';
DELETE FROM `snapinGroupAssoc` WHERE `sgaID` = '0' OR `sgaSnapinID` = '0' OR `sgaStorageGroupID` = '0';
DELETE FROM `snapinAssoc` WHERE `saID` = '0' OR `saHostID` = '0' OR `saSnapinID` = '0';
DELETE FROM `hosts` WHERE `hostID` NOT IN (SELECT `hmHostID` FROM `hostMAC` WHERE `hmPrimary` = '1');
DELETE FROM `hosts` WHERE `hostID` NOT IN (SELECT `hmHostID` FROM `hostMAC`);
DELETE FROM `hostMAC` WHERE `hmhostID` NOT IN (SELECT `hostID` FROM `hosts`);
DELETE FROM `snapinAssoc` WHERE `saHostID` NOT IN (SELECT `hostID` FROM `hosts`);
DELETE FROM `groupMembers` WHERE `gmHostID` NOT IN (SELECT `hostID` FROM `hosts`);

You don’t want to run those on a regular basis I find because it can kill running tasks/sessions if those were setup to run over the weekend for example (state ID see here)

DELETE FROM `tasks` WHERE `taskStateID` IN ("1","2","3");
DELETE FROM `snapinTasks` WHERE `stState` in ("1","2","3");
TRUNCATE TABLE multicastSessions; 
TRUNCATE TABLE multicastSessionsAssoc; 
DELETE FROM tasks WHERE taskTypeId=8;

And history is another story altogether…

TRUNCATE TABLE history;
TRUNCATE TABLE userTracking;