Error converting DB type to Postgres

@icewind1991

My current ownCloud installation uses MySQL, and I wanted to use the occ db:convert-type command to convert it to using PostgreSQL. The command runs successfully for many tables, but fails when copying data in the oc_cards table, with a 'Invalid input syntax for type bytea' error.

Full error here:

[Doctrine\DBAL\Exception\DriverException]                                                                                                                                                   
  An exception occurred while executing 'INSERT INTO oc_cards ("id", "addressbookid", "carddata", "uri", "lastmodified", "etag", "size") VALUES (?, ?, ?, ?, ?, ?, ?)' with params ["22", "4  
  ", "BEGIN:VCARD\r\nVERSION:3.0\r\nPRODID:-\/\/ownCloud\/\/NONSGML Contacts 0.3.0.18\/\/EN\r\nUID:C6A69A66-EA20-0001-16CF-980418E0D300\r\n<redacted>\r\nEND:VCARD\r\n", "C6A69A66-EA00-0001-3593-1BC012FD2AA0.vcf", "1460505773", "da3c04e952f171051d80a4  
  9145582dd3", "256"]:                                                                                                                                                                        
  SQLSTATE[22P02]: Invalid text representation: 7 ERROR:  invalid input syntax for type bytea

Oddly, it managed to copy 22 records from oc_cards before hitting this error, and I've tried twice now and both times it was on this record.

Running ownCloud 9.0.1 and PostgreSQL 9.5.2. I've also tried changing the standard_conforming_strings option from on to off, but no difference.

@icewind1991 You know PostgreSQL, any ideas?

@Xenopathic it more likely looks like one of your vcards contains special characters. Or something is not escaped properly when reading the values ?

The only special character in that vcard compared to others is a literal backlash. Strange why that's causing issues, they are getting correctly escaped (double backslash) when being passed to Postgres

EDIT: Actually I lie, there's also commas, but I strongly doubt that is causing the problems

Yeah, looks like it's the backslash. I modified that failing entry to remove it, and now a different entry in oc_calendarobjects is failing (this one I don't need to redact):

An exception occurred while executing 'INSERT INTO oc_calendarobjects ("id", "calendardata", "uri", "calendarid", "lastmodified", "etag", "size", "componenttype", "firstoccurence", "last  
  occurence", "uid") VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)' with params ["1", "BEGIN:VCALENDAR\r\nVERSION:2.0\r\nPRODID:-\/\/bitfire web engineering\/\/DAVdroid 0.8.0 (ical4j 1.0.x)\/\/  
  EN\r\nBEGIN:VTODO\r\nDTSTAMP:20150630T002641Z\r\nUID:20150530T232228Z-22289@b89540b3cf33cd88\r\nCREATED:20150530T232128Z\r\nLAST-MODIFIED:20150630T002538Z\r\nSUMMARY:Maths\r\nDESCRIPTION  
  :\\n[x] Unit M5\\n[x] Unit D1\\n[x] Unit D2\\n[x] Unit S2\r\nPRIORITY:1\r\nSTATUS:COMPLETED\r\nCOMPLETED:20150630T002538Z\r\nPERCENT-COMPLETE:100\r\nEND:VTODO\r\nEND:VCALENDAR\r\n", "201  
  [email protected]", "3", "1460505807", "b182e443b3145eb5b949985e7ec0ac51", "429", "VTODO", null, null, "20150530T232228Z-22289@b89540b3cf33cd88"]:                   
  SQLSTATE[22P02]: Invalid text representation: 7 ERROR:  invalid input syntax for type bytea

Notice the entry contains (literal) \n, which gets encoded as \\n during the INSERT

The same issue happens to me. Running ownCloud 9.0.1 and PostgreSQL 9.4. I'm trying to convert from sqlite3 to pgsql. After I deleted data in oc_cards and oc_calendarobjects tables it stops now somewhere in oc_preferences I guess..
Here is the db:convert-type output:
[Doctrine\DBAL\Exception\SyntaxErrorException] An exception occurred while executing 'SELECT setval('oc_oc_comments_pb2dqh9ra2tkh_id_seq', (SELECT MAX() FROM ))': SQLSTATE[42601]: Syntax error: 7 BŁĄD: błąd składni w lub blisko ")" LINE 1: ...'oc_oc_comments_pb2dqh9ra2tkh_id_seq', (SELECT MAX() FROM )) ^

@piotr277

@piotr277 That looks like a different issue, I'd suggest opening a new issue report

@Xenopathic These may be connected. I've encountered the same problem as you

SQLSTATE[22P02]: Invalid text representation: 7 ERROR:

until I've removed data from oc_cards and oc_calendarobjects tables.
Moreover I confirm that

SQLSTATE[22P02]: Invalid text representation: 7 ERROR:

happens not only during conversion: mysql > pgsql, but sqlite > pgsql as well.

SQLSTATE[42601]: Syntax error: 7 - It's different.

Have you been able to success in migrating from mysql to postgresql? I'm exactly in the same situation, facing the same problem, and have not been able to find any good answer.

Any fix for this issue? I'm running into it right now while trying to convert between mysql and postgresql.

  [Doctrine\DBAL\Exception\DriverException]
  An exception occurred while executing 'INSERT INTO oc_cards ("id", "addressbookid", "carddata", "uri", "lastmodified", "etag", "size") VALUES (?,
   ?, ?, ?, ?, ?, ?)' with params ["53", "2", "BEGIN:VCARD\r\nVERSION:4.0\r\nUID:a027d6f3-5427-4ac1-9511-ac9eec147e21\r\nPRODID:+\/\/IDN bitfire.at
  \/\/DAVdroid\/1.0.8 vcard4android ez-vcard\/0.9.9\r\nFN:FULL NAME\r\nN:LAST NAME;FIRST NAME;;;\r\nTEL;TYPE=cell:+CELLPH\r\nEMAIL;TYPE=h
  ome:EMAIL\r\nEMAIL:EMAIL\r\nNICKNAME:NICK \r\nADR;LABEL=\"STRING 1\\nSTRING 2\\nSTRING 3\";TYPE=\r\n home:;;STRING 1;STRING 2;STRING 3;STRING 4;STRING 5\r\nREV:20160501T211015Z\r\nEND:VCARD\r\n", "a027d6f3-
  5427-4ac1-9511-ac9eec147e21.vcf", "1462137014", "084c6e44c54a9eb840b5d427e31e9c9c", "484"]:
  SQLSTATE[22P02]: Invalid text representation: 7 ERROR:  invalid input syntax for type bytea

  [Doctrine\DBAL\Driver\PDOException]
  SQLSTATE[22P02]: Invalid text representation: 7 ERROR:  invalid input syntax for type bytea

  [PDOException]
  SQLSTATE[22P02]: Invalid text representation: 7 ERROR:  invalid input syntax for type bytea

Quick workaround

Even on small installations neither quick nor a workaround :)

I added a str_replace() to the conversion. I don't know if it breaks anything else but at least the conversion finished without errors and data is accessable. Maybe someone from OC team could have a look at this? Is some escaping of the values done before to avoid SQL syntax issues?

--- core/Command/Db/ConvertType.php.orig        2017-04-22 17:27:11.925623423 +0200
+++ core/Command/Db/ConvertType.php     2017-04-22 17:38:24.410917755 +0200
@@ -302,6 +302,10 @@
                                }
 
                                foreach ($row as $key => $value) {
+                                       if (is_string($value)) {
+                                               $value = str_replace('\\', '\\\\', $value);
+                                       }
+
                                        $insertQuery->setParameter($key, $value);
                                }
                                $insertQuery->execute();

The sequences of my postgres (target of db conversion) where not resynchronized resulting in dup key errors while trying to create a (e.g.) new calendar entry. But I don't understand how this is possible during conversion without an error. I did:

sudo -u apache ./occ db:convert-type --clear-schema --port=5432 --password=foo pgsql owncloud localhost owncloud

Anyway, attached is a PHP script I used to fix the sequences. Looking into ConvertType.php offers a better solution ;)

fix_oc_seqs.php.txt

Hey, this issue has been closed because the label status/STALE is set and there were no updates for 7 days. Feel free to reopen this issue if you deem it appropriate.

(This is an automated comment from GitMate.io.

Convert-type is currently broken: #27075.

This thread has been automatically locked since there has not been any recent activity after it was closed. Please open a new issue for related bugs.

Navigation Menu

Repository navigation

Error converting DB type to Postgres #24065

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Error converting DB type to Postgres #24065

Description

Activity

PVince81 commented on Apr 21, 2016

RobinMcCorkell commented on Apr 21, 2016

RobinMcCorkell commented on Apr 21, 2016

piotr277 commented on Apr 26, 2016

RobinMcCorkell commented on Apr 26, 2016

piotr277 commented on Apr 26, 2016

RobinMcCorkell commented on Apr 26, 2016

flixman commented on May 13, 2016

grawlinson commented on Jun 18, 2016

tuxmainy commented on Apr 22, 2017

tuxmainy commented on Apr 22, 2017

ownclouders commented on Dec 21, 2017

PVince81 commented on Dec 21, 2017

lock commented on Jul 31, 2019

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions