OTServs are open source MMORPGs with a huge community. Mostly all of them have a serious problem: if the server crashes, people can clone items. This is a dirty trick that can be executed because the state of a player is saved to the SQL database when the player logs out. This means the the database is not always in valid state. If 5 different players logged out with the same item, the item would be saved five times on the DB! So a crash would effectively clone the items. The OTServ core is several years old and the main branches still don't provide a solution.

As online games, they consist of a world of dynamic data that changes at a very fast pace. There are thousands of online players and millions of registered players. The data is stored in MySQL. There are tables such as players, player_items, player_skills, player_storage and so on, some with millions of rows. I believe the problem could be that overhead brought by SQL makes it impossible to save all the data at the same pace it happens - hundreds of updates per player per second.

Why OTServs have that problem, what's the impact of the use of SQL databases on the issue and how could it be solved?

  • 2
    Why not saving the player state when the said state changes? And only save the parts that have changed? The kind of things that are easy with SQL (via UPDATE) and not that easy with raw files? – Cyrille Ka Mar 15 '13 at 2:21
  • 4
    Hm, why didn't you consider the obvious approach? Save the data when they change. The root cause is the hacks, KISS. – yannis Mar 15 '13 at 2:22
  • 1
    @Dokkat Had you structured your code to work with a database from the beginning, that wouldn't really be an issue. But no, I'm not suggesting you save on every move, but you could save the player's state once per minute, for example. – yannis Mar 15 '13 at 2:29
  • 1
    Notice it's not me, it's a big community of programmers, some really good. If the solution was trivial I think someone should have had come up with it already. Instead item cloning is, as of today, still a huge problem for OTServs and the suggestion is simply "don't crash"! Which IMO is ridiculous. Things break! – MaiaVictor Mar 15 '13 at 2:31
  • 2
    Why is the server crashing in the first place? Why isnt there any redundancy? When items are created, why arent they tracked with a unique id? I suspect the problem here isnt storage, its the overall design of the system. – GrandmasterB Mar 15 '13 at 4:05

Well, to some extent database design was the problem, since giving each item a unique ID from the start would have solved your particular problem.

However, your bigger issue is that the SQL solution wasn't the fastest possible. That will pretty much always be true. SQL is designed to go as fast as possible while preserving ACID guarantees and while following a relational model. If you don't care about those things then it may well not be the best solution.

For raw speed, it would be hard to beat dumping your in-memory structure in a giant block to disk. Then you don't even have to serialize/deserialize. If you are always going to save/load the whole world state at once and can deal with the pain of integrating that with the parts done with SQL, and you really need that speed, it might be worth it.

If you want to take everything out of the database and only use JSON, you will get to solve all the concurrency problems the database handles for you (unless it is still feasible to save/load everything every time, which I doubt). That really won't be easy.


Blame this one both the design adopted by the OT community and MySQL.

This is really not somewhere that I would consider any kind of SQL database server, and especially not one with as many flaws as MySQL. Possibly your problems could be solved with PostgreSQL but even that is not a good fit to this problem. You would do better to look for a solution based on message queueing where all transactions are messages. Make sure that you use/configure an MQ solution that guarantees delivery of messages. Then the app only has to get its transaction into an MQ broker, and your internal systems can take care of writing data to a datastore if necessary, and resolving conflicts.

However, be warned, that if you move to message queuing you will likely have to deal with issues such as serial numbers (similar to DNS zone serial numbers), timestamps, and resolving synchronization corner cases. But at least this is a well documented art. Usually this kind of code is hidden inside database server replication software and never revealed to the customer, but the computer science community has developed all the algorithms in the open, at conferences on Very Large Data Bases, and so on. Hunt for information on CiteSeer, follow up the citations in the papers you read, and you will find everything that you need to implement this yourself.

But please implement this in a modern language like Python or Scala first, and only if you really need to, translate parts of it to lower level C.

More on why I feel that MySQL is a risky choice for this use case. You have asked about a scenario where synchronization of replicas is an important issue. I have seen so many issues with just master-slave replicas on MySQL 5.5 that I simply don't trust it. But don't trust me. Try two or thtree solutions and test them under load with your application. MySQL is fine for webapps that use the ActiveRecord design pattern, but other open source dbs do exist and are better for many scenarios. For anything as important as a dbms, you need to do comprehensive testing if you want to avoid suffering later.

A lot of people use a simple form of MySQL replication which basically sends a stream of commands from the master to the replica server. You can even chain this together so that the slave sends a stream of commmands onward to yet another server, perhaps one that is considered the "backup" copy and is never used for select queries or to replace a crashed master db. The problem seems to arise because not all commands will produce the same result when run on different servers. Commands using temporary files, random number functions and others, cannot be replicated. In addition I don't believe that MySQL enforces identical configuration on the replicas so you could get DDL that creates tables with different default character sets and sorting rules. Other databases have more replication systems but even Oracle can go haywire if its sequence numbers get munged up.

You are building a banking system and it could easily end up handling a much higher volume of transactions than the biggest New York banks, so the common Enterprise solution of an SQL database is probably not right for you. Tell your developers that they are working on a banking system and that it must be IMPOSSIBLE for money/objects to replicate themselves in multiple accounts. Give them the time and resources to build this system and tell them to read the research that is openly available, i.e. they do not have to reinvent the wheel, just implement it in a way that gives more control than a prepackaged db replication system.

  • 2
    specially not one with as many flaws as MySQL and what flaws are these exactly? And how do these flaws have anything to do with this specific question? – yannis Mar 15 '13 at 3:47
  • Are you saying this would help because the application would be able to send all the messages asynchronously to the MQ, which would be an architecture fast enough to scale? – psr Mar 15 '13 at 4:03
  • @psr: close. The message broker would save the messages and make sure that they are delivered. This is usually done with an MQ broker cluster which actually has a journalling database backend to ensure that messages get delivered even after network outages and server crashes. Then you put a layer of software between the MQ broker and your real database (the one where you do reads and updates) to keep things in sync. But if you really want to outdo the rest of the massive multiplayer game developers you will have to do some serious work to get the sync algorithm right. – Michael Dillon Mar 15 '13 at 9:33
  • 1
    @MichaelDillon Excellent, include that in your answer please (and turn that sentence from a borderline trolling statement to valid technical criticism). Personally I never had any issues with master-slave replicas on MySQL on any of my applications (and having spend some time in the financial sector, I've had the opportunity to work with terabyte size databases). But that doesn't mean issues don't exist, I was always lucky to work with awesome database administrators and in all likelihood they solved any issues before I even knew about them. – yannis Mar 15 '13 at 9:35
  • @Yannis: took your advice and incorporated the comment into my answer. And my experience was in a business that had never hired DBAs, just let the Unix sysamdins do whatever they think is necessary. Not good. Even an inexperienced DBS is worth it, because they get to focus on one complex area at the core of your business, so they will always do better than a sysadmin who is constantly fighting fires all over the place. – Michael Dillon Mar 16 '13 at 1:20

Not the answer you're looking for? Browse other questions tagged or ask your own question.