Bug 29910 - Purge old Hea data periodically
Summary: Purge old Hea data periodically
Status: CLOSED FIXED
Alias: None
Product: Project Infrastructure
Classification: Unclassified
Component: Hea (show other bugs)
Version: unspecified
Hardware: All All
: P5 - low enhancement
Assignee: Jonathan Druart
QA Contact: Jonathan Druart
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2022-01-20 01:03 UTC by Victor Grousset/tuxayo
Modified: 2022-12-12 21:22 UTC (History)
5 users (show)

See Also:
Change sponsored?: ---
Patch complexity: ---
Documentation contact:
Documentation submission:
Text to go in the release notes:
Version(s) released in:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Victor Grousset/tuxayo 2022-01-20 01:03:11 UTC
AFAIK it's not purged so we might have misleading information from old libraries than no longer use Koha. So it lessens the trust and the insight Hea gives us.

There could be a cron than runs some SQL that deletes submissions that haven't been updated since more than X months.
Comment 1 Fridolin Somers 2022-01-21 20:52:03 UTC
Sure.

This is why we see versions like 3.14, 3.22 ^^
Comment 2 Victor Grousset/tuxayo 2022-01-23 19:16:08 UTC
That wouldn't be a surprise to have such old production instances never updated.

However 3.18.x is the oldest branch with Hea available so seeing 3.14 and 3.09 (?!) in the data makes no sense XD

I see that Hea is hosted by BibLibre, good thing you stumbled on this ticket Fridolin ^^
So, how feasible would it be to have a cron job that runs an SQL delete query?
Comment 3 Jonathan Druart 2022-01-26 11:14:42 UTC
> select year(modification_time) as yyyy, count(*) from installation group by yyyy;
+------+----------+
| yyyy | count(*) |
+------+----------+
| 2014 |        8 |
| 2019 |      114 |
| 2020 |     2726 |
| 2021 |     5893 |
| 2022 |     4987 |
+------+----------+

What do we remove? What does "periodically" mean?
Comment 4 Katrin Fischer 2022-01-26 11:57:17 UTC
I think it's supposed to report every month... so maybe we could keep a timeframe of like 6 months back?
Comment 5 Katrin Fischer 2022-01-26 11:58:38 UTC
(In reply to Katrin Fischer from comment #4)
> I think it's supposed to report every month... so maybe we could keep a
> timeframe of like 6 months back?

Assuming the date updates if a new report comes in - or is it only updated if data actually changes (like a timestamp on the database)? We'd want the date last reported I think.
Comment 6 Marcel de Rooy 2022-01-26 12:06:38 UTC
(In reply to Jonathan Druart from comment #3)
> > select year(modification_time) as yyyy, count(*) from installation group by yyyy;
> +------+----------+
> | yyyy | count(*) |
> +------+----------+
> | 2014 |        8 |
> | 2019 |      114 |
> | 2020 |     2726 |
> | 2021 |     5893 |
> | 2022 |     4987 |
> +------+----------+
> 
> What do we remove? What does "periodically" mean?

Nothing between 2014 and 2019 is a little bit odd?
I agree with Katrin: 6 months or perhaps 12 months to be safe should be the cutoff.
Comment 7 Jonathan Druart 2022-01-26 12:53:31 UTC
(In reply to Marcel de Rooy from comment #6)
> (In reply to Jonathan Druart from comment #3)
> > > select year(modification_time) as yyyy, count(*) from installation group by yyyy;
> > +------+----------+
> > | yyyy | count(*) |
> > +------+----------+
> > | 2014 |        8 |
> > | 2019 |      114 |
> > | 2020 |     2726 |
> > | 2021 |     5893 |
> > | 2022 |     4987 |
> > +------+----------+
> > 
> > What do we remove? What does "periodically" mean?
> 
> Nothing between 2014 and 2019 is a little bit odd?

I've purged them already some years ago.
Comment 8 Fridolin Somers 2022-01-26 22:07:12 UTC
In my opinion keeping 1 year is a good compromise.
Comment 9 David Cook 2022-01-27 02:37:08 UTC
Another thought... rather than purging the data... make it more obvious on the Hea webapp what numbers belong to what year?

That could be really interesting as you could see changes in data over time. 

Then it might be worthwhile keeping maybe the last 5 years of data, if long-term storage of data is a problem.

Of course, numbers can also be misleading. A drop in numbers in more recent years might make it seem like fewer people using Koha when really it's just fewer people reporting their usage...

But anyway, that's my 2 cents.
Comment 10 Jonathan Druart 2022-01-27 08:35:31 UTC
(In reply to David Cook from comment #9)
> Another thought... rather than purging the data... make it more obvious on
> the Hea webapp what numbers belong to what year?

How? Who? When?

I am not willing to work on Hea improvements.
Comment 11 Katrin Fischer 2022-01-27 09:02:13 UTC
Besides numbers we created Hea to see what people are using etc. If a Koha installation stops sending, we should probably assume it's no longer active or wanting to share data. I think from this perspective we should definitely purge data.
Comment 12 Marcel de Rooy 2022-01-27 09:50:14 UTC
(In reply to Katrin Fischer from comment #11)
> Besides numbers we created Hea to see what people are using etc. If a Koha
> installation stops sending, we should probably assume it's no longer active
> or wanting to share data. I think from this perspective we should definitely
> purge data.

Agree completely. Lets not add new features here but have recent data.
Comment 13 Katrin Fischer 2022-01-27 13:33:39 UTC
I am also not interested in new features, and this is for a separate bug, but I keep thinking if it's ok to give them no option to delete their data, especially library name/address information. Or maybe it's possible and I am just not aware? So a year is already quite long, but I am ok with it, even tho I'd prefer something shorter.
Comment 14 David Cook 2022-01-27 22:56:21 UTC
(In reply to Jonathan Druart from comment #10)
> (In reply to David Cook from comment #9)
> > Another thought... rather than purging the data... make it more obvious on
> > the Hea webapp what numbers belong to what year?
> 
> How? Who? When?
> 
> I am not willing to work on Hea improvements.

Fair enough. I'm not willing to work on it either. I always think of it as a BibLibre project, so I figure they might be willing to make some improvements. 

Actually, I see a little problem with the website. Maybe I'll try to contribute a little change... hehe.
Comment 15 Victor Grousset/tuxayo 2022-01-29 20:58:39 UTC
> thinking if it's ok to give them no option to delete their data, especially library name/address information.

It depends on how Hea works when receiving new data from and existing library. If it overwrites the existing record, then removing library name&address and resubmitting the data Hea allows to get unlisted. (and remove lib name&address from the DB)
Comment 16 Jonathan Druart 2022-03-18 09:10:40 UTC
Added the following cronjob:

0 1 1 * * /usr/bin/mysqldump heastats | gzip > /home/hea/dumps/$(date +%Y-%m-%d)-heastats.sql.gz && /usr/bin/mysql -e "DELETE FROM installation WHERE modification_time < DATE_SUB(NOW(), INTERVAL 6 MONTH);"

It will dump the DB and remove all installations without changes in the last 6 months.

First (manual) run removed 6123 installations.
8744 still in DB.
Comment 17 Marcel de Rooy 2022-03-18 09:32:15 UTC
Great!
Comment 18 Victor Grousset/tuxayo 2022-03-18 23:43:32 UTC
Amazing thanks!

Now oldest reported version is 16.11 instead of 3.09!
Went from 54 million patrons to 40 million.