logo logo

 Back to main page

The NWNX Community Forum

 FAQFAQ   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 
 
First time user -- setting up mysql and database

 
Post new topic   Reply to topic    nwnx.org Forum Index -> Database related
View previous topic :: View next topic  
Author Message
Toro



Joined: 22 Jul 2007
Posts: 4

PostPosted: Sun Jul 22, 2007 22:53    Post subject: First time user -- setting up mysql and database Reply with quote

Hello everyone,
first off I'd like to say that NWN1 lives !! and its mostly due to people like yourselves who have contributed sooo much to this game.

Ok as stated above, I am a first time user and fairly competent at following directions but I am stuck at some pretty early stage things and its disheartning but I shall prevail.

I have mySQL 5.0 installed and can access it from mysql command line client, i have a password on it also and have no trouble getting to the mysql>
I also have the administrator tool installed.

Ok my first question (pardon if its very noob) Everything I seem to want to do requires a username , no where when i set up the server did it ask me to setup a username. for instance, when i run the admin tool, it asks for username.

i also have the PRC server pack installed because this is what I'm trying to get running it has instructions but I get to this part and I am stuck

"Using mySQL
This assumes mySQL is in the PATH for Windows. The precacher creates a file called out.sql, which needs moving to the server where the mySQL database is going to be created.

open a command prompt
go to the directory you put out.sql in
type: mysql -u user -p databasename < out.sql > out.log (replace the user and databasename with whatever it should be)
enter the password when prompted


"
These instruction are assuming that i already have a database going i think. However I do not. The prc server pack did not contain the aps module that I've read so much about that creates the databases for you.
Can i use the mod that comes from the vault to create the database?
i have a file run from the precacher mysql bat named out.sql. This can be added with the admin tool right?

Thxs for any assistance,
I'm sure I'll have a ton of more questions
I know this questions regard the prc server pack but i felt that you guys might be able to help me more because its specific to the mysql database,
but if not, i apologize.
Back to top
View user's profile Send private message
Papillon
x-man


Joined: 28 Dec 2004
Posts: 1060
Location: Germany

PostPosted: Mon Jul 23, 2007 22:40    Post subject: Reply with quote

Toro wrote:
Ok my first question (pardon if its very noob) Everything I seem to want to do requires a username , no where when i set up the server did it ask me to setup a username. for instance, when i run the admin tool, it asks for username.


Didn't the MySQL setup routine ask you for a root (or sa) password during the installation ? This user is there by default, you just have to set a password for him. Then, you can add new users with the admin tool afterwards.

Toro wrote:
These instruction are assuming that i already have a database going i think. However I do not.


Yes, you are correct. You will need to create a new database (e.g. "nwn", also sometimes called a schema) and then setup tables called "pwdata" and "pwobjdata". The create statement can be found in the demo module that comes with the NWNX download, if I remember correctly, you just have to enable the MySQL version of the statement and then run the module. If not, you should be able to run that statement in MySQL Query Browers (install it if you do not have it). That's for APS (the default persistence system for NWNX).

For PRC, you will have to run their .sql file according to the instructions, and it will probably create another bunch of tables. The "mysql -u ..." command will ask for your "user"'s password (just use "root" instead of "user if you have not setup a special DB user yet) and then do whatever is in out.sql.
_________________
Papillon
Back to top
View user's profile Send private message Visit poster's website MSN Messenger
Toro



Joined: 22 Jul 2007
Posts: 4

PostPosted: Tue Jul 24, 2007 2:18    Post subject: Reply with quote

the password is no problem, it did ask me on installation. It was just the username which had me scratching my head

so this statement
type: mysql -u user -p databasename < out.sql > out.log

would look like this (assuming i name my DB "nwn")
mysql -u root -p nwn <out.sql > out.log

ps. i installed the gui tools and root as username worked !!
The query browser asked me for a default schema?? although it said i can ignore this, what is it asking for??

ok figured out that the default schema its asking for is basically which database you want to start out in..

Another question: i looked at the mysql admin tool also, is there a way to import that out.sql thru the GUI , (i suck at typing commands, they never seem to work) so whats the easiest way to to import that out.sql into ( i managed to create a blank database called nwn) my nwn database??

ok tried to create the tables via the aps module but i couldnt get it to create the tables
this is my nwnx_odbc.text

NWNX ODBC2 plugin V.0.9.2.4
(c) 2005 by Ingmar Stieger (Papillon) and Jeroen Broekhuizen
visit us at http://www.nwnx.org

