hyperion and mysql server on remote host - possible?
Martin Marcher 29 May 2007 21:06:45
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'.
First the mysql auth stuff (which works fine imho)
$ mysql -u root -h 10.200.2.10 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 10.200.2.13 ERROR 2003 (HY000): Can't connect to MySQL server on '10.200.2.13' (111)
$ mysql -u openxchange -h backend ERROR 1045 (28000): Access denied for user 'openxchange'@'office.openforce.com' (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`?
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_id=2 AND cid=1
