Update from 9.1.6 to 9.1.7 failing with postgresql DB

[ege1]

Steps to reproduce

1. Have 9.1.6 running with postgresql DB
2. install 9.1.7
3. Start upgrade script (sudo -u www-data php occ upgrade)

Expected behaviour

Upgrade process should start and finish without error

Actual behaviour

Error at schema update check:

sudo -u www-data php occ upgrade
ownCloud or one of the apps require upgrade - only a limited number of commands are available
You may use your browser or the occ upgrade command to do the upgrade
Set log level to debug
Checking whether the database schema can be updated (this can take a long time depending on the database size)
 oc_appconfig
  1/24 [=>--------------------------]   4%OC\DB\MigrationException: An exception occurred while executing 'SELECT min_value, increment_by FROM "oc_activity_activity_id_seq"':

SQLSTATE[42703]: Undefined column: 7 ERROR:  column "min_value" does not exist
LINE 1: SELECT min_value, increment_by FROM "oc_activity_activity_id...
               ^
Update failed
Maintenance mode is kept active
Reset log level

Server configuration

Operating system:
Debian GNU/Linux 8

Web server:
nginx

Database:
PostgreSQL 10.1

PHP version:
5.6.30-0+deb8u1

ownCloud version: (see ownCloud admin page)
9.1.7

Updated from an older ownCloud or fresh install:
updated
Version before failed upgrade, 9.1.6

Where did you install ownCloud from:
owncloud.org tarball

Signing status (ownCloud 9.0 and above):
Integrity checker has been disabled. Integrity cannot be verified.

The content of config/config.php:

    "system": {
        "instanceid": "oc2b96fb5632",
        "passwordsalt": "***REMOVED SENSITIVE VALUE***",
        "trusted_domains": [
            "owncloud.geiger-online.ch",
            "oc.geiger-online.ch"
        ],
        "datadirectory": "\/var\/www\/owncloud_data",
        "dbtype": "pgsql",
        "version": "9.1.6.2",
        "dbname": "owncloud",
        "dbhost": "192.168.122.1",
        "dbtableprefix": "oc_",
        "log_type": "syslog",
        "log_query": false,
        "loglevel": 0,
        "logfile": "",
        "dbuser": "***REMOVED SENSITIVE VALUE***",
        "dbpassword": "***REMOVED SENSITIVE VALUE***",
        "installed": true,
        "forcessl": true,
        "mail_smtpmode": "sendmail",
        "mail_from_address": "owncloud",
        "mail_domain": "geiger-online.ch",
        "theme": "",
        "maintenance": false,
        "secret": "***REMOVED SENSITIVE VALUE***",
        "forceSSLforSubdomains": true,
        "trashbin_retention_obligation": "auto",
        "filesystem_check_changes": 0,
        "appstore.experimental.enabled": false
    }
}

List of activated apps:

Enabled:
  - activity: 2.3.2
  - bookmarks: 0.9.0
  - calendar: 1.4.1
  - comments: 0.3.0
  - configreport: 0.1.1
  - contacts: 1.5.1
  - dav: 0.2.7
  - documents: 0.13.1
  - federatedfilesharing: 0.3.0
  - federation: 0.1.0
  - files: 1.5.1
  - files_pdfviewer: 0.8.1
  - files_sharing: 0.10.0
  - files_texteditor: 2.1
  - files_trashbin: 0.9.0
  - files_versions: 1.3.0
  - files_videoplayer: 0.9.8
  - firstrunwizard: 1.1
  - gallery: 15.0.0
  - mail: 0.6.0
  - notifications: 0.3.0
  - provisioning_api: 0.5.0
  - systemtags: 0.3.0
  - tasks: 0.9.4
  - templateeditor: 0.1
  - updatenotification: 0.2.1
Disabled:
  - encryption
  - external
  - files_antivirus
  - files_external
  - user_external
  - user_ldap

Are you using external storage, if yes which one: local/smb/sftp/...
no
Are you using encryption: yes/no
no
Are you using an external user-backend, if yes which one: LDAP/ActiveDirectory/Webdav/...
no

Logs

Web server error log

ownCloud log (data/owncloud.log)

