NWNx2 and NWN 1.69 with MySQL

[MontyThreeCard]

First off, let me say that I am very familiar with NWN1, NWN2, and MySQL. I had set up a NWNx4 database for our NWN2 persistant world, and it works just fine - as a matter of fact, we administer the database from the website that we built, which allows for in game modifications right from the web.

So, having said that, I tried to go back and set up NWNx2 for our NWN1 server. I used the same DB, but copied the structure of all of our tables with new table names, prefixing them with "nwn1_". Should have been an easy setup right? Nope. NWNx2 loads, starts the server and module, and appears to connect to the DB.

However, when I fire a query, and check the result set, the first field is the spacer periods that NWNx2 sets up in the local variable when it initializes, that should have been swapped out by the hooks for data. I have the proper dll's in place, as a matter of fact, I have tried several different NWNx2 versions (removing the old dll's and exe's of course), and either I get the "Library not loaded" error, or I get the periods as the first Get field.

The only non-standard thing is that I use a remote MySQL database, since it is pre-existing, and add the IP to the NWNx.ini file, along with the remote passwords.

I have checked my code, installed the aps_include functions rather than my custom ones, everything I can think of. I was wondering if anyone has had a similar experience, and if so, how did you correct the problem. We do not use the ODBC, at least not now, but fire the queries directly to the MySQL.

Any help would be gratefully appreciated.

M3C

[Zebranky]

How remote is this remote MySQL server? nwnx_odbc executes queries synchronously, so if the ping from the NWN server to the MySQL server is 50 ms, the server is going to hang at least that long to run each query. Translation: serious lag if you use the DB heavily.

That said, it doesn't explain your problem. I know you said you've tried different versions of everything, but have you used the ones linked in my signature? That has solved similar weird issues in the past, so it's worth a shot.
_________________
Win32 SVN builds: http://www.mercuric.net/nwn/nwnx/

<Fluffy-Kooshy> NWNx plugin is to this as nuclear warheads are to getting rid of fire ants.

<ThriWork> whenever I hear nwn extender, I think what does NWN need a penis extender for?

[MontyThreeCard]

Sure, I'll try them. At this point, I'll try anything. Let me ask you this - what is the behavior if your SQL syntax is wrong? I will be double-checking it anyway, but could that cause a problem like this?

I am also looking at a weird issue between the computers - the website connects fine, but in DOS mode the ping times out between the NWN server and the MySQL server, and so does the tracert. Maybe a firewall/router issue? I'll see what I can find out.

Thanks,

M3C

[peachykeen]

If you have any firewalls, DMZs or routers between two systems, ping (and often tracert) will fail: most of those are setup to block the ping port by default. So, that's not always a good indicator.

Your issue may be related, though. I've run into problems before connecting to certain APIs and such... MySQL uses a different port (in the 3360 area, if I remember right) than NWServer and HTTP servers. Usually it's not an issue, unless you need to remotely login and send queries. Make sure you have the right port forwarded for the MySQL server as well, and it's set to accept external login attempts (you should technically write a proper API, as it's more secure, but that's probably a bit much on the bother for this ).

[Zebranky]

If the log says it's connecting, it's likely not a firewall issue on the MySQL front, though a firewall is the most likely cause of ping/tracert failing.

Set LogLevel=2 in the ODBC section of your NWNX.ini to get full query logging. If you still don't see any queries in nwnx_odbc.txt, the queries aren't making it to nwnx_odbc properly; if you see them and they're failing, it's more likely either a syntax issue or something else more DB-side.
_________________
Win32 SVN builds: http://www.mercuric.net/nwn/nwnx/

<Fluffy-Kooshy> NWNx plugin is to this as nuclear warheads are to getting rid of fire ants.

<ThriWork> whenever I hear nwn extender, I think what does NWN need a penis extender for?

[MontyThreeCard]

Ok, I got the module and the db on the same server now. I try to run, and while it appears that nwnx2 connects to the db, the result set I get is still the series of dots .........

And, I just found out what was wrong! You MUST use the ODBC2 dll when using MySQL! I had no idea. I read and reread the documentation, but I never got that. I would make it a HUGE point to add to the MySQL documentation that if you do not add the ODBC2 Driver when using MySQL, you will get no response from the database.

Sorry to tie everyone up, but this was exercising me for a while, and I had to get it solved. All of your help pointed me in the right direction. Thank you!!

Gratefully,

M3C