Disconnections from MySQL Server

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for MySQL
Post Reply
alexh
Posts: 7
Joined: Fri 29 Jun 2007 22:57

Disconnections from MySQL Server

Post by alexh » Thu 13 Mar 2008 14:34

Hello,

Ever since using CoreLab months ago, I've noticed that when I try to initiate a connection to a remote mysql server, I consistently run into some problems with timeouts and disconnections.

I've increased the ConnectionTimeout, and that doesn't seem to be the problem.

Oddly, when I launch the program for the first time, it always seems to communicate with mysql just fine. If I immediately follow with additional commands (viewing other pages that also communicate with the server), it also causes no problem. I'm running into the problem when I'm idle for a few minutes, and then executing another command. That's where I get the failure to communicate with mysql server error message.

If I then debug --> restart, I'll get the same error. But then, if I debug --> restart a second time, it will then properly work and reinitiate a connection with the server.

Someone suggested this below, but do I have to do this for each query:
[email protected] wrote:I solved the problem ,

if (maincss.dataHandler.getConnction().Ping()==false)
{

}


use ping to check the connection and recreate the connection ,
that is the solution.

Thank you in advance

Alexey.mdr
Posts: 729
Joined: Thu 13 Dec 2007 10:24

Post by Alexey.mdr » Thu 13 Mar 2008 15:08

Could you post the exception text and the stacktrace please?

alexh
Posts: 7
Joined: Fri 29 Jun 2007 22:57

Post by alexh » Thu 13 Mar 2008 15:28

Thanks, hope this helps:

Code: Select all

Server Error in '/MillhornPracticeManagement' Application.
Lost connection to MySQL server during query
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: CoreLab.MySql.MySqlException: Lost connection to MySQL server during query

Source Error:

Line 65:         string mySelectQuery = "SELECT ClientID, ClientName FROM CLIENT WHERE ClientID = '" + thisClientID + "'";
Line 66:         MySqlCommand myCommand = new MySqlCommand(mySelectQuery, myConnection);
Line 67:         MySqlDataReader myReader = myCommand.ExecuteReader();
Line 68:         // always call Close when done reading. 
Line 69:         while (myReader.Read())


Source File: c:\Projects\MillhornPracticeManagement\Default.aspx.cs    Line: 67

Stack Trace:

[MySqlException (0x80004005): Lost connection to MySQL server during query]
   CoreLab.MySql.bb.a(Int32& A_0, Int32& A_1) +154
   CoreLab.MySql.bb.a(Byte[] A_0, Int32 A_1, Int32 A_2) +249
   CoreLab.MySql.bb.a() +45
   CoreLab.MySql.bb.c() +58
   CoreLab.MySql.c.a(f[]& A_0, Int32& A_1) +101
   CoreLab.MySql.c.a(Byte[] A_0, Int32 A_1, Boolean A_2) +97
   CoreLab.MySql.z.e() +112
   CoreLab.MySql.z.o() +77
   CoreLab.MySql.MySqlCommand.a(CommandBehavior A_0, IDisposable A_1, Int32 A_2, Int32 A_3) +1051
   CoreLab.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior) +289
   System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior) +7
   CoreLab.MySql.MySqlCommand.ExecuteReader() +26
   _Default.fetchClientDetails(String thisClientID) in c:\Projects\MillhornPracticeManagement\Default.aspx.cs:67
   _Default.Page_Load(Object sender, EventArgs e) in c:\Projects\MillhornPracticeManagement\Default.aspx.cs:38
   System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +15
   System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +33
   System.Web.UI.Control.OnLoad(EventArgs e) +99
   System.Web.UI.Control.LoadRecursive() +47
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1436


Version Information: Microsoft .NET Framework Version:2.0.50727.1433; ASP.NET Version:2.0.50727.1433 

Alexey.mdr
Posts: 729
Joined: Thu 13 Dec 2007 10:24

Post by Alexey.mdr » Thu 13 Mar 2008 15:45

Do you open/close a connection on each database data exchange operation?
Could you provide a block of code where this error shows up please?

alexh
Posts: 7
Joined: Fri 29 Jun 2007 22:57

Post by alexh » Thu 13 Mar 2008 17:44

Alexey.mdr wrote:Do you open/close a connection on each database data exchange operation?
Could you provide a block of code where this error shows up please?
Thank you for your reply.

No, db connection isn't opened and closed on each query. All SQL communication is done within our Class files, so pretty much every time an essential class is opened, the connection to the db is opened, the select queries take place, and the db connection is closed. The data is manipulated while in the sesion/viewstate and the update statements are executed all at once once updateDB() is called within the class.

