upgrade to grafana 9: failed to migrate permissions: failed to create permissions for role: Duplicate entry

[TomRitserveldt]

What happened: upgrading from grafana 8.5.3 to 9.1.6 grafana fails to start and we receive following error in the logs:

logger=migrator t=2022-09-28T11:54:23.792079902Z level=info msg="Starting DB migrations"
logger=migrator t=2022-09-28T11:54:23.807151581Z level=info msg="Executing migration" id="dashboard permissions"
logger=migrator t=2022-09-28T11:54:23.855710899Z level=error msg="Executing migration failed" id="dashboard permissions" error="failed to migrate permissions: failed to create permissions for role: Error 1062: Duplicate entry '320-dashboards:read-folders:id:271' for key 'permission.UQE_permission_role_id_action_scope'"
logger=migrator t=2022-09-28T11:54:23.856134685Z level=error msg="Exec failed" error="failed to migrate permissions: failed to create permissions for role: Error 1062: Duplicate entry '320-dashboards:read-folders:id:271' for key 'permission.UQE_permission_role_id_action_scope'" sql="code migration"

I checked the database but could'nt find anything like that entry in the 'permission' table. Any clues or actions we could take to work around this? I tried adding the force_migration=true option to the config, to no avail.

What you expected to happen: migration to work :)

How to reproduce it (as minimally and precisely as possible): Have an external database with data from a working grafana 8.5.3 with a a number of dashboards in it, start up a grafana 9.1.6 instance pointing to the database (old grafana instances are gone)

Environment:

• Grafana version: 9.1.6
• Data source type & version: mysql 8.0.28
• OS Grafana is installed on: Debian buster

[adamdougal]

I also encountered the same issue when updating from 8.5.5 to 9.1.6. It looks like only a couple of folders were affected.

[zxlxgd]

I also encountered the same issue when updating from 7.5 to 9.2

[Jguer]

Hey, can you share the output of the following SQL query:

SELECT
    id,
    uid,
    COUNT(*) AS "Count"
FROM dashboard
GROUP BY
    id
HAVING COUNT(*) > 1

Just to ensure there's nothing wrong with the dashboard row entries before trying out other diagnostics

[Jguer]

The output of the following SQL query can also help:

SELECT GROUP_CONCAT(id)
FROM dashboard_acl
GROUP BY dashboard_id, role HAVING COUNT(*) > 1

[Jguer]

Hey @zxlxgd @adamdougal and @TomRitserveldt . We might have found a cause for the failing migration and have a fix ready, but we want to make sure this is the cause for your upgrade issues.

Can you run the following query on your instance's DB and tell us the DB type and version?

SELECT GROUP_CONCAT(id)
FROM dashboard_acl
GROUP BY dashboard_id, role HAVING COUNT(*) > 1

[Jguer]

hey @zxlxgd @adamdougal @TomRitserveldt

We can't reproduce this issue and we don't want to change an active migration if we're not sure it fixes the issue. The output of this query in the DB would be really valuable for it:

SELECT GROUP_CONCAT(id)
FROM dashboard_acl
GROUP BY dashboard_id, role HAVING COUNT(*) > 1

By the end of the week we'll close this issue as 'not reproducible'. We can always reopen this issue if we have more data on how to reproduce it or the debug information above

[TomRitserveldt]

hey @Jguer , sorry for the late reply we've been quite busy. results of the query: BEFORE MIGRATION:

MySQL [grafanadb]> SELECT GROUP_CONCAT(id) FROM dashboard_acl GROUP BY dashboard_id, role HAVING COUNT(*) > 1;
Empty set (0.141 sec)

MySQL [grafanadb]> SELECT GROUP_CONCAT(id) FROM dashboard_acl GROUP BY dashboard_id, role;
+------------------+
| GROUP_CONCAT(id) |
+------------------+
| 2                |
| 1                |
| 9                |
| 10               |
| 12               |
| 11               |
| 13               |
+------------------+

AFTER MIGRATION TO 9.1.6:

MySQL [grafanadb]> SELECT GROUP_CONCAT(id) FROM dashboard_acl GROUP BY dashboard_id, role HAVING COUNT(*) > 1;
+------------------+
| GROUP_CONCAT(id) |
+------------------+
| 20,22            |
| 23,25            |
+------------------+


MySQL [grafanadb]> SELECT GROUP_CONCAT(id) FROM dashboard_acl GROUP BY dashboard_id, role;
+------------------+
| GROUP_CONCAT(id) |
+------------------+
| 2                |
| 1                |
| 9                |
| 10               |
| 12               |
| 11               |
| 13               |
| 21               |
| 20,22            |
| 24               |
| 23,25            |
+------------------+

[Jguer]

Hey, Grafana 9.3 should contain a fix for this issue and it should be possible to directly upgrade to this version if you only have this issue

EDIT: fixed 9.4 to 9.3