Auto-Deletion of Table Rows

Question

I'm new to MySQL, but however I need MySQL to work as it will be at the center of my new SANS (Server Address Name System) system. The reason for this system is to provide a replacement system for gameservers, since the default Gamespy service that some games use is being switched off at the end of next month.

The function of MySQL in SANS is to store the IPs and ports of active gameservers (which are patched to send info to MySQL), and then make the clients (again, patched to retrieve the information from MySQL) add the servers to their in-game server lists.

Of cause, the issue here is that gameservers can easily go offline for any one of 1,000+ reasons, and we don't really want the client's game showing gameservers that are offline, mainly because:

• If we need to block any fake gameservers, these fake gameservers will still be in the server list (and also the MySQL database)
• It will clog up the server list very quickly
• Temporary servers such as home, development and test servers will still be in the list
• If a servers' IP and/or port changes for any reason (for example the server IP is dynamic), there will be duplicate servers in the list, and clients may not know which one to pick.

I've thought of a couple of solutions, including making the client ping each gameserver in turn to check to see if it is online, but this is not ideal for a couple of reasons:

• The server computers' administrator may have WAN ping switched off, meaning that although our gameserver may be online, it won't show in the list
• The pings of clients may be seen as suspicious behaviour to the various server administrators that administrate the networks that the server computers sit on, meaning that the client could be blocked because of this.

I've thought of a simple solution: get MySQL (or phpMyAdmin) to remove each table row 10 seconds after it has been added. Is this sort of behaviour even possible?

I'm on Windows Server 2008 R2, with latest MySQL server and Xampp.

Answer 1

I think you could use a MySQL trigger to accomplish this (I'm not sure about the 10 second delay), but I believe there's a better solution:

You could add a column called Status to whichever table stores the gameserver information. Then you could use flags to differentiate types of gameservers: fake, test, active, inactive, etc. Next you would filter what the user sees to only show active gameservers. If the server doesn't report back every 10 seconds, the flag is simply marked as inactive. And finally you could schedule a job to run once a day to clean up records older than 24 hours.

If this doesn't work for your particular problem, let me know and I'll look into coding the trigger.