Bug 534320 (RHQ-1128)

Summary: we are not purging config items when bulk deleting resource things
Product: [Other] RHQ Project Reporter: John Mazzitelli <mazz>
Component: Core ServerAssignee: RHQ Project Maintainer <rhq-maint>
Status: NEW --- QA Contact:
Severity: medium Docs Contact:
Priority: high    
Version: 1.1CC: cwelton, hbrock, jshaughn, mazz, rbuck
Target Milestone: ---Keywords: FutureFeature, SubBug
Target Release: ---   
Hardware: All   
OS: All   
URL: http://jira.rhq-project.org/browse/RHQ-1128
See Also: https://bugzilla.redhat.com/show_bug.cgi?id=1323332
Whiteboard:
Fixed In Version: Doc Type: Enhancement
Doc Text:
Story Points: ---
Clone Of: Environment:
Last Closed: Type: ---
Regression: --- Mount Type: ---
Documentation: --- CRM:
Verified Versions: Category: ---
oVirt Team: --- RHEL 7.3 requirements from Atomic Host:
Bug Depends On:    
Bug Blocks: 620933    

Description John Mazzitelli 2008-11-17 16:37:00 EST
ResourceManagerBean.doBulkDelete is not purging all related Configuration items from the things it bulk deletes.  This leaves alot of orphaned rows in rhq_config and related tables. We need to clean that stuff out - rhq_config needs to be clean/optimized because it gets large as it is under normal usage.
Comment 1 John Mazzitelli 2008-11-17 16:43:45 EST
These bulk deletes need to have their configurations bulk deleted first:

        q = entityManager.createNamedQuery(PackageInstallationStep.QUERY_DELETE_BY_RESOURCES);
        q = entityManager.createNamedQuery(InstalledPackageHistory.QUERY_DELETE_BY_RESOURCES);
        q = entityManager.createNamedQuery(ResourceOperationHistory.QUERY_DELETE_BY_RESOURCES);
        q = entityManager.createNamedQuery(CreateResourceHistory.QUERY_DELETE_BY_RESOURCES);
        q = entityManager.createNamedQuery(ResourceConfigurationUpdate.QUERY_DELETE_BY_RESOURCES);
        q = entityManager.createNamedQuery(PluginConfigurationUpdate.QUERY_DELETE_BY_RESOURCES);
Comment 2 John Mazzitelli 2008-11-20 11:51:59 EST
There is a difficulty here.

I thought all I needed to do was:

// bulk delete: Config update and its config (added the first 3 lines here)
q = entityManager.createNamedQuery(ResourceConfigurationUpdate.QUERY_DELETE_CONFIG_BY_RESOURCES);
q.setParameter("resources", resources);
q.executeUpdate();
q = entityManager.createNamedQuery(ResourceConfigurationUpdate.QUERY_DELETE_BY_RESOURCES);
q.setParameter("resources", resources);
q.executeUpdate();

Where:

@NamedQuery(name = ResourceConfigurationUpdate.QUERY_DELETE_CONFIG_BY_RESOURCES, query = "DELETE FROM Configuration doomed WHERE doomed.id IN (SELECT rcu.configuration FROM ResourceConfigurationUpdate rcu WHERE rcu.resource IN (:resources)))"),

But it is a chicken-and-egg problem.

You can't delete the config because of the constraint violation it would cause (resource has foreign keys to config)
You can't delete the resource first, because once you do, that QUERY_DELETE_CONFIG query's subquery results in 0 rows

SELECT rcu.configuration FROM ResourceConfigurationUpdate rcu WHERE rcu.resource IN (:resources)))

results in 0 rows, hence the IN() clause is empty and nothing is deleted from config
Comment 3 Jay Shaughnessy 2008-11-20 12:19:24 EST
Right, the issue here is that the FK relationships to config are such that you can't remove the config prior to removing the object having that config.  The reason we're in good shape for a resource's resource and plugin config is that we still have hibernate cascade delete in place and we remove the resource via the entity manager, not bulk delete.  To do this at the code level would require one of the follwoing two strategies, neither being very pleasant:

1) bulk delete as we do today and then perform our own search for orphaned config.
This would be pretty inefficient as we'd have to search the 6 different relationships, none of which are indexed, I think.

2) prior to the bulk delete gather the config ids being referenced by the 6 FK's in play, and then delete config by id.
This is not fun as we don't want to have to fetch info just to perform deletes.

