Failed to connect to database when converting sqlite to mysql

amadeous · December 23, 2019, 10:05pm

Nextcloud version (eg, 12.0.2): 17.0.2.1
Operating system and version (eg, Ubuntu 17.04): probably Ubuntu 18.04
Apache or nginx version (eg, Apache 2.4.25): unknown (running on a shared hosting)
PHP version (eg, 7.1): 7.3.1

The issue I am facing:
I created a NextCloud instance for an association I am in charge. I installed it and we started using it. I thought it was using MySQL but I recently noticed it was not. This is why I wanted to convert the database to MySQL from sqlite. We are already more than 10 users on this instance !
I created a MySQL database on the shared server provider interface and could connect to it with the mysql command (or equivalent) with the defined credentials, from several addresses. I tried to convert the database with php occ db:convert-type mysql nextcloud-u mydomain.tld:3306 nextcloud-db but I got an error with the following stacktrace:

An unhandled exception has been thrown:
Doctrine\DBAL\DBALException: Failed to connect to the database: An exception occurred in driver: SQLSTATE[HY000] [14] unable to open database file in /path/lib/private/DB/Connection.php:64
Stack trace:
#0 /path/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(1040): OC\DB\Connection->connect()
#1 /path/lib/private/DB/Connection.php(216): Doctrine\DBAL\Connection->executeUpdate('PRAGMA read_unc...', Array, Array)
#2 /path/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(683): OC\DB\Connection->executeUpdate('PRAGMA read_unc...')
#3 /path/lib/private/DB/Connection.php(151): Doctrine\DBAL\Connection->setTransactionIsolation(2)
#4 /path/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/DriverManager.php(195): OC\DB\Connection->__construct(Array, Object(Doctrine\DBAL\Driver\PDOSqlite\Driver), Object(Doctrine\DBAL\Configuration), Object(Doctrine\Common\EventManager))
#5 /path/lib/private/DB/ConnectionFactory.php(156): Doctrine\DBAL\DriverManager::getConnection(Array, Object(Doctrine\DBAL\Configuration), Object(Doctrine\Common\EventManager))
#6 /path/lib/private/Server.php(635): OC\DB\ConnectionFactory->getConnection('sqlite3', Array)
#7 /path/3rdparty/pimple/pimple/src/Pimple/Container.php(118): OC\Server->OC\{closure}(Object(OC\Server))
#8 /path/lib/private/ServerContainer.php(120): Pimple\Container->offsetGet('OCP\\IDBConnecti...')
#9 /path/lib/private/AppFramework/Utility/SimpleContainer.php(161): OC\ServerContainer->query('OCP\\IDBConnecti...')
#10 /path/3rdparty/pimple/pimple/src/Pimple/Container.php(114): OC\AppFramework\Utility\SimpleContainer->OC\AppFramework\Utility\{closure}(Object(OC\Server))
#11 /path/lib/private/ServerContainer.php(120): Pimple\Container->offsetGet('DatabaseConnect...')
#12 /path/lib/private/Server.php(1554): OC\ServerContainer->query('DatabaseConnect...')
#13 /path/lib/private/Server.php(362): OC\Server->getDatabaseConnection()
#14 /path/3rdparty/pimple/pimple/src/Pimple/Container.php(118): OC\Server->OC\{closure}(Object(OC\Server))
#15 /path/lib/private/ServerContainer.php(120): Pimple\Container->offsetGet('OC\\Authenticati...')
#16 /path/lib/private/AppFramework/Utility/SimpleContainer.php(69): OC\ServerContainer->query('OC\\Authenticati...', true)
#17 /path/lib/private/AppFramework/Utility/SimpleContainer.php(99): OC\AppFramework\Utility\SimpleContainer->buildClass(Object(ReflectionClass))
#18 /path/lib/private/AppFramework/Utility/SimpleContainer.php(114): OC\AppFramework\Utility\SimpleContainer->resolve('OC\\Authenticati...')
#19 /path/lib/private/ServerContainer.php(145): OC\AppFramework\Utility\SimpleContainer->query('OC\\Authenticati...', true)
#20 /path/lib/private/AppFramework/Utility/SimpleContainer.php(69): OC\ServerContainer->query('OC\\Authenticati...', true)
#21 /path/lib/private/AppFramework/Utility/SimpleContainer.php(99): OC\AppFramework\Utility\SimpleContainer->buildClass(Object(ReflectionClass))
#22 /path/lib/private/AppFramework/Utility/SimpleContainer.php(114): OC\AppFramework\Utility\SimpleContainer->resolve('OC\\Authenticati...')
#23 /path/lib/private/ServerContainer.php(145): OC\AppFramework\Utility\SimpleContainer->query('OC\\Authenticati...', true)
#24 /path/lib/private/AppFramework/Utility/SimpleContainer.php(161): OC\ServerContainer->query('OC\\Authenticati...')
#25 /path/3rdparty/pimple/pimple/src/Pimple/Container.php(114): OC\AppFramework\Utility\SimpleContainer->OC\AppFramework\Utility\{closure}(Object(OC\Server))
#26 /path/lib/private/ServerContainer.php(120): Pimple\Container->offsetGet('OC\\Authenticati...')
#27 /path/lib/private/Server.php(374): OC\ServerContainer->query('OC\\Authenticati...')
#28 /path/3rdparty/pimple/pimple/src/Pimple/Container.php(118): OC\Server->OC\{closure}(Object(OC\Server))
#29 /path/lib/private/ServerContainer.php(120): Pimple\Container->offsetGet('OC\\User\\Session')
#30 /path/lib/private/AppFramework/Utility/SimpleContainer.php(161): OC\ServerContainer->query('OC\\User\\Session')
#31 /path/3rdparty/pimple/pimple/src/Pimple/Container.php(114): OC\AppFramework\Utility\SimpleContainer->OC\AppFramework\Utility\{closure}(Object(OC\Server))
#32 /path/lib/private/ServerContainer.php(120): Pimple\Container->offsetGet('UserSession')
#33 /path/lib/private/Server.php(1437): OC\ServerContainer->query('UserSession')
#34 /path/lib/private/Server.php(1028): OC\Server->getSession()
#35 /path/3rdparty/pimple/pimple/src/Pimple/Container.php(118): OC\Server->OC\{closure}(Object(OC\Server))
#36 /path/lib/private/ServerContainer.php(120): Pimple\Container->offsetGet('OC\\Security\\CSR...')
#37 /path/lib/private/Server.php(1023): OC\ServerContainer->query('OC\\Security\\CSR...')
#38 /path/3rdparty/pimple/pimple/src/Pimple/Container.php(118): OC\Server->OC\{closure}(Object(OC\Server))
#39 /path/lib/private/ServerContainer.php(120): Pimple\Container->offsetGet('OC\\Security\\CSR...')
#40 /path/lib/private/Server.php(1900): OC\ServerContainer->query('OC\\Security\\CSR...')
#41 /path/lib/private/Server.php(823): OC\Server->getCsrfTokenManager()
#42 /path/3rdparty/pimple/pimple/src/Pimple/Container.php(118): OC\Server->OC\{closure}(Object(OC\Server))
#43 /path/lib/private/ServerContainer.php(120): Pimple\Container->offsetGet('OCP\\IRequest')
#44 /path/lib/private/AppFramework/Utility/SimpleContainer.php(161): OC\ServerContainer->query('OCP\\IRequest')
#45 /path/3rdparty/pimple/pimple/src/Pimple/Container.php(114): OC\AppFramework\Utility\SimpleContainer->OC\AppFramework\Utility\{closure}(Object(OC\Server))
#46 /path/lib/private/ServerContainer.php(120): Pimple\Container->offsetGet('Request')
#47 /path/lib/private/Server.php(1304): OC\ServerContainer->query('Request')
#48 /path/lib/private/Server.php(466): OC\Server->getRequest()
#49 /path/3rdparty/pimple/pimple/src/Pimple/Container.php(118): OC\Server->OC\{closure}(Object(OC\Server))
#50 /path/lib/private/ServerContainer.php(120): Pimple\Container->offsetGet('OCP\\L10N\\IFacto...')
#51 /path/lib/private/AppFramework/Utility/SimpleContainer.php(161): OC\ServerContainer->query('OCP\\L10N\\IFacto...')
#52 /path/3rdparty/pimple/pimple/src/Pimple/Container.php(114): OC\AppFramework\Utility\SimpleContainer->OC\AppFramework\Utility\{closure}(Object(OC\Server))
#53 /path/lib/private/ServerContainer.php(120): Pimple\Container->offsetGet('L10NFactory')
#54 /path/lib/private/Server.php(1490): OC\ServerContainer->query('L10NFactory')
#55 /path/lib/private/Server.php(1501): OC\Server->getL10NFactory()
#56 /path/lib/base.php(234): OC\Server->getL10N('lib')
#57 /path/lib/base.php(645): OC::checkConfig()
#58 /path/lib/base.php(1078): OC::init()
#59 /path/console.php(46): require_once('/path...')
#60 /path/occ(11): require_once('/path...')
#61 {main}

