Seriously, why haven't I heard of this before. This has to be the single most awesome feature I've ever heard of. Apparently MySQL (by adding a single flag in the config file) supports execution of statements at regular intervals. I am currently working on my online browser-based HTML5 game (Celestial Empires Online) and have gotten to the point where I need constant data manipulation in the backend (not user generated), so I started researching the best way to execute MySQL stored procedures/functions from CRON when I ran across this:

CREATE EVENT myevent    ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR    DO      UPDATE myschema.mytable SET mycol = mycol + 1;

Now the entire game backend can be ran completely within MySQL, using only apache2 as a transport system to pass data to and from mysql. No managing of connections, no persistence needed, no custom code or shell scripts. Just MySQL, PHP/Apache, and the user's web browser/&#106avascript.<br><br>The web sure has grown up :D

Quote: Original post by essial
Apparently MySQL (by adding a single flag in the config file) supports execution of statements at regular intervals. I am currently working on my online browser-based HTML5 game (Celestial Empires Online) and have gotten to the point where I need constant data manipulation in the backend (not user generated) [...]

Well a simple cronjob can do an update. The reason people don't use SQL to do it is because they don't want to program their full game's update function in SQL.

I'm writing a real-time HTML 5 game at the moment actually. I wrote a C++ comet server to handle connections and data efficiently. It runs the game loop in the server code where all the active user's information is in RAM. This results in infrequent accesses to the database. I'm on a basic hosting plan though so I'm keeping everything at like 0% CPU with very little database interaction so as to stay within acceptable terms.

I assume you're trying to scale to a lot of users though?

I'm using a $10/month rackspace cloud server running ubuntu 10.04 so all good here on usage. I just don't see why you ~wouldn't~ want the DB updates to be called from.. well.. the db directly, seems like less steps to me. MySQL query browser has a nice UI that makes things easy to find, and everything is in stored functions and procedures so that makes things even clearer.

In most games, the application layer contains at least some data and functionality that you don't need/want to store in a database backend. Updating this kind of system requires that the update procedure is done outside of the database anyway.

That said, turn-based web strategy games could well be run almost entirely in a database engine, in which case the built-in timer would be very convenient.