Dapper looses parameters

[dj-raphael]

In some rare cases Dapper sends requests to SQLServer without parameters. I was able to reproduce it in test project:
https://github.com/dj-raphael/Dapper-test-lost-parameters

To reproduce, you should start this project in Visual Studio in Debug mode and wait for about a minute. However we encounter this problem from time to time (very rarely) in Release build on our production server. Here is the screenshot of exception we get:
https://github.com/dj-raphael/Dapper-test-lost-parameters/blob/master/exception.png

Here is the log obtained using MiniProfiler.Integrations library, I'm logging sql request before it was sent to SQLServer

try
{
    ...
    var result = await connection.QueryAsync(sql.ToString(), map: Map, types: types, splitOn: splitOn, param: parameterValues).ConfigureAwait(false);
}
catch (SqlException e)
{
    e.Data["profile-query-after-dapper"] = profiler.GetCommands();
    e.AppendExceptionData(sql.ToString(), parameterValues);
    throw;
}

https://github.com/dj-raphael/Dapper-test-lost-parameters/blob/master/example-log.txt

Log shows that I'm passing parameters to Dapper but SQLServer receives a request without any parameters at all. Another interesting fact is that it happens only when at least one of parameters is an array.

[NickCraver]

In the example project (correct me if I'm wrong), it's spinning up workers and never-ending threads on each is perpetuity until something breaks...what what breaks? Is it out of memory? Connections? Can you reproduce this with raw ADO.NET? I can't think of a reason this would fail in Dapper itself (if we hit resource exhaustion we should blow sky high), but something in lower I can see doing it.

[mattyas]

We have been having the same issue for one of our queries after starting to use IN @parameter with an IEnumerable. If we recycle our app pool the query starts to work again, making us believe it has something to do with the caching in Dapper. The problem is that we have only seen it in production and is sofar unable to reproduce it in dev. When we have seen the error, the app has been running for a while before trying to run the query, if we run it quite soon after the start it works as expected.

Looking at the sql and parameters:
The parameter @ids is an IEnumerable and @EventId an int
The part of the sql that we have a problem with is:
x.EventId = @EventId AND y.Id IN @Ids AND y.PerformedEventId IS NOT NULL
normally it translates to (given 3 ints)
x.EventId = @EventId AND y.Id IN (@Ids1,@Ids2,@Ids3) AND y.PerformedEventId IS NOT NULL
but when it starts failing in production the sql that is being executed is
x.EventId = @EventId AND y.Id IN @Ids AND y.PerformedEventId IS NOT NULL
giving the sql exception about incorrect syntax near @ids

[NickCraver]

@mattyas Thanks for the info here - that's extremely helpful to track this down.

[dj-raphael]

Completely agree with @mattyas, problem occurs only in arrays.

I've found a workaround, if when calling method you'll add parameter buffered: false than the problem disappears, I wasn't able to reproduce it for 6 hours using my sample if using this workaround

However disabling buffering is not a good solution, so it would be great if this bug will be actually fixed

[dj-raphael]

Unfortunately workaround mentioned in my previous post works for test project, but doesn't work for our actual program: using parameter buffered:false I receive the following exception:

Invalid attempt to call FieldCount when reader is closed.
   at System.Data.SqlClient.SqlDataReader.get_FieldCount()
   at StackExchange.Profiling.Data.ProfiledDbDataReader.get_FieldCount()
   at Dapper.SqlMapper.GetColumnHash(IDataReader reader, Int32 startBound, Int32 length)
   at Dapper.SqlMapper.<MultiMapImpl>d__145`1.MoveNext()

Calling ToList inside using connection does not help to fix this problem.

So it would be great if this bug will be fixed, it keeps causing troubles on our production system, occurring several times per day

[danhemma]

I have not been able to find a workaround for this problem. Moving from parameters of type IEnumerable to use xml made the problem less frequent (about one tenth as frequent) but it was not totally resolved.

My last attempt to tackle this was to catch SqlException with Number 137 (parameter missing) and when that occurs call Dapper.SqlMapper.PurgeQueryCache and retry the command or query.

After deploying this I have been able to verify that this works a intended;

1. A query fails with error 137.
2. The cache is purged
3. The same query is retried with the same set of parameters and this time it is successful.

With help from MiniProfiler.Integrations I can verify that when the query fails the parameters are not included in the database call but after purging the cache they are.

[StevenFlyover]

Dapper version: 1.50.5
dotNet version: 4.5.2
i having a same issue too. IIS webAPI, save data to database, all parameters saved to database exclude last 2 sometime would lost. About a dozen for every 1,000 requests or so.

using (var conn = new SqlConnection(connString))
{
conn.Open();

DynamicParameters dynamicParams = new DynamicParameters();
dynamicParams.Add("@p1", xxx);
dynamicParams.Add("@p2", xxx);
dynamicParams.Add("@p3", xxx);
dynamicParams.Add("@p4", xxx);
dynamicParams.Add("@p5", xxx);
dynamicParams.Add("@p6", xxx);
dynamicParams.Add("@p7", xxx);
dynamicParams.Add("@p8", xxx);
dynamicParams.Add("@p9", xxx);
dynamicParams.Add("@p10", xxx);
dynamicParams.Add("@p11", xxx);
dynamicParams.Add("@p12", xxx);
dynamicParams.Add("@p13", xxx);

.......
dynamicParams.Add("@p14", xxx);

if (XXXXXX)
{
	dynamicParams.Add("@p15", xxx);
}
dynamicParams.Add("@rtn", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);

conn.Execute("SP", dynamicParams, commandType: CommandType.StoredProcedure);
.....

}

[dj-raphael]

we use workaround:

            try
            {
                result = await connection.QueryAsync(sql, map, parameterValues, null, splitOn: splitOn, commandTimeout: _commandTimeout).ConfigureAwait(false);
            }
            catch (SqlException e)
            {
                if (e.Message.Contains("Must declare the scalar variable"))
                {
                    global::Dapper.SqlMapper.PurgeQueryCache();
                    await Task.Delay(10);
                    try
                    {
                        result = await connection.QueryAsync(sql, map, parameterValues, null, splitOn: splitOn, commandTimeout: _commandTimeout).ConfigureAwait(false);
                    }
                }
                else
                {
                    throw;
                }
            }

After clearing cache, the next error throws after about 2 days

[supershowwei]

I have same issue.