Good afternoon,

We are facing problems regarding lost connection and timeout on mysql, i've noticed that there are lots of sleep and aborted connections.

for checking aborted connections (currently, 183 aborted clients and 64 aborted connections)

SHOW GLOBAL STATUS LIKE '%Abort%'

Our setup is llblgenpro 5.2.1, .net framework 4.6 with Mysql database.

We already checked that all of our connections are enclosed by an "using" statement on adapter, like llblgen documentation advises, so there is no adapter left unclosed.

so, i think that there isn't connections left open on code, but still the database gets lots of sleep and aborted connections that i think it is related to connection losses

Does someone knows if there is an issue like that? Maybe something i'm ignoring?

Thanks in advance.

Exception example:

{ "ClassName": "Devart.Data.MySql.MySqlException", "Message": "Lost connection to MySQL server during query", "Data": null, "InnerException": { "ClassName": "System.TimeoutException", "Message": "Server did not respond within the specified timeout interval.", "Data": null, "InnerException": { "ClassName": "System.IO.IOException", "Message": "Unable to read data from the transport connection: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.", "Data": null, "InnerException": { "NativeErrorCode": 10060, "ClassName": "System.Net.Sockets.SocketException", "Message": "A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond", "Data": null, "InnerException": null, "HelpURL": null, "StackTraceString": " at System.Net.Sockets.Socket.Receive(Byte[] buffer, Int32 offset, Int32 size, SocketFlags socketFlags)\r\n at System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 size)", "RemoteStackTraceString": null, "RemoteStackIndex": 0, "ExceptionMethod": "8\nReceive\nSystem, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089\nSystem.Net.Sockets.Socket\nInt32 Receive(Byte[], Int32, Int32, System.Net.Sockets.SocketFlags)", "HResult": -2147467259, "Source": "System", "WatsonBuckets": null }, "HelpURL": null, "StackTraceString": " at System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 size)\r\n at Devart.Common.ae.a(Byte[] A_0, Int32 A_1, Int32 A_2)", "RemoteStackTraceString": null, "RemoteStackIndex": 0, "ExceptionMethod": "8\nRead\nSystem, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089\nSystem.Net.Sockets.NetworkStream\nInt32 Read(Byte[], Int32, Int32)", "HResult": -2146232800, "Source": "System", "WatsonBuckets": null }, "HelpURL": null, "StackTraceString": " at Devart.Common.ae.a(Byte[] A_0, Int32 A_1, Int32 A_2)\r\n at Devart.Common.x.c(Byte[] A_0, Int32 A_1, Int32 A_2)\r\n at Devart.Common.af.d(Byte[] A_0, Int32 A_1, Int32 A_2)", "RemoteStackTraceString": null, "RemoteStackIndex": 0, "ExceptionMethod": "8\na\nDevart.Data.MySql, Version=8.9.931.0, Culture=neutral, PublicKeyToken=09af7300eec23701\nDevart.Common.ae\nInt32 a(Byte[], Int32, Int32)", "HResult": -2146233083, "Source": "Devart.Data.MySql", "WatsonBuckets": null }, "HelpURL": null, "StackTraceString": " at Devart.Data.MySql.aw.d(Exception A_0)\r\n at Devart.Common.af.d(Byte[] A_0, Int32 A_1, Int32 A_2)\r\n at Devart.Data.MySql.c.n()\r\n at Devart.Data.MySql.c.h()\r\n at Devart.Data.MySql.u.a(a4[]& A_0, Int32& A_1, Boolean A_2)\r\n at Devart.Data.MySql.u.a(Byte[] A_0, Int32 A_1, Boolean A_2, String A_3)\r\n at Devart.Data.MySql.k.e()\r\n at Devart.Data.MySql.MySqlCommand.InternalExecute(CommandBehavior behavior, IDisposable stmt, Int32 startRecord, Int32 maxRecords)\r\n at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior, Boolean nonQuery)\r\n at Devart.Common.DbCommandBase.ExecuteNonQuery()\r\n at Devart.Data.MySql.MySqlConnection.Commit()\r\n at Devart.Data.MySql.MySqlTransaction.Commit()\r\n at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterCore.Commit()\r\n at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterCore.SD.LLBLGen.Pro.ORMSupportClasses.ITransactionController.Commit()\r\n at Twyne.PrivateCommand.Lead.SaveImpressionHandler.PersistDb(SaveImpressionCommand command) in C:\\Ifficient\\Source\\Twyne.Private\\Twyne.PrivateCommand\\Lead\\Save\\SaveImpressionHandler.cs:line 245", "RemoteStackTraceString": null, "RemoteStackIndex": 0, "ExceptionMethod": "8\nd\nDevart.Data.MySql, Version=8.9.931.0, Culture=neutral, PublicKeyToken=09af7300eec23701\nDevart.Data.MySql.aw\nVoid d(System.Exception)", "HResult": -2147467259, "Source": "Devart.Data.MySql", "WatsonBuckets": null, "message": "Lost connection to MySQL server during query", "source": 2013, "sqlstate": "00000" }

