version cleanup fails on old databases with many entries

[tehlers320]

What Grafana version are you using?

5.0.1

What datasource are you using?

Aurora5.6 on r4.larges

What OS are you running grafana on?

Amazon Linux

What did you do?

Upgraded

What was the expected result?

Version trim works

What happened instead?

t=2018-03-16T15:44:34+0000 lvl=eror msg="Failed to delete expired dashboard versions" logger=cleanup error="Error 1390: Prepared statement contains too many placeholders"

A couple of notes:
As a user of grafana for a long time we had a pre-existing system which was not turned off when the "versioning" was enabled. This system continually updated dashboards and our versions kept increasing. The version table is 30GB so this may be an extreme case but other users may also see this bug if they have implemented the same thing.

[DanCech]

It sounds like the version cleanup job needs a limit on how many old versions it'll try to delete in one hit

[bergquist]

Yeap.

I suggest we change how dashboard_versions are deleted

I think the select statement could be made into a Group By only using the dashboard_version table.
https://github.com/grafana/grafana/blob/master/pkg/services/sqlstore/dashboard_version.go#L79

When it comes to deleting I think we should delete ranges instead of per id.
https://github.com/grafana/grafana/blob/master/pkg/services/sqlstore/dashboard_version.go#L93

1. Select the last X versions for dashboards with too many versions
2. Save the first dashboard_id from that result
3. Delete all dashboard_versions below that ID with the same dashboard_id

Deleting ranges of rows is much more performant than deleting by id