Syntax Error on db:convert-type when converting any DB to ->PostgreSQL | NC 20

[manjman]

How to use GitHub

• Please use the 👍 reaction to show that you are affected by the same issue.
• Please don't comment if you have no relevant information to add. It's just extra noise for everyone subscribed to this issue.
• Subscribe to receive notifications on status change and new comments.

Steps to reproduce

1. occ db:convert-type --all-apps --password "somepw" pgsql user host db conversion from MariaDB to PGSQL

Expected behaviour

Migration should be successfull

Actual behaviour

Fatal Error with Syntax error in AbstractPostgreSQLDriver.php...

In AbstractPostgreSQLDriver.php line 54:
                                                                                                            
  An exception occurred while executing 'SELECT setval('oc_text_documents_id_seq', (SELECT MAX() FROM ))':  
                                                                                                            
  SQLSTATE[42601]: Syntax error: 7 ERROR:  syntax error at or near ")"                                      
  LINE 1: ...ECT setval('oc_text_documents_id_seq', (SELECT MAX() FROM ))                                   
                                                                       ^                                    
In PDOConnection.php line 83:
                                                                           
  SQLSTATE[42601]: Syntax error: 7 ERROR:  syntax error at or near ")"     
  LINE 1: ...ECT setval('oc_text_documents_id_seq', (SELECT MAX() FROM ))  
                                                                       ^   
                                                                           
In PDOConnection.php line 78:
                                                                           
  SQLSTATE[42601]: Syntax error: 7 ERROR:  syntax error at or near ")"     
  LINE 1: ...ECT setval('oc_text_documents_id_seq', (SELECT MAX() FROM ))  
                                                                       ^

Server configuration

Operating system:
CentOS 7
Web server:
Apache 2.4
Database:
MariaDB 10.4.17 // pgsql 11.10
PHP version:
php 7.4.13
Nextcloud version: (see Nextcloud admin page)
20.0.4.0
Updated from an older Nextcloud/ownCloud or fresh install:
updated
Where did you install Nextcloud from:
official package
Signing status:

Signing status

No errors have been found.

List of activated apps:

App list

Enabled:
  - accessibility: 1.6.0
  - activity: 2.13.4
  - audioplayer: 3.0.0
  - cloud_federation_api: 1.3.0
  - comments: 1.10.0
  - contactsinteraction: 1.1.0
  - dashboard: 7.0.0
  - dav: 1.16.2
  - federatedfilesharing: 1.10.2
  - federation: 1.10.1
  - files: 1.15.0
  - files_automatedtagging: 1.10.1
  - files_external: 1.11.1
  - files_pdfviewer: 2.0.1
  - files_rightclick: 0.17.0
  - files_sharing: 1.12.1
  - files_trashbin: 1.10.1
  - files_versions: 1.13.0
  - files_videoplayer: 1.9.0
  - firstrunwizard: 2.9.0
  - groupfolders: 8.2.0
  - groupquota: 0.1.4
  - logreader: 2.5.0
  - lookup_server_connector: 1.8.0
  - news: 15.1.0
  - notifications: 2.8.0
  - oauth2: 1.8.0
  - password_policy: 1.10.1
  - photos: 1.2.1
  - privacy: 1.4.0
  - provisioning_api: 1.10.0
  - ransomware_protection: 1.8.0
  - recommendations: 0.8.0
  - serverinfo: 1.10.0
  - settings: 1.2.0
  - sharebymail: 1.10.0
  - support: 1.3.0
  - survey_client: 1.8.0
  - systemtags: 1.10.0
  - text: 3.1.0
  - theming: 1.11.0
  - twofactor_backupcodes: 1.9.0
  - updatenotification: 1.10.0
  - user_ldap: 1.10.2
  - user_status: 1.0.1
  - viewer: 1.4.0
  - weather_status: 1.0.0
  - workflow_pdf_converter: 1.5.0
  - workflowengine: 2.2.0
Disabled:
  - admin_audit
  - encryption
  - nextcloud_announcements

Nextcloud configuration:

Config report

