Exception occurred while executing 'PRAGMA journal_mode = WAL'

TrapperMD · July 31, 2016, 1:52pm

Dear all,

After the last OC 8.1.6 update I have an issue with accessing the SQLite3 database.
This is the follow-up of topic "1091 Can’t DROP ‘PRIMARY’ with upgrade 8.1.6->8.1.9".

Linked to GitHub issue #25663

Steps to reproduce
1. There had been an automatic update via http://download.opensuse.org/repositories/isv:/ownCloud:/community/xUbuntu_14.04/ .
2. In the same run of Ubuntu upgrade with the automatic owncloud update also mysql and php might have been updated.
3. I do not think that the database has been corrupted, as I get the same failure scenario with owncloud.db backups which had been saved one month earlier (when owncloud was running fine). But somehow the connection between OC and the database could be lost.
4. I checked /etc/php/php5.6/mods-available/pdo_sqlite.ini and sqlite3.ini, and both point to valid copies of their corresponding .so-files. Also, both are linked/activated in apache2/conf.d.

Expected behaviour
OC starting and accessing its database.

Actual behaviour
1. OC wants to update himself without me having initiated an update. Update fails with error message:

Interner Serverfehler

Der Server hat einen internen Fehler und konnte Ihre Anfrage nicht vervollständigen.

Bitte wende Dich an den Serveradministrator, sollte dieser Fehler mehrfach auftreten, und füge Deiner Anfrage die unten stehenden technischen Details bei.

Weitere Details können im Serverprotokoll gefunden werden.

Technische Details

    Entfernte Adresse: 192.168.1.22
    Anforderungskennung: 6RVAhYb8UueVP44tC2A8
    Typ: Doctrine\DBAL\DBALException
    Code: 0
    Nachricht: Failed to connect to the database: An exception occurred while executing 'PRAGMA journal_mode = WAL': SQLSTATE[HY000]: General error: 11 database disk image is malformed
    Datei: /var/www/owncloud2/lib/private/db/connection.php
    Zeile: 50


Spur

#0 /var/www/owncloud2/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(973): OC\DB\Connection->connect()
#1 /var/www/owncloud2/lib/private/db/connection.php(151): Doctrine\DBAL\Connection->executeUpdate('PRAGMA read_unc...', Array, Array)
#2 /var/www/owncloud2/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(621): OC\DB\Connection->executeUpdate('PRAGMA read_unc...')
#3 /var/www/owncloud2/lib/private/db/connection.php(83): Doctrine\DBAL\Connection->setTransactionIsolation(2)
#4 /var/www/owncloud2/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/DriverManager.php(172): OC\DB\Connection->__construct(Array, Object(Doctrine\DBAL\Driver\PDOSqlite\Driver), Object(Doctrine\DBAL\Configuration), Object(Doctrine\Common\EventManager))
#5 /var/www/owncloud2/lib/private/db/connectionfactory.php(124): Doctrine\DBAL\DriverManager::getConnection(Array, Object(Doctrine\DBAL\Configuration), Object(Doctrine\Common\EventManager))
#6 /var/www/owncloud2/lib/private/server.php(302): OC\DB\ConnectionFactory->getConnection('sqlite3', Array)
#7 /var/www/owncloud2/3rdparty/pimple/pimple/src/Pimple/Container.php(112): OC\Server->OC\{closure}(Object(OC\Server))
#8 /var/www/owncloud2/lib/private/appframework/utility/simplecontainer.php(96): Pimple\Container->offsetGet('DatabaseConnect...')
#9 /var/www/owncloud2/lib/private/server.php(697): OC\AppFramework\Utility\SimpleContainer->query('DatabaseConnect...')
#10 /var/www/owncloud2/lib/private/db.php(42): OC\Server->getDatabaseConnection()
#11 /var/www/owncloud2/lib/private/server.php(209): OC_DB::getConnection()
#12 /var/www/owncloud2/3rdparty/pimple/pimple/src/Pimple/Container.php(112): OC\Server->OC\{closure}(Object(OC\Server))
#13 /var/www/owncloud2/lib/private/appframework/utility/simplecontainer.php(96): Pimple\Container->offsetGet('AppConfig')
#14 /var/www/owncloud2/lib/private/server.php(643): OC\AppFramework\Utility\SimpleContainer->query('AppConfig')
#15 /var/www/owncloud2/lib/private/server.php(344): OC\Server->getAppConfig()
#16 /var/www/owncloud2/3rdparty/pimple/pimple/src/Pimple/Container.php(112): OC\Server->OC\{closure}(Object(OC\Server))
#17 /var/www/owncloud2/lib/private/appframework/utility/simplecontainer.php(96): Pimple\Container->offsetGet('AppManager')
#18 /var/www/owncloud2/lib/private/server.php(863): OC\AppFramework\Utility\SimpleContainer->query('AppManager')
#19 /var/www/owncloud2/lib/private/app.php(238): OC\Server->getAppManager()
#20 /var/www/owncloud2/lib/private/app.php(93): OC_App::getEnabledApps()
#21 /var/www/owncloud2/lib/base.php(592): OC_App::loadApps(Array)
#22 /var/www/owncloud2/lib/base.php(1125): OC::init()
#23 /var/www/owncloud2/index.php(46): require_once('/var/www/ownclo...')
#24 {main}