I represented the address of the database in multiple ways: with 127.0.0.1, localhost and by adding the port with “:3306:” in the end. None if this worked.
I checked the database was correctly set up and reachable by starting a docker nextcloud instance. I could install NextCloud using this MySQL database and use it normally.
However, when trying to migrate from sqlite to MySQL, I cannot make it work for some reason.

Is this the first time I’ve seen this error? (Y/N): Y

Steps to replicate it:

Create a new nextcloud instance
Add files and a bunch of users
Use the command php occ db:convert-type mysql nextcloud-u 127.0.0.1:3306 nextcloud-db to migrate from sqlite to mysql.

The output of my Nextcloud log in Admin > Logging:
Not relevant, nothing came up.

The output of my config.php file in /path/to/nextcloud:

<?php
$CONFIG = array (
  'instanceid' => '...',
  'passwordsalt' => '...',
  'secret' => '...',
  'trusted_domains' =>
  array (
    0 => '...',
  ),
  'datadirectory' => '/path/httpdocs/data',
  'overwrite.cli.url' => 'http://mydomain.tld',
  'dbtype' => 'sqlite3',
  'version' => '17.0.2.1',
  'logtimezone' => 'UTC',
  'installed' => true,
  'mail_from_address' => 'nextcloud',
  'mail_smtpmode' => 'sendmail',
  'mail_domain' => 'mydomain.tld',
  'maintenance' => false,
  'theme' => '',
  'loglevel' => 2,
  'mail_sendmailmode' => 'smtp',
  'mail_smtpsecure' => 'ssl',
  'updater.secret' => '...',
);

