SQLSTATE 42501 when Upgrading from 12.0.3 to 12.0.4

ℹ️ Support
, , , ,
1

Environment:

  • Nextcloud version (eg, 12.0.3):
  • Operating system and version (11.1-RELEASE-p4):
  • Apache or nginx version (nginx/1.12.1):
  • PHP version (PHP 7.1.10 (cli) (built: Nov 4 2017) (NTS)):

Issue: Upgrading from 12.0.3 to 12.0.4

I will admit I am not very familiar with PostgreSQL DBMS. However, I will share as much as I can, since I am currently stumped.

I have a Nextcloud Instance at version 12.0.3 that I am upgrading to 12.0.4. I had been doing some testing previously on another machine where it was installed on a MySQL instance. Using pgloader, I was able to migrate from MySQL to Postgresql (relatively smoothly; wasn’t able to migrate the calendar or contacts data, but that’s okay, I had file backups).

My Current PostgreSQL Data consists of the following:

                           List of databases
   Name    |   Owner   | Encoding  | Collate | Ctype |    Access privileges    
-----------+-----------+-----------+---------+-------+-------------------------
 nextcloud | nextcloud | UTF8      | C       | C     | =Tc/nextcloud          +
           |           |           |         |       | nextcloud=CTc/nextcloud
 postgres  | postgres  | SQL_ASCII | C       | C     | 
 template0 | postgres  | SQL_ASCII | C       | C     | =c/postgres            +
           |           |           |         |       | postgres=CTc/postgres
 template1 | postgres  | SQL_ASCII | C       | C     | =c/postgres            +
           |           |           |         |       | postgres=CTc/postgres

(4 rows)
Figure 1: Database List

With the following users/roles:

                                List of roles
Role name  |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
nextcloud  | Create DB                                                  | {}
pgsql_root | Superuser, Create role, Create DB                          | {}
postgres   | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

Figure 2: User/Role List

And the following schemas:

List of schemas
  Name  |  Owner   
--------+----------
 public | postgres
(1 row)

Figure 3: Schema List

The update process had successfully downloaded and applied the file changes (at least from the browser’s point of view). I would label this as the “First Stage”. At the beginning of the “Second Stage”, a message at the bottom advises the following:

To avoid timeouts with larger installations, you can instead run the following command from your installation directory:
./occ upgrade

So I obliged with the following command:

sudo -u www php nextcloud/occ upgrade
Figure 4: Upgrade Command

…and received the following output:

The process control (PCNTL) extensions are required in case you want to interrupt long running commands - see http://php.net/manual/en/book.pcntl.php
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
Updating database schema
Doctrine\DBAL\Exception\DriverException: An exception occurred while executing 'ALTER INDEX idx_17216_appconfig_config_key_index RENAME TO appconfig_config_key_index':

SQLSTATE[42501]: Insufficient privilege: 7 ERROR:  must be owner of relation idx_17216_appconfig_config_key_index
Update failed
Maintenance mode is kept active
Reset log level

Is this the first time you’ve seen this error? (Y):

Now, from the error I already understand this is a permissions issue. I have tried the following to grant the correct permissions to the nextcloud role for the “nextcloud” database:

However, I have run into the same issue when running the command again in Figure 4. What am I missing?

** EDIT **: Adding Nextcloud.log
https://pastebin.com/9EBVBUJB

2

I did play around FreeBSD recently and can only help you with two small things:

You need to install these extensions:
pkg install php71-pcntl

Also the sudo didn’t work directly, this one worked out of the box:
su -m www -c 'php nextcloud/occ upgrade'

I found this howto for FreeBSD, it runs everything in a jail. I don’t run everything in a jail but it gives a few ideas about FreeBSD-related stuff (name of packages, build for redis and php 7.1, …):

3

@tflidd - I did take a look at the link you included in your post. Most of this information concentrates on the deployment aspect for Nextcloud on FreeBSD. This will be great information if I decide to wipe the slate clean (which is looking more likely with each post I make) and try to set everything up again. It may be fun to try working with zfs snapshots the next time around in case an issue arises.

First, I would like to make a couple of corrections to the previous post. In Figure 3, the list of schemas is only accurate when you are connected to the correct database. In this case, I connected to the default database, which is postgres. I have since tested this and confirmed that these values are different when connected to the nextcloud database:

List of schemas
  Name  |  Owner   
--------+----------
 nextcloud | nextcloud
(1 row)