Dec 30 21:24:21 web ownCloud[19299]: {core} starting upgrade from 9.1.6.2 to 9.1.7.2
Dec 30 21:24:21 web ownCloud[19299]: {PHP} file_put_contents(/var/www/owncloud_data/.htaccess): failed to open stream: Permission denied at /var/www/owncloud/lib/private/Setup.php#502
Dec 30 21:24:22 web ownCloud[19299]: {core} Simulated database structure update failed (exception 'OC\DB\MigrationException' with message 'An exception occurred while executing 'SELECT min_value, increment_by FR
OM "oc_activity_activity_id_seq"':#012#012SQLSTATE[42703]: Undefined column: 7 ERROR:  column "min_value" does not exist#012LINE 1: SELECT min_value, increment_by FROM "oc_activity_activity_id...#012
   ^' in /var/www/owncloud/lib/private/DB/Migrator.php:169#012Stack trace:#012#0 /var/www/owncloud/lib/private/DB/Migrator.php(127): OC\DB\Migrator->checkTableMigrate(Object(Doctrine\DBAL\Schema\Table))#012#1 /v
ar/www/owncloud/lib/private/DB/MDB2SchemaManager.php(130): OC\DB\Migrator->checkMigrate(Object(Doctrine\DBAL\Schema\Schema))#012#2 /var/www/owncloud/lib/private/legacy/db.php(202): OC\DB\MDB2SchemaManager->simul
ateUpdateDbFromStructure('/var/www/ownclo...')#012#3 /var/www/owncloud/lib/private/Updater.php(288): OC_DB::simulateUpdateDbFromStructure('/var/www/ownclo...')#012#4 /var/www/owncloud/lib/private/Updater.php(232
): OC\Updater->checkCoreUpgrade()#012#5 /var/www/owncloud/lib/private/Updater.php(150): OC\Updater->doUpgrade('9.1.7.2', '9.1.6.2')#012#6 /var/www/owncloud/core/Command/Upgrade.php(290): OC\Updater->upgrade()#01
2#7 /var/www/owncloud/3rdparty/symfony/console/Command/Command.php(259): OC\Core\Command\Upgrade->execute(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput)
)#012#8 /var/www/owncloud/3rdparty/symfony/console/Application.php(844): Symfony\Component\Console\Command\Command->run(Object(Symfony\Component\Console\Input\ArgvInput), Object(Symfony\Component\Console\Output\
ConsoleOutput))#012#9 /var/www/owncloud/3rdparty/symfony/console/Application.php(192): Symfony\Component\Console\Application->doRunCommand(Object(OC\Core\Command\Upgrade), Object(Symfony\Component\Console\Input\
ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))#012#10 /var/www/owncloud/3rdparty/symfony/console/Application.php(123): Symfony\Component\Console\Application->doRun(Object(Symfony\Component\C
onsole\Input\ArgvInput), Object(Symfony\Component\Console\Output\ConsoleOutput))#012#11 /var/www/owncloud/lib/private/Console/Application.php(146): Symfony\Component\Console\Application->run(NULL, NULL)#012#12 /
var/www/owncloud/console.php(102): OC\Console\Application->run()#012#13 /var/www/owncloud/occ(11): require_once('/var/www/ownclo...')#012#14 {main})
Dec 30 21:24:22 web ownCloud[19299]: {core} Exception: {"Exception":"OC\\DB\\MigrationException","Message":"An exception occurred while executing 'SELECT min_value, increment_by FROM \"oc_activity_activity_id_se
q\"':\n\nSQLSTATE[42703]: Undefined column: 7 ERROR:  column \"min_value\" does not exist\nLINE 1: SELECT min_value, increment_by FROM \"oc_activity_activity_id...\n               ^","Code":0,"Trace":"#0 \/var\/
www\/owncloud\/lib\/private\/DB\/Migrator.php(127): OC\\DB\\Migrator->checkTableMigrate(Object(Doctrine\\DBAL\\Schema\\Table))\n#1 \/var\/www\/owncloud\/lib\/private\/DB\/MDB2SchemaManager.php(130): OC\\DB\\Migr
ator->checkMigrate(Object(Doctrine\\DBAL\\Schema\\Schema))\n#2 \/var\/www\/owncloud\/lib\/private\/legacy\/db.php(202): OC\\DB\\MDB2SchemaManager->simulateUpdateDbFromStructure('\/var\/www\/ownclo...')\n#3 \/var
\/www\/owncloud\/lib\/private\/Updater.php(288): OC_DB::simulateUpdateDbFromStructure('\/var\/www\/ownclo...')\n#4 \/var\/www\/owncloud\/lib\/private\/Updater.php(232): OC\\Updater->checkCoreUpgrade()\n#5 \/var\
/www\/owncloud\/lib\/private\/Updater.php(150): OC\\Updater->doUpgrade('9.1.7.2', '9.1.6.2')\n#6 \/var\/www\/owncloud\/core\/Command\/Upgrade.php(290): OC\\Updater->upgrade()\n#7 \/var\/www\/owncloud\/3rdparty\/
symfony\/console\/Command\/Command.php(259): OC\\Core\\Command\\Upgrade->execute(Object(Symfony\\Component\\Console\\Input\\ArgvInput), Object(Symfony\\Component\\Console\\Output\\ConsoleOutput))\n#8 \/var\/www\
/owncloud\/3rdparty\/symfony\/console\/Application.php(844): Symfony\\Component\\Console\\Command\\Command->run(Object(Symfony\\Component\\Console\\Input\\ArgvInput), Object(Symfony\\Component\\Console\\Output\\
ConsoleOutput))\n#9 \/var\/www\/owncloud\/3rdparty\/symfony\/console\/Application.php(192): Symfony\\Component\\Console\\Application->doRunCommand(Object(OC\\Core\\Command\\Upgrade), Object(Symfony\\Component\\C
onsole\\Input\\ArgvInput), Object(Symfony\\Component\\Console\\Output\\ConsoleOutput))\n#10 \/var\/www\/owncloud\/3rdparty\/symfony\/console\/Application.php(123): Symfony\\Component\\Console\\Application->doRun
(Object(Symfony\\Component\\Console\\Input\\ArgvInput), Object(Symfony\\Component\\Console\\Output\\ConsoleOutput))\n#11 \/var\/www\/owncloud\/lib\/private\/Console\/Application.php(146): Symfony\\Component\\Con
sole\\Application->run(NULL, NULL)\n#12 \/var\/www\/owncloud\/console.php(102): OC\\Console\\Application->run()\n#13 \/var\/www\/owncloud\/occ(11): require_once('\/var\/www\/ownclo...')\n#14 {main}","File":"\/va
r\/www\/owncloud\/lib\/private\/DB\/Migrator.php","Line":169}

