Converting to MySQL

Red Squirrel · Post by **Red Squirrel** » Tue Feb 03, 2009 1:11 am

I've started working on this tonight. The server is finally setup, now to start coding.

I've only did rough research and brainstorming tonight but I'm hoping to start coding shortly once all my thoughts are together. This on it's own will be a rather large overhaul, though I am possibly considering a method that may be less efficient use of mysql, but will greatly speed things up code wise, so I'll decide what is best.

Ticket: http://www.iceteks.net/devel/index.php? ... ask_id=134

Archived topic from AOV, old topic ID:4180, old post ID:26806

Red Squirrel · Post by **Red Squirrel** » Fri Feb 20, 2009 12:49 am

I officially started on this and got to the point where I can connect and write data to the DB. So getting some progress.

Archived topic from AOV, old topic ID:4180, old post ID:27801

Red Squirrel · Post by **Red Squirrel** » Thu Feb 26, 2009 5:17 pm

I think I got something going on that may possibly work, but I'll need to put it under more stress to ensure it can keep up with active shard chagnes as I was just testing it on idle shard.

There's more details in the ticket as to how it works, but basically, from a player perspective, there will be no world saves. There will actually be some, but they will last like 0.2 secs which is faster then the run speed delay for example, so it will not be noticeable. They will happen often though.

To do an actual full save when we convert over from data file to SQL may actually take about a day, but from that point on, it will only save the changes.

I still need to work on error handling though, as to ensure if queries error out, that it is retried and/or logged so we don't end up with missing items.

I also still have to work on the world load process and ensure that will be fast enough. all in all, this is going fairly well. I've been through several versions of the engine and think this revision will be the one.

Archived topic from AOV, old topic ID:4180, old post ID:28126

Kwan · Post by **Kwan** » Thu Feb 26, 2009 5:49 pm

I have a question.

I understand the advantage of saving to MySQL, faster/invizable save times. And all that fun stuff.

I also understand were only saving changes. So how exacly would you preform a time warp. How long would that take? Will your do a full backup every so often?

Archived topic from AOV, old topic ID:4180, old post ID:28130

Red Squirrel · Post by **Red Squirrel** » Thu Feb 26, 2009 7:00 pm

I would take a "snapshot" of the db every day or so and it would get downloaded to my home server as backup. To do a time warp I'd have to truncate all sql tables then re-add the data from the others. I'll probably write a bash script to do this faster but I don't ever see myself doing a revert unless something catastrophic happens (like altar's house dropping).

Now what will be weird here is say the shard crashes, if not all data was fully buffered out, there could be some stuff missing, and some not missing, rather then a true time warp. Say user A gets an artifact in gaunt then user B gets an artifact. user A's artifact just got saved to the DB now, then the shard crashes, user A will have his arty but user B might not.

I might be able to arrange so that the buffer gets written after a crash though, so we'll see how that works out.

Though still there will be way less data loss should the shard go down roughly. (ex: bad shutdown of server or something)

Archived topic from AOV, old topic ID:4180, old post ID:28136

dprantl · Post by **dprantl** » Fri Feb 27, 2009 10:28 pm

Red Squirrel wrote:I would take a "snapshot" of the db every day or so and it would get downloaded to my home server as backup. To do a time warp I'd have to truncate all sql tables then re-add the data from the others. I'll probably write a bash script to do this faster but I don't ever see myself doing a revert unless something catastrophic happens (like altar's house dropping).

Yes, that would definitely be a DISASTER!

Archived topic from AOV, old topic ID:4180, old post ID:28213

Red Squirrel · Post by **Red Squirrel** » Sun Mar 08, 2009 1:20 am

For those curious, here is how a playermobile looks like saved in the database:

(added returns to not screw up forum layout)

Code: Select all

32;-633550995556569295;-633550995556100607;-633550995556100607;
00;0;-1;632859953392610000;0;0100;0;0;5;0;-1;0;000-1;0;0;00225;-1;20;
1843;2747;0;400;4;Mark0;00;0;0;0;0;3;ENU000433777;35;35;10;67;35;10;
127;00;0;03;7000;55;2551300;2552552552552552552552552552552552552
552552551450;2552552552552552552552552551500;2552552552552552552
552552552552552551350;25525525525525514;255255255111;25525525525
57;1073741827;1073741832;1073741834;1073741837;1073741839;10737
41840;1073741841;10;0;011845;2745;0;1;00000;29;00;0;0;00;-6391561
50344;0;0;12;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0
;0;0;0;0;0;0;0;633720569884102724;0;0;0;0;0;632859953396360000;0;
0;0;1;-639155837854;0;0;00;0;0;0;0;0;-639155837854;0;-633550994764
095526;-639155837854;0;0;1440000000000;288000000000;1554531250;0;

