Grafana - Migration failed err: pq: relation \"UQE_user_login\" already exists"

[dR3b]

Issue description

Jul 03 10:43:15 HOST 7yc83cxwvgayycrk8wmq1ba5apywkap0-unit-script-grafana-start[5353]: t=2019-07-03T10:43:15+0200 lvl=info msg="Executing migration" logger=migrator id="create index UQE_user_email - v2"
Jul 03 10:43:15 HOST 7yc83cxwvgayycrk8wmq1ba5apywkap0-unit-script-grafana-start[5353]: t=2019-07-03T10:43:15+0200 lvl=info msg="Executing migration" logger=migrator id="copy data_source v1 to v2"
Jul 03 10:43:15 HOST 7yc83cxwvgayycrk8wmq1ba5apywkap0-unit-script-grafana-start[5353]: t=2019-07-03T10:43:15+0200 lvl=info msg="Executing migration" logger=migrator id="Drop old table user_v1"
Jul 03 10:43:15 HOST 7yc83cxwvgayycrk8wmq1ba5apywkap0-unit-script-grafana-start[5353]: t=2019-07-03T10:43:15+0200 lvl=info msg="Executing migration" logger=migrator id="Add column help_flags1 to user table"
Jul 03 10:43:15 HOST 7yc83cxwvgayycrk8wmq1ba5apywkap0-unit-script-grafana-start[5353]: t=2019-07-03T10:43:15+0200 lvl=info msg="Executing migration" logger=migrator id="Update user table charset"
Jul 03 10:43:15 HOST 7yc83cxwvgayycrk8wmq1ba5apywkap0-unit-script-grafana-start[5353]: t=2019-07-03T10:43:15+0200 lvl=eror msg="Executing migration failed" logger=migrator id="Update user table charset" error="pq: relation \"UQE_user_login\" already exists"                                                                                                                                                                                                                               
Jul 03 10:43:15 HOST 7yc83cxwvgayycrk8wmq1ba5apywkap0-unit-script-grafana-start[5353]: t=2019-07-03T10:43:15+0200 lvl=eror msg="Exec failed" logger=migrator error="pq: relation \"UQE_user_login\" already exists" sql="ALTER TABLE \"user\" ALTER \"login\" TYPE VARCHAR(190), ALTER \"email\" TYPE VARCHAR(190), ALTER \"name\" TYPE VARCHAR(255), ALTER \"password\" TYPE VARCHAR(255), ALTER \"salt\" TYPE VARCHAR(50), ALTER \"rands\" TYPE VARCHAR(50), ALTER \"company\" TYPE VARCHAR(255), ALTER \"theme\" TYPE VARCHAR(255);"...
Jul 03 10:43:15 HOST 7yc83cxwvgayycrk8wmq1ba5apywkap0-unit-script-grafana-start[5353]: t=2019-07-03T10:43:15+0200 lvl=eror msg="Server shutdown" logger=server reason="Service init failed: Migration failed err: pq: relation \"UQE_user_login\" already exists"                                                                                                                                                                                                                               
Jul 03 10:43:15 HOST systemd[1]: grafana.service: Main process exited, code=exited, status=1/FAILURE
Jul 03 10:43:15 HOST systemd[1]: grafana.service: Failed with result 'exit-code'.

Steps to reproduce

Install Grafana and PostgreSQL 11/10:

  services.postgresql = {
    enable = true;
    package = pkgs.postgresql_11;
    initialScript = pkgs.writeText "Initial-PostgreSQL-Database" ''
      CREATE DATABASE grafana;
      CREATE USER grafana WITH ENCRYPTED PASSWORD '<PASSWORD>';
      GRANT ALL PRIVILEGES ON DATABASE grafana TO grafana;
    '';
  };

  services.grafana = {

    enable = true;
    addr = "localhost";
    port = 4000;
    domain = "<DOMAIN>";
    protocol = "http";

    database.type = "postgres";
    database.host = "localhost:5432";
    database.name = "grafana";
    database.user = "grafana";
    database.passwordFile = "/etc/nixos/modules/services/grafana.pass";

    analytics.reporting.enable = false;
    security.adminUser = "sysadmin";
    security.adminPasswordFile = "/etc/nixos/modules/services/grafana.pass";
    
  };

The user “grafana” can log in to postgresql. This works and entries are also created:

psql (11.4)
Type "help" for help.

grafana=# \dt
            List of relations
 Schema |     Name      | Type  |  Owner  
--------+---------------+-------+---------
 public | migration_log | table | grafana
 public | user          | table | grafana
(2 rows)

Any ideas?
Thanks

Technical details

 - system: `"x86_64-linux"`
 - host os: `Linux 4.19.56, NixOS, 19.03.173037.8edf24a379e (Koi)`
 - multi-user?: `yes`
 - sandbox: `yes`
 - version: `nix-env (Nix) 2.2.2`
 - channels(<USER>): `""`
 - channels(root): `"nixos-19.03.173037.8edf24a379e"`
 - nixpkgs: `/nix/var/nix/profiles/per-user/root/channels/nixos`

