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 -vthe 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