Upgrade from 10.0.3 to 11.0.1 fails with database error

[nblock]

Steps to reproduce

1. Download and unpack NC 11.0.1
2. Copy config/config.php and data folder to unpacked folder
3. occ upgrade

Expected behaviour

The upgrade should work

Actual behaviour

The upgrade fails during database migration step with the following error message:

[snipped]
Updating <theming> ...
Updated <theming> to 1.1.1
Update 3rd-party app: calendar
Update 3rd-party app: contacts
Update 3rd-party app: notes
Doctrine\DBAL\Exception\DriverException: An exception occurred while executing 'INSERT INTO "oc_appconfig" ("appid","configkey","configvalue") SELECT ?,?,?  WHERE NOT EXISTS (SELECT 1 FROM "oc_appcon
fig" WHERE "appid" = ? AND "configkey" = ?)' with params ["twofactor_backupcodes", "installed_version", "1.0.0", "twofactor_backupcodes", "installed_version"]:

SQLSTATE[HY000]: General error: 5 database is locked
Update failed
Maintenance mode is kept active
Reset log level

Server configuration

Operating system: Arch Linux

Web server: Nginx

Database: sqlite

PHP version: PHP 7.0.14 (cli) (built: Dec 7 2016 17:11:27) ( NTS )

Nextcloud version: 11.0.1

Updated from an older Nextcloud/ownCloud or fresh install: update from 10.0.3

Where did you install Nextcloud from: cli

List of activated apps:

Apps

Enabled:
  - activity: 2.4.1
  - admin_audit: 1.1.0
  - comments: 1.1.0
  - dav: 1.1.1
  - federatedfilesharing: 1.1.1
  - federation: 1.1.1
  - files: 1.6.1
  - files_pdfviewer: 1.0.1
  - files_sharing: 1.1.1
  - files_trashbin: 1.1.0
  - files_versions: 1.4.0
  - files_videoplayer: 1.0.0
  - firstrunwizard: 2.0
  - gallery: 16.0.0
  - lookup_server_connector: true
  - nextcloud_announcements: 1.0
  - notifications: 1.0.1
  - password_policy: 1.1.0
  - provisioning_api: 1.1.0
  - systemtags: 1.1.3
  - templateeditor: 0.2
  - theming: 1.1.1
  - twofactor_backupcodes: true
  - updatenotification: 1.1.1
  - workflowengine: true
Disabled:
  - calendar
  - contacts
  - encryption
  - external
  - files_accesscontrol
  - files_automatedtagging
  - files_external
  - files_retention
  - files_texteditor
  - logreader
  - notes
  - serverinfo
  - sharebymail
  - survey_client
  - user_external
  - user_ldap
  - user_saml

The content of config/config.php:

Config report

{
    "system": {
        "datadirectory": "\/path\/http\/nextcloud\/testing-only\/data",
        "dbtype": "sqlite",
        "version": "9.1.3.2",
        "installedat": "1329129724.251",
        "lastupdatedat": "1336147799.5594",
        "installed": true,
        "loglevel": 0,
        "instanceid": "5079251d41ae7",
        "maintenance": true,
        "theme": "",
        "memcache.local": "\\OC\\Memcache\\APCu",
        "trusted_domains": [
            "cloud.nblock.org"
        ],
        "secret": "***REMOVED SENSITIVE VALUE***",
        "mail_from_address": "owncloud",
        "mail_smtpmode": "php",
        "trashbin_retention_obligation": "auto",
        "mail_smtphost": "localhost",
        "mail_smtpport": "25",
        "mail_domain": "somedomain.tld",
        "appstore.experimental.enabled": true,
        "asset-pipeline.enabled": false,
        "updater.release.channel": "stable"
    }
}

Are you using external storage, if yes which one: no

Are you using encryption: no

Are you using an external user-backend, if yes which one: no

Client configuration

Browser: Firefox

Operating system: Linux

Logs

