MySQL *shifty eyes*

[A Cow]

MySQL *shifty eyes*

i know this would be honestly more work than its worth but have you guys ever considerd having the save in mysql vs text?

i just finished up my database programing and networking courses so me personal i am going to try and take this on.. but.. would you guys have any pointers on where to look ( aka what files do the saving, or how it knows when its time to save ) if not.. no worry il go thro just that might save some time..

as always il post any info i find on the forums.. because any update for the better helps us all

 

[mordero]

What would be the purpose? It would just add a complexity that isn't needed. The only thing I can think of off the top of my head that is saved in plain text are the accounts, almost everything else is saved in binary files.

 

[narcof]

I read also the thread about saves:

http://www.runuo.com/forums/modification-suggestions/81212-save-time-saver.html

It would be interesting to create an external database, and send updates in background, either like the guy said, for certain areas of game at intervals, or individual updates each time a value changes - advantage over interval saves, it'd be a more up to date snapshot of the shard. Not sure of the performance impact this would cause though with a large number of players.

 

[TheOutkastDev]

What would be the purpose? It would just add a complexity that isn't needed. The only thing I can think of off the top of my head that is saved in plain text are the accounts, almost everything else is saved in binary files.

Saving to a database would allow for easier integration with PHP based web pages, allowing users to create, login, and manage their UO Account settings via a website.

 

[mordero]

Saving to a database would allow for easier integration with PHP based web pages, allowing users to create, login, and manage their UO Account settings via a website.

That could be done with how things are now. However, I can see how using a database would be useful, but for being able to change anything in an account while the server is running, the server would have to access the database for everything related to accounts. Currently, accounts.xml is only used when loading the server and saving it. Once its loaded, the accounts are stored in memory.

 

[TheOutkastDev]

That could be done with how things are now. However, I can see how using a database would be useful, but for being able to change anything in an account while the server is running, the server would have to access the database for everything related to accounts. Currently, accounts.xml is only used when loading the server and saving it. Once its loaded, the accounts are stored in memory.

Not really. You just query the database periodically for account updates.

 

[A Cow]

personaly what i would want it to do in the end is instead of having the big save ( world save ) it would save it to a database.

also.. what i would like it to do is instead of loading all the clients at start have it load none but when they connect to the server it gets all there data and keeps it stored.. aka less memory footprint on large servers..

i dono might be ambisius.. might be good for my final?

 

[Courageous]

...personaly what i would want it to do in the end is instead of having the big save ( world save ) it would save it to a database....

Re: world saves in mysql.

This could be done.

The good news: this should be pretty easy to do. What you want to do is intercept each object in UO after serialization, and then simply push the object individually to mysql as a blob.

The bad news: it will be significantly slower, I'm afraid.

If you were thinking about doing more natural mysql integration, such as having fields in tables representative of objects values, I have worse news: this will be all but impossible. Given the bad and worse news, I have to ask.

Why do you want to do this?

(BTW, as other posters hinted at, the natural place for mysql integration is the account system).

C//

 

[A Cow]

hrm.. well acculy i was testing out a shard today ( just local my compy only ) and i must say.. the saves are a TON faster than they where ( in 02/03 ara ) and more common ( instead of every hour what every 5 / 10min? )

anways i think it should work fine.. but thanks for the intrest.. who knows maby i might still figure something crazy out to make it faster

 

[Courageous]

.. who knows maby i might still figure something crazy out to make it faster...

You could try. There's not much to make faster, though. The save cycle involves converting the objects into bytes of a recoverable format and then flushing them to disk. Any external database is just in the way... at a minimum you add in "copying the bytes to the database" before flushing them to disk.

The only way I can think of that even might make runuo saves faster is the use of an OODBMS. Such systems typically have optimizations to utilize the PMMU (paged memory management unit, the hardware that handles paging and swapping for the operating system) as an integral part of flushing objects to disk. Thing is, 'twould be a terrible pain to integrate such a thing.

Note that OODBMS systems are embedded. Unlike MySQL, Oracle, SQL Server, and the like, OODBMS systems run as an integral part of the software that using them. Not unlike runuo's own persistence layer: right in the software, writing straight to disk.

As I said, if you're looking for a good application of MySQL to runuo, look at the accounting system.

C//

 

[Ryan]

