[Mono-bugs] [Bug 73252][Blo] New - SqlCommand.ExecuteReader Method (CommandBehavior) exhausts connection pool

[bugzilla-daemon@bugzilla.ximian.com]

Please do not reply to this email- if you want to comment on the bug, go to the
URL shown below and enter your comments there.

Changed by mono@eyekahn.com.

http://bugzilla.ximian.com/show_bug.cgi?id=73252

--- shadow/73252	2005-03-02 19:49:29.000000000 -0500
+++ shadow/73252.tmp.9639	2005-03-02 19:49:29.000000000 -0500
@@ -0,0 +1,159 @@
+Bug#: 73252
+Product: Mono: Class Libraries
+Version: unspecified
+OS: SUSE 9.1
+OS Details: 
+Status: NEW   
+Resolution: 
+Severity: 
+Priority: Blocker
+Component: Sys.Data.SqlClient
+AssignedTo: rodrigo@novell.com                            
+ReportedBy: mono@eyekahn.com               
+QAContact: mono-bugs@ximian.com
+TargetMilestone: ---
+URL: 
+Cc: 
+Summary: SqlCommand.ExecuteReader Method (CommandBehavior) exhausts connection pool
+
+Description of Problem:
+
+When executing the SqlCommand.ExecuteReader Method (CommandBehavior) 
+overload, after 100 ExecuteReader() methods are called ( the default 
+connection pool size ), the pool is exhausted. In a single threaded app 
+(like a console app ) this simply hangs the application entirely. No 
+exceptions or errors are thrown, it just hanges.
+
+If executing via a multi-threaded app, such as an asp.net application, 
+the exhaustion of the connection pool causes the application to throw an 
+error. In the case of mod_mono and apache2, this will display an entry in 
+the apache error log like this:
+
+** (/usr/bin/mod-mono-server.exe:13508): WARNING **: : error looking up 
+event handle 0x4fc
+
+Every call to that pool (connection using the same connection string) 
+throws the following exception until the 
+
+Further exceptions thrown by mono show up like this:
+
+[Wed Dec 08 48:10:56 2004] [error] [asp.net MONO] 
+System.NullReferenceException: Object reference not set to an instance of 
+an object
+in <0x001df> Mono.Data.Tds.Protocol.TdsConnectionPool:GetConnection ()
+in <0x002cc> System.Data.SqlClient.SqlConnection:Open ()
+in <0x0004f> (wrapper remoting-invoke-with-check) 
+System.Data.SqlClient.SqlConnection:Open ()
+in <0x00077> QC.Data.SqlServer.SqlHelper:ExecuteReader 
+(string,System.Data.CommandType,string,System.Data.SqlClient.SqlParameter
+[])
+
+This exception continues to be thrown until the application is killed. 
+Then once the app is restarted, it's only a matter of time before it 
+happens again.
+
+Steps to reproduce the problem:
+1. compile the following code into a console app exe
+
+using System;
+using System.Data.SqlClient;
+using System.Data;
+
+namespace ConnectionError.AuthorUI
+{
+//	using Authors.Components;
+//	using Authors.Manager;
+
+	class Class1
+	{
+		/// <summary>
+		/// The main entry point for the application.
+		/// </summary>
+		[STAThread]
+		static void Main(string[] args)
+		{
+			try
+			{
+				for(int i = 1; i <= 300; i++) 
+				{
+
+					string connectionString 
+= "server=localhost;uid=sa;pwd=;database=pubs";
+
+					//mono locks when max pool size 
+is hit, .net runs all 300 iterations
+					SqlConnection conn = new 
+SqlConnection(connectionString);
+					conn.Open();
+
+					SqlCommand cmd = new SqlCommand
+("SELECT au_Lname FROM Authors where au_ID = '722-51-5454'");
+					cmd.Connection = conn;
+					cmd.CommandType = 
+CommandType.Text;
+
+					// Workaround for issue is to not 
+pass the CommandBehavior to the ExecuteReader() call
+					// Replacing the using statement 
+with this one for demonstration of the workaround
+					//using(SqlDataReader reader = 
+cmd.ExecuteReader())
+					using(SqlDataReader reader = 
+cmd.ExecuteReader(CommandBehavior.CloseConnection))
+					{
+						while(reader.Read())
+						{
+							Console.WriteLine
+("Author Information: " + reader.GetString(0) + " " + i);
+						}
+					}
+					// uncomment the following line 
+for the workaround
+					//conn.Close();
+				}
+				Console.WriteLine("All done...");
+				Console.ReadLine();
+			}
+			catch(Exception ex)
+			{
+				Console.WriteLine(ex.Message);
+			}
+		}
+	}
+}
+2. Save the source to a file called connectionpool.cs
+3. Modify the connection string. This uses the pubs database that is 
+included with Sql Server
+4. compile the source with
+mcs -out:TestPool.exe connectionpooling.cs -r:System.Data
+5. Run mono TestPool.exe
+
+Actual Results:
+
+Application will hang after executing ExecuteReader() 100 times
+
+Expected Results:
+
+300 times through the ExecuteReader() without hanging
+
+
+How often does this happen? 
+
+Always
+
+Additional Information:
+
+I have seen this behavior in all versions of MONO I've used. 1.0.1., 
+1.0.2, 1.0.4, 1.0.5 and 1.1.4. I have seen it in 1.1.4 on both Suse 9.1 
+installed via RPMS and Windows installed via the combined installer 
+downloaded from Novell forge. I have seen this behavior regardless of 
+compilation with the MS.NET compiler or mcs. Regardless of which compiler 
+is used, it always runs on the MS.NET runtime as expected.
+
+There is a workaround, however it requires more code. It also means that 
+code that runs on MS.NET and should run on MONO without issues doesn't.
+
+The workaround is to explicitly open the connection initially and not 
+pass the ConnectionBehavior.CloseConnection to the ExecuteReader(), then 
+explicitly close the connection. I've commented out the workaround code 
+in the source.