Skip to content

[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 )) #58715

Description

@ClementJ35

⚠️ This issue respects the following points: ⚠️

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>

Activity

  1. added theissue type on Mar 4, 2026
  2. szaimen commented on Mar 4, 2026

    @szaimen
    Contributor
  3. moved this from To triage to Triaged in 🧑‍🤝‍🧑 Community triageon Mar 4, 2026
  4. joshtrichards commented on Mar 4, 2026

    @joshtrichards
    Member

    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)

  5. ClementJ35 commented on Mar 5, 2026

    @ClementJ35
    Author

    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.

  6. ClementJ35 commented on Mar 5, 2026

    @ClementJ35
    Author

    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.

  7. rudiservo commented on Mar 5, 2026

    @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.

  8. added a commit that references this issue on Mar 6, 2026
    4c74e34
  9. samr7 commented on Mar 13, 2026

    @samr7

    Will comment that I was encountering something similar to this while trying to do db:convert-type to pgsql:

    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: SELECT setval('jobs_id_seq', (SELECT MAX() FROM ))
                                                              ^
    
    
    In Exception.php line 24:
    
      SQLSTATE[42601]: Syntax error: 7 ERROR:  syntax error at or near ")"
      LINE 1: SELECT setval('jobs_id_seq', (SELECT MAX() FROM ))
                                                              ^
    
    
    In Connection.php line 71:
    
      SQLSTATE[42601]: Syntax error: 7 ERROR:  syntax error at or near ")"
      LINE 1: SELECT setval('jobs_id_seq', (SELECT MAX() FROM ))
                                                              ^
    

    Applying #58762 got me past this and the conversion was successful. Thank you!

  10. ClementJ35 commented on Mar 23, 2026

    @ClementJ35
    Author

    Will comment that I was encountering something similar to this while trying to do db:convert-type to pgsql:

    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: SELECT setval('jobs_id_seq', (SELECT MAX() FROM ))
                                                              ^
    
    
    In Exception.php line 24:
    
      SQLSTATE[42601]: Syntax error: 7 ERROR:  syntax error at or near ")"
      LINE 1: SELECT setval('jobs_id_seq', (SELECT MAX() FROM ))
                                                              ^
    
    
    In Connection.php line 71:
    
      SQLSTATE[42601]: Syntax error: 7 ERROR:  syntax error at or near ")"
      LINE 1: SELECT setval('jobs_id_seq', (SELECT MAX() FROM ))
                                                              ^
    

    Applying #58762 got me past this and the conversion was successful. Thank you!

    Same here.

  11. mwdle commented on Apr 2, 2026

    @mwdle

    Ran into this same issue and applying #58762 also resolved it for me. Thanks!

  12. christoph-ehm commented on May 15, 2026

    @christoph-ehm

    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.

    The exception is thrown here:

    foreach ($conn->createSchemaManager()->listSequences() as $sequence) {
    $sequenceName = $sequence->getName();
    $sqlInfo = 'SELECT table_schema, table_name, column_name
    FROM information_schema.columns
    WHERE column_default = ? AND table_catalog = ?';
    $result = $conn->executeQuery($sqlInfo, [
    "nextval('$sequenceName'::regclass)",
    $databaseName
    ]);
    $sequenceInfo = $result->fetchAssociative();
    $result->free();
    /** @var string $tableName */
    $tableName = $sequenceInfo['table_name'];
    /** @var string $columnName */
    $columnName = $sequenceInfo['column_name'];
    $sqlMaxId = "SELECT MAX($columnName) FROM $tableName";
    $sqlSetval = "SELECT setval('$sequenceName', ($sqlMaxId))";
    $conn->executeQuery($sqlSetval);

    Exactly the same exception that I get. The "oc_jobs" table does not have/get a sequence as id (like a few other tables in the nextcloud database) but it wants the table to have an id column with a sequence as (default) value in order to set its current value to the maximum id in the table. The default value is actually empty. ChatGPT suggests, the ids are probably pasted over (they are not contiguous anyways) and I assume, this is right (why would you set the sequence's current value otherwise?).

    • Simple stupid fix (for hackers): don't set a value to the sequence, if there is no sequence. Skipping this query: SELECT setval('oc_jobs_id_seq', (SELECT MAX() FROM )).

      After executing the first query for the sequence's name, I inserted this:

      if ($result->num_rows == 0) continue;

      The only effect is, it gives me no exception at the end. I don't know if it results in a usable migration or not.

    • Better: change $conn->createSchemaManager()->listSequences() to return only valid sequences. (I did not look at other code, so maybe there needs to be something else to update when the ids are not id_seqs.)

    PS: I added the line above and finished the migration and the files show up after the migration. Containers and stuff are back running and I can open nextcloud but as one may have expected, it runs into errors as soon as I try to make changes to the stored filesystem.

  13. christoph-ehm commented on May 20, 2026

    @christoph-ehm

    I have installed the 32.0.8 version manually (from source archive) and I get no problems with the OCC command. I installed several additional Apps but basically, the stored data files are about as empty as in my initial attempt (2 directories and some config files which were added for a test).

    (My idea was to reproduce the error in the installed version that we use in production to add an else-case that computes a fallback value if the query returns no rows.)

  14. Peter1994 commented on Nov 7, 2026

    @Peter1994

    I encountered a similar issue while converting a production Nextcloud instance from MySQL to PostgreSQL. Eventually migrating the instance to Nextcloud AIO using the migration documentation.

    Environment:

    • Nextcloud Server: 34.0.5.1
    • Source database: MySQL 8.4
    • Target database: PostgreSQL 16
    • Installation: Docker Compose
    • Database conversion performed using occ db:convert-type

    Command used:

    docker exec -it --user www-data nextcloud-nc-frontend-1 \
      php occ db:convert-type \
      --clear-schema \
      pgsql ncadmin nc-postgres-db nextcloud_db \
      --port=5432

    The conversion proceeded through the database tables, including the large oc_filecache table containing 2,174,613 records.

    However, it eventually failed with the following error:

    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: ...T setval('oc_preview_versions_id_seq', (SELECT MAX() FROM ))
                                                                           ^
    
    
    In Exception.php line 24:
    
      SQLSTATE[42601]: Syntax error: 7 ERROR:  syntax error at or near ")"
      LINE 1: ...T setval('oc_preview_versions_id_seq', (SELECT MAX() FROM ))
                                                                           ^
    
    
    In Connection.php line 71:
    
      SQLSTATE[42601]: Syntax error: 7 ERROR:  syntax error at or near ")"
      LINE 1: ...T setval('oc_preview_versions_id_seq', (SELECT MAX() FROM ))
                                                                           ^
    
    
    db:convert-type [--port PORT] [--password PASSWORD] [--clear-schema] [--all-apps] [--chunk-size CHUNK-SIZE] [--] <type> <username> <hostname> <database>
    

    Despite the final error, the target PostgreSQL database contained data:

    • oc_users: 5 users
    • oc_filecache: 2,174,613 records
    • oc_share: 1 record

    The original MySQL database remained intact, and the Nextcloud configuration was not switched to PostgreSQL.

    I was unable to complete the migration and rolled back to the original MySQL-based installation.

    Hopefully this additional reproduction confirms that the issue also affects Nextcloud 34.0.5.1.

  15. poodle64 commented on Nov 21, 2026

    @poodle64

    A minimal reproduction on a fresh install with stock images, in case it helps answer the question above about how the target was provisioned: it fails the same way with the target created by the official postgres image and nothing else, on both current releases.

    Versions: nextcloud:34-apache (34.0.6) and nextcloud:35-apache (35.0.3), mariadb:11.8 (11.8.9), postgres:18 (18.7).

    docker network create ncconv
    docker run -d --name db --network ncconv -e MARIADB_ROOT_PASSWORD=rootpass \
      -e MARIADB_DATABASE=nextcloud -e MARIADB_USER=nextcloud -e MARIADB_PASSWORD=ncpass mariadb:11.8
    docker run -d --name pg --network ncconv \
      -e POSTGRES_USER=nextcloud -e POSTGRES_PASSWORD=ncpass -e POSTGRES_DB=nextcloud postgres:18
    docker run -d --name nc --network ncconv -e MYSQL_HOST=db -e MYSQL_DATABASE=nextcloud \
      -e MYSQL_USER=nextcloud -e MYSQL_PASSWORD=ncpass \
      -e NEXTCLOUD_ADMIN_USER=admin -e NEXTCLOUD_ADMIN_PASSWORD=adminpass nextcloud:34-apache
    # wait until `docker exec -u www-data nc php occ status` reports installed: true,
    # optionally add a few files with `occ files:put`, then:
    docker exec -u www-data nc php occ db:convert-type --all-apps --clear-schema -n \
      --password ncpass pgsql nextcloud pg nextcloud

    Result (identical on 34.0.6 and 35.0.3), exit code 7, after every table has been copied:

    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: SELECT setval('oc_jobs_id_seq', (SELECT MAX() FROM ))
    

    config.php is left on dbtype mysql, and convert-type's catch turns maintenance mode off again.

    The target schema that createSchema() builds from the migrations always holds four sequences that no column's default uses:

    $ psql -U nextcloud -d nextcloud -Atc "select sequencename from pg_sequences s where schemaname='public' and not exists (select 1 from information_schema.columns c where c.column_default = 'nextval(''' || s.sequencename || '''::regclass)') order by 1"
    oc_jobs_id_seq
    oc_preview_locations_id_seq
    oc_preview_versions_id_seq
    oc_previews_id_seq
    

    (oc_jobs.id has no default and is not an identity column.) Where oc_jobs_id_seq comes from: core/Migrations/Version33000Date20251124110529.php calls $schema->dropAutoincrementColumn('jobs', 'id'), and SchemaWrapper::dropAutoincrementColumn removes the autoincrement but drops the backing sequence only on Oracle, so on PostgreSQL the sequence stays behind. PgSqlTools::resynchronizeDatabaseSequences then iterates every sequence, finds no information_schema.columns row for nextval('oc_jobs_id_seq'::regclass), and builds SELECT MAX() FROM from the empty result. #58762 skips such sequences; dropping the sequence in dropAutoincrementColumn on PostgreSQL as well, as it already does on Oracle, would also stop new installs and upgraded instances from carrying them.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Type

    Projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions