Error converting DB type to Postgres

[RobinMcCorkell]

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?

[ghost]

@Xenopathic From what i know you can only convert from SQLite to either MySQL or PostgreSQL, not convert from MySQL to PostgreSQL. The tool was never designed to switch between MySQL and PostgreSQL.

[ghost]

Ok, i might be wrong. Confused this with the info that you can't go back from MySQL or PostgreSQL to SQLite: #6457

[PVince81]

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

[RobinMcCorkell]

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

[RobinMcCorkell]

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  
  50530T232228Z-22289@b89540b3cf33cd88.ics", "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

[piotr277]

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 )) ^

[RobinMcCorkell]

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

[piotr277]

@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.

[RobinMcCorkell]

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

[flixman]

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.

[grawlinson]

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

[ghost]

Quick workaround:

1. Export addressbooks (https://doc.owncloud.org/server/8.2/user_manual/pim/contacts.html#special-carddav-urls)
2. Delete all addressbooks
3. Run the migration
4. Re-import addressbooks
5. Profit

Same probably needs to be done for Calendars (https://doc.owncloud.org/server/8.2/user_manual/pim/calendar.html#special-caldav-urls)

[tuxmainy]

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();

[tuxmainy]

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

[ownclouders]

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.

[PVince81]

Convert-type is currently broken: #27075.

[lock]

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.