Unable to db:convert-type to pgsql: value too long for type character varying(250)

jamesstidard commented 4 years ago

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

php occ db:convert-type --all-apps pgsql user host database (migrating from sqlite to Postgres)

Expected behaviour

Successful migration.

Actual behaviour

The schema produced in the Postgres database doesn't allow records in the current database to be inserted.

I've redacted the values of the fields below just as it's potentially sensitive information. Though I have included the approx lengths of the longer strings.

An exception occurred while executing 'INSERT INTO "oc_filecache" ("fileid", "storage", "path",
  "path_hash", "parent", "name", "mimetype", "mimepart", "size", "mtime", "storage_mtime", "encryp
  ted", "unencrypted_size", "etag", "permissions", "checksum") VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?
  , ?, ?, ?, ?, ?, ?)' with params ["...", "...", "...275 length string here...", "...", "...", "... 252 length string here ...", "...", "...", "...", "...", "...", "...", "...", "...", "...", ""]:

  SQLSTATE[22001]: String data, right truncated: 7 ERROR:  value too long for type character varying(250)

I believe it to be the oc_filecache.name field to be the problem. The generated schema uses a varying(250) and the name of the file exceeds this.

Is it safe to manually edit this and then rerun the migration and have the migration use an existing schema? If so, how would I do this?

Thanks.

Server configuration

Operating system: Ubuntu 18.04.5 LTS

Web server: Docker Image: nextcloud:19.0.3

Database: Docker Image: postgres:12

PHP version: Docker Image: nextcloud:19.0.3 appears to use v7.4.10

Nextcloud version: (see Nextcloud admin page) 19.0.3

Updated from an older Nextcloud/ownCloud or fresh install: Started life as a v16 docker image and incrementally upgraded to v19

Where did you install Nextcloud from: https://hub.docker.com/_/nextcloud

Signing status:

Signing status

``` Login as admin user into your Nextcloud and access http://example.com/index.php/settings/integrity/failed paste the results here. ```

List of activated apps:

App list

``` If you have access to your command line run e.g.: www-data@9c1765f5416f:~/html$ php occ app:list Enabled: - accessibility: 1.5.0 - activity: 2.12.0 - announcementcenter: 3.8.1 - bruteforcesettings: 2.0.1 - cloud_federation_api: 1.2.0 - comments: 1.9.0 - contactsinteraction: 1.0.0 - dav: 1.15.0 - federatedfilesharing: 1.9.0 - federation: 1.9.0 - files: 1.14.0 - files_accesscontrol: 1.9.1 - files_pdfviewer: 1.8.0 - files_rightclick: 0.16.0 - files_sharing: 1.11.0 - files_trashbin: 1.9.0 - files_versions: 1.12.0 - files_videoplayer: 1.8.0 - firstrunwizard: 2.8.0 - logreader: 2.4.0 - lookup_server_connector: 1.7.0 - nextcloud_announcements: 1.8.0 - notifications: 2.7.0 - oauth2: 1.7.0 - onlyoffice: 5.0.0 - password_policy: 1.9.1 - photos: 1.1.0 - privacy: 1.3.0 - provisioning_api: 1.9.0 - recommendations: 0.7.0 - serverinfo: 1.9.0 - settings: 1.1.0 - sharebymail: 1.9.0 - support: 1.2.1 - survey_client: 1.7.0 - systemtags: 1.9.0 - text: 3.0.1 - theming: 1.10.0 - twofactor_backupcodes: 1.8.0 - updatenotification: 1.9.0 - viewer: 1.3.0 - workflowengine: 2.1.0 Disabled: - admin_audit - encryption - files_external - user_ldap ```

Nextcloud configuration:

Config report