The error can take place at ANY time in the application, even if the instruction is a single update statement, and no classes are even used. Again, I notice this can be reproduced ANYWHERE I use mysql code to a remote mysql server -- the key here, however, is the time. As expressed in the first post, if the mysql statements are made in succession, the error usually isn't experienced. If I sit on a page for a few minutes, and then make another command, thats where I run into the error...... seriously sounds like some type of timeout or keepalive issue, but I've looked into both extensively.

A code example:

Code: Select all

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using CoreLab.MySql;
/// 
/// Summary description for Clients
/// 
public class Client
{
    int clientid;

    string firstname, lastname;
    string company;
    
    string address1, address2;
    string city, state, zip;
    string phone1, phone2, fax;
    string email;
    string username, password;
    string status;
    string additional;  // additional contract terms applies to all client carts

    ArrayList cartids;
    bool isActive;

    public int getClientID() { return clientid; }
    public string getFirstName() { return firstname; }
    public string getLastName() { return lastname; }
    public string getFullName() { return firstname + " " + lastname; }
    public string getCompany() { return company; }
    public string getAddress1() { return address1;}
    public string getAddress2() { return address2;}
    public string getCity() { return city; }
    public string getState() { return state;}
    public string getZip() { return zip;}
    public string getPhone1() { return phone1;}
    public string getPhone2() { return phone2;}
    public string getFAX() { return fax;}
    public string getEmail() { return email;}
    public string getUsername() { return username;}
    public string getPassword() { return password;}
    public string getStatus() { return status;}
    public bool getIsActive() { return isActive;}
    public string getAdditional() { return additional; }

    public void setAdditional(string thisAdditional) { additional = thisAdditional; }


	public Client()
	{
        isActive = false;

        clientid = 0;
        firstname = "";
        lastname ="";
        firstname = "";
        company="";

        email = "";
        address1 = "";
        address2 = "";
        city = "";
        state = "";
        zip = "";
        phone1 = "";
        phone2 = "";
        fax = "";

        username = "";
        password = "";
        status ="A";

        additional = "";
    }


    public void setValues(string thisFirstName, string thisLastName, string thisCompany, string thisAddress1, string thisAddress2, string thisCity, string thisState, string thisZip, string thisPhone1, string thisPhone2, string thisFAX, string thisEmail, string thisUsername, string thisPassword, string thisStatus, string thisAdditional)
    {
        firstname = thisFirstName;
        lastname = thisLastName;
        company = thisCompany;

        email = thisEmail;
        address1 = thisAddress1;
        address2 = thisAddress2;
        city = thisCity;
        state = thisState;
        zip = thisZip;
        phone1 = thisPhone1;
        phone2 = thisPhone2;
        fax = thisFAX;
        additional = thisAdditional;

        // isActive = true;

        
    }

    public int updateDB()
    {
        MySqlConnection myConnection = connectDB();
        myConnection.Open();

        if (isActive == true)
        {
            
            // update records
            string myInsertQuery = "UPDATE Clients SET FirstName = '" + firstname + "', LastName = '" + lastname + "', Email = '" + email + "', Address1 = '" + address1 + "', Address2 = '" + address2 + "', City = '" + city + "', State = '" + state + "', Zip = '" + zip + "', Phone1 = '" + phone1 + "', Phone2 = '" + phone2 + "', FAX = '" + fax + "', Username = '" + username + "', Password = '" + password + "', Status = '" + status + "', Additional = '" + additional + "' WHERE ClientID = " + clientid.ToString() + ";";
            MySqlCommand myCommand = new MySqlCommand(myInsertQuery, myConnection);

            myCommand.ExecuteNonQuery();
        }
        else if (isActive == false)
        {
            // insert new record
            string myInsertQuery = "INSERT INTO Clients (ClientID, FirstName, LastName, Company, Address1, Address2, City, State, Zip, Phone1, Phone2, FAX, Email, Username, Password, Status, Additional) VALUES ('', '" + firstname + "','" + lastname + "','" + company + "','" + address1 + "','" + address2 + "','" + city + "','" + state + "','" + zip + "','" + phone1 + "','" + phone2 + "','" + fax + "','" + email + "','" + username + "','" + password + "','" + status + "', '" + additional + "');";
            MySqlCommand myCommand = new MySqlCommand(myInsertQuery, myConnection);

            myCommand.ExecuteNonQuery();
            clientid = getLastClientID();
            isActive = true;
        }

        myConnection.Close();

        return clientid;
    }

    public int getLastClientID()
    {
        MySqlConnection myConnection = connectDB();
        myConnection.Open();

        string mySelectQuery = "SELECT ClientID FROM Clients ORDER BY ClientID ASC;";
        MySqlCommand myCommand = new MySqlCommand(mySelectQuery, myConnection);
        MySqlDataReader myReader = myCommand.ExecuteReader();

        int count = 0;
        int result = 0;

        while (myReader.Read())
        {
            result = myReader.GetInt32(0);
            count++;
        }

        myReader.Close();
        // always call Close when done reading. 
        myConnection.Close();

        return result;
    }


