next »

[ITS_Mike]

What is the shutdown priority for MySQL's server process on Windows?  I setup one of my game servers as a service, but I need to ensure that it has a higher shutdown priority than MySQL, otherwise character data could become lost when the computer is restarted.  I have not found any tools that allow me to view the current shutdown priority of a process.  I asked in the #mysql channel on Freenode, but no on in there knew the answer.

[bateleur]

Is that really the right way to solve that problem?

Suppose a player's client sends data to the server. What does the client do if it receives no response? If the client assumes that no response means failure (as it should) then there's no need for your game to shut down with higher priority than MySQL is there? Failure to perform a MySQL operation due to server shutdown is exactly the same as any other kind of failure. If you have time you return a fail code to the client. If you don't have time then the client receives nothing, which is also fine.

Possibly you are worried about MySQL shutting down in the middle of a string of queries, but shutdown priority isn't a great way to handle that either because your game still has to cope gracefully with its own server-side component going down in the middle of the same string of operations. If possible (and for most games it is) make sure that each client request which sets state on the server is simple enough to be expressed as a single database query.

[ITS_Mike]

Losing character data is a big problem.

It sounds like you have my question mixed up a bit...  I am not writing a MySQL server.  I am writing a game server which communicates with a MySQL server to store player data in a database.  If the MySQL server goes down before the game server (on system shutdown) the game server will be unable to put the character's most recent data in the database, causing a rollback.  The amount of data lost can be limited by saving every so often, but that is just ignoring the problem rather than fixing it.

[bateleur]

Well... either that or you haven't understood my answer!

I know you're not writing a MySQL server. My central point is that forcing the MySQL server to shut down second doesn't help you, because you need to make your system robust against incomplete operations anyway. Otherwise when you suffer a component failure on your server it risks breaking your database integrity.

If what you're saying is that you store all your game data in a volatile state within the server: don't. Use the database as your state store and update it whenever persistent data changes. That way a shutdown is only ever as harmful to player data as it is surprising. This is good, since most shutdowns are not surprising.

[ITS_Mike]

That would cause a great deal of traffic.  However, assuming that won't be a problem, you still have data loss if the data changes and the MySQL server is down already due to the computer being shutdown.  This could of course be fixed if the game server starts the MySQL service again.  I don't know if it is possible to start a service while the computer is being shut down though.

[Zaphos]

That would cause a great deal of traffic.  However, assuming that won't be a problem, you still have data loss if the data changes and the MySQL server is down already due to the computer being shutdown.

At most you're talking about losing a few seconds of play time, in that case?

[ITS_Mike]

Which could cause corruption of the database due to an incomplete state.

I am currently finding out if it is possible to have the game server start the MySQL server if it is down.  I am sure that it will work under normal circumstances (unless privileges prevent it) but I am worried about attempting to do that while the computer is shutting down.

[Kaelan]

Which could cause corruption of the database due to an incomplete state.

I am currently finding out if it is possible to have the game server start the MySQL server if it is down.  I am sure that it will work under normal circumstances (unless privileges prevent it) but I am worried about attempting to do that while the computer is shutting down.

No matter how much you do to try and get ordered shutdown, you're still not going to be robust against system shutdown or power loss. Just do it right and use the database as your backend, or design a system that handles lost updates correctly. There's lots of existing documentation out there describing high performance database architecture and caching systems; you can easily apply it to your problem.

(The common solution to incomplete database state is transactions, FYI.)

[ITS_Mike]

Which could cause corruption of the database due to an incomplete state.

I am currently finding out if it is possible to have the game server start the MySQL server if it is down.  I am sure that it will work under normal circumstances (unless privileges prevent it) but I am worried about attempting to do that while the computer is shutting down.

No matter how much you do to try and get ordered shutdown, you're still not going to be robust against system shutdown or power loss. Just do it right and use the database as your backend, or design a system that handles lost updates correctly. There's lots of existing documentation out there describing high performance database architecture and caching systems; you can easily apply it to your problem.

(The common solution to incomplete database state is transactions, FYI.)

Thanks for the input, but it will take too long to redesign the database code.  I might try it on one of my future projects though.

Edit: I have decided that getting this to work well will take too long, so the server won't be running as a service.  I may come back to this at some point in the future.

[Kaelan]

Edit: I have decided that getting this to work well will take too long, so the server won't be running as a service.  I may come back to this at some point in the future.

Given that, you can probably try issuing a mysql shutdown when your server gets notified that Windows is shutting down. In the common case (User-requested windows shutdown) your server app will get a few seconds to 'clean up' before Windows decides it's hung. Just be aware that mysql can sometimes take minutes+ to shut down since it does lots of cleanup.

Also, just in case you haven't seen it:
http://support.microsoft.com/kb/203878