Gossamer Forum
Home : Products : Gossamer Links : Discussions :

Links mod_perl cpanel MySQL- inconsistancy/unstable

Quote Reply
Links mod_perl cpanel MySQL- inconsistancy/unstable
I'm in the midde of using the GT modules to write a mmorpg browser based online game for our links install. It's really amazing just how flexable the codebase is, even after all these years.

I'm running into some really strange issues that I've never run across before working with the GT code. I'm pretty much beating my head into a wall at this point trying to figure out what's gone wrong.... Figured I'd ask here before I commit myself to the loony bin.

In a nutshell, the data in the MySQL database seems to randomly revert back to older data, after successful updates.

For example, a player flys all 25 of his characters to a different country. To do that, a player has to click on each characters shortcut, then click the "Fly" to button. I'll have this done while I watch the debug logs, and I'll see the update statement for each character with no errors. After all the characters have been updated, a query will show that 1/3 of them still have the old location data. It's pretty random though... the non-update could happen to anywhere between no characters and 10 characters.

It's not just that scenario either... it's pretty much any update to the character's data...

Has anyone ever had anything like this happen before? Our live install (not where this game is being developed) is running on a dedicated server with no cp and we've never had any problems remotely like this. The development environment is running under Cpanel and mod_perl. Could Cpanel be part of the problem?

Any tips/ideas at all would be much appreciated!

Thanks!

AlexJ
Quote Reply
Re: [AlexJ] Links mod_perl cpanel MySQL- inconsistancy/unstable In reply to
Hi,

Have you tried doing some tests with debugging enabled on your dev install? This should give you details on the queries being run (so you can check its all updating ok)

Otherwise, maybe a SQL Caching issue? (never seen that myself, but I guess its possible)

Cheers

Andy (mod)
andy@ultranerds.co.uk
Want to give me something back for my help? Please see my Amazon Wish List
GLinks ULTRA Package | GLinks ULTRA Package PRO
Links SQL Plugins | Website Design and SEO | UltraNerds | ULTRAGLobals Plugin | Pre-Made Template Sets | FREE GLinks Plugins!
Quote Reply
Re: [AlexJ] Links mod_perl cpanel MySQL- inconsistancy/unstable In reply to
That one's a weird one. Some ideas of things to check out:
- mod_perl issues: $DB isn't getting reset (see what we do in Links::init when in mod_perl)
- MySQL autocommit not turned on? Probably not the case since some data is getting written
- MySQL replication issues if you have more than one database server?

Adrian
Quote Reply
Re: [AlexJ] Links mod_perl cpanel MySQL- inconsistancy/unstable In reply to
Thanks for the tips guys....

After switching to a high end dedicated server, I still had the same issues... Cpanel was not the culpret.... I ended up hitting up the guys over on the official MySQL forums.

Someone over there suggested that I try locking the table during updates.... Problem solved....!! It's crazy it was such a simple resolution... Then again I've never written a MMORPG type game, where hundreds of actions can be happening every second....

Now... that said... Are there proper table lock and unlock features built into GT::SQL; ?

On the cron job that I fixed, I used DBI like so:
Code:

use DBI;
my $dbh = DBI->connect('dbi:mysql:tablename_community','username_comm','pw', {AutoCommit => 1} ) or die "Connection Error: $DBI::errstr\n";

# Put temporary lock on table
$dbh->do('LOCK TABLES glinks_gw_chars WRITE');

#
# Updates and queries
#

# Remove temporary lock on table
$dbh->do('UNLOCK TABLES');

Is there a more elegant way to do this with the GT::SQL library? At the very least I'd like to stick with the GT:SQL library and not load up DBI.... As the entire application is written around GT:SQL...

Thanks,

AlexJ
Quote Reply
Re: [AlexJ] Links mod_perl cpanel MySQL- inconsistancy/unstable In reply to
I thought maybe doing a direct query using the GT libary might work... like this:

Code:
my $stl = $DB->table('gw_chars')->do_query(qq|LOCK TABLES glinks_gw_chars WRITE|) || die $GT::SQL::error;

All calls to the table after the locking result in error, as undefined. It's as if after doing a manual lock like I did above, the GT::SQL calls are not being recognised as coming from the same thread....

Hopefully someone can shed some light on how I can fix this and stick with the GT::SQL routines... It would really stink to have to change everything over to DBI.... huh?

AlexJ