There may be other solutions but the best approach here may be to perform db-level cascade delete on the FK.  So, when the bulk delete takes out the referencing rows the db uses the FK relationship to kill the config. All under the covers.  This would be my recommendation at the moment.

Note that putting cascade delete on the config FKs for rhq_config_update and rhq_operation_history handles most of the issues.

Comment 4 Jay Shaughnessy 2008-11-20 12:50:49 EST
Never mind, the cascade delete approach won't work for the same reason that makes this tricky, CD will not allow us to delete the parent in the FK relationship.  Just because, say, a referring row in rhq_config_update  is deleted, that won't trigger a cascade delete.  back to the drawing board.
Comment 5 Jay Shaughnessy 2008-11-21 16:16:17 EST
Here's an idea that's related.  Still add the cascade deletes on the FKs.  So, for example, set it up such that rhq_config_update is deleted by the db if it's referenced rhq_configuration is deleted.  Then, change the bulk delete query for deleting rhq_config_update such that it actually deletes the related *config* as opposed to the config update.  The killing of the config would then cascade and kill the config_update.

So, for example, this query:

  PluginConfigurationUpdate.QUERY_DELETE_BY_RESOURCES, query = "DELETE FROM PluginConfigurationUpdate pcu WHERE pcu.resource IN (:resources))"

Would change to this query:

  PluginConfigurationUpdate.QUERY_DELETE_BY_RESOURCES, query = "DELETE FROM Configuration c WHERE c IN ( SELECT pcu.configuration WHERE pcu.resource IN (:resources))"

Comment 6 Jay Shaughnessy 2008-11-21 18:16:06 EST
Even the previous suggestion isn't quite sufficient because of the complex graph in place.  For example, it's possible that Config C1 can be referenced by both Resource R1 and ConfigUpdate U1. And, at the same time R1 can be referenced by U1.

        R1 --> C1 
        U1 --> C1
        U1 --> R1

So, in an attempt to delete U1 by cascade delete of C1 you violate the constraint between R1 and C1.  If you leave U1 you violate the the constraint between U1 and R1 when you try to remove R1.  As far as I can tell, to get this to work you must perform two queries:

part 1 - remove all the configuration and, by cascade, associated plugin config updates with the exception of configurations in the scenario above. In other words, remove the configurations that would have been orphaned by the query in part 2.

   DELETE FROM Configuration c WHERE c IN ( SELECT pcu.configuration WHERE pcu.resource IN (:resources) AND NOT pcu.configuration = pcu.resource.pluginConfiguration )

part 2 - Now that we've protected against orphan configs, bulk delete remaining config updates (same as what we had originally)
   
   DELETE FROM PluginConfigurationUpdate pcu WHERE pcu.resource IN (:resources))

This needs to be done for pluginconfigupdate and resourceconfigupdate.  This is working locally.  It adds 2 more queries to bulk delete.

Note, just for fun, it seems that this still doesn't clean all the confis because, at least in the test code, config update is generating orphaned configs *during the update process*.  I have to see whether this happens outside of the test code as well.  In that case we've got a bug generating orphaned configs.
Comment 7 Jay Shaughnessy 2008-12-01 14:04:13 EST
r2150 adds the db-support (schema 2.31) for the approach in the above comment for rhq_config_update.
r2151 adds the queries and related logic.

Assuming these changes work well we can revisit the above approach for the remaining orphaned configs.
Comment 8 Red Hat Bugzilla 2009-11-10 15:24:35 EST
This bug was previously known as http://jira.rhq-project.org/browse/RHQ-1128
This bug is related to RHQ-1125
Comment 9 wes hayutin 2010-02-16 11:53:28 EST
Temporarily adding the keyword "SubBug" so we can be sure we have accounted for all the bugs.

keyword:
new = Tracking + FutureFeature + SubBug
Comment 10 wes hayutin 2010-02-16 11:58:49 EST
making sure we're not missing any bugs in rhq_triage
Comment 11 Corey Welton 2010-08-30 13:21:39 EDT
jay -- what's left to do here?
Comment 12 Jay Shaughnessy 2010-08-30 13:39:02 EDT
Reading this through perhaps we need to revisit and see if the logic was applied anywhere other than rhq_config_update.

Given the list in the description we would need to check the following as well:

PackageInstallationStep
InstalledPackageHistory
ResourceOperationHistory
CreateResourceHistory
PluginConfigurationUpdate
Comment 13 Jay Shaughnessy 2014-05-02 16:20:03 EDT
As far as I know we still leave various config items. This should be revisited if and when we re-implement config storage design.