Server: Update to 11.0.3 failed

[hukoeth]

Steps to reproduce

1. Start updater and wait for first step to complete
2. Select "web updater"
3. Error is shown

Expected behaviour

Nextcloud should update

Actual behaviour

Error:

DoctrineDBALExceptionDriverException: An exception occurred while executing 'ALTER TABLE oc_storages ALTER numeric_id TYPE SERIAL': SQLSTATE[42704]: Undefined object: 7 ERROR: type "serial" does not exist
Detailed logs
Preparing update
Set log level to debug
Turned on maintenance mode
Updating database schema
DoctrineDBALExceptionDriverException: An exception occurred while executing 'ALTER TABLE oc_storages ALTER numeric_id TYPE SERIAL': SQLSTATE[42704]: Undefined object: 7 ERROR: type "serial" does not exist

Postgresql doesn't allow to change to type serial. Is Postgresql not supported?

Server configuration

Operating system:
Ubuntu 17.04

Web server:
Apache

Database:
Postgres 9.6.2

PHP version:
7.0

Nextcloud version: (see Nextcloud admin page)
11.0.2

Updated from an older Nextcloud/ownCloud or fresh install:
Updated multiple times

Where did you install Nextcloud from:
Can't remember

[ziirish]

Hi,

I have the same issue (I already noticed it with plugins update since 11.0.2) but now this is the whole nextcloud upgrade that is stuck.

[MorrisJobke]

cc @icewind1991 @nickvergessen

[nickvergessen]

I don't quite understand, because the column/table was last changed 2 years ago.
That is very weird 😕

[hukoeth]

Maybe and issue with doctrine?

[nickvergessen]

I guess so, since we didn't update that either.

[hukoeth]

I'll do a fresh test install with a new database and then update to the latest beta. Will report back in a bit.

[hukoeth]

OK so on the test install I can't upgrade to beta because of a different error but if I compare the two databases, the column on the "old" database is type bigint and on the test one it's integer. No idea how that happened.

I changed the type to integer now on the old database and tried to update from 12.0.0 beta 2 to beta 3 but now the "Start Update" button doesn't work. Is that again weirdness on my server or can someone confirm? If someone confirms, I can log a new issue for this.

[MorrisJobke]

I changed the type to integer now on the old database and tried to update from 12.0.0 beta 2 to beta 3 but now the "Start Update" button doesn't work. Is that again weirdness on my server or can someone confirm? If someone confirms, I can log a new issue for this.

This was a bug in beta2 -.- Sorry for this. Apply this patch and it should work again: https://github.com/nextcloud/server/pull/4762.patch

cd /tmp
wget https://github.com/nextcloud/server/pull/4762.patch
cd /var/www/nextcloud
patch -p1 < /tmp/4762.patch

Then it should work. Keep in mind to clear browser caches, because otherwise the JS is not updated on the admin page to contain the fix.

[ziirish]

'version' => '11.0.2.7'

I'm using those unofficial docker images but they used to work fine until 11.0.3.

$ docker-compose pull
$ docker-compose up -d
$ docker exec -it cloud_nextcloud_1 occ
Nextcloud 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
Nextcloud version 11.0.3
$ docker exec -it cloud_nextcloud_1 occ upgrade
Nextcloud 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
Turned on maintenance mode
Updating database schema
Doctrine\DBAL\Exception\DriverException: An exception occurred while executing 'ALTER TABLE oc_storages ALTER numeric_id TYPE SERIAL':

SQLSTATE[42704]: Undefined object: 7 ERROR:  type "serial" does not exist                                                                                                                                                                      
Update failed
Maintenance mode is kept active
Reset log level

And here is my current SQL schema:

nextcloud_db=# \d oc_storages
                                       Table "public.oc_storages"
    Column    |         Type          |                            Modifiers                             
--------------+-----------------------+------------------------------------------------------------------
 id           | character varying(64) | 
 numeric_id   | bigint                | not null default nextval('oc_storages_numeric_id_seq'::regclass)
 available    | bigint                | not null default (1)::bigint
 last_checked | bigint                | 
Indexes:
    "idx_106898_primary" PRIMARY KEY, btree (numeric_id)
    "idx_106898_storages_id_index" UNIQUE, btree (id)

Looks like the same issue as @hukoeth where bigint should not be there.

The question is: is it possible a pg_dump/import changed the type of those columns?

[MorrisJobke]

Oh sorry ... wrong button.

[ziirish]

no problem, thanks

[MorrisJobke]

The question is: is it possible a pg_dump/import changed the type of those columns?

Seems so, because we didn't changed it in the past. 🤔

And this is postgres, right?

[ziirish]

yes, postgres database.
I did migrate from 9.1 to 9.6 through a pg_dump/import.

[hukoeth]

I did a DB migrate as well but I can't remember if this was before or after the first failed upgrade attempt of Nextcloud.
However in my table only the numeric_id is bigint. The rest (except ID) is integer so it can't be only the migration I would assume?!

[ziirish]

So I just setup a new database with a fresh install and here is what I get:

nextcloud2_db=# \d oc_storages
                                       Table "public.oc_storages"
    Column    |         Type          |                            Modifiers                             
--------------+-----------------------+------------------------------------------------------------------
 id           | character varying(64) | default NULL::character varying
 numeric_id   | integer               | not null default nextval('oc_storages_numeric_id_seq'::regclass)
 available    | integer               | not null default 1
 last_checked | integer               | 
