SAP Sybase SQL Anywhere NullReference Exception when openening and closing many connections in a service

Question

Currently I've the problem that SAP Sybase SQL Anywhere randomly throws NullReferenceExceptions in a service which executes a lot of sql queries. The connections are always created in a using block and opened & closed correctly. There are not many parallel connections, but after a while (randomly) the following exception is thrown when opening and closing connections:

Exception: System.NullReferenceException: The object was not set to an instance.
   bei iAnywhere.Data.SQLAnywhere.SAConnection.Dispose(Boolean disposing)
   bei iAnywhere.Data.SQLAnywhere.SAConnection.Close()
   bei iAnywhere.Data.SQLAnywhere.SAConnection.get_State()
   bei product.Framework.DAL.ConnectionManager.GetOpenPoolConnection[T](String ModuleName, String Connection, Boolean resizePoolOnRimteOut, Int64 Time
Out, Boolean isSecondTry)
   bei product.Framework.DAL.ORM.Sybase.SybaseStack.LoadDataFromDB[T](String where_part, String connectionStringName, Object[] sa_params)
   bei product.Framework.DAL.ORM.Sybase.SybaseStack.LoadData[T](String optWherePart, Object[] parameter)
   bei product.PlugIn.DocCenterClient.AS_Modules.DefaultInstanceDataExportModule.DoSingalProcessing()
   bei System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCt
x)
   bei System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
   bei System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
   bei System.Threading.ThreadHelper.ThreadStart()

Does someone know what is causing this behavior? We could not figure out any regularity behind it.

The exception is being thrown in GetOpenPoolConnection, which creates a new SAConnection and opens it:

internal static T GetOpenPoolConnection<T>(string Connection = "Default") where T : DbConnection
{
    // Read connection string from static dictionary
    string cConnectionString = GetConnectionString(Connection);
    T cToReturn = null;

    if (cConnectionString != null)
    {
        if (typeof(T) == typeof(SqlConnection))
        {
            cToReturn = (new SqlConnection(cConnectionString) as T);
            cToReturn.Open();
        }
        else if (typeof(T) == typeof(SAConnection))
        {
            cToReturn = (new SAConnection(cConnectionString) as T);
            cToReturn.Open();
        }
        else if (typeof(T) == typeof(OdbcConnection))
        {
            cToReturn = (new OdbcConnection(cConnectionString) as T);
            cToReturn.Open();
        }

        return cToReturn;
    }
    else
    {
        return null;
    }
}

This function is called as:

using (SAConnection connection = DAL.ConnectionManager.GetOpenPoolConnection<SAConnection>())
{
    var res = connection.Query<Guid>("SELECT InstanceDataGuid FROM AS_EX_Objects WHERE ExchangeObjectId = ?", new { ExchangeObjectId = ic.ItemId.ToString() });
    if (res.Any())
    {
        instanceDataGuid = res.Single<Guid>();
   }
}

We are using SAP SQL Anywhere 12 as database engine/server and SAP SQL Anywhere 16 as client component. The project and DB driver are 64-bit only. All the bugs that cause this should be fixed in the version of the ADO .Net Driver we're using (in the bugfixes, see engineering cases #797124, #741721 and #738144).

