Verified.  Thanks Jan.  (Fixed a few unit tests while checking this so 
that's my contribution :) ).

Changes have been made in Git for this ticket:

[jan] Use binary column type for preference values (Bug #10803).

  3 files changed, 26 insertions(+), 6 deletions(-)
http://git.horde.org/horde-git/-/commit/0d5d516b1828b784eec145ea0c4e609dafb32b0f

It was probably overlooked during the change to migrations, that at 
some minor release the pref_value field was changed to BYTEA for 
Postrgres only. This left our databases a bit inconsistent, because 
the other DBs continued to use TEXT fields.
Theoretically this could be solved by migrating pref_value to 
"binary", though I'm not sure how well this handled by the databases 
and out drivers. I'm going to create a unit test and we need to see 
how that works across databases and versions.

BUMP/PING

DB Experts: need your opinion on how to fix immediately.

Figured this out.  The problem is that the Prefs library is declaring 
the value field as a *binary* field, and therefore it explicitly uses 
a Horde_Db_Value_Binary object.  So this object is internally calling 
quoteBinary before sending to the server.

Tracing the commit log, this commit broke things because it eliminated 
the 'BYTEA' definition for postrgres:

commit 6d2e2cf77674ba1dda28fcbe9c01e2a9745a5130
Author: Jan Schneider <jan@horde.org>
Date:   Fri Jan 28 18:05:33 2011 +0100

     Convert Horde_Prefs to migrations.


However, this later commit:

commit 9e5828c9e08a801d8cf4ac307da10d4ab129d436
Author: Chuck Hagenbuch <chuck@horde.org>
Date:   Sat Mar 12 20:47:41 2011 -0500

     Bug #9153: Standardize on using Horde_Db_Column::binaryToString() for now

     This isn't automatic-elegant, but it works. Also extend the 
binary tests to
     other backends, and fix double-escaping of percents in SQLite binary data.

now assumes that **ALL** DBs are treating a value as binary.  So it 
seems to be the proper solution would be to convert ALL databases to 
binary for the prefs field, since that is what the Prefs code directly 
requires, and has required since release 1.0.

As a fallback, we would have to create some pseudo-type for the 
prefs_field indicating it can be a text field, but only if it supports 
null characters (IIRC, this is why postgresql requires BYTEA).   
However, this would appear to involve significant BC headaches.

Bumping priority - this apparently breaks all postgresql installations 
so it needs to be resolved ASAP.

Then the migration scripts broke postgresql from Horde 3 -> Horde 4 
and need to be fixed.

This doesn't make sense though.  We only send escaped values if the 
column is identified as 'binary'.  So your prefs table is identified 
that column as binary, even though it isn't so it is not being 
correctly decoded when retrieving.  Or at least I think that is what 
the code is saying.  As I always note: the DB code makes no sense to 
me.  I have difficult tracing the code.

If the type of pref_value should not be text something in the 
installation or updating scripts must be wrong, I installed Horde 
Webmail afresh with pear when it was at 4.02 or 03, so that's when the 
database was created.

That's not what our migration scripts create though, unless *I* am 
missing something.

No it isn't.  Or at least it shouldn't be.  Postgresql can't handle 
PHP serialized data in a TEXT field - it requires a BLOB/BYTEA.  If 
this has been changed recently, this is a *major* regression.  See Bug 
#8130.

My prefs table is set for bytea data.

Just to be thorough: The database appears to pass the test
AllTests.php ->

OK, but incomplete or skipped tests!
Tests: 168, Assertions: 238, Skipped: 19.

I'm not looking at the code right now, but shouldn't this only happen 
with BLOB fields? pref_value is a TEXT field though.

Thanks for the feedback.

As far as I know the server is working fine, the difference between 
Horde working correctly or not is the downgrade from 1.1.2 to 1.1.1. I 
have upgraded and downgraded again to check this.

With 1.1.1 the values stored appear differently in clear text 
(a:2:{s:4:...) instead of the hex strings. As the only difference 
between the two states is the version of the pear module and the 
server appears to be working fine, I would not know where else to look 
for the cause of this.

This is exactly what is expected to be stored .  These values should 
be automatically converted when querying the DB by the PDO layer.   
Sure enough, NO code has changed involving *decoding* of the value 
from Postgresql from 1.1.1 to 1.1.2.  So your bug report doesn't make 
sense.

The only thing that has changed is the format of the data we pass to 
Postgresql.  But the unit tests verify this is working fine on a 9.1.1 
server.  So you should try running the Postgresql DB unit tests to see 
if they pass on your server.

After updating to Horde Webmail Edition 4.04 various variables 
(last_login, last_login_tasks, probably others) are not stored in the 
database (PostgreSQL 9.1.1) correctly.

Instead of the correct values strings appear with contents like: 
'\x613a323a.....' (hex coded ascii I assume)

Downgrading Horde_Db from 1.1.2 to 1.1.1 fixes this.