CODEWITHSUNDEEP
c#mysqlunity-game-enginecharacter-encoding
user4851649
user4851649

Reputation:

Query MySQL error

I can open a connection to MySQL 5.7 fine. When I execute a query, I get an error.

My code:

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 DatabaseHandler : MonoBehaviour {

    public string host, database, user, password, charset, soo;
    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);
        string connectionString = "Server="+host+";Database="+database+";User ID="+user+";Password="+password+";CharSet="+charset+";port=3306"+";Pooling=";
        if (pooling){
            connectionString += "true;";
        } else {
            connectionString += "false;";
        }

        try {
            con = new MySqlConnection(connectionString);
            con.Open();
            Debug.Log ("Mysql State: " + con.State);
            String cmdText = "SELECT * FROM unicorn";
            MySqlCommand cmd = new MySqlCommand(cmdText, con);
            soo = (String) cmd.ExecuteScalar(); //  THIS is line 54 in the error
            Debug.Log (soo);
        } catch (MySqlException ex) 
        {
            Debug.Log("hits ex");
            Console.WriteLine("Error: {0}",  ex.ToString());

        } finally 
        {
            if (rdr != null) 
            {
                rdr.Close();
            }

            if (con != null) 
            {
                con.Close();
            }   
        }
    }

    void OnApplicationQuit() {
        if(con != null) {
            if(con.State.ToString() != "Closed") {
                con.Close();
                Debug.Log ("MySql Connection closed");
            }
            con.Dispose();
        }
    }

    public string GetConnectionState(){
        return con.State.ToString ();
    }
}

Error:

KeyNotFoundException: The given key was not present in the dictionary. System.Collections.Generic.Dictionary`2[System.String,MySql.Data.MySqlClient.CharacterSet].get_Item (System.String key) (at /Users/builduser/buildslave/mono-runtime-and-classlibs/build/mcs/class/corlib/System.Collections.Generic/Dictionary.cs:150) MySql.Data.MySqlClient.CharSetMap.GetCharacterSet (DBVersion version, System.String CharSetName) MySql.Data.MySqlClient.MySqlField.SetFieldEncoding () MySql.Data.MySqlClient.MySqlField.set_CharacterSetIndex (Int32 value) MySql.Data.MySqlClient.MySqlField.SetTypeAndFlags (MySqlDbType type, ColumnFlags flags) MySql.Data.MySqlClient.NativeDriver.GetColumnData (MySql.Data.MySqlClient.MySqlField field) MySql.Data.MySqlClient.NativeDriver.GetColumnsData (MySql.Data.MySqlClient.MySqlField[] columns) MySql.Data.MySqlClient.Driver.GetColumns (Int32 count) MySql.Data.MySqlClient.ResultSet.LoadColumns (Int32 numCols) MySql.Data.MySqlClient.ResultSet..ctor (MySql.Data.MySqlClient.Driver d, Int32 statementId, Int32 numCols) MySql.Data.MySqlClient.Driver.NextResult (Int32 statementId) MySql.Data.MySqlClient.MySqlDataReader.NextResult () MySql.Data.MySqlClient.MySqlCommand.ExecuteReader (CommandBehavior behavior) MySql.Data.MySqlClient.MySqlCommand.ExecuteReader () MySql.Data.MySqlClient.MySqlCommand.ExecuteScalar () (wrapper remoting-invoke-with-check) MySql.Data.MySqlClient.MySqlCommand:ExecuteScalar () DatabaseHandler.Awake () (at Assets/Scripts/DatabaseHandler.cs:54)

Additional info: I had a similar error when making the initial connection to MySQL 5.7 server. It was fixed by including the charset in the connectionString e.g.

Database=weather;Server=127.0.0.1;Uid=root;Password=123456;pooling=false;CharSet=utf8;port=3306

I'm not sure if that bit of information would help. This is my post from that issue (solved) if you are curious. Unity3D connection to MySQL error

Thank you!

Upvotes: 1

Views: 795

Answers (2)

Autodidact
Autodidact

Reputation: 768

I don't know the fix for this but it started happening to me when our db went from antelope to barracuda. BTW varchars still return, only when you try to return numeric datatypes do you get this error. At least for me! Don't know how to fix it yet but hoping that info helps someone.

Upvotes: 0

user4851649
user4851649

Reputation:

I noticed that many people were not having the same issues as I was. So I ended up using a different host (rackspace) for my MySQL database to avoid the above issue. By going to a different host, issue avoided. The issue was probably with how the database was setup.

Upvotes: 0

Related Questions