[Tickets #12712] H3->H5 Migration Postgres problems

[noreply at bugs.horde.org]

DO NOT REPLY TO THIS MESSAGE. THIS EMAIL ADDRESS IS NOT MONITORED.

Ticket URL: http://bugs.horde.org/ticket/12712
------------------------------------------------------------------------------
  Ticket             | 12712
  Created By         | skhorde at smail.inf.fh-bonn-rhein-sieg.de
  Summary            | H3->H5 Migration Postgres problems
  Queue              | Horde Base
  Version            | 5.1.2
  Type               | Bug
  State              | Unconfirmed
  Priority           | 1. Low
  Milestone          |
  Patch              | 1
  Owners             |
------------------------------------------------------------------------------


skhorde at smail.inf.fh-bonn-rhein-sieg.de (2013-09-26 13:19) wrote:

during our migration from Horde3 to Horde5 with a Postgresql database, I
have two problems with SQL commands:

a)
2013-09-25 13:05:09 CEST STATEMENT:  DROP SEQUENCE
turba_shares_share_id_seq CASCADE
2013-09-25 13:05:09 CEST ERROR:  "turba_shares_seq" is not a table
2013-09-25 13:05:09 CEST HINT:  Use DROP SEQUENCE to remove a sequence.

the script tries to drop a squence with DROP TABLE.
Tested on Postgres v8.4.17-0squeeze1.

b)
2013-09-25 13:11:46 CEST STATEMENT:  ALTER TABLE "horde_prefs" ALTER
COLUMN "pref_value" TYPE bytea
2013-09-25 13:11:46 CEST ERROR:  invalid input syntax for type bytea

Postgres cannot cast TEXT to BYTEA, whyever ... .
see
https://drupal.org/node/1031122#comment-6219708
http://www.postgresql.org/docs/8.3/static/datatype-binary.html
http://www.postgresql.org/docs/8.0/static/functions-binarystring.html

Tested on Postgres v8.4.17-0squeeze1 and 8.1.19-0etch1.

=============================================

Attached patch contains a workaround for a) in dropTable()  by using  
DROP SEQUENCE if the name ends in _seq.

For b) I extended Db/Adapter/Postgresql/Schema.php with a special  
conversation text->binary according link #1 above, which seems to be  
backed by the postgresql.org links. One could add the conversion to a  
ALTER TABLE command, but then I had to make the patch larger. The many  
backslashes are required, because first PHP and then SQL expands the  
backslashes a second time, hence, the E'\\\\' becomes plain '\'.



skhorde at smail.inf.fh-bonn-rhein-sieg.de (2013-09-26 13:19) uploaded:  
h5_migration_postgresql_SQL_errors.patch.bz2

http://bugs.horde.org/h/services/download/?app=whups&actionID=download_file&file=h5_migration_postgresql_SQL_errors.patch.bz2&ticket=12712&fn=%2Fh5_migration_postgresql_SQL_errors.patch.bz2