The output of my Apache/nginx/system log in /var/log/____:
Not relevant, nothing came up.

Thank you in advance for your assistance!

Bernie_O · December 23, 2019, 11:10pm

Dashes (-) in database names (and maybe also in database usernames) are likely to cause problems with mysql.

Can you setup a new database/username without dashes or any other funky characters and try again?

amadeous · December 24, 2019, 1:42pm

Thank you for your proposition @Bernie_O!

I created a new database and username without hyphen or fancy characters. I tried with the following command
php occ db:convert-type mysql nextcloudDBUser mydomain.tld:3306 nextcloudDB --all-apps --clear-schema and it failed with the same stack trace.

Before running this command, I tried to connect to the database with the mysql command and it worked. I also tried to use the IP address of the database server and to remove or add the port number (I noticed on a docker fresh install that the port was required) to successfully connect to the database.

Bernie_O · December 24, 2019, 2:22pm

Is the user running the command able to read the sqlite3 database in the datafolder of nextcloud?
And (but that is not the cause of this error): options should precede the database type in that command. See here for correct syntax:
https://docs.nextcloud.com/server/17/admin_manual/configuration_database/db_conversion.html#run-the-conversion

amadeous · December 24, 2019, 4:18pm

It might be that! I tried to use the cron tab on the web interface of the hosting provided to execute occ with the right version of PHP and provided the correct argument. I got the following message

