Upgrade DB migration fails: `Exception: Database error when running migration 28000Date20230728104802 for app core`

I33Buckler commented 1 year ago

⚠️ This issue respects the following points: ⚠️

[X] This is a bug, not a question or a configuration/webserver/proxy issue.
[X] This issue is not already reported on Github OR Nextcloud Community Forum (I've searched it).
[X] Nextcloud Server is up to date. See Maintenance and Release Schedule for supported versions.
[X] I agree to follow Nextcloud's Code of Conduct.

Bug description

In the process of upgrading my Nextcloud installation from Nextcloud 26.0.1.1 to 27.1.0 the upgrade failed with the following message.

'Exception: Database error when running migration 28000Date20230728104802 for app core An exception occurred while executing a query: SQLSTATE[3F000]: Invalid schema name: 7 ERROR: schema "ag_catalog" does not exist". '
Cannot proceed further with installation.
Detailed logs reports the following:
'Preparing update

Set log level to debug

Turned on maintenance mode

Repair step: Repair MySQL collation

Repair info: Not a mysql database -> nothing to do

Repair step: Repair SQLite autoincrement

Repair step: Copy data from accounts table when migrating from ownCloud

Repair step: Drop account terms table when migrating from ownCloud

Updating database schema

Exception: Database error when running migration 28000Date20230728104802 for app core An exception occurred while executing a query: SQLSTATE[3F000]: Invalid schema name: 7 ERROR: schema "ag_catalog" does not exist'

Steps to reproduce

1.Ran the web updater. Installation failed with message above. 2.Tried running 'occ upgrade' with the following messages. 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 Setting log level to debug Turned on maintenance mode Updating database schema Exception: Database error when running migration 28000Date20230728104802 for app core An exception occurred while executing a query: SQLSTATE[3F000]: Invalid schema name: 7 ERROR: schema "ag_catalog" does not exist Update failed Maintenance mode is kept active Resetting log level

Expected behavior

Expected the upgrade to succeed and any necessary changes applied by the process.

Installation method

None

Nextcloud Server version

26

Operating system

Debian/Ubuntu

PHP engine version

PHP 8.2

Web server

Apache (supported)

Database engine version

PostgreSQL

Is this bug present after an update or on a fresh install?

Upgraded to a MAJOR version (ex. 22 to 23)

Are you using the Nextcloud Server Encryption module?

Encryption is Disabled

What user-backends are you using?

[X] Default user-backend (database)
[ ] LDAP/ Active Directory
[ ] SSO - SAML
[ ] Other

Configuration report

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
{
    "system": {
        "passwordsalt": "***REMOVED SENSITIVE VALUE***",
        "secret": "***REMOVED SENSITIVE VALUE***",
        "trusted_domains": [
            "localhost",
            "nextcloud.canberramjg.ddns.net"
        ],
        "datadirectory": "***REMOVED SENSITIVE VALUE***",
        "logfile": "",
        "dbtype": "pgsql",
        "version": "27.0.2.1",
        "overwrite.cli.url": "http:\/\/localhost",
        "dbname": "***REMOVED SENSITIVE VALUE***",
        "dbhost": "***REMOVED SENSITIVE VALUE***",
        "dbport": "",
        "dbtableprefix": "oc_",
        "dbuser": "***REMOVED SENSITIVE VALUE***",
        "dbpassword": "***REMOVED SENSITIVE VALUE***",
        "installed": true,
        "instanceid": "***REMOVED SENSITIVE VALUE***",
        "default_phone_region": "AU",
        "mail_smtpmode": "smtp",
        "mail_sendmailmode": "smtp",
        "mail_from_address": "***REMOVED SENSITIVE VALUE***",
        "mail_domain": "***REMOVED SENSITIVE VALUE***",
        "mail_smtphost": "***REMOVED SENSITIVE VALUE***",
        "mail_smtpport": "465",
        "mail_smtpauth": 1,
        "mail_smtpname": "***REMOVED SENSITIVE VALUE***",
        "mail_smtppassword": "***REMOVED SENSITIVE VALUE***",
        "memcache.local": "\\OC\\Memcache\\APCu",
        "memcache.distributed": "\\OC\\Memcache\\Redis",
        "memcache.locking": "\\OC\\Memcache\\Redis",
        "redis": {
            "host": "***REMOVED SENSITIVE VALUE***",
            "port": 6379
        },
        "app_install_overwrite": [
            "maps",
            "ocdownloader",
            "duplicatefinder",
            "emlviewer",
            "scanner",
            "sciencemesh",
            "flowupload"
        ],
        "memories.exiftool": "\/srv\/NextCloud\/nextcloud\/apps\/memories\/exiftool-bin\/exiftool-amd64-glibc",
        "memories.vod.path": "\/srv\/NextCloud\/nextcloud\/apps\/memories\/exiftool-bin\/go-vod-amd64",
        "memories.vod.ffmpeg": "\/usr\/bin\/ffmpeg",
        "memories.vod.ffprobe": "\/usr\/bin\/ffprobe",
        "maintenance": true,
        "preview_libreoffice_path": "\/usr\/bin\/libreoffice",
        "enable_previews": true,
        "preview_max_memory": 4096,
        "preview_max_filesize_image": 256,
        "enabledPreviewProviders": [
            "OC\\Preview\\TXT",
            "OC\\Preview\\MarkDown",
            "OC\\Preview\\OpenDocument",
            "OC\\Preview\\PDF",
            "OC\\Preview\\MSOffice2003",
            "OC\\Preview\\MSOfficeDoc",
            "OC\\Preview\\Image",
            "OC\\Preview\\Photoshop",
            "OC\\Preview\\TIFF",
            "OC\\Preview\\SVG",
            "OC\\Preview\\Font",
            "OC\\Preview\\MP3",
            "OC\\Preview\\Movie",
            "OC\\Preview\\MKV",
            "OC\\Preview\\MP4",
            "OC\\Preview\\AVI",
            "OC\\Preview\\HEIC"
        ],
        "memories.gis_type": 2,
        "memories.vod.disable": false,
        "memories.vod.vaapi": true,
        "memories.vod.vaapi.low_power": true,
        "memories.vod.external": true,
        "theme": "",
        "loglevel": 2,
        "log_type": "syslog",
        "syslog_tag": "Nextcloud",
        "mail_smtpsecure": "ssl",
        "updater.secret": "***REMOVED SENSITIVE VALUE***"
    }
}

List of activated Apps

Enabled:
  - activity: 2.19.0
  - admin_audit: 1.17.0
  - appointments: 1.15.3
  - bookmarks: 13.1.1
  - calendar: 4.5.0
  - camerarawpreviews: 0.8.2
  - checksum: 1.2.2
  - circles: 27.0.1
  - cloud_federation_api: 1.10.0
  - collectives: 2.7.1
  - comments: 1.17.0
  - contacts: 5.4.0
  - contactsinteraction: 1.8.0
  - cookbook: 0.10.2
  - dashboard: 7.7.0
  - dav: 1.27.0
  - deck: 1.11.0
  - external: 5.2.1
  - externalportal: 1.2.0
  - federatedfilesharing: 1.17.0
  - federation: 1.17.0
  - files: 1.22.0
  - files_accesscontrol: 1.17.0
  - files_automatedtagging: 1.17.0
  - files_external: 1.19.0
  - files_pdfviewer: 2.8.0
  - files_rightclick: 1.6.0
  - files_sharing: 1.19.0
  - files_texteditor: 2.15.1
  - files_trashbin: 1.17.0
  - files_versions: 1.20.0
  - fileslibreofficeedit: 1.1.0
  - firstrunwizard: 2.16.0
  - forms: 3.3.1
  - gpxpod: 5.0.12
  - groupfolders: 15.2.0
  - integration_dropbox: 2.0.1
  - integration_giphy: 1.0.8
  - integration_google: 2.0.2
  - integration_mastodon: 2.0.2
  - integration_openstreetmap: 1.0.6
  - integration_twitter: 1.0.6
  - integration_youtube: 0.1.4
  - logreader: 2.12.0
  - lookup_server_connector: 1.15.0
  - memories: 5.4.1
  - money: 0.20.0
  - music: 1.8.4
  - ncdownloader: 1.0.20
  - nextcloud_announcements: 1.16.0
  - notifications: 2.15.0
  - oauth2: 1.15.1
  - password_policy: 1.17.0
  - passwords: 2023.9.30
  - passwords_handbook: 2023.9.20
  - pdf_downloader: 1.1.3
  - phonetrack: 0.7.6
  - photos: 2.3.0
  - polls: 5.3.2
  - previewgenerator: 5.3.0
  - privacy: 1.11.0
  - provisioning_api: 1.17.0
  - recognize: 4.3.2
  - recommendations: 1.6.0
  - related_resources: 1.2.0
  - richdocuments: 8.2.0
  - root_cache_cleaner: 0.1.5
  - serverinfo: 1.17.0
  - settings: 1.9.0
  - sharebymail: 1.17.0
  - sociallogin: 5.5.3
  - spreed: 17.0.3
  - support: 1.10.0
  - survey_client: 1.15.0
  - systemtags: 1.17.0
  - tasks: 0.15.0
  - text: 3.8.0
  - theming: 2.2.0
  - twofactor_backupcodes: 1.16.0
  - updatenotification: 1.17.0
  - user_status: 1.7.0
  - viewer: 2.1.0
  - weather_status: 1.7.0
  - workflow_ocr: 1.27.4
  - workflowengine: 2.9.0
Disabled:
  - bruteforcesettings: 2.7.0
  - emlviewer: 1.0.7 (installed 1.0.7)
  - encryption: 2.15.0
  - files_reminders: 1.0.0
  - google_synchronization: 1.0.9 (installed 1.0.9)
  - libresign: 7.1.1 (installed 7.1.1)
  - messagevault: 1.0.3 (installed 1.0.3)
  - scanner: 0.2.3 (installed 0.2.3)
  - suspicious_login: 5.0.0
  - twofactor_totp: 9.0.0
  - user_ldap: 1.17.0

Nextcloud Signing status

Not available as the server no longer functions following the upgrade failure.

Nextcloud Logs

No response

Additional info

No response

joshtrichards commented 1 year ago

I don't think this is coming from NC or that db migration. I don't see where ag_catalog could possibly be coming from within server.

Are you using Apache AGE in your PostgreSQL server perhaps?

https://age.apache.org/age-manual/master/intro/setup.html

May be see if ag_catalog listed in your postgresql search_path? e.g. show search_path;

I33Buckler commented 1 year ago

I don't think this is coming from NC or that db migration. I don't see where ag_catalog could possibly be coming from within server.

Are you using Apache AGE in your PostgreSQL server perhaps?

https://age.apache.org/age-manual/master/intro/setup.html

May be see if ag_catalog listed in your postgresql search_path? e.g. show search_path;

Yes I have APACHE AGE installed. However, it is not specified in the search path for the nextcloud database installation. show search_path for the nextcloud database returns "$user", public. It surprises me as well that the upgrade was identifying ag_catalog. I have databases in addition to the nextcloud database on the server.

joshtrichards commented 1 year ago

You can see what that db migration in NC does here:

https://github.com/nextcloud/server/blob/stable27/core/Migrations/Version28000Date20230728104802.php

It just drops one legacy table and adds one new table. It's a really simple migration.

Sounds almost like the tables were inserted into the ag_catalog schema at some point or something, and the dbuser in-use for NC doesn't have appropriate permissions to add/drop tables therein...

I33Buckler commented 1 year ago

You can see what that db migration in NC does here:

https://github.com/nextcloud/server/blob/stable27/core/Migrations/Version28000Date20230728104802.php

It just drops one legacy table and adds one new table. It's a really simple migration.

Sounds almost like the tables were inserted into the ag_catalog schema at some point or something, and the dbuser in-use for NC doesn't have appropriate permissions to add/drop tables therein...

Interesting as 'select schema_name from information_schema.schemata;' on the nextcloud database returns public information_schema pg_catalog pg_toast

I33Buckler commented 1 year ago

The nextcloud database is not the only database located on the server. The schema ag_catalog is to be found in the other database, though not in the nextcloud database. The ag_catalog schema located in the other database contains the following tables:

ag_catalog.ag_graph
ag_catalog.ag_label
ag_catalog.gtfs_common_shapes

joshtrichards commented 1 year ago

I don't know. It sounds like it's either a local config issue with your db user/roles/schemas or something with that extension. I don't know enough about AGE to even speculate about what that might be.

If AGE/ag_catalog was truly isolated from your NC database then the NC updater wouldn't even know it existed.

I33Buckler commented 1 year ago

I don't know. It sounds like it's either a local config issue with your db user/roles/schemas or something with that extension. I don't know enough about AGE to even speculate about what that might be.

If AGE/ag_catalog was truly isolated from your NC database then the NC updater wouldn't even know it existed.

Thanks. It is a mystery. Have tried everything I can find on interrogating the issue and cannot find anything that suggests that the NC database should be attempting to access the ag_catalog schema. Any suggestions on how to determine what could be causing the issue would be helpful.

I33Buckler commented 1 year ago

Is quite a mystery. Noticed that in the postgres database I have dblink extension installed. This is described as 'connect to other PostgreSQL databases from within a database'. Figuring it could be an issue I decided to drop the extension and it prompted me with 'ERROR: schema "ag_catalog" does not exist'. I am beginning to think this is the culprit.

mingfang commented 7 months ago

I'm also seeing this problem and I don't have dblink installed.

ishaan812 commented 3 months ago

Hey any fix for this, same issue for me, could reproduce all the debugging steps the author undertook as well. But just for one of the tables I have. If I comment out the one column named tags or even rename it to something like tags1 in the table then the migration runs fine. Is it some reserved keyword issue thats causing it although I dont have age installed in this db anyways so not sure why its an issue.

nextcloud / server