Game Engine and Mysql

So im wanting to send some data from a game to a mysql database.

Through some research ive found out i should probably use the mysql connector/python modules

However before i dive into this i realise that when it comes to compiling all this…i will probably run into a fair few issues which im not so keen to get into :P.

Is there any way anyone knows how to get the basic data sent to this database?
Collecting the data and turning it into xml is also easy in python. So the other option is to convert xml to mysql on server side…(which is not my side :P) and so make it the IT guys problem haha. Its for game stats and stuff.

Any ideas? anyone tried this?

I wouldn’t recommend having clients connect directly to your database as this opens up a whole bunch of security issues (try reading about what happened with Super Meat Boy). Clients should just send the raw data to the game server and let the game server handle inserting it into your database. I’ve used MySQLdb for this, and it’s fairly easy to use provide you know a little SQL.

Yes i suggested they handle it on their side. But i think everyone likes to throw the responsibility around :slight_smile:

Unfortunately MySQLdb i think is no longer compliant with the current python shipping with Blender? 3.x?

on the security side i think the entire database is secure and isolated. So its not a big issue. I suppose people could mess with high scores etc…:stuck_out_tongue:

Abstracting DB access through a process on the server is absolutely the way to go.
1 - you can audit the data
2 - no need to include SQL libraries with the client

You could actually write it yourself (in Python2 with MySQLdb) and send that to the IT staff, telling them it needs to auto-start on boot.

Communicating between the game and your custom server process could be done with a TCP socket.

Why is the server running Blender? If for some reason that’s necessary, you can just have it communicate with a Python 2 process running on the same machine or try searching for a Python 3 version of MySQLdb (or use a tool to convert the existing Python 2 version).

Why is the server running Blender?

If its doing this ? why do you have to write to a MYSQL database anyway? why not set up your own database structure, than you can control, manipulate, and encrypt. Your own server could control what is written and not. Is there a reason it has to be MYSQL?

@pqftgs thanks for the TCP socket idea! ive seen render farms work this way,

@Mobius, oh i was thinking if mysql was running from client (which is not my preferred method)

Agreed dont want to look at sql libraries with client.

The end result has to be on a mysql database this is outside my control. So i think the solution to go with is send raw data to server side and have some script on that side handle auditing the data and converting to something useful for the database. Maybe send an xml or something from bge.
This is of course if the OS on client side is going to allow these data sending…oh man…in over my level of expertise here :stuck_out_tongue: i just write the in game functions!!!

Dealing through a restrictive webhost (just guessing) really limits what you can do in a sane manner.
I’d consider renting a cheap VPS where you have absolute power. Probably looking at $5 - $20 per month.

Otherwise it might be time to sit down with the server admin and sort out this permissions breakdown.
It’s going to be reeealllly hard if you don’t have access to the server and the other party refuses action.

Doing client/server communication for games has a lot of complexity. There are a number of techniques you can use to exchange data and each one has pros/cons depending on the details of your system.

Do you have more details about your server environment and the other people on your team? It sounds like your server is a typical “web host” that provides you with a MySQL database, FTP server, and a web server for making basic websites. Often these have tools that can automatically configured your website to be “a Wordpress site” and similar features. Is that what you are using?

The easiest option for accessing the server would be to expose the MySQL interface and connect directly to it from your client. The plus to this idea is that the complexity is low. The SQL server is already there you just need to get the correct driver to connect to it. The minuses are pretty big, connections to SQL servers degrade when there is a lot of latency between your client and server (like when you connect over the Internet). SQL servers are not really designed to be publicly facing and as other have noted it is a security risk to expose it directly. Most SQL servers are deployed behind a firewall with strict access controls.

If the game data you are storing is not real-time data (i.e. just high scores and not anything related to the game state) then you might consider setting up a web service (fancy name for ‘web site’) to handle your data. Here you have a web server in addition to your SQL server. The SQL server is not connected directly to the Internet. Instead you have your website on the Internet and your game client sends data to the website and the website stores the results in the SQL database.

This has some good points.

  • This is the industry standard for how people host SQL data on a public server.
  • You client and server talk over regular HTTP
    • This is friendly for going through firewalls.
    • You can enable encryption just by using HTTPS if you choose.
  • Python has several libraries to make it easy to send HTTP requests to servers and they are straight forward to use.
  • You are still using existing (debugged/stable) servers and client libraries to handle all the network communication.

The minus are:

  • You now have an extra server in the system. That server and the extra links between the client, web server, and SQL server create complexity and can introduce bugs.
  • You might have to learn how write some simple PHP scripts to get your server to communicate with the SQL server.
  • HTTP tends to be fairly high latency. It is fine for downloading bulk data like high scores, but if you wanted to store current player positions it would not be a good choice.

If you need a real-time game server, then you have to write a custom process that runs on your server. The server would have to accept raw TCP or UDP data and process it accordingly. This means that you are going to design and debug your own communication protocol in addition to creating your own server process and client libraries to handle that data exchange.

Writing network protocol systems is a pretty big task. Look at the credits for any AAA game with multi-player capability and you will typically see at least one person that was just the “Network Programmer”.

It seems unlikely that if you don’t have any control over the SQL server then you probably also don’t have the permissions to run a custom game server.

Thanks all for the suggestions.

I have full access to the server permissions (its not standard web host) some mate of mine who has set up his own sql server and data centres.

So looks like the thing will be sorted hopefully soon :slight_smile: I will still be writing the python script along side the technician ON SERVER SIDE :P. So all game has to do is send basic data via http or something similar. Shouldnt be any latency issues its just high score.