Bug #88519 MySQL .Net/Connector may return wrong resultset after using Thread.Abort
Submitted: 16 Nov 2017 10:43 Modified: 30 Nov 2017 6:29
Reporter: Joerg Plenert Email Updates:
Status: Verified Impact on me:
None 
Category:Connector / NET Severity:S2 (Serious)
Version:V6.9.10 OS:Microsoft Windows
Assigned to: CPU Architecture:Any

[16 Nov 2017 10:43] Joerg Plenert
Description:
In an environment of massive multi threading, where query threads are aborted and database connection are reused, it may happen, that a query returns the result of another (before aborted?) query.

Exception in that case will be something like that:

System.ArgumentException: Column 'TelType' does not belong to table .
   at System.Data.DataRow.GetDataColumn(String columnName)
   at System.Data.DataRow.get_Item(String columnName)
   at MySQLAbortTest.SqlAbortTest_Thread.DoWork() in d:\prj\MySQLAbortTest\Program.cs:line 81

The field must be in the result, but the returned result belongs to another query that did not had that field.

Other possible exceptions in that case are:
System.ArgumentException: Thread was being aborted.Couldn't store <19.11.2014 08:43:24> in ReceiveTS Column.  Expected type is DateTime. ---> System.Threading.ThreadAbortException: Thread was being aborted.
   at System.Data.Common.DateTimeStorage.Set(Int32 record, Object value)
   at System.Data.DataColumn.set_Item(Int32 record, Object value)
   --- End of inner exception stack trace ---
   at System.Data.DataColumn.set_Item(Int32 record, Object value)
   at System.Data.DataTable.NewRecordFromArray(Object[] value)
   at System.Data.DataTable.LoadDataRow(Object[] values, Boolean fAcceptChanges)
   at System.Data.ProviderBase.SchemaMapping.LoadDataRow()
   at System.Data.Common.DataAdapter.FillLoadDataRow(SchemaMapping mapping)
   at System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue)
   at System.Data.Common.DataAdapter.Fill(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
   at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
   at MySQLAbortTest.SqlAbortTest_Thread.DoWork() in d:\prj\MySQLAbortTest\Program.cs:line 77

Same behaviour with Connector 6.9.5.

How to repeat:
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading;
using System.Threading.Tasks;

namespace MySQLAbortTest
{
	class SqlAbortTest_Query
	{
		public string Query { get; set; }
		public string Field { get; set; }

		public SqlAbortTest_Query(string query, string field)
		{
			Query = query;
			Field = field;
		}
	}

	class SqlAbortTest_Thread
	{
		MySqlConnection con;
		Thread thread;
		object conLock;
		object abortLock;
		SqlAbortTest_Query nextQuery;
		public bool State { get; protected set; }
		public long AbortAt { get; protected set; }

		public SqlAbortTest_Thread()
		{
			string connStr = String.Format("server={0};user={1};database={2};port={3};password={4};", "localhost", "root", "<DATABASE>", 3306, "<PASSWORD>");
			con = new MySqlConnection(connStr);
			con.Open();

			conLock = new object();
			abortLock = new object();
		}

		public void Start(SqlAbortTest_Query nextQuery, long abortAt)
		{
			AbortAt = abortAt;
			this.nextQuery = nextQuery;
			thread = new Thread(new ThreadStart(DoWork));
			thread.Start();
			State = true;
		}

		public void Abort()
		{
			// Just lock to be sure that Exception is written to disk
			lock (abortLock)
			{
				if (thread.ThreadState != System.Threading.ThreadState.Stopped)
					thread.Abort();
				State = false;
			}
		}

		void DoWork()
		{
			try
			{
				StringBuilder sb = new StringBuilder();
				DataTable table = new DataTable();

				MySqlCommand cmd = new MySqlCommand(nextQuery.Query, con);
				MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
				lock (conLock)
				{
					adapter.Fill(table);
				}
			
				foreach (DataRow row in table.Rows)
					sb.Append(row[nextQuery.Field].ToString());
			}
			catch (ThreadAbortException) { }
			catch (Exception ex)
			{
				lock (abortLock)
				{
					using (StreamWriter writer = File.AppendText("exception.log"))
					{
						writer.WriteLine(ex.ToString());
					}
				}
			}
		}
	}

	class SqlAbortTest
	{
		List<SqlAbortTest_Query> sqlAbortTests = new List<SqlAbortTest_Query>()
		{
			<MULTIPLE SELECTS ON MULTIPLE BIG TABLES>
			new SqlAbortTest_Query("select * from joint where ... ", "<Field>"),
			new SqlAbortTest_Query("select * from joint where ... ", "<Field>"),
			new SqlAbortTest_Query("select * from joint where ... ", "<Field>"),
			new SqlAbortTest_Query("select * from joint where ... ", "<Field>"),
		};

		List<SqlAbortTest_Thread> threadList;

		public SqlAbortTest()
		{
			threadList = new List<SqlAbortTest_Thread>();
			for (int i = 0; i < 20; i++)
				threadList.Add(new SqlAbortTest_Thread());
		}

		public void DoTest()
		{
			Random random = new Random();

			while (true)
			{
				// Start & Abort new threads
				foreach (var th in threadList)
				{
					if (!th.State)
						th.Start(sqlAbortTests[(int)Math.Round(random.NextDouble() * (sqlAbortTests.Count - 1), 0)], Environment.TickCount + (int)random.NextDouble() * 5);
					else if (Environment.TickCount > th.AbortAt)
						th.Abort();
				}
			}
		}
	}

	class Program
	{
		static void Main(string[] args)
		{
			SqlAbortTest at = new SqlAbortTest();
			at.DoTest();
		}
	}
}
[16 Nov 2017 19:42] Bradley Grainger
Using Thread.Abort is generally considered a bad practice in .NET: https://stackoverflow.com/a/1560567 https://stackoverflow.com/a/6384859

I would recommend exposing the active MySqlCommand (in use by each thread) and calling command.Cancel (from another thread) to safely interrupt the database work being done.
[16 Nov 2017 22:33] Joerg Plenert
Bradley you are completly right - it's bad practise.

But it's supported by the .Net framework as well as the connector:
- ASP.NET ist heavily using Thread.Abort by design.
- There where changes in the 6.9.9 version of the connector that should even improve Abort behaviour. 

If it's implemented, it should work....
[30 Nov 2017 6:29] Chiranjeevi Battula
Hello Joerg Plenert,

Thank you for the bug report.
Verified based on internal discussion with dev's.

Thanks,
Chiranjeevi.