hyperion and mysql server on remote host - possible?

[martin.marcher]

Hello,

i'm (trying to) use hyperion and had a couple of quirks. Situation is
as follows:

mysql server: 10.200.2.10 (backend) (reference are set to the names in
the config files)
open-xchange install: 10.200.2.13 (office) (reference are set to the
names in the config files)

oxinstaller breaks at:

initializing database (step 2/2)
Server response:
 java.sql.SQLException: Server connection failure during transaction.
Due to underlying exception: 'java.net.SocketException:
java.lang.reflect.InvocationTargetException'.

** BEGIN NESTED EXCEPTION **

java.net.SocketException
MESSAGE: java.lang.reflect.InvocationTargetException

First the mysql auth stuff (which works fine imho)

$ mysql -u root -h [URL="http://10.200.2.10/"]10.200.2.10[/URL]
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 88
Server version: 5.0.32-Debian_7etch1-log Debian etch distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> Bye

$ mysql -u openxchange -h [URL="http://10.200.2.13/"]10.200.2.13[/URL]
ERROR 2003 (HY000): Can't connect to MySQL server on '[URL="http://10.200.2.13/"]10.200.2.13[/URL]' (111)

$ mysql -u openxchange -h backend
ERROR 1045 (28000): Access denied for user
'openxchange'@'[URL="http://office.openforce.com/"]office.openforce.com[/URL]' (using password: NO)

$ mysql -u openxchange -h backend -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 89
Server version: 5.0.32-Debian_7etch1-log Debian etch distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> Bye

the grants for mysql:

 $ mysql -u root -h backend -e "show grants for 'openxchange'@'office'"
+----------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for openxchange@office
                                                                  |
+----------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'openxchange'@'office' IDENTIFIED BY
PASSWORD '*xxxxx' WITH GRANT OPTION |
+----------------------------------------------------------------------------------------------------------------------------------------------------------+

Now here is what happens when I execute the oxinstaller script:

The interesting line is imho:

 133                      98 Query       INSERT INTO db_pool VALUES
