Welcome!

Join our community of MMO enthusiasts and game developers! By registering, you'll gain access to discussions on the latest developments in MMO server files and collaborate with like-minded individuals. Join us today and unlock the potential of MMO server development!

Join Today!

Why character's inventory is represented in DBs as varbinary?

Junior Spellweaver
Joined
Feb 18, 2011
Messages
108
Reaction score
41
Hi,

I was wondering why, in the name of god, you guys (the community as an entity) decided to store item informations in the data base as a varbinary with the size of my city.

Does anyone have any clue about any possible reasonable explanation about why this is done that way in what seems to all data bases?

I first thought about execution time, but when I saw the size of the memory chunk that is stored in the sql server it fastly gone away of my mind. I'm out of thoughts here.
 
Joined
Jul 4, 2006
Messages
172
Reaction score
36
i dont think "we" decided. AFAIK the first GS that was leaked from webzen (96Y then modded to .97d) was legit and the DB was created back then. So i guess this is a question for webzen developers ^^
 
Joined
Aug 6, 2005
Messages
550
Reaction score
296
I think webzen put it all into a blob because computers were not so fast back then. If you would use a proper database model you would end up with a lot of joins and additional database round trips. ORMs with change trackers were not available like today, too.

Oh well, and the community just continued using the same principle ;)
 
Junior Spellweaver
Joined
Feb 18, 2011
Messages
108
Reaction score
41
I think webzen put it all into a blob because computers were not so fast back then. If you would use a proper database model you would end up with a lot of joins and additional database round trips. ORMs with change trackers were not available like today, too.

Oh well, and the community just continued using the same principle ;)

That makes sense. Webzen did something 15 years ago and ppl just keep reproducing :D
 
Junior Spellweaver
Joined
Aug 7, 2010
Messages
137
Reaction score
137
And for some reason even having full control on server side, they keep using same database system. But "winning team should not be changed".

We decided to change long time ago ( like 6 or 7 years ) to mysql and this is the result of item storage in database.

All items from all kind of boxes ( Inventory, Warehouse, Event Inventory, Gremory Case, Muun Inventory, Sold Items, etc) are stored in the same table.
With indexes, a good pc and good settings you don't have to worry about performance.
It can't be stored 2 items with same serial due to indexes.

ptr0x - Why character's inventory is represented in DBs as varbinary? - RaGEZONE Forums

ptr0x - Why character's inventory is represented in DBs as varbinary? - RaGEZONE Forums
 
Joined
Nov 4, 2012
Messages
928
Reaction score
544
Because is more reliable save a inventory memory direcly on db than make a whole db struct to that. Is faster, reliable and avoid some problems related with items
 
Junior Spellweaver
Joined
Feb 18, 2011
Messages
108
Reaction score
41
Because is more reliable save a inventory memory direcly on db than make a whole db struct to that. Is faster, reliable and avoid some problems related with items

I disagree in every aspect of what you said. Could you elaborate why saving 7KB as varbinary is faster and more reliable than the proper data design?

And what are those problems related with items you said?
 
Joined
Aug 6, 2005
Messages
550
Reaction score
296
Because is more reliable save a inventory memory direcly on db than make a whole db struct to that. Is faster, reliable and avoid some problems related with items
I suspect you mean reliability in terms of "if this one update sql statement succeeds, all the inventory is saved at once"?
You know what the ' ' in ACID stands for? ;-)
Faster? Depends! Updating the whole blob every time a character is saved, or because maybe only one item durability has changed isn't efficient. It's efficient if you have an inventory that is completely filled with every slot and every item has changed compared to the last save point - but that's a rare case.
Problems? I don't know what you mean. Saving these items as blobs causes a whole bunch of more problems. With a proper database model you can prevent wrong data by using unique or check constraints.
 
Joined
Nov 4, 2012
Messages
928
Reaction score
544
I suspect you mean reliability in terms of "if this one update sql statement succeeds, all the inventory is saved at once"?
You know what the ' ' in ACID stands for? ;-)
Faster? Depends! Updating the whole blob every time a character is saved, or because maybe only one item durability has changed isn't efficient. It's efficient if you have an inventory that is completely filled with every slot and every item has changed compared to the last save point - but that's a rare case.
Problems? I don't know what you mean. Saving these items as blobs causes a whole bunch of more problems. With a proper database model you can prevent wrong data by using unique or check constraints.

Just chek when inventory is saved, and how server is made bro. When item update durability that is not directly saved on db. but only when character logouts or made an transaction with other players, cashshop or something related to inventory.

