Migrate Owncloud from PostgreSQL to MySQL

I am running a self-hosted owncloud instance for more than a year now. Back in August I decided to setup a new Ubuntu 14.04 server at home and tried to run owncloud with a PostgreSQL backend - which turned out to be a bad decision.

Quite often I noticed the following error in my owncloud.log file:

{"app":"index","message":"Doctrine\\DBAL\\DBALException: An exception occurred while executing 'SELECT \"fileid\", \"storage\", \"path\", \"parent\", \"name\", \"mimetype\", \"mimepart\", \"size\", \"mtime\",\n\t\t\t\t\t   \"storage_mtime\", \"encrypted\", \"unencrypted_size\", \"etag\", \"permissions\"\n\t\t\t\tFROM \"oc_filecache\" WHERE \"storage\" = ? AND \"path_hash\" = ?':\n\nSQLSTATE[25P02]: In failed sql transaction: 7 ERROR:  current transaction is aborted, commands ignored until end of transaction block","level":4,"time":"2014-11-26T15:28:16+00:00"}
{"app":"PHP","message":"Cannot modify header information - headers already sent at \/var\/www\/owncloud\/lib\/private\/response.php#83","level":3,"time":"2014-11-26T15:28:16+00:00"}

Later on I found out that it occurred when owncloud tried to scan and index the files in the data folder. The way I got to know this is, I noticed that some of the files I uploaded were not displayed in owncloud but were present on the filesystem. So when I ran

sudo -u www-data /usr/bin/php5 /var/www/owncloud/occ files:scan ahe -v

the command also ended in this error message:

  [Doctrine\DBAL\DBALException]
  An exception occurred while executing 'SELECT "fileid", "storage", "path", "parent", "name", "mimetype", "mimepart", "size", "mtime",
  					   "storage_mtime", "encrypted", "unencrypted_size", "etag", "permissions"
  				FROM "oc_filecache" WHERE "storage" = ? AND "path_hash" = ?':

  SQLSTATE[25P02]: In failed sql transaction: 7 ERROR:  current transaction is aborted, commands ignored until end of transaction block



Exception trace:
 () at /var/www/owncloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php:47
 Doctrine\DBAL\DBALException::driverExceptionDuringQuery() at /var/www/owncloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Statement.php:140
 Doctrine\DBAL\Statement->execute() at /var/www/owncloud/lib/private/db/statementwrapper.php:63
 OC_DB_StatementWrapper->execute() at /var/www/owncloud/lib/private/db.php:235
 OC_DB::executeAudited() at /var/www/owncloud/lib/private/files/cache/cache.php:128
 OC\Files\Cache\Cache->get() at /var/www/owncloud/lib/private/files/cache/homecache.php:62
 OC\Files\Cache\HomeCache->get() at /var/www/owncloud/lib/private/files/cache/cache.php:367
 OC\Files\Cache\Cache->remove() at /var/www/owncloud/lib/private/files/cache/scanner.php:163
 OC\Files\Cache\Scanner->removeFromCache() at /var/www/owncloud/lib/private/files/cache/scanner.php:272
 OC\Files\Cache\Scanner->scanChildren() at /var/www/owncloud/lib/private/files/cache/scanner.php:284
 OC\Files\Cache\Scanner->scanChildren() at /var/www/owncloud/lib/private/files/cache/scanner.php:284
 OC\Files\Cache\Scanner->scanChildren() at /var/www/owncloud/lib/private/files/cache/scanner.php:284
 OC\Files\Cache\Scanner->scanChildren() at /var/www/owncloud/lib/private/files/cache/scanner.php:207
 OC\Files\Cache\Scanner->scan() at /var/www/owncloud/lib/private/files/utils/scanner.php:125
 OC\Files\Utils\Scanner->scan() at /var/www/owncloud/apps/files/command/scan.php:57
 OCA\Files\Command\Scan->scanFiles() at /var/www/owncloud/apps/files/command/scan.php:81
 OCA\Files\Command\Scan->execute() at /var/www/owncloud/3rdparty/symfony/console/Symfony/Component/Console/Command/Command.php:244
 Symfony\Component\Console\Command\Command->run() at /var/www/owncloud/3rdparty/symfony/console/Symfony/Component/Console/Application.php:897
 Symfony\Component\Console\Application->doRunCommand() at /var/www/owncloud/3rdparty/symfony/console/Symfony/Component/Console/Application.php:191
 Symfony\Component\Console\Application->doRun() at /var/www/owncloud/3rdparty/symfony/console/Symfony/Component/Console/Application.php:121
 Symfony\Component\Console\Application->run() at /var/www/owncloud/console.php:43
 require_once() at /var/www/owncloud/occ:11





  [PDOException]
  SQLSTATE[25P02]: In failed sql transaction: 7 ERROR:  current transaction is aborted, commands ignored until end of transaction block



