Client side (.NET) connection pooling problem with SA12 and SA16 server

[former_SQLA_member1694875]

Something is wrong with client side (.NET) connection pooling when connecting to SQL Anywhere 12 and 16 servers. When connection is returned back to the pool it loses its name (connection name becomes empty). When trying to find a connection in the pool next time, nothing is found because connection strings do not match (the new connection has a name and the one in the pool doesn't). So a new connection is established everytime, i. e. pooling does not work.

Here is a sample C# code:

//test.aspx.cs:

protected void Page_Load(object sender, EventArgs e)
{
    if (!Page.IsPostBack)
    {
        HttpContext.Current.Session["user_name"] = "CONN_TEST_USER";
    }
}

protected void Button1_Click(object sender, EventArgs e)
{
    DBUtil db = new DBUtil();

    for (int i = 0; i < 10; i++)
    {
        object obj1 = db.ExecuteNonQuery("MESSAGE string('test ', connection_property('number'), ', ', connection_property('name'), ', ', current timestamp);");
    }
}

//DBUtil.cs:

#region Variables
    public SAConnection conn;
    protected SATransaction trans;   
#endregion

public DBUtil()
{
}

public int ExecuteNonQuery(string SQLStatement)
{
    int n = 0;
    SACommand cmd = this.GetAsaCommand(SQLStatement);
    try
    {
        n = cmd.ExecuteNonQuery();
    }
    catch (Exception ex)
    {            
        throw (ex);
    }
    finally
    {
        CloseConnection();
        if (cmd != null) cmd.Dispose();
    }
    return n;
}

protected SACommand GetAsaCommand(string SQLStatement)
{
    SACommand cmd = new SACommand(SQLStatement, this.GetConnection(), this.trans);             
    return cmd;
}

protected SAConnection GetConnection()
{
    try
    {
        if (this.conn == null)
        {
            string application = "WEB 2.0";
            this.conn = new SAConnection("ENG=...;DBN=...;LINKS=tcpip;POOLING=TRUE;Max Pool Size=100;Min Pool Size=0;PROWS=100;Idle=60;UID=web_user;PWD=sql;APP=" + application + " ();Con=" + HttpContext.Current.Session["user_name"].ToString());
        }
        if (this.conn.State != ConnectionState.Open)
        {
            this.conn.Open();
        }
    }
    catch (Exception ex)
    {            
    }
    return this.conn;
}

public void CloseConnection()
{
    try
    {
        if (this.conn != null && this.conn.State != ConnectionState.Closed)
        {
            this.conn.Close();
        }
    }
    catch (Exception ex)
    {           
    }
}

After clicking the button, I get server messages with the following query:

select msg_id, msg_text from sa_server_messages() order by msg_id desc

The results:

1856,test 255, CONN_TEST_USER, 2014-06-06 09:48:18.152
1855,test 254, CONN_TEST_USER, 2014-06-06 09:48:18.148
1854,test 253, CONN_TEST_USER, 2014-06-06 09:48:18.144
1853,test 252, CONN_TEST_USER, 2014-06-06 09:48:18.140
1852,test 251, CONN_TEST_USER, 2014-06-06 09:48:18.137
1851,test 250, CONN_TEST_USER, 2014-06-06 09:48:18.132
1850,test 249, CONN_TEST_USER, 2014-06-06 09:48:18.127
1849,test 248, CONN_TEST_USER, 2014-06-06 09:48:18.123
1848,test 247, CONN_TEST_USER, 2014-06-06 09:48:18.119
1847,test 246, CONN_TEST_USER, 2014-06-06 09:48:18.088

sa_conn_info() shows that last connection (255) with ReqType='CONNECT_POOL_CACHE' and empty name.

SA client version: 12.0.1.4104.
SA server versions: 12.0.1.4104, 16.0.0.1691. But it works correctly with server version 11.0.1.x.
Visual Studio 2013 Professional, .Net framework 4.0, IIS7.

Besides, the problem can be reproduced only on compiled version and not running in Visual Studio environment.

Can anybody else reproduce this problem and is there any workaround available? Or maybe we are missing something?
Thanks in advance.

[jeff_albion]

This bug should already be fixed in CR #743743 in 12.0.1.3941. Are you positive that you're picking up the correct assembly version for the client?

[former_SQLA_member1694875]

Yes, we are using iAnywhere.Data.SQLAnywhere.v4.0.dll file from 12.0.1.4104 build (dll version 12.0.1.41044).

[jeff_albion]

You mentioned:

Besides, the problem can be reproduced only on compiled version and not running in Visual Studio environment.

Is this perhaps because the compiled version is linked against an older version of the assembly and your development environment is picking up the correct version...?

How are you referencing the SQL Anywhere assembly in your ASP.NET project? Are you adding a direct reference to the assembly DLL file, or to a GAC reference, or are you using the web.config file to specify a <connectionStrings> element?

[former_SQLA_member1694875]

Direct reference to the DLL file is being used. And web.config file is being used to specify connection strings (although I've hardcoded it in the question above) but not in <connectionstrings> element but in something like this:

<configuration>
  <appsettings>
    <add key="ConnectString" value="ENG=...;DBN=...;LINKS=tcpip;POOLING=TRUE;Max Pool Size=100;Min Pool Size=0;PROWS=100;Idle=60;UID=web_user;PWD=sql"/>
  </appsettings>
  ...
</configuration>

[former_SQLA_member1694875]

Jeff or others, can you give any hints to solve this problem? Our customer wants to migrate from SA11 to SA12 but without solving this problem it seems risky. Thanks.

[Breck_Carter]

Try running Process Explorer to determine which version of the DLL is actually loaded at runtime. You might have to run it with elevated permissions.

[former_SQLA_member1694875]

Process Explorer shows:

C:\\Windows\\Microsoft.NET\\assembly\\GAC_MSIL\\iAnywhere.Data.SQLAnywhere.v4.0\\v4.0_12.0.1.41044__f222fc4333e0d400\\iAnywhere.Data.SQLAnywhere.v4.0.dll

[jeff_albion]

Hi Arthoor,

I can reproduce this issue - you are right, while the connection is connected, the name appears but once it goes into the connection pool, the connection name disappears. I am going to investigate this behaviour further underneath CR #768516.

However, I'm finding that if I close the connection, it is re-used as expected from the connection pool. Are you seeing the same behaviour?

[former_SQLA_member1694875]

Jeff,

No, we see partly different behavior. The name disappears when connection is returned to the pool but is never reused again, as I wrote in the question. Maybe you are testing server side pooling, not client side (.NET)?

[former_SQLA_member1694875]

Jeff,

Are there any news about this problem? It is still very important as we can't migrate to newer versions of SA.

[jeff_albion]

Hi Arthoor,

The issue is still open. However, like I replied originally, for myself, the actual pooling of the connection still works, up to the default maximum number of connections in the pool. How many connections are you seeing?

If you would like to expedite this issue, you should open an incident underneath your SAP Support Plan and explain why this bug is important to your migration and we can provide more detailed tracking information as the bug is examined.

[former_SQLA_member1694875]

As I wrote in the question, sa_conn_info() shows that last (one) connection with empty name.

[MCMartin]

This conforms to the question Connection pooling and connection parameters there the answer was yes the CON parameter counts for finding a reusable connection, so I would say it is a bug, that the connection name is emptied on close.