OC cannot access its database, e.g. occ -V gives ...

Interner Serverfehler
Der Server hat einen internen Fehler und konnte Ihre Anfrage nicht vervollständigen.
Bitte wende Dich an den Serveradministrator, sollte dieser Fehler mehrfach auftreten, und füge Deiner Anfrage die unten stehenden technischen Details bei.
Weitere Details können im Serverprotokoll gefunden werden.
Technische Details
Entfernte Adresse: 192.168.1.22
Anforderungskennung: 6RVAhYb8UueVP44tC2A8
Typ: Doctrine\DBAL\DBALException
Code: 0
Nachricht: Failed to connect to the database: An exception occurred while executing 'PRAGMA journal_mode = WAL': SQLSTATE[HY000]: General error: 11 database disk image is malformed
Datei: /var/www/owncloud2/lib/private/db/connection.php
Zeile: 50
Spur

0 /var/www/owncloud2/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(973): OC\DB\Connection->connect()

1 /var/www/owncloud2/lib/private/db/connection.php(151): Doctrine\DBAL\Connection->executeUpdate('PRAGMA read_unc...', Array, Array)

2 /var/www/owncloud2/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(621): OC\DB\Connection->executeUpdate('PRAGMA read_unc...')

3 /var/www/owncloud2/lib/private/db/connection.php(83): Doctrine\DBAL\Connection->setTransactionIsolation(2)

4 /var/www/owncloud2/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/DriverManager.php(172): OC\DB\Connection->__construct(Array, Object(Doctrine\DBAL\Driver\PDOSqlite\Driver), Object(Doctrine\DBAL\Configuration), Object(Doctrine\Common\EventManager))

5 /var/www/owncloud2/lib/private/db/connectionfactory.php(124): Doctrine\DBAL\DriverManager::getConnection(Array, Object(Doctrine\DBAL\Configuration), Object(Doctrine\Common\EventManager))

6 /var/www/owncloud2/lib/private/server.php(302): OC\DB\ConnectionFactory->getConnection('sqlite3', Array)

7 /var/www/owncloud2/3rdparty/pimple/pimple/src/Pimple/Container.php(112): OC\Server->OC{closure}(Object(OC\Server))

8 /var/www/owncloud2/lib/private/appframework/utility/simplecontainer.php(96): Pimple\Container->offsetGet('DatabaseConnect...')

9 /var/www/owncloud2/lib/private/server.php(697): OC\AppFramework\Utility\SimpleContainer->query('DatabaseConnect...')

10 /var/www/owncloud2/lib/private/db.php(42): OC\Server->getDatabaseConnection()

11 /var/www/owncloud2/lib/private/server.php(209): OC_DB::getConnection()

12 /var/www/owncloud2/3rdparty/pimple/pimple/src/Pimple/Container.php(112): OC\Server->OC{closure}(Object(OC\Server))