without seeing you code this is something that you will have to actually go in and evaluate / check all the spots where you are creating instances of objects and disposing them. can you show the code that does the opening and closing of connections..? if you have this in a using then do not explicitly call the connection.Close() method — MethodMan, Jun 14 '16 at 20:30
Already thought about posting some code, but this happens at different points, but i will add some example. — BendEg, Jun 14 '16 at 20:33
if the code is the same / pretty consistent in regards to how you are Opening and Closing then show an example of each.. — MethodMan, Jun 14 '16 at 20:34
@MethodMan ok, added some code where this is happening (using Dapper there) — BendEg, Jun 14 '16 at 20:36
check out this link it may answer your question about `DAL.ConnectionManager.GetOpenPoolConnection` http://www.codegur.press/35697729/how-does-dapper-create-an-instance-of-the-correct-command-class-in-ado-net also on your `ADO NET Drivers` is this `32 bit or 64 bit` what do you have your `platform target` set to under the project properties..? — MethodMan, Jun 14 '16 at 20:41
I've written `DAL.ConnectionManager.GetOpenPoolConnection` so thats no problem :D. In this case it is 64 Bit only. — BendEg, Jun 14 '16 at 20:43
can you post that if it's not too much trouble.. perhaps there is an issue in there can't really tell with the little bit of code that you have posted.. sorry — MethodMan, Jun 14 '16 at 20:44
@MethodMan ok, added the code. But don't be afraid, thats really ugly code, would not write any thing like this any more :) — BendEg, Jun 14 '16 at 20:47
I would personally store the 3 different connection string in a config file but the only think that I can recommend is that you step through the code to see if it's doing the same thing you've been noticing on the target machine. can you reproduce the error on your local machine..? — MethodMan, Jun 14 '16 at 20:55
Yep, but the service some times crashs after a day and some times after an hour. So it's hard to figure out. But i will try it like you explaned. — BendEg, Jun 14 '16 at 20:56
if you know of what type of DataBase then I would just assign the connection string to a value that should be already declared and assigned from the reading the App.Config I currently do this but using 2 different connection strings. — MethodMan, Jun 14 '16 at 21:00
Yes, i would like to change this code, but it is used in many other places, so i was afraid editing it until now. I will schedule some larger refactoring work and clean this parts. Thanks for your time! — BendEg, Jun 14 '16 at 21:02
What are `Query` and `Single` methods? Did you wrote them? Are they implemented using deferred execution? — Bozhidar Stoyneff, Jun 22 '16 at 19:16
They are a part of dapper and written by stackoverflow. This is also happening using raw ado.net — BendEg, Jun 22 '16 at 19:30
Can you try not to use `GetOpenPoolConnection`? Given the call stack it's the last place in your code before the error. Next are methods in the Sybase libs; I don't think you could do anything about them except update (if it is available)... — Bozhidar Stoyneff, Jun 25 '16 at 07:20
I think you will need more information. You don't really know what is null, it could be a number of variables from what is posted. — HBomb, Jun 27 '16 at 02:18
I would advise wrapping your GetOpenPoolConnection internal statements in a try catch block, and then log in a text file somewhere a everything you can about the call... including what step in the code you are in. — HBomb, Jun 27 '16 at 02:19
Preferably, you could step through the code, but you're stating that this is happening randomly in production after some time, so my assumption is that you cannot reproduce the error locally. That's why I'm suggesting the logging route. — HBomb, Jun 27 '16 at 02:20
Your call to GetConnectionString(Connection) could in fact be returning null for some reason, which might be causing this.... impossible to tell from the code posted at the moment. — HBomb, Jun 27 '16 at 02:21
As a matter of fact, you could also try `// Read connection string from static dictionary string cConnectionString = GetConnectionString(Connection); T cToReturn = null; if (cConnectionString == null) {throw new Exception("Connection String was null!")}; else{.....` — HBomb, Jun 27 '16 at 02:25
that would be easier to implement instead of the verbose logging (but might not be sufficient for your issue.) — HBomb, Jun 27 '16 at 02:26
@HBomb thank you, will add more logging functions in here, so maybe can get more information about the environment. — BendEg, Jun 27 '16 at 06:08
it may be helpful check below link. https://stackoverflow.com/questions/4660142/what-is-a-nullreferenceexception-and-how-do-i-fix-it — M Danish, May 07 '18 at 10:27
As from your code whenever the connection returned is null the "using" is hitting an exception, perhaps using a try catch will allow to consider this eventuality to avoid a crash and simply leave a single SQL undone or tried another time. Consider if any external root cause can prevent it, i.e. network timeout. Once I experienced DB connection issues due to a "next generation" network firewall which had IPS function detecting too many new connections per second as brute force attack against the server. — A. Lion, Jan 27 '19 at 17:56

SAP Sybase SQL Anywhere NullReference Exception when openening and closing many connections in a service

0 Answers0