Exception trace:
 () at /var/www/owncloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Statement.php:138
 PDOStatement->execute() at /var/www/owncloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Statement.php:138
 Doctrine\DBAL\Statement->execute() at /var/www/owncloud/lib/private/db/statementwrapper.php:63
 OC_DB_StatementWrapper->execute() at /var/www/owncloud/lib/private/db.php:235
 OC_DB::executeAudited() at /var/www/owncloud/lib/private/files/cache/cache.php:128
 OC\Files\Cache\Cache->get() at /var/www/owncloud/lib/private/files/cache/homecache.php:62
 OC\Files\Cache\HomeCache->get() at /var/www/owncloud/lib/private/files/cache/cache.php:367
 OC\Files\Cache\Cache->remove() at /var/www/owncloud/lib/private/files/cache/scanner.php:163
 OC\Files\Cache\Scanner->removeFromCache() at /var/www/owncloud/lib/private/files/cache/scanner.php:272
 OC\Files\Cache\Scanner->scanChildren() at /var/www/owncloud/lib/private/files/cache/scanner.php:284
 OC\Files\Cache\Scanner->scanChildren() at /var/www/owncloud/lib/private/files/cache/scanner.php:284
 OC\Files\Cache\Scanner->scanChildren() at /var/www/owncloud/lib/private/files/cache/scanner.php:284
 OC\Files\Cache\Scanner->scanChildren() at /var/www/owncloud/lib/private/files/cache/scanner.php:207
 OC\Files\Cache\Scanner->scan() at /var/www/owncloud/lib/private/files/utils/scanner.php:125
 OC\Files\Utils\Scanner->scan() at /var/www/owncloud/apps/files/command/scan.php:57
 OCA\Files\Command\Scan->scanFiles() at /var/www/owncloud/apps/files/command/scan.php:81
 OCA\Files\Command\Scan->execute() at /var/www/owncloud/3rdparty/symfony/console/Symfony/Component/Console/Command/Command.php:244
 Symfony\Component\Console\Command\Command->run() at /var/www/owncloud/3rdparty/symfony/console/Symfony/Component/Console/Application.php:897
 Symfony\Component\Console\Application->doRunCommand() at /var/www/owncloud/3rdparty/symfony/console/Symfony/Component/Console/Application.php:191
 Symfony\Component\Console\Application->doRun() at /var/www/owncloud/3rdparty/symfony/console/Symfony/Component/Console/Application.php:121
 Symfony\Component\Console\Application->run() at /var/www/owncloud/console.php:43
 require_once() at /var/www/owncloud/occ:11


files:scan [--all] [user_id1] ... [user_idN]

As this is a PostgreSQL error, I searched the owncloud forums for other people’s experience with owncloud + PostgreSQL. Unfortunately it seems that owncloud doesn’t play too nicely with PostgreSQL. :(

Looking back I had an owncloud setup with MySQL before and cannot remember having had any issues with it. So I decided to take the easy way and find out how to migrate my owncloud data from PostgreSQL to MySQL. This was when I stumbled upon this great little command in the owncloud console.php: db:convert-type

This little gem did the trick for me so that after executing the following command, my data had automatically been migrated over to MySQL and the owncloud configuration changed accordingly. As it’s been so nice to me I thought, I leave it here for others coming up with the same question:

sudo -u www-data /usr/bin/php5 /var/www/owncloud/console.php db:convert-type --all-apps mysql owncloud localhost owncloud