Figure 3.b

I have tried a couple of things since my last post.

I have changed the database account specified in config.php to an account I know has permissions to everything; pgsql_root (See Figure 1). I understand this is bad practice for security reasons, among other things. Since this is a private, personal cloud right now, and is not internet-facing, I accepted the risk involved.

I obtained the same results when running su -m www -c ‘php nextcloud/occ upgrade’ before the role change in config.php. However, after the role change, I obtained different results from running under pgsql_root:

Switching to the PGSQL_ROOT role
Figure 4: Database Exceptions when switching roles

Now, it is interesting that when running under pgsql_root that it complains that oc_appconfig is not a relation:

List of Relations After Switching to PGSQL_ROOT
Figure 5: Performed by running \dit nextcloud.* using the psql tool as the FreeBSD user postgres

At this point I’m quite baffled.

(Side Note): I use the ports method of installing software on FreeBSD, and usually keep these up to date. Between then and now, I have removed the previous version of PHP installed, and now have a more current version, along with selected extensions, including devel/php71-pcntl.

4

I don’t know Postgresql either but personally I would run my system on a database I know a bit more. And to start with it, I’d rather use a new setup to avoid any pitfalls from migration.

5

At this point I’m using this post as a point of reference.
I definitely could start over, writing some documentation in the process for future reference. I could start out again with the familiar environment of MySQL than PostgreSQL.

However, I want to see if I can figure out what I can do to get back on track without starting from scratch. I have heard a lot of good things about using PostgreSQL and wanted to dive in to see what’s different. Turns out, it was more than I thought, and that’s what makes it interesting for me.

:slightly_smiling_face: I want to thank you for your time and help so far. I am going to continue to update this thread for the day or so, and see if I can recover. If not, I’ll just pull all of the data I have into a centralized collection, save the general configuration I have, and reinstall Nextcloud. In the future, I’ll see if I can run Nextcloud on a bhyve instance and use a zfs dataset for the primary data backend.

1 Like
6

After looking at some documentation on PostgresSQL, I believe I understand more about what is happening. First, I was curious why \dit alone did not return any of the objects in the nextcloud database in Figure 5.

nextcloud=# \conninfo
You are connected to database "nextcloud" as user "pgsql_root" via socket in "/tmp" at port "5432".
nextcloud=# SHOW search_path;
   search_path   
-----------------
 "$user", public
(1 row)

Figure 6: Search Path for nextcloud Database

Per the PostgreSQL Client Applications page in the manual, under the Patterns section:

Whenever the pattern parameter is omitted completely, the \d commands display all objects that are visible in the current schema search path — this is equivalent to using * as the pattern. (An object is said to be visible if its containing schema is in the search path and no object of the same kind and name appears earlier in the search path. This is equivalent to the statement that the object can be referenced by name without explicit schema qualification.) To see all objects in the database regardless of visibility, use . as the pattern.

The key part of which I did not understand at first, is this part of the paragraph:

An object is said to be visible if its containing schema is in the search path and no object of the same kind and name appears earlier in the search path

If I use nextcloud.* as my pattern, only then am I able to see the tables per Figure 5. Alternatively, if I change the search path in Figure 6 and add nextcloud, I also should be able to see the tables without qualification (i.e. nextcloud.*).

However, it is interesting that pgsql_root was the owner of all those tables. I believe this is because I used that role to import all of the data via pgloader from my previous Nextcloud instance, which used MySQL as the backend. It should have had nothing to do with changing which role I used in config.php.

To fix this, I ran the following using psql. Recall that Figure 5 contains the state of pertinent Nextcloud data, so this should be a nice reference to compare any changes I’ve made against:

nextcloud=# REASSIGN OWNED BY pgsql_root TO nextcloud;
REASSIGN OWNED

Here are the references I found when trying to determine how to change the owner of all the necessary database objects back to nextcloud:

After the ownership change, I checked and verified that nextcloud was now the owner of the tables:

Pastebin - After Ownership Change

Figure 7 - After Ownership Change

Then, I ran the upgrade script again:

su -m www -c 'php 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
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

Well damn it… :face_with_raised_eyebrow:

So what’s a SERIAL?

Hmm, doesn’t look like I can make some direct changes. Also, I am not sure if the update would recognize/accept any changes I’ve made at this point.

At this point, I’ll accept that I should just do a clean install :stuck_out_tongue: . It was fun poking around to see what’s going on though!