Devart.Data.MySql, Version=8.9.931.0

That's 2 years old version. Could you please try with the latest one.

Also does the database reside on the same machine, or are you connecting to it over a LAN, as it seems like a network error?

Walaa wrote:

Devart.Data.MySql, Version=8.9.931.0

That's 2 years old version. Could you please try with the latest one.

Also does the database reside on the same machine, or are you connecting to it over a LAN, as it seems like a network error?

Thank you for your reply.

The database is an Amazon RDS.

I'm updating the package to see what happens

amazon rds is likely running into transient errors with respect to connections, which is the same for e.g. Azure and SQL Server. To avoid these, please use transient error recovery. We ship an Azure / SQL Server class for that, but it's very easy to rework that one for mysql.

https://www.llblgen.com/Documentation/5.6/LLBLGen%20Pro%20RTF/Using%20the%20generated%20code/gencode_transientrecovery.htm

Below you see the azure transient error recovery class, which you can change for mysql. To make sure it works for mysql, you have to lookup the error codes for mysql which are considered a transient error, e.g. connection lost/timeout that kind of thing. E.g. an FK violation isn't a transient error and therefore it should ignore that one (so only pick up the ones which you know you can retry and recover from)

////////////////////////////////////////////////////////////////////////////////////////////////////////
// LLBLGen Pro is (c) 2002-2013 Solutions Design. All rights reserved.
// http://www.llblgen.com
// The sourcecode for the ORM Support classes has been made available to LLBLGen Pro licensees
// so they can modify, update and/or extend it. Distribution of this sourcecode in textual, non-compiled, 
// non-binary form to non-licensees is prohibited. Distribution of binary compiled versions of this 
// sourcecode to non-licensees has been granted under the following license.
////////////////////////////////////////////////////////////////////////////////////////////////////////
// COPYRIGHTS:
// Copyright (c)2002-2013 Solutions Design. All rights reserved.
// http://www.llblgen.com
// 
// The ORM Support classes library sourcecode is released to LLBLGen Pro licensees under the 
// following license:
// --------------------------------------------------------------------------------------------
// 
// Redistribution and use of the sourcecode in compiled, binary forms, with or without modification, 
// are permitted provided that the following conditions are met: 
//
// 1) Redistributions must reproduce the above copyright notice, this list of 
//  conditions and the following disclaimer in the documentation and/or other materials 
//  provided with the distribution. 
// 2) Redistribution of the sourcecode in textual, non-binary, non-compiled form is prohibited.
// 
// THIS SOFTWARE IS PROVIDED BY SOLUTIONS DESIGN ``AS IS'' AND ANY EXPRESS OR IMPLIED WARRANTIES, 
// INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A 
// PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL SOLUTIONS DESIGN OR CONTRIBUTORS BE LIABLE FOR 
// ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT 
// NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR 
// BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, 
// STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE 
// USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. 
//
// The views and conclusions contained in the software and documentation are those of the authors 
// and should not be interpreted as representing official policies, either expressed or implied, 
// of Solutions Design. 

//////////////////////////////////////////////////////////////////////
// Contributers to the code:
//      - Frans Bouma
//////////////////////////////////////////////////////////////////////
//
// Part of this code is based on http://www.getcodesamples.com/src/A122E27E/5586EA7F
// which is part of the EntLib v5 or higher. The above mentioned code is used to
// determine which errors are transient exceptions.
//
//////////////////////////////////////////////////////////////////////
using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Reflection;
using System.Text;

namespace SD.LLBLGen.Pro.ORMSupportClasses
{
    /// <summary>
    /// Specific strategy to be used with SQL Azure and also usable for local SQL Server connections. 
    /// </summary>
    public class SqlAzureRecoveryStrategy : RecoveryStrategyBase
    {
        /// <summary>
        /// Initializes a new instance of the <see cref="SqlAzureRecoveryStrategy"/> class.
        /// </summary>
        public SqlAzureRecoveryStrategy() : base()
        {
        }

