[horde] Problem with Horde Webmail 1.1.3 and PostgreSQL-8.2.9

Mon Sep 15 13:04:11 UTC 2008

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi!

I've just installed horde-webmail-1.1.3 and want to use it
together with a PostgreSQL database. Database version is
PostgreSQL-8.2.9, webserver is apache-1.3.41, php interpreter
version is 4.4.9

Setup went fine, but now I can't create any persisted object
like adressbooks, notes, etc.

I think the reason is a type mismatch between the DDL and
the SQL queries, which manifests itself in the following
log messages:
[...]
Sep 15 14:24:04 HORDE [error] [kronolith] MDB2 Error: unknown error: _doquery: [Error message: Could not execute statement]
[Last executed query: PREPARE mdb2_statement_pgsql_c5aed2008bcf541573d91a88a0032d2a (text, text, text, text, text, text, text, text)  AS INSERT INTO kronolith_shares (share_name, share_owner, attribute_name, perm_creator, perm_default, perm_guest, share_flags, share_id) VALUES ($1, $2, $3, $4, $5, $6, $7, $8)]
[Native message: ERROR:  column "perm_creator" is of type smallint but expression is of type text
ZEILE 1: ..._shares (share_name, share_owner, attribute_name, perm_creat...
~                                                              ^
TIP:  You will need to rewrite or cast the expression.]
~ [pid 19602 on line 894 of "/var/spool/httpd/htdocs/horde/lib/Horde/Share/sql.php"]
Sep 15 14:24:04 HORDE [error] [nag] MDB2 Error: unknown error: _doquery: [Error message: Could not execute statement]
[Last executed query: PREPARE mdb2_statement_pgsql_2db25889e8366daf3d30400daf838b9f (text, text, text, text, text, text, text, text)  AS INSERT INTO nag_shares (share_name, share_owner, attribute_name, perm_creator, perm_default, perm_guest, share_flags, share_id) VALUES ($1, $2, $3, $4, $5, $6, $7, $8)]
[Native message: ERROR:  column "perm_creator" is of type smallint but expression is of type text
ZEILE 1: ..._shares (share_name, share_owner, attribute_name, perm_creat...
~                                                              ^
TIP:  You will need to rewrite or cast the expression.]
~ [pid 19602 on line 894 of "/var/spool/httpd/htdocs/horde/lib/Horde/Share/sql.php"]
Sep 15 14:24:04 HORDE [error] [mnemo] MDB2 Error: unknown error: _doquery: [Error message: Could not execute statement]
[Last executed query: PREPARE mdb2_statement_pgsql_ba2fce42c0dbc760c887dc9bb41f2847 (text, text, text, text, text, text, text, text)  AS INSERT INTO mnemo_shares (share_name, share_owner, attribute_name, perm_creator, perm_default, perm_guest, share_flags, share_id) VALUES ($1, $2, $3, $4, $5, $6, $7, $8)]
[Native message: ERROR:  column "perm_creator" is of type smallint but expression is of type text
ZEILE 1: ..._shares (share_name, share_owner, attribute_name, perm_creat...
~                                                              ^
TIP:  You will need to rewrite or cast the expression.]
~ [pid 19602 on line 894 of "/var/spool/httpd/htdocs/horde/lib/Horde/Share/sql.php"]
Sep 15 14:24:04 HORDE [error] [turba] MDB2 Error: unknown error: _doquery: [Error message: Could not execute statement]
[Last executed query: PREPARE mdb2_statement_pgsql_2d9b545ca213360a3d950fb3517559da (text, text, text, text, text, text, text, text, text)  AS INSERT INTO turba_shares (share_name, share_owner, attribute_name, attribute_params, perm_creator, perm_default, perm_guest, share_flags, share_id) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)]
[Native message: ERROR:  column "perm_creator" is of type smallint but expression is of type text
ZEILE 1: ...e, share_owner, attribute_name, attribute_params, perm_creat...
~                                                              ^
TIP:  You will need to rewrite or cast the expression.]
~ [pid 19602 on line 894 of "/var/spool/httpd/htdocs/horde/lib/Horde/Share/sql.php"]
Sep 15 14:24:04 HORDE [error] [turba] MDB2 Error: unknown error: _doquery: [Error message: Could not execute statement]
[Last executed query: PREPARE mdb2_statement_pgsql_2d9b545ca213360a3d950fb3517559da (text, text, text, text, text, text, text, text, text)  AS INSERT INTO turba_shares (share_name, share_owner, attribute_name, attribute_params, perm_creator, perm_default, perm_guest, share_flags, share_id) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)]
[Native message: ERROR:  column "perm_creator" is of type smallint but expression is of type text
ZEILE 1: ...e, share_owner, attribute_name, attribute_params, perm_creat...
[...]

PostgreSQL itself logs the following messages (example):
[...]
ERROR:  column "perm_creator" is of type smallint but expression is of type text at character 207
HINT:  You will need to rewrite or cast the expression.
STATEMENT:  PREPARE mdb2_statement_pgsql_8b3c146eec1432e439c7d3ee9c5bda80 (text, text, text, text, text, text, text, text, text)  AS INSERT INTO turba_shares (share_name, share_owner, attribute_name, attribute_params, perm_creator, perm_default, perm_guest, share_flags, share_id) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)
[...]

If you look at the database, the turba_shares table was created like this:

horde=> \d turba_shares
~                             Table "public.turba_shares"
~      Column      |          Type          |                Modifiers
- ------------------+------------------------+-----------------------------------------
~ share_id         | integer                | not null default 0
~ share_name       | character varying(255) | not null default ' '::character varying
~ share_owner      | character varying(32)  | not null default ' '::character varying
~ share_flags      | smallint               | not null default 0
~ perm_creator     | smallint               | not null default 0
~ perm_default     | smallint               | not null default 0
~ perm_guest       | smallint               | not null default 0
~ attribute_name   | character varying(255) | not null default ' '::character varying
~ attribute_desc   | character varying(255) |
~ attribute_params | text                   |
Indexes:
~    "turba_shares_pkey_idx" PRIMARY KEY, btree (share_id)
~    "turba_shares_perm_creator_idx" btree (perm_creator)
~    "turba_shares_perm_default_idx" btree (perm_default)
~    "turba_shares_perm_guest_idx" btree (perm_guest)
~    "turba_shares_share_name_idx" btree (share_name)
~    "turba_shares_share_owner_idx" btree (share_owner)

Here, indeed the "perm_creator" column (as well as several others)
was created with type "smallint". This is consistent with the SQL
scripts provided in the horde-webmail distribution to create the
database. So it's wrong to try to insert text values into those
column(s).

Is this a known bug? (I haven't found anything in the horde TTS)
Is there a fix or workaround?

Thanks!

- - andreas

- --
Andreas Haumer                     | mailto:andreas at xss.co.at
*x Software + Systeme              | http://www.xss.co.at/
Karmarschgasse 51/2/20             | Tel: +43-1-6060114-0
A-1100 Vienna, Austria             | Fax: +43-1-6060114-71
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFIzl1JxJmyeGcXPhERApKbAKCRvC3Mfj0QaxegoTCyARVWbhxD0ACcDu28
OPGvVNjFCFkkbYAVJRihluM=
=Mw/1
-----END PGP SIGNATURE-----