Nextcloud log (data/nextcloud.log)

Nextcloud log

``` {"reqId":"t8JUKlmOeQFHjHg2Y\/gW","remoteAddr":"","app":"core","message":"Exception: {\"Exception\":\"Doctrine\\\\DBAL\\\\Exception\\\\DriverException\",\"Message\":\"An exception occurred while executing 'INSERT INTO \\\"oc_appconfig\\\" (\\\"appid\\\",\\\"configkey\\\",\\\"configvalue\\\") SELECT ?,?,? WHERE NOT EXISTS (SELECT 1 FROM \\\"oc_appconfig\\\" WHERE \\\"appid\\\" = ? AND \\\"configkey\\\" = ?)' with params [\\\"twofactor_backupcodes\\\", \\\"installed_version\\\", \\\"1.0.0\\\", \\\"twofactor_backupcodes\\\", \\\"installed_version\\\"]:\\n\\nSQLSTATE[HY000]: General error: 5 database is locked\",\"Code\":0,\"Trace\":\"#0 \\\/path\\\/http\\\/nextcloud\\\/testing-only\\\/nextcloud\\\/3rdparty\\\/doctrine\\\/dbal\\\/lib\\\/Doctrine\\\/DBAL\\\/DBALException.php(128): Doctrine\\\\DBAL\\\\Driver\\\\AbstractSQLiteDriver->convertException('An exception oc...', Object(Doctrine\\\\DBAL\\\\Driver\\\\PDOException))\\n#1 \\\/path\\\/http\\\/nextcloud\\\/testing-only\\\/nextcloud\\\/3rdparty\\\/doctrine\\\/dbal\\\/lib\\\/Doctrine\\\/DBAL\\\/Connection.php(996): Doctrine\\\\DBAL\\\\DBALException::driverExceptionDuringQuery(Object(Doctrine\\\\DBAL\\\\Driver\\\\PDOSqlite\\\\Driver), Object(Doctrine\\\\DBAL\\\\Driver\\\\PDOException), 'INSERT INTO \\\"oc...', Array)\\n#2 \\\/path\\\/http\\\/nextcloud\\\/testing-only\\\/nextcloud\\\/lib\\\/private\\\/DB\\\/Connection.php(215): Doctrine\\\\DBAL\\\\Connection->executeUpdate('INSERT INTO \\\"oc...', Array, Array)\\n#3 \\\/path\\\/http\\\/nextcloud\\\/testing-only\\\/nextcloud\\\/lib\\\/private\\\/DB\\\/AdapterSqlite.php(86): OC\\\\DB\\\\Connection->executeUpdate('INSERT INTO \\\"oc...', Array)\\n#4 \\\/path\\\/http\\\/nextcloud\\\/testing-only\\\/nextcloud\\\/lib\\\/private\\\/DB\\\/Connection.php(253): OC\\\\DB\\\\AdapterSqlite->insertIfNotExist('*PREFIX*appconf...', Array, Array)\\n#5 \\\/path\\\/http\\\/nextcloud\\\/testing-only\\\/nextcloud\\\/lib\\\/private\\\/AppConfig.php(157): OC\\\\DB\\\\Connection->insertIfNotExist('*PREFIX*appconf...', Array, Array)\\n#6 \\\/path\\\/http\\\/nextcloud\\\/testing-only\\\/nextcloud\\\/lib\\\/private\\\/AllConfig.php(164): OC\\\\AppConfig->setValue('twofactor_backu...', 'installed_versi...', '1.0.0')\\n#7 \\\/path\\\/http\\\/nextcloud\\\/testing-only\\\/nextcloud\\\/lib\\\/private\\\/Installer.php(503): OC\\\\AllConfig->setAppValue('twofactor_backu...', 'installed_versi...', '1.0.0')\\n#8 \\\/path\\\/http\\\/nextcloud\\\/testing-only\\\/nextcloud\\\/lib\\\/private\\\/Installer.php(445): OC\\\\Installer::installShippedApp('twofactor_backu...')\\n#9 \\\/path\\\/http\\\/nextcloud\\\/testing-only\\\/nextcloud\\\/lib\\\/private\\\/Updater.php(252): OC\\\\Installer::installShippedApps(true)\\n#10 \\\/path\\\/http\\\/nextcloud\\\/testing-only\\\/nextcloud\\\/lib\\\/private\\\/Updater.php(124): OC\\\\Updater->doUpgrade('11.0.1.2', '9.1.3.2')\\n#11 \\\/path\\\/http\\\/nextcloud\\\/testing-only\\\/nextcloud\\\/core\\\/Command\\\/Upgrade.php(261): OC\\\\Updater->upgrade()\\n#12 \\\/path\\\/http\\\/nextcloud\\\/testing-only\\\/nextcloud\\\/3rdparty\\\/symfony\\\/console\\\/Command\\\/Command.php(256): OC\\\\Core\\\\Command\\\\Upgrade->execute(Object(Symfony\\\\Component\\\\Console\\\\Input\\\\ArgvInput), Object(Symfony\\\\Component\\\\Console\\\\Output\\\\ConsoleOutput))\\n#13 \\\/path\\\/http\\\/nextcloud\\\/testing-only\\\/nextcloud\\\/3rdparty\\\/symfony\\\/console\\\/Application.php(818): Symfony\\\\Component\\\\Console\\\\Command\\\\Command->run(Object(Symfony\\\\Component\\\\Console\\\\Input\\\\ArgvInput), Object(Symfony\\\\Component\\\\Console\\\\Output\\\\ConsoleOutput))\\n#14 \\\/path\\\/http\\\/nextcloud\\\/testing-only\\\/nextcloud\\\/3rdparty\\\/symfony\\\/console\\\/Application.php(186): Symfony\\\\Component\\\\Console\\\\Application->doRunCommand(Object(OC\\\\Core\\\\Command\\\\Upgrade), Object(Symfony\\\\Component\\\\Console\\\\Input\\\\ArgvInput), Object(Symfony\\\\Componet\\\\Console\\\\Output\\\\ConsoleOutput))\\n#15 \\\/path\\\/http\\\/nextcloud\\\/testing-only\\\/nextcloud\\\/3rdparty\\\/symfony\\\/console\\\/Application.php(117): Symfony\\\\Component\\\\Console\\\\Application->doRun(Object(Symfony\\\\Component\\\\Console\\\\Input\\\\ArgvInput), Object(Symfony\\\\Component\\\\Console\\\\Output\\\\ConsoleOutput))\\n#16 \\\/path\\\/http\\\/nextcloud\\\/testing-only\\\/nextcloud\\\/lib\\\/private\\\/Console\\\/Application.php(169): Symfony\\\\Component\\\\Console\\\\Application->run(Object(Symfony\\\\Component\\\\Console\\\\Input\\\\ArgvInput), Object(Symfony\\\\Component\\\\Console\\\\Output\\\\ConsoleOutput))\\n#17 \\\/path\\\/http\\\/nextcloud\\\/testing-only\\\/nextcloud\\\/console.php(90): OC\\\\Console\\\\Application->run()\\n#18 \\\/path\\\/http\\\/nextcloud\\\/testing-only\\\/nextcloud\\\/occ(11): require_once('\\\/path\\\/http\\\/nextc...')\\n#19 {main}\",\"File\":\"\\\/path\\\/http\\\/nextcloud\\\/testing-only\\\/nextcloud\\\/3rdparty\\\/doctrine\\\/dbal\\\/lib\\\/Doctrine\\\/DBAL\\\/Driver\\\/AbstractSQLiteDriver.php\",\"Line\":85}","level":3,"time":"2017-01-24T17:19:30+00:00","method":"--","url":"--","user":"--","version":"9.1.3.2"} {"reqId":"t8JUKlmOeQFHjHg2Y\/gW","remoteAddr":"","app":"updater","message":"\\OC\\Updater::failure: Doctrine\\DBAL\\Exception\\DriverException: An exception occurred while executing 'INSERT INTO \"oc_appconfig\" (\"appid\",\"configkey\",\"configvalue\") SELECT ?,?,? WHERE NOT EXISTS (SELECT 1 FROM \"oc_appconfig\" WHERE \"appid\" = ? AND \"configkey\" = ?)' with params [\"twofactor_backupcodes\", \"installed_version\", \"1.0.0\", \"twofactor_backupcodes\", \"installed_version\"]:\n\nSQLSTATE[HY000]: General error: 5 database is locked","level":3,"time":"2017-01-24T17:19:30+00:00","method":"--","url":"--","user":"--","version":"9.1.3.2"} ```

