Using two databases

[Elorn]

Hiya,

I'm currently in the process of connecting two previously separate NWN servers, to form a single persistent world.

I would like to have the ability to share a database between the two servers, for example to transfer some character information. On the other hand, I'd also prefer to store all the data that's only important to one server in a local DB (because of performance).

My first thought was to use the Bioware DB calls for local persistence and NWNX ODBC for the global database, but I found out that one of he two modules already heavily uses nwnx odbc.
So now I'm wondering if I could make two different ODBC connections with the servers. The default ODBC dll doesn’t seem to be configurable to make this possible, so I’m thinking of compiling a second ODBC dll, which would do exactly the same as the normal one, except it’d use different variables to communicate with the scripts on the module. (I would also make a second set of scripts to use on the module.)

Now on to my questions…
Do you think this would work? Or am I making things much more complicated then I should and is there a much easier solution to my problem?

And secondly, while trying to compile the ODBC code, I got the following error:
d:\code\nwnx\odbc2\src\nwnxodbc.h(37) : fatal error C1083: Cannot open include file: 'mysql.h': No such file or directory
The line in question reads:
#include <mysql.h>

I assume this means I’m missing a package or library used to make the mysql connection. I read something on the forum about the mysql development libraries, but I’m having some trouble figuring out which ones I should be using exactly. Maybe you could point me in the right direction?

[Acrodania]

Instead of two databases you do have other options.

1) You can run the MySQL instance on one server and have both point at it. Module-specific stuff would be stored in different tables (location1&2, etc). Things that are shared can be in common tables. With a tag on the module you could then tell it which module specific tables to use with each server, keeping your code-base the same.

2) Do something similar to the above with seperate tables for module-spcific things but instead of one server handling the database you would set it up on BOTH and replicate between them to keep the information current. Advantage is failover in case one server died. Disadvantage is increased network traffic and complexity.

Papillon hinted at providing additional connects for external web-servers but so far hasn't released anything....

[Elorn]

Hmmm I don't want to go with option 1 because the two servers are run on different locations, I'm afraid that the one not running at the same location as the DB will get a lot of lag.

Perhaps I could set up two DB's and only replicate the tables with global information though. Only problem is that if more servers are added to it there will be a lot of traffic needed to make the replication happen.

The additional connects for external web-servers solution sounds exactly what I'm looking for I know this is all just a hobby, but any idea on if this will be released in the near future? In that case I might just wait for it.

[Primogenitor]

If its just player-specific information, maybe you could put the information as local variables on the player (or on an item in the players inventory) just before moving them from one server to the other? I assume your using the Vaultster plugin too to move player .bics around?

[Elorn]

[Elorn]

I figured out where to get the libraries btw, they were included in the regular mysql installation, silly me.
Thanks for the advice everyone!

[Papillon]

Elorn, there are no plans to include multiple database connections at this point. Early this year, someone started work on having multiple result sets, and this would have been a nice base for multiple connections, but unfortunately this never came into existence.

Compiling a second DLL that reacts to another keyword (e.g. ODBC->CENTRALODBC) and uses it's own config and ini files would be the easiest, although probably not the most elegant solution.
_________________
Papillon

[Elorn]

[Gryphyn]