If you check at GS sources, the object struct have two backups of this blob. soo if some transaction fails, the server automatically roolback with old blob at object struct, after all this is saved at db.

Is not just once save, and save it again. That is performance gain over consecutive querys to finally do a translation at db.
Also if i remember, that is explained on some korean docs that comes with some original files from webzen mu project documentation.
 
Joined
Aug 6, 2005
Messages
550
Reaction score
296
Oh sorry, I probably made a grammar mistake when I meant 'or because maybe only one item durability has changed'. I know when the server actually saves, but it's still inefficient to always save the whole item blob at these save points even if nothing (or very little) actually changed.
 
Junior Spellweaver
Joined
Feb 18, 2011
Messages
108
Reaction score
41
Just chek when inventory is saved, and how server is made bro. When item update durability that is not directly saved on db. but only when character logouts or made an transaction with other players, cashshop or something related to inventory.

If you check at GS sources, the object struct have two backups of this blob. soo if some transaction fails, the server automatically roolback with old blob at object struct, after all this is saved at db.

Is not just once save, and save it again. That is performance gain over consecutive querys to finally do a translation at db.
Also if i remember, that is explained on some korean docs that comes with some original files from webzen mu project documentation.

So you justify a bad data design with a bad code? We have access to both the code and the data structure. The way the server persists informations is just wrong, as it is the fact of saving structural relational data as a varbinary. It just does not make sense.
 
Junior Spellweaver
Joined
Aug 7, 2010
Messages
137
Reaction score
137
If you use blob to store any data, you need to save everything each time you want to update database, even items / skills that has not been changed.

Using a good struct allow you to only save data that has been changed. You just add a Flag on an object that has been changed / moved / removed and only save that. That saves time and resources.

The best thing to do is gain courage and move to a new database design. It will take time now, but it will he really helpful on the future.

Imagine an update where you need to change all item sockets on database or any other attribute. With a simple query on a structured table, you can make that update easy & fast without any problem.

We had like 2 / 3 years of failed servers until we finally got a stable database system (noob at MySQL settings, noob at Table design, problems with MySQL & C++ communication until we took a look at how Trinity Core handled it).




 
Last edited:
Joined
Nov 4, 2012
Messages
928
Reaction score
544
If you use blob to store any data, you need to save everything each time you want to update database, even items / skills that has not been changed.

Using a good struct allow you to only save data that has been changed. You just add a Flag on an object that has been changed / moved / removed and only save that. That saves time and resources.

The best thing to do is gain courage and move to a new database design. It will take time now, but it will he really helpful on the future.

Imagine an update where you need to change all item sockets on database or any other attribute. With a simple query on a structured table, you can make that update easy & fast without any problem.

We had like 2 / 3 years of failed servers until we finally got a stable database system (noob at MySQL settings, noob at Table design, problems with MySQL & C++ communication until we took a look at how Trinity Core handled it).





Good, but i already do it to without much problems (Except procedures, you need to re-create all of them or just simple change server side logic)
The problem still in server design anyway not only at field as varbinary or something else bro.
 
Junior Spellweaver
Joined
Aug 7, 2010
Messages
137
Reaction score
137
Good, but i already do it to without much problems (Except procedures, you need to re-create all of them or just simple change server side logic)
The problem still in server design anyway not only at field as varbinary or something else bro.

Indeed, that's why we created new software really from scratch.

Also the main reason why we decided to make a new server was to learn new techniques and how to apply them. That helps a lot when you want to start your own project or even improve your own.

The final result is:
LoginServer (acts like old ConnectServer & JoinServer).
GameServer (old GameServer & DataServer).
ServerLink (old ExDB).

We don't use a DataServer. All character related Query are on GameServer, and we use CallBack to get results so gameserver won't freeze or be delayed because of that (screenshots at the end).

We use something similar to ExDB, but we called ServerLink because it connects every gameserver of same group (Like code from 0 to 19). This softwares manages Chat between gameservers (post, whisper, ...), Guild, Castle Siege, Crywolf & Arka War (everything that should be applied on every gameserver). This software handles all queries related to those events i listed.

This one is a Callback after verifying if a selected character to Login exists. It returns character GUID that is what we use to connect data between tables.


We use the information obtained above to create a Query Holder that will load all character data and will return to a callback function.


And this is the callback function


And this is how a function that loads information looks like



IMPORTANT:
The reason why i explain all this and show what we have done is NOT to say that old system is bad and our system is good. I just want to show that, if you want, you can create your own server.
Of course you may encounter problems during the process, we struggle a lot until we finally got something stable.
Also is a fun process too, you are going to like it.
 
Last edited:
Back
Top