Unity and MySQL Connector?
Im on Unity 2019.4.11f1 I have MySQL installed, and have been coding in C# outside unity without any issues. I am using the Connector/NET from mysql, I created a DIR inside my Unity project called Plugins, and copied all the DLLs from the pack to my project. When I goback to Unity Editor, it shows a ton of errors about not loading: Assembly 'Assets/Plugins/MySql.Data.dll' will not be loaded due to errors: Reference has errors 'Google.Protobuf'. and continuing down till I find it says its missing System.Memory, but I downloaded that as well from all the tutorials and added it to the Plugins as well. My Project settings are set to Scripting Backend Mono, API Comp. Level .NET 4.X I managed to get something from https://github.com/Hanslen/Unity-with-MYSQL to seem to Work with Unity, as it stoped making errors, untill I try to accualy use it. I can connect to the database, but everytime I run a Query/Command it errors out with a invalid Key. After searching the web for the last few days all I can find is that the version from GitHub is to old to use the mysql coalations, so im back to trying to get the ones from MySQLs site to work. I am trying to setup a Server end in this Unity Project. In Visual Studio itself my projects work fine, no errors. When I code in DevCPP, no errors. The connectors seem to work everywhere but Unity. Is there a specific version I need for Unity to work? For tutorials on Unity on youtube everything seems to want me to use www requests and run a webserver to run the querys but this seems very odd since there are connectors there, and seems like it would be a waste of resources to need to run a server on unity and a webserver to talk to a database server, is this the only way unity now supports? I used unity years ago and never had any issues connecting to MySQL, so I am assuming something simple is hiding from me. Can someone please guide me to a Current Tutorial or guide as to HOW to implement MySQL in Unity 2019?
To be clear, I have Visual Studio 2019 working fine with $$anonymous$$ySQL, but only inside a Visual Studio "Project" When I open a script inside unity, it seems to only use what unity has not what visual studio has. In Visual Studio itself, I am connected to the database, says all is good to go, but if for example in a Unity script I do a using $$anonymous$$ySql.Data.$$anonymous$$ySqlClient; Unity complains it dosent exist/not usable. But when I create a new project, or use existing projects inside Visual Studio, everything works. Just when I try to use Unity scripts without some kind of Visual Studio Project seems to be a nightmare. So I guess my issue is that Unity itself dosent let me use the Visual Studio stuff it has, but I dont know how to make a Visual Studio "Project" work with unity since unity just makes seperate script files, dosent seem to use studio projects. I have $$anonymous$$ySQL Connector 8.0.22 for .NET and the $$anonymous$$ySQL for Visual Studio 2019 installed and works fine, but only in actual Visual Studio 'Projects' nothing in Unity.
From what I can see, the $$anonymous$$ySQL Connector is based on .NET 4.6.2, Im assu$$anonymous$$g, as it has a System.Buffers extension that its properties says is version 4.6.28619.1 $$anonymous$$icrosoft .Net Framework. Digging around the Editors files, I found a dir, C:\Program Files\Unity\Hub\Editor\2019.4.13f1\Editor\Data\$$anonymous$$onoBleedingEdge\lib\mono that looks like it has a bunch of versions of the .Net Framework, including the 4.6.2-api. Just seems like Unity isnt using it, and I have no idea what version of .NET unity is using for everything. I have the 4.X set for the frame work in project settings but dont know how to tell the project im working on to use a specific version, is this possible either threw the editor or via the scripts? I know it has to be possible to do somehow as I see there are assets for sale on the store that says its doing it, but I dont see a point in needing to pay someone else to do what I should be able to do myself.
Answer by peacemakers1980 · Nov 03, 2020 at 05:49 PM
Seems Unity just wont support the new versions of MySQL itself. But I did manage to get it up and running. The MySQL 8 Server/Connector/NET parts will not work in Unity 2019. Goto MySQl and download a OLD version, I used version 5.7.31 for the Server, I used the newest installer for the server (https://dev.mysql.com/downloads/windows/installer/8.0.html) When installing I told it to custom install, and selected the older 5.7.31 version of the server itself, set it up like normal using defaults. The connector/net version 6.9.5 (https://downloads.mysql.com/archives/c-net/ select version 6.9.5 and instead of Windows get the Mono .NET under the Operating System) and download, when downloaded extract the zip. Inside the folder you extract to, get the MySQL.Data.dll file and copy it to your Assets of your project. I just created a folder for Plugins in my assets, and copied the DLL there. Load your project in Unity, I updated to version 2019.4.13f1, and it seems to accept the MySQL.Data file without error. As for accessing the database I used the MySQL terminal, created a database, user, tables, and populated some of the stuff to make sure the server was running ok and to give me a way to send commands to the MySQL Server and get results to test the code. I have a Empty Game Object in scene, created a new c# script, and attached it to the empty game object. Here is my source.
using UnityEngine;
using System;
using System.Data;
using System.Text;
using System.Collections;
using System.Collections.Generic;
using System.Security.Cryptography;
using MySql.Data;
using MySql.Data.MySqlClient;
public class MySQLData : MonoBehaviour
{
public string host, database, user, password;
public bool pooling = true;
private string connectionString;
private MySqlConnection con = null;
private MySqlCommand cmd = null;
private MySqlDataReader rdr = null;
void Awake()
{
DontDestroyOnLoad(this.gameObject);
connectionString = "Server=" + host + ";Database=" + database + ";User=" + user + ";Password=" + password + ";Pooling=";
if (pooling)
{
connectionString += "True";
}
else
{
connectionString += "False";
}
try
{
con = new MySqlConnection(connectionString);
con.Open();
Debug.Log("Mysql state: " + con.State);
string sql = "SELECT * FROM user_base";
cmd = new MySqlCommand(sql, con);
cmd = new MySqlCommand(sql, con);
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
Debug.Log("???");
Debug.Log(rdr[0]+" -- "+rdr[1]);
}
rdr.Close();
}
catch (Exception e)
{
Debug.Log(e);
}
}
void onApplicationQuit()
{
if (con != null)
{
if (con.State.ToString() != "Closed")
{
con.Close();
Debug.Log("Mysql connection closed");
}
con.Dispose();
}
}
}
Back in the editor if you select the empty game object you added the script to, you can assign the Database connection info (hostname, username, etc.) On starting the project you should get a happy message saying its connected to the database, it will then run a SELECT * command, you will need to change this part of course for your own tables and what you are doing, but using that code I was able to implement MySQL into Unity, connect and use the database without Key errors or the wonderfull lag to death to say it timed out trying to talk to anything. Its not the most efficient code but it works, and hopefully it will help others trying to use MySQL in Unity without needing a second webserver and php as a middle man to save on resources and increase speed.
This definitely works. I tried the ODBC Driver and this method, and both methods work.
For Unity 2020 I used the v6.9.11 of the MySQL .Net Connector Plugin and it worked. For Unity 2021 I used v6.9.11 and v8.0.28 of the MySQL .Net Connector Plugin and both worked.
I only tested the v4.x dll's in Unity, not the v2.x.
I made a repo MySQL Unity to help people who need a quick solution. My examples cover implementations for ODBC and the MySQL .Net Connector Plugins.
I would recommend using the MySQL Connector over the ODBC driver. If you can, try setting up a REST API to do your CRUD operations.
Answer by peacemakers1980 · Nov 04, 2020 at 11:13 AM
The KEY error I get:
System.Collections.Generic.KeyNotFoundException: The given key was not present in the dictionary.
at System.Collections.Generic.Dictionary`2[TKey,TValue].get_Item (TKey key) [0x0001e] in <fb001e01371b4adca20013e0ac763896>:0
at MySql.Data.MySqlClient.CharSetMap.GetCharacterSet (MySql.Data.Common.DBVersion version, System.String CharSetName) [0x00000] in <326e9aab93854e739606c3572c385a34>:0
at MySql.Data.MySqlClient.MySqlField.SetFieldEncoding () [0x0004b] in <326e9aab93854e739606c3572c385a34>:0
at MySql.Data.MySqlClient.MySqlField.set_CharacterSetIndex (System.Int32 value) [0x00007] in <326e9aab93854e739606c3572c385a34>:0
at MySql.Data.MySqlClient.NativeDriver.GetColumnData (MySql.Data.MySqlClient.MySqlField field) [0x000ad] in <326e9aab93854e739606c3572c385a34>:0
at MySql.Data.MySqlClient.NativeDriver.GetColumnsData (MySql.Data.MySqlClient.MySqlField[] columns) [0x00004] in <326e9aab93854e739606c3572c385a34>:0
at MySql.Data.MySqlClient.Driver.GetColumns (System.Int32 count) [0x0001c] in <326e9aab93854e739606c3572c385a34>:0
at MySql.Data.MySqlClient.ResultSet.LoadColumns (System.Int32 numCols) [0x00000] in <326e9aab93854e739606c3572c385a34>:0
at MySql.Data.MySqlClient.ResultSet..ctor (MySql.Data.MySqlClient.Driver d, System.Int32 statementId, System.Int32 numCols) [0x00029] in <326e9aab93854e739606c3572c385a34>:0
at MySql.Data.MySqlClient.Driver.NextResult (System.Int32 statementId) [0x00035] in <326e9aab93854e739606c3572c385a34>:0
at MySql.Data.MySqlClient.MySqlDataReader.NextResult () [0x00053] in <326e9aab93854e739606c3572c385a34>:0
at MySql.Data.MySqlClient.MySqlDataReader.Close () [0x00020] in <326e9aab93854e739606c3572c385a34>:0
at MySql.Data.MySqlClient.MySqlCommand.ResetReader () [0x00020] in <326e9aab93854e739606c3572c385a34>:0
at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader (System.Data.CommandBehavior behavior) [0x002d1] in <326e9aab93854e739606c3572c385a34>:0
at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader () [0x00000] in <326e9aab93854e739606c3572c385a34>:0
at (wrapper remoting-invoke-with-check) MySql.Data.MySqlClient.MySqlCommand.ExecuteReader()
at MySQLData.Awake () [0x000fe] in C:\UnityProjects\KoloCrypto_Server\Assets\Scripts\Database\MySQLData.cs:44
UnityEngine.Debug:Log(Object)
MySQLData:Awake() (at Assets/Scripts/Database/MySQLData.cs:55)
The Source of the script: using UnityEngine; using System; using System.Data; using System.Text;
using System.Collections; using System.Collections.Generic; using System.Security.Cryptography;
using MySql.Data; using MySql.Data.MySqlClient;
public class MySQLData : MonoBehaviour { public string host, database, user, password; public bool pooling = true;
private string connectionString;
private MySqlConnection con = null;
private MySqlCommand cmd = null;
private MySqlDataReader rdr = null;
private MD5 _md5Hash;
void Awake()
{
DontDestroyOnLoad(this.gameObject);
connectionString = "charset=utf8; Server=" + host + ";Database=" + database + ";User=" + user + ";Password=" + password + ";Pooling=";
if (pooling)
{
connectionString += "True";
}
else
{
connectionString += "False";
}
try
{
con = new MySqlConnection(connectionString);
con.Open();
Debug.Log("Mysql state: " + con.State);
string sql = "SELECT * FROM user_base";
cmd = new MySqlCommand(sql, con);
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
Debug.Log("???");
Debug.Log(rdr[0]+" -- "+rdr[1]);
}
rdr.Close();
}
catch (Exception e)
{
Debug.Log(e);
}
}
void onApplicationQuit()
{
if (con != null)
{
if (con.State.ToString() != "Closed")
{
con.Close();
Debug.Log("Mysql connection closed");
}
con.Dispose();
}
}
}
Answer by princemawan33 · Jun 04, 2021 at 07:49 AM
Hi, I have tried this it work in the editor but on the webgl build it did not work. Please tell me what to do to run this because i don't have much time. Regards.
Answer by simplezed · Jun 15, 2021 at 08:11 AM
A couple of years ago we thought about software development for our business. we were considering crm software development services, as well as ready-made solutions. but we settled on the first option. we do not regret it. the support of t specialists is priceless.
Your answer
Follow this Question
Related Questions
I need your help for SaveLoad method. 1 Answer
How to create a sprite from an image retrieved from mySQL database? 0 Answers
how to save value to mysql 1 Answer
Parse Mysql data to scroll list 0 Answers
Can't build my Project 1 Answer