On Mon, 2004-03-29 at 18:28, Lee Brink wrote: [.........] > > I'm open to any suggestions on what's going on, and potential avenues to > debug. I'm currently hitting a brick wall. > > Lee > > -
Hello Lee
We were using oracle with Squirrelmail until january but we are using
postgreSQL now for saving addressbook/userprefs and sessions (better
speed and less resources).
I remember we had some problems with apache/php and with SM when using
oracle. Supposing that you have a working installation of apache/PHP
w/oracle support, you need to define some env variables in apache
(without this it did not work). We updated our 'apachectl' file with
these values:
-------------------------------------
ORACLE_VERSION=8.1.7
ORACLE_HOME=/site/oracle/product/${ORACLE_VERSION}
LD_LIBRARY_PATH=${ORACLE_HOME}/lib:/site/lib
NLS_LANG=american_norway.we8iso8859p1
export ORACLE_HOME LD_LIBRARY_PATH NLS_LANG
-------------------------------------
ORACLE_HOME and LD_LIBRARY_PATH are the important ones. You have to
assign to them the values in your system.
We used NLS_LANG too because we had problems with norwegian vocals.
Note: You have to remember to restart apache everytime you start/stop
your database.
I remember we had to change some code in squirrelmail too. (I am not
sure if this was a misconfiguration of our oracle DB). The problem was
that we had to use uppercase letters when using a key value to access
the oracle DB. In SM-1.2.11 we had to use:
In functions/db_prefs.php:
$row['PREFKEY']
$row['PREFVAL']
instead of:
$row['prefkey']
$row['prefval']
In functions/abook_database.php:
$row['NICKNAME']
$row['FIRSTNAME']
$row['LASTNAME']
$row['EMAIL']
$row['LABEL']
instead of:
$row['nickname']
$row['firstname']
$row['lastname']
$row['email']
$row['label']
Another important thing we found out is a missing index in the
'userprefs' table. In doc/db-backend.txt 'userprefs' is defined like
this for postgreSQL:
--------------------------------------
CREATE TABLE "userprefs" (
"username" varchar(128) NOT NULL,
"prefkey" varchar(64) NOT NULL,
"prefval" text,
CONSTRAINT "userprefs_pkey" PRIMARY KEY ("prefkey", "username")
);
--------------------------------------
In our system we use:
--------------------------------------
CREATE TABLE userprefs (
username character varying(128) NOT NULL,
prefkey character varying(128) NOT NULL,
prefval text
);
ALTER TABLE ONLY userprefs ADD CONSTRAINT userprefs_pkey PRIMARY KEY
(prefkey, username);
CREATE INDEX userprefs_username_index ON userprefs USING btree
(username);
--------------------------------------
Very important to have an index for the column 'username'. Without it
the system just gets slower and slower. We have today aroung 90.000
entries in our 'userprefs' table. With this index the load in our DB
server when down from 3.00-4.00 to 1.00 and every action in squirrelmail
happens almost instantaneously instead of using 3-4 seconds.
I hope this will help.
with regards
--
Rafael Martinez, <[EMAIL PROTECTED]>
Center for Information Technology Services
University of Oslo, Norway
signature.asc
Description: This is a digitally signed message part