Creating schema in new database
The following tables will not be converted:
oc_privatedata


The current PHP memory limit is below the recommended value of 512MB.
Nextcloud is in maintenance mode - no apps have been loaded

And the MySQL database has been filled with tables!
I think that I do not have the right rights with my shell and that the cron tab has.

However, that’s not the end of it…

Only the migration table has lines (54) so it seems that data has not been migrated. Should I perform the migration with the maintenance mode off? I thought it would be better in order to avoid changes in the database while performing the migration
While searching for solutions, I noticed that a confirmation should been asked before performing the conversion. I did not see this message. Shall I edit Nextcloud’s code accordingly to avoid the confirmation awaiting?

Thank you for your prompt support, I really appreciate it!

Bernie_O · December 24, 2019, 6:44pm

Try with option -n:

 -n, --no-interaction  Do not ask any interactive question

https://docs.nextcloud.com/server/17/admin_manual/configuration_server/occ_command.html#

I would do the migration with Nextcloud being in maintenance mode (on)

amadeous · December 25, 2019, 8:46am

I tried in maintenance mode (on) with the --no interaction option and got the same result…
(Full command: php occ db:convert-type --all-apps --clear-schema --no-interaction --password="..." mysql nextcloudDBUser 127.0.0.1:3306 nextcloudDB)

Should I clear the database before trying to convert again the sqlite database? I was concern about not messing up any internal database migration progress/state.

I wonder: would it be possible that the occ command (or some internal state in wordpress, stored in files) is corrupted, preventing a normal database conversion? I keep having this PHP Memory Limit warning but according my phpinfo(), it is as configured at 1024MB…

I had a look at the conf/config.php file and noticed that sqlite3 is still the used database, as expected since the conversion is incomplete.

Bernie_O · December 25, 2019, 11:33am

Yes, I would do that.

I doubt that unless you get a file integrity check notice in nextclouds admin panel.

EDIT: no idea about the php Memory limit. The command should run also with 512mb (I also have only 512mb).

amadeous · December 25, 2019, 2:58pm

I cleared the database and tried again but it did not helped. I also tried to run the conversion without being in maintenance mode (off) since the problem was that app were not loaded. Still nothing more…

It creates the tables but only one is filled with information.

From the admin panel, no file integrity warning, nothing more than just a warning about my current sqlite database being weak…
I don’t know what I can do to make it work…

Concerning the Memory limit, I noticed that it occured only when using occ. I have no warning in the admin panel. Could it change something during the database conversion?

Bernie_O · December 26, 2019, 7:29am

I just tried that command myself and can confirm that (despite --no-interaction given) there is a confirmation required to continue with the conversion after the database schema has been created.

As a workaround you can comment out lines 214-220 in /PATH/TO/NEXTCLOUD/core/Command/Db/ConvertType.php (by adding double slashes (//) to the beginning of each line).

You might want to undo the changes after successfull conversion, otherwise you might get a file-integrity warning in the admin-panel.

And after the migration you also might want to enable utf8mb4: Enabling MySQL 4-byte support — Nextcloud latest Administration Manual latest documentation

EDIT: Despite the fact that the sqlite3-database will not be changed with the migration, it might be a good idea to create a backup: copy nextcloud.db in the datadirectory to a safe place.

Bernie_O · December 30, 2019, 2:32pm

For reference: I created a pull request that will enable the option --no-interaction for the command occ db:convert-type which will hopefully find its way into one of the next versions of Nextcloud:

If you click on the changed-files tab, you could apply the changes yourself (it is only adding two lines and deleting another line).

amadeous · January 1, 2020, 2:00pm

I finally got it to work!

Running the conversion took before something like 15 seconds but I guess it was just a timeout because the confirmation never came. Commenting the lines you mentioned did the trick and I could convert the database to MariaDB. I configured the uft8mb4 support as well.

Thank you a lot for your support! I really appreciate it! You have my gratitude
Best wishes for 2020!