    public ArrayList populateCarts(int thisClientID)
    {
        MySqlConnection myConnection = connectDB();
        myConnection.Open();

        ArrayList myList = new ArrayList();

        string mySelectQuery = "SELECT CartID FROM Carts WHERE ClientID = " + thisClientID.ToString() + ";";
        MySqlCommand myCommand = new MySqlCommand(mySelectQuery, myConnection);
        MySqlDataReader myReader = myCommand.ExecuteReader();

        int count = 0;
        int result = 0;

        while (myReader.Read())
        {
            result = myReader.GetInt32(0);
            myList.Add(result);
            count++;
        }

        myReader.Close();
        // always call Close when done reading. 
        myConnection.Close();

        return myList;
    }

    public ArrayList populateCarts()
    {
        ArrayList myList = new ArrayList();

        if (isActive)
        {

            MySqlConnection myConnection = connectDB();
            myConnection.Open();

            string mySelectQuery = "SELECT CartID FROM Carts WHERE ClientID = " +  clientid.ToString() + ";";
            MySqlCommand myCommand = new MySqlCommand(mySelectQuery, myConnection);
            MySqlDataReader myReader = myCommand.ExecuteReader();

            int count = 0;
            int result = 0;

            while (myReader.Read())
            {
                result = myReader.GetInt32(0);
                myList.Add(result);
                count++;
            }

            myReader.Close();
            // always call Close when done reading. 
            myConnection.Close();
        }

        return myList;
    }

    public void openClient(int openClientID)
    {
        MySqlConnection myConnection = connectDB();
        myConnection.Open();

        string mySelectQuery = "SELECT FirstName, LastName, Company, Address1, Address2, City, State, Zip, Phone1, Phone2, FAX, Email, Username, Password, Status FROM Clients WHERE ClientID = " + openClientID.ToString() + ";";
        MySqlCommand myCommand = new MySqlCommand(mySelectQuery, myConnection);
        MySqlDataReader myReader = myCommand.ExecuteReader();

        int count = 0;

        while (myReader.Read())
        {
            firstname = myReader.GetString(0).ToString();
            lastname = myReader.GetString(1).ToString();
            company = myReader.GetString(2).ToString();
            address1 = myReader.GetString(3).ToString();
            address2 = myReader.GetString(4).ToString();
            city = myReader.GetString(5).ToString();
            state = myReader.GetString(6).ToString();
            zip = myReader.GetString(7).ToString();
            phone1 = myReader.GetString(8).ToString();
            phone2 = myReader.GetString(9).ToString();
            fax = myReader.GetString(10).ToString();
            email = myReader.GetString(11).ToString();
            username = myReader.GetString(12).ToString();
            password = myReader.GetString(13).ToString();
            status = myReader.GetString(14).ToString();

            clientid = openClientID;
            count++;
        }

        myReader.Close();
        // always call Close when done reading. 
        myConnection.Close();

        if (count > 0)
        {
            isActive = true;
            // proceed

            cartids = getCartIDs();
        }

        else
            isActive = false;   // nothing found

    }

    public ArrayList getClientNames()
    {
        MySqlConnection myConnection = connectDB();
        myConnection.Open();

        string mySelectQuery = "SELECT ClientID, Name FROM clients ORDER BY Name ASC;";
        MySqlCommand myCommand = new MySqlCommand(mySelectQuery, myConnection);
        MySqlDataReader myReader = myCommand.ExecuteReader();

        ArrayList myList = new ArrayList();
        ArrayList idList = new ArrayList();
        ArrayList nameList = new ArrayList();

        while (myReader.Read())
        {
            idList.Add((string)myReader.GetInt32(0).ToString());
            nameList.Add((string)myReader.GetString(1).ToString());
        }

        myReader.Close();
        // always call Close when done reading. 
        myConnection.Close();

        myList.Add(idList);
        myList.Add(nameList);

        return myList;
    }


    public ArrayList getCartIDs()
    {
        ArrayList myList = new ArrayList();

        if (isActive)
        {
            MySqlConnection myConnection = connectDB();
            myConnection.Open();

            string mySelectQuery = "SELECT CartID FROM Carts WHERE ClientID = " + clientid.ToString() + ";";
            MySqlCommand myCommand = new MySqlCommand(mySelectQuery, myConnection);
            MySqlDataReader myReader = myCommand.ExecuteReader();

            while (myReader.Read())
            {
                myList.Add((string)myReader.GetInt32(0).ToString());
            }

            myReader.Close();
            // always call Close when done reading. 
            myConnection.Close();
        }

        return myList;
    }




