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

[ptr0x]

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.

[MentaL]

[-UnicorN-]

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 ^^

[nevS]

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 ;)

[ptr0x]

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

[mesosa]

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.

[SmileYzn]

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

[ptr0x]

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?

[nevS]

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 'A' 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.

[SmileYzn]

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 'A' 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.

[nevS]

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.

[ptr0x]

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.

[mesosa]

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).


https://puu.sh/BpKyu/c12023810d.png
https://puu.sh/BpKzB/cf6aa73d4d.png
https://puu.sh/BpKzQ/bc821c6309.png

[SmileYzn]

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).


https://puu.sh/BpKyu/c12023810d.png
https://puu.sh/BpKzB/cf6aa73d4d.png
https://puu.sh/BpKzQ/bc821c6309.png

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.

[mesosa]

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.
http://puu.sh/Bq7zS/2db5b9e93b.png

We use the information obtained above to create a Query Holder that will load all character data and will return to a callback function.
http://puu.sh/Bq7Dj/2db42e80d8.png

And this is the callback function
http://puu.sh/Bq7FF/19b279c8ba.png

And this is how a function that loads information looks like
http://puu.sh/Bq7J4/5e464b373a.png


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.