MySql Entity Framework - Connect Timeout expired

1

I am using Google Cloud SQL with MySql v5.7 from C# .NET-core 2.2 and entity framework 6 application.

In my logs I can see the following exception from multiple locations in the code that I use the database from:

MySql.Data.MySqlClient.MySqlException (0x80004005): Connect Timeout expired. ---> System.OperationCanceledException: The operation was canceled.
   at System.Threading.CancellationToken.ThrowOperationCanceledException()
   at System.Threading.SemaphoreSlim.WaitUntilCountOrTimeoutAsync(TaskNode asyncWaiter, Int32 millisecondsTimeout, CancellationToken cancellationToken)
   at MySqlConnector.Core.ConnectionPool.GetSessionAsync(MySqlConnection connection, IOBehavior ioBehavior, CancellationToken cancellationToken) in C:\projects\mysqlconnector\src\MySqlConnector\Core\ConnectionPool.cs:line 42
   at MySql.Data.MySqlClient.MySqlConnection.CreateSessionAsync(Nullable`1 ioBehavior, CancellationToken cancellationToken) in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlConnection.cs:line 507
   at MySql.Data.MySqlClient.MySqlConnection.CreateSessionAsync(Nullable`1 ioBehavior, CancellationToken cancellationToken) in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlConnection.cs:line 523
   at MySql.Data.MySqlClient.MySqlConnection.OpenAsync(Nullable`1 ioBehavior, CancellationToken cancellationToken) in C:\projects\mysqlconnector\src\MySqlConnector\MySql.Data.MySqlClient\MySqlConnection.cs:line 232
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenDbConnectionAsync(Boolean errorsExpected, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenAsync(CancellationToken cancellationToken, Boolean errorsExpected)
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteAsync(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary`2 parameterValues, CancellationToken cancellationToken)

This happens temporarily for split second when there is a some load on the database(not very high, about 20% cpu of the database machine).

Configuring The Context:

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    if (!optionsBuilder.IsConfigured)
    {
        optionsBuilder.UseMySql(
            new System.Net.NetworkCredential(string.Empty, ConfigurationManager.CacheCS).Password, builder =>
            {
                builder.EnableRetryOnFailure(15, TimeSpan.FromSeconds(30), null);
            }
            );
    }
}

This sets up to 15 retries and maximum of 30 seconds between retries.

It seems from the log that the MySqlConnector does not retry on this specific error.

My Tries

Tried adding transient error numbers to the list of error numbers to add:

builder.EnableRetryOnFailure(15, TimeSpan.FromSeconds(30), MySqlErrorCodes.TransientErrors);

where MySqlErrorCodes.TransientErrors is defined as:

 public enum MySqlErrorCode
    {
        // Too many connections
        ConnectionCountError = 1040,
        // Unable to open connection
        UnableToConnectToHost = 1042,
        // Lock wait timeout exceeded; try restarting transaction
        LockWaitTimeout = 1205,
        // Deadlock found when trying to get lock; try restarting transaction
        LockDeadlock = 1213,
        // Transaction branch was rolled back: deadlock was detected
        XARBDeadlock = 1614

    }

    public class MySqlErrorCodes
    {
        static MySqlErrorCodes()
        {
            TransientErrors = new HashSet<int>()
            {
                (int)MySqlErrorCode.ConnectionCountError,
                (int)MySqlErrorCode.UnableToConnectToHost,
                (int)MySqlErrorCode.LockWaitTimeout,
                (int)MySqlErrorCode.LockDeadlock,
                (int)MySqlErrorCode.XARBDeadlock
            };
        }

        public static HashSet<int> TransientErrors { get; private set; }
    }

This didn't work.

Questions

How can I solve this issue? Is there a way to make Entity Framework more resilient to such connectivity issues?

Edit

The issue occurs when I use this code to execute a raw sql command to call a stored procedure:

public static async Task<RelationalDataReader> ExecuteSqlQueryAsync(this DatabaseFacade databaseFacade,
                                                     string sql,
                                                     CancellationToken cancellationToken = default(CancellationToken),
                                                     params object[] parameters)
{

    var concurrencyDetector = databaseFacade.GetService<IConcurrencyDetector>();

    using (concurrencyDetector.EnterCriticalSection())
    {
        var rawSqlCommand = databaseFacade
            .GetService<IRawSqlCommandBuilder>()
            .Build(sql, parameters);

        return await rawSqlCommand
            .RelationalCommand
            .ExecuteReaderAsync(
                databaseFacade.GetService<IRelationalConnection>(),
                parameterValues: rawSqlCommand.ParameterValues,
                cancellationToken: cancellationToken);
    }
}

...

using (var context = new CacheDbContext())
            {
                using (var reader = await context
                    .Database
                    .ExecuteSqlQueryAsync("CALL Counter_increment2(@p0, @p1, @p2)",
                        default(CancellationToken),
                        new object[] { id, counterType, value })
                    .ConfigureAwait(false)
                    )
                {
                    reader.DbDataReader.Read();
                    if (!(reader.DbDataReader[0] is DBNull))
                        return Convert.ToInt32(reader.DbDataReader[0]);
                    else
                    {
                        Logger.Error($"Counter was not found! ('{id}, '{counterType}')");
                        return 1;
                    }
                }
            }

I think this may be why there are no retries for the connect timeout.

How can I retry this safely while not executing the same stored procedure twice?

Edit

These are the global variables:

SHOW GLOBAL VARIABLES LIKE '%timeout%'

connect_timeout 10
delayed_insert_timeout  300
have_statement_timeout  YES
innodb_flush_log_at_timeout 1
innodb_lock_wait_timeout    50
innodb_rollback_on_timeout  OFF
interactive_timeout 28800
lock_wait_timeout   31536000
net_read_timeout    30
net_write_timeout   60
rpl_semi_sync_master_async_notify_timeout   5000000
rpl_semi_sync_master_timeout    3000
rpl_stop_slave_timeout  31536000
slave_net_timeout   30
wait_timeout    28800

SHOW GLOBAL STATUS LIKE '%timeout%'

Ssl_default_timeout 7200
Ssl_session_cache_timeouts  0

SHOW GLOBAL STATUS LIKE '%uptime%'

Uptime  103415
Uptime_since_flush_status   103415

In addition to the connect time out issue I am also seeing the following log:

MySql.Data.MySqlClient.MySqlException (0x80004005): MySQL Server rejected client certificate ---> System.IO.IOException: Unable to read data from the transport connection: Broken pipe. ---> System.Net.Sockets.SocketException: Broken pipe

Which seems to be a related issue regarding the connection to the database.

Is it safe to retry on such exception?

c#
mysql
entity-framework
.net-core
google-cloud-sql
asked on Stack Overflow Dec 2, 2020 by Montoya • edited Dec 7, 2020 by Montoya

0 Answers

Nobody has answered this question yet.


User contributions licensed under CC BY-SA 3.0