Converting database from sqlite to mysql fails with access denied error

ℹ️ Support
,
1

Attempting a Nextcloud database conversion from sqlite to mysql fails with an access denied error.

Nextcloud version 16.0.5:
Operating system and version CentOS 7:
Apache or nginx version Apache 2.4.41:
PHP version 7.2:

In the Nextcloud Admin overview, it recommends converting the database from sqlite to mysql. I am following the documentation for this. I have attempted this on and off in Nextcloud versions 15 and 16 but have run into the same error every time. Is there something missing from the documentation?

Is this the first time you’ve seen this error? (N):

Steps to replicate it:

  1. Run the following command, while logged into command line as the user, using the user’s mysql database password:

php occ db:convert-type --all-apps mysql user localhost nextcloud

This is the full output:

php occ db:convert-type --all-apps mysql user localhost nextcloud --password="password"

In Connection.php line 64:

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

Troubleshooting:

  1. I am able to log into mysql on command line with the same user the Nextcloud files are owned by with
mysql -u user -p -h localhost
  1. I am able to log into the existing sqlite database from command line and list tables.
  2. I had set my database username and password with the occ command e.g. (username obfuscated). I tried this password as well as the mysql password, but got the same error
php occ config:system:set dbuser --value "user""

The output of your Nextcloud log in Admin > Logging:

The conversion attempts generates no output to the Nextcloud log

The output of your config.php file in /path/to/nextcloud (make sure you remove any identifiable information!):

$ cat config/config.php |grep -v pass |grep -v secret |grep -v salt
<?php
$CONFIG = array (
  'instanceid' => 'idstring',
  'trusted_domains' =>
  array (
    0 => 'nextcloud.user.com',
  ),
  'datadirectory' => '/home/user/public_html/nextcloud/data',
  'overwrite.cli.url' => 'https://www.user.com/nextcloud',
  'dbtype' => 'sqlite3',
  'version' => '16.0.5.1',
  'installed' => true,
  'memcache.local' => '\\OC\\Memcache\\APCu',
  'theme' => '',
  'loglevel' => 2,
  'maintenance' => false,
  'mail_smtpmode' => 'sendmail',
  'mail_smtpauthtype' => 'LOGIN',
  'mail_from_address' => 'nextcloudadmin',
  'mail_domain' => 'user.com',
  'updater.release.channel' => 'stable',
  'app_install_overwrite' =>
  array (
    0 => 'calendar',
  ),
  'dbuser' => 'user',
);

This is written to /var/log/mysqld.log. I am using the same password that allows the user to log into mysql on the command line

2019-10-05T22:31:39.368399Z 8643 [Note] Access denied for user 'user'@'localhost' (using password: YES)
2019-10-05T22:31:39.370709Z 8644 [Note] Access denied for user 'user'@'localhost' (using password: YES)
2

Meines Wissens verwendet der mysql-Befehl nicht die Netzwerkschnittstelle für den Datenbankzugriff, wenn man an der Konsole als Host “localhost” nutzt, sondern den Datenbank-Socket. Dies kann der Grund dafür sein, dass Dein Test zwar erfolgrech verläuft, Nextcloud sich aber beschwert.

Hier wird beschrieben, wie Du einen neuen Datenbank-User anlegst:

Wenn Du dich von der Konsole an der Datenbank anmeldest, kannst Du mittels des folgenden Befehls prüfen, ob 'user'@'localhost' zugriffsberechtigt ist.

SELECT User,Host FROM mysql.user;