[Bug]: occ db:convert fail with the error : Syntax error: 7 ERROR: syntax error at or near ")" LINE 1: ... setval('oc_preview_locations_id_seq', (SELECT MAX() FROM ))

[ClementJ35]

⚠️ This issue respects the following points: ⚠️

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

Bug description

Hi,
I'm trying to convert my mariadb DB to postgresql.
It fails after proceeding each tables with the error message :

                                                                                                                       
  An exception occurred while executing a query: SQLSTATE[42601]: Syntax error: 7 ERROR:  syntax error at or near ")"  
  LINE 1: ... setval('oc_preview_locations_id_seq', (SELECT MAX() FROM ))                                              
                                                                       ^                                               
                                                                                                                       

In Exception.php line 24:
                                                                           
  SQLSTATE[42601]: Syntax error: 7 ERROR:  syntax error at or near ")"     
  LINE 1: ... setval('oc_preview_locations_id_seq', (SELECT MAX() FROM ))  
                                                                       ^   
                                                                           

In Connection.php line 71:
                                                                           
  SQLSTATE[42601]: Syntax error: 7 ERROR:  syntax error at or near ")"     
  LINE 1: ... setval('oc_preview_locations_id_seq', (SELECT MAX() FROM ))  
                                                                       ^

May be related to the new preview stanza introduced in v33

Steps to reproduce

1. Type sudo -u nextcloud occ db:convert-type --password "$PG_PASSWORD" pgsql "$PG_USER" 127.0.0.1 "$PG_DATABASE" --clear-schema

Expected behavior

To finish the job.

Nextcloud Server version

33

Operating system

Other

PHP engine version

PHP 8.4

Web server

Apache (supported)

Database engine version

MariaDB

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

Upgraded to a MAJOR version (ex. 31 to 32)

Are you using the Nextcloud Server Encryption module?

Encryption is Disabled

What user-backends are you using?

• Default user-backend (database)
• LDAP/ Active Directory
• SSO - SAML
• Other

Configuration report

{
    "system": {
        "datadirectory": "***REMOVED SENSITIVE VALUE***",
        "logfile": "\/var\/log\/nextcloud\/nextcloud.log",
        "apps_paths": [
            {
                "path": "\/usr\/share\/webapps\/nextcloud\/apps",
                "url": "\/apps",
                "writable": false
            },
            {
                "path": "\/var\/lib\/nextcloud\/apps",
                "url": "\/apps-appstore",
                "writable": true
            }
        ],
        "installed": true,
        "instanceid": "***REMOVED SENSITIVE VALUE***",
        "passwordsalt": "***REMOVED SENSITIVE VALUE***",
        "secret": "***REMOVED SENSITIVE VALUE***",
        "trusted_domains": [
            "***REMOVED SENSITIVE VALUE***",
            "127.0.0.1"
        ],
        "dbtype": "mysql",
        "version": "33.0.0.16",
        "overwrite.cli.url": "http:\/\/127.0.0.1",
        "dbname": "***REMOVED SENSITIVE VALUE***",
        "dbhost": "***REMOVED SENSITIVE VALUE***",
        "dbport": "",
        "dbtableprefix": "oc_",
        "mysql.utf8mb4": true,
        "dbuser": "***REMOVED SENSITIVE VALUE***",
        "dbpassword": "***REMOVED SENSITIVE VALUE***",
        "trusted_proxies": "***REMOVED SENSITIVE VALUE***",
        "default_phone_region": "FR",
        "mail_smtpmode": "smtp",
        "mail_sendmailmode": "smtp",
        "mail_from_address": "***REMOVED SENSITIVE VALUE***",
        "mail_domain": "***REMOVED SENSITIVE VALUE***",
        "mail_smtphost": "***REMOVED SENSITIVE VALUE***",
        "mail_smtpport": "25",
        "default_language": "fr",
        "loglevel": 0,
        "maintenance": false,
        "theme": "",
        "memcache.local": "\\OC\\Memcache\\APCu",
        "memcache.dsitributed": "\\OC\\Memcache\\Redis",
        "memcache.locking": "\\OC\\Memcache\\Redis",
        "redis": {
            "host": "***REMOVED SENSITIVE VALUE***",
            "port": 0,
            "dbindex": 0,
            "timeout": 1.5
        },
        "maintenance_window_start": 1,
        "integrity.check.disabled": false,
        "app_install_overwrite": [
            "keeweb",
            "passwords",
            "passman"
        ]
    }
}

