{"id":2132,"date":"2017-12-05T10:31:57","date_gmt":"2017-12-05T01:31:57","guid":{"rendered":"https:\/\/www.codedojo.com\/?p=2132"},"modified":"2017-12-05T15:48:42","modified_gmt":"2017-12-05T06:48:42","slug":"how-to-get-mysql-connector-net-working-with-unity","status":"publish","type":"post","link":"https:\/\/www.codedojo.com\/?p=2132","title":{"rendered":"How to get MySQL Connector\/Net working with Unity 2017"},"content":{"rendered":"

So you thought my post on networking<\/a> a few days ago was boring?\u00a0 Get ready for database fun!<\/p>\n

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.<\/p>\n

Why do you want to directly talk to mySQL from a Unity app?<\/h1>\n

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!”<\/p>\n

Well, for the CLIENT APP<\/em>, 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)<\/a><\/p>\n

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’.<\/p>\n

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.<\/p>\n

Step 1: Getting the MySQL Connector\/Net files<\/h1>\n

\"\"<\/a><\/p>\n

I downloaded the files<\/a> for “.NET & Mono” and drag and dropped the 4.5 branch files into my Unity project. (spoiler, this was dumb, I only needed one)<\/p>\n

Note<\/strong>: Managed .dll files do NOT have to be dragged into any specific folder like C++ plugins do, anything inside of your Assets\u00a0 folder is ok, even sub-folders.<\/p>\n

Step 2: Activating Unity’s 4.5 .Net\u00a0 support<\/h1>\n

From inside of Unity, I clicked on one of the newly added .dll files.\u00a0 Near the bottom of the property window (it’s easy to miss) it gave this message:<\/p>\n

“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.”<\/strong><\/p>\n

To fix this, I changed Unity’s scripting runtime version to ‘Experimental .NET 4.6 Equivalent’.<\/p>\n

\"\"<\/a><\/p>\n

Step 3: Noticing I added the wrong files<\/h1>\n

I added ‘using MySql.Data;<\/strong>‘ to a .cs as a test.\u00a0 It compiled, but when I ran the game I got a “TypeLoadException<\/strong>” without any details.\u00a0 I installed Unity 2017.3.01f (beta) because I heard it gave better description of these kinds of errors, and it did:<\/p>\n

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,'<\/pre>\n
EntityFramework?\u00a0 Ohhh, so that zip had multiple versions of MySQL Connector and this one wants EntityFramework, whatever that is.\u00a0 I removed them all (and the .xml files too) and re-added\u00a0MySql.Data.dll<\/strong>.<\/p>\n
Note:<\/strong> You probably don’t need to update to a beta version like I did; this isn’t recommended usually.\u00a0 Obviously I just like to live dangerously.<\/p>\n
Great, no errors and I can do a ‘using MySql.Data;<\/strong>‘ just fine.<\/p>\n
Step 4: Adding support for System.Data<\/h1>\n
I added ‘using System.Data;<\/strong>‘ as we’ll need that too.\u00a0 Uh oh, got an error there.\u00a0 So I dragged and dropped\u00a0\u00a0<unity install dir>\\Editor\\Data\\MonoBleedingEdge\\lib\\mono\\4.6-api\\System.Data.dll<\/strong>\u00a0into my project.\u00a0 (I’m attempting to use the version that comes with Unity that matches the .NET version I’m using)<\/p>\n
Well, it now compiles, but now I’m seeing ‘loading script assembly “assets\/plugins\/system.data.dll’ failed!’<\/strong>\u00a0error in the Unity log.\u00a0 Is this the wrong version?<\/p>\n
The error goes away if I use <Unity dir>\\Editor\\Data\\MonoBleedingEdge\\lib\\mono\\2.0-api\\System.Data.dll<\/strong> instead. Fine, I’ll use that. (spoiler, this causes problems later)<\/p>\n
I wrote some test code<\/a> using MySQL Connector, it works fine in the Unity Editor!\u00a0 I’m able to create\/destroy\/modify SQL databases. No warnings in Unity either!\u00a0 But uh oh, now I’m seeing this in Visual Studio:<\/p>\n
warning MSB3277: Found conflicts between different versions of the same dependent assembly that could not be resolved.\".<\/pre>\n
I guess Visual Studio is set to use a newer version and having the old System.Data.dll referenced is bad?<\/p>\n
Not only that, but when I build a Win64 stand-alone version, when running it, I got this error:<\/p>\n
NullReferenceException: Object reference not set to an instance of an object\r\n at MySql.Data.MySqlClient.MySqlPoolManager.GetKey (MySql.Data.MySqlClient.MySqlConnectionStringBuilder settings) [0x00012] in <861c41359f7d4496a8fdf269ae744290>:0 \r\n at MySql.Data.MySqlClient.MySqlPoolManager.GetPool (MySql.Data.MySqlClient.MySqlConnectionStringBuilder settings) [0x00000] in <861c41359f7d4496a8fdf269ae744290>:0 \r\n at MySql.Data.MySqlClient.MySqlConnection.Open () [0x0016d] in <861c41359f7d4496a8fdf269ae744290>:0 \r\n 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 \r\n at SQLTest.Start () [0x00030] in F:\\Unity\\SQLTest\\Assets\\_Script\\SQLTest.cs:64<\/pre>\n
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\u00a0<\/strong>and not where I took them from.\u00a0 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).<\/p>\n
It works!\u00a0 So is it fast?<\/h1>\n
Hallelujah! Everything runs ok, both local in Windows (to test this, you need to setup a MySQL server on Windows) and on linux.\u00a0 (I’m actually using MariaDB, a MySQL fork, that works fine too)<\/p>\n
Speed seems comparable to using the C++ version.\u00a0 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:<\/p>\n
Command > ?\r\n\/? for help\r\n\/create to create testtable\r\n\/delete to remove testtable\r\n\/add <count> to add a bunch of records\r\n\/show <index> to look at a specific record\r\n\/stats to show current status of testtable and record count\r\n\/seektest <count> to benchmark loading <count> random accounts and reading ID and RandomNum from them\r\n\/updatetest <count> to benchmark updating <count> random accounts with a new RandomNum and RandomIndexedNum\r\n\/ranktest <count> Rank, benchmark, and view top <count> records\r\n\r\n\r\nCommand > \/updatetest 1000\r\nUpdating RandomNum and RandomIndexedNum for the first 1000 accounts, each in a different SQL statement\r\nUpdated 999 accounts in 5.860773 seconds. (0.005860773 p\/account) (time taken: `w5.869883 seconds``)\r\n\r\nCommand > \/seektest 1000\r\nReading ID and RandomNum for 1000 random accounts in 1000 different SQL SELECT statements...\r\nRead from 1000 accounts in 0.1809998 seconds. (0.0001809998 p\/account)\r\n (time taken: `w0.1875515 seconds``)\r\n\r\nCommand > \/ranktest 1000\r\nRanking top 1000 random numbers...\r\n#1 ID: 50117 RandomNum: 1999986\r\n#2 ID: 31319 RandomNum: 1999854\r\n#3 ID: 5009 RandomNum: 1999835\r\n#4 ID: 7211 RandomNum: 1999784\r\n#5 ID: 40406 RandomNum: 1999772\r\n#6 ID: 9686 RandomNum: 1999698\r\n#7 ID: 18099 RandomNum: 1999644\r\n#8 ID: 41235 RandomNum: 1999593\r\n#9 ID: 12447 RandomNum: 1999505\r\n#10 ID: 37578 RandomNum: 1999396\r\n(not displaying the rest...)Took 0.02680588 to rank and get 1000 records.\r\n#1 ID: 35 RandomIndexedNum: 19954265\r\n#2 ID: 552 RandomIndexedNum: 19942897\r\n#3 ID: 161 RandomIndexedNum: 19898997\r\n#4 ID: 874 RandomIndexedNum: 19872310\r\n#5 ID: 484 RandomIndexedNum: 19859544\r\n#6 ID: 453 RandomIndexedNum: 19777522\r\n#7 ID: 649 RandomIndexedNum: 19776457\r\n#8 ID: 851 RandomIndexedNum: 19769359\r\n#9 ID: 488 RandomIndexedNum: 19741107\r\n#10 ID: 727 RandomIndexedNum: 19711418\r\n(not displaying the rest...)Took 0.0008430481 to rank and get 1000 indexed records.\r\n (time taken: `w0.02984619 seconds``)<\/pre>\n
To break it down (note: this database has 50,000 records):<\/p>\n
Update a record: 5 ms<\/p>\n
Seek and read from a random record: 0.18 ms<\/p>\n
Sort 50k unindexed ints and return the top 1000:\u00a0 27 ms<\/p>\n
Sort 50k indexed ints and return the top 1000:\u00a0 0.84 ms<\/p>\n
Final thoughts<\/h1>\n
Well, I probably should have created the table with\u00a0ENGINE=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.<\/p>\n
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?\u00a0 I don’t know, but it works fine now so whatever.<\/p>\n","protected":false},"excerpt":{"rendered":"
So you thought my post on networking a few days ago was boring?\u00a0 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 […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3,6,21],"tags":[],"class_list":["post-2132","post","type-post","status-publish","format-standard","hentry","category-development","category-tech-tips","category-unity"],"_links":{"self":[{"href":"https:\/\/www.codedojo.com\/index.php?rest_route=\/wp\/v2\/posts\/2132","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.codedojo.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.codedojo.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.codedojo.com\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.codedojo.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=2132"}],"version-history":[{"count":7,"href":"https:\/\/www.codedojo.com\/index.php?rest_route=\/wp\/v2\/posts\/2132\/revisions"}],"predecessor-version":[{"id":2141,"href":"https:\/\/www.codedojo.com\/index.php?rest_route=\/wp\/v2\/posts\/2132\/revisions\/2141"}],"wp:attachment":[{"href":"https:\/\/www.codedojo.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2132"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.codedojo.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2132"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.codedojo.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2132"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}