Courageous is actually right here... we did a lot of tests on saving to MySQL and SQL Server and both were significantly slower than saving to a flat file. Now that's not to say that RunUO couldn't be improved to take advantage of MySQL and have a faster save mechanism but it would require a lot of re-design.

The accounting system in MySQL is kind of "old hat" we've been doing that for a while and it's not something that's really "simple" to maintain hence why we have not publicly released it.

Either way there's a lot of cool things we've done with MySQL... we've moved all command logging, full blown speech logging and what have you to MySQL. UOGamers Hybrid does about 40 - 60 million lines of text per day depending on the day of the week so you can imagine the sheer volume of MySQL transactions that generates.

I have a feeling in the coming months you could see an "Advanced" RunUO released that could have some MySQL toys

Ryan

 

[Courageous]

Kewl. I always figured that the reason the main runuo distro didn't have mysql is that this would be yet another thing to support with the newbies. I.e., best not, and leave it as an add on.

But having installed mysql a bizzilion times now, I guess it wouldn't be *that* hard. Once could follow the lead of Confluence, and have the program auto-populate its own schema on startup (if not found).

C//

 

[A Cow]

Courageous is actually right here... we did a lot of tests on saving to MySQL and SQL Server and both were significantly slower than saving to a flat file. Now that's not to say that RunUO couldn't be improved to take advantage of MySQL and have a faster save mechanism but it would require a lot of re-design.

The accounting system in MySQL is kind of "old hat" we've been doing that for a while and it's not something that's really "simple" to maintain hence why we have not publicly released it.

Either way there's a lot of cool things we've done with MySQL... we've moved all command logging, full blown speech logging and what have you to MySQL. UOGamers Hybrid does about 40 - 60 million lines of text per day depending on the day of the week so you can imagine the sheer volume of MySQL transactions that generates.

I have a feeling in the coming months you could see an "Advanced" RunUO released that could have some MySQL toys

Ryan

From Day 1 I always knew you guys where the best.. quite excited

Kewl. I always figured that the reason the main runuo distro didn't have mysql is that this would be yet another thing to support with the newbies. I.e., best not, and leave it as an add on.

But having installed mysql a bizzilion times now, I guess it wouldn't be *that* hard. Once could follow the lead of Confluence, and have the program auto-populate its own schema on startup (if not found).

C//

hah amen to that for me installing MySQL is like.. the trip to work everyone hates it but they do it without noticing..

also i my database is on a private server so >.> yea.. lol..

 

[A Cow]

ok.. so.. anyways.. this is probably going to end up getting me killed..( via over work for no gain )

1) is it possible to change the encrypyion to md5 ( you will see why in 2min )
2) you where saying you can do the accounts to mysql.. any idea on where i should start looking into this?
3) overall reason.. what do you think the likeyness of this beeing..
1 login for vBullition, and RunUO? aka.. you register on the forums.. your runUO account is made and vice versa?

 

[Courageous]

Yes. You could arrange it so that if you create a login in VBulletin, you also have a login in your world.

C//

 

[PerfectWing]

That's actually been done on at least a few shards I've played in the past.

 

[A Cow]

That's actually been done on at least a few shards I've played in the past.

heh but i bet they wont share there code its acculy not to bad i think ive figured it out.. now i just have to wait till mon / tue for my new windows server to be insatlled.. the guy took the worng xpcd ( sp0 vs sp2key )

 

[quantomsadmn]

With the current system to integrate with runuo via web requires to have a "bot" that listens for commands. Easiest way to do this is to write a simplified UO client in C++ or other language that takes commands.

So in your php script you'd call it up with a query.

You could probably do this with the regular client and easyuo as well.

I personally would love to see runuo use a realtime mysql database. Tons of stuff would have to be restructured, but it could be done.

 

[snicker7]

Use ODBC to connect to your MySQL server running vbulletin and periodically query the users table (10m or so?) and then update the accounts on the server to match it. Voila.

 

[Courageous]

Use ODBC to connect to your MySQL server running vbulletin and periodically query the users table (10m or so?) and then update the accounts on the server to match it. Voila.

Nice tip. Does MySQL support stored triggers, especially external ones? If so, one could do a variation of this where any mutation to VBulletin's account table invoked a trigger that turned around and sent a socket based message to the runuo server which in turn caused it to do the above described.

C//