0

I'm using MySQL server (5.6) database extensively through its .NET connector (6.8.3). All tables are created with MyISAM engine for performance reasons. I have only one process with one thread accessing the DB sequentially, so there is no need for transactions and concurrency.

Please note: this is unlikely to be a timeout configuration issue, because my query is trivial. I did read all timeout related questions (links below). Of course, I may be wrong, but saying just "increase the net_write_timeout parameter" without explaining why exactly it can be relevant here is not an answer.

Some of my tables are created dynamically during program execution so I'm using create on first use idiom, with the following method to check whether the table exists:

private bool TableExists(Space baseSpace, Space extendedSpace)
{
    var tableName = GenerateTableName(baseSpace, extendedSpace);

    var sqlConnection = this.connectionPool.Take();

    this.existsCommand.Connection = sqlConnection;
    this.existsCommand.Parameters["@tableName"].Value = tableName.Trim('`');

    var result = existsCommand.ExecuteScalar() as long?;

    this.connectionPool.Putback(sqlConnection);

    return result == 1;
}

The query inside the existsCommand is as follows (broken into two lines here for readability):

SELECT COUNT(*) FROM information_schema.tables 
WHERE table_schema = 'my_schema' AND table_name = @tableName

The value of this.existsCommand.Parameters["@tableName"].Value variable contains the correct name of the table, that already exists in this case ("sample_matches_A_to_A_x").

The this.connectionPool.Take() method returns the first MySqlConnection object from my collection of available connections that meets the following predicate:

private bool IsAvailable(MySqlConnection connection)
{
    return connection != null
        && connection.State == System.Data.ConnectionState.Open;
}

Usually this works correctly, for a number of hours and then suddenly an exception occurs on this line:

var result = existsCommand.ExecuteScalar() as long?;

With the following contents:

Fatal error encountered during command execution

Stack trace:

at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)

at MySql.Data.MySqlClient.MySqlCommand.ExecuteScalar()

at implementation.SampleMatchesMySqlTable.TableExists(Space baseSpace, Space extendedSpace) in C:...\SampleMatchesMySqlTable.cs:line 168

Inner exception:

Unable to write data to the transport connection: Software caused connection abort.

Inner exception:

Software caused connection abort.

with ErrorCode equal to 10053 and SocketErrorCode being System.Net.Sockets.SocketError.ConnectionAborted

Stack trace of the innermost exception:

at System.Net.Sockets.Socket.Send(Byte[] buffer, Int32 offset, Int32 size, SocketFlags socketFlags)

at System.Net.Sockets.NetworkStream.Write(Byte[] buffer, Int32 offset, Int32 size)

I've reviewed the following possible duplicates and unfortunately they don't seem relevant here, except one:

SHOW TABLES encountered Fatal error encountered during command execution

Unfortunately, it doesn't provide enough information to decide whether it is an exact duplicate nor it's answered.

There relate to timeout problems. My query is small, returns only one integer, and searches for a table in a meta-table containing something like 20 other tables.

Fatal error encountered during command execution. in C# when i use Insert Into

https://stackoverflow.com/questions/7895178/fatal-error-in-mysql

MySQL Exception - Fatal Error Encountered During Data Read

Fatal error encountered during data read

Fatal error causing timeout -mysql

ADO.Net Entity framework, Fatal error encountered during data read

Different error regarding reading the resultset:

Fatal error encountered during command execution

Syntax errors and connection string issues:

Mysql Fatal error encountered during command execution

fatal error encountered during execution... during update

Fatal error encountered during command execution

fatal error encountered during command execution during update

fatal error encountered during command execution c# mysql

Fatal error encountered during command execution with a mySQL INSERT

I have Fatal error encountered during command execution

MySql exception was unhandled - Fatal error encountered during command execution

https://stackoverflow.com/questions/24098561/mysql-fatal-error-encountered-during-command-execution-in-c-sharp

Fatal Error Encounter During Command Execution MySQL VB

"Fatal error encountered during command execution." mysql-connector .net

"Fatal error encountered during command execution."

C#, MySQL - fatal error encountered during command execution- Checked other solutions, something I am Missing

Creating a view: "fatal error encountered during command execution" when trying to add a view from MySQL DB

Reading CSV files:

Fatal error encountered during command execution while importing from csv to mysql

MySQL fatal error encountered during command execution - looping through all csv files in folder for load data local infile

Community
  • 1
  • 1
BartoszKP
  • 34,786
  • 15
  • 102
  • 130

1 Answers1

0

I ended up modifying the IsAvailable method as follows:

private bool IsAvailable(MySqlConnection connection)
{
    var result = false;

    try
    {
        if (connection != null)
        {
            result = connection.Ping();
        }
    }
    catch (Exception e)
    {
        Console.WriteLine("Ping exception: " + e.Message);
    }

    return result && connection.State == System.Data.ConnectionState.Open;
}

The .Ping call seems to be better in verifying connection's state then its properties, although it's very poorly documented. From some other sources I've read in the meantime:

an alternative is to run a simple query like SELECT 1 FROM DUAL instead of calling Ping.

Also, there seems that sometimes a DataReader, even though it was disposed (everything is implemented with the using statement), is still attached to the connection for some brief period of time. Because of this I've modified the condition to this:

if (connection != null && connection.State == ConnectionState.Open)

The State property has the Executing value as long as a DataReader is attached to it, so additionally verifying if it's Open at the beginning works fine here.

BartoszKP
  • 34,786
  • 15
  • 102
  • 130