    protected MySqlConnection connectDB()
    {
        Misc m = new Misc();
        return m.connectDB();
    }


    public ArrayList getRecentTransactions()
    {
        ArrayList myList = new ArrayList();

        if (isActive)
        {
            Cart car = new Cart();

            // todo method convertDateStringToString should be in class Misc in addition to Cart - performance
            string searchDate = car.convertDateStringToString(DateTime.Now.AddMonths(-3).ToShortDateString());

            MySqlConnection myConnection = connectDB();
            myConnection.Open();

            //todo - i removed the WHERE clauase specifying only the last 90 days should be included.  errors occurred with it enabled.  currently all results will be displayed
            string mySelectQuery = "SELECT Transactions.TransactionID FROM Transactions, CartsSchedule WHERE Transactions.ClientID = " + clientid.ToString() + " AND Transactions.TransactionID = CartsSchedule.TransactionID ORDER BY CartsSchedule.RentDate ASC;";
            MySqlCommand myCommand = new MySqlCommand(mySelectQuery, myConnection);
            MySqlDataReader myReader = myCommand.ExecuteReader();

            int result = 0;

            while (myReader.Read())
            {
                result = myReader.GetInt32(0);
                myList.Add(result);
            }

            myReader.Close();
            // always call Close when done reading. 
            myConnection.Close();
        }
        return myList;
    }




}

See any problems or bad practices with CoreLab? Thank you!

Alexey.mdr
Posts: 729
Joined: Thu 13 Dec 2007 10:24

Post by Alexey.mdr » Fri 14 Mar 2008 06:32

Probably the problem is here:

Code: Select all

MySqlConnection myConnection = connectDB();
It is likely that you you are trying to use a connection that is no longer valid on the server side.
As you are saying the error occurs only after you were idle for some time.
MySQL server might close the connection and return the error
after the connect_timeout variable is expired on the server side.
Try using a new

Code: Select all

MySqlConnection myConnection = new MySqlConnection(“user id=root;password=root;...”);
See if you receive the error again. If not, the problem was here.
You might want to use connection pooling in this case, if you'd prefer.

alexh
Posts: 7
Joined: Fri 29 Jun 2007 22:57

Update

Post by alexh » Wed 09 Apr 2008 23:01

Requesting additional help on this topic...

Hello,

I have taken your suggestions and still receive error messages that I have lost connection to the mysql server -- though I think it is less frequent.

In either case, this problem is present in other aspects, as well.

As demonstrated in the class above, most all mysql interactions take place in our Classes, on not on the aspx page, itself.

For the sake of trying to isolate this problem, I configured the mySQL into Visual Studios data sources, and added MySqlDataSource to the form. I configured a single dropdownlist without issue.

I then proceeded to add a second MySqlDataSource to the form. I then add a second DropDownList and configure data sources for the element. This is where I ran into problem.

When configuring the data source, it wizard asks which data source to use. I specify MySqlDataSource2. It then asks for which connection string to use. Regardless of what connection string I choose, when I press Next I am presented with an error message saying lost connection to server.

The work around is to cancel out of the wizard, open the server explorer, and refresh the MySql Server. The table list is then repopulated, and the next time I go to configure the DropDownList data source, I don't receive an error message.

This error is reproducable. After each and every component I add to a form and configure to use MySqlDataSource, I must then Refresh my Data Source in order to be able to successfully add/configure another component to the form.

-------

So this Lost connection to MySql Server error message is present not only in my code -- but also while configuring the UI.

---

Thank you in advance.

Alexey.mdr
Posts: 729
Joined: Thu 13 Dec 2007 10:24

Post by Alexey.mdr » Thu 10 Apr 2008 11:53

We have investigated the problem.
The issue is not reproducible in our test environment.
We concluded that the problem is most likely in the server configuration.
Please see this link for most common reasons of the error:
http://dev.mysql.com/doc/refman/5.0/en/gone-away.html

alexh
Posts: 7
Joined: Fri 29 Jun 2007 22:57

Post by alexh » Thu 10 Apr 2008 15:29

Alexey.mdr wrote:We have investigated the problem.
The issue is not reproducible in our test environment.
We concluded that the problem is most likely in the server configuration.
Please see this link for most common reasons of the error:
http://dev.mysql.com/doc/refman/5.0/en/gone-away.html
I appreciate your investigation, and will test changing the connection options on the mysql server, will test using an alternate mysql server, and will also test with a mysql v5 install, instead of 4.

I will update this thread with what I experience, in case any one else runs into this in the future.

Thank you.

Alexey.mdr
Posts: 729
Joined: Thu 13 Dec 2007 10:24

Post by Alexey.mdr » Fri 11 Apr 2008 07:19

We are waiting for the results of your testing.
Feel free to contact us if the problem appears again.

Post Reply