I'm at a point where I can pretty much reference the script and read what all that means.

There is still some errors in the format but I'm working on it.

BTW C++ > C# BY FAR when it comes to data manipulation. OMG C# makes stuff so much harder and more complicated.

Archived topic from AOV, old topic ID:4180, old post ID:28764

Plastic Man · Post by **Plastic Man** » Sun Mar 08, 2009 9:31 am

dprantl wrote:
Red Squirrel wrote:I would take a "snapshot" of the db every day or so and it would get downloaded to my home server as backup. To do a time warp I'd have to truncate all sql tables then re-add the data from the others. I'll probably write a bash script to do this faster but I don't ever see myself doing a revert unless something catastrophic happens (like altar's house dropping).
Yes, that would definitely be a DISASTER!

*Note to self, Lock all my wealth down on the roof of my Luna House.*

*prepares to display an Oracle*

Archived topic from AOV, old topic ID:4180, old post ID:28775

Red Squirrel · Post by **Red Squirrel** » Thu Mar 12, 2009 11:04 pm

Made some more progress today. Managed to fix a crash issue as soon as world loads, was a booboo I made. (loaded guilds but forgot to load the data for them lol)

There's still some issues, but so far there are no grenades and it's all stuff I already have an idea how to tackle.

I can confirm that most items load properly, even houses. Actually, ever see a house without a sign? yeah.... that was odd. (loaded a partial db)

There are a few item deserialize errors but I need to let the file->sql conversion run tonight so I can reproduce it since I opened the file loader instead of the sql loader when I went to debug. That wipes the db. Oops.

I'll be sure to make a snapshot this time.

But yeah, this is going fairly smooth. While I did run into issues and ended up not doing it exactly as I wanted, I have to say this is going fairly smooth and we are heading in the right direction.

Archived topic from AOV, old topic ID:4180, old post ID:28936

Red Squirrel · Post by **Red Squirrel** » Wed Mar 18, 2009 5:27 pm

This has been progressing slower then expected, but there is some progress being done. I have it to the point where it can save most data and load off most data. My main thing now is getting accounts working properly with the new system, and then getting changes to properly replicate.

At this point I just need to do some brute force testing to ensure it is solid. There will also be performance testing done.

I'm hoping to get it working by the end of this month so I can start on the property system and then craft system (they'll tie together closely). Once those back end things are done then we can finally start on the actual content.

Archived topic from AOV, old topic ID:4180, old post ID:29076

Red Squirrel · Post by **Red Squirrel** » Sat May 16, 2009 5:26 pm

Now that UOGateway is pretty much done to the point I want it, I have now started working on SQL again. I've been evaluating to see if there are better ways to do some of the things I want, but I don't want to spend too much time on this anymore and I just want to ensure it's solid even though lot of code might be partially duplicated.

Items, Mobiles, Guilds are done, Accounts are almost done, from there I just need to fix up some of the things that don't quite work well yet, and test the heck out of it.

From that point I need to add the security audit system which will log activity to a special logging system, though that may potentially be text based. Not sure yet. Account/char login/logout/creation, house drops, and other such transactions will also be logged into a special system allowing us to run reports that will help us catch suspicious activity as well as people with too many accounts.

There is still lot of brainstorming to do as far as security systems go, but security is something that constantly will need to be updated as people find ways to fool it.

Once I think SQL is stable I may release it before the rest of the revamp, but I'm not sure yet if I want to do that.

Archived topic from AOV, old topic ID:4180, old post ID:30265

jrhather · Post by **jrhather** » Sat May 16, 2009 11:44 pm

I'm looking forward to this.

Archived topic from AOV, old topic ID:4180, old post ID:30267

Red Squirrel · Post by **Red Squirrel** » Mon May 18, 2009 6:58 pm

Added a progress report:

http://www.iceteks.net/devel/index.php? ... comment206

Once I solve most of these issues I will add this to TC1 and open up beta testing.

Archived topic from AOV, old topic ID:4180, old post ID:30311

Red Squirrel · Post by **Red Squirrel** » Wed May 27, 2009 8:34 pm

Running into a few issues but so far they're classified as minor. One may turn major though.

problem 1: random duplicate errors, so need to figure out why it's trying to do an INSERT twice on the same serial.

problem 2: the performance is BRUTAL under win2k3. Attempting to install SP1 to see if it helps.

I also have some other misc fixes to do, but you can expect a beta testing phase in the next few weeks.

Archived topic from AOV, old topic ID:4180, old post ID:30423

