AnsweredAssumed Answered

Cleanup policy_version table

Question asked by StefanKlotz on Feb 25, 2019
Latest reply on Feb 26, 2019 by Stephen_Hughes

I'd like to cleanup our policy_version table as it contains more than 6000 rows now (with more than 1GB in size), but we only have around 500 active policies. So even if I keep the last three versions, this is still only 1500 rows.

My first idea was to delete based on the timestamp and a criteria like "older than 3 weeks" or so. But for policies, which are not touched since months, this will delete ALL versions.

Second idea was to play with the "version"-column from the different tables and make some substraction to keep at least the last 3 versions or so. But I don't know how these versions are calculated in the different tables, there is no clear structure. For example I see the following Output, when using the followin MySQL-command (I hope this is generally correct):

SELECT published_service.name,published_service.version AS published_service_version, policy_version.version AS policy_version_version, policy.version AS policy_version
FROM published_service
LEFT JOIN policy_version ON published_service.policy_goid = policy_version.policy_goid
LEFT JOIN policy ON published_service.policy_goid = policy.goid
WHERE published_service.disabled = 0
ORDER BY published_service.name ASC;

+------------+------------------------------------+-------------------------------+--------------------+
| name       | published_service_version | policy_version_version | policy_version |
+------------+------------------------------------+-------------------------------+--------------------+
| policy_1   |                        11 |                     13 |             16 |
| policy_1   |                        11 |                     13 |             16 |
| policy_1   |                        11 |                     11 |             16 |
| policy_1   |                        11 |                     10 |             16 |
| policy_1   |                        11 |                      9 |             16 |
| policy_1   |                        11 |                      7 |             16 |
| policy_1   |                        11 |                      7 |             16 |
| policy_1   |                        11 |                      6 |             16 |
| policy_1   |                        11 |                      4 |             16 |
| policy_1   |                        11 |                      3 |             16 |
| policy_1   |                        11 |                      2 |             16 |
| policy_1   |                        11 |                      1 |             16 |
| policy_1   |                        11 |                      0 |             16 |
| policy_2   |                         6 |                      4 |              5 |
| policy_2   |                         6 |                      3 |              5 |
| policy_2   |                         6 |                      2 |              5 |
| policy_2   |                         6 |                      1 |              5 |
| policy_2   |                         6 |                      0 |              5 |
| policy_3   |                         8 |                      9 |             10 |
| policy_3   |                         8 |                      8 |             10 |
| policy_3   |                         8 |                      8 |             10 |
| policy_3   |                         8 |                      7 |             10 |
| policy_3   |                         8 |                      6 |             10 |
| policy_3   |                         8 |                      4 |             10 |
| policy_3   |                         8 |                      3 |             10 |
| policy_3   |                         8 |                      2 |             10 |
| policy_3   |                         8 |                      1 |             10 |
| policy_3   |                         8 |                      0 |             10 |
| policy_4   |                        25 |                     94 |            100 |
| policy_4   |                        25 |                     93 |            100 |
| policy_4   |                        25 |                     92 |            100 |
| policy_4   |                        25 |                     91 |            100 |
| policy_4   |                        25 |                     90 |            100 |
| policy_4   |                        25 |                     89 |            100 |
| policy_4   |                        25 |                     88 |            100 |

 

The "policy"-version is always the highest number and reflecting the latest number shown in the "Revision History"-window from the Policy Manager. I was expecting that the highest number of either the "published_service"-version or the "policy_version"-version is equal or by one smaller than the "policy"-version value. But that's not constantly the case, therefor can't be used for any calculation.

Therefor the question is, if anyone of you has an idea how to delete old versions with just a single MySQL. Or can this only be achieved via a scripted command (count for each policy and loop through it)?

Or is there any other possibility (or built-in function) to achieve this?

Thank you!

 

Ciao Stefan

Outcomes