[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-11-02 09:20

Message:
Logged In: YES 
user_id=85809

There is a configure switch to set the main database name
(--with-main-db=name), but this doesn't solve your problem
entirely. As far as I understand you need to run RefDB using
a single database, rather than with a main database and one
or more reference databases. I have to admit that I have not
anticipated any need for doing this. Unfortunately both the
main database and the reference databases contain a table
"t_meta" with identical columns. I'd have to make the main
database meta table name configurable too to avoid the name
clash. Even then some RefDB commands are likely to fail,
e.g. the createdb command, as it will attempt to create a
database that already exists, instead of just adding columns
to it. You'd have to create the reference database tables
from a SQL script (which RefDB provides) instead of using
the createdb command. You'd also have to refrain from using
the main database maintenance mode of refdbd as it would
delete your reference data.

I think I could provide (fragile) support for this feature
in one of the next prereleases for further testing. Adding
robust support may require more thorough changes which I
could add at a later time.

Would you mind submitting a feature request for this? After
all, it's not a bug but rather a limitation of RefDB.

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

Comment By: steletch (steletch)
Date: 2006-10-31 18:18

Message:
Logged In: YES 
user_id=1129476

Hum, that's bad..

I've checked on my ISP and i cannot (at least for now)
change the database name. I've asked him why and if it could
be at least aliased, but no answer yet.

Could it be possible to get a parameter for specifying the
database name (for customizing refdb to the one provided by
my ISP).

I'm ok for a personal hack if tests are needed ...

And for the non existing message, i've no clue, but i've
carefully checked and nothing happens on the server side.

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

Comment By: Markus Hoenicka (mhoenicka)
Date: 2006-10-27 09:24

Message:
Logged In: YES 
user_id=85809

 > Note that on the remote host, the database name is *not*
 > refdb, and the password is *not* refdb.

That pretty much explains why your setup can't work. Each
RefDB installation requires exactly one main database called
refdb to hold the styles and the journal name word list, and
at least one reference database with a different name. The
refdb database is not used by all client commands, but many
require a connection to this database as well. This is
especially true for the viewstat command. The command does
not access any reference data, but it still needs a
connection to the database engine to retrieve version
numbers and such. Therefore viewstat causes refdbd to
connect to the refdb database. If that is missing, you're
screwed.

So please make sure there is a main database called refdb,
created from the appropriate dump file, and make sure the
permissions allow you to connect to that database from your
local computer.

BTW I don't understand why the refdbd -s -e 0 -l 7 log says
nothing. If you run the viewstat command, and get the "could
not open main database" message on the client side, refdbd
must have listed the connection parameters. You should see
that it tries to connect to the refdb database, and you
should also see the error message returned by the database
engine.

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

Comment By: Nobody/Anonymous (nobody)
Date: 2006-10-26 22:05

Message:
Logged In: NO 

Unfortunately, nothing :-(

For what it worths, the remote database name is not refdb
but cm126109

I can connect to it with:
mysql -h <remote host> -u <remote user> -p <remote password>
-d <remote database>, and see all the tables i've already
created (the database itself contains all tables you expect
from a refdb database, *but its name is not refdb*)

Here is what i've done:
1 - start refdbd using --- refdbd -V -s -e 0 -l 7
2 - start refdba using --- refdba -u <remote user> -w
<remote password> -d <remote database> -i <remote host>

In refdba, if i type set, i can see refdb is pointing to
<remote host>, but when i type viewstat, i get:
from refdba --- could not establish server connection
from refdbd --- <nothing> ...

Note that on the remote host, the database name is *not*
refdb, and the password is *not* refdb.

Anything else ?

When i enter in refdba the command *set*, i see the correct
<host>

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

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