Greenfield nextcloud Linux MySQL next reboot borked DB access

[fermulator]

Good day,

Some weeks back I installed my first nextcloud instance. (nextcloud newbie, but advanced Linux user)

Was working through the installation manual instructions
https://docs.nextcloud.com/server/14/admin_manual/installation/index.html

Its obvious that there are "many ways" to install here. I went with roughly:

• Ubuntu 16.04 LTS
• MySQL 5.7
• Apache 2.4
• PHP 7.0

By the end, I had a working nextcloud instance! It was awesome. Did some basic configurations and PoC, seemed good. But later rebooted the server for maintenance then boom next boot nextcloud was down. (see below for error messages) RCA is DB permission issues.

Steps to reproduce

Not 100% certain

Specifically I focused on the Ubuntu manual install instructions -- https://docs.nextcloud.com/server/14/admin_manual/installation/source_installation.html#example-installation-on-ubuntu-16-04-lts-server

I will note that there were several times of uncertainty in DB creation (several issues, but it seemed like I had got through them)

Expected behaviour

Installation instructions for Ubuntu server should maybe be more clear (though I have yet to isolate exactly what went wrong, could be user error). Installation instructions need to result in persistent functionality (subsequent reboot shouldn't break the installation).

Actual behaviour

After installation, worked well. Few weeks later rebooted and nextcloud stopped responding to web hits. Checked logs and noticed:

[Sat Nov 10 10:57:32.601205 2018] [:error] [pid 18466] [client 1.0.0.52:46566] PHP Fatal error:  Uncaught Doctrine\\DBAL\\DBALException: Failed to connect to the database: An exception occured in driver: SQLSTATE[HY000] [1698] Access denied for user 'oc_fermulator'@'localhost' in /srv/nextcloud/lib/private/DB/Connection.php:64\nStack trace:\n#0 /srv/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(429): OC\\DB\\Connection->connect()\n#1 /srv/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(389): Doctrine\\DBAL\\Connection->getDatabasePlatformVersion()\n#2 /srv/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(328): Doctrine\\DBAL\\Connection->detectDatabasePlatform()\n#3 /srv/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(623): Doctrine\\DBAL\\Connection->getDatabasePlatform()\n#4 /srv/nextcloud/lib/private/DB/Connection.php(151): Doctrine\\DBAL\\Connection->setTransactionIsolation(2)\n#5 /srv/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/DriverManager.php(172): OC\\DB\\Connection->__construct(Array, Object(Doctrine\\DBAL\\Driver\\PDOMySql\\Driver), Object in /srv/nextcloud/lib/private/DB/Connection.php on line 64

Server configuration

Operating system: Ubuntu 16.04.5 LTS; 4.4.0-137-generic
Web server: apache 2.4.18-2ubuntu3.9
Database: mysql 5.7.24-0ubuntu0.16.04.1
PHP version: 7.0.32-0ubuntu0.16.04.1
Nextcloud version: 14.0.0.0 initially (maybe upgraded to 14.0.0.3)
Updated from an older Nextcloud/ownCloud or fresh install: fresh install to 14.0.0.0 (first time greenfield)
Where did you install Nextcloud from: nextcloud-14.0.0.tar.bz2
Signing status: ?

List of activated apps: only the SMB hook

/srv/nextcloud# sudo -u www-data php occ app:list
An unhandled exception has been thrown:
Doctrine\DBAL\DBALException: Failed to connect to the database: An exception occured in driver: SQLSTATE[HY000] [1698] Access denied for user 'oc_fermulator'@'localhost' in /srv/nextcloud/lib/private/DB/Connection.php:64

# cat config/config.php
<?php
$CONFIG = array (
  'instanceid' => 'SNIP',
  'passwordsalt' => 'SNIP',
  'secret' => 'SNIP',
  'trusted_domains' => 
  array (
    0 => 'nextcloud',
    1 => 'DOMAIN_SNIP',
  ),
  'trusted_proxies' => 
  array (
    0 => '1.0.0.52',
  ),
  'overwritehost' => 'SNIP',
  'overwriteprotocol' => 'https',
  'overwritewebroot' => '/cloud',
  'overwritecondaddr' => '^1\\.0\\.0\\.52$',
  'datadirectory' => '/opt/nextcloud/',
  'dbtype' => 'mysql',
  'version' => '14.0.0.19',
  'overwrite.cli.url' => 'https://nextcloud',
  'dbname' => 'nextcloud',
  'dbhost' => 'localhost',
  'dbport' => '',
  'dbtableprefix' => 'oc_',
  'dbuser' => 'oc_fermulator',
  'dbpassword' => 'SNIP',
  'installed' => true,
  'updater.secret' => SNIP,
);

Are you using external storage, if yes which one: local+smb

Are you using encryption: no

Are you using an external user-backend, if yes which one: not as of yet

[Sat Nov 10 10:57:32.602302 2018] [:error] [pid 18466] [client 1.0.0.52:46566] PHP Fatal error:  Uncaught Doctrine\\DBAL\\DBALException: Failed to connect to the database: An exception occured in driver: SQLSTATE[HY000] [1698] Access denied for user 'oc_fermulator'@'localhost' in /srv/nextcloud/lib/private/DB/Connection.php:64\nStack trace:\n#0 /srv/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(429): OC\\DB\\Connection->connect()\n#1 /srv/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(389): Doctrine\\DBAL\\Connection->getDatabasePlatformVersion()\n#2 /srv/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(328): Doctrine\\DBAL\\Connection->detectDatabasePlatform()\n#3 /srv/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(623): Doctrine\\DBAL\\Connection->getDatabasePlatform()\n#4 /srv/nextcloud/lib/private/DB/Connection.php(151): Doctrine\\DBAL\\Connection->setTransactionIsolation(2)\n#5 /srv/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/DriverManager.php(172): OC\\DB\\Connection->__construct(Array, Object(Doctrine\\DBAL\\Driver\\PDOMySql\\Driver), Object in /srv/nextcloud/lib/private/DB/Connection.php on line 64

[fermulator]

willing to work with an expert to debug the situation, and find the root cause, then go back and try to understand what was missing in the docs w.r.t. DB steps; then I can fix and pull request

[nextcloud-bot]

GitMate.io thinks possibly related issues are #10440 (MYSQL), #3381 (Failure to install Nextcloud with MySQL and unmasked DB password), #8245 (Nextcloud DB Replication...), #7690 (Linux Distribution Packages for Nextcloud), and #3911 (Slow Nextcloud).

[fermulator]

After reviewing #3381, I vaguely recall being confused by a similar thought:
#3381 (comment)

By the way, I'm curious: Why does Nextcloud attempt to create a new MySQL user here? Why not just use the one I set up for it?

[kesselb]

When you use dbuser and dbpassword from config.php can you login to your mysql server? 😕

[fermulator]

Indeed this is going to be the root cause most certainly. (the database instructions) - admittedly my DB foo is lacking

Docs-wise:

At the installation of the MySQL/MariaDB server, you will be prompted to create a root password. Be sure to remember your password as you will need it during Nextcloud database setup.
, this was fine;

I think I was eventually executing on:
https://docs.nextcloud.com/server/14/admin_manual/configuration_database/linux_database_configuration.html

I am able to sudo mysql -u root. (of course)

Definitely there is some GRANT/ACCESS issue from the installation (but no idea how it was working before....)

here's the current state

# mysql --user=oc_fermulator --password nextcloud
Enter password: 
ERROR 1698 (28000): Access denied for user 'oc_fermulator'@'localhost'

# mysql --user=oc_fermulator --password oc_nextcloud
Enter password: 
ERROR 1698 (28000): Access denied for user 'oc_fermulator'@'localhost'

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| nextcloud          |
| performance_schema |
+--------------------+

What's odd about the oc_fermulator user in the nextcloud configuration, is that this is not usually how I would roll. Typically i would create a dedicated user for nextcloud to AUTH to the DB for. Indeed I did!:

MariaDB [mysql]> select host, user, password from mysql.user;
+-----------+-----------------+-------------------------------------------+
| host      | user            | password                                  |
+-----------+-----------------+-------------------------------------------+
| localhost | root            | *SNIP |
| localhost | nextcloud_admin |                                           |
+-----------+-----------------+-------------------------------------------+
2 rows in set (0.00 sec)

But why isn't the nextcloud config using that user? (hmmm my memory fails me since I set this up >4 weeks ago at least)

I just re-ran the GRANT PRIVS for the nextcloud_admin user because it was refusing auth:

MariaDB [(none)]> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES ON nextcloud.* TO 'nextcloud_admin'@'localhost' IDENTIFIED BY '(SNIP)';
FLUSH privileges;

Now I can get in;

root@nextcloud:/srv/nextcloud/config# mysql --user=nextcloud_admin --password nextcloud
Enter password: 
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 25570
Server version: 10.0.36-MariaDB-0ubuntu0.16.04.1 Ubuntu 16.04

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 [nextcloud]> 

Then I rebooted the entire server just to start fresh, and 👍 ! (voila)

[fermulator]

Unfortunately I can't remember if I used the wizard or not (i don't remember doing so)
https://docs.nextcloud.com/server/14/admin_manual/installation/source_installation.html#installation-wizard

Thinking MAYBE ... I tried to do the DB config manually, and somehow screwed up the DB username in the nextcloud configuration.

Perhaps the "missing link" DOCS wise, is that from the installation page, we should link/reference to the "linux_database_configuration.html" page where appropriate?

[fermulator]

A subsequent thought, what is most concerning is that it suddenly broke. The initial greenfield installation was working ... and post-reboot it suddenly stopped. I struggle to fathom how though ; clearly the nextcloud config was messed up. I definitely did not edit/change it. (is it possible the upgrade, or some settings in the GUI, can change that config.php?)

[fermulator]

I wonder if it was related to password expiration possibly ... https://dev.mysql.com/doc/refman/5.7/en/password-management.html (will monitor).

[kesselb]

As this seems to be a setup issue I would like to ask you to raise your question in the forums: https://help.nextcloud.com