[MorrisJobke]

SQLSTATE[HY000]: General error: 5 database is locked

Do you by any chance know what went on during the upgrade? Was there high IO load? If you have a bigger instance we highly recommend to switch to MySQL, because SQLite locks down the whole file and doesn't allow concurrent access to the database.

During update we usually only run exactly one connection to the database. So there shouldn't be any other conenction (that locks the database) possible. Maybe you tried to open the database manually during the update. Could this be the case?

[nblock]

Do you by any chance know what went on during the upgrade? Was there high IO load?

The instance was not online during the upgrade process. I copied data and config/config.php into a fresh installation which was not reachable from users. I started the upgrade using occ from there.

If you have a bigger instance we highly recommend to switch to MySQL, because SQLite locks down the whole file and doesn't allow concurrent access to the database.

I'd love too, see Issue #173.

During update we usually only run exactly one connection to the database. So there shouldn't be any other connection (that locks the database) possible.

I tried the upgrade several times with the last few tries being isolated from any user requests.

Maybe you tried to open the database manually during the update. Could this be the case?

No

[MorrisJobke]

I then have no idea why the DB should be locked -.-

[c-MM]

I have the exact same issue upgrading from 10.0.4 to 11.0.2. System is debian jessie also with sqlite. It's a very small installation with just one user (besides the admin account).

