How to migrate ownCloud 6 from sqlite to MySQL

Photo from unsplash

Those who do not know what ownCloud is probably should read about it here: http://owncloud.com. This is an awesome tool that provides functionality similar to dropbox, but uses your own server to host the files. We all know about dropbox’s bad behavior like storing passwords on the server side, accessing your content when you don’t want it. Actually recently I was monitoring disk activity on a computer with dropbox installed on it and found that for some reason dropbox keeps reading files where it has not business to do. It was clearly configured to replicate files in its own folder, yet was reading all files on the disk. One way or another there are a lot of reasons to get rid of dropbox and switch to ownCloud. It’s cheaper, secure and can be used to store business or confidential information where dropbox fails to provide both security and confidentiality.

By default ownCloud uses sqlite3 to keep records of the files it stores and people like myself that decided to go forth with the default configuration get trapped with this configuration. Once you load more than couple gigs of data you will notice that it gets slower and sloooower and slo… Sometimes you might even experience complete freeze on the client side.

The solution to the problem is moving the data from sqlite to something mroe serious like MySQL. Both databases have SQL in their name so the migration should be easy. Unfortunately ownCloud doesn’t provide any means to automate this migrate. To make matters worse the dialects sqlite3 and MySQL use are a little bit different so you can’t feed sqlite3 database dump directly into MySQL. I saw multiple solutions to the problem migrating ownCloud 5, but nothing for OC6 so far. Here is how I migrated it in just a few simple commands:

  1. In your ownCloud installation find the file called owncloud.db. It is located where the rest of the files are.
  2. Change to that directory: cd <where you woncloud.db is located>
  3. Stop the webserver, for instance service httpd stop
  4. Dump the sqlite3 data: sqlite3 owncloud.db dump >> owncloud.sql
  5. Create MySQL database for owncloud, start mysql and run this: create database owncloud
  6. Give necessary permissions to access owncloud MySQL database: grant all privileges on owncloud.* to owncloud_user@localhost identified by 'mysuperpassword'; flush privileges
  7. Now read this file, patch it to make compatible with MySQL and feed to MySQL: cat owncloud.sql | sed 's/CLOB/LONGTEXT/g' | sed '1,1d' | sed 's/BEGIN TRANSACTION/START TRANSACTION/g' | sed '1iUSE owncloud;' | sed 's/"/`/g' | mysql
  8. Update your ownCloud config file config/config.php, edit the database access information, keep a copy of the old file to rollback if necessary:
    'dbtype' => 'mysql',
    'dbname' => 'owncloud',
    'dbuser' => 'owncloud_user',
    'dbpassword' => 'mysuperpassword',
    'dbhost' => 'localhost',
    'dbtableprefix' => 'oc_',
  9. Check every table in the mysql database. Find all fields marked as primary keys of type integer and make them auto_increment.
  10. Start the web server: service httpd start
  11. Verify that all data is in place, you can see activity, deleted files etc
  12. Not sure if this is typical, but when I tried to access the page for the first time I’ve got “duplicate key in table oc_jobs” exception. I just ran mysql and changed the id of that record to 100+id, then the new record got inserted no problem.
  13. Restart ownCloud clients on all computers
  14. For couple days watch the data/owncloud.log file for possible errors

All done!

Leave a Reply