MySQL Bugs: #24138: connection not getting closed in mysql server .

Bug #24138	connection not getting closed in mysql server .
Submitted:	9 Nov 2006 12:22	Modified:	16 Nov 2006 20:29
Reporter:	Ranjith Kumar	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	both 1.0.7 and 5.0.2	OS:	Windows (windows )
Assigned to:		CPU Architecture:	Any

Description:
problem while using .net connector to close the mysql connection . the connection is still open in the server when viewed in mysql administrator . 

How to repeat:

im using mysql 5.0 with c#.net and i tried using both the connectors (1.0.7 and 5.0.2 ) which is used to connect mysql with .net . 

i used the ".close(),.dispose() methods to close the connection with the server and i  see that in the c#.net software the connection state is closed but the connection is still there in mysql server.
 
i developed a program which uses 3 computers . when all the three computers are working i  get " too many connections error " .

and there is also a fuction in the connector ".Cancel() for mysqlcommand which is not working . 

thanking you 

Ranjith kumar 
renji.k@gmail.com   

Suggested fix:
plese see if you can close the connection completely

Hi Ranjith and thanks for your problem report.
I will need some more info from you before I can proceede:
 - MySQL server version
 - my.cnf/ini file
 - NET FW version
 - OS version(s)
 - Small but complete test case demonstrating this behavior

The ini file

Attachment: my_ini.zip (application/x-zip-compressed, text), 3.56 KiB.

Dear Tonci Grgin , 
			Thank you for the mail and support you give  . I have attached a rar file along with this mail which  has the  small windows application where I have used your .net connector 5.0.2 ( beta ) . here I have opened the  mysql connection and have fetched data from the database and shown it on a windows form . i have described the code within 
 * explanation *
the follwing lines will explain the code 

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using WindowsApplication1;
using MySql.Data.MySqlClient;

namespace WindowsApplication1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
        
                   * i describe the connection here *     

	 MySqlConnection ne = new MySqlConnection("username=yroot;password=hello1234;server=localhost;database=fms"); 
            

           * filing the adapter (im not using ne.Open() since a MysqlDataAdapter will automaticallly do that *  

	MySqlDataAdapter dap = new MySqlDataAdapter("select * from tbl_cus_mas", ne);

            
            * i fill the datatable here *  

       DataTable dt = new DataTable();

       dap.Fill(dt);

        * the retrived data is sent to the windows form *  

        dataGridView1.DataSource = dt;

       ne.Close();  * i close the connection here  *
          
        }
    }
}

after i close the connection i see a sleeping connection in the mysql adinistrator server connections . but when i check the state of connection in .net the state is closed .  the same happens with your connector 1.0.7 . 

problem i face : when i run the software which has more connetions on a network i get " too many connections " error . 

mysql server version i use is 5.0
ini file attached 
.net version 2.0 ( visual studio 2005)  
operating systems : microsoft windows xp proffesional / microsoft windows 2003 server ( enterprise edition )

Hi Ranjith. Please attach your code to this report ("Files" tab). You can't send it to me by e-mail.

is there any patch released for that 

ranjith

This is expected behavior.  You are not disabling connection pooling.  Because pooling is enabled, when you close a connection it does not actually close the socket to the server.  Instead it marks the connection as idle.