13 /var/www/owncloud2/lib/private/appframework/utility/simplecontainer.php(96): Pimple\Container->offsetGet('AppConfig')

14 /var/www/owncloud2/lib/private/server.php(643): OC\AppFramework\Utility\SimpleContainer->query('AppConfig')

15 /var/www/owncloud2/lib/private/server.php(344): OC\Server->getAppConfig()

16 /var/www/owncloud2/3rdparty/pimple/pimple/src/Pimple/Container.php(112): OC\Server->OC{closure}(Object(OC\Server))

17 /var/www/owncloud2/lib/private/appframework/utility/simplecontainer.php(96): Pimple\Container->offsetGet('AppManager')

18 /var/www/owncloud2/lib/private/server.php(863): OC\AppFramework\Utility\SimpleContainer->query('AppManager')

19 /var/www/owncloud2/lib/private/app.php(238): OC\Server->getAppManager()

20 /var/www/owncloud2/lib/private/app.php(93): OC_App::getEnabledApps()

21 /var/www/owncloud2/lib/base.php(592): OC_App::loadApps(Array)

22 /var/www/owncloud2/lib/base.php(1125): OC::init()

23 /var/www/owncloud2/index.php(46): require_once('/var/www/ownclo...')

24 {main}

Server configuration
Operating system: Ubuntu 14.04.1
Web server: apache2
Database: Mysql 5.5.50, SQLite 3.8.2
PHP version: 5.6
ownCloud version: 8.1.6
Updated from an older ownCloud or fresh install: continuously updated since v6 via SUSE repos http://download.opensuse.org/repositories/isv:/ownCloud:/community/xUbuntu_14.04/

config.php:

<?php
$CONFIG = array (
'instanceid' => 'oc3a41047555',
'passwordsalt' => '3ee12637fa36136a5e3cbe60433a1f',
'datadirectory' => '/var/www/owncloud/data',
'dbtype' => 'sqlite3',
'dbname' => 'ownclouddb',
'dbhost' => 'localhost',
'version' => '8.1.6.2',
'installed' => true,
'forcessl' => true,
'theme' => '',
'maintenance' => false,
'trusted_domains' =>
array (
0 => '192.168.1.24',
1 => 'localhost',
2 => 'home.mydomain.de',
),
'forceSSLforSubdomains' => true,
'appcodechecker' => false,
'xframe_restriction' => false,
'cache_path' => '',
'quota_include_external_storage' => false,
'filesystem_check_changes' => 0,
'appstore.experimental.enabled' => false,
'updatechecker' => false,
);

ownCloud log (data/owncloud.log)

