So you thought my post on networking a few days ago was boring? Get ready for database fun!
Normally I would just write about what worked, but in this case I’d rather trek through the foggy world of missteps I went through yesterday to make the various errors searchable, maybe my confusion and pain can be of help to some poor future googler.
Why do you want to directly talk to mySQL from a Unity app?
The first thing you normally see when searching online for help with integrating SQL and Unity is “you idiot, it’s too dangerous to ever connect directly to your server database!”
Well, for the CLIENT APP, that’s correct, it’s a horrible idea because anybody can steal your logon credentials and ruin your database. (I love you Team Meat, but … yeah)
Instead, you need a gateway of some kind (for high scores, this is usually a .php page on a website) that can do things like limit score additions from the same day from the same IP address and other types of ‘hardening’.
But for a game server that needs to access lots of data, a direct connection to a database from your Unity-based dedicated server is absolutely vital.
Step 1: Getting the MySQL Connector/Net files
I downloaded the files for “.NET & Mono” and drag and dropped the 4.5 branch files into my Unity project. (spoiler, this was dumb, I only needed one)
Note: Managed .dll files do NOT have to be dragged into any specific folder like C++ plugins do, anything inside of your Assets folder is ok, even sub-folders.
Step 2: Activating Unity’s 4.5 .Net support
From inside of Unity, I clicked on one of the newly added .dll files. Near the bottom of the property window (it’s easy to miss) it gave this message:
“Plugin targets .NET 4.x and is marked as compatible with Editor, Editor can only use assemblies targeting .NET 3.5 or lower, please unselect Editor as compatible platform.”
To fix this, I changed Unity’s scripting runtime version to ‘Experimental .NET 4.6 Equivalent’.
Step 3: Noticing I added the wrong files
I added ‘using MySql.Data;‘ to a .cs as a test. It compiled, but when I ran the game I got a “TypeLoadException” without any details. I installed Unity 2017.3.01f (beta) because I heard it gave better description of these kinds of errors, and it did:
TypeLoadException: Could not load type of field 'MySql.Data.Entity.MySqlMigrationCodeGenerator+<>c:<>9__4_0' (1) due to: Could not load file or assembly 'EntityFramework, Version=5.0.0.0,'
EntityFramework? Ohhh, so that zip had multiple versions of MySQL Connector and this one wants EntityFramework, whatever that is. I removed them all (and the .xml files too) and re-added MySql.Data.dll.
Note: You probably don’t need to update to a beta version like I did; this isn’t recommended usually. Obviously I just like to live dangerously.
Great, no errors and I can do a ‘using MySql.Data;‘ just fine.
Step 4: Adding support for System.Data
I added ‘using System.Data;‘ as we’ll need that too. Uh oh, got an error there. So I dragged and dropped <unity install dir>\Editor\Data\MonoBleedingEdge\lib\mono\4.6-api\System.Data.dll into my project. (I’m attempting to use the version that comes with Unity that matches the .NET version I’m using)
Well, it now compiles, but now I’m seeing ‘loading script assembly “assets/plugins/system.data.dll’ failed!’ error in the Unity log. Is this the wrong version?
The error goes away if I use <Unity dir>\Editor\Data\MonoBleedingEdge\lib\mono\2.0-api\System.Data.dll instead. Fine, I’ll use that. (spoiler, this causes problems later)
I wrote some test code using MySQL Connector, it works fine in the Unity Editor! I’m able to create/destroy/modify SQL databases. No warnings in Unity either! But uh oh, now I’m seeing this in Visual Studio:
warning MSB3277: Found conflicts between different versions of the same dependent assembly that could not be resolved.".
I guess Visual Studio is set to use a newer version and having the old System.Data.dll referenced is bad?
Not only that, but when I build a Win64 stand-alone version, when running it, I got this error:
NullReferenceException: Object reference not set to an instance of an object at MySql.Data.MySqlClient.MySqlPoolManager.GetKey (MySql.Data.MySqlClient.MySqlConnectionStringBuilder settings) [0x00012] in <861c41359f7d4496a8fdf269ae744290>:0 at MySql.Data.MySqlClient.MySqlPoolManager.GetPool (MySql.Data.MySqlClient.MySqlConnectionStringBuilder settings) [0x00000] in <861c41359f7d4496a8fdf269ae744290>:0 at MySql.Data.MySqlClient.MySqlConnection.Open () [0x0016d] in <861c41359f7d4496a8fdf269ae744290>:0 at RTSqlManager.Init (System.String serverIP, System.String userName, System.String userPassword, System.String databaseName) [0x00083] in F:\Unity\SQLTest\Assets\RT\MySql\RTSqlManager.cs:51 at SQLTest.Start () [0x00030] in F:\Unity\SQLTest\Assets\_Script\SQLTest.cs:64
Uh… ok, I guess I used the wrong version of the .dll or something, I can see it’s being included but … when I look around, I can see other .DLL’s are coming from directory is actually from <Unity dir>\Editor\Data\MonoBleedingEdge\lib\mono\unityjit and not where I took them from. Using the System.Data.dll from this “unityjit” dir fixed the Visual Studio warning AND fixed the standalone versions (Win and Linux at least, that’s all I tested).
It works! So is it fast?
Hallelujah! Everything runs ok, both local in Windows (to test this, you need to setup a MySQL server on Windows) and on linux. (I’m actually using MariaDB, a MySQL fork, that works fine too)
Speed seems comparable to using the C++ version. As is my way, I wrote a Unity app with built-in Telnet server to test speeds, here are the results on a dedicated linux server:
Command > ? /? for help /create to create testtable /delete to remove testtable /add <count> to add a bunch of records /show <index> to look at a specific record /stats to show current status of testtable and record count /seektest <count> to benchmark loading <count> random accounts and reading ID and RandomNum from them /updatetest <count> to benchmark updating <count> random accounts with a new RandomNum and RandomIndexedNum /ranktest <count> Rank, benchmark, and view top <count> records Command > /updatetest 1000 Updating RandomNum and RandomIndexedNum for the first 1000 accounts, each in a different SQL statement Updated 999 accounts in 5.860773 seconds. (0.005860773 p/account) (time taken: `w5.869883 seconds``) Command > /seektest 1000 Reading ID and RandomNum for 1000 random accounts in 1000 different SQL SELECT statements... Read from 1000 accounts in 0.1809998 seconds. (0.0001809998 p/account) (time taken: `w0.1875515 seconds``) Command > /ranktest 1000 Ranking top 1000 random numbers... #1 ID: 50117 RandomNum: 1999986 #2 ID: 31319 RandomNum: 1999854 #3 ID: 5009 RandomNum: 1999835 #4 ID: 7211 RandomNum: 1999784 #5 ID: 40406 RandomNum: 1999772 #6 ID: 9686 RandomNum: 1999698 #7 ID: 18099 RandomNum: 1999644 #8 ID: 41235 RandomNum: 1999593 #9 ID: 12447 RandomNum: 1999505 #10 ID: 37578 RandomNum: 1999396 (not displaying the rest...)Took 0.02680588 to rank and get 1000 records. #1 ID: 35 RandomIndexedNum: 19954265 #2 ID: 552 RandomIndexedNum: 19942897 #3 ID: 161 RandomIndexedNum: 19898997 #4 ID: 874 RandomIndexedNum: 19872310 #5 ID: 484 RandomIndexedNum: 19859544 #6 ID: 453 RandomIndexedNum: 19777522 #7 ID: 649 RandomIndexedNum: 19776457 #8 ID: 851 RandomIndexedNum: 19769359 #9 ID: 488 RandomIndexedNum: 19741107 #10 ID: 727 RandomIndexedNum: 19711418 (not displaying the rest...)Took 0.0008430481 to rank and get 1000 indexed records. (time taken: `w0.02984619 seconds``)
To break it down (note: this database has 50,000 records):
Update a record: 5 ms
Seek and read from a random record: 0.18 ms
Sort 50k unindexed ints and return the top 1000: 27 ms
Sort 50k indexed ints and return the top 1000: 0.84 ms
Final thoughts
Well, I probably should have created the table with ENGINE=MEMORY as I suspect the update record test was hampered by IO writeback (this system doesn’t have an SSD), but overall I’m happy with these speeds.
I wonder how the speeds would compare with SQLite? Is the fact that I’m using a beta version of Unity change/break anything I did? I don’t know, but it works fine now so whatever.