        /// <summary>
        /// Initializes a new instance of the <see cref="SqlAzureRecoveryStrategy"/> class.
        /// </summary>
        /// <param name="maximumNumberOfRetries">The maximum number of retries.</param>
        /// <param name="delayCalculator">The delay calculator.</param>
        public SqlAzureRecoveryStrategy(int maximumNumberOfRetries, RecoveryDelay delayCalculator) : base(maximumNumberOfRetries, delayCalculator)
        {
        }


        /// <summary>
        /// Determines whether the specified exception is a transient exception.
        /// </summary>
        /// <param name="toCheck">The exception to check.</param>
        /// <returns>
        /// true if the exception is a transient exception and can be retried, false otherwise. The empty implementation
        /// returns false.
        /// </returns>
        protected override bool IsTransientException(Exception toCheck)
        {
            if(toCheck is null)
            {
                return false;
            }
            if(toCheck is TimeoutException)
            {
                return true;
            }

            // We'll use reflection here, as otherwise we have to take a dependency on SqlClient on .NET Standard. The reflection is less ideal, but as we're already
            // in a state where an exception has been thrown, a couple more cycles aren't noticable.  
            
            string typeName = toCheck.GetType().Name;
            if(typeName != "SqlException")
            {
                return false;
            }
            // traverse all errors in the errors collection, as it might be the transient error is burried under another error.
            var errorsProperty = toCheck.GetType().GetProperty("Errors");
            if(errorsProperty is null)
            {
                return false;
            }

            var errors = errorsProperty.GetValue(toCheck) as IEnumerable;
            if(errors == null)
            {
                return false;
            }

            PropertyInfo numberProperty = null;
            foreach(object error in errors)
            {
                if(numberProperty is null)
                {
                    numberProperty = error.GetType().GetProperty("Number");
                }

                if(numberProperty is null)
                {
                    return false;
                }

                var errorNumber = numberProperty.GetValue(error);
                switch(Convert.ToInt32(errorNumber))
                {
                    // Time out. 
                    case -2:
                    // DBNETLIB Error Code: 20
                    // The instance of SQL Server you attempted to connect to does not support encryption.
                    case 20:
                    // SQL Error Code: 64
                    // A connection was successfully established with the server, but then an error occurred during the login process. 
                    // (provider: TCP Provider, error: 0 - The specified network name is no longer available.) 
                    case 64:
                    // SQL Error Code: 233
                    // The client was unable to establish a connection because of an error during connection initialization process before login. 
                    // Possible causes include the following: the client tried to connect to an unsupported version of SQL Server; the server was too busy 
                    // to accept new connections; or there was a resource limitation (insufficient memory or maximum allowed connections) on the server. 
                    // (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)
                    case 233:
                    // SQL Error Code: 1205
                    // Dead lock situation. 
                    case 1205:
                    // SQL Error Code: 10053
                    // A transport-level error has occurred when receiving results from the server.
                    // An established connection was aborted by the software in your host machine.
                    case 10053:
                    // SQL Error Code: 10054
                    // A transport-level error has occurred when sending the request to the server. 
                    // (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)
                    case 10054:
                    // SQL Error Code: 10060
                    // A network-related or instance-specific error occurred while establishing a connection to SQL Server. 
                    // The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server 
                    // is configured to allow remote connections. (provider: TCP Provider, error: 0 - A connection attempt failed 
                    // because the connected party did not properly respond after a period of time, or established connection failed 
                    // because connected host has failed to respond.)"}
                    case 10060:
                    // SQL Error Code: 10928
                    // Resource ID: %d. The %s limit for the database is %d and has been reached. For more information,
                    case 10928:
                    // SQL Error Code: 10929
                    // Resource ID: %d. The %s minimum guarantee is %d, maximum limit is %d and the current usage for the database is %d.
                    // However, the server is currently too busy to support requests greater than %d for this database.
                    case 10929:
                    // SQL Error Code: 40197
                    // The service has encountered an error processing your request. Please try again.
                    case 40197:
                    // SQL Error Code: 40143
                    // The service has encountered an error processing your request. Please try again.
                    case 40143:
                    // SQL Error Code: 40501
                    // The service is currently busy. Retry the request after 10 seconds.
                    case 40501:
                    // SQL Error Code: 40613
                    // Database XXXX on server YYYY is not currently available. Please retry the connection later. If the problem persists, contact customer 
                    // support, and provide them the session tracing ID of ZZZZZ.
                    case 40613:
                        return true;
                }
            }
            return false;
        }
    }
}