o Logfile maximum size limit is: 524288 bytes
o Log level: Everything will be logged.
o Using MySQL connection.
o Hooking SCO....hooked at 5d3560
o Hooking RCO....hooked at 5d3440
o Connect successful.
o Got request: DROP TABLE pwdata
! SQL Error: Unknown table 'pwdata'
o Got request: CREATE TABLE pwdata (player text(64),tag text(64),name text(64),val memo,expire text(4),last date)
! SQL Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'memo,expire text(4),last date)' at line 1
o Got request: SELECT player FROM pwdata WHERE player='Eliason' AND tag='Drax' AND name='demoName'
! SQL Error: Table 'nwn.pwdata' doesn't exist
o Empty set
o Got request: INSERT INTO pwdata (player,tag,name,val,expire) VALUES('Eliason','Drax','demoName','testValue',0)
! SQL Error: Table 'nwn.pwdata' doesn't exist
o Got request: SELECT val FROM pwdata WHERE player='Eliason' AND tag='Drax' AND name='demoName'
! SQL Error: Table 'nwn.pwdata' doesn't exist
o Empty set
o Disconnecting from database.

i think this is where i'm messing up in the instructions in aps/nwnx odbc documentation:
By default, it creates tables for the internal database. If you want to use MySQL instead, open the module with the toolset and edit the scripts "demo_createtable" and "demo_obj_create" accordingly.

i'm not very good at editing scripts , although i saw the demo_createtable script, i dont see anywhere the other script. Is it possible to get a mod. already configured to create a mysql database instead of the sqlite ?

i even tried this using the sqlite database (with the necesary changes to nwnx ini file) and it still didnt work for me.
Back to top
View user's profile Send private message
Papillon
x-man


Joined: 28 Dec 2004
Posts: 1060
Location: Germany

PostPosted: Tue Jul 24, 2007 12:24    Post subject: Reply with quote

Toro wrote:
Another question: i looked at the mysql admin tool also, is there a way to import that out.sql thru the GUI , (i suck at typing commands, they never seem to work) so whats the easiest way to to import that out.sql into ( i managed to create a blank database called nwn) my nwn database??


Sure, just connect with Query Browser and call FILE - OPEN QUERY and load your SQL file from disk. It's probably do it with the GUI tool myself, much easier and nicer.


Toro wrote:

i'm not very good at editing scripts , although i saw the demo_createtable script, i dont see anywhere the other script. Is it possible to get a mod. already configured to create a mysql database instead of the sqlite ?


No, you have to do it yourself, but it's really easy. Get the module from the nwnx_odbc2_0924.zip package. It does contain both scripts. Then, edit both and comment out the SQLite part and comment in the MySQL part.

That should do it.
_________________
Papillon
Back to top
View user's profile Send private message Visit poster's website MSN Messenger
Toro



Joined: 22 Jul 2007
Posts: 4

PostPosted: Wed Jul 25, 2007 2:29    Post subject: Reply with quote

ok, i know this is really low level stuff but I have to ask
how do I comment out the sqlite and comment in mysql
I tried removing that /* before and after the mysql part abd i think that commented in the mysql because it turned blue, i then tried adding /* around the sqlite part to comment it out but it stayed blue.
btw I got the sqlite database working as it should =)
Once i get this mysql commented in, will it (like the sqlite) leave a mysql database in my nwn directory ?


void main()
{
SQLExecDirect("DROP TABLE pwdata");
SendMessageToPC(GetLastUsedBy(), "Table 'pwdata' deleted.");

// For SQLite
SendMessageToPC(GetLastUsedBy(), "Creating Table 'pwdata' for SQLite...");
SQLExecDirect("CREATE TABLE pwdata (" +
"player varchar(64) NOT NULL default '~'," +
"tag varchar(64) NOT NULL default '~'," +
"name varchar(64) NOT NULL default '~'," +
"val text," +
"expire int(11) default NULL," +
"last timestamp NOT NULL default current_timestamp," +
"PRIMARY KEY (player,tag,name)" +
")");

// For MySQL
/*
SendMessageToPC(GetLastUsedBy(), "Creating Table 'pwdata' for MySQL...");
SQLExecDirect("CREATE TABLE pwdata (" +
"player varchar(64) NOT NULL default '~'," +
"tag varchar(64) NOT NULL default '~'," +
"name varchar(64) NOT NULL default '~'," +
"val text," +
"expire int(11) default NULL," +
"last timestamp NOT NULL default CURRENT_TIMESTAMP," +
"PRIMARY KEY (player,tag,name)" +
") ENGINE=MyISAM DEFAULT CHARSET=latin1;");
*/
Back to top
View user's profile Send private message
Papillon
x-man


Joined: 28 Dec 2004
Posts: 1060
Location: Germany

PostPosted: Wed Jul 25, 2007 9:05    Post subject: Reply with quote

you start a comment like this: /*
and you end a comment like this: */
(note the order)

