Server: Database conversion: hangs forever

[nblock]

Steps to reproduce

1. Create an empty PostgreSQL database
2. Perform a database conversion: ./occ db:convert-type --password <password> --all-apps --clear-schema pgsql <username> <hostname> <database>
   

   Expected behaviour

A working database conversion.

Actual behaviour

The conversion hangs forever at oc_cards:

[snipped]
oc_activity
 4948/4948 [============================] 100%
oc_activity_mq
    0/0 [============================]   0%
oc_addressbooks
 8/8 [============================] 100%
oc_cards
  330/1162 [=======>--------------------]  28%

Server configuration

Operating system: Arch Linux

Web server: Nginx

Database: SQLite

PHP version: 7.0.7

Nextcloud version: 9.0.50

Updated from an older Nextcloud/ownCloud or fresh install: Updated, starting from ownCloud 3.0

Where did you install Nextcloud from: archive

Signing status (Nextcloud 9.0 and above):

No errors have been found.

List of activated apps:

Enabled:
  - activity: 2.2.1
  - admin_audit: 1.0.0
  - calendar: 1.2.2
  - comments: 0.2
  - contacts: 1.3.1.0
  - dav: 0.1.6
  - federatedfilesharing: 0.1.0
  - federation: 0.0.4
  - files: 1.4.4
  - files_pdfviewer: 0.8.1
  - files_sharing: 0.9.1
  - files_trashbin: 0.8.0
  - files_versions: 1.2.0
  - files_videoplayer: 0.9.8
  - firstrunwizard: 1.1
  - gallery: 14.5.0
  - notes: true
  - notifications: 0.2.3
  - provisioning_api: 0.4.1
  - systemtags: 0.2
  - tasks: true
  - templateeditor: 0.1
  - updatenotification: 0.1.0
Disabled:
  - encryption
  - external
  - files_external
  - files_texteditor
  - user_external
  - user_ldap

Are you using encryption: no

[MorrisJobke]

Is there anything in the log file?

[nblock]

Is there anything in the log file?

Nope

[nblock]

The postgres log has something interesting:

ERROR:  invalid input syntax for type bytea
STATEMENT:  INSERT INTO oc_cards ("id", "addressbookid", "carddata", "uri", "lastmodified", "etag", "size") VALUES ($1, $2, $3, $4, $5, $6, $7)

It might be related to https://github.com/owncloud/core/issues/24065. The converter crashes in the referenced issue. It hangs forever in my case.

[nblock]

I deleted problematic rows (those had large contact photos) and now the converter hits this entry:

  An exception occurred while executing 'INSERT INTO oc_cards ("id", "addressbookid", "carddata", "uri", "lastmodified", "etag", "size") VALUES (?, ?, ?, ?, ?, ?, ?)' with params ["347", "3", "BEGIN
:VCARD\r\nVERSION:3.0\r\nPRODID:-\/\/Apple Inc.\/\/iOS 9.2\/\/EN\r\nN:;Max;Mustermann;;\r\nFN:Max\r\nEMAIL;TYPE=WORK\\,PREF:max.mustermanngmx.at\r\nTEL;TYPE=CELL\\,PREF:1234 12345674\r\nREV:2016-
01-14T20:26:31+00:00\r\nUID:3943abc9-2941-4591-bb9d-d05f175b2dc1\r\nEND:VCARD", "3943abc9-2941-4591-bb9d-d05f175b2dc1.vcf", "1466510685", "adb832bfcd0c3115c723440128dd22c7", "254"]:  
  SQLSTATE[22P02]: Invalid text representation: 7 ERROR:  invalid input syntax for type bytea 

The good thing is: the converter now crashes and does no longer hang.

[nickvergessen]

Crashes means it throws an exception?

[nblock]

@nickvergessen Yes

[nblock]

I just tested the migration from sqlite to postgresql on Nextcloud 10.0.1 and it has the same issues as described above. Is there anything else I can provide?

[gjedeer]

I'm getting the same error when converting oc_cards from sqlite to pgsql. It looks like row type information is not being preserved in ConvertType.php in copyTable() where rows are being read from one database and written to the other. setParameter() can optionally take a 3rd argument, the type, but it's not being set.

But that's all I know, deducted this from stack traces, I know nothing about Doctrine or nextCloud code base and internet searches weren't helpful in figuring out how to retrieve the type information from source database. So no patch from me.

Having type information for binary columns would allow the database abstraction layer to properly escape the values before inserting them.

Stack trace: https://gist.github.com/gjedeer/603f9f882e272ac7ae9eb0d614a33fcb

[MorrisJobke]

@icewind1991 could you have a look?

[pkel]

I triggered the same behaviour while migrating from maria to postgres.

[nickvergessen]

I identified the problem, it's rather arbitrary that it happens with contacts.
It's actually related to the clob column we are using. Any table with such a column and the content \, causes the problem.

[gjedeer]

@nickvergessen thank you a lot! Hopefully I'll now be able to move to Postgres.