(4,'jdbc:mysql://localhost/?useUnicode=true&characterEnc
oding=UTF-8&autoReconnect=true&useUnicode=true&useServerPrepStmts=false&useTimezone=true&serverTimezone=UTC&connectTime
   out=15000&socketTimeout=15000','com.mysql.jdbc.Driver','openexchange','xxx')

because when the install script then does:

 140                      98 Query       SELECT
url,driver,login,password,name,db_pool_id FROM db_pool where name =
'open-xc    hange-db' LIMIT 1

here it get's the info that the mysql server is on localhost where it
of course won't find anything listening. Manual change of said column
doesn't help as this is of course recreated on every run. grepping
around didn't lead me to where the localhost mysql url is inserted so
that I can change it. So i'm stuck here....

How do i tell the install script that i _don't_ want localhost but
backend as the `url` in `db_pool`?

[martin.marcher]

rest of log session

Rest of the session is here:

 1 070529 15:51:48 95 Connect root@office on
2 95 Query select @@version_comment limit 1
3 95 Query drop database if exists `configdb`
4 95 Query drop database if exists
`open-xchange-db`
5 95 Query GRANT ALL PRIVILEGES ON *.* TO
'openexchange'@'office' IDENTIFIED BY 'xxx' WITH GRA NT OPTION
6 95 Query create database `configdb`
7 95 Quit
8 96 Connect root@office on
9 96 Query flush privileges
10 96 Quit
11 97 Connect openexchange@office on configdb
12 97 Query select @@version_comment limit 1
13 97 Query CREATE TABLE configdb_sequence (
14 id INT4 UNSIGNED AUTO_INCREMENT,
15 PRIMARY KEY (id)
16 )
17 97 Query INSERT INTO configdb_sequence VALUES (0)
18 97 Query DROP PROCEDURE IF EXISTS get_configdb_id
19 97 Query CREATE PROCEDURE
get_configdb_id() NOT DETERMINISTIC MODIFIES SQL DATA
20 BEGIN
21 DECLARE identifier INT4 UNSIGNED;
22 SET identifier = 0;
23 SELECT id INTO identifier FROM configdb_sequence FOR UPDATE;
24 IF 0 = identifier THEN
25 INSERT INTO configdb_sequence (id) VALUES (identifier);
26 END IF;
27 SET identifier = identifier + 1;
28 UPDATE configdb_sequence SET id = identifier;
29 SELECT identifier;
30 END
31 97 Query CREATE TABLE db_pool (
32 db_pool_id INT4 UNSIGNED NOT NULL,
33 url varchar(255) NOT NULL,
34 driver varchar(128) NOT NULL,
35 login varchar(128) NOT NULL,
36 password varchar(128) NOT NULL,
37 hardlimit int,
38 max int,
39 initial int,
40 name varchar(128) NOT NULL,
41 PRIMARY KEY (db_pool_id),
42 INDEX (name)
43 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci
44 97 Query CREATE TABLE db_cluster (
45 cluster_id INT4 UNSIGNED NOT NULL,
46 read_db_pool_id INT4 UNSIGNED NOT NULL,
47 write_db_pool_id INT4 UNSIGNED NOT NULL,
48 weight INT4 UNSIGNED,
49 max_units INT4,
50 PRIMARY KEY (cluster_id),
51 FOREIGN KEY(write_db_pool_id) REFERENCES db_pool (db_pool_id)
52 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci
53 97 Query CREATE TABLE reason_text (
54 id INT4 UNSIGNED NOT NULL,
55 text VARCHAR(255) NOT NULL,
56 PRIMARY KEY (id)
57 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci
58 97 Query CREATE TABLE context (
59 cid INT4 UNSIGNED NOT NULL,
60 name VARCHAR(128) NOT NULL,
61 enabled tinyint(1),
62 reason_id INT4 UNSIGNED,
63 filestore_id INT4 UNSIGNED,
64 filestore_name VARCHAR(32),
65 filestore_login VARCHAR(32),
66 filestore_passwd VARCHAR(32),
67 quota_max int8,
68 PRIMARY KEY (cid),
69 INDEX (filestore_id)
70 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci
71 97 Query CREATE TABLE filestore (
72 id INT4 UNSIGNED NOT NULL,
73 uri VARCHAR(255) NOT NULL,
74 size INT8 UNSIGNED NOT NULL,
75 max_context INT4,
76 PRIMARY KEY (id),
77 INDEX (max_context),
78 CONSTRAINT filestore_uri_unique UNIQUE(uri)
79 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
80 97 Query CREATE TABLE server (
81 server_id INT4 UNSIGNED NOT NULL,
82 name varchar(255) NOT NULL,
83 PRIMARY KEY (server_id),
84 CONSTRAINT server_name_unique UNIQUE (name)
85 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
86 97 Query CREATE TABLE login2context (
87 cid INT4 UNSIGNED NOT NULL,
88 login_info varchar(128) NOT NULL,
89 PRIMARY KEY (`login_info`),
90 FOREIGN KEY(`cid`) REFERENCES context (`cid`)
91 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
92 97 Query CREATE TABLE context_server2db_pool (
93 server_id INT4 UNSIGNED NOT NULL,
94 cid INT4 UNSIGNED NOT NULL,
95 read_db_pool_id INT4 UNSIGNED NOT NULL,
96 write_db_pool_id INT4 UNSIGNED NOT NULL,
97 db_schema VARCHAR(32) NOT NULL,
98 PRIMARY KEY(`cid`, `server_id`),
99 INDEX (write_db_pool_id),
100 INDEX (server_id),
101 INDEX (db_schema),
102 FOREIGN KEY(`cid`) REFERENCES context (`cid`) ON DELETE
CASCADE ON UPDATE CASCADE
103 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci
104 97 Quit
105 070529 15:52:08 98 Connect openexchange@office on configdb
106 98 Query SET NAMES utf8
107 98 Query SET character_set_results = NULL
108 98 Query SHOW VARIABLES
109 98 Query SHOW COLLATION
110 98 Query SET autocommit=1
111 98 Query SET sql_mode='STRICT_TRANS_TABLES'
112 98 Query SELECT cid FROM context WHERE cid = 1
113 98 Query SELECT name,server_id FROM
server WHERE name = 'local'
114 98 Query SET autocommit=0
115 98 Query UPDATE configdb_sequence SET id=id+1
116 98 Query SELECT id FROM configdb_sequence
117 98 Query commit
118 98 Query SET autocommit=1
119 98 Query INSERT INTO server
(server_id,name) VALUES (2,'local')
120 98 Query SELECT id,uri FROM filestore
WHERE uri = 'file:///var/opt/open-xchange/filespool'
121 98 Query SET autocommit=0
122 98 Query UPDATE configdb_sequence SET id=id+1
123 98 Query SELECT id FROM configdb_sequence
124 98 Query commit
125 98 Query SET autocommit=1
126 98 Query INSERT INTO filestore
(id,uri,size,max_context) VALUES (3,'file:///var/opt/open-xch
ange/filespool',8796093022208,1000)
127 98 Query SELECT
url,driver,login,password FROM db_pool
128 98 Query SET autocommit=0
129 98 Query UPDATE configdb_sequence SET id=id+1
130 98 Query SELECT id FROM configdb_sequence
131 98 Query commit
132 98 Query SET autocommit=1
133 98 Query INSERT INTO db_pool VALUES
(4,'jdbc:mysql://localhost/?useUnicode=true&characterEnc
oding=UTF-8&autoReconnect=true&useUnicode=true&useServerPrepStmts=false&useTimezone=true&serverTimezone=UTC&connectTime
out=15000&socketTimeout=15000','com.mysql.jdbc.Driver','openexchange','xxx')
134 98 Query SET autocommit=0
135 98 Query UPDATE configdb_sequence SET id=id+1
136 98 Query SELECT id FROM configdb_sequence
137 98 Query commit
138 98 Query SET autocommit=1
139 98 Query INSERT INTO db_cluster VALUES
(5,0,4,100,1000)
140 98 Query SELECT
url,driver,login,password,name,db_pool_id FROM db_pool where name =
'open-xc hange-db' LIMIT 1
141 98 Query SELECT db_schema FROM
context_server2db_pool WHERE write_db_pool_id = 4 GROUP BY db
_schema
142 98 Query SET autocommit=0
143 98 Query commit
144 070529 15:52:12 98 Query rollback
145 98 Query SELECT
db_schema,write_db_pool_id FROM context_server2db_pool WHERE cid = 1
146 98 Query DELETE FROM
context_server2db_pool WHERE cid = 1
147 98 Query DELETE FROM login2context WHERE cid = 1
148 98 Query DELETE FROM context WHERE cid = 1
149 98 Query SET autocommit=1
150 070529 15:52:13 98 Query SELECT cid FROM context WHERE cid = 1
151 98 Query SELECT server_id FROM server
WHERE name='local'
152 98 Query SELECT
read_db_pool_id,write_db_pool_id,db_schema FROM context_server2db_pool
WHERE server_id=2 AND cid=1
153 98 Query SELECT
read_db_pool_id,write_db_pool_id,db_schema FROM context_server2db_pool
WHERE server_id=2 AND cid=1
154 070529 15:52:14 98 Query SELECT
read_db_pool_id,write_db_pool_id,db_schema FROM context_server2db_pool
WHERE server_id=2 AND cid=1
155 98 Query SELECT
read_db_pool_id,write_db_pool_id,db_schema FROM context_server2db_pool
WHERE server_id=2 AND cid=1
156 98 Query SELECT
read_db_pool_id,write_db_pool_id,db_schema FROM context_server2db_pool
WHERE server_id=2 AND cid=1
157 98 Query SELECT
read_db_pool_id,write_db_pool_id,db_schema FROM context_server2db_pool
WHERE server_i

d=2 AND cid=1

[aLiEnTxC]

i think you can edit the install script /opt/open-xchange/sbin/oxinstaller

OXDB_HOST=
or use
--oxdb-host=myhost

and change the command from mysql on line 144, 270, 277, 280, 287, 288

check also this files after install:
/opt/open-xchange/etc/groupware/configdb.properties
/opt/open-xchange/etc/admindaemon/configdb.properties

[martin.marcher]

i think you can edit the install script /opt/open-xchange/sbin/oxinstaller

OXDB_HOST=
or use
--oxdb-host=myhost

and change the command from mysql on line 144, 270, 277, 280, 287, 288

check also this files after install:
/opt/open-xchange/etc/groupware/configdb.properties
/opt/open-xchange/etc/admindaemon/configdb.properties

I changed that already but to no avail. If I hadn't done that I wouldn't have gotten that far as there are a couple of hardcoded localhost references in the oxinstaller script....

the configdb.properties files are btw generated from the oxinstaller script (at least the properties), so if you change that from localhost to something else and then run oxinstaller you will find that there is localhost in there again.

[aLiEnTxC]

try this diff

--- /opt/open-xchange/sbin/oxinstaller.orig     2007-05-30 16:02:31.000000000 +0200
+++ /opt/open-xchange/sbin/oxinstaller  2007-05-30 16:09:30.000000000 +0200
@@ -142,7 +142,7 @@

 checkDB() {
     echo "show databases" | \
-       mysql -h localhost -u $OXDB_USER -p${OXDB_PASS} | \
+       mysql -h $OXDB_HOST -u $OXDB_USER -p${OXDB_PASS} | \
        grep $1 >/dev/null && return 0
 }

@@ -267,25 +267,24 @@

 FILESPOOLPATH=/var/opt/open-xchange/filespool

-cat<<EOF | mysql -u root -p
+cat<<EOF | mysql -h $OXDB_HOST -u root -p
 drop database if exists \`${CONFIGDB}\`;
 drop database if exists \`${OXDB}\`;
 GRANT ALL PRIVILEGES ON *.* TO '$OXDB_USER'@'localhost' IDENTIFIED BY '$OXDB_PASS' WITH GRANT OPTION;
+FLUSH PRIVILEGES;
 create database \`${CONFIGDB}\`;
 EOF

-mysqladmin -u root -p flush-privileges
-
 cat $ADMINCONF/mysql/configdb.sql \
-    | mysql -h localhost -u $OXDB_USER -p${OXDB_PASS} configdb
+    | mysql -h $OXDB_HOST -u $OXDB_USER -p${OXDB_PASS} configdb
 my_echo "done"


 my_echo "Configuring services"

 for confd in $ADMINCONF $SERVERCONF; do
-    v_setprop readUrl "jdbc:mysql://localhost/configdb" ${confd}/configdb.properties
-    v_setprop writeUrl "jdbc:mysql://localhost/configdb" ${confd}/configdb.properties
+    v_setprop readUrl "jdbc:mysql://$OXDB_HOST/configdb" ${confd}/configdb.properties
+    v_setprop writeUrl "jdbc:mysql://$OXDB_HOST/configdb" ${confd}/configdb.properties

     v_setprop readProperty.1 "user=$OXDB_USER" ${confd}/configdb.properties
     v_setprop readProperty.2 "password=$OXDB_PASS" ${confd}/configdb.properties

[martin.marcher]

I think we are talking about different things

I already have that diff (and prepared it for a bug report so that the ox-dev guys can use it later)

My Problem is that configdb gets created (somewhat fine) but the value inserted is "jdbc:mysql://localhost/?useUnico...." which is read from the database during oxinstaller execution. that leads to the problem that it tries to connect to localhost.

the oxinstaller script doesn't have any references to localhost anymore (also created a new parameter OXINSTALL_HOST=`hostname -f` so that

GRANT ALL PRIVILEGES ON *.* TO '$OXDB_USER'@'localhost' IDENTIFIED BY '$OXDB_PASS' WITH GRANT OPTION;

now reads

GRANT ALL PRIVILEGES ON *.* TO '$OXDB_USER'@'$OXINSTALL_HOST' IDENTIFIED BY '$OXDB_PASS' WITH GRANT OPTION;

to get the privileges right.

I can't find where the localhost comoes from in the codepeace below, which is what is inserted into mysql during oxinstaller execution.

I'm pretty sure it's another script/tool that runs and generates these values and not oxinstaller (I think it's createcontext which, it seems, calls some java code which in turn inserts exactly that jdbc uri with the localhost reference, no idea where to change that....)

 INSERT INTO db_pool VALUES
(4,'jdbc:mysql://localhost/?useUnicode=true&characterEnc
oding=UTF-8&autoReconnect=true&useUnicode=true&useServerPrepStmts=false&useTimezone=true&serverTimezone=UTC&connectTime
   out=15000&socketTimeout=15000','com.mysql.jdbc.Driver','openexchange','xxx')

[Martin Heiland]

You may check the configdb for the following lines
/opt/open-xchange/etc/groupware/configdb.properties:
...
readUrl=jdbc:mysql://ext.ern.al.ip:3306/configdb
writeUrl=jdbc:mysql://ext.ern.al.ip:3306/configdb
...

This sets a pointer of the admindaemon to the configdb which may be an external host and/or database cluster.
The Admindaemon connects to that hosts and takes a look at

mysql> use configdb;
Database changed
mysql> select * from db_pool;
+------------+--------------------------------------------------------------------------------------------------------------
| db_pool_id | url | driver | login | password | hardlimit | max | initial | name |
+------------+--------------------------------------------------------------------------------------------------------------
| 3 | jdbc:mysql://ext.ern.al.ip/?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&useUnicode=true (...)
| 6 | jdbc:mysql://ext.ern.al.ip/?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&useUnicode=true (...)
+------------+--------------------------------------------------------------------------------------------------------------
2 rows in set (0.00 sec)

mysql>

to find the corresponding database servers. As this example is a distributed setup using a write and a read host (master/slave), it is configured like that in another table in configdb:

mysql> select * from db_cluster;
+------------+-----------------+------------------+--------+-----------+
| cluster_id | read_db_pool_id | write_db_pool_id | weight | max_units |
+------------+-----------------+------------------+--------+-----------+
| 4 | 6 | 3 | 50 | 10000 |
+------------+-----------------+------------------+--------+-----------+
1 row in set (0.00 sec)


As you can see here, only the configdb is configured on the OX Groupware/Admindaemon machine which points to further databases which is somewhat useful in a distributed setup where you have one configdb which points to all other ressources.

I am not sure this helps you at this point, but should make the whole context more clear.

[martin.marcher]

/opt/open-xchange/etc/groupware/configdb.properties:
...
readUrl=jdbc:mysql://ext.ern.al.ip:3306/configdb
writeUrl=jdbc:mysql://ext.ern.al.ip:3306/configdb

$ grep -i -R 'jdbc:' admindaemon/configdb.properties groupware/configdb.properties 
admindaemon/configdb.properties:readUrl=jdbc:mysql://backend.openforce.com/configdb
admindaemon/configdb.properties:writeUrl=jdbc:mysql://backend.openforce.com/configdb
groupware/configdb.properties:readUrl=jdbc:mysql://backend.openforce.com/configdb
groupware/configdb.properties:writeUrl=jdbc:mysql://backend.openforce.com/configdb

This sets a pointer of the admindaemon to the configdb which may be an external host and/or database cluster.
The Admindaemon connects to that hosts and takes a look at

That was how I found out why i still get a connection refused as posted initially, see above my none of my configdb.properties doesn't point to anything near localhost.

Btw. the CVS checkout I have does not honor configdb.properties but generates the values depending on the oxinstaller variables and writes them back to configdb.properties

I'll post a complete session including the oxinstaller script and configdb.properties files so that you can see what happens and that oxinstaller indeed can't directly know anything about 'localhost' it has to be somewhere deeper imho

[Rene Stach]

Hello,

i'm (trying to) use hyperion and had a couple of quirks. Situation is
as follows:

mysql server: 10.200.2.10 (backend) (reference are set to the names in
the config files)
open-xchange install: 10.200.2.13 (office) (reference are set to the
names in the config files)

As far as I know the current release of Hyperion is not designed for distributed setups with the groupware running on one server and the database running on another server. I am not sure if you can get this working though but I think some efforts and code changes are required.

René

[martin.marcher]

The forum is too limited in size I can't post all of the infos I'd like to. I opened a thread on the mailing list after I was unable to register in the first place (we use greylisting and you seem to only send the registration mail once) but it seems to be pretty quiet.

I'm pretty sure that there's more to it than simply changing configdb.properties. As it will be overwritten during oxinstaller, check that in the code, as I have seen you are from quality assurance. Why would I change configdb.properties if it readUrl and writeUrl are generated from oxinstaller? (Don't worry I tried to change it before executing oxinstaller, tried to leave it at the original all the same result - just a hint that a lot of hints here to check this file should rather point to oxinstaller. On the other hand maybe I got a bad CVS checkout but I upgraded and behaviour didn't change)

The original file says jdbc:mysql://localhost/configdb instead of this:

for confd in $ADMINCONF $SERVERCONF; do
    v_setprop readUrl "jdbc:mysql://$OXDB_HOST/configdb" ${confd}/configdb.properties
    v_setprop writeUrl "jdbc:mysql://$OXDB_HOST/configdb" ${confd}/configdb.properties
    v_setprop readProperty.1 "user=$OXDB_USER" ${confd}/configdb.properties
    v_setprop readProperty.2 "password=$OXDB_PASS" ${confd}/configdb.properties
    v_setprop writeProperty.1 "user=$OXDB_USER" ${confd}/configdb.properties
    v_setprop writeProperty.2 "password=$OXDB_PASS" ${confd}/configdb.properties
done

Also the grants part in the original script explicitely refers to localhost and not "hostname -f" to only grant access to configdb from the executing host.

Since my configdb.properties (neither admindaemon nor nor groupware) contain any localhost reference and neither does oxinstaller the localhost jdbc uri has to come from elsewhere....


This is where the configdb.properties file is overwritten (at least certain properties as posted in the codepiece from oxinstaller above)
/opt/open-xchange/etc/admindaemon/oxfunctions.sh

# usage:
# ox_set_property property value /path/to/file
# 
ox_set_property() {
    local prop="$1"
    local val="$2"
    local propfile="$3"
    test -z "$prop"     && die "ox_set_property: missing prop argument (arg 1)"
    test -z "$val"      && die "ox_set_property: missing val argument (arg 2)"
    test -z "$propfile" && die "ox_set_property: missing propfile argument (arg 3)"
    local tmp=${propfile}.tmp$$
    rm -f $tmp
    # quote & in URLs to make sed happy
    val="$(echo $val | sed 's/\&/\\\&/g')"
    # some values need quoting, so leave quotes, if already present
    local q=
    if grep -E "^.*$prop[:=].*\".*\".*$" $propfile >/dev/null; then
    q='"'
    fi
    if grep -E "^$prop" $propfile >/dev/null; then
    cat<<EOF | sed -f - $propfile > $tmp
s;\(^$prop[:=]\).*$;\1${q}${val}${q};
EOF
    if [ $? -gt 0 ]; then
        rm -f $tmp
        die "ox_set_property: FATAL: error setting property $prop to \"$val\" in $propfile"
    else
        mv $tmp $propfile
    fi
    else
    echo "${prop}=$val" >> $propfile
    fi
}

[martin.marcher]

As far as I know the current release of Hyperion is not designed for distributed setups with the groupware running on one server and the database running on another server. I am not sure if you can get this working though but I think some efforts and code changes are required.

René

seems our posts were at the same time didn't see it before.

ok that info gets me further. Searching for a howto to set up a dev environment