Description:
When a query times out, Connector opens a new connection to kill the query.
Starting with version 8.0.31 this fails.
How to repeat:
Example program:
----
using System;
using System.Data;
using MySql.Data;
using MySql.Data.MySqlClient;
using System.Diagnostics;
using System.Collections;
using System.Collections.Generic;
namespace z
{
class Program
{
const string connectionString = "SERVER=localhost;UID=sami;password=Ab12_6543!;database=test;logging=true;";
static void Main(string[] args)
{
Console.WriteLine("Hello World?");
var mySqlAssembly = typeof(MySqlConnection).Assembly;
Console.WriteLine(mySqlAssembly.GetName());
// Configure Logging
MySql.Data.MySqlClient.MySqlTrace.Switch.Level = SourceLevels.All;
MySql.Data.MySqlClient.MySqlTrace.Listeners.Add(new TextWriterTraceListener(Console.Out));
// Try the test
Console.WriteLine("Running Non-Query version");
RunNonQuery();
// Wait before ending the application and closing the pool.
Console.WriteLine("Press Enter to close the application and the Connection Pool");
Console.ReadLine();
}
static void LogException(Exception ex)
{
var messages = new List<string>();
var exception = ex;
do
{
messages.Add(exception.GetType().Name + ": " + exception.Message);
exception = exception.InnerException;
} while (exception != null && exception is MySqlException);
Console.WriteLine();
Console.WriteLine(String.Join("\r\n\t", messages));
}
/// <summary>Run an ExecuteNonQuery </summary>
static void RunNonQuery()
{
const string query = @"
INSERT INTO t1 values(42);
SELECT SLEEP(20);";
using (var connection = new MySql.Data.MySqlClient.MySqlConnection(connectionString))
{
connection.Open();
using (var transaction = connection.BeginTransaction())
{
var command = new MySql.Data.MySqlClient.MySqlCommand(query, connection);
command.Transaction = transaction;
command.CommandTimeout = 5;
try
{
command.ExecuteNonQuery();
}
catch (Exception ex)
{
LogException(ex);
}
}
}
}
}
}
----
Running the program with Connector version 8.0.30:
----
[root@sami-ahlroos-default b]# dotnet run
Hello World?
MySql.Data, Version=8.0.30.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d
Running Non-Query version
mysql Information: 1 : 1: Connection Opened: connection string = 'server=localhost;user id=sami;password=Ab12_6543!;database=test;logging=True'
mysql Information: 3 : 1: Query Opened: SELECT @@max_allowed_packet, @@character_set_client,
@@character_set_connection, @@license, @@sql_mode, @@lower_case_table_names, @@autocommit
mysql Information: 4 : 1: Resultset Opened: field(s) = 7, affected rows = -1, inserted id = -1
mysql Information: 5 : 1: Resultset Closed. Total rows=1, skipped rows=0, size (bytes)=164
mysql Information: 6 : 1: Query Closed
mysql Information: 3 : 1: Query Opened: SELECT TIMEDIFF(NOW(), UTC_TIMESTAMP())
mysql Information: 4 : 1: Resultset Opened: field(s) = 1, affected rows = -1, inserted id = -1
mysql Information: 5 : 1: Resultset Closed. Total rows=1, skipped rows=0, size (bytes)=9
mysql Information: 6 : 1: Query Closed
mysql Information: 3 : 1: Query Opened: SHOW COLLATION
mysql Information: 4 : 1: Resultset Opened: field(s) = 6, affected rows = -1, inserted id = -1
mysql Information: 5 : 1: Resultset Closed. Total rows=222, skipped rows=0, size (bytes)=7487
mysql Information: 6 : 1: Query Closed
mysql Information: 3 : 1: Query Opened: SET NAMES latin1
mysql Information: 4 : 1: Resultset Opened: field(s) = 0, affected rows = 0, inserted id = 0
mysql Information: 5 : 1: Resultset Closed. Total rows=0, skipped rows=0, size (bytes)=0
mysql Information: 6 : 1: Query Closed
mysql Information: 3 : 1: Query Opened: SET character_set_results=NULL
mysql Information: 4 : 1: Resultset Opened: field(s) = 0, affected rows = 0, inserted id = 0
mysql Information: 5 : 1: Resultset Closed. Total rows=0, skipped rows=0, size (bytes)=0
mysql Information: 6 : 1: Query Closed
mysql Information: 10 : 1: Set Database: test
mysql Information: 3 : 1: Query Opened: SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
mysql Information: 4 : 1: Resultset Opened: field(s) = 0, affected rows = 0, inserted id = 0
mysql Information: 5 : 1: Resultset Closed. Total rows=0, skipped rows=0, size (bytes)=0
mysql Information: 6 : 1: Query Closed
mysql Information: 3 : 1: Query Opened: BEGIN
mysql Information: 4 : 1: Resultset Opened: field(s) = 0, affected rows = 0, inserted id = 0
mysql Information: 5 : 1: Resultset Closed. Total rows=0, skipped rows=0, size (bytes)=0
mysql Information: 6 : 1: Query Closed
mysql Information: 3 : 1: Query Opened: INSERT INTO t1 values(42);
SELECT SLEEP(20)
mysql Information: 4 : 1: Resultset Opened: field(s) = 0, affected rows = 1, inserted id = 0
mysql Information: 5 : 1: Resultset Closed. Total rows=0, skipped rows=0, size (bytes)=0
mysql Information: 13 : 1: Error encountered attempting to open result: Number=0, Message=Reading from the stream has failed.
mysql Information: 2 : 1: Connection Closed
MySqlException: Fatal error encountered during command execution.
MySqlException: Fatal error encountered attempting to read the resultset.
MySqlException: Reading from the stream has failed.
Press Enter to close the application and the Connection Pool
----
And starting with 8.0.31:
----
[root@sami-ahlroos-default b]# dotnet run
Hello World?
MySql.Data, Version=8.0.31.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d
Running Non-Query version
mysql Information: 1 : 1: Connection Opened: connection string = 'server=localhost;user id=sami;password=Ab12_6543!;database=test;logging=True'
mysql Information: 3 : 1: Query Opened: SELECT @@max_allowed_packet, @@character_set_client,
@@character_set_connection, @@license, @@sql_mode, @@lower_case_table_names, @@autocommit
mysql Information: 4 : 1: Resultset Opened: field(s) = 7, affected rows = -1, inserted id = -1
mysql Information: 5 : 1: Resultset Closed. Total rows=1, skipped rows=0, size (bytes)=164
mysql Information: 6 : 1: Query Closed
mysql Information: 3 : 1: Query Opened: SELECT TIMEDIFF(NOW(), UTC_TIMESTAMP())
mysql Information: 4 : 1: Resultset Opened: field(s) = 1, affected rows = -1, inserted id = -1
mysql Information: 5 : 1: Resultset Closed. Total rows=1, skipped rows=0, size (bytes)=9
mysql Information: 6 : 1: Query Closed
mysql Information: 3 : 1: Query Opened: SHOW COLLATION
mysql Information: 4 : 1: Resultset Opened: field(s) = 6, affected rows = -1, inserted id = -1
mysql Information: 5 : 1: Resultset Closed. Total rows=222, skipped rows=0, size (bytes)=7487
mysql Information: 6 : 1: Query Closed
mysql Information: 3 : 1: Query Opened: SET NAMES latin1
mysql Information: 4 : 1: Resultset Opened: field(s) = 0, affected rows = 0, inserted id = 0
mysql Information: 5 : 1: Resultset Closed. Total rows=0, skipped rows=0, size (bytes)=0
mysql Information: 6 : 1: Query Closed
mysql Information: 3 : 1: Query Opened: SET character_set_results=NULL
mysql Information: 4 : 1: Resultset Opened: field(s) = 0, affected rows = 0, inserted id = 0
mysql Information: 5 : 1: Resultset Closed. Total rows=0, skipped rows=0, size (bytes)=0
mysql Information: 6 : 1: Query Closed
mysql Information: 10 : 1: Set Database: test
mysql Information: 3 : 1: Query Opened: SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
mysql Information: 4 : 1: Resultset Opened: field(s) = 0, affected rows = 0, inserted id = 0
mysql Information: 5 : 1: Resultset Closed. Total rows=0, skipped rows=0, size (bytes)=0
mysql Information: 6 : 1: Query Closed
mysql Information: 3 : 1: Query Opened: BEGIN
mysql Information: 4 : 1: Resultset Opened: field(s) = 0, affected rows = 0, inserted id = 0
mysql Information: 5 : 1: Resultset Closed. Total rows=0, skipped rows=0, size (bytes)=0
mysql Information: 6 : 1: Query Closed
mysql Information: 3 : 1: Query Opened: INSERT INTO t1 values(42);
SELECT SLEEP(20)
mysql Information: 4 : 1: Resultset Opened: field(s) = 0, affected rows = 1, inserted id = 0
mysql Information: 5 : 1: Resultset Closed. Total rows=0, skipped rows=0, size (bytes)=0
mysql Information: 13 : 1: Error encountered attempting to open result: Number=0, Message=Reading from the stream has failed.
mysql Warning: 31 : Error occurred aborting the connection. Exception was: Authentication to host 'localhost' for user 'sami' using method 'mysql_native_password' failed with message: Access denied for user 'sami'@'localhost' (using password: NO)
mysql Information: 6 : 1: Query Closed
MySqlException: Fatal error encountered during command execution.
MySqlException: Fatal error encountered attempting to read the resultset.
MySqlException: Reading from the stream has failed.
Press Enter to close the application and the Connection Pool
---
Note the "using password: NO" at the end. Looks like the connector somehow forgets the password to use.
Suggested fix:
The connection for executing "KILL QUERY" should succeed.
I suspect the fix to https://bugs.mysql.com/bug.php?id=107110 is somehow related, but could not come up with any good explanation as to how.