[aanderse]

Appears to be a known bug upstream in Grafana/PostgreSQL with specific versions of PostgreSQL.

https://community.grafana.com/t/error-starting-grafana-with-a-new-postgresql-10-9-database/18670

[dR3b]

Ok bugs are always there but why did it make it into the stable channel? Are there no automatic tests? The combination of PostgreSQL and Grafana should not be so rare.

[aanderse]

The test doesn't seem to use a database at all. I'm not familiar with grafana so maybe you can answer some questions for me. From a quick search grafana seems to work on external data sources, so I'm not clear on why it is generating a database of its own. I assume grafana generates its own database to store results of data analysis from the external data source you provided to grafana? If so I would suggest both the module and test for grafana could benefit from a few minor updates to cover this scenario.

In the meantime it looks like your solution is to downgrade postgresql if possible. Would that work for now?

[dR3b]

Yes, that's correct. Grafana needs a database to store users and dashboards. By default it is configured to use sqlite3. External data sources can be e.g. Prometheus or InfluxDB.

It's no problem for me. In the meantime I have replaced PostgreSQL with MariaDB. The Nix module for MySQL is much better than PostgreSQL...

[dR3b]

I also noticed that the group field under "/var/lib/grafana" are not set correctly. Shouldn't "root:root" be used?

$ ll /var/lib
drwx------  9 gitea   nogroup 4096  5. Jul 12:04 gitea
drwx------  5 grafana nogroup 4096  5. Jul 09:07 grafana

This is not true for "gitea" either?

[aanderse]

Taking a quick look at the module code it doesn't automatically provision a database for the user and leaves that up to them. The test should be expanded to cover at least mysql and postgresql variants, as well as sqlite which is currently covered.

The ownership should be grafana:grafana instead of grafana:nobody as nobody/nogroup shouldn't be used.

[dR3b]

Should I create a new issue for it?

[aanderse]

I'll likely take a look at this, so you can probably just leave it here. I might ping you for some testing, if you don't mind.

[dR3b]

I'll be glad to help.

[aanderse]

@dR3b Well I hit a bit of a roadblock in automatically provisioning databases for grafana. According to https://community.hortonworks.com/articles/33401/how-to-set-up-grafana-to-use-mysql-database-rather.html with a mysql database the grafana user requires the with grant option clause. Currently NixOS can't provision such a user automatically, which is a known issue.

In the meantime it seems there is a workaround for your specific issue.

[aanderse]

@WilliButz As the maintainer of the grafana test maybe you would be interested in expanding the test to provision mysql and postgresql databases so we can test against issues like this in the future?

[WilliButz]

@aanderse Thank's for highlighting me, I'll extend the test in the next days 👍

[dR3b]

@aanderse
I don't understand this right now. I use the following and it worked without any problems.

  services.mysql = {
    enable = true;
    package = pkgs.mariadb;
    ensureDatabases = [ "grafana" ];
    ensureUsers = [
      { name = "grafana";
        ensurePermissions = { "grafana.*" = "ALL PRIVILEGES"; };
      }
    ];
  };

It creates the DB and everything works.

[aanderse]

@dR3b as mentioned the problem only applies to specific postgresql versions, so using grafana with mysql should be fine.

[ekbfh]

Hello!
I also get this error.
I have predefined Postgres DB for grafana, i create user and database for grafana and then grafana can't migrate with error:

lvl=eror msg="Executing migration failed" logger=migrator id="Update user table charset" error="pq: relation \"UQE_user_login\" already exists"

Postgres 9.6.14
Grafana any from 5.0.3 to 6.2.5

[aanderse]

@ekbfh As mentioned in the linked workaround your two options are to either pin postgresql to a version which this bug doesn't exist, or execute the commands linked in the workaround.

Please let us know if you need help on either of those solutions.

[jaimebrolesi]

@aanderse where is the workaround link? I can't downgrade my postgres version :(

[aanderse]

@jaimebrolesi up a few comments: #64281 (comment)

[jaimebrolesi]

@aanderse I upgraded my pg to 11.5 and it worked like a charm! Thanks ;)

[DeamonMV]

It's worked for me

I using zalando postgres operator, PG version was 11.4. After I updated postgres to 11.5. grafana started as usual.

[stale]

Thank you for your contributions.
This has been automatically marked as stale because it has had no activity for 180 days.
If this is still important to you, we ask that you leave a comment below. Your comment can be as simple as "still important to me". This lets people see that at least one person still cares about this. Someone will have to do this at most twice a year if there is no other activity.
Here are suggestions that might help resolve this more quickly:

1. Search for maintainers and people that previously touched the
   related code and @ mention them in a comment.
2. Ask on the NixOS Discourse. 3. Ask on the #nixos channel on
   irc.freenode.net.