{
    "system": {
        "instanceid": "***REMOVED SENSITIVE VALUE***",
        "passwordsalt": "***REMOVED SENSITIVE VALUE***",
        "secret": "***REMOVED SENSITIVE VALUE***",
        "trusted_domains": [
            "x.x.x.x",
            "cloud.example.com",
            "x.x.x.x"
        ],
        "datadirectory": "***REMOVED SENSITIVE VALUE***",
        "overwrite.cli.url": "https:\/\/cloud.example.com",
        "htaccess.RewriteBase": "\/",
        "dbtype": "mysql",
        "version": "20.0.4.0",
        "dbname": "***REMOVED SENSITIVE VALUE***",
        "dbhost": "***REMOVED SENSITIVE VALUE***",
        "dbport": "",
        "dbtableprefix": "oc_",
        "dbuser": "***REMOVED SENSITIVE VALUE***",
        "dbpassword": "***REMOVED SENSITIVE VALUE***",
        "installed": true,
        "ldapIgnoreNamingRules": false,
        "ldapProviderFactory": "\\OCA\\User_LDAP\\LDAPProviderFactory",
        "maintenance": false,
        "theme": "",
        "loglevel": 2,
        "mail_smtpmode": "smtp",
        "mail_smtpauthtype": "AUTH",
        "mail_from_address": "***REMOVED SENSITIVE VALUE***",
        "mail_domain": "***REMOVED SENSITIVE VALUE***",
        "mail_smtphost": "***REMOVED SENSITIVE VALUE***",
        "mail_smtpport": "PORT",
        "updater.release.channel": "stable",
        "mysql.utf8mb4": true,
        "filelocking.enabled": true,
        "memcache.locking": "\\OC\\Memcache\\Redis",
        "memcache.distributed": "\\OC\\Memcache\\Redis",
        "memcache.local": "\\OC\\Memcache\\Redis",
        "redis": {
            "host": "***REMOVED SENSITIVE VALUE***",
            "port": 0,
            "timeout": 0,
            "password": "***REMOVED SENSITIVE VALUE***"
        },
        "quota_include_external_storage": true,
        "app_install_overwrite": [
            "admin_notifications"
        ],
        "trashbin_retention_obligation": "auto, 60"

Are you using external storage, if yes which one:
local/smb
Are you using encryption:
no
Are you using an external user-backend, if yes which one:
LDAP/ActiveDirectory

[wjdp]

Had similar issue, was able to work around this issue by disabling the "Text" app and omitting the --all-apps option when migrating.

Obvs by doing this you will loose any data in the text app (whatever that is).

---

Differences:

• Was going from sqlite → Postgres
• No LDAP
• Ubuntu/docker (linuxserver image)
• Using local external storage only

[Sync1011]

Had the same issue, was able to work around this issue by disabling the "Text" app and omitting the --all-apps option when migrating.

Obvs by doing this you will loose any data in the text app.

Thanks! This worked for me.

[ckujau]

Had the same issue, was able to work around this issue by disabling the "Text" app and omitting the --all-apps option when migrating.

I didn't want to believe it, but Indeed: both disabling the Text app and omitting the --all-apps option must be done, and the migration then confirms that, but finishes successfully:

[...]
Creating schema in new database
The following tables will not be converted:
oc_text_documents
oc_text_sessions
oc_text_steps
Please note that tables belonging to available but currently not installed apps
can be included by specifying the --all-apps option.
Continue with the conversion (y/n)? [n] y
oc_migrations
Skipping migrations table because it was already filled by running the migrations

Thanks for the workaround!

[ml31415]

Just wanted to add, that this happens with mysql->pgsql as well. I just ran into the same issue, so the topic might be misleading.

[enoch85]

Went ahead and changed the title.

[jkufner]

I found this problem too. The bug is in ./lib/private/DB/PgSqlTools.php, where $tableName = $sequenceInfo['table_name']; is false for some tables. I'm not sure how bad the problem is, but one if helped to skip sequences without table name. I guess these tables are not in use, or there is a bigger problem somewhere else :)

[szaimen]

There was an enhancement recently concerning the conversion. Please try again when NC21.0.3 is out and report back! Thanks! :)

[ghost]

This issue has been automatically marked as stale because it has not had recent activity and seems to be missing some essential information. It will be closed if no further activity occurs. Thank you for your contributions.

[autra]

There was an enhancement recently concerning the conversion. Please try again when NC21.0.3 is out and report back! Thanks! :)

it's closed, but I've tested it with nextcloud 21.0.3 with the text app activated, and it worked perfectly.