Skip to content

Database conversion: hangs forever #173

Closed
Closed
Database conversion: hangs forever#173
Labels
bugfeature: install and update
Milestone
Nextcloud 12.0

Description

@nblock
nblock
opened on Jun 20, 2016

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

Activity

  1. MorrisJobke commented on Jun 20, 2016

    @MorrisJobke
    MorrisJobke
    on Jun 20, 2016
    Member

    Is there anything in the log file?

  2. nblock commented on Jun 20, 2016

    @nblock
    nblock
    on Jun 20, 2016
    ContributorAuthor

    Is there anything in the log file?

    Nope

  3. nblock commented on Jun 20, 2016

    @nblock
    nblock
    on Jun 20, 2016
    ContributorAuthor

    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 owncloud/core#24065. The converter crashes in the referenced issue. It hangs forever in my case.

  4. nblock commented on Jun 21, 2016

    @nblock
    nblock
    on Jun 21, 2016
    ContributorAuthor

    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.

  5. nickvergessen commented on Sep 13, 2016

    @nickvergessen
    nickvergessen
    on Sep 13, 2016
    Member

    Crashes means it throws an exception?

  6. nickvergessen
    added
    needs info
    on Sep 13, 2016

  7. nblock commented on Sep 14, 2016

    @nblock
    nblock
    on Sep 14, 2016
    ContributorAuthor

    @nickvergessen Yes

  8. nblock commented on Nov 5, 2016

    @nblock
    nblock
    on Nov 5, 2016
    ContributorAuthor

    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?

  9. nickvergessen
    added
    bug
    feature: install and update
    on Nov 7, 2016

  10. gjedeer commented on Dec 22, 2016

    @gjedeer
    gjedeer
    on Dec 22, 2016

    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

  11. MorrisJobke commented on Jan 25, 2017

    @MorrisJobke
    MorrisJobke
    on Jan 25, 2017
    Member

    @icewind1991 could you have a look?

  12. pkel commented on Mar 22, 2017

    @pkel
    pkel
    on Mar 22, 2017

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

  13. nickvergessen commented on Mar 26, 2017

    @nickvergessen
    nickvergessen
    on Mar 26, 2017
    Member

    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.

  14. nickvergessen
    closed this as completedin #4071on Apr 4, 2017

  15. gjedeer commented on Apr 4, 2017

    @gjedeer
    gjedeer
    on Apr 4, 2017

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

  16. MorrisJobke
    added this to the Nextcloud 12.0 milestone on Apr 4, 2017
  17. MorrisJobke
    removed
    needs info
    on Apr 4, 2017
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    bugfeature: install and update

    Type

    No type

    Projects

    No projects

      Milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions