Description:
If a connection in a transaction experiences a connection timeout exception then the connection still exists and holds the locks. This has caused severe production outages after we upgraded our MySQL connector to version 8.0.31.
The problem does not exist in version 8.0.30. I believe this bug is a result of the fix to MySQL bug 107110. The commit for which can be viewed at https://github.com/mysql/mysql-connector-net/commit/e72ba5d96bc3da1f04564eb0b7821d2959c560...
How to repeat:
The steps to repeat require two separate programs. The source code to the programs is below.
STEPS TO REPRODUCE:
-------------------
1. Modify the connection strings to suit your environment and compile Program1 and Program2.
2. Run Program1. It should display the text:
Program1: Transaction in progress: press Enter to rollback
3. Run Program2. It should display the text:
Press Enter to start transaction
4. Press enter for Program2. After 5 seconds it will display an error message similar to the following:
Program2: Exception:
Fatal error encountered during command execution.
Fatal error encountered attempting to read the resultset.
Reading from the stream has failed.
Unable to read data from the transport connection: ...
A connection attempt failed because the connected party did not ...
Program2: Press Enter to start transaction
5. Stop Program1 (type Ctrl-C).
6. Press enter again for Program2. The expected result is that Program2 would print:
Program2: Transaction committed
Instead, it prints the exception message in step 4 above.
7. Downgrade the MySQL connect to version 8.0.30. Repeat steps 1 to 6 above, and you will have the expected result.
PROGRAM 1:
----------
using MySql.Data.MySqlClient;
namespace Program1;
// Demonstrates a bug that was introduced fixing MySQL bug 107110 (https://bugs.mysql.com/bug.php?id=107110)
// Commit https://github.com/mysql/mysql-connector-net/commit/e72ba5d96bc3da1f04564eb0b7821d2959c560...
class Program
{
static void Main(string[] args)
{
string connectionString = "server=127.0.0.1;User Id=scott;password=tiger;database=test_db;";
SetupData(connectionString);
using (var connection = new MySqlConnection(connectionString))
{
connection.Open();
using (var transaction = connection.BeginTransaction())
{
using var command = connection.CreateCommand();
command.CommandText = "select * from tmp_mysql_connector_testing where id = 1 for update";
command.ExecuteNonQuery();
Console.WriteLine("Program1: Transaction in progress: press Enter to rollback");
Console.ReadLine();
}
Console.WriteLine("Program1: Transaction rolled back");
}
}
static void SetupData(string connectionString)
{
using (var connection = new MySqlConnection(connectionString))
{
connection.Open();
using var command = connection.CreateCommand();
command.CommandText = "create table if not exists tmp_mysql_connector_testing(id int not null primary key, val int)";
command.ExecuteNonQuery();
command.CommandText = "select count(*) from tmp_mysql_connector_testing";
var rowCount = Convert.ToInt32(command.ExecuteScalar());
if (rowCount == 0)
{
command.CommandText = "insert into tmp_mysql_connector_testing(id, val) values(1, 1)";
command.ExecuteNonQuery();
}
}
}
}
PROGRAM 2:
----------
using System.Text;
using MySql.Data.MySqlClient;
namespace Program2;
class Program
{
static void Main(string[] args)
{
string connectionString = "server=127.0.0.1;User Id=scott;password=tiger;database=test_db;";
for (;;)
{
Console.WriteLine("Program2: Press Enter to start transaction");
Console.ReadLine();
try
{
using (var connection = new MySqlConnection(connectionString))
{
connection.Open();
using (var transaction = connection.BeginTransaction())
{
using var command = connection.CreateCommand();
command.CommandText = "update tmp_mysql_connector_testing set val = val + 1 where id = 1";
command.CommandTimeout = 5;
command.ExecuteNonQuery();
transaction.Commit();
}
Console.WriteLine("Program2: Transaction committed");
}
}
catch (Exception ex)
{
var stringBuilder = new StringBuilder();
stringBuilder.AppendLine("Program2: Exception:");
while (ex != null)
{
stringBuilder.AppendLine(" " + ex.Message);
ex = ex.InnerException;
}
Console.WriteLine(stringBuilder.ToString());
}
}
}
}
Suggested fix:
I have looked at the change in commit e72ba5d96bc3da1f04564eb0b7821d2959c560dd and I do not completely understand why a connection is cloned.
I am not familiar with ambient transactions, but I suspect that this fix works for Ambient transactions, but not explicit transactions. I cannot be 100% sure of this, however.
