Down the rabbit hole

Now, let’s dig into what this trace can tell us:

If you scroll down, you will see that one POST call to payforadoptions service failed with a “500” return code.

(You might have realised that they still say “200 OK” which means the failed call was not catched by the caller and just ignored the failure, here it’s ok because it’s only the housekeeping (db cleaning) function that occasionnaly failed but in case of critical call it would be important to let the end user know by catching that in caller and sending back the right error code.)

Let’s drill down by clicking on the latest red sub segment of the trace PayForAdoption.

Under Exception you should be able to see the root cause of our problem:

message

Not allowed to change the 'ConnectionString' property. The connection's current state is open.

As well as the stacktrace captured by X-Ray:

System.Data.SqlClient.SqlConnection.ConnectionString_Set (undefined:0)
System.Data.SqlClient.SqlConnection.set_ConnectionString (undefined:0)
PayForAdoption.Controllers.HomeController.CleanupAdoptions (/src/Controllers/HomeController.cs:78)
Microsoft.Extensions.Internal.ObjectMethodExecutor+<>c__DisplayClass33_0.<WrapVoidMethod>b__0 (undefined:0)
Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor+VoidResultExecutor.Execute (undefined:0)
Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeActionMethodAsync (undefined:0)
Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next (undefined:0)
Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeNextActionFilterAsync (undefined:0)
System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw (undefined:0)
Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow (undefined:0)
Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next (undefined:0)
Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeInnerFilterAsync (undefined:0)
System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw (undefined:0)
System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification (undefined:0)
Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker+<<InvokeFilterPipelineAsync>g__Awaited|19_0>d.MoveNext (undefined:0)
System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw (undefined:0)
System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification (undefined:0)
Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker+<<InvokeAsync>g__Awaited|17_0>d.MoveNext (undefined:0)
System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw (undefined:0)
System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification (undefined:0)
Microsoft.AspNetCore.Routing.EndpointMiddleware+<<Invoke>g__AwaitRequestTask|6_0>d.MoveNext (undefined:0)
System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw (undefined:0)
System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification (undefined:0)
Microsoft.AspNetCore.Authorization.AuthorizationMiddleware+<Invoke>d__5.MoveNext (undefined:0)
System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw (undefined:0)
System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification (undefined:0)
Amazon.XRay.Recorder.Handlers.AspNetCore.Internal.AWSXRayMiddleware+<Invoke>d__14.MoveNext (undefined:0)

Following the stacktrace /src/Controllers/HomeController.cs:78 we can open /workshopfiles/one-observability-demo/PetAdoptions/payforadoption/PayForAdoption/Controllers/HomeController.cs and check the code around this line.

We indeed can see that connectionString variable is set again in the circled line, but it shouldn’t be that way since the code is reusing the same connection.

Let’s fix that by applying the following changes. Open the HomeController.cs file on Cloud9 and replace the following line starting from the class declaration to the end of the CleanupAdoptions method body:

    public class HomeController : ControllerBase
    {
        private static HttpClient _httpClient = new HttpClient(new HttpClientXRayTracingHandler(new HttpClientHandler()));
        private static IConfiguration _configuration;
        private static string ConnectionString;

        public HomeController(IConfiguration configuration)
        {
            _configuration = configuration;
            AWSSDKHandler.RegisterXRayForAllServices();
        }

        [HttpPost("CompleteAdoption")]
        public async Task<string> CompleteAdoption([FromQuery] string petId, string pettype)
        {
            try
            {
                Console.WriteLine($"[{AWSXRayRecorder.Instance.GetEntity().TraceId}] - In CompleteAdoption Action method - PetId:{petId} - PetType:{pettype}");
                AWSXRayRecorder.Instance.AddAnnotation("PetId", petId);
                AWSXRayRecorder.Instance.AddAnnotation("PetType", pettype);
                
                var sqlCommandText = $"INSERT INTO [dbo].[transactions] ([PetId], [Transaction_Id], [Adoption_Date]) VALUES ('{petId}', '{Guid.NewGuid().ToString()}', '{DateTime.Now.ToString()}')";

                AWSXRayRecorder.Instance.AddMetadata("Query",sqlCommandText);

                using (SqlConnection _sqlConnection = new SqlConnection(await GetConnectionString()))
                {
                    using var command = new TraceableSqlCommand(sqlCommandText, _sqlConnection);
                    command.Connection.Open();
                    command.ExecuteNonQuery();
                }
            }
            catch (Exception e)
            {
                return e.Message;
            }

            await AWSXRayRecorder.Instance.TraceMethod("UpdateAvailability", () =>  UpdateAvailability(petId,pettype));

            return "Success";
        }
        
        [HttpPost("CleanUpAdoptions")]
        public async Task CleanupAdoptions()
        {
            var sqlCommandText = $"DELETE FROM [dbo].[transactions]";

            AWSXRayRecorder.Instance.AddMetadata("Query",sqlCommandText);
    
            using (SqlConnection _sqlConnection = new SqlConnection(await GetConnectionString()))
            {
                using var command = new TraceableSqlCommand(sqlCommandText, _sqlConnection);
                command.Connection.Open();
                command.ExecuteNonQuery();
            }
        }

This modification follow the good practices mentionned here https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql-server-connection-pooling on how to do proper connection pooling.

Let’s deploy it!

cd ~/environment/workshopfiles/one-observability-demo/PetAdoptions/cdk/pet_stack
cdk synth && cdk deploy Services

It might take few minutes since PayForAdoption ECS service needs to be updated, replacing containers.