How to convert Nextcloud database from sqlite3 to mysql

Reading Time: 3 minutes

My wife wants to use Nextcloud as cloud storage.

My toy is Raspberry Pi, not strawberry pi.

In this article I converted database of Nextcloud from sqlite3 to mysql.

How to convert

Just run occ command with proper arguments.
– My database name and user name is nextcloud.

sudo -u www-data php occ db:convert-type mysql nextcloud db nextcloud

I enter container and execute above command, then I faced problem with,

could not find driver

Then I added database driver for PHP.

docker-php-ext-install pdo_mysql
docker-php-ext-enable pdo_mysql

You need to enter database password but there is limitation which character is allowed.

In my case I firstly include “?” but it always fails.
So I excluded “?” and went fine.　 

Next I faced another problem.
\x9F\x91\x8D is not correct data format.

sudo -u www-data php occ db:convert-type mysql nextcloud db nextcloud
The process control (PCNTL) extensions are required in case you want to interrupt long running commands - see https://www.php.net/manual/en/book.pcntl.php
The current PHP memory limit is below the recommended value of 512MB.
Nextcloud is in maintenance mode - no apps have been loaded

 105/154 [===================>--------]  68%
In ExceptionConverter.php line 114:

  An exception occurred while executing a query: SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect string value: '\xF
  0\x9F\x91\x8D' for column `nextcloud`.`oc_comments`.`message` at row 1

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

x9F\x91\x8D is “👍” in emoji.

I did below countermeasure then this error was removed.
– Link

This is command what I run.

# In nextcloud container
sudo -u www-data php occ config:system:set mysql.utf8mb4 --type boolean --value="true"

# In mysql container
mysql -u root -p
(Enter password)
> ALTER DATABASE nextcloud CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
> exit;

sudo -u www-data php occ maintenance:repair
sudo -u www-data php occ db:convert-type mysql nextcloud db nextcloud --clear-schema --password='YOUR_PASSWORD'

I tried occ db:convert-type again and passed.
It took over 3 hours!

config/config.php is also modified.

<?php
$CONFIG = array (
  (snip)
  'dbtype' => 'mysql',
   'mysql.utf8mb4' => true,
  'dbname' => 'nextcloud',
  'dbhost' => 'db',
  'dbuser' => 'nextcloud',
  'dbpassword' => 'YOUR_PASSWORD',
);

With above configuration Nextcloud intends to access mysql with TCP socket which has 3-way handshake.
I corrected to use Unix Domain Socket like ‘dbhost’ => ‘localhost:/var/run/mysqld/mysqld.sock’,

Smoke test

On top page there is no warnings/errors related database.

On administration page mysql was recognized!

Conclusion

How was it?

I faced some issues than expected.
I hope this article will help you to avoid wasting time like me!