Like this:
Code:
void main()
{
SQLExecDirect("DROP TABLE pwdata");
SendMessageToPC(GetLastUsedBy(), "Table 'pwdata' deleted.");

// For SQLite
/*
SendMessageToPC(GetLastUsedBy(), "Creating Table 'pwdata' for SQLite...");
SQLExecDirect("CREATE TABLE pwdata (" +
"player varchar(64) NOT NULL default '~'," +
"tag varchar(64) NOT NULL default '~'," +
"name varchar(64) NOT NULL default '~'," +
"val text," +
"expire int(11) default NULL," +
"last timestamp NOT NULL default current_timestamp," +
"PRIMARY KEY (player,tag,name)" +
")");
*/

// For MySQL
SendMessageToPC(GetLastUsedBy(), "Creating Table 'pwdata' for MySQL...");
SQLExecDirect("CREATE TABLE pwdata (" +
"player varchar(64) NOT NULL default '~'," +
"tag varchar(64) NOT NULL default '~'," +
"name varchar(64) NOT NULL default '~'," +
"val text," +
"expire int(11) default NULL," +
"last timestamp NOT NULL default CURRENT_TIMESTAMP," +
"PRIMARY KEY (player,tag,name)" +
") ENGINE=MyISAM DEFAULT CHARSET=latin1;");


The MySQL database files will be created in MySQL's own directory, not within the NWNX directory. Basically, the location does not matter, since you never specifiy the files directly.
_________________
Papillon
Back to top
View user's profile Send private message Visit poster's website MSN Messenger
Toro



Joined: 22 Jul 2007
Posts: 4

PostPosted: Wed Jul 25, 2007 16:10    Post subject: Reply with quote

Thanks Paps !! You're the best and I appreciate your patience.
FYI I now have a Mysql database with 2 tables in it, as it should be !!
I knew those /* was the answer but i guess i missed the order of it last night.

Anyway on to the next step, which has to do with the query browser
I opened it, made nwn the default schema, saw my database there with the 2 tables. File>open query> navigated to my folder> changed that drop down box to any ansi so i can open the out.sql file> opened it.

It opened in that top window (took a cpl minutes). So now what ? How do i create those tables in nwn database.
I tried this : after it all opened in that top window, i hit execute.
i get an error near the bottom of the query browser stating
query returned no ruleset
unknown table prc_cached2da error nr 1051

I'm guessing that i'm getting this error because the tables arent there but isnt this suppose to create the tables ?

(later that day)
I tried this, instead of open query, i tried open script,, then loaded it. I think this worked because i see all the prc tables now along with pwdata and pwobjdata, This is correct right?
Back to top
View user's profile Send private message
Papillon
x-man


Joined: 28 Dec 2004
Posts: 1060
Location: Germany

PostPosted: Wed Aug 01, 2007 21:33    Post subject: Reply with quote

If the PRC tables have been created, you should be set. I do not know if any data has to be written to those tables by the script as well, but at least it seems it passed without any errors. Good job !
_________________
Papillon
Back to top
View user's profile Send private message Visit poster's website MSN Messenger
Quadhund



Joined: 26 May 2008
Posts: 2

PostPosted: Wed May 28, 2008 18:31    Post subject: Reply with quote

Papillon wrote:
Toro wrote:
Another question: i looked at the mysql admin tool also, is there a way to import that out.sql thru the GUI , (i suck at typing commands, they never seem to work) so whats the easiest way to to import that out.sql into ( i managed to create a blank database called nwn) my nwn database??


Sure, just connect with Query Browser and call FILE - OPEN QUERY and load your SQL file from disk. It's probably do it with the GUI tool myself, much easier and nicer.


Toro wrote:

i'm not very good at editing scripts , although i saw the demo_createtable script, i dont see anywhere the other script. Is it possible to get a mod. already configured to create a mysql database instead of the sqlite ?


No, you have to do it yourself, but it's really easy. Get the module from the nwnx_odbc2_0924.zip package. It does contain both scripts. Then, edit both and comment out the SQLite part and comment in the MySQL part.

That should do it.


I noticed this discussion pertained to NWN1. I assume the same thing (creation of the database through the module scripts) is necessary for NWN2, but the file provided is for NWN1. How would one create the database in NWN2?

EDIT: I also wanted to add that I got to the same point as Toro did in NWN2, when I entered the NWNX4 module, but no portal was showing up for MySQL. I assumed it was because of the same issue as Toro (and I am running on XP btw)
Back to top
View user's profile Send private message
Styxx42



Joined: 08 Aug 2009
Posts: 8

PostPosted: Sat Aug 08, 2009 5:52    Post subject: Reply with quote

Toro for having the guts to ask these questions and and Papillon for having the patience to answer them.

YOU ROCK.

THANKS. I am at step one above but feel much more confident having found this thread.
I know squat about MySql but this GUI tool I just found because of this tread will help me progress further then I am right now.

Thanks again to you both.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    nwnx.org Forum Index -> Database related All times are GMT + 2 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group