``` www-data@9c1765f5416f:~/html$ php occ config:list system { "system": { "htaccess.RewriteBase": "\/", "memcache.local": "\\OC\\Memcache\\APCu", "apps_paths": [ { "path": "\/var\/www\/html\/apps", "url": "\/apps", "writable": false }, { "path": "\/var\/www\/html\/custom_apps", "url": "\/custom_apps", "writable": true } ], "instanceid": "***REMOVED SENSITIVE VALUE***", "passwordsalt": "***REMOVED SENSITIVE VALUE***", "secret": "***REMOVED SENSITIVE VALUE***", "trusted_domains": [ "10.1.62.101:9000", "nextcloud.internal.wv" ], "datadirectory": "***REMOVED SENSITIVE VALUE***", "dbtype": "sqlite3", "version": "19.0.3.1", "overwrite.cli.url": "http:\/\/10.1.62.101:9000", "installed": true, "mail_smtpmode": "smtp", "mail_smtpsecure": "ssl", "mail_sendmailmode": "smtp", "mail_from_address": "***REMOVED SENSITIVE VALUE***", "mail_domain": "***REMOVED SENSITIVE VALUE***", "mail_smtpauthtype": "LOGIN", "mail_smtpauth": 1, "mail_smtphost": "***REMOVED SENSITIVE VALUE***", "mail_smtpport": "465", "mail_smtpname": "***REMOVED SENSITIVE VALUE***", "mail_smtppassword": "***REMOVED SENSITIVE VALUE***", "maintenance": false, "loglevel": 2 } } ```

Are you using external storage, if yes which one: Docker volume binds.

Are you using encryption: yes/no

Are you using an external user-backend, if yes which one: LDAP/ActiveDirectory/Webdav/...

LDAP configuration (delete this part if not used)

LDAP config

``` With access to your command line run e.g.: sudo -u www-data php occ ldap:show-config from within your Nextcloud installation folder Without access to your command line download the data/owncloud.db to your local computer or access your SQL server remotely and run the select query: SELECT * FROM `oc_appconfig` WHERE `appid` = 'user_ldap'; Eventually replace sensitive data as the name/IP-address of your LDAP server or groups. ```

Client configuration

Browser:

Operating system:

Logs

Web server error log

``` Insert your webserver log here ```

Nextcloud log (data/nextcloud.log)

Nextcloud log

``` Insert your Nextcloud log here ```

Browser log

``` Insert your browser log here, this could for example include: a) The javascript console log b) The network log c) ... ```

kesselb commented 4 years ago

cc @nickvergessen @icewind1991

nickvergessen commented 4 years ago

can you please share the real strings?

jamesstidard commented 4 years ago

Hey @nickvergessen, thanks for the response. I redacted them initially, incase the file names were sensitive from my company's perspective. I asked for permission to share them now. This is a screenshot I grabbed of the error.

I no longer have the output but I ran the image above through a OCR, so hopefully helpful if you need access to the raw text (but be aware of potential OCR errors). I can rerun the migration if needed, just didn't want to put the nextcloud into maintenance mode and disrupting people if not necessary.

An exception occurred while executing 'INSERT INTO "oc_filecache" ("fileid", "storage", "path", "path_hash", "parent", "name", "mimetype", "mimepart", "size", "mtime", "storage_mtime", "encrypted", "unencrypted_size", "etag", "permissions", "checksum") VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ? , ?, ?, ?, ?, ?, ?)' with params ["233262", "2", "files_trashbin\/files\/Flexible Wave Power — AWS Ocean Energy — clean renewable marine energy — sub—sea wave power — power converter for remote communities — autonomous power generator — award—winning design — the future of wave power — Archimedes Waveswing_files.d1589439525", "c43148c4a0a0998a791f06de3d15651e", "102745", "Flexible Wave Power — AWS Ocean Energy — clean renewable marine energy — sub—sea wave power — power converter for remote communities — autonomous power generator — award—winning design — the future of wave power — Archimedes Waveswing_files.d1589439525", "2", "1", "1980096", "1589439478", "158943 9478", "0", "0", "5ebcebf66d435", "31", ""]: 
SQLSTATE[22001]: String data, right truncated: 7 ERROR: value too long for type character varying(250) 
In PDOStatement.php line 119: 
SQLSTATE[22001]: String data, right truncated: 7 ERROR: value too long for type character varying(250) 
In PDOStatement.php line 117: 
SQLSTATE[22001]: String data, right truncated: 7 ERROR: value too long for type character varying(250)

nickvergessen commented 4 years ago

yeah, it's 253 for me, but maybe the dashes were utf8 dashes before OCR, so it could be each dash is 2 chars in terms of the database.