Red Squirrel · Post by **Red Squirrel** » Sun Jun 28, 2009 11:24 pm

These changes will be added to TC1 shortly:

*Fixed issue with duplicate entries. Doom puzzle code uses World.RemoveItem() instead of item.Delete() and the code to set the delete state is in Item.Delete(). Added code in RemoveItem() and RemoveMobile() to be safe as there may be other places where these are used instead of .Delete().

*The delta file (renamed that to cache file as it's more appropriate) seems to work now but still needs heavier testing under different circumstances.

*Shut down sequence is better but still needs work. Occasionally not everything saves

*All possibility for SQL injection or special character errors have been removed. All data is saved as integers only. This takes more space but was easiest and is 100% fool proof.

Archived topic from AOV, old topic ID:4180, old post ID:30700

Red Squirrel · Post by **Red Squirrel** » Wed Aug 12, 2009 11:44 pm

Been working on account edit outside of game (realtime account reload system) for past few days. Getting things together in preparation for the account creation form. As our SQL DB is designed only for data to be written to real time, not read (only gets read at load time) special cases need to be done so that accounts that change in sql are reloaded into the game.

Once this is done way it will work is if a new account is added or an existing is modified in mysql, it will load in game within next 5 minute cycle. For now this will most likely be for creations but I will most likely add an account management system where you can log in and do stuff like transfer chars to other accounts, change your password/email and what not.

Archived topic from AOV, old topic ID:4180, old post ID:30855

Red Squirrel · Post by **Red Squirrel** » Sun Aug 23, 2009 9:57 pm

The account reload process seems to work nice now. this same process is what will pickup new accounts from the SQL table, which means I just need to implement the form and a type of user validation scheme and I can setup account creations on beta.

I also need to do some more testing to ensure loss of SQL connectivity is handled properly and do lot of general code cleaning and tidy up.

Archived topic from AOV, old topic ID:4180, old post ID:30926

Red Squirrel · Post by **Red Squirrel** » Fri Aug 28, 2009 12:00 pm

I have run into lot of issues regarding memory and stability. While this system uses more ram and more ram intensive operations, C# can't handle this as well and craps out a lot. I need to find a way to make it more efficient so that it does not crash.

I may even need to go to 64 bit platform which would cost a lot as i would need to upgrade my dev and test environments, AND update live. Currently it's all 32 bit. AFAIK even on a 64 bit processor you can't run a 64 bit VM otherwise I could at least convert test to 64 bit, and convert live. But best to be the same accross the board...

If it turns out an upgrade is needed then this project may need to be posponed until more funds are available.

Archived topic from AOV, old topic ID:4180, old post ID:30955

dprantl · Post by **dprantl** » Fri Aug 28, 2009 2:50 pm

Red Squirrel wrote:AFAIK even on a 64 bit processor you can't run a 64 bit VM otherwise I could at least convert test to 64 bit, and convert live.

Yes you can. You need to enable CPU Virtualization in the BIOS (or something like that, I think it's also called HyperVisor support). I'm running multiple 64-bit OSes in VMware and Hyper-V as we speak.

Archived topic from AOV, old topic ID:4180, old post ID:30956

Red Squirrel · Post by **Red Squirrel** » Fri Aug 28, 2009 3:20 pm

Yeah that's only the newer enterprise class processors I think though, like xeons and stuff.

Archived topic from AOV, old topic ID:4180, old post ID:30957

Red Squirrel · Post by **Red Squirrel** » Sun Sep 06, 2009 12:31 am

Still need to figure out the memory issue but it only seems to do it with DBs ranging in the millions so for now I moved back to the caching system. It's working fairly nicely so far and everything saves properly even if the SQL server goes down. Been optimizing that proceedure for the past few days.

Archived topic from AOV, old topic ID:4180, old post ID:30980

Red Squirrel · Post by **Red Squirrel** » Sun Sep 06, 2009 9:58 pm

Sweet! Think I just fixed the issue with the "full save" function. Now it actually does a, full save! It used to leave stuff behind and I could not figure out why. Still need to stress test it some more though...

Archived topic from AOV, old topic ID:4180, old post ID:30984

Red Squirrel · Post by **Red Squirrel** » Mon Nov 02, 2009 8:30 pm

Just a quick update. As you may have seen the system is up for full blown testing on TC1. (more info in news section). The system is almost complete and is in intensive testing phase.

Once most of the basic testing is done I want to start on the account form, and at that time I will open up registrations on TC1 for testing of the account system. I will most likely have a basic web based system where you can change your password and what not.

Archived topic from AOV, old topic ID:4180, old post ID:31269