"reqId":"cEUEF\/se7gNzvxRzJsoP","remoteAddr":"212.27.185.253","app":"index","message":"Exception: {\"Exception\":\"Doctrine\$
"reqId":"vjQFGf15JZlPq+dVD1yT","remoteAddr":"212.27.185.253","app":"index","message":"Exception: {\"Exception\":\"Doctrine\$
{"reqId":"SithAJT107BsSi3u6wRH","remoteAddr":"192.168.1.22","app":"index","message":"Exception: {\"Exception\":\"Doctrine\\$

Best regards
Chris

anon30676603 · July 31, 2016, 1:56pm

Hi,

its highly advised to not use sqlite (even with one single user, especially on productive systems). Before doing the upgrade i recommend to convert to either MySQL or PostgreSQL by following the documentation available here:

https://doc.owncloud.org/server/9.0/admin_manual/configuration_database/db_conversion.html

anon30676603 · July 31, 2016, 2:07pm

Hi,

if you're posting an issue at the bugtracker please also link it in here to avoid that doubled support at two different places are given:

Still i would suggest to switch to MySQL/PostgreSQL instead of using the crappy sqlite.

TrapperMD · July 31, 2016, 3:03pm

Hi,

Reg. GitHub Link, see 2nd paragraph of my posting, please. I was not able to link acc. to an error mesaage by the system mentioning a limitation for new users. If you want me to link, please allow.

Reg. MySQL preferred against SQLite, I totally agree. And I would love to do so. But as obviously occ command does not work at all, I can also not convert the database from SQLite format to MySQL. I tried and failed.

I am afraid that I need to solve this issue before being able to convert.

Best
Chris

anon30676603 · July 31, 2016, 4:28pm

Sorry, relates wasn't clear that you had created to an issue or just referring to an old issue you had found. "Relates" sound more like the latter.

TrapperMD · August 3, 2016, 2:43pm

OK, solved.

I was able to restore a database update from a time point where it was already compatible with 8.1.x and not yet corrupted. It is such a pleasure having tons of backups available.

Upgrade ran through, OC is up and running, next step will be conversion of database to MySQL.

Best
Chris

Exception occurred while executing 'PRAGMA journal_mode = WAL'

0 /var/www/owncloud2/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(973): OC\DB\Connection->connect()

1 /var/www/owncloud2/lib/private/db/connection.php(151): Doctrine\DBAL\Connection->executeUpdate('PRAGMA read_unc...', Array, Array)

2 /var/www/owncloud2/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(621): OC\DB\Connection->executeUpdate('PRAGMA read_unc...')

3 /var/www/owncloud2/lib/private/db/connection.php(83): Doctrine\DBAL\Connection->setTransactionIsolation(2)

4 /var/www/owncloud2/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/DriverManager.php(172): OC\DB\Connection->__construct(Array, Object(Doctrine\DBAL\Driver\PDOSqlite\Driver), Object(Doctrine\DBAL\Configuration), Object(Doctrine\Common\EventManager))

5 /var/www/owncloud2/lib/private/db/connectionfactory.php(124): Doctrine\DBAL\DriverManager::getConnection(Array, Object(Doctrine\DBAL\Configuration), Object(Doctrine\Common\EventManager))

6 /var/www/owncloud2/lib/private/server.php(302): OC\DB\ConnectionFactory->getConnection('sqlite3', Array)

7 /var/www/owncloud2/3rdparty/pimple/pimple/src/Pimple/Container.php(112): OC\Server->OC{closure}(Object(OC\Server))

8 /var/www/owncloud2/lib/private/appframework/utility/simplecontainer.php(96): Pimple\Container->offsetGet('DatabaseConnect...')

9 /var/www/owncloud2/lib/private/server.php(697): OC\AppFramework\Utility\SimpleContainer->query('DatabaseConnect...')

10 /var/www/owncloud2/lib/private/db.php(42): OC\Server->getDatabaseConnection()

11 /var/www/owncloud2/lib/private/server.php(209): OC_DB::getConnection()

12 /var/www/owncloud2/3rdparty/pimple/pimple/src/Pimple/Container.php(112): OC\Server->OC{closure}(Object(OC\Server))

13 /var/www/owncloud2/lib/private/appframework/utility/simplecontainer.php(96): Pimple\Container->offsetGet('AppConfig')

14 /var/www/owncloud2/lib/private/server.php(643): OC\AppFramework\Utility\SimpleContainer->query('AppConfig')

15 /var/www/owncloud2/lib/private/server.php(344): OC\Server->getAppConfig()

16 /var/www/owncloud2/3rdparty/pimple/pimple/src/Pimple/Container.php(112): OC\Server->OC{closure}(Object(OC\Server))

17 /var/www/owncloud2/lib/private/appframework/utility/simplecontainer.php(96): Pimple\Container->offsetGet('AppManager')

18 /var/www/owncloud2/lib/private/server.php(863): OC\AppFramework\Utility\SimpleContainer->query('AppManager')

19 /var/www/owncloud2/lib/private/app.php(238): OC\Server->getAppManager()

20 /var/www/owncloud2/lib/private/app.php(93): OC_App::getEnabledApps()

21 /var/www/owncloud2/lib/base.php(592): OC_App::loadApps(Array)

22 /var/www/owncloud2/lib/base.php(1125): OC::init()

23 /var/www/owncloud2/index.php(46): require_once('/var/www/ownclo...')

24 {main}