[MorrisJobke]

Without further details we sadly can't do much here. Just retry and check if you can find more information why it was locked at all. I will close this for now and we will reopen if more details are available.

[martonw]

I ran into the same issue. After some investigation, it seems that the upgrade routine tries to connect (in case of SQLite, this means opening the file) two times during the procedure. This is relatively easy to verify by printing a single line to the console output when the DB connect() function is called (ie within the 3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOSqlite/Driver.php file).

Although the second connect() will succeed, the next SQL command will fail and report the above General error: 5 database is locked error message. So I think the problem is that the upgrade script shall not open 2 DB connections at the same time, as this will not work for the SQLite driver.

After some tracing it seems that the first connection is made at the Init() call, right at the start of the script's execution - while the second one is made when the schema upgrade is performed, via the MDB2SchemaManager class.
As a workaround, I have made the following change in the /opt/nextcloud/lib/private/DB/MDB2SchemaManager.php file:

        /**
         * @param \Doctrine\DBAL\Schema\Schema|\Doctrine\DBAL\Schema\SchemaDiff $schema
         * @return bool
         */
        private function executeSchemaChange($schema) {
                $this->conn->beginTransaction();
                foreach ($schema->toSql($this->conn->getDatabasePlatform()) as $sql) {
                        $this->conn->query($sql);
                }
                $this->conn->commit();

                if ($this->conn->getDatabasePlatform() instanceof SqlitePlatform) {
                        // Commenting out the following 2 lines 
                        /*$this->conn->close();
                        $this->conn->connect();*/
                }
                return true;
        }

This change let the script move on and successfully perform the update. But I am sure this is not the proper way to fix the problem anyway.

[c-MM]

Thanks a lot! This worked for me!

[ytjohn]

Just had to do this today ugrading from 12 to 13.