List of activated Apps

Enabled:
  - app_api: 33.0.0
  - bruteforcesettings: 6.0.0-dev.0
  - calendar: 6.2.1
  - cloud_federation_api: 1.17.0
  - contacts: 8.4.0
  - contactsinteraction: 1.14.1
  - dav: 1.36.0
  - federatedfilesharing: 1.23.0
  - files: 2.5.0
  - files_external: 1.25.1
  - files_pdfviewer: 6.0.0-dev.0
  - files_sharing: 1.25.2
  - files_versions: 1.26.0
  - lookup_server_connector: 1.21.0
  - notes: 4.13.0
  - notifications: 6.0.0
  - oauth2: 1.21.0
  - passman: 2.4.12
  - previewgenerator: 5.13.0
  - profile: 1.2.0
  - provisioning_api: 1.23.0
  - related_resources: 4.0.0-dev.0
  - secrets: 2.1.6
  - settings: 1.16.0
  - text: 7.0.0-dev.3
  - theming: 2.8.0
  - twofactor_backupcodes: 1.22.0
  - twofactor_totp: 15.0.0-dev.0
  - viewer: 6.0.0-dev.0
  - workflowengine: 2.15.0
Disabled:
  - circles: 33.0.0
  - files_reminders: 1.6.0 (installed 1.4.0)
  - keeweb: 0.6.22 (installed 0.6.22)
  - logreader: 6.0.0
  - password_policy: 5.0.0-dev.0
  - passwords: 2026.2.20 (installed 2026.2.20)
  - privacy: 5.0.0-dev.0
  - recommendations: 6.0.0-dev.0
  - richdocuments: 10.1.0 (installed 10.1.0)
  - serverinfo: 5.0.0-dev.0
  - twofactor_nextcloud_notification: 7.0.0

Nextcloud Signing status

Integrity is failing. (too much lines to paste here)

Nextcloud Logs

None seems relevant as it is occ command.

Additional info

Nextcloud runs from alpine linux packages.
The full logs for the command is :

nextcloud:~# sudo -u nextcloud occ db:convert-type --password "$PG_PASSWORD" pgsql "$PG_USER" 127.0.0.1 "$PG_DATABASE" --clear-schema
Clearing schema in new database
Creating schema in new database
 - app_api
 - bruteforcesettings
 - calendar
 - cloud_federation_api
 - contacts
 - contactsinteraction
 - dav
 - federatedfilesharing
 - files
 - files_external
 - files_pdfviewer
 - files_sharing
 - files_versions
 - lookup_server_connector
 - notes
 - notifications
 - oauth2
 - passman
 - previewgenerator
 - profile
 - provisioning_api
 - related_resources
 - secrets
 - settings
 - text
 - theming
 - twofactor_backupcodes
 - twofactor_totp
 - viewer
 - workflowengine
