Description:
I am working on a 'small' C# server application that uses SQL as it's backend.
While testing the code's compatibility with Mysql's connector (MySql.Data.dll version 5.0.8.1), i ran into some issues.
1) On Win32, Visual Studio 2007, it compiles and runs fine, no glitches
2) On Mono (1.2.4.0) it compiles, but when running it gives the following error:
Unhandled Exception: MySql.Data.MySqlClient.MySqlException: Unable to connect to any of the specified MySQL hosts. ---> System.Exception: Exception of type System.Exception was thrown.
at MySql.Data.MySqlClient.NativeDriver.Open () [0x00000] --- End of inner exception stack trace ---
at MySql.Data.MySqlClient.NativeDriver.Open () [0x00000]
at MySql.Data.MySqlClient.Driver.Create (MySql.Data.MySqlClient.MySqlConnectionStringBuilder settings) [0x00000]
at MySql.Data.MySqlClient.MySqlPool.CreateNewPooledConnection () [0x00000]
at MySql.Data.MySqlClient.MySqlPool.GetPooledConnection () [0x00000]
at MySql.Data.MySqlClient.MySqlPool.GetConnection () [0x00000]
what strikes me as odd is that this code works perfectly normal on Win32, yet on Linux/Mono it fails constantly.
I traced the exception back to this piece of code in NativeDriver.cs
try
{
#if !PocketPC
if (Settings.ConnectionProtocol == MySqlConnectionProtocol.SharedMemory)
{
SharedMemoryStream str = new SharedMemoryStream(Settings.SharedMemoryName);
str.Open(Settings.ConnectionTimeout);
baseStream = str;
}
else
{
#endif
string pipeName = Settings.PipeName;
if (Settings.ConnectionProtocol != MySqlConnectionProtocol.NamedPipe)
pipeName = null;
StreamCreator sc = new StreamCreator(Settings.Server, Settings.Port, pipeName);
baseStream = sc.GetStream(Settings.ConnectionTimeout);
#if !PocketPC
}
#endif
if (baseStream == null)
throw new Exception();
}
catch (Exception ex)
{
throw new MySqlException(
Resources.UnableToConnectToHost,
(int)MySqlErrorCode.UnableToConnectToHost,
ex);
}
after some hack and slash I noticed that Settings.ConnectionProtocol == MySqlConnectionProtocol.Sockets.
Setting Connection Timeout and/or Port in the connectionstring as others suggested doesn't fix it, and I haven't managed to dig any further than this.
How to repeat:
The code:
public class BaseMySqlFactory
{
private string connectionString;
private bool logStatements;
public bool LogStatements { get { return logStatements; } set { logStatements = value; } }
public BaseMySqlFactory(string connectionString)
{
this.connectionString = connectionString;
logStatements = false;
}
#region Base SQL Methods
public MySqlConnection GetConnection()
{
MySqlConnection conn = new MySqlConnection(connectionString);
conn.Open();
Lib.WriteLine("SQL", "New Connection");
return conn;
}
public DataTable ExecuteDataTable(string statement)
{
LogSQL(statement);
using (MySqlConnection conn = GetConnection())
{
/// Create the command
MySqlCommand command = new MySqlCommand();
command.Connection = conn;
command.CommandText = statement;
// Create the data adapter
MySqlDataAdapter adapter = new MySqlDataAdapter();
adapter.SelectCommand = command;
try
{
// Fill a new data table
DataTable table = new DataTable();
adapter.Fill(table);
return table;
}
catch (Exception ex)
{
throw;
}
}
}
public DataSet ExecuteDataSet(string statement)
{
LogSQL(statement);
using (MySqlConnection conn = GetConnection())
{
/// Create the command
MySqlCommand command = new MySqlCommand();
command.Connection = conn;
command.CommandText = statement;
// Create the data adapter
MySqlDataAdapter adapter = new MySqlDataAdapter();
adapter.SelectCommand = command;
// Fill a new data table
DataSet dataSet = new DataSet();
adapter.Fill(dataSet);
return dataSet;
}
}
public int ExecuteNonQuery(string statement)
{
LogSQL(statement);
using (MySqlConnection conn = GetConnection())
{
try
{
MySqlCommand command = new MySqlCommand();
command.Connection = conn;
command.CommandText = statement;
return command.ExecuteNonQuery();
}
catch (MySqlException ex)
{
Lib.WriteLine("SQL", "EXCEPTION in BaseODBC (Running non query): " + ex.Message + ex.StackTrace);
return 0;
}
}
}
public string CreateInsertUpdateSQL(string type, NameValueCollection items)
{
List<string> fields = new List<string>();
List<string> values = new List<string>();
string ret = "";
if (type.ToLower() == "insert")
{
foreach (string key in items)
{
string val = items[key];
val = DataCleaner.SanitizeReturn(val);
int testint = 0;
bool testbool = false;
fields.Add(key);
if(string.IsNullOrEmpty(val))
{
values.Add("NULL");
}
else if(int.TryParse(val, out testint))
{
values.Add(val);
}
else if(bool.TryParse(val,out testbool))
{
values.Add(val);
}
else
{
values.Add("'"+val+"'");
}
}
ret = " (" + string.Join(", ", fields.ToArray()) + ") VALUES (" + string.Join(", ", fields.ToArray()) + ")";
}
else if (type.ToLower() == "update")
{
foreach (string key in items)
{
string val = items[key];
val = DataCleaner.SanitizeReturn(val);
int testint = 0;
bool testbool = false;
fields.Add(key);
if (string.IsNullOrEmpty(val))
{
values.Add(key + " = NULL");
}
else if (int.TryParse(val, out testint))
{
values.Add(key + " = " + val);
}
else if (bool.TryParse(val, out testbool))
{
values.Add(key + " = " + val);
}
else
{
values.Add(key + " = " + "'" + val + "'");
}
}
ret = string.Join(", ", values.ToArray());
}
return ret;
}
public object ExecuteScalar(string statement)
{
LogSQL(statement);
using (MySqlConnection conn = GetConnection())
{
MySqlCommand command = new MySqlCommand();
command.Connection = conn;
command.CommandText = statement;
return command.ExecuteScalar();
}
}
public int ExecuteRowCount(string statement)
{
object result = ExecuteScalar(statement);
if (result == null)
{
return 0;
}
else
{
return Int32.Parse(result.ToString());
}
}
public bool ExecuteBooleanFromRowCount(string statement)
{
int rowCount = ExecuteRowCount(statement);
if (rowCount == 0)
{
return false;
}
else
{
return true;
}
}
#endregion
private void LogSQL(string sql)
{
if (logStatements)
{
Lib.WriteLine("SQL", sql);
}
}
#endregion
}
Description: I am working on a 'small' C# server application that uses SQL as it's backend. While testing the code's compatibility with Mysql's connector (MySql.Data.dll version 5.0.8.1), i ran into some issues. 1) On Win32, Visual Studio 2007, it compiles and runs fine, no glitches 2) On Mono (1.2.4.0) it compiles, but when running it gives the following error: Unhandled Exception: MySql.Data.MySqlClient.MySqlException: Unable to connect to any of the specified MySQL hosts. ---> System.Exception: Exception of type System.Exception was thrown. at MySql.Data.MySqlClient.NativeDriver.Open () [0x00000] --- End of inner exception stack trace --- at MySql.Data.MySqlClient.NativeDriver.Open () [0x00000] at MySql.Data.MySqlClient.Driver.Create (MySql.Data.MySqlClient.MySqlConnectionStringBuilder settings) [0x00000] at MySql.Data.MySqlClient.MySqlPool.CreateNewPooledConnection () [0x00000] at MySql.Data.MySqlClient.MySqlPool.GetPooledConnection () [0x00000] at MySql.Data.MySqlClient.MySqlPool.GetConnection () [0x00000] what strikes me as odd is that this code works perfectly normal on Win32, yet on Linux/Mono it fails constantly. I traced the exception back to this piece of code in NativeDriver.cs try { #if !PocketPC if (Settings.ConnectionProtocol == MySqlConnectionProtocol.SharedMemory) { SharedMemoryStream str = new SharedMemoryStream(Settings.SharedMemoryName); str.Open(Settings.ConnectionTimeout); baseStream = str; } else { #endif string pipeName = Settings.PipeName; if (Settings.ConnectionProtocol != MySqlConnectionProtocol.NamedPipe) pipeName = null; StreamCreator sc = new StreamCreator(Settings.Server, Settings.Port, pipeName); baseStream = sc.GetStream(Settings.ConnectionTimeout); #if !PocketPC } #endif if (baseStream == null) throw new Exception(); } catch (Exception ex) { throw new MySqlException( Resources.UnableToConnectToHost, (int)MySqlErrorCode.UnableToConnectToHost, ex); } after some hack and slash I noticed that Settings.ConnectionProtocol == MySqlConnectionProtocol.Sockets. Setting Connection Timeout and/or Port in the connectionstring as others suggested doesn't fix it, and I haven't managed to dig any further than this. How to repeat: The code: public class BaseMySqlFactory { private string connectionString; private bool logStatements; public bool LogStatements { get { return logStatements; } set { logStatements = value; } } public BaseMySqlFactory(string connectionString) { this.connectionString = connectionString; logStatements = false; } #region Base SQL Methods public MySqlConnection GetConnection() { MySqlConnection conn = new MySqlConnection(connectionString); conn.Open(); Lib.WriteLine("SQL", "New Connection"); return conn; } public DataTable ExecuteDataTable(string statement) { LogSQL(statement); using (MySqlConnection conn = GetConnection()) { /// Create the command MySqlCommand command = new MySqlCommand(); command.Connection = conn; command.CommandText = statement; // Create the data adapter MySqlDataAdapter adapter = new MySqlDataAdapter(); adapter.SelectCommand = command; try { // Fill a new data table DataTable table = new DataTable(); adapter.Fill(table); return table; } catch (Exception ex) { throw; } } } public DataSet ExecuteDataSet(string statement) { LogSQL(statement); using (MySqlConnection conn = GetConnection()) { /// Create the command MySqlCommand command = new MySqlCommand(); command.Connection = conn; command.CommandText = statement; // Create the data adapter MySqlDataAdapter adapter = new MySqlDataAdapter(); adapter.SelectCommand = command; // Fill a new data table DataSet dataSet = new DataSet(); adapter.Fill(dataSet); return dataSet; } } public int ExecuteNonQuery(string statement) { LogSQL(statement); using (MySqlConnection conn = GetConnection()) { try { MySqlCommand command = new MySqlCommand(); command.Connection = conn; command.CommandText = statement; return command.ExecuteNonQuery(); } catch (MySqlException ex) { Lib.WriteLine("SQL", "EXCEPTION in BaseODBC (Running non query): " + ex.Message + ex.StackTrace); return 0; } } } public string CreateInsertUpdateSQL(string type, NameValueCollection items) { List<string> fields = new List<string>(); List<string> values = new List<string>(); string ret = ""; if (type.ToLower() == "insert") { foreach (string key in items) { string val = items[key]; val = DataCleaner.SanitizeReturn(val); int testint = 0; bool testbool = false; fields.Add(key); if(string.IsNullOrEmpty(val)) { values.Add("NULL"); } else if(int.TryParse(val, out testint)) { values.Add(val); } else if(bool.TryParse(val,out testbool)) { values.Add(val); } else { values.Add("'"+val+"'"); } } ret = " (" + string.Join(", ", fields.ToArray()) + ") VALUES (" + string.Join(", ", fields.ToArray()) + ")"; } else if (type.ToLower() == "update") { foreach (string key in items) { string val = items[key]; val = DataCleaner.SanitizeReturn(val); int testint = 0; bool testbool = false; fields.Add(key); if (string.IsNullOrEmpty(val)) { values.Add(key + " = NULL"); } else if (int.TryParse(val, out testint)) { values.Add(key + " = " + val); } else if (bool.TryParse(val, out testbool)) { values.Add(key + " = " + val); } else { values.Add(key + " = " + "'" + val + "'"); } } ret = string.Join(", ", values.ToArray()); } return ret; } public object ExecuteScalar(string statement) { LogSQL(statement); using (MySqlConnection conn = GetConnection()) { MySqlCommand command = new MySqlCommand(); command.Connection = conn; command.CommandText = statement; return command.ExecuteScalar(); } } public int ExecuteRowCount(string statement) { object result = ExecuteScalar(statement); if (result == null) { return 0; } else { return Int32.Parse(result.ToString()); } } public bool ExecuteBooleanFromRowCount(string statement) { int rowCount = ExecuteRowCount(statement); if (rowCount == 0) { return false; } else { return true; } } #endregion private void LogSQL(string sql) { if (logStatements) { Lib.WriteLine("SQL", sql); } } #endregion }