Db credentials not working after host migration

Hi all,
I am trying to migrate my Nextcloud v17 installation from one system to another.

Source host
FreeBSD 12.1-RELEASE-p1
Apache v2.4.41
PHP v7.2.25
mysql56-server-5.6.45

Target host
Debian 10 4.19.0-6-amd64
Apache 2.4.38-3+deb10u3
PHP 7.3.11-1~deb10u1
mariadb-server-10.3

I installed phpmyadmin on both systems. I then exported the nextcloud database from the source host and imported it on the target host. I copied over the nextcloud/config/config.php file.

However, when I run the occ script, it can’t authenticate:

/var/www/nextcloud# 
su -m www-data -c "php ./occ upgrade"
An unhandled exception has been thrown:
Doctrine\DBAL\DBALException: Failed to connect to the database: An exception occurred in driver: SQLSTATE[HY000] [1044] Access denied for user 'nextcloud'@'localhost' to database 'nextcloud' in /var/www/nextcloud/lib/private/DB/Connection.php:64

The database username and password are both “nextcloud”. I can log in manually with these credentials:

/var/www/nextcloud# mysql -u nextcloud -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 210
Server version: 10.3.18-MariaDB-0+deb10u1 Debian 10

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

What could be going wrong when the occ script tries to connect? Here is the relevant part of my config:

  'dbtype' => 'mysql',
  'version' => '17.0.2',
  'dbname' => 'nextcloud',
  'dbhost' => '127.0.0.1',
  'dbtableprefix' => 'oc_',
  'dbuser' => 'nextcloud',
  'dbpassword' => 'nextcloud',

TIA

At first you should check which users are allowed to access the database service:

MariaDB [(none)]> select user,host from mysql.user;

Now you should check which users are allowed to access which database:

MariaDB [(none)]> select user,host,db from mysql.db;

Keep in mind that “localhost” is not the same as “127.0.0.1” :wink:

If necessary, aunt G will tell you how you could add missing access rights.

after you import the db-dumpfile, you have to create the user for the imported db; as mysql-root do sth. like
grant whatever on nextcloud-db.* to 'your nc-user'@'localhost' identified by 'strong password';
(please look up exact syntax, rights, names etc. in documentation)
GOOD LUCK!

1 Like

Thank you @pete.dawgg and @j-ed

Before I go on, a quick question:

When you export database, does it contain the corresponding user credentials? Or are these stored in a separate database?

I ask because after I exported and imported my nextcloud database using phpmyadmin, I checked to see that the user rights were imported as well, and that seemed to be the case, at least as far as I could see via the phpmyadmin web interface. But I may have been mistaken.

TIA

You have to differentiate between Nextcloud users and general database users. If you’re going to export the Nextcloud database, only these user login accounts are exported. If the database users should also be exported, you have to export the internal databases too.

See https://linuxize.com/post/how-to-manage-mysql-databases-and-users-from-the-command-line/

The information_schema , mysql , performance_schema , and sys databases are created at installation time and they are storing information about all other databases, system configuration, users, permission and other important data. These databases are necessary for the proper functionality of the MySQL installation.

Logging into mysql and adding a password for the nextcloud user fixed things. Thanks for the link above which was very helpful.

sorry for getting back so late - you could also have checked with:

  1. log into mysql as root

  2. type:
    select user from mysql.user;

    MariaDB [(none)]> select user from mysql.user;
    ±---------+
    | user |
    ±---------+
    | ncuser | -> my nextcloud-user
    

    | root |
    ±---------+
    6 rows in set (0.000 sec)

then you can see if your nextcloud user exists in mariadb/mysql.
GOOD LUCK!