The following tables will not be converted:
oc_ex_event_handlers
oc_ex_speech_to_text
oc_ex_speech_to_text_q
oc_ex_text_processing
oc_ex_text_processing_q
oc_ex_translation
oc_ex_translation_q
oc_files_reminders
oc_file_metadata
oc_passwords_challenge
oc_passwords_folder
oc_passwords_folder_rv
oc_passwords_keychain
oc_passwords_password
oc_passwords_password_rv
oc_passwords_pw_tag_rel
oc_passwords_registration
oc_passwords_session
oc_passwords_share
oc_passwords_tag
oc_passwords_tag_rv
oc_richdocuments_assets
oc_richdocuments_direct
oc_richdocuments_template
oc_richdocuments_wopi
oc_shares_limits
Please note that tables belonging to disabled (but not removed) apps
can be included by specifying the --all-apps option.
Continue with the conversion (y/n)? [n] y
 - oc_accounts
 6/6 [============================] 100%  1 sec/1 sec 
 - oc_accounts_data
 90/90 [============================] 100% < 1 sec/< 1 sec
 - oc_addressbookchanges
 103/103 [============================] 100% < 1 sec/< 1 sec
 - oc_addressbooks
 7/7 [============================] 100% < 1 sec/< 1 sec
 - oc_appconfig
 147/147 [============================] 100% < 1 sec/< 1 sec
 - oc_appconfig_ex
    0 [>---------------------------] < 1 sec
 - oc_authorized_groups
    0 [>---------------------------] < 1 sec
 - oc_authtoken
 18/18 [============================] 100% < 1 sec/< 1 sec
 - oc_bruteforce_attempts
    0 [>---------------------------] < 1 sec
 - oc_calendar_appt_bookings
    0 [>---------------------------] < 1 sec
 - oc_calendar_appt_configs
    0 [>---------------------------] < 1 sec
 - oc_calendar_invitations
    0 [>---------------------------] < 1 sec
 - oc_calendar_proposal_dats
    0 [>---------------------------] < 1 sec
 - oc_calendar_proposal_dts
    0 [>---------------------------] < 1 sec
 - oc_calendar_proposal_pts
    0 [>---------------------------] < 1 sec
 - oc_calendar_proposal_vts
    0 [>---------------------------] < 1 sec
 - oc_calendar_reminders
 5/5 [============================] 100% < 1 sec/< 1 sec
 - oc_calendar_resources
    0 [>---------------------------] < 1 sec
 - oc_calendar_resources_md
    0 [>---------------------------] < 1 sec
 - oc_calendar_rooms
    0 [>---------------------------] < 1 sec
 - oc_calendar_rooms_md
    0 [>---------------------------] < 1 sec
 - oc_calendarchanges
 146/146 [============================] 100%  1 sec/1 sec 
 - oc_calendarobjects
 202/202 [============================] 100% < 1 sec/< 1 sec
 - oc_calendarobjects_props
 509/509 [============================] 100%  1 sec/1 sec 
 - oc_calendars
 12/12 [============================] 100% < 1 sec/< 1 sec
 - oc_calendars_federated
    0 [>---------------------------] < 1 sec
 - oc_calendarsubscriptions
    0 [>---------------------------] < 1 sec
 - oc_cards
 121/121 [============================] 100% < 1 sec/< 1 sec
 - oc_cards_properties
 528/528 [============================] 100%  1 sec/1 sec 
 - oc_collres_accesscache
    0 [>---------------------------] < 1 sec
 - oc_collres_collections
    0 [>---------------------------] < 1 sec
 - oc_collres_resources
    0 [>---------------------------] < 1 sec
 - oc_comments
    0 [>---------------------------] < 1 sec
 - oc_comments_read_markers
    0 [>---------------------------] < 1 sec
 - oc_dav_absence
    0 [>---------------------------] < 1 sec
 - oc_dav_cal_proxy
    0 [>---------------------------] < 1 sec
 - oc_dav_shares
 1/1 [============================] 100% < 1 sec/< 1 sec
 - oc_direct_edit
    0 [>---------------------------] < 1 sec
 - oc_directlink
    0 [>---------------------------] < 1 sec
 - oc_ex_apps
    0 [>---------------------------] < 1 sec
 - oc_ex_apps_daemons
    0 [>---------------------------] < 1 sec
 - oc_ex_apps_routes
    0 [>---------------------------] < 1 sec
 - oc_ex_deploy_options
    0 [>---------------------------] < 1 sec
 - oc_ex_occ_commands
    0 [>---------------------------] < 1 sec
 - oc_ex_settings_forms
    0 [>---------------------------] < 1 sec
 - oc_ex_task_processing
    0 [>---------------------------] < 1 sec
 - oc_ex_ui_files_actions
    0 [>---------------------------] < 1 sec
 - oc_ex_ui_scripts
    0 [>---------------------------] < 1 sec
 - oc_ex_ui_states
    0 [>---------------------------] < 1 sec
 - oc_ex_ui_styles
    0 [>---------------------------] < 1 sec
 - oc_ex_ui_top_menu
    0 [>---------------------------] < 1 sec
 - oc_external_applicable
 1/1 [============================] 100% < 1 sec/< 1 sec
 - oc_external_config
 1/1 [============================] 100% < 1 sec/< 1 sec
 - oc_external_mounts
 1/1 [============================] 100% < 1 sec/< 1 sec
 - oc_external_options
 6/6 [============================] 100% < 1 sec/< 1 sec
 - oc_federated_invites
    0 [>---------------------------] < 1 sec
 - oc_federated_reshares
    0 [>---------------------------] < 1 sec
 - oc_file_locks
 14/14 [============================] 100% < 1 sec/< 1 sec
 - oc_filecache
