Mysql Indexes missing

Grafana Installation
1

Dear Grafana Team,

Is there any script that can re-create the indexes of the Grafana Mysql Backend?
Seem I’ve lost most of the indexes in the mysql and it’s create a lots of trouble in the making of new user, dashboards, etc.
I didn’t realized it until created new user and in the database the id was null and user can’t login. Since I checked the indexes, only several indexes there.

Please help and send me the link of the script if any.

Regards,
Fadjar Tandabawana

2

Hi @fadjar340,

I’m asking some of the internal engineers that work on Grafana OS to see what you can do about fixing this. It sounds like there are several ways. More soon!

3

Hi @fadjar34,

So I have good news and bad news. The good news is that several engineers on the team have encountered situations like this before, and there is a way to fix it. The bad news is that the fix is very manual; we don’t currently have any script that will fix this for you, although perhaps now we should develop one for the community.

Here are some notes from the team to help you. Please let me know if this info helps you get unblocked. Together we’ll get this fixed.

description of problem

  • the tl;dr from one senior engineer: “you can trick Grafana into recreating an index for a table by removing rows from the migration_log table”

  • first identify which indexes are missing, then you have to look in the migration_log table to see where it says it was created and delete that row.

  • The team has seen this when Grafana gets stopped during an upgrade, like the container gets killed exactly during an in-process upgrade. Or MySQL fell over during the upgrade. Also: had you been fiddling with MySQL directly before this happened?

  • for your scenario, it may be enough to just get the list of missing indexes, and then delete the corresponding rows from the migration_log table, and on startup grafana will make them

possible solution

  • Grafana executes database migrations when it starts up. It has a list of statements that it runs if they are not in the migration_log table.

  • One way to do this is to go into the migration_log and delete the rows that create the indexes. Then restart Grafana and it will run those statements again. The tricky part is that he says some and not all. If an index already exists then the migrations will not work.

  • You could run a query like this:

SELECT id,
       migration_id,
       sql,
       success,
       error,
       timestamp
  FROM migration_log
  where migration_id like '%index%';
  • See attached screenshot for what this query will return.

There might be a faster or more clever way to do this, but I hope that this method works for you. If possible, I’d back up the DB and test all this in a dev environment before you attempt any fixes.

Let me know if this helps!

4

image
image1319×373 123 KB

5

I just following this and some indexes created, but I have stoped when create IDX alert notification journal, whic is the table is not there, and my grafana just stop and can not start.

I saw in the migration_log, there are DROP TABLE IF EXISTS alert_notification_journal only

If there any script that can build the table alert_notification_journal, please let me know.

[Edit]
I have the script anyway and now up and running…