| Bug #30232 | Too many connection error with C# application | ||
|---|---|---|---|
| Submitted: | 3 Aug 2007 16:39 | Modified: | 28 Oct 2007 14:37 |
| Reporter: | newbie Shai | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | Connector / NET | Severity: | S3 (Non-critical) |
| Version: | 5.0.27 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[3 Aug 2007 17:19]
MySQL Verification Team
We're sorry, but the bug system is not the appropriate forum for asking help on using MySQL products. Your problem is not the result of a bug. Support on using our products is available both free in our forums at http://forums.mysql.com/ and for a reasonable fee direct from our skilled support engineers at http://www.mysql.com/support/ Thank you for your interest in MySQL.
[4 Aug 2007 14:55]
newbie Shai
Dear Help,
I was reading through this article on this link http://www.primaryobjects.com/CMS/Article69.aspx which says that the connection pooling has a flaw. So how can I solve my problem please help me. Thanks.
[7 Aug 2007 12:16]
Tonci Grgin
Not enough information was provided for us to be able to handle this bug. Please re-read the instructions at http://bugs.mysql.com/how-to-report.php If you can provide more information, feel free to add it to this bug and change the status back to 'Open'. Thank you for your interest in MySQL. Explanation: Hi newbie and thanks for your report. We can not speculate on c/NET pooling before you meet even most common requirements. So please attach here: - NET FW version - my.ini (or my.cnf if on Linux) file used to start your server - Small but *complete* test case exhibiting this error every time it is run
[7 Aug 2007 16:52]
newbie Shai
Dear Tonci,
First of all thanks into looking my problem. I am using mysql .net connector 1.0.9. I have already attached my my.ini file too. Below is the snippet of the code. In function1 based on the variable quantity I run a for loop. That is where I get my error of too many connection. I have attached also my connect.cs which the file I used to keep my connection and information and from where I get my connection in my application. Hope you can see where is my mistake. Thanks.
//function 1
private void function1 ()
{
startSerial = Convert.ToDecimal(txtStartSerial.Text);
startSerialLength = txtStartSerial.TextLength;
for (int i = 0; i < quantity; i++)
{
decimal decimalSerial = startSerial + i;
string stringSerial = decimalSerial.ToString().PadLeft(startSerialLength, '0');
String mySelectQuery1 = "Select " +
"tblStock.stockID, " +
"tblStock.stockPrice " +
"From tblStock " +
"Where tblStock.stockStatus ='y' And " +
"tblStock.stockSIQ='" + stringSerial + "' And tblStock.productID=" + productCodeID;
MySqlDataReader myReader1 = null;
MySqlCommand myCommand1 = new MySqlCommand(mySelectQuery1);
myCommand1.Connection = GlobalClass.openConnection();
try
{
myReader1 = myCommand1.ExecuteReader();
if (myReader1.HasRows == true)
{
while (myReader1.Read())
{
availableCouponQuantity++;
String myUpdateQuery1 = "Update tblStock " +
"Set tblStock.stockStatus = 'b'" +
"Where tblStock.stockID=" + Convert.ToInt32(myReader1.GetValue(0).ToString());
MySqlCommand myCommand2 = new MySqlCommand(myUpdateQuery1);
myCommand2.Connection = GlobalClass2.openConnection();
try
{
myCommand2.ExecuteNonQuery();
//txtImei.Text = "";
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
MessageBox.Show(ex.Message);
}
myCommand2.Dispose();
GlobalClass2.closeConnection();
}
}
else
{
MessageBox.Show("Coupon " + stringSerial + " Not Available");
GlobalClass.closeConnection();
}
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
MessageBox.Show(ex.Message);
}
myReader1.Close();
myCommand1.Dispose();
GlobalClass.closeConnection();
GlobalClass.disposeConnection();
}
connect. cs
using System;
using System.Collections.Generic;
using System.Text;
using System.Windows.Forms;
using MySql.Data.MySqlClient;
namespace mSystem
{
static class GlobalClass
{
static MySqlConnection connectionLocal ;
public static MySqlConnection openConnection()
{
try
{
connectionLocal = new MySqlConnection("Address='localhost';Database='test1';User Name='root';Password='12345'");
connectionLocal.Open();
return connectionLocal;
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
MessageBox.Show(ex.Message + "MYSQL ERROR");
return null;
}
catch (System.Net.Sockets.SocketException ex)
{
MessageBox.Show(ex.Message+"SYSTEM ERROR");
return null;
}
}
public static void closeConnection()
{
connectionLocal.Close();
}
public static void disposeConnection()
{
connectionLocal.Dispose();
}
}
}
[24 Aug 2007 20:26]
Tonci Grgin
Newbie, sorry for the delay, it's been busy around here. I have few questions / requests: - Do you see error in <your_host_name>.err file? It is usually located in datadir. - Please attach DDL script suitable for import via mysql cl client and add a few records to it. You can also put it in your test case if possible. - Boil down your test case to bare essentials needed to reproduce the error and *attach* it here (do not copy/paste). With this test case I'm, in fact, doing code review.
[25 Aug 2007 2:21]
newbie Shai
mysql err file
Attachment: mysql.err (application/octet-stream, text), 27.53 KiB.
[25 Aug 2007 2:24]
newbie Shai
Dear Tonci,
Thanks for your reply I still appreciate it. Ok as your requirement I have attached the .err file. Actually what this file does it capture the errors is it? I dont see any error but maybe I dont know how to read it well maybe you can go through. Ok just to let you know this problem of mine is not consistent. Some times it is ok even with the same dataset. But what I suspect is that after using the system for some time around 1 hours I guess the total number of connection grows tremendiously which end of the day cause this problem ? I am not too sure on this please help me see the problem ? Thanks. I hope you can really help me man.
[27 Aug 2007 9:05]
Tonci Grgin
Newbie, still no simplified test case? Can you please try latest snapshot from http://downloads.mysql.com/snapshots/mysql-connector-net-1.0/mysql-connector-net-1.0.11-20... and inform me of result? I believe a similar problem was fixed there. In the meantime, I'll examine your error log.
[27 Aug 2007 16:19]
newbie Shai
Dear Tonci,
I dont quite get you about the sample test case ? The funny part if I tell you here is that sometimes when I use back the same set of data it gives me no error and some times it gives me error ? So how do you want me to give the sample test ? Is it a set of data ? The link which you sent me is about what the latest version of the .net connector is it? Thanks for all your time looking into this case ? Actually what is the main cause is it due to some bug in the connector is it ? Thanks.
[28 Aug 2007 9:30]
Tonci Grgin
Hi newbie. > I dont quite get you about the sample test case ? The funny part if I tell you here is that sometimes when I use back the same set of data it gives me no error and some times it gives me error ? So how do you want me to give the sample test ? Is it a set of data ? Please check for some of my test cases for c/NET in bugsdb, for example in Bug#25912 ([29 Jan 14:42] Tonci Grgin). Essentially, you need to produce one procedure test case (if possible) and add data to it so I can test. > The link which you sent me is about what the latest version of the .net connector is it? Yes, we should always test with latest versions, especially in areas where things develop quickly, like c/NET. > Thanks for all your time looking into this case No problem. Thanks for your interest in MySQL. > Actually what is the main cause is it due to some bug in the connector is it? Can't really tell right now.
[29 Aug 2007 3:23]
newbie Shai
Dear Tonci,
I have attached a file which create my tblStock table and with 600 data lines in it. This is my sample data set for you. Below is my codes which run on this data set. In function1 I will read startserial so for example based on my data set here the start serial should be 0001639200003645 and quantity is 600. So my end serial will be 0001639200004244 which you will get after traversing the loop. The problem here is that as I am noticing after quite a number of test is that the error appears after running numerous different set of data. So I guess after some time the pool of connection becomes full. I am not sure if I am analysing correctly my problem. Hope you can highlight to me. Do you think by putting pooling='false' will solve this problem ? Thanks once againg tonci. I am really interested in mysql because I read a lot about it. I would like to use it to store huge data hope I will succeed with mysql. Thanks.
//function 1
private void function1 ()
{
startSerial = Convert.ToDecimal(txtStartSerial.Text);
startSerialLength = txtStartSerial.TextLength;
for (int i = 0; i < quantity; i++)
{
decimal decimalSerial = startSerial + i;
string stringSerial = decimalSerial.ToString().PadLeft(startSerialLength, '0');
String mySelectQuery1 = "Select " +
"tblStock.stockID " +
"From tblStock " +
"Where tblStock.stockStatus ='y' And " +
"tblStock.stockSIQ='" + stringSerial + "'";
MySqlDataReader myReader1 = null;
MySqlCommand myCommand1 = new MySqlCommand(mySelectQuery1);
myCommand1.Connection = GlobalClass.openConnection();
try
{
myReader1 = myCommand1.ExecuteReader();
if (myReader1.HasRows == true)
{
while (myReader1.Read())
{
availableCouponQuantity++;
String myUpdateQuery1 = "Update tblStock " +
"Set tblStock.stockStatus = 'b'" +
"Where tblStock.stockID=" + Convert.ToInt32(myReader1.GetValue(0).ToString());
MySqlCommand myCommand2 = new MySqlCommand(myUpdateQuery1);
myCommand2.Connection = GlobalClass2.openConnection();
try
{
myCommand2.ExecuteNonQuery();
//txtImei.Text = "";
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
MessageBox.Show(ex.Message);
}
myCommand2.Dispose();
GlobalClass2.closeConnection();
}
}
else
{
MessageBox.Show("Coupon " + stringSerial + " Not Available");
GlobalClass.closeConnection();
}
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
MessageBox.Show(ex.Message);
}
myReader1.Close();
myCommand1.Dispose();
GlobalClass.closeConnection();
GlobalClass.disposeConnection();
}
connect. cs
using System;
using System.Collections.Generic;
using System.Text;
using System.Windows.Forms;
using MySql.Data.MySqlClient;
namespace mSystem
{
static class GlobalClass
{
static MySqlConnection connectionLocal ;
public static MySqlConnection openConnection()
{
try
{
connectionLocal = new MySqlConnection("Address='localhost';Database='test1';User
Name='root';Password='12345'");
connectionLocal.Open();
return connectionLocal;
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
MessageBox.Show(ex.Message + "MYSQL ERROR");
return null;
}
catch (System.Net.Sockets.SocketException ex)
{
MessageBox.Show(ex.Message+"SYSTEM ERROR");
return null;
}
}
public static void closeConnection()
{
connectionLocal.Close();
}
public static void disposeConnection()
{
connectionLocal.Dispose();
}
}
}
[29 Aug 2007 3:25]
newbie Shai
sampledataset
Attachment: sampledataset.sql (text/plain), 59.44 KiB.
[29 Aug 2007 10:43]
Tonci Grgin
Hi newbie.
> I am noticing after quite a number of test is that the error appears after running numerous different set of data. So I guess after some time the pool of connection becomes full. I am not sure if I am analysing correctly my problem. Hope you can highlight to me. Do you think by putting pooling='false' will solve this problem ?
This *could* be the part of problem as there is no *removepooledconnection* function in GA versions of c/NET thus if, and only if, you change your connection string by connecting to different servers while application is running you may hit OOM or this error. Is this condition true for your application? If not, then this is not the case.
Again, your test case is quite inadequate and won't run the way you pasted it, I was referring more to test case like this:
ProcBtnClick
connection
datareader
do some stuff
close everything
endProc
From what I see in your code, you are opening connections needlessly... Your both commands operate on same database and same set of data yet you open new connection for each one of them:
myCommand2.Connection = GlobalClass.openConnection();
and
myCommand2.Connection = GlobalClass2.openConnection();
Please remove connect.cs and that whole GlobalClass stuff and then try with just one connection.
>Thanks once againg tonci. I am really interested in mysql because I read a lot about it. I would like to use it to store huge data hope I will succeed with mysql. Thanks.
No need to thank me. I'll help as much as I can but bare in mind there are a lot of people posting their problems here, and if you check, you'll see I work on mostly all connector issues. If you need code reviews / optimization help etc you can check http://www.mysql.com/support/.
[29 Aug 2007 13:22]
newbie Shai
Dear Tonci, >This *could* be the part of problem as there is no *removepooledconnection* function in GA versions of c/NET thus if, and only if, you change your connection string by connecting to different servers while application is running you may hit OOM or this error. Is this condition true for your application? If not, then this is not the case. I dont get you when you say no "removepooledconnection* function in GA ? What is GA ? So which version of the c/Net is most appropriate for me ? What is OOM ? From what I see in your code, you are opening connections needlessly... Your both commands operate on same database and same set of data yet you open new connection for each one of them: myCommand2.Connection = GlobalClass.openConnection(); and myCommand2.Connection = GlobalClass2.openConnection(); >If I dont open a new connection I get an error as saying that there is already an open connection ? Can you show me what I should exactly modify on my code I am totall confuse over there ? How to run the whole thing with just one connection an example please ? Thanks.
[30 Aug 2007 8:32]
Tonci Grgin
We're sorry, but the bug system is not the appropriate forum for asking help on using MySQL products. Your problem is not the result of a bug. Support on using our products is available both free in our forums at http://forums.mysql.com/ and for a reasonable fee direct from our skilled support engineers at http://www.mysql.com/support/ Thank you for your interest in MySQL. Explanation: newbie, I am really not at liberty to teach you how to write C# applications. That's why I'm insisting on simplified, 1 procedure test case exhibiting the error. Since you're constantly failing to attach one I must assume you're calling for code review which brings us to my first statement. Now, - GA = Generally available - OOM=Out of Memory - Proper version is last one found on downloads page (GA) or even the one in snapshots page (if it has some fix you need) according to your framework. >If I dont open a new connection I get an error as saying that there is already an open connection ? Can you show me what I should exactly modify on my code I am totall confuse over there ? How to run the whole thing with just one connection an example please ? I believe the error is that "...there is already an open datareader..." This is not our limitation but the one of NET FW. You can not have multiple readers open on same connection at the same time. Finish what you're doing, close the reader and then open new one. connection1 command1 (connection1) command2 (connection1) dr1(any command) //do stuff with dr1 dispose dr1 dr2(any command) //do stuff with dr2 dispose dr2 etc In your case - first loop is totally unnecessary, you should use SELECT MAX(...) and get result directly - opening second connection is totally unnecessary I suggest you review provided tests in c/NET installation\Samples folder and test cases available in bugsdb. There is a multitude of tests attached here. Go to http://bugs.mysql.com/bug.php, fill in the fields and search. Here is an URL to start from: http://bugs.mysql.com/search.php?search_for=&status=All&severity=&limit=10&order_by=&cmd=d... If you can provide me with test case in form I asked before, please reopen the report.
[31 Aug 2007 5:33]
newbie Shai
Dear Tonci,
First of all sorry for the inconvenience I am really sorry there might be some misunderstanding my site with regards to what you requiring with regards to the test case. I have gone to the folder which you mention "C:\Program Files\MySQL\MySQL Connector Net 1.0.9\Samples\Table Editor\cs" I have opened the Form1.cs. I have seen the code there. Please correct me I am actually preparing a test case. But I am not clear of what exactly you are requiring I have send you the test data can you create the table base on the data ? So next what you want I is it a full and simplified function is it ? I have listed below a full working function but only you need to put a text box called as txtStartSerial. So this function is linked to its keydown event. Please let me know if this one is correct ?
private void txtStartSerial_KeyDown(object sender, KeyEventArgs e)
{
if (e.KeyValue == 13)
{
if (txtStartSerial.Text.Length == 0)
{
MessageBox.Show("Insert A Value For Start Serial");
}
else
{
startSerial = Convert.ToDecimal(txtStartSerial.Text);
startSerialLength = txtStartSerial.TextLength;
for (int i = 0; i < quantity; i++)
{
decimal decimalSerial = startSerial + i;
string stringSerial = decimalSerial.ToString().PadLeft(startSerialLength, '0');
String mySelectQuery1 = "Select " +
"tblStock.stockID " +
"From tblStock " +
"Where tblStock.stockStatus ='y' And " +
"tblStock.stockSIQ='" + stringSerial + "' And tblStock.productID=" + productCodeID;
MySqlDataReader myReader1 = null;
MySqlCommand myCommand1 = new MySqlCommand(mySelectQuery1);
myCommand1.Connection = GlobalClass.openConnection();
try
{
myReader1 = myCommand1.ExecuteReader();
if (myReader1.HasRows == true)
{
while (myReader1.Read())
{
String myUpdateQuery1 = "Update tblStock " +
"Set tblStock.stockStatus = 'b'" +
"Where tblStock.stockID=" + Convert.ToInt32(myReader1.GetValue(0).ToString());
MySqlCommand myCommand2 = new MySqlCommand(myUpdateQuery1);
myCommand2.Connection = GlobalClass2.openConnection();
try
{
myCommand2.ExecuteNonQuery();
//txtImei.Text = "";
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
MessageBox.Show(ex.Message);
}
myCommand2.Dispose();
GlobalClass2.closeConnection();
}
}
else
{
MessageBox.Show("Coupon " + stringSerial + " Not Available");
GlobalClass.closeConnection();
}
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
MessageBox.Show(ex.Message);
}
myReader1.Close();
myCommand1.Dispose();
GlobalClass.closeConnection();
}
}
}
}
I hope this time I have send you the right working test case. Thanks and also sorry dear tonci. I really appreciate your time for my problem here.
[1 Sep 2007 7:29]
Tonci Grgin
Newbie, thanks for the extra effort, reopening. Explaining to other people the nature of your problem is an art in itself, I know. It's not that easy. From my part, I have too many bug reports to write test cases for each one so I have to insist on clean and complete reports. Thanks for your interest in MySQL.
[1 Sep 2007 8:43]
newbie Shai
Dear Tonci,
Thanks I do understand your job role and really appreciate for your patience. But did I fulfill to your test case requirement ? I just forgot t o add another thing there is one quantity variable which you can set as 600 for that particular data set. Thanks and hope you can help me see where the too many connection error is exactly. Many thanks and sorry for my misunderstandings.
[4 Oct 2007 18:10]
Tonci Grgin
Hi newbie and sorry for the delay, we had many events lately that I needed to attend. Will resume work on this report today.
[19 Oct 2007 15:15]
Tonci Grgin
I am having troubles setting up legal software environment for testing NET FW 1.1 bugs... VSExpress works only with NET FW 2.0 so I'll have to find someone with VS2003.
[25 Oct 2007 16:09]
Reggie Burnett
Newbie If you are still having a problem, please try the latest 1.0 release. It is version 1.0.10.1 and contains a backported pooling fix that was the result of bug #25603. If this fixes your problem or you are no longer having the problem, please let us know so we can close this issue. Reggie
[25 Oct 2007 16:47]
Tonci Grgin
Thanks Reggie.
[26 Oct 2007 2:40]
newbie Shai
Dear Reggie,
I dont get you what is the version 1.0 ? Is it a new .net connector ? Because from what I know the latest version is 5.0 right ? Hope you can clear to me what is 1.0 ? Thanks.
[26 Oct 2007 3:16]
Reggie Burnett
You reported this problem against 1.0.9. We now have 1.0.10.1 available. Our 5.0 and 5.1 products are for .net 2.0 applications.
[26 Oct 2007 3:30]
newbie Shai
Dear Reggie,
My application is in effect based on .net 2.0 framework. So I guess I should be using 5.0 or 5.1 right ? If this is the case the pooling problem is should be happening in the 5.0 right ? In that case I can set Pooling="true"; Thanks.
[26 Oct 2007 16:00]
Tonci Grgin
Right newbie. Sorry I presumed you use NET fw 1.1 when c/NET 1.x was mentioned...
[27 Oct 2007 3:31]
newbie Shai
Dear Tonci,
Thanks let me use the new connector and then I will try and see. Thanks once again for all your time. For time being maybe we can close this bug. Thanks for all your effort and time.
[28 Oct 2007 14:37]
Tonci Grgin
Newbie, I agree. Closing the report as asked. In any case, this report brought to my attention the fact that I don't have proper environment for testing NET fw 1.1 bugs. Thanks for your interest in MySQL.
Description: Dear All, I have C# application. My application receives a total number and a start serial so for example when I put the total number as 200 and start serial as 000400. So what happens is that I will run a for loop and do a select query to check each of my serial status from the database. So in this case if will be first 000400,000401.... till the 200 count. The problem I get stuck sometimes is an error saying too many connection. Each time I run my query I open and immediately within the same loop I do a close too. So what can it be any idea is it a bug or wat ? I am using Innodb storage engine and mysql .net connector 1.0.9. Thanks for all the consideration. How to repeat: Running a for loop of open and close on a certain number.