Migrating ownCloud from sqlite to MySQL and dropping ownCloud alltogether

No backup, not pity. You need to know what you are doing and don’t blame me for your broken ownCloud.

Recently, I was upgrading my ownCloud installation from 9.1.x to 10.0.x, Cause I like to keep my stuff up to date. This was fairly easy through the admin interface. After the update, there where some recommendations about using a „memcache.filelock“. The Redis-setup was quite easy too. The 2nd recommendation was about using MySQL instead of sqlite. For my very small installation, sqlite should have been quite enough, but the ownCloud console „occ“ offers an automated conversion script. So I thought to myself: Why not?

Trying it automatically

This didn’t work for me!

You simply have to create a database for ownCloud and run this command. You will be asked for the password of the database connection.

sudo -u www-data php occ db:convert-type --clear-schema --all-apps -- mysql owncloud localhost owncloud

The script also updates the database-type in the Configuration file „config/config.php“. But there are bugs in the conversion script documented at github, namely #28223 and #27075, that hit me too. So I decided to convert the database manually. There are several tools to help us with this.

Doing it manually

I started with converting the database to match the xml database scheme in  „db_structure.xml“ using the „occ“-command. Putting ownCloud into maintenance mode should be a good idea too.

sudo -u www-data php occ maintenance:singleuser --on
sudo -u www-data php occ db:generate-change-script |sqlite3 data/owncloud.db

Now you need to dump the sqlite database and load it into MySQL. Unfortunately the sqlite dump is not compatible with MySQL, but it can be converted. You can find a script called „sqlite3-to-mysql.py“ on several locations on the internet. Good old „sed“-command might have worked too. The script only removes and replaces some lines. It also needed some extra replacement rules to it.

...
 "COLLATE BINARY": "COLLATE utf8_bin",
 "CLOB": "LONGTEXT",
 ", lock ": ",`lock` ",
...

sqlite3 data/owncloud.db .dump >dump.sql
cat dump.sql|./sqlite3-to-mysql.py|mysql owncloud

Afterwards there where still some differences between „db_structure.xml“. So I ran the change-script-generator again.

sudo -u www-data php occ db:generate-change-script|mysql owncloud
sudo -u www-data php occ db:convert-mysql-charset

This removes „execution_duration“ column from „oc_jobs“ table. This is because of an error in „db_structure.xml“. So I added „execution_duration“ back again afterwards. This could be the cause for #28223.

Dropping ownCloud in Favor of NextCloud

After thinking about the Bugs I encountered, I decided to change from ownCloud to NextCloud. On the NextCloud manual, the procedure is just like a manual update. Everything worked fine, without any problems. ownClound 10.0.7 -> NextCloud 12.