Nextcloud & MySQL

1

I have a fairly standard nextcloud setup with a fairly standard mysql setup:

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

services.nextcloud.config.dbtype = "mysql";

But I get: SQLSTATE[HY000] [1044] Access denied for user 'nextcloud'@'localhost' to database 'nextcloud' in the logs, and the nextcloud service doesn’t start up.

On the console I have:

# mysql -u nextcloud
ERROR 1698 (28000): Access denied for user 'nextcloud'@'localhost'

But:

mysql> SELECT User, Host, plugin FROM mysql.user;
+------------------+-----------+-----------------------+
| User             | Host      | plugin                |
+------------------+-----------+-----------------------+
| mysql            | localhost | auth_socket           |
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session    | localhost | caching_sha2_password |
| mysql.sys        | localhost | caching_sha2_password |
| mysqlbackup      | localhost | auth_socket           |
| nextcloud        | localhost | auth_socket           |
| root             | localhost | caching_sha2_password |
+------------------+-----------+-----------------------+
7 rows in set (0.00 sec)

Also, runuser -u nextcloud -- mysql succeeds.

So is the issue that nextcloud doesn’t try to access over auth_socket, but over password, and this fails?

2

Not really. After setting services.nextcloud.config.dbpass and doing this:

alter user nextcloud@localhost identified with caching_sha2_password by 'somepassword';

It still shows the same error.

3

I should add that I’m trying to migrate from SQLite.

4

Also trying to convert the database beforehand fails:

# nextcloud-occ db:convert-type mysql nextcloud localhost nextcloud
What is the database password?
Creating schema in new database

In Connection.php line 85:
                                                                                                                                                                         
  Failed to connect to the database: An exception occurred in the driver: SQLSTATE[HY000] [1044] Access denied for user 'nextcloud'@'localhost' to database 'nextcloud'  
                                                                                                                                                                         

db:convert-type [--port PORT] [--password PASSWORD] [--clear-schema] [--all-apps] [--chunk-size CHUNK-SIZE] [--] <type> <username> <hostname> <database>


# runuser -u nextcloud -- nextcloud-occ db:convert-type --all-apps mysql nextcloud localhost nextcloud
What is the database password?
Creating schema in new database

In Connection.php line 85:
                                                                                                                                                                       
  Failed to connect to the database: An exception occurred in the driver: SQLSTATE[HY000] [1045] Access denied for user 'nextcloud'@'localhost' (using password: YES)  
                                                                                                                                                                       

db:convert-type [--port PORT] [--password PASSWORD] [--clear-schema] [--all-apps] [--chunk-size CHUNK-SIZE] [--] <type> <username> <hostname> <database>
5

Looks like at some point you were using mysql instead of mariadb perhaps? What happens if you change the authentication plugin from auth_socket to unix_socket? Also, do you run mysql_upgrade after you upgrade major versions of mariadb?

Just a few guess… let me know if any of that helps, or if any of that isn’t clear.

6

I tried again today and after a lot of back and forth and some hickups, I managed to transition to Postgresql! Performance seems to be way better now.

Here is roughly what I did, but I might have missed some steps.

  1. I had postgresql running already because of another service, if you don’t, probably services.postgresql.enable = true;
  2. Run nextcloud-occ db:convert-type --all-apps pgsql nextcloud /run/postgresql nextcloud. This might ask you to set a password, but this password is never needed again I hope
  3. config services.nextcloud.database.createLocally = true;
    add the following big clunk to your config:
  services.nextcloud = {
    config = {
      dbtype = "pgsql";
      dbhost = "/run/postgresql";
    };
  };
  services.postgresql = {
    # Copied & adapted from nixpkgs
    enable = true;
    ensureDatabases = [ "nextcloud" ];
    ensureUsers = [{
      name = "nextcloud";
      ensurePermissions = { "DATABASE nextcloud" = "ALL PRIVILEGES"; };
    }];
  };

this does basically the same like services.nextcloud.database.createLocally = true;, but for some reason only the lengthy version works for me, otherwise I get SQLSTATE[08006] [7] connection to server on socket "/run/postgresql/.s.PGSQL.5432" failed: FATAL: Peer authentication failed for user "nextcloud". (One thing the lengthy version doesn’t do is starting postgresql before nextcloud, so if you ever have trouble with nextcloud accessing the db at startup, it might be a race condition and you should retry/restart the nextcloud service.) If you understand why one works and not the other, let me know!
4. nixos-rebuild switch