ownCloud Database Migration

Starting with version 10.15.0, the ownCloud package will transition from SQLite to its original MySQL/MariaDB back-end. This update also includes optimized configurations to eliminate all warnings in the settings screen. However, the transition remains somewhat experimental and cannot yet be fully integrated into the upgrade wizard. As a result, some manual steps are required to complete the process. The good news is that this is a one-time task, and detailed step-by-step instructions for executing it via SSH are provided below:

Instructions for ownCloud version 10.14.0

1. Install redis from the SynoCommunity repository.
2. Enable SSH (if needed) and connect to your DiskStation.
3. Set up the following shortcuts:

oc_php=/usr/local/bin/php74
oc_sql=/usr/local/mariadb10/bin/mysql

For DSM 6:

oc_home=/var/services/web/owncloud
oc_occ=/var/services/web/owncloud/occ
oc_usr=http

For DSM 7:

oc_home=/var/services/web_packages/owncloud
oc_occ=/var/services/web_packages/owncloud/occ
oc_usr=sc-owncloud

4. Save your MySQL password (replace [...] with your value).

oc_rpwd=[your MySQL root password]

5. Set a random password for the database user.

oc_upwd=$(LC_ALL=C tr -dc 'A-Za-z0-9:@.,/+!=-' </dev/urandom | head -c 30; echo)

6. Enable maintenance mode.

sudo -u $oc_usr $oc_php $oc_occ maintenance:mode --on

7. Add HSTS to the Apache configuration.

sudo tee -a $oc_home/.htaccess > /dev/null <<EOF
<IfModule mod_headers.c>
Header always set Strict-Transport-Security "max-age=15552000; includeSubDomains"
</IfModule>
EOF
sudo chown $oc_usr $oc_home/.htaccess

8. Prepare the environment for migration.

sudo -u $oc_usr $oc_php $oc_occ config:system:set mysql.utf8mb4 --value=true

9. Open MariaDB 10 in DSM and enable the TCP/IP connection.

10. Create a new MySQL database.

$oc_sql -u root -p$oc_rpwd -e "create database owncloud; grant all privileges on owncloud.* to 'oc_admin'@'localhost' identified by '$oc_upwd';"

11. Disable maintenance mode.

sudo -u $oc_usr $oc_php $oc_occ maintenance:mode --off

12. Perform the database conversion (this may take several minutes).

For DSM 6:

sudo -u $oc_usr $oc_php $oc_occ db:convert-type --port=3307 --password=$oc_upwd --all-apps mysql oc_admin 127.0.0.1 owncloud

For DSM 7:

sudo -u $oc_usr $oc_php $oc_occ db:convert-type --password=$oc_upwd --all-apps mysql oc_admin 127.0.0.1 owncloud

Note: If an error occurs, you can remove the partially migrated database and restart from Step 10 (Create a new MySQL database). To remove the database and user, run the following command:

$oc_sql -u root -p$oc_rpwd -e "drop database owncloud;" -e "drop user 'oc_admin'@'localhost';"

13. Enable maintenance mode.

sudo -u $oc_usr $oc_php $oc_occ maintenance:mode --on

14. Configure the database connection.

sudo -u $oc_usr $oc_php $oc_occ config:system:set dbhost --value="localhost:/run/mysqld/mysqld10.sock"

15. Open MariaDB 10 in DSM and disable the TCP/IP connection.

For DSM 6:

16. Backup the PHP config file, preserving its attributes.

sudo cp -p /usr/syno/etc/packages/WebStation/PHPSettings.json /usr/syno/etc/packages/WebStation/PHPSettings.json.bak

17. Modify the JSON file using jq.

sudo jq '.["com-synocommunity-packages-owncloud"].extensions += ["pdo_mysql", "redis"]' /usr/syno/etc/packages/WebStation/PHPSettings.json > temp.json

18. Move the modified file back to its original location.

sudo mv temp.json /usr/syno/etc/packages/WebStation/PHPSettings.json

19. Restore the original file's attributes (permissions, ownership, timestamps).

sudo cp --attributes-only /usr/syno/etc/packages/WebStation/PHPSettings.json.bak /usr/syno/etc/packages/WebStation/PHPSettings.json

20. Restart Apache (if it fails, you may need to start it manually).

if jq -e 'to_entries | map(select((.key | startswith("'"com-synocommunity-packages-"'")) and .key != "'"com-synocommunity-packages-owncloud"'")) | length == 0' "/usr/syno/etc/packages/WebStation/PHPSettings.json" >/dev/null; then \
sudo /usr/syno/sbin/synoservice --restart pkgctl-Apache2.4 \
else echo "Failed to restart Apache" ;\
fi

For DSM 7:

16. Open Web Station from the DSM application menu.
17. In the Script Language Settings section, select the ownCloud Profile.
18. Click Edit to modify the configuration.
19. Under Extensions, enable pdo_mysql.
20. Click Save to apply the changes.

21. Configure memory caching.

sudo -u $oc_usr $oc_php $oc_occ config:system:set memcache.local --value="\OC\Memcache\APCu"

22. Configure file locking.

sudo -u $oc_usr $oc_php $oc_occ config:system:set memcache.locking --value="\OC\Memcache\Redis"
sudo -u $oc_usr $oc_php $oc_occ config:system:set filelocking.enabled --value true

23. Disable maintenance mode.

sudo -u $oc_usr $oc_php $oc_occ maintenance:mode --off

24. Set up background jobs.

sudo -u $oc_usr $oc_php $oc_occ system:cron

Note: After upgrading to ownCloud version 10.15.0, you may encounter login issues with messages such as "You took too long to login, please try again." To resolve this, clear your browser's cache for cookies and site data.

Note: After the upgrade, you may need to reconfigure HSTS and background jobs. Please refer to the steps "Add HSTS to the Apache configuration" and "Set up background jobs" to reapply these settings. Additionally, the HTTPS redirect may also need to be applied.

25. Add HTTPS redirect to the Apache configuration (if needed).

sudo tee -a $oc_home/.htaccess > /dev/null <<EOF
RewriteEngine On
RewriteCond %{HTTPS} off
RewriteRule ^(.*)$ https://%{HTTP_HOST}%{REQUEST_URI} [L,R=301]
EOF
sudo chown $oc_usr $oc_home/.htaccess