[Release] Simple MySql Pool handler

[Spirited]

Originally Posted by marlyandedsel

my MysqlComman


Mysql REader


and part of the DataHolder



can you tell me if this is good or not?

Sleeping connections are bad. You'll have them with that code because you're using MySql 6.3.7 (or whatever)'s pool.

[marlyandedsel]

Fang what Version of MySql should I used so that Sleeping Connection wont happen

[Spirited]

Originally Posted by marlyandedsel

Fang what Version of MySql should I used so that Sleeping Connection wont happen

It happens in every version.

[marlyandedsel]

using System.Data.Odbc;
using System.Data;

/// <summary>
/// This function checks for any sleeping connections beyond a reasonable time and kills them.
/// Since .NET appears to have a bug with how pooling MySQL connections are handled and leaves
/// too many sleeping connections without closing them, we will kill them here.
/// </summary>
/// iMinSecondsToExpire - all connections sleeping more than this amount in seconds will be killed.
/// <returns>integer - number of connections killed</returns>
static public int KillSleepingConnections(int iMinSecondsToExpire)
{
string strSQL = "show processlist";
System.Collections.ArrayList m_ProcessesToKill = new ArrayList();

OdbcConnection myConn = new OdbcConnection(Global.strDBServer);
OdbcCommand myCmd = new OdbcCommand(strSQL, myConn);
OdbcDataReader MyReader = null;

try
{
myConn.Open();

// Get a list of processes to kill.
MyReader = myCmd.ExecuteReader();
while (MyReader.Read())
{
// Find all processes sleeping with a timeout value higher than our threshold.
int iPID = Convert.ToInt32(MyReader["Id"].ToString());
string strState = MyReader["Command"].ToString();
int iTime = Convert.ToInt32(MyReader["Time"].ToString());

if (strState == "Sleep" && iTime >= iMinSecondsToExpire && iPID > 0)
{
// This connection is sitting around doing nothing. Kill it.
m_ProcessesToKill.Add(iPID);
}
}

MyReader.Close();

foreach (int aPID in m_ProcessesToKill)
{
strSQL = "kill " + aPID;
myCmd.CommandText = strSQL;
myCmd.ExecuteNonQuery();
}
}
catch (Exception excep)
{
}
finally
{
if (MyReader != null && !MyReader.IsClosed)
{
MyReader.Close();
}

if (myConn != null && myConn.State == ConnectionState.Open)
{
myConn.Close();
}
}

return m_ProcessesToKill.Count;
}

// Call it with the following code to kill sleeping connections >= 100 seconds.
KillSleepingConnections(100);

I search and I got that one but I try to implement it in, I dunno when to call the KillSleepingConnections(100);

[Spirited]

Project Kibou has a maintenance thread that cleans up things on the server every 30 seconds. I have the MySql Kill Command in there.

[koko20]

i have error Error 1 'Conquer_Online_Server.Database.DataHolder.Connect ionString' is inaccessible due to its protection level C:\Users\Administrator\Desktop\fixed\Source\cleanm ysql.cs 15 98 Conquer_Online_Server_x86
in public static void Kill()
{
string command = "SHOW processlist";
List<ulong> processes = new List<ulong>();

MySqlConnection conn = new MySqlConnection(Conquer_Online_Server.Database.Dat aHolder.ConnectionString);
MySqlCommand cmd = new MySqlCommand(command, conn);
MySqlDataReader reader = null;

[Spirited]

Originally Posted by koko20

i have error Error 1 'Conquer_Online_Server.Database.DataHolder.Connect ionString' is inaccessible due to its protection level C:\Users\Administrator\Desktop\fixed\Source\cleanm ysql.cs 15 98 Conquer_Online_Server_x86
in public static void Kill()
{
string command = "SHOW processlist";
List<ulong> processes = new List<ulong>();

MySqlConnection conn = new MySqlConnection(Conquer_Online_Server.Database.Dat aHolder.ConnectionString);
MySqlCommand cmd = new MySqlCommand(command, conn);
MySqlDataReader reader = null;

There's no way that I'm teaching you how to use the code that you're copying from me. If you cannot figure it out then just don't use it.

[koko20]

Thank you my friend Fang Do you think these codes eliminate the problem

[marlyandedsel]

Fang should I call this KillConnections.Kill(); every reading or opening the database?

[Spirited]

Originally Posted by koko20

Thank you my friend Fang Do you think these codes eliminate the problem

They do. I ran it with a block test and the connections were killed successfully (in the exception to the one connection that it was using to kill the connections- which is killed on the next execution of the void).

Originally Posted by marlyandedsel

Fang should I call this KillConnections.Kill(); every reading or opening the database?

No. You should make a new thread and call it "ServerManagement" (if you don't already have a server management thread that manages server cleanups and runs about twice a minute). Definitely don't call this to kill connections every time mysql is executed.

[12tails]

Call it to kill after every time you query something.... and see what happens ^^
Fang already gave you a good start... it's up to you how to use it...
i've swiched to flatfile database oO... works wonderfully! ;D

[marlyandedsel]

is it in the Thread.cs?

[BaussHacker]

Originally Posted by koko20

i have error Error 1 'Conquer_Online_Server.Database.DataHolder.Connect ionString' is inaccessible due to its protection level C:\Users\Administrator\Desktop\fixed\Source\cleanm ysql.cs 15 98 Conquer_Online_Server_x86
in public static void Kill()
{
string command = "SHOW processlist";
List<ulong> processes = new List<ulong>();

MySqlConnection conn = new MySqlConnection(Conquer_Online_Server.Database.Dat aHolder.ConnectionString);
MySqlCommand cmd = new MySqlCommand(command, conn);
MySqlDataReader reader = null;

It's not public.

[marlyandedsel]

or can I call it the KillConnections.Kill(); every after reading and closing database aside from doing servermanagement?

[Spirited]

Originally Posted by marlyandedsel

or can I call it the KillConnections.Kill(); every after reading and closing database aside from doing servermanagement?

It's much much more efficient to do it my way by making a new thread to handle it and other server tasks.