chunked query, 6 chunks
 5282/5282 [============================] 100% 12 secs/12 secs
 - oc_filecache_extended
chunked query, 2 chunks
 1753/1753 [============================] 100% 2 secs/2 secs
 - oc_files_metadata
 130/130 [============================] 100% < 1 sec/< 1 sec
 - oc_files_metadata_index
    0 [>---------------------------] < 1 sec
 - oc_files_versions
chunked query, 2 chunks
 1021/1021 [============================] 100% 2 secs/2 secs
 - oc_flow_checks
    0 [>---------------------------] < 1 sec
 - oc_flow_operations
    0 [>---------------------------] < 1 sec
 - oc_flow_operations_scope
    0 [>---------------------------] < 1 sec
 - oc_group_admin
    0 [>---------------------------] < 1 sec
 - oc_group_user
 1/1 [============================] 100% < 1 sec/< 1 sec
 - oc_groups
 1/1 [============================] 100% < 1 sec/< 1 sec
 - oc_jobs
 43/43 [============================] 100% < 1 sec/< 1 sec
 - oc_known_users
    0 [>---------------------------] < 1 sec
 - oc_login_flow_v2
    0 [>---------------------------] < 1 sec
 - oc_migrations
Skipping migrations table because it was already filled by running the migrations
 - oc_mimetypes
 61/61 [============================] 100% < 1 sec/< 1 sec
 - oc_mounts
    0 [>---------------------------] < 1 sec
 - oc_notes_meta
 2/2 [============================] 100% < 1 sec/< 1 sec
 - oc_notifications
    0 [>---------------------------] < 1 sec
 - oc_notifications_pushhash
    0 [>---------------------------] < 1 sec
 - oc_notifications_settings
    0 [>---------------------------] < 1 sec
 - oc_oauth2_access_tokens
    0 [>---------------------------] < 1 sec
 - oc_oauth2_clients
    0 [>---------------------------] < 1 sec
 - oc_open_local_editor
    0 [>---------------------------] < 1 sec
 - oc_passman_credentials
    0 [>---------------------------] < 1 sec
 - oc_passman_delete_vault_request
    0 [>---------------------------] < 1 sec
 - oc_passman_files
    0 [>---------------------------] < 1 sec
 - oc_passman_revisions
    0 [>---------------------------] < 1 sec
 - oc_passman_share_request
    0 [>---------------------------] < 1 sec
 - oc_passman_sharing_acl
    0 [>---------------------------] < 1 sec
 - oc_passman_vaults
    0 [>---------------------------] < 1 sec
 - oc_preferences
 221/221 [============================] 100% < 1 sec/< 1 sec
 - oc_preferences_ex
    0 [>---------------------------] < 1 sec
 - oc_preview_generation
    0 [>---------------------------] < 1 sec
 - oc_preview_locations
    0 [>---------------------------] < 1 sec
 - oc_preview_versions
    0 [>---------------------------] < 1 sec
 - oc_previews
 84/84 [============================] 100%  1 sec/1 sec 
 - oc_profile_config
 5/5 [============================] 100% < 1 sec/< 1 sec
 - oc_properties
 1/1 [============================] 100% < 1 sec/< 1 sec
 - oc_ratelimit_entries
 3/3 [============================] 100% < 1 sec/< 1 sec
 - oc_reactions
    0 [>---------------------------] < 1 sec
 - oc_recent_contact
 1/1 [============================] 100% < 1 sec/< 1 sec
 - oc_schedulingobjects
    0 [>---------------------------] < 1 sec
 - oc_sec_signatory
    0 [>---------------------------] < 1 sec
 - oc_secrets
    0 [>---------------------------] < 1 sec
 - oc_share
 19/19 [============================] 100% < 1 sec/< 1 sec
 - oc_share_external
    0 [>---------------------------] < 1 sec
 - oc_storages
 8/8 [============================] 100% < 1 sec/< 1 sec
 - oc_storages_credentials
 1/1 [============================] 100% < 1 sec/< 1 sec
 - oc_systemtag
    0 [>---------------------------] < 1 sec
 - oc_systemtag_group
    0 [>---------------------------] < 1 sec
 - oc_systemtag_object_mapping
    0 [>---------------------------] < 1 sec
 - oc_taskprocessing_tasks
    0 [>---------------------------] < 1 sec
 - oc_text2image_tasks
    0 [>---------------------------] < 1 sec
 - oc_text_documents
    0 [>---------------------------] < 1 sec
 - oc_text_sessions
    0 [>---------------------------] < 1 sec
 - oc_text_steps
    0 [>---------------------------] < 1 sec
 - oc_textprocessing_tasks
    0 [>---------------------------] < 1 sec
 - oc_twofactor_backupcodes
    0 [>---------------------------] < 1 sec
 - oc_twofactor_providers
 12/12 [============================] 100% < 1 sec/< 1 sec
 - oc_twofactor_totp_secrets
    0 [>---------------------------] < 1 sec
 - oc_user_transfer_owner
    0 [>---------------------------] < 1 sec
 - oc_users
 6/6 [============================] 100% < 1 sec/< 1 sec
 - oc_vcategory
    0 [>---------------------------] < 1 sec
 - oc_vcategory_to_object
    0 [>---------------------------] < 1 sec
 - oc_webauthn
    0 [>---------------------------] < 1 sec