Indexes:
    "oc_storages_pkey" PRIMARY KEY, btree (numeric_id)
    "storages_id_index" UNIQUE, btree (id)

I then pg_dump'ed it and import it but the types don't seem to change.
I don't know what caused those changes then.

$ createdb -O nextcloud nextcloud3_db
$ pg_dump nextcloud2_db >nextcloud3.sql
$ psql nextcloud3_db <nextcloud3.sql
nextcloud3_db=# \d oc_storages
                                       Table "public.oc_storages"
    Column    |         Type          |                            Modifiers
--------------+-----------------------+------------------------------------------------------------------
 id           | character varying(64) | default NULL::character varying
 numeric_id   | integer               | not null default nextval('oc_storages_numeric_id_seq'::regclass)
 available    | integer               | not null default 1
 last_checked | integer               |
Indexes:
    "oc_storages_pkey" PRIMARY KEY, btree (numeric_id)
    "storages_id_index" UNIQUE, btree (id)

[MorrisJobke]

Okay ... last time the schema was changed is 5 years ago: https://github.com/nextcloud/server/blame/80eb3064e2e71e634e818eb49e52a5fe6166da81/db_structure.xml#L98-L98

I guess the change was done in the schema reader. Let me check that.

[MorrisJobke]

Also in there is no change that could cause this. 🤔

[hukoeth]

Well I changed the types manually to the expected values, the update went through and the types didn't change back. I will keep an eye on the database schemas whenever I do config changes, updates or play around with apps to catch any change and will report back in case this happens again.

[nickvergessen]

Yes please, thanks and good luck

[adsworth]

I'm having a similar issue updating the bookmarks apps. https://github.com/nextcloud/bookmarks/issues/372. I'm running 11.0.0.

[adsworth]

and I created the database from a MySQL migration, so this might also be because of a pgimport issue.

[mauritslamers]

Even though this issue is closed, I wanted to indicate that this issue also arrives when you migrate from a recent version of OwnCloud that was installed using a PostgreSQL database to NextCloud.

It can be solved by manually changing the types on the indicated tables. Log in as postgres user into your database, then run the following model queries. For every SQL error complaining about SERIAL, take the field indicated in the failed query message and change its type to int. For every SQL error complaining about BIGSERIAL, take the field indicated in the failed query and change its type to bigint.

After every change, reload the webinterface and restart the upgrade procedure, which then will either complain about the following database migration, or continue.

Hopefully this is useful for someone.

owncloud=# alter table oc_filecache alter fileid TYPE int;
ALTER TABLE
owncloud=# alter table oc_properties alter id TYPE int;
ALTER TABLE
owncloud=# alter table oc_addressbooks alter id TYPE bigint;
ALTER TABLE
owncloud=# alter table oc_cards alter id TYPE bigint;
ALTER TABLE
owncloud=# alter table oc_addressbookchanges alter id TYPE bigint;
ALTER TABLE
owncloud=# alter table oc_calendarobjects alter id TYPE bigint;
ALTER TABLE
owncloud=# alter table oc_calendars alter id TYPE bigint;
ALTER TABLE
owncloud=# alter table oc_calendarchanges alter id TYPE bigint;
ALTER TABLE
owncloud=# alter table oc_calendarsubscriptions alter id TYPE bigint;
ALTER TABLE
owncloud=# alter table oc_schedulingobjects alter id TYPE bigint;
ALTER TABLE
owncloud=# alter table oc_cards_properties alter id TYPE bigint;
ALTER TABLE
owncloud=# alter table oc_dav_shares alter id TYPE bigint;
ALTER TABLE
owncloud=# alter table oc_share_external alter id TYPE int;
ALTER TABLE
owncloud=# alter table oc_files_trash alter auto_id TYPE int;

[mirrom]

@mauritslamers Thank you very much! Altering the tables on postgresql did the trick for me, upgraded from owncloud 9.1.4 to 10.0.4 to nextcloud 12.0.4 to 12.0.5.

[nsteinmetz]

Hi @mauritslamers,

Same for me, thanks a lot - I had to add two more queries for one of my 2 instances for the migration from OC 10.0.4 to NC 12.

ALTER TABLE oc_activity ALTER activity_id TYPE int;
ALTER TABLE oc_activity_mq ALTER mail_id TYPE int;

And then upgraded to NC13 smoothly.

So the full list to copy paste is :

alter table oc_filecache alter fileid TYPE int;
alter table oc_properties alter id TYPE int;
alter table oc_addressbooks alter id TYPE bigint;
alter table oc_cards alter id TYPE bigint;
alter table oc_addressbookchanges alter id TYPE bigint;
alter table oc_calendarobjects alter id TYPE bigint;
alter table oc_calendars alter id TYPE bigint;
alter table oc_calendarchanges alter id TYPE bigint;
alter table oc_calendarsubscriptions alter id TYPE bigint;
alter table oc_schedulingobjects alter id TYPE bigint;
alter table oc_cards_properties alter id TYPE bigint;
alter table oc_dav_shares alter id TYPE bigint;
alter table oc_share_external alter id TYPE int;
alter table oc_files_trash alter auto_id TYPE int;
ALTER TABLE oc_activity ALTER activity_id TYPE int;
ALTER TABLE oc_activity_mq ALTER mail_id TYPE int;