[ownclouders]

GitMate.io thinks possibly related issues are #13100 (database connection error displays host, username and password details), #25590 (1091 Can't DROP 'PRIMARY' with upgrade 8.1.6->8.1.9), #26594 (Include app name in exceptions of /lib/private/Installer.php), #23637 (Error output when passing --data-dir during command line installation), and #22877 (Support importing arrays using occ config:import).

[ege1]

Forgot to add the Schema of that failing table:

                Sequence "public.oc_activity_activity_id_seq"
  Type  | Start | Minimum |       Maximum       | Increment | Cycles? | Cache
--------+-------+---------+---------------------+-----------+---------+-------
 bigint |     1 |       1 | 9223372036854775807 |         1 | no      |     1
Owned by: public.oc_activity.activity_id

[ownclouders]

Hey, this issue has been closed because the label status/STALE is set and there were no updates for 7 days. Feel free to reopen this issue if you deem it appropriate.

(This is an automated comment from GitMate.io.)

[ege1]

Just because no one takes a look into that bug and fixes it, doesn't make it go away...

[dsoulayrol]

Hi.

I have the same problem when trying to migrate from 9.1.6 to 10.0.6 on Debian 9. Tarballs are from the Open Build Service. The underlying server is Apache with PHP 7.2, database is Postgres 10.

Here is the status of the activity app at the moment the upgrade stops.

root@vega:/var/www/owncloud# sudo -u www-data php occ migrations:status activity
ownCloud or one of the apps require upgrade - only a limited number of commands are available
You may use your browser or the occ upgrade command to do the upgrade
    >> App:                                                activity
    >> Version Table Name:                                 oc_migrations
    >> Migrations Namespace:                               OCA\activity\Migrations
    >> Migrations Directory:                               /var/www/owncloud/apps/activity/appinfo/Migrations
    >> Previous Version:                                   Already at first migration step
    >> Current Version:                                    0
    >> Next Version:                                       20161122085340
    >> Latest Version:                                     20170724182159
    >> Executed Migrations:                                0
    >> Executed Unavailable Migrations:                    0
    >> Available Migrations:                               4
    >> New Migrations:                                     4

I tried to search if I could upgrade manually the activity app related table objects, but I'm quite ignorant about owncloud internals. I did not find any clue in the Migrations directories from any 10.0 versions and I did not find where to search in 9.1.7.

[DeepDiver1975]

Postgres 10.

this is the trouble - there is an issue in the used database library and/or our code which breaks on postgres 10.

[dsoulayrol]

Thanks, you're right.
I tried the patch provided by wzator on doctrine/dbal#2868 and it worked for me. No other issue for now.

[mbalmer]

On PostgreSQL 10 and onwards, you can't query min_value and increment_by directly from the sequence. You have to use the catalog:

select min_value, increment_by
  from pg_catalog.pg_sequence
 where sequencename = '<name of the sequence>'

The "offending" code is in the file ./lib/composer/doctrine/dbal/lib/Doctrine/DBAL/Schema/PostgreSqlSchemaManager.php.

This SQL code unfortunately is incompatible with PostgreSQL version prior to 10. The PHP code will have to discriminate between the version, but that should be fairly easy.

[DeepDiver1975]

This SQL code unfortunately is incompatible with PostgreSQL version prior to 10. The PHP code will have to discriminate between the version, but that should be fairly easy.

this is all handled in doctrine/dbal which we use as db abstraction layer.
the library has been updated on master and postgres 10 should be supported now - needs testing.
given the php version requirements we cannot update the library for owncloud 9 or 10 - yet

[PVince81]

@DeepDiver1975 I think we can update dbal on master now ?

[PVince81]

seems master is already on dbal 2.8 in composer.lock despite composer.json saying 2.5.

needs to be verified...

[PVince81]

we'll need to setup CI for Postgres 10 to verify that it works on master

[PVince81]

I've made a new issue for Postgres 10 support here: #33187

Closing this OC 9.1 issue then