[Refdb-devel] [ refdb-Bugs-1584375 ] External database link not possible

[SourceForge.net <no...@so...>]

Bugs item #1584375, was opened at 2006-10-25 14:06
Message generated for change (Comment added) made by mhoenicka
You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=385991&aid=1584375&group_id=26091

Please note that this message will contain a full copy of the comment thread,
including the initial issue submission, for this request,
not just the latest update.
Category: refdbd
Group: None
Status: Open
Resolution: Works For Me
Priority: 5
Private: No
Submitted By: steletch (steletch)
Assigned to: Markus Hoenicka (mhoenicka)
Summary: External database link not possible

Initial Comment:
Unless i have not read correctly the manual, i think it
is not possible to have a separate refdb server and a
database server (be it mysql or postgresql).

It would be nice to be able to separate it since having
a separate database server would be nice (i.e. the
external database server can be on the internet and
refdbd on another machine). In fact, most providers
allow database functinalities but do not permit
software installation (refdb).

If i'm wrong, please tell me where i could correct it
please.

Cheers,

Stéphane

----------------------------------------------------------------------

>Comment By: Markus Hoenicka (mhoenicka)
Date: 2006-10-26 15:04

Message:
Logged In: YES 
user_id=85809

I would like to know what refdbd -l 7 says at the point
where you run "viewstat". Does that give any clue?

----------------------------------------------------------------------

Comment By: steletch (steletch)
Date: 2006-10-26 14:54

Message:
Logged In: YES 
user_id=1129476

To be clear...

I can connect to the external database with mysql -u -p,
i've checked via phpmyadmin or the mysql command line that
the database is created and correctly filled in 'as fas as i
can redad from the dump).

I lanched refdb with -l 7
I then launched refdba with -u -p -i as appropriate

The result of the command 'viewstat' is:
"could not open main database"

I'll check again tonight in case i missed any step in this
process.

I'll let you know after the test.


----------------------------------------------------------------------

Comment By: Markus Hoenicka (mhoenicka)
Date: 2006-10-26 14:38

Message:
Logged In: YES 
user_id=85809

 > refdbd  -i <database server ip> -b <database port> -V -u
<database admin> -w <database admin password> -s -e 0 -l 7
-d <database name>

The -u and -w switches are not required here, they are used
only if you run refdbd to check or update the main database.
If you run refdbd as an application server, the username and
passwords are always provided by the clients.

 > ??? Nothin tells me i have it :-(

Well, refdbd can't. As I said above, refdbd needs a client
connection providing the username and password in order to
even attempt to talk to a database engine. At this point,
i.e. right after startup, refdbd does not know whether or
not it can connect to the database engine.

 > refdba: viewstat
 > could not open main database

You need to use the same username+password in refdba as you
would in the mysql command line client. Can you indeed
connect from your local box to the remote MySQL server with
a command like this:

mysql -u <username> -p refdb

The permissions of the MySQL engine must allow the user in
question to connect to the particular database from your
local box. You may want to review the contents of the db,
host, and user tables of the mysql database in order to
check your permissions.

To make sure your setup is ok you should also look at the -l
7 output of refdbd when you run the viewstat command. You'll
see the connection parameters as they arrive at refdbd.


----------------------------------------------------------------------

Comment By: Nobody/Anonymous (nobody)
Date: 2006-10-25 23:53

Message:
Logged In: NO 

Well, i tried to follow your recommendations, but i'm pretty
missing something...

I tried to do:

 refdbd  -i <database server ip> -b <database port> -V -u
<database admin> -w <database admin password> -s -e 0 -l 7
-d <database name>

And i get :

dbi_driver_dir went to:

dbi is up using default driver dir
Available libdbi database drivers:
mysql
Requested libdbi driver found
Database directory:
/var/lib/refdb/db
application server started
share extended notes by default
use cm126109 as default database
use /tmp/refdbd_fifo9900 as fifo
server waiting n_max_fd=4

???
Nothin tells me i have it :-(

When i do:
 refdba
Please enter your password: 
refdba: set
serverip 127.0.0.1
port 9734
verbose f
pager stdout
username stephane
timeout 180
logfile /var/log/refdba.log
logdest 2
loglevel 6
refdblib 
 (i tried also by setting a username + password)

refdba: viewstat
could not open main database


So refdbd has not connected correclyt :-(

I've set up the database manually on the remote computer
(using the import mysql function for the appropriate
mysql41+ dump). I can see via phpmyadmin the database 's
structure is there, and from my local computer, i can
connect to the remote computer.

Anything i'm missing ?

Cheers,

Stéphane

----------------------------------------------------------------------

Comment By: Markus Hoenicka (mhoenicka)
Date: 2006-10-25 14:23

Message:
Logged In: YES 
user_id=85809

I'm sure that this is just a configuration problem as it was
one of the design goals to be able to separate the clients,
the application server, and the database engine physically
if you have any reasons to do so. refdbd (or rather the
libdbi driver it uses) basically acts as the database engine
client. In order to access the database engine on a
different machine, you need to tweak two refdbd settings:

- set the IP address of the box that runs your database
engine. Either set "serverip" in the refdbdrc config file,
or use the -i command line switch. Use the IP address or the
hostname if it can be resolved properly

- set the port where the database engine listens. Set
"dbsport" in refdbcrc, or use the -b command line switch
(*NOT* the more appealing -p switch which defines which port
refdbd listens on for client connections)

This should be all it takes on the RefDB side. Of course you
need to configure your database engine appropriately. E.g.
both MySQL and PostgreSQL listen to local connections only
by default for security reasons. You'll have to flip a
switch to make them serve external connections. Also, both
engines support host-based authentication. That is, you need
to set the database permissions properly to allow
connections from refdbd. It may be slightly confusing that
the host in question is the box where refdbd runs, not where
the client runs (because refdbd acts as the database engine
client, as mentioned before).


----------------------------------------------------------------------

You can respond by visiting: 
https://sourceforge.net/tracker/?func=detail&atid=385991&aid=1584375&group_id=26091