In ExceptionConverter.php line 62:
                                                                                                                       
  An exception occurred while executing a query: SQLSTATE[42601]: Syntax error: 7 ERROR:  syntax error at or near ")"  
  LINE 1: ... setval('oc_preview_locations_id_seq', (SELECT MAX() FROM ))                                              
                                                                       ^                                               
                                                                                                                       

In Exception.php line 24:
                                                                           
  SQLSTATE[42601]: Syntax error: 7 ERROR:  syntax error at or near ")"     
  LINE 1: ... setval('oc_preview_locations_id_seq', (SELECT MAX() FROM ))  
                                                                       ^   
                                                                           

In Connection.php line 71:
                                                                           
  SQLSTATE[42601]: Syntax error: 7 ERROR:  syntax error at or near ")"     
  LINE 1: ... setval('oc_preview_locations_id_seq', (SELECT MAX() FROM ))  
                                                                       ^   
                                                                           

db:convert-type [--port PORT] [--password PASSWORD] [--clear-schema] [--all-apps] [--chunk-size CHUNK-SIZE] [--] <type> <username> <hostname> <database>

[szaimen]

cc @CarlSchwan

[joshtrichards]

How, precisely, did you provision the target PosgreSQL setup?

We do need to make this bit of code more robust, but the root cause is likely a schema matter:

See #58063 (comment)

[ClementJ35]

Hi,
I used the nextcloud AIO readme page : https://github.com/nextcloud/all-in-one/blob/main/migration.md#migrate-the-files-and-the-database

I'll retry with the piece of documentation you pointed and comment back.

[ClementJ35]

No luck, it still crashes at the same point with the same error. Here's what I did from the https://docs.nextcloud.com/server/33/admin_manual/configuration_database/linux_database_configuration.html#postgresql-database documentation :

• dropped the DB
• Added pgsql.ini tweaks
• recreate the DB exactly as the documentation stated

Still crashes.
I tried to drop the oc_preview_locations table as it is empty, even with previews generated, but it doesn't support it.

[rudiservo]

I have the same issue
An exception occurred while executing a query: SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error a t or near ")" LINE 1: SELECT setval('oc_jobs_id_seq', (SELECT MAX() FROM ))

Upgraded from v30 to v33, trying to convert from maria to pgsql for AIO, postgresql is version 18 oficial docker image.