i know here is many articles about it, our problem is specific, i think. Problem is that often mysql return this error:
answer on this is very often
The issue you are running into is that you are starting up a second MySqlCommand while still reading back data with the DataReader. The MySQL connector only allows one concurrent query. You need to read the data into some structure, then close the reader, then process the data. Unfortunately you can't process the data as it is read if your processing involves further SQL queries.
but our sw open datareader, closedatareader, is waiting until first is closed and next open new and etc...
So every select has own datareader and when select is end, datareader is forget.
Have somebody any idea for this problem?
Next problem is with slow select, somethimes select takes 0 ms, somethimes the same select (table have 0-5 rows) takes
11.12.2013 14:53:18: Spustam loc. query: SELECT * FROM clovek WHERE id_doch='00080005' LIMIT 1
11.12.2013 14:53:18: Koncim loc. query: SELECT * FROM clovek WHERE id_doch='00080005' LIMIT 1
11.12.2013 14:53:18: Zobrazujem form overenia 11.12.2013 14:53:18: Spustam query: SELECT * FROM spravy WHERE id_doch='00080005' AND precitane='' LIMIT 1
11.12.2013 14:53:18: Chyba MySQL: There is already an open DataReader associated with this Connection which must be closed first.. Query: SELECT * FROM spravy WHERE id_doch='00080005' AND precitane='' LIMIT 1
11.12.2013 14:53:18: Zatvaram MySQL. Query: SELECT * FROM spravy WHERE id_doch='00080005' AND precitane='' LIMIT 1
11.12.2013 14:53:19: Spustam query: UPDATE vyr_operacie SET koniec='20131211145307' WHERE id_doch='00080005' AND koniec='' ORDER BY zaciatok LIMIT 1
11.12.2013 14:53:19: Koncim query: UPDATE vyr_operacie SET koniec='20131211145307' WHERE id_doch='00080005' AND koniec='' ORDER BY zaciatok LIMIT 1. Trvanie: 0 ms.
11.12.2013 14:53:19: Spustam query: SELECT * FROM history WHERE datum='20131211145311' AND id_doch='00080005' AND pr='0' AND typ='0' AND fy='5' AND vlozil='CZ' AND manual='0' LIMIT 1
11.12.2013 14:53:20: Koncim query: SELECT * FROM history WHERE datum='20131211145311' AND id_doch='00080005' AND pr='0' AND typ='0' AND fy='5' AND vlozil='CZ' AND manual='0' LIMIT 1. Trvanie: 0 ms.
11.12.2013 14:53:20: Spustam query: SELECT NOW() FROM licencia
11.12.2013 14:53:20: Chyba MySQL: There is already an open DataReader associated with this Connection which must be closed first.. Query: SELECT NOW() FROM licencia
11.12.2013 14:53:20: Zatvaram MySQL. Query: SELECT NOW() FROM licencia
second problem with long select
11.12.2013 14:54:13: Zobrazujem form overenia 11.12.2013 14:54:13: Spustam query: SELECT * FROM spravy WHERE id_doch='00080005' AND precitane='' LIMIT 1
11.12.2013 14:54:13: Koncim query: SELECT * FROM history WHERE datum='20131211145410' AND id_doch='00080005' AND pr='0' AND typ='0' AND fy='5' AND vlozil='CZ' AND manual='0' LIMIT 1. Trvanie: 0 ms.
11.12.2013 14:54:13: Chyba MySQL: There is already an open DataReader associated with this Connection which must be closed first.. Query: SELECT * FROM spravy WHERE id_doch='00080005' AND precitane='' LIMIT 1
11.12.2013 14:54:13: Zatvaram MySQL. Query: SELECT * FROM spravy WHERE id_doch='00080005' AND precitane='' LIMIT 1
11.12.2013 14:54:13: Spustam query: SELECT * FROM spravy WHERE id_doch='00080005' AND precitane='' LIMIT 1
11.12.2013 14:54:13: Uploadnute zaznamy: 1 11.12.2013 14:54:27: Koncim query: SELECT * FROM spravy WHERE id_doch='00080005' AND precitane='' LIMIT 1. Trvanie: 14000 ms.
11.12.2013 14:54:27: Spustam query: SELECT NOW() FROM licencia 11.12.2013 14:54:27: Koncim query: SELECT NOW() FROM licencia. Trvanie: 0 ms.
public static DataTable spustiQuery(string query)
{
if (nastavenia.LOG) CustomExHa.AddLog("Spustam query: " + query);
DateTime zaciatok = DateTime.Now;
while (bezi_query)
{
Thread.Sleep(50);
pocet_query++;
if (pocet_query > 500)
{
bezi_query = false;
pocet_query = 0;
}
}
bezi_query = true;
DateTime start = DateTime.Now;
DataTable tmp = new DataTable();
DataTable tmpempty = new DataTable();
tmpempty.Columns.Add("test", typeof(int));
string tmpstr = "";
DataSet ds = new DataSet();
MySqlDataAdapter adapter = new MySqlDataAdapter();
//MySqlDataReader reader = new MySqlDataReader();
if (MYSuperConn == null)
{
MYSuperConn = new MySqlConnection(connStr + ";Connection Timeout=30; default command timeout=6000");
}
if (MYSuperConn.State != ConnectionState.Open)
{
try
{
MYSuperConn.Open();
StavDB = true;
}
catch (Exception ex)
{
CustomExHa.AddLog("Chyba MySQL: " + ex.Message + ". Query: " + query);
StavDB = false;
bezi_query = false;
}
}
else
{
StavDB = true;
}
//MySqlConnection mc = MYSuperConn.Clone();
adapter.SelectCommand = new MySqlCommand(query, MYSuperConn);
//reader = new MySqlCommand(query, MYSuperConn).ExecuteReader();
try
{
//reader.fil
adapter.Fill(ds);
StavDB = true;
}
catch (Exception ep)
{
try
{
//MYSuperConn.Close();
bezi_query = false;
}
catch
{
bezi_query = false;
}
tmpstr = ep.Message;
bezi_query = false;
CustomExHa.AddLog("Chyba MySQL: " + ep.Message + ". Query: " + query);
if (ep.Message.IndexOf("already an open DataReader") > 0)
{
CustomExHa.AddLog("Zatvaram MySQL. Query: " + query);
MYSuperConn.Close();
return spustiQuery(query);
}
}
try
{
if (ds.Tables.Count > 0)
{
tmp = ds.Tables[0];
}
else
{
tmp = null;
}
}
catch (Exception ep)
{
tmpstr = ep.Message;
bezi_query = false;
CustomExHa.AddLog("Chyba MySQL: " + ep.Message + ". Query: " + query);
}
bezi_query = false;
DateTime koniec = DateTime.Now;
TimeSpan trvanie = koniec - zaciatok;
if ((tmp == null) && (query.IndexOf("SELECT ") > -1)) CustomExHa.AddLog("Chyba MySQL: Null Result. Query: " + query);
if (nastavenia.LOG) CustomExHa.AddLog("Koncim query: " + query + ". Trvanie: " + trvanie